Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
5.7.31-34, 8.0.22-13, 5.7.33-36 (Q1 2021)
-
None
Description
If you want to change the tx-isolation on a session that already executed a transaction the change is not honored so it may cause a failure in the service.
Example:
[[email protected] ~]# mysql -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.31-34-log Percona Server (GPL), Release 34, Revision 2e68637 Copyright (c) 2009-2020 Percona LLC and/or its affiliates Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql> use percona Database changed mysql> show create table checksums\G *************************** 1. row *************************** Table: checksums Create Table: CREATE TABLE `checksums` ( `db` char(64) COLLATE utf8_unicode_ci NOT NULL, `tbl` char(64) COLLATE utf8_unicode_ci NOT NULL, `chunk` int(11) NOT NULL, `chunk_time` float DEFAULT NULL, `chunk_index` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `lower_boundary` blob, `upper_boundary` blob, `this_crc` char(40) COLLATE utf8_unicode_ci NOT NULL, `this_cnt` int(11) NOT NULL, `master_crc` char(40) COLLATE utf8_unicode_ci DEFAULT NULL, `master_cnt` int(11) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`db`,`tbl`,`chunk`), KEY `ts_db_tbl` (`ts`,`db`,`tbl`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> use db_test_tx_iso Database changed mysql> show create table test_tx_iso\G *************************** 1. row *************************** Table: test_tx_iso Create Table: CREATE TABLE `test_tx_iso` ( `key` varbinary(742) NOT NULL, `version` bigint(20) NOT NULL, `value` mediumblob, `pelock` varbinary(64) DEFAULT NULL, PRIMARY KEY (`key`,`version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 1 row in set (0.00 sec) mysql> show global variables like 'binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec) mysql> show global variables like 'tx_isolatio%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.01 sec) mysql> show variables like 'tx_isolatio%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.10 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db_test_tx_iso`.`test_tx_iso`; Query OK, 2 rows affected (0.24 sec) Records: 1 Duplicates: 1 Warnings: 0
*works ok, tx-isolation is read-commited and binlog-format=row
*now within same session:
mysql> set session tx_isolation="REPEATABLE-READ"; set session binlog_format=STATEMENT; REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db_test_tx_iso`.`test_tx_iso`; Query OK, 0 rows affected, 1 warning (0.01 sec) Query OK, 0 rows affected (0.00 sec) ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. mysql> show variables like 'tx_isolatio%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.01 sec)
*Query fails even if session changed tx-isolation to REPEATABLE-READ
Now if I start a new session:
[[email protected] ~]# mysql -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.31-34-log Percona Server (GPL), Release 34, Revision 2e68637 Copyright (c) 2009-2020 Percona LLC and/or its affiliates Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql> show variables like 'tx_isolatio%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) mysql> set session tx_isolation="REPEATABLE-READ"; set session binlog_format=STATEMENT; REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db_test_tx_iso`.`test_tx_iso`; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected, 1 warning (0.05 sec) Records: 1 Duplicates: 1 Warnings: 1 mysql> REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db_test_tx_iso`.`test_tx_iso`; Query OK, 2 rows affected, 1 warning (0.01 sec) Records: 1 Duplicates: 1 Warnings: 1
Queries are executed in the expected way
How to repeat:
1- Start server with binlog_format=ROW and transaction_isolation=READ-COMMITTED
2- create 2 tables, no need to add data or specific table format.
3- Run a REPLACE INTO table1 SELECT FROM table2; command
4- Change tx-isolation to REPEATABLE READ and binlog_format to STATEMENT (pretty much what pt-table-checksum needs to do)
5- Repeat query from step 3, it should fail.