revision-id: 047c7b119f498856918069540a741fb9fee279de (mariadb-10.1.39-56-g047c7b119f4) parent(s): e7695f95ae714f3168ce953fd022ddfb40f03e67 author: Sachin committer: Sachin timestamp: 2019-06-11 00:09:31 +0530 message: MDEV-17603 REPLACE and INSERT…ON DUPLICATE KEY UPDATE are deadlock-prone in statement-based replication Make INSERT...ON DUPLICATE KEY UPDATE unsafe while using stmt or mixed format When there is more then one unique key. Although there is two exception. 1. Auto Increment key is not counted because Innodb will get gap lock for failed Insert and concurrent insert will get a next increment value. 2. Count only unique keys for which insertion is performed. --- .../suite/rpl/r/rpl_known_bugs_detection.result | 29 +++++++++++------ .../suite/rpl/t/rpl_known_bugs_detection.test | 20 +++++++----- sql/sql_class.h | 28 ++++++++--------- sql/sql_insert.cc | 36 ++++++++++++++++++++++ 4 files changed, 81 insertions(+), 32 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result index ea738b710fd..9aa681bdc85 100644 --- a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result +++ b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result @@ -2,18 +2,27 @@ call mtr.add_suppression("Unsafe statement written to the binary log using state include/master-slave.inc [connection master] call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, -UNIQUE(b)); -INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT, +UNIQUE(b), c int) engine=innodb; +INSERT INTO t1 VALUES (1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +COMMIT; SELECT * FROM t1; -a b -1 10 -2 2 -call mtr.add_suppression("Slave SQL.*suffer.*http:..bugs.mysql.com.bug.php.id=24432"); -include/wait_for_slave_sql_error.inc [errno=1105] -Last_SQL_Error = 'Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10'' +a b c +1 1 2 +2 2 3 +include/wait_for_slave_sql_error.inc [errno=1062] +Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)'' SELECT * FROM t1; -a b +a b c +1 1 1 +2 2 3 stop slave; include/wait_for_slave_to_stop.inc reset slave; diff --git a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test index ab263ece407..75fb59e7a21 100644 --- a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test +++ b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test @@ -6,6 +6,7 @@ call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); source include/have_debug.inc; +source include/have_innodb.inc; # because of pretend_version_50034_in_binlog the test can't run with checksum source include/have_binlog_checksum_off.inc; @@ -25,19 +26,22 @@ call mtr.add_suppression("Unsafe statement written to the binary log using state # # testcase with INSERT VALUES -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, -UNIQUE(b)); +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT, +UNIQUE(b), c int) engine=innodb; sync_slave_with_master; connection master; -INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +INSERT INTO t1 VALUES (1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; SELECT * FROM t1; -connection slave; - +--connection slave # show the error message -#1105 = ER_UNKNOWN_ERROR ---let $slave_sql_errno= 1105 +--let $slave_sql_errno= 1062 --let $show_slave_sql_error= 1 -call mtr.add_suppression("Slave SQL.*suffer.*http:..bugs.mysql.com.bug.php.id=24432"); --source include/wait_for_slave_sql_error.inc # show that it was not replicated SELECT * FROM t1; diff --git a/sql/sql_class.h b/sql/sql_class.h index 1cb516c0656..24545e2e140 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2208,6 +2208,20 @@ class THD :public Statement, /* container for handler's private per-connection data */ Ha_data ha_data[MAX_HA]; + /** + Bit field for the state of binlog warnings. + + The first Lex::BINLOG_STMT_UNSAFE_COUNT bits list all types of + unsafeness that the current statement has. + + This must be a member of THD and not of LEX, because warnings are + detected and issued in different places (@c + decide_logging_format() and @c binlog_query(), respectively). + Between these calls, the THD->lex object may change; e.g., if a + stored routine is invoked. Only THD persists between the calls. + */ + uint32 binlog_unsafe_warning_flags; + #ifndef MYSQL_CLIENT binlog_cache_mngr * binlog_setup_trx_data(); @@ -2317,20 +2331,6 @@ class THD :public Statement, */ enum_binlog_format current_stmt_binlog_format; - /** - Bit field for the state of binlog warnings. - - The first Lex::BINLOG_STMT_UNSAFE_COUNT bits list all types of - unsafeness that the current statement has. - - This must be a member of THD and not of LEX, because warnings are - detected and issued in different places (@c - decide_logging_format() and @c binlog_query(), respectively). - Between these calls, the THD->lex object may change; e.g., if a - stored routine is invoked. Only THD persists between the calls. - */ - uint32 binlog_unsafe_warning_flags; - /* Number of outstanding table maps, i.e., table maps in the transaction cache. diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 0be8abf4842..3d9c91dc060 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1025,6 +1025,42 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, error= 1; break; } + /* + INSERT...ON DUPLICATE KEY UPDATE on a table with more than one unique keys + can be unsafe. + */ + if(thd->wsrep_binlog_format() <= BINLOG_FORMAT_STMT && + !thd->is_current_stmt_binlog_format_row() && + !thd->lex->is_stmt_unsafe() && + thd->lex->sql_command == SQLCOM_INSERT && + thd->lex->duplicates == DUP_UPDATE) + { + uint unique_keys= 0; + uint keys= table->s->keys, i= 0; + Field *field; + for (KEY* keyinfo= table->s->key_info; + i < keys && unique_keys <= 1; i++, keyinfo++) + if (keyinfo->flags & HA_NOSAME && + !(keyinfo->key_part->field->flags & AUTO_INCREMENT_FLAG)) + { + for (uint j= 0; j < keyinfo->user_defined_key_parts; j++) + { + field= keyinfo->key_part[j].field; + if(!bitmap_is_set(table->write_set,field->field_index)) + goto exit; + } + unique_keys++; +exit:; + } + + if (unique_keys > 1) + { + thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS); + thd->binlog_unsafe_warning_flags|= thd->lex->get_stmt_unsafe_flags(); + thd->set_current_stmt_binlog_format_row_if_mixed(); + } + + } #ifndef EMBEDDED_LIBRARY if (lock_type == TL_WRITE_DELAYED) {