- commits - lists.mariadb.org

[Commits] 2e0fb93e6f3: MDEV-17614 INSERT on dup key update is replication unsafe
by sachin.setiya＠mariadb.com 12 Jun '19

12 Jun '19

revision-id: 2e0fb93e6f3359ffe146f309e32c59922400b7c4 (mariadb-10.1.39-56-g2e0fb93e6f3) parent(s): e7695f95ae714f3168ce953fd022ddfb40f03e67 author: Sachin committer: Sachin timestamp: 2019-06-12 11:47:26 +0530 message: MDEV-17614 INSERT on dup key update is replication unsafe 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. But if user supplies auto inc value it can be unsafe. 2. Count only unique keys for which insertion is performed. So this patch also addresses the bug id #72921 --- .../suite/rpl/r/rpl_known_bugs_detection.result | 20 ---- mysql-test/suite/rpl/r/rpl_mdev_17614.result | 98 +++++++++++++++++ .../suite/rpl/t/rpl_known_bugs_detection.test | 39 ------- mysql-test/suite/rpl/t/rpl_mdev_17614.test | 121 +++++++++++++++++++++ sql/sql_class.h | 28 ++--- sql/sql_insert.cc | 38 +++++++ 6 files changed, 271 insertions(+), 73 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..adef091ea3e 100644 --- a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result +++ b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result @@ -1,26 +1,6 @@ call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); 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; -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'' -SELECT * FROM t1; -a b -stop slave; -include/wait_for_slave_to_stop.inc -reset slave; -reset master; -drop table t1; -start slave; -include/wait_for_slave_to_start.inc CREATE TABLE t1 ( id bigint(20) unsigned NOT NULL auto_increment, field_1 int(10) unsigned NOT NULL, diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17614.result b/mysql-test/suite/rpl/r/rpl_mdev_17614.result new file mode 100644 index 00000000000..28de23e28c9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mdev_17614.result @@ -0,0 +1,98 @@ +include/master-slave.inc +[connection master] +call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); +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 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)'' +#Different value from server +SELECT * FROM t1; +a b c +1 1 1 +2 2 3 +stop slave; +include/wait_for_slave_to_stop.inc +reset slave; +reset master; +drop table t1; +start slave; +include/wait_for_slave_to_start.inc +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +INSERT INTO t1 VALUES (default, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +COMMIT; +SELECT * FROM t1; +a b c +1 1 2 +3 2 3 +#same data as master +SELECT * FROM t1; +a b c +1 1 2 +3 2 3 +drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 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(3, NULL, 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 c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +#same data as master +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, 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); +INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +COMMIT; +SELECT * FROM t1; +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)'' +#Different value from server +SELECT * FROM t1; +a b c +1 1 1 +2 2 3 +stop slave; +include/wait_for_slave_to_stop.inc +reset slave; +reset master; +drop table t1; +start slave; +include/wait_for_slave_to_start.inc +include/rpl_end.inc 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..5ea056d5f14 100644 --- a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test +++ b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test @@ -14,45 +14,6 @@ source include/have_binlog_checksum_off.inc; source include/master-slave.inc; -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); - -# -# This is to test that slave properly detects if -# master may suffer from: -# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values" -# (i.e. on master, INSERT ON DUPLICATE KEY UPDATE is used and manipulates -# an auto_increment column, and is binlogged statement-based). -# - -# testcase with INSERT VALUES -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, -UNIQUE(b)); -sync_slave_with_master; -connection master; -INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; -SELECT * FROM t1; -connection slave; - -# show the error message -#1105 = ER_UNKNOWN_ERROR ---let $slave_sql_errno= 1105 ---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; - -# restart replication for the next testcase -stop slave; ---source include/wait_for_slave_to_stop.inc -reset slave; -connection master; -reset master; -drop table t1; -connection slave; -start slave; ---source include/wait_for_slave_to_start.inc - # testcase with INSERT SELECT connection master; CREATE TABLE t1 ( diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17614.test b/mysql-test/suite/rpl/t/rpl_mdev_17614.test new file mode 100644 index 00000000000..9b86c8c15b5 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mdev_17614.test @@ -0,0 +1,121 @@ +source include/have_debug.inc; +source include/have_innodb.inc; +-- source include/have_binlog_format_statement.inc +source include/master-slave.inc; +# MDEV-17614 +# INSERT on dup key update is replication unsafe +# There can be three case +# 1. 2 unique key, Replication is unsafe. +# 2. 2 unique key , with one auto increment key, Safe to replicate because Innodb will acquire gap lock +# 3. n no of unique keys (n>1) but insert is only in 1 unique key +# 4. 2 unique key , with one auto increment key(but user gives auto inc value), unsafe to replicate + +# Case 1 +call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); +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 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 +# show the error message +--let $slave_sql_errno= 1062 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc +--echo #Different value from server +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +--source include/wait_for_slave_to_stop.inc +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; +--source include/wait_for_slave_to_start.inc +# Case 2 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (default, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; + +connection master; +drop table t1; +--sync_slave_with_master + +# Case 3 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; +connection master; +drop table t1; +--sync_slave_with_master + +# Case 4 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +sync_slave_with_master; +connection master; +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 +# show the error message +--let $slave_sql_errno= 1062 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc +--echo #Different value from server +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +--source include/wait_for_slave_to_stop.inc +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; +--source include/wait_for_slave_to_start.inc + +--source include/rpl_end.inc 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..fa1b455f187 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1025,6 +1025,44 @@ 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 && + //User given auto inc can be unsafe + !keyinfo->key_part->field->val_int())) + { + 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) {

1 0

2c50b269ac2: MDEV-17603 REPLACE and INSERT…ON DUPLICATE KEY UPDATE are deadlock-prone in statement-based replication
by sachin.setiya＠mariadb.com 12 Jun '19

12 Jun '19

revision-id: 2c50b269ac247a0c7d86b0bec80bf38fb5499024 (mariadb-10.1.39-56-g2c50b269ac2) parent(s): e7695f95ae714f3168ce953fd022ddfb40f03e67 author: Sachin committer: Sachin timestamp: 2019-06-12 11:45:43 +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. But if user supplies auto inc value it can be unsafe. 2. Count only unique keys for which insertion is performed. So this patch also addresses the bug id #72921 --- .../suite/rpl/r/rpl_known_bugs_detection.result | 20 ---- mysql-test/suite/rpl/r/rpl_mdev_17614.result | 98 +++++++++++++++++ .../suite/rpl/t/rpl_known_bugs_detection.test | 39 ------- mysql-test/suite/rpl/t/rpl_mdev_17614.test | 121 +++++++++++++++++++++ sql/sql_class.h | 28 ++--- sql/sql_insert.cc | 38 +++++++ 6 files changed, 271 insertions(+), 73 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..adef091ea3e 100644 --- a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result +++ b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result @@ -1,26 +1,6 @@ call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); 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; -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'' -SELECT * FROM t1; -a b -stop slave; -include/wait_for_slave_to_stop.inc -reset slave; -reset master; -drop table t1; -start slave; -include/wait_for_slave_to_start.inc CREATE TABLE t1 ( id bigint(20) unsigned NOT NULL auto_increment, field_1 int(10) unsigned NOT NULL, diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17614.result b/mysql-test/suite/rpl/r/rpl_mdev_17614.result new file mode 100644 index 00000000000..28de23e28c9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mdev_17614.result @@ -0,0 +1,98 @@ +include/master-slave.inc +[connection master] +call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); +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 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)'' +#Different value from server +SELECT * FROM t1; +a b c +1 1 1 +2 2 3 +stop slave; +include/wait_for_slave_to_stop.inc +reset slave; +reset master; +drop table t1; +start slave; +include/wait_for_slave_to_start.inc +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +INSERT INTO t1 VALUES (default, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +COMMIT; +SELECT * FROM t1; +a b c +1 1 2 +3 2 3 +#same data as master +SELECT * FROM t1; +a b c +1 1 2 +3 2 3 +drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 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(3, NULL, 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 c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +#same data as master +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, 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); +INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +COMMIT; +SELECT * FROM t1; +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)'' +#Different value from server +SELECT * FROM t1; +a b c +1 1 1 +2 2 3 +stop slave; +include/wait_for_slave_to_stop.inc +reset slave; +reset master; +drop table t1; +start slave; +include/wait_for_slave_to_start.inc +include/rpl_end.inc 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..5ea056d5f14 100644 --- a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test +++ b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test @@ -14,45 +14,6 @@ source include/have_binlog_checksum_off.inc; source include/master-slave.inc; -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); - -# -# This is to test that slave properly detects if -# master may suffer from: -# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values" -# (i.e. on master, INSERT ON DUPLICATE KEY UPDATE is used and manipulates -# an auto_increment column, and is binlogged statement-based). -# - -# testcase with INSERT VALUES -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, -UNIQUE(b)); -sync_slave_with_master; -connection master; -INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; -SELECT * FROM t1; -connection slave; - -# show the error message -#1105 = ER_UNKNOWN_ERROR ---let $slave_sql_errno= 1105 ---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; - -# restart replication for the next testcase -stop slave; ---source include/wait_for_slave_to_stop.inc -reset slave; -connection master; -reset master; -drop table t1; -connection slave; -start slave; ---source include/wait_for_slave_to_start.inc - # testcase with INSERT SELECT connection master; CREATE TABLE t1 ( diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17614.test b/mysql-test/suite/rpl/t/rpl_mdev_17614.test new file mode 100644 index 00000000000..9b86c8c15b5 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mdev_17614.test @@ -0,0 +1,121 @@ +source include/have_debug.inc; +source include/have_innodb.inc; +-- source include/have_binlog_format_statement.inc +source include/master-slave.inc; +# MDEV-17614 +# INSERT on dup key update is replication unsafe +# There can be three case +# 1. 2 unique key, Replication is unsafe. +# 2. 2 unique key , with one auto increment key, Safe to replicate because Innodb will acquire gap lock +# 3. n no of unique keys (n>1) but insert is only in 1 unique key +# 4. 2 unique key , with one auto increment key(but user gives auto inc value), unsafe to replicate + +# Case 1 +call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); +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 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 +# show the error message +--let $slave_sql_errno= 1062 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc +--echo #Different value from server +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +--source include/wait_for_slave_to_stop.inc +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; +--source include/wait_for_slave_to_start.inc +# Case 2 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (default, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; + +connection master; +drop table t1; +--sync_slave_with_master + +# Case 3 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; +connection master; +drop table t1; +--sync_slave_with_master + +# Case 4 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +sync_slave_with_master; +connection master; +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 +# show the error message +--let $slave_sql_errno= 1062 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc +--echo #Different value from server +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +--source include/wait_for_slave_to_stop.inc +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; +--source include/wait_for_slave_to_start.inc + +--source include/rpl_end.inc 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..fa1b455f187 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1025,6 +1025,44 @@ 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 && + //User given auto inc can be unsafe + !keyinfo->key_part->field->val_int())) + { + 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) {

1 0

[Commits] 96ee9ea: MDEV-18479 Another complement
by IgorBabaev 11 Jun '19

11 Jun '19

revision-id: 96ee9ea02e69fb45f369815ace2187dd73398ac4 (mariadb-5.5.64-17-g96ee9ea) parent(s): 6db2ebbb2a63994ef2b43d42a11dbacb8b55c207 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-10 22:38:55 -0700 message: MDEV-18479 Another complement This patch complements the patch that fixes bug MDEV-18479. This patch takes care of possible overflow in JOIN::get_examined_rows(). --- mysql-test/r/derived_view.result | 18 +++++++++--------- sql/sql_select.cc | 17 ++++++++++------- 2 files changed, 19 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index d74b532..f8cf919 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2942,15 +2942,15 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived9> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived10> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived11> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived12> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived13> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived15> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived16> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived7> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived8> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) 17 DERIVED t2 system NULL NULL NULL NULL 1 17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where 17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d7ff92a..a273aae 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6894,17 +6894,22 @@ double JOIN::get_examined_rows() { ha_rows examined_rows; double prev_fanout= 1; + double records; JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); JOIN_TAB *prev_tab= tab; - examined_rows= tab->get_examined_rows(); + records= tab->get_examined_rows(); while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab))) { - prev_fanout *= prev_tab->records_read; - examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout); + prev_fanout= COST_MULT(prev_fanout, prev_tab->records_read); + records= + COST_ADD(records, + COST_MULT((double) (tab->get_examined_rows()), prev_fanout)); prev_tab= tab; } + examined_rows= + records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records; return examined_rows; } @@ -10824,7 +10829,7 @@ ha_rows JOIN_TAB::get_examined_rows() } } else - examined_rows= (ha_rows) records_read; + examined_rows= records_read; return examined_rows; } @@ -22987,9 +22992,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, else { ha_rows examined_rows= tab->get_examined_rows(); - ha_rows displ_rows= examined_rows; - set_if_smaller(displ_rows, HA_ROWS_MAX/2); - item_list.push_back(new Item_int((longlong) (ulonglong) displ_rows, + item_list.push_back(new Item_int((ulonglong) examined_rows, MY_INT64_NUM_DECIMAL_DIGITS)); /* Add "filtered" field to item_list. */

1 0

[Commits] c8ac1b1: MDEV-18479 Anothe complement
by IgorBabaev 11 Jun '19

11 Jun '19

revision-id: c8ac1b1fefdf1eae17c0c6942ba269bb9fae46a9 (mariadb-5.5.64-17-gc8ac1b1) parent(s): 6db2ebbb2a63994ef2b43d42a11dbacb8b55c207 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-10 22:37:04 -0700 message: MDEV-18479 Anothe complement This patch complements the patch that fixes bug MDEV-18479. This patch takes care of possible overflow in JOIN::get_examined_rows(). --- mysql-test/r/derived_view.result | 18 +++++++++--------- sql/sql_select.cc | 17 ++++++++++------- 2 files changed, 19 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index d74b532..f8cf919 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2942,15 +2942,15 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived9> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived10> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived11> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived12> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived13> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived15> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived16> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived7> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE <derived8> ALL NULL NULL NULL NULL 9223372036854775807 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) 17 DERIVED t2 system NULL NULL NULL NULL 1 17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where 17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d7ff92a..a273aae 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6894,17 +6894,22 @@ double JOIN::get_examined_rows() { ha_rows examined_rows; double prev_fanout= 1; + double records; JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); JOIN_TAB *prev_tab= tab; - examined_rows= tab->get_examined_rows(); + records= tab->get_examined_rows(); while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab))) { - prev_fanout *= prev_tab->records_read; - examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout); + prev_fanout= COST_MULT(prev_fanout, prev_tab->records_read); + records= + COST_ADD(records, + COST_MULT((double) (tab->get_examined_rows()), prev_fanout)); prev_tab= tab; } + examined_rows= + records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records; return examined_rows; } @@ -10824,7 +10829,7 @@ ha_rows JOIN_TAB::get_examined_rows() } } else - examined_rows= (ha_rows) records_read; + examined_rows= records_read; return examined_rows; } @@ -22987,9 +22992,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, else { ha_rows examined_rows= tab->get_examined_rows(); - ha_rows displ_rows= examined_rows; - set_if_smaller(displ_rows, HA_ROWS_MAX/2); - item_list.push_back(new Item_int((longlong) (ulonglong) displ_rows, + item_list.push_back(new Item_int((ulonglong) examined_rows, MY_INT64_NUM_DECIMAL_DIGITS)); /* Add "filtered" field to item_list. */

1 0

[Commits] c91095e08: Range Locking: address review input
by Sergei Petrunia 10 Jun '19

10 Jun '19

revision-id: c91095e08fbdacb69831a93ab403a80487dee435 (v5.8-1042-gc91095e08) parent(s): 1b423e3954d932c4624337308e3e1cd98481a495 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-06-10 23:11:27 +0300 message: Range Locking: address review input --- include/rocksdb/utilities/transaction_db.h | 26 ++++++++++++++++++---- utilities/transactions/pessimistic_transaction.cc | 4 +--- .../transactions/pessimistic_transaction_db.cc | 8 +++---- utilities/transactions/transaction_lock_mgr.h | 3 +++ 4 files changed, 29 insertions(+), 12 deletions(-) diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h index 7ebac3e06..84761c80e 100644 --- a/include/rocksdb/utilities/transaction_db.h +++ b/include/rocksdb/utilities/transaction_db.h @@ -33,9 +33,26 @@ enum TxnDBWritePolicy { const uint32_t kInitialMaxDeadlocks = 5; +class BaseLockMgr; + +// A lock manager handle +// The workflow is as follows: +// * Use a factory method (like NewRangeLockManager()) to create a lock +// manager and get its handle. +// * A Handle for a particular kind of lock manager will have extra +// methods and parameters to control the lock manager +// * Pass the handle to RocksDB in TransactionDBOptions::lock_mgr_handle. It +// will be used to perform locking. +class LockManagerHandle { + public: + // Get the underlying LockManager. To be used by RocksDB Internals + virtual BaseLockMgr* GetManager()=0; + virtual ~LockManagerHandle(){}; +}; + // A handle to control RangeLockMgr (Range-based lock manager) from outside // RocksDB -class RangeLockMgrHandle { +class RangeLockMgrHandle : public LockManagerHandle { public: virtual int set_max_lock_memory(size_t max_lock_memory) = 0; virtual uint64_t get_escalation_count() = 0; @@ -44,7 +61,7 @@ class RangeLockMgrHandle { // A factory function to create a Range Lock Manager. The created object should // be: -// 1. Passed in TransactionDBOptions::range_lock_mgr to open the database in +// 1. Passed in TransactionDBOptions::lock_mgr_handle to open the database in // range-locking mode // 2. Used to control the lock manager when the DB is already open. RangeLockMgrHandle* NewRangeLockManager( @@ -112,8 +129,9 @@ struct TransactionDBOptions { // for the special way that myrocks uses this operands. bool rollback_merge_operands = false; - // If non-null, range locking should be used, and the lock manager is passed. - std::shared_ptr<RangeLockMgrHandle> range_lock_mgr; + // nullptr means use default lock manager. + // Other value means the user provides a custom lock manager. + std::shared_ptr<LockManagerHandle> lock_mgr_handle; }; struct TransactionOptions { diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc index 082934e75..4c0578444 100644 --- a/utilities/transactions/pessimistic_transaction.cc +++ b/utilities/transactions/pessimistic_transaction.cc @@ -537,8 +537,6 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family, // an upgrade. if (!previously_locked || lock_upgrade) { s = txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive); - if (!s.ok()) - return s; } SetSnapshotIfNeeded(); @@ -569,7 +567,7 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family, // since the snapshot. This must be done after we locked the key. // If we already have validated an earilier snapshot it must has been // reflected in tracked_at_seq and ValidateSnapshot will return OK. - if (s.ok()) { //psergey-todo: this check seems to be meaningless, s.ok()==true always + if (s.ok()) { s = ValidateSnapshot(column_family, key, &tracked_at_seq); if (!s.ok()) { diff --git a/utilities/transactions/pessimistic_transaction_db.cc b/utilities/transactions/pessimistic_transaction_db.cc index 14b82de00..bacf95479 100644 --- a/utilities/transactions/pessimistic_transaction_db.cc +++ b/utilities/transactions/pessimistic_transaction_db.cc @@ -42,12 +42,10 @@ PessimisticTransactionDB::PessimisticTransactionDB( void PessimisticTransactionDB::init_lock_manager() { - if (txn_db_options_.range_lock_mgr) { + if (txn_db_options_.lock_mgr_handle) { // A custom lock manager was provided in options - std::shared_ptr<RangeLockMgr> tmp; - tmp = std::static_pointer_cast<RangeLockMgr>(txn_db_options_.range_lock_mgr); - lock_mgr_= tmp; - range_lock_mgr_ = static_cast<RangeLockMgr*>(lock_mgr_.get()); + lock_mgr_.reset(txn_db_options_.lock_mgr_handle->GetManager()); + range_lock_mgr_ = dynamic_cast<RangeLockMgr*>(lock_mgr_.get()); } else { // Use point lock manager by default std::shared_ptr<TransactionDBMutexFactory> mutex_factory = diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h index 608d6f34f..54ae77a52 100644 --- a/utilities/transactions/transaction_lock_mgr.h +++ b/utilities/transactions/transaction_lock_mgr.h @@ -243,6 +243,9 @@ class RangeLockMgr : LockStatusData GetLockStatusData() override; + BaseLockMgr* GetManager() override { + return this; + } private: toku::locktree_manager ltm_;

1 0

047c7b119f4: MDEV-17603 REPLACE and INSERT…ON DUPLICATE KEY UPDATE are deadlock-prone in statement-based replication
by sachin.setiya＠mariadb.com 10 Jun '19

10 Jun '19

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) {

1 0

[Commits] d88e173890f: Range Locking: Limited support for shared point locks: Lock escalation
by Sergei Petrunia 10 Jun '19

10 Jun '19

revision-id: d88e173890f85c5138067c0dec897cc60566972a (fb-prod201801-235-gd88e173890f) parent(s): eb6f779acccf4e0f86de98eb894155fa28616cc9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-06-10 17:40:00 +0300 message: Range Locking: Limited support for shared point locks: Lock escalation - Update RocksDB to a revision with it MyRocks part of it: - Testcase - @@rocksdb_max_lock_memory is now a writable global variable - When in range locking mode, there is 'rocksdb_current_lock_memory' status variable that shows amount of memory currently used for locks --- .../rocksdb/include/select_from_rocksdb_locks.inc | 25 ++++- .../rocksdb/r/range_locking_shared_locks.result | 125 ++++++++++++++++++++- .../rocksdb/t/range_locking_shared_locks.test | 85 +++++++++++++- rocksdb | 2 +- storage/rocksdb/ha_rocksdb.cc | 46 ++++++-- 5 files changed, 265 insertions(+), 18 deletions(-) diff --git a/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc b/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc index 31280b0510c..79bd261d7b4 100644 --- a/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc +++ b/mysql-test/suite/rocksdb/include/select_from_rocksdb_locks.inc @@ -1,3 +1,16 @@ +# +# An include to print contents of I_S.ROCKSB_LOCKS +# +# Implicit "parameters" +# - Currently it prints locks on t1.PRIMARY +# +# Explicit "parameter" variables: +# - $TRX1_ID - print this transaction as "TRX1" +# - $TRX2_ID - print this transaction as "TRX2" +# +# - $select_from_is_rowlocks_current_trx_only +# - $order_by_rowkey + --echo # select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc --disable_query_log set @cf_id=(select column_family from information_schema.rocksdb_ddl @@ -24,12 +37,20 @@ if ($TRX2_ID) let $transaction_col = replace($transaction_col, '$TRX2_ID', "\$TRX2_ID"); } ---sorted_result +if ($order_by_rowkey) +{ + let $extra_order_by = ORDER BY 3,2; +} + +if (!$order_by_rowkey) +{ + --sorted_result +} eval select replace(column_family_id, @cf_id, "\$cf_id") as COLUMN_FAMILY_ID, $transaction_col as TRANSACTION_ID, replace(`key`, @indexnr, '\${indexnr}') as `KEY`, mode -from information_schema.rocksdb_locks $extra_where; +from information_schema.rocksdb_locks $extra_where $extra_order_by; --enable_query_log diff --git a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result index 014b107aea5..12b6ae9a6ea 100644 --- a/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result +++ b/mysql-test/suite/rocksdb/r/range_locking_shared_locks.result @@ -112,8 +112,6 @@ pk a 3 3 4 4 5 5 -# TODO: the following prints an X lock on the range, because GetRangeLock API -# currently only supports write locks: # select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc COLUMN_FAMILY_ID TRANSACTION_ID KEY mode $cf_id $TRX1_ID 0000${indexnr}80000002 S @@ -126,3 +124,126 @@ $cf_id $TRX2_ID 0000${indexnr}80000384 X rollback; disconnect con1; drop table t0,t1; +# +# Test shared point locks and lock escalation +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int primary key, +a int +) engine=rocksdb; +insert into t1 +select 1000 + 100*A.a + 10*B.a + C.a, 12345 from t0 A, t0 B, t0 C; +show status like 'rocksdb_locktree_current_lock_memory'; +Variable_name Value +rocksdb_locktree_current_lock_memory 0 +connect con1,localhost,root,,; +connection con1; +begin; +# CON1: get some shared locks +select * from t1 where pk=1001 lock in share mode; +pk a +1001 12345 +select * from t1 where pk=1100 lock in share mode; +pk a +1100 12345 +select * from t1 where pk=1200 lock in share mode; +pk a +1200 12345 +select * from t1 where pk=2500 lock in share mode; +pk a +connection default; +begin; +# DEFAULT: get the same locks so we have locks with multiple owners +select * from t1 where pk=1001 lock in share mode; +pk a +1001 12345 +select * from t1 where pk=1100 lock in share mode; +pk a +1100 12345 +select * from t1 where pk=1200 lock in share mode; +pk a +1200 12345 +# DEFAULT: get shared locks with one owner: +select * from t1 where pk=2510 lock in share mode; +pk a +# DEFAULT: exclusive locks on 0-10: +insert into t1 select A.a, 0 from t0 A; +connection con1; +# CON1: exclusive locks on 2000-2010: +insert into t1 select 2000+A.a, 0 from t0 A; +# select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX2_ID 0000${indexnr}80000000 X +$cf_id $TRX2_ID 0000${indexnr}80000001 X +$cf_id $TRX2_ID 0000${indexnr}80000002 X +$cf_id $TRX2_ID 0000${indexnr}80000003 X +$cf_id $TRX2_ID 0000${indexnr}80000004 X +$cf_id $TRX2_ID 0000${indexnr}80000005 X +$cf_id $TRX2_ID 0000${indexnr}80000006 X +$cf_id $TRX2_ID 0000${indexnr}80000007 X +$cf_id $TRX2_ID 0000${indexnr}80000008 X +$cf_id $TRX2_ID 0000${indexnr}80000009 X +$cf_id $TRX1_ID 0000${indexnr}800003e9 S +$cf_id $TRX2_ID 0000${indexnr}800003e9 S +$cf_id $TRX1_ID 0000${indexnr}8000044c S +$cf_id $TRX2_ID 0000${indexnr}8000044c S +$cf_id $TRX1_ID 0000${indexnr}800004b0 S +$cf_id $TRX2_ID 0000${indexnr}800004b0 S +$cf_id $TRX1_ID 0000${indexnr}800007d0 X +$cf_id $TRX1_ID 0000${indexnr}800007d1 X +$cf_id $TRX1_ID 0000${indexnr}800007d2 X +$cf_id $TRX1_ID 0000${indexnr}800007d3 X +$cf_id $TRX1_ID 0000${indexnr}800007d4 X +$cf_id $TRX1_ID 0000${indexnr}800007d5 X +$cf_id $TRX1_ID 0000${indexnr}800007d6 X +$cf_id $TRX1_ID 0000${indexnr}800007d7 X +$cf_id $TRX1_ID 0000${indexnr}800007d8 X +$cf_id $TRX1_ID 0000${indexnr}800007d9 X +$cf_id $TRX1_ID 0000${indexnr}800009c4 S +$cf_id $TRX2_ID 0000${indexnr}800009ce S +connection default; +show status like 'rocksdb_locktree_current_lock_memory'; +Variable_name Value +rocksdb_locktree_current_lock_memory 7896 +set @save_mlm= @@rocksdb_max_lock_memory; +# Set the limit to cause lock escalation: +set @cur_mem_usage= (select +variable_value +from +information_schema.GLOBAL_STATUS +where +variable_name='rocksdb_locktree_current_lock_memory'); +set global rocksdb_max_lock_memory = cast(@cur_mem_usage+4 as SIGNED); +connection con1; +insert into t1 select 3000+A.a, 0 from t0 A; +# select * from information_schema.rocksdb_locks; # With replacements by select_from_rocksdb_locks.inc +COLUMN_FAMILY_ID TRANSACTION_ID KEY mode +$cf_id $TRX2_ID 0000${indexnr}80000000 - 0000${indexnr}80000009 X +$cf_id $TRX1_ID 0000${indexnr}800003e9 S +$cf_id $TRX2_ID 0000${indexnr}800003e9 S +$cf_id $TRX1_ID 0000${indexnr}8000044c S +$cf_id $TRX2_ID 0000${indexnr}8000044c S +$cf_id $TRX1_ID 0000${indexnr}800004b0 S +$cf_id $TRX2_ID 0000${indexnr}800004b0 S +$cf_id $TRX1_ID 0000${indexnr}800007d0 - 0000${indexnr}800007d9 X +$cf_id $TRX1_ID 0000${indexnr}800009c4 S +$cf_id $TRX2_ID 0000${indexnr}800009ce S +$cf_id $TRX1_ID 0000${indexnr}80000bb8 X +$cf_id $TRX1_ID 0000${indexnr}80000bb9 X +$cf_id $TRX1_ID 0000${indexnr}80000bba X +$cf_id $TRX1_ID 0000${indexnr}80000bbb X +$cf_id $TRX1_ID 0000${indexnr}80000bbc X +$cf_id $TRX1_ID 0000${indexnr}80000bbd X +$cf_id $TRX1_ID 0000${indexnr}80000bbe X +$cf_id $TRX1_ID 0000${indexnr}80000bbf X +$cf_id $TRX1_ID 0000${indexnr}80000bc0 X +$cf_id $TRX1_ID 0000${indexnr}80000bc1 X +connection con1; +rollback; +connection default; +rollback; +disconnect con1; +set global rocksdb_max_lock_memory= cast(@save_mlm as SIGNED); +drop table t0, t1; diff --git a/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test index 52118aa343f..abc437742bb 100644 --- a/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test +++ b/mysql-test/suite/rocksdb/t/range_locking_shared_locks.test @@ -110,9 +110,6 @@ select * from t1 where a between 2 and 5 lock in share mode; select * from t1 where a between 2 and 5 lock in share mode; let $TRX1_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; ---echo # TODO: the following prints an X lock on the range, because GetRangeLock API ---echo # currently only supports write locks: - --source suite/rocksdb/include/select_from_rocksdb_locks.inc rollback; @@ -121,3 +118,85 @@ disconnect con1; drop table t0,t1; +--echo # +--echo # Test shared point locks and lock escalation +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int primary key, + a int +) engine=rocksdb; + +insert into t1 +select 1000 + 100*A.a + 10*B.a + C.a, 12345 from t0 A, t0 B, t0 C; + +show status like 'rocksdb_locktree_current_lock_memory'; + +connect (con1,localhost,root,,); +connection con1; + +begin; +--echo # CON1: get some shared locks +select * from t1 where pk=1001 lock in share mode; +select * from t1 where pk=1100 lock in share mode; +select * from t1 where pk=1200 lock in share mode; + +select * from t1 where pk=2500 lock in share mode; +let $TRX1_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; + +connection default; +begin; +--echo # DEFAULT: get the same locks so we have locks with multiple owners +select * from t1 where pk=1001 lock in share mode; +select * from t1 where pk=1100 lock in share mode; +select * from t1 where pk=1200 lock in share mode; + +--echo # DEFAULT: get shared locks with one owner: +select * from t1 where pk=2510 lock in share mode; +let $TRX2_ID=`select transaction_id from information_schema.rocksdb_trx where thread_id=connection_id()`; + + +--echo # DEFAULT: exclusive locks on 0-10: +insert into t1 select A.a, 0 from t0 A; + +connection con1; +--echo # CON1: exclusive locks on 2000-2010: +insert into t1 select 2000+A.a, 0 from t0 A; + +let $order_by_rowkey=1; +#select * from information_schema.rocksdb_locks; +--source suite/rocksdb/include/select_from_rocksdb_locks.inc + +connection default; +show status like 'rocksdb_locktree_current_lock_memory'; +set @save_mlm= @@rocksdb_max_lock_memory; + +--echo # Set the limit to cause lock escalation: +set @cur_mem_usage= (select + variable_value + from + information_schema.GLOBAL_STATUS + where + variable_name='rocksdb_locktree_current_lock_memory'); + +set global rocksdb_max_lock_memory = cast(@cur_mem_usage+4 as SIGNED); + +connection con1; +insert into t1 select 3000+A.a, 0 from t0 A; + +#select * from information_schema.rocksdb_locks; +--source suite/rocksdb/include/select_from_rocksdb_locks.inc + +connection con1; +rollback; +connection default; +rollback; + +disconnect con1; +set global rocksdb_max_lock_memory= cast(@save_mlm as SIGNED); + +drop table t0, t1; + + diff --git a/rocksdb b/rocksdb index 2f0ee897552..ae4869e880a 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit 2f0ee897552bb4a8aa66b933c0d6f8529a82e2e8 +Subproject commit ae4869e880a4118bd912fafe7fbeb6f464c4d893 diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index af6bc90ede7..c7a05f581a3 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -487,6 +487,10 @@ static void rocksdb_set_max_latest_deadlocks(THD *thd, struct st_mysql_sys_var *var, void *var_ptr, const void *save); +static void rocksdb_set_max_lock_memory(THD *thd, + struct st_mysql_sys_var *var, + void *var_ptr, const void *save); + static void rdb_set_collation_exception_list(const char *exception_list); static void rocksdb_set_collation_exception_list(THD *thd, struct st_mysql_sys_var *var, @@ -595,7 +599,7 @@ static uint32_t rocksdb_max_manual_compactions = 0; // (note that this is different from rocksdb_max_row_locks as // that one is a hard per-thread count limit, and this one is a // global memory limit) -static ulong rocksdb_max_lock_memory; +static ulonglong rocksdb_max_lock_memory; static my_bool rocksdb_use_range_locking = 0; static std::shared_ptr<rocksdb::RangeLockMgrHandle> range_lock_mgr; @@ -980,6 +984,13 @@ static MYSQL_SYSVAR_UINT(max_latest_deadlocks, rocksdb_max_latest_deadlocks, nullptr, rocksdb_set_max_latest_deadlocks, rocksdb::kInitialMaxDeadlocks, 0, UINT32_MAX, 0); +static MYSQL_SYSVAR_ULONGLONG(max_lock_memory, rocksdb_max_lock_memory, + PLUGIN_VAR_RQCMDARG, + "Range-locking mode: Maximum amount of memory " + "that locks from all transactions can use at a time", + nullptr, rocksdb_set_max_lock_memory, + /*initial*/1073741824, 0, UINT64_MAX, 0); + static MYSQL_SYSVAR_ENUM( info_log_level, rocksdb_info_log_level, PLUGIN_VAR_RQCMDARG, "Filter level for info logs to be written mysqld error log. " @@ -1715,13 +1726,6 @@ static MYSQL_SYSVAR_BOOL(use_range_locking, rocksdb_use_range_locking, "Use Range Locking (NEW, incomplete yet)", nullptr, nullptr, FALSE); -static MYSQL_SYSVAR_ULONG( - max_lock_memory, rocksdb_max_lock_memory, - PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY, - "Max Lock Memory when using Range Locking (like in TokuDB)", - nullptr, nullptr, 1024*1024*1024 /* default value */, 1024 /* min value */, - ULONG_MAX /* max value */, 0); - static const int ROCKSDB_ASSUMED_KEY_VALUE_DISK_SIZE = 100; static struct st_mysql_sys_var *rocksdb_system_variables[] = { @@ -5006,7 +5010,7 @@ static int rocksdb_init_func(void *const p) { { range_lock_mgr->set_max_lock_memory(rocksdb_max_lock_memory); sql_print_information("RocksDB: USING NEW RANGE LOCKING"); - sql_print_information("RocksDB: Max lock memory=%lu", rocksdb_max_lock_memory); + sql_print_information("RocksDB: Max lock memory=%llu", rocksdb_max_lock_memory); } else sql_print_information("RocksDB: USING POINT LOCKING"); @@ -12932,10 +12936,13 @@ static void show_rocksdb_stall_vars(THD *thd, SHOW_VAR *var, char *buff) { // psergey: lock tree escalation count status variable. // static longlong rocksdb_locktree_escalation_count=1234; +static longlong rocksdb_locktree_current_lock_memory=0; static SHOW_VAR rocksdb_locktree_status_variables[] = { DEF_STATUS_VAR_FUNC("escalation_count", &rocksdb_locktree_escalation_count, SHOW_LONGLONG), + DEF_STATUS_VAR_FUNC("current_lock_memory", + &rocksdb_locktree_current_lock_memory, SHOW_LONGLONG), // end of the array marker {NullS, NullS, SHOW_LONG}}; @@ -12946,7 +12953,9 @@ static void show_rocksdb_locktree_vars(THD *thd, SHOW_VAR *var, char *buff) { var->type = SHOW_ARRAY; if (range_lock_mgr) { - rocksdb_locktree_escalation_count= range_lock_mgr->get_escalation_count(); + auto status = range_lock_mgr->GetStatus(); + rocksdb_locktree_escalation_count = status.escalation_count; + rocksdb_locktree_current_lock_memory = status.current_lock_memory; var->value = reinterpret_cast<char *>(&rocksdb_locktree_status_variables); } else @@ -13614,6 +13623,23 @@ void rocksdb_set_delayed_write_rate(THD *thd, struct st_mysql_sys_var *var, RDB_MUTEX_UNLOCK_CHECK(rdb_sysvars_mutex); } +void rocksdb_set_max_lock_memory(THD *thd, struct st_mysql_sys_var *var, + void *var_ptr, const void *save) { + const uint64_t new_val = *static_cast<const uint64_t *>(save); + if (rocksdb_max_lock_memory != new_val) { + if (range_lock_mgr->set_max_lock_memory(new_val)) { + /* NO_LINT_DEBUG */ + sql_print_warning("MyRocks: failed to set max_lock_memory"); + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_ERROR_WHEN_EXECUTING_COMMAND, + "Cannot set max_lock_memory to size below currently used"); + } else { + // Succeeded + rocksdb_max_lock_memory = new_val; + } + } +} + void rocksdb_set_max_latest_deadlocks(THD *thd, struct st_mysql_sys_var *var, void *var_ptr, const void *save) { RDB_MUTEX_LOCK_CHECK(rdb_sysvars_mutex);

1 0

[Commits] ae4869e88: Range Locking: shared point lock support: support lock escalations
by Sergei Petrunia 10 Jun '19

10 Jun '19

revision-id: ae4869e880a4118bd912fafe7fbeb6f464c4d893 (v5.8-1043-gae4869e88) parent(s): 2f0ee897552bb4a8aa66b933c0d6f8529a82e2e8 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-06-10 17:16:12 +0300 message: Range Locking: shared point lock support: support lock escalations - Make Lock Escalation keep shared locks. Shared locks are not collapsed with other kinds of locks. - Replace RangeLockMgrHandle::get_escalation_count() with GetStatus() which also reports amount of memory used for Range Locking (and there is more data we could report through this) - Initialize LTM_STATUS_S::m_initialized. --- include/rocksdb/utilities/transaction_db.h | 9 ++- .../transactions/range_locking/ft/ft-status.h | 2 +- .../range_locking/locktree/locktree.cc | 78 ++++++++++++++++------ .../range_locking/locktree/treenode.cc | 10 +++ .../transactions/range_locking/locktree/treenode.h | 11 --- utilities/transactions/transaction_lock_mgr.cc | 18 ++--- utilities/transactions/transaction_lock_mgr.h | 6 +- 7 files changed, 89 insertions(+), 45 deletions(-) diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h index 7ebac3e06..7444cb5f2 100644 --- a/include/rocksdb/utilities/transaction_db.h +++ b/include/rocksdb/utilities/transaction_db.h @@ -38,7 +38,14 @@ const uint32_t kInitialMaxDeadlocks = 5; class RangeLockMgrHandle { public: virtual int set_max_lock_memory(size_t max_lock_memory) = 0; - virtual uint64_t get_escalation_count() = 0; + + class Counters { + public: + uint64_t escalation_count; + uint64_t current_lock_memory; + }; + + virtual Counters GetStatus() = 0; virtual ~RangeLockMgrHandle() {}; }; diff --git a/utilities/transactions/range_locking/ft/ft-status.h b/utilities/transactions/range_locking/ft/ft-status.h index 25051f1ed..242964f0b 100644 --- a/utilities/transactions/range_locking/ft/ft-status.h +++ b/utilities/transactions/range_locking/ft/ft-status.h @@ -80,7 +80,7 @@ public: TOKU_ENGINE_STATUS_ROW_S status[LTM_STATUS_NUM_ROWS]; private: - bool m_initialized; + bool m_initialized = false; }; typedef LTM_STATUS_S* LTM_STATUS; extern LTM_STATUS_S ltm_status; diff --git a/utilities/transactions/range_locking/locktree/locktree.cc b/utilities/transactions/range_locking/locktree/locktree.cc index 0e5f7c307..00ce5aace 100644 --- a/utilities/transactions/range_locking/locktree/locktree.cc +++ b/utilities/transactions/range_locking/locktree/locktree.cc @@ -742,11 +742,16 @@ static int extract_first_n_row_locks(concurrent_tree::locked_keyrange *lkr, int num_to_extract; row_lock *row_locks; bool fn(const keyrange &range, TXNID txnid, bool is_shared, TxnidVector *owners) { - // psergey-todo: multiple owners! if (num_extracted < num_to_extract) { row_lock lock; lock.range.create_copy(range); lock.txnid = txnid; + lock.is_shared= is_shared; + // deep-copy the set of owners: + if (owners) + lock.owners = new TxnidVector(*owners); + else + lock.owners = nullptr; row_locks[num_extracted++] = lock; return true; } else { @@ -834,38 +839,60 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca // through them and merge adjacent locks with the same txnid into // one dominating lock and save it to a set of escalated locks. // - // first, find the index of the next row lock with a different txnid + // first, find the index of the next row lock that + // - belongs to a different txnid, or + // - belongs to several txnids, or + // - is a shared lock (we could potentially merge those but + // currently we don't) int next_txnid_index = current_index + 1; + while (next_txnid_index < num_extracted && - extracted_buf[current_index].txnid == extracted_buf[next_txnid_index].txnid) { + (extracted_buf[current_index].txnid == + extracted_buf[next_txnid_index].txnid) && + !extracted_buf[next_txnid_index].is_shared && + !extracted_buf[next_txnid_index].owners) { next_txnid_index++; } // Create an escalated range for the current txnid that dominates // each range between the current indext and the next txnid's index. - const TXNID current_txnid = extracted_buf[current_index].txnid; + //const TXNID current_txnid = extracted_buf[current_index].txnid; const DBT *escalated_left_key = extracted_buf[current_index].range.get_left_key(); const DBT *escalated_right_key = extracted_buf[next_txnid_index - 1].range.get_right_key(); // Try to find a range buffer for the current txnid. Create one if it doesn't exist. // Then, append the new escalated range to the buffer. - uint32_t idx; - struct txnid_range_buffer *existing_range_buffer; - int r = range_buffers.find_zero<TXNID, txnid_range_buffer::find_by_txnid>( - current_txnid, - &existing_range_buffer, - &idx - ); - if (r == DB_NOTFOUND) { - struct txnid_range_buffer *XMALLOC(new_range_buffer); - new_range_buffer->txnid = current_txnid; - new_range_buffer->buffer.create(); - new_range_buffer->buffer.append(escalated_left_key, escalated_right_key); - range_buffers.insert_at(new_range_buffer, idx); - } else { - invariant_zero(r); - invariant(existing_range_buffer->txnid == current_txnid); - existing_range_buffer->buffer.append(escalated_left_key, escalated_right_key); + // (If a lock is shared by multiple txnids, append it each of txnid's lists) + TxnidVector *owners_ptr; + TxnidVector singleton_owner; + if (extracted_buf[current_index].owners) + owners_ptr = extracted_buf[current_index].owners; + else { + singleton_owner.insert(extracted_buf[current_index].txnid); + owners_ptr = &singleton_owner; + } + + for (auto cur_txnid : *owners_ptr ) { + uint32_t idx; + struct txnid_range_buffer *existing_range_buffer; + int r = range_buffers.find_zero<TXNID, txnid_range_buffer::find_by_txnid>( + cur_txnid, + &existing_range_buffer, + &idx + ); + if (r == DB_NOTFOUND) { + struct txnid_range_buffer *XMALLOC(new_range_buffer); + new_range_buffer->txnid = cur_txnid; + new_range_buffer->buffer.create(); + new_range_buffer->buffer.append(escalated_left_key, escalated_right_key, + !extracted_buf[current_index].is_shared); + range_buffers.insert_at(new_range_buffer, idx); + } else { + invariant_zero(r); + invariant(existing_range_buffer->txnid == cur_txnid); + existing_range_buffer->buffer.append(escalated_left_key, escalated_right_key, + !extracted_buf[current_index].is_shared); + } } current_index = next_txnid_index; @@ -873,6 +900,7 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca // destroy the ranges copied during the extraction for (int i = 0; i < num_extracted; i++) { + delete extracted_buf[i].owners; extracted_buf[i].range.destroy(); } } @@ -880,6 +908,12 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca // Rebuild the locktree from each range in each range buffer, // then notify higher layers that the txnid's locks have changed. + // + // (shared locks: if a lock was initially shared between transactions TRX1, + // TRX2, etc, we will now try to acquire it acting on behalf on TRX1, on + // TRX2, etc. This will succeed and an identical shared lock will be + // constructed) + invariant(m_rangetree->is_empty()); const size_t num_range_buffers = range_buffers.size(); for (size_t i = 0; i < num_range_buffers; i++) { @@ -894,7 +928,7 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, void *after_esca keyrange range; range.create(rec.get_left_key(), rec.get_right_key()); row_lock lock = { .range = range, .txnid = current_txnid, - .is_shared= false, // psergey-todo: SharedLockEscalation + .is_shared= !rec.get_exclusive_flag(), .owners= nullptr }; insert_row_lock_into_tree(&lkr, lock, m_mgr); iter.next(); diff --git a/utilities/transactions/range_locking/locktree/treenode.cc b/utilities/transactions/range_locking/locktree/treenode.cc index f44918a1b..5bf349749 100644 --- a/utilities/transactions/range_locking/locktree/treenode.cc +++ b/utilities/transactions/range_locking/locktree/treenode.cc @@ -134,6 +134,16 @@ void treenode::swap_in_place(treenode *node1, treenode *node2) { node2->m_is_shared= tmp_is_shared; } +void treenode::add_shared_owner(TXNID txnid) { + assert(m_is_shared); + if (m_txnid != TXNID_SHARED) { + m_owners= new TxnidVector; + m_owners->insert(m_txnid); + m_txnid= TXNID_SHARED; + } + m_owners->insert(txnid); +} + void treenode::free(treenode *node) { // destroy the range, freeing any copied keys node->m_range.destroy(); diff --git a/utilities/transactions/range_locking/locktree/treenode.h b/utilities/transactions/range_locking/locktree/treenode.h index 6b082acc4..f23324f03 100644 --- a/utilities/transactions/range_locking/locktree/treenode.h +++ b/utilities/transactions/range_locking/locktree/treenode.h @@ -167,17 +167,6 @@ private: // it has at the time of destruction. keyrange m_range; - void add_shared_owner(TXNID txnid) - { - assert(m_is_shared); - if (m_txnid != TXNID_SHARED) - { - m_owners= new TxnidVector; - m_owners->insert(m_txnid); - m_txnid= TXNID_SHARED; - } - m_owners->insert(txnid); - } void remove_shared_owner(TXNID txnid); bool has_multiple_owners() { return (m_txnid == TXNID_SHARED); } diff --git a/utilities/transactions/transaction_lock_mgr.cc b/utilities/transactions/transaction_lock_mgr.cc index 34144c2af..564cd8600 100644 --- a/utilities/transactions/transaction_lock_mgr.cc +++ b/utilities/transactions/transaction_lock_mgr.cc @@ -1154,25 +1154,25 @@ RangeLockMgr::~RangeLockMgr() { ltm_.destroy(); } -uint64_t RangeLockMgr::get_escalation_count() { +RangeLockMgrHandle::Counters RangeLockMgr::GetStatus() { LTM_STATUS_S ltm_status_test; ltm_.get_status(&ltm_status_test); + Counters res; // Searching status variable by its string name is how Toku's unit tests // do it (why didn't they make LTM_ESCALATION_COUNT constant visible?) - TOKU_ENGINE_STATUS_ROW key_status = NULL; // lookup keyname in status - for (int i = 0; ; i++) { + for (int i = 0; i < LTM_STATUS_S::LTM_STATUS_NUM_ROWS; i++) { TOKU_ENGINE_STATUS_ROW status = &ltm_status_test.status[i]; - if (status->keyname == NULL) - break; if (strcmp(status->keyname, "LTM_ESCALATION_COUNT") == 0) { - key_status = status; - break; + res.escalation_count = status->value.num; + continue; + } + if (strcmp(status->keyname, "LTM_SIZE_CURRENT") == 0) { + res.current_lock_memory = status->value.num; } } - assert(key_status); - return key_status->value.num; + return res; } void RangeLockMgr::AddColumnFamily(const ColumnFamilyHandle *cfh) { diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h index 608d6f34f..6c8e0638c 100644 --- a/utilities/transactions/transaction_lock_mgr.h +++ b/utilities/transactions/transaction_lock_mgr.h @@ -239,7 +239,11 @@ class RangeLockMgr : return ltm_.set_max_lock_memory(max_lock_memory); } - uint64_t get_escalation_count() override; + size_t get_max_lock_memory() { + return ltm_.get_max_lock_memory(); + } + + Counters GetStatus() override; LockStatusData GetLockStatusData() override;

1 0

[Commits] f04edf4: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 09 Jun '19

09 Jun '19

revision-id: f04edf4b68f6b9fd2722dca289a675266ec99a7a (mariadb-5.5.64-13-gf04edf4) parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-09 10:39:52 -0700 message: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)' Handling of top level conjuncts in WHERE whose used_tables() contained RAND_TABLE_BIT in the function make_join_select() was incorrect. As a result if such a conjunct referred to fields non of which belonged to the last joined table it was pushed twice. (This could be seen for a test case from subselect.test whose output was changed after this patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for the query from this test case we clearly see that one of the conjuncts is pushed twice.) This fact by itself was not good. Besides, if such a conjunct was pushed to a table that was the result of materialization of a semi-join the query could return a wrong result set. In particular we could watch it for queries with semi-join subqueries whose left parts used stored functions without "deterministic' specifier. --- mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_mat.result | 89 +++++++++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_scache.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- mysql-test/r/subselect_sj_mat.result | 89 +++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 75 ++++++++++++++++++++++++++ sql/sql_select.cc | 87 +++++++++++++++++++++++++----- 9 files changed, 331 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cdedc02..2999f4c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index efc348a..0e10800 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2453,6 +2453,95 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a s +3 paul 1 1 songwriter 1 +4 art 1 1 songwriter 1 +1 mrs 2 2 song character 2 +explain extended select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a729129..5c21e74 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index c41fa1b..8ab1f1b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1c18135..f0afa7e 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 89c6712..0d5fbc3 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index fd9435e..cb4b630 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2493,4 +2493,93 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a s +3 paul 1 1 songwriter 1 +4 art 1 1 songwriter 1 +1 mrs 2 2 song character 2 +explain extended select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index c82c1e7..9c3788a 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2230,4 +2230,79 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; + + +--echo # +--echo # MDEV-19580: function invocation in the left part of IN subquery +--echo # + +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); + +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); + +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); + +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); + +delimiter $$; + +create function f1(who int, dt date) returns int +deterministic +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +create function f2(who int, dt date) returns int +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +delimiter ;$$ + +--echo # Deterministic function in left part of IN subquery: semi-join is OK + +let $q1= +select * from t1 + left join t4 on t1.b = t4.id + where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q1; +eval explain extended $q1; + +--echo # Non-deterministic function in left part of IN subq: semi-join is OK + +let $q2= +select * from t1 + left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q2; +eval explain extended $q2; + +let $q3= +select t1.*, t4.*, + (select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s + from t1 left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q3; +eval explain extended $q3; + +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; + --echo # End of 5.5 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 37f8292..d7ff92a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond, table_map used_table, int join_tab_idx_arg, bool exclude_expensive_cond, - bool retain_ref_cond); + bool retain_ref_cond, + bool is_top_and_level); static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); @@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) RAND_TABLE_BIT; } - /* - Following force including random expression in last table condition. - It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 - */ - if (tab == join->join_tab + last_top_base_tab_idx) - current_map|= RAND_TABLE_BIT; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && @@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { tmp= make_cond_for_table(thd, cond, used_tables, current_map, i, FALSE, FALSE); + if (tab == join->join_tab + last_top_base_tab_idx) + { + /* + This pushes conjunctive conditions of WHERE condition such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, cond, used_tables, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp, rand_cond); + } } /* Add conditions added by add_not_null_conds(). */ if (tab->select_cond) @@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) psergey: have put the -1 below. It's bad, will need to fix it. */ COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2, - current_map, /*(tab - first_tab)*/ -1, - FALSE, FALSE); + current_map, + /*(tab - first_tab)*/ -1, + FALSE, FALSE); + if (tab == last_tab) + { + /* + This pushes conjunctive conditions of ON expression of an outer + join such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp_cond, rand_cond); + } bool is_sjm_lookup_tab= FALSE; if (tab->bush_children) { @@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables, return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, true); } @@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, int join_tab_idx_arg, bool exclude_expensive_cond __attribute__ ((unused)), - bool retain_ref_cond) + bool retain_ref_cond, + bool is_top_and_level) { + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + if (used_table && !(cond->used_tables() & used_table)) return (COND*) 0; // Already checked @@ -18852,11 +18880,28 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, Item *item; while ((item=li++)) { + /* + Special handling of top level conjuncts with RAND_TABLE_BIT: + if such a conjunct contains a reference to a field that is not + an outer field then it is pushed to the corresponding table by + the same rule as all other conjuncts. Otherwise, if the conjunct + is used in WHERE is is pushed to the last joined table, if is it + is used in ON condition of an outer join it is pushed into the + last inner table of the outer join. Such conjuncts are pushed in + a call of make_cond_for_table_from_pred() with the + parameter 'used_table' equal to PSEUDO_TABLE_BITS. + */ + if (is_top_and_level && used_table == rand_table_bit && + (item->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit) + { + /* The conjunct with RAND_TABLE_BIT has been allready pushed */ + continue; + } Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, tables, used_table, - join_tab_idx_arg, + join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (fix) new_cond->argument_list()->push_back(fix); } @@ -18880,6 +18925,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } else { // Or list + if (is_top_and_level && used_table == rand_table_bit && + (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + Item_cond_or *new_cond=new Item_cond_or; if (!new_cond) return (COND*) 0; // OOM /* purecov: inspected */ @@ -18891,7 +18943,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, tables, 0L, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); @@ -18908,6 +18960,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } } + if (is_top_and_level && used_table == rand_table_bit && + (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + /* Because the following test takes a while and it can be done table_count times, we mark each item that we have examined with the result

1 0

[Commits] 5d06edfb261: MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
by Sergei Petrunia 07 Jun '19

07 Jun '19

revision-id: 5d06edfb2616ab0b4b067580c281afbbef8fdc74 (mariadb-10.2.24-69-g5d06edfb261) parent(s): 9b22354a594570e23cc675d90836743ce7a3ba1c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-06-08 02:28:29 +0300 message: MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON Make it visible --- mysql-test/r/subselect_no_semijoin.result | 55 +++++++++++++++++++++++++++++++ mysql-test/t/subselect_no_semijoin.test | 23 +++++++++++++ sql/sql_explain.cc | 5 +++ sql/sql_explain.h | 1 + sql/sql_select.cc | 1 + 5 files changed, 85 insertions(+) diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index a0625246e2b..55d408c54fc 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7291,5 +7291,60 @@ pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; # End of 10.2 tests +# +# MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON +# +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1),(5); +CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (4),(5); +SET @tmp19714=@@optimizer_switch; +SET optimizer_switch='subquery_cache=off'; +explain format=json +SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "pseudo_bits_condition": "1 = t1.a or <in_optimizer>(1,<exists>(subquery#3))", + "table": { + "table_name": "t2", + "access_type": "system", + "rows": 1, + "filtered": 100 + }, + "subqueries": [ + { + "query_block": { + "select_id": 3, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "1 = t3.c" + } + } + } + ] + } + } + ] + } +} +SET optimizer_switch=@tmp19714; +drop table t1,t2,t3; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test index 46791667173..eda95b93503 100644 --- a/mysql-test/t/subselect_no_semijoin.test +++ b/mysql-test/t/subselect_no_semijoin.test @@ -8,5 +8,28 @@ set @join_cache_level_for_subselect_test=@@join_cache_level; --source t/subselect.test +--echo # +--echo # MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1),(5); + +# t2 must be MyISAM or Aria and contain 1 row +CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); + +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (4),(5); + +SET @tmp19714=@@optimizer_switch; +SET optimizer_switch='subquery_cache=off'; + +explain format=json +SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; + +SET optimizer_switch=@tmp19714; + +drop table t1,t2,t3; + set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 1ab10fb3c37..3ebd44dc0bb 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -857,6 +857,11 @@ void Explain_select::print_explain_json(Explain_query *query, writer->add_member("outer_ref_condition"); write_item(writer, outer_ref_cond); } + if (pseudo_bits_cond) + { + writer->add_member("pseudo_bits_condition"); + write_item(writer, pseudo_bits_cond); + } /* we do not print HAVING which always evaluates to TRUE */ if (having || (having_value == Item::COND_FALSE)) diff --git a/sql/sql_explain.h b/sql/sql_explain.h index 3ca816477fc..08af84b3562 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -232,6 +232,7 @@ class Explain_select : public Explain_basic_join /* Expensive constant condition */ Item *exec_const_cond; Item *outer_ref_cond; + Item *pseudo_bits_cond; /* HAVING condition */ Item *having; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 182e913c16c..0ee3735c464 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25025,6 +25025,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, xpl_sel->exec_const_cond= exec_const_cond; xpl_sel->outer_ref_cond= outer_ref_cond; + xpl_sel->pseudo_bits_cond= pseudo_bits_cond; if (tmp_having) xpl_sel->having= tmp_having; else

1 0