- commits - lists.mariadb.org

[Commits] 5bfc8128c83: MDEV-8874 Replication filters configured in my.cnf are ignored if slave reset and reconfigured
by sachin.setiya＠mariadb.com 12 Jun '19

12 Jun '19

revision-id: 5bfc8128c8381e9347589aa0e27cd4fbd66ef19d (mariadb-10.1.39-57-g5bfc8128c83) parent(s): 2e0fb93e6f3359ffe146f309e32c59922400b7c4 author: Sachin committer: Sachin timestamp: 2019-06-12 15:58:44 +0530 message: MDEV-8874 Replication filters configured in my.cnf are ignored if slave reset and reconfigured Don't delete the rpl_filter on RESET SLAVE. --- mysql-test/lib/My/Config.pm | 8 +- mysql-test/suite/multi_source/mdev-8874.cnf | 25 +++++ mysql-test/suite/multi_source/mdev-8874.result | 114 ++++++++++++++++++++ mysql-test/suite/multi_source/mdev-8874.test | 141 +++++++++++++++++++++++++ sql/rpl_mi.cc | 2 - 5 files changed, 287 insertions(+), 3 deletions(-) diff --git a/mysql-test/lib/My/Config.pm b/mysql-test/lib/My/Config.pm index 12647edf0a4..86bb7a5f961 100644 --- a/mysql-test/lib/My/Config.pm +++ b/mysql-test/lib/My/Config.pm @@ -327,7 +327,13 @@ sub new { # Skip comment next; } - + # Correctly process Replication Filter when they are defined + # with connection name. + elsif ( $line =~ /^([\w]+.[\w]+)\s*=\s*(.*)\s*/){ + my $option= $1; + my $value= $2; + $self->insert($group_name, $option, $value); + } else { croak "Unexpected line '$line' found in '$path'"; } diff --git a/mysql-test/suite/multi_source/mdev-8874.cnf b/mysql-test/suite/multi_source/mdev-8874.cnf new file mode 100644 index 00000000000..dc89db72772 --- /dev/null +++ b/mysql-test/suite/multi_source/mdev-8874.cnf @@ -0,0 +1,25 @@ +!include my.cnf + +[mysqld.1] +log-bin +log-slave-updates + +[mysqld.2] +log-bin +log-slave-updates + +[mysqld.3] +log-bin +log-slave-updates + +[mysqld.4] +server-id=4 +log-bin=server4-bin +log-slave-updates +m1.replicate_ignore_table='a.t1' +m2.replicate_ignore_table='b.t1' +replicate_ignore_table='c.t1' + +[ENV] +SERVER_MYPORT_4= @mysqld.4.port +SERVER_MYSOCK_4= @mysqld.4.socket diff --git a/mysql-test/suite/multi_source/mdev-8874.result b/mysql-test/suite/multi_source/mdev-8874.result new file mode 100644 index 00000000000..25185eed02a --- /dev/null +++ b/mysql-test/suite/multi_source/mdev-8874.result @@ -0,0 +1,114 @@ +create database a; +use a; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +create database b; +use b; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +create database c; +use c; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +change master 'm1' to master_port=MYPORT_1 , master_host='127.0.0.1', master_user='root'; +change master 'm2' to master_port=MYPORT_2 , master_host='127.0.0.1', master_user='root'; +change master to master_port=MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +set default_master_connection = 'm1'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +include/wait_for_slave_to_start.inc +set default_master_connection = ''; +include/wait_for_slave_to_start.inc +select @@global.'m1'.replicate_ignore_table; +@@global.'m1'.replicate_ignore_table +a.t1 +select @@global.'m2'.replicate_ignore_table; +@@global.'m2'.replicate_ignore_table +b.t1 +select @@global.replicate_ignore_table; +@@global.replicate_ignore_table +c.t1 +use a; +#No t1 table +show tables; +Tables_in_a +t2 +use b; +#No t1 table +show tables; +Tables_in_b +t2 +use c; +#No t1 table +show tables; +Tables_in_c +t2 +#TEST +STOP ALL SLAVES; +Warnings: +Note 1938 SLAVE 'm2' stopped +Note 1938 SLAVE '' stopped +Note 1938 SLAVE 'm1' stopped +RESET SLAVE 'm1' ALL ; +RESET SLAVE 'm2' ALL ; +RESET SLAVE ALL ; +drop database a; +drop database b; +drop database c; +change master 'm1' to master_port=MYPORT_1 , master_host='127.0.0.1', master_user='root'; +change master 'm2' to master_port=MYPORT_2 , master_host='127.0.0.1', master_user='root'; +change master to master_port=MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +Warnings: +Note 1937 SLAVE 'm2' started +Note 1937 SLAVE '' started +Note 1937 SLAVE 'm1' started +set default_master_connection = 'm1'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +include/wait_for_slave_to_start.inc +set default_master_connection = ''; +include/wait_for_slave_to_start.inc +#Replication Filter should be intact (t1 still not replicated) +select @@global.'m1'.replicate_ignore_table; +@@global.'m1'.replicate_ignore_table +a.t1 +select @@global.'m2'.replicate_ignore_table; +@@global.'m2'.replicate_ignore_table +b.t1 +select @@global.replicate_ignore_table; +@@global.replicate_ignore_table +c.t1 +use a; +#No t1 table +show tables; +Tables_in_a +t2 +use b; +#No t1 table +show tables; +Tables_in_b +t2 +use c; +#No t1 table +show tables; +Tables_in_c +t2 +#CleanUp +drop database a; +drop database b; +drop database c; +stop all slaves; +SET default_master_connection = "m1"; +include/wait_for_slave_to_stop.inc +SET default_master_connection = "m2"; +include/wait_for_slave_to_stop.inc +SET default_master_connection = ""; +include/wait_for_slave_to_stop.inc diff --git a/mysql-test/suite/multi_source/mdev-8874.test b/mysql-test/suite/multi_source/mdev-8874.test new file mode 100644 index 00000000000..d03c255b911 --- /dev/null +++ b/mysql-test/suite/multi_source/mdev-8874.test @@ -0,0 +1,141 @@ +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/have_debug.inc +# MDEV-8874 +# In Named Master slave connection if we do reset slave 'connection_name' ALL and then +# if we reconnect slave, replication filters are ignored. +# This patch fixes this issue. +--connect (server_1,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connect (server_2,127.0.0.1,root,,,$SERVER_MYPORT_2) +--connect (server_3,127.0.0.1,root,,,$SERVER_MYPORT_3) +--connect (server_4,127.0.0.1,root,,,$SERVER_MYPORT_4) + +--connection server_1 +create database a; +use a; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +--save_master_pos + +--connection server_2 +create database b; +use b; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +--save_master_pos + +--connection server_3 +create database c; +use c; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +--save_master_pos + +--connection server_4 +--disable_warnings +--replace_result $SERVER_MYPORT_1 MYPORT_1 +eval change master 'm1' to master_port=$SERVER_MYPORT_1 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_2 MYPORT_2 +eval change master 'm2' to master_port=$SERVER_MYPORT_2 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_3 MYPORT_3 +eval change master to master_port=$SERVER_MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +set default_master_connection = 'm1'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = ''; +--source include/wait_for_slave_to_start.inc +select @@global.'m1'.replicate_ignore_table; +select @@global.'m2'.replicate_ignore_table; +select @@global.replicate_ignore_table; + +--enable_warnings +--sync_with_master 0,'m1' +--sync_with_master 0,'m2' +--sync_with_master 0,'' +use a; +--echo #No t1 table +show tables; +use b; +--echo #No t1 table +show tables; +use c; +--echo #No t1 table +show tables; +--echo #TEST +STOP ALL SLAVES; +RESET SLAVE 'm1' ALL ; +RESET SLAVE 'm2' ALL ; +RESET SLAVE ALL ; +drop database a; +drop database b; +drop database c; +--replace_result $SERVER_MYPORT_1 MYPORT_1 +eval change master 'm1' to master_port=$SERVER_MYPORT_1 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_2 MYPORT_2 +eval change master 'm2' to master_port=$SERVER_MYPORT_2 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_3 MYPORT_3 +eval change master to master_port=$SERVER_MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +set default_master_connection = 'm1'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = ''; +--source include/wait_for_slave_to_start.inc +--sync_with_master 0,'m1' +--sync_with_master 0,'m2' +--sync_with_master 0,'' + +--echo #Replication Filter should be intact (t1 still not replicated) +select @@global.'m1'.replicate_ignore_table; +select @@global.'m2'.replicate_ignore_table; +select @@global.replicate_ignore_table; +use a; +--echo #No t1 table +show tables; +use b; +--echo #No t1 table +show tables; +use c; +--echo #No t1 table +show tables; + + +#--echo #restart the server +#--source include/restart_mysqld.inc + + +--echo #CleanUp +--connection server_1 +drop database a; +--save_master_pos + +--connection server_2 +drop database b; +--save_master_pos + +--connection server_3 +drop database c; +--save_master_pos + +--connection server_4 +--sync_with_master 0,'m1' +--sync_with_master 0,'m2' +--sync_with_master 0,'' +--disable_warnings +stop all slaves; +--enable_warnings +SET default_master_connection = "m1"; +--source include/wait_for_slave_to_stop.inc +SET default_master_connection = "m2"; +--source include/wait_for_slave_to_stop.inc +SET default_master_connection = ""; +--source include/wait_for_slave_to_stop.inc diff --git a/sql/rpl_mi.cc b/sql/rpl_mi.cc index 70e60b1d4ad..7aea89337a7 100644 --- a/sql/rpl_mi.cc +++ b/sql/rpl_mi.cc @@ -122,8 +122,6 @@ Master_info::~Master_info() */ if (strncmp(connection_name.str, STRING_WITH_LEN("wsrep"))) #endif - rpl_filters.delete_element(connection_name.str, connection_name.length, - (void (*)(const char*, uchar*)) free_rpl_filter); my_free(connection_name.str); delete_dynamic(&ignore_server_ids); mysql_mutex_destroy(&run_lock);

2 1

[Commits] 321a43d9ac5: MDEV-8874 Replication filters configured in my.cnf are ignored if slave reset and reconfigured
by sachin.setiya＠mariadb.com 12 Jun '19

12 Jun '19

revision-id: 321a43d9ac586034790407dc519c72e90b3bff81 (mariadb-10.1.39-54-g321a43d9ac5) parent(s): b003b0c934cf6b59358e31144c4f69cf34622fb8 author: Sachin committer: Sachin timestamp: 2019-06-06 12:03:45 +0530 message: MDEV-8874 Replication filters configured in my.cnf are ignored if slave reset and reconfigured Don't delete the rpl_filter on RESET SLAVE. --- mysql-test/lib/My/Config.pm | 8 +- mysql-test/suite/multi_source/mdev-8874.cnf | 25 +++++ mysql-test/suite/multi_source/mdev-8874.result | 93 +++++++++++++++++++ mysql-test/suite/multi_source/mdev-8874.test | 123 +++++++++++++++++++++++++ sql/rpl_mi.cc | 2 - 5 files changed, 248 insertions(+), 3 deletions(-) diff --git a/mysql-test/lib/My/Config.pm b/mysql-test/lib/My/Config.pm index 12647edf0a4..349a397ed6d 100644 --- a/mysql-test/lib/My/Config.pm +++ b/mysql-test/lib/My/Config.pm @@ -327,7 +327,13 @@ sub new { # Skip comment next; } - + + # Replication Filter + elsif ( $line =~ /^([\w]+.[\w]+)\s*=\s*(.*)\s*/){ + my $option= $1; + my $value= $2; + $self->insert($group_name, $option, $value); + } else { croak "Unexpected line '$line' found in '$path'"; } diff --git a/mysql-test/suite/multi_source/mdev-8874.cnf b/mysql-test/suite/multi_source/mdev-8874.cnf new file mode 100644 index 00000000000..01da70cbaa0 --- /dev/null +++ b/mysql-test/suite/multi_source/mdev-8874.cnf @@ -0,0 +1,25 @@ +!include my.cnf + +[mysqld.1] +log-bin +log-slave-updates + +[mysqld.2] +log-bin +log-slave-updates + +[mysqld.3] +log-bin +log-slave-updates + +[mysqld.4] +server-id=4 +log-bin=server4-bin +log-slave-updates +m1.replicate_ignore_table='a.t1' +m2.replicate_ignore_table='b.t1' +replicate_ignore_table='c.t1' + +[ENV] +SERVER_MYPORT_4= @mysqld.4.port +SERVER_MYSOCK_4= @mysqld.4.socket diff --git a/mysql-test/suite/multi_source/mdev-8874.result b/mysql-test/suite/multi_source/mdev-8874.result new file mode 100644 index 00000000000..6fb364a3d2d --- /dev/null +++ b/mysql-test/suite/multi_source/mdev-8874.result @@ -0,0 +1,93 @@ +create database a; +use a; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +create database b; +use b; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +create database c; +use c; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +change master 'm1' to master_port=MYPORT_1 , master_host='127.0.0.1', master_user='root'; +change master 'm2' to master_port=MYPORT_2 , master_host='127.0.0.1', master_user='root'; +change master to master_port=MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +set default_master_connection = 'm1'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +include/wait_for_slave_to_start.inc +set default_master_connection = ''; +include/wait_for_slave_to_start.inc +use a; +show tables; +Tables_in_a +t2 +use b; +show tables; +Tables_in_b +t2 +use c; +show tables; +Tables_in_c +t2 +#TEST +STOP ALL SLAVES; +Warnings: +Note 1938 SLAVE 'm2' stopped +Note 1938 SLAVE '' stopped +Note 1938 SLAVE 'm1' stopped +RESET SLAVE 'm1' ALL ; +RESET SLAVE 'm2' ALL ; +RESET SLAVE ALL ; +drop database a; +drop database b; +drop database c; +change master 'm1' to master_port=MYPORT_1 , master_host='127.0.0.1', master_user='root'; +change master 'm2' to master_port=MYPORT_2 , master_host='127.0.0.1', master_user='root'; +change master to master_port=MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +Warnings: +Note 1937 SLAVE 'm2' started +Note 1937 SLAVE '' started +Note 1937 SLAVE 'm1' started +set default_master_connection = 'm1'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +include/wait_for_slave_to_start.inc +set default_master_connection = ''; +include/wait_for_slave_to_start.inc +use a; +show tables; +Tables_in_a +t2 +use b; +show tables; +Tables_in_b +t2 +use c; +show tables; +Tables_in_c +t2 +#CleanUp +drop database a; +drop database b; +drop database c; +stop all slaves; +Warnings: +Note 1938 SLAVE 'm2' stopped +Note 1938 SLAVE '' stopped +Note 1938 SLAVE 'm1' stopped +SET default_master_connection = "m1"; +include/wait_for_slave_to_stop.inc +SET default_master_connection = "m2"; +include/wait_for_slave_to_stop.inc +SET default_master_connection = ""; +include/wait_for_slave_to_stop.inc diff --git a/mysql-test/suite/multi_source/mdev-8874.test b/mysql-test/suite/multi_source/mdev-8874.test new file mode 100644 index 00000000000..b6d1aafe139 --- /dev/null +++ b/mysql-test/suite/multi_source/mdev-8874.test @@ -0,0 +1,123 @@ +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/have_debug.inc + +--connect (server_1,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connect (server_2,127.0.0.1,root,,,$SERVER_MYPORT_2) +--connect (server_3,127.0.0.1,root,,,$SERVER_MYPORT_3) +--connect (server_4,127.0.0.1,root,,,$SERVER_MYPORT_4) + +--connection server_1 +create database a; +use a; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +--save_master_pos + +--connection server_2 +create database b; +use b; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +--save_master_pos + +--connection server_3 +create database c; +use c; +create table t1(a int); +insert into t1 values(1); +create table t2(a int); +insert into t2 values(1); +--save_master_pos + +--connection server_4 +--disable_warnings +--replace_result $SERVER_MYPORT_1 MYPORT_1 +eval change master 'm1' to master_port=$SERVER_MYPORT_1 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_2 MYPORT_2 +eval change master 'm2' to master_port=$SERVER_MYPORT_2 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_3 MYPORT_3 +eval change master to master_port=$SERVER_MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +set default_master_connection = 'm1'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = ''; +--source include/wait_for_slave_to_start.inc + +--enable_warnings +--sync_with_master 0,'m1' +--sync_with_master 0,'m2' +--sync_with_master 0,'' +use a; +show tables; +use b; +show tables; +use c; +show tables; +--echo #TEST +STOP ALL SLAVES; +RESET SLAVE 'm1' ALL ; +RESET SLAVE 'm2' ALL ; +RESET SLAVE ALL ; +drop database a; +drop database b; +drop database c; +--replace_result $SERVER_MYPORT_1 MYPORT_1 +eval change master 'm1' to master_port=$SERVER_MYPORT_1 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_2 MYPORT_2 +eval change master 'm2' to master_port=$SERVER_MYPORT_2 , master_host='127.0.0.1', master_user='root'; +--replace_result $SERVER_MYPORT_3 MYPORT_3 +eval change master to master_port=$SERVER_MYPORT_3 , master_host='127.0.0.1', master_user='root'; +start all slaves; +set default_master_connection = 'm1'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = ''; +--source include/wait_for_slave_to_start.inc +--sync_with_master 0,'m1' +--sync_with_master 0,'m2' +--sync_with_master 0,'' + +use a; +show tables; +use b; +show tables; +use c; +show tables; + + +#--echo #restart the server +#--source include/restart_mysqld.inc + + +--echo #CleanUp +--connection server_1 +drop database a; +--save_master_pos + +--connection server_2 +drop database b; +--save_master_pos + +--connection server_3 +drop database c; +--save_master_pos + +--connection server_4 +--sync_with_master 0,'m1' +--sync_with_master 0,'m2' +--sync_with_master 0,'' +stop all slaves; +SET default_master_connection = "m1"; +--source include/wait_for_slave_to_stop.inc +SET default_master_connection = "m2"; +--source include/wait_for_slave_to_stop.inc +SET default_master_connection = ""; +--source include/wait_for_slave_to_stop.inc diff --git a/sql/rpl_mi.cc b/sql/rpl_mi.cc index 70e60b1d4ad..7aea89337a7 100644 --- a/sql/rpl_mi.cc +++ b/sql/rpl_mi.cc @@ -122,8 +122,6 @@ Master_info::~Master_info() */ if (strncmp(connection_name.str, STRING_WITH_LEN("wsrep"))) #endif - rpl_filters.delete_element(connection_name.str, connection_name.length, - (void (*)(const char*, uchar*)) free_rpl_filter); my_free(connection_name.str); delete_dynamic(&ignore_server_ids); mysql_mutex_destroy(&run_lock);

3 2

[Commits] 9ca54bd686f: MDEV-19705: Assertion `tmp >= 0' failed in best_access_path
by Varun 12 Jun '19

12 Jun '19

revision-id: 9ca54bd686f22000116df952841821a7831318f5 (mariadb-10.4.5-43-g9ca54bd686f) parent(s): 2fd82471aba9447e5490b24da5da89c33a21525e author: Varun committer: Varun timestamp: 2019-06-12 13:56:32 +0530 message: MDEV-19705: Assertion `tmp >= 0' failed in best_access_path The reason for hitting the assert is that rec_per_key estimates have some garbage value. So the solution to fix this would be for long unique keys to use use rec_per_key for only 1 keypart, that means rec_per_key[0] would have the estimate. --- mysql-test/main/long_unique.result | 17 ++++++++++++++++- mysql-test/main/long_unique.test | 14 ++++++++++++++ sql/table.cc | 4 +++- 3 files changed, 33 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result index 3843ff4aff0..03a63d8e7b5 100644 --- a/mysql-test/main/long_unique.result +++ b/mysql-test/main/long_unique.result @@ -1140,7 +1140,7 @@ t1 0 a 1 a A NULL NULL NULL YES HASH t1 0 a 2 c A NULL NULL NULL YES HASH t1 0 b 1 b A NULL NULL NULL YES HASH t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES BTREE +t1 0 e 1 e A NULL NULL NULL YES BTREE drop table t1; #visibility of db_row_hash create table t1 (a blob unique , b blob unique); @@ -1462,4 +1462,19 @@ t1 CREATE TABLE `t1` ( KEY `pk` (`pk`,`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# MDEV-19705: Assertion `tmp >= 0' failed in best_access_path +# +CREATE TABLE t1 (d varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a'),('q'); +CREATE TABLE t2 (f varchar(10), a2 datetime, b int, a1 varchar(1024), pk int NOT NULL, PRIMARY KEY (pk), UNIQUE KEY (f,a1,a2), KEY f2 (f(4),a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('aaa','1985-09-06',-163,'s',1),('bbb','1995-01-05',3,'pucaz',2),('ccc','0000-00-00',NULL,'help',3),('ddd',NULL,618,'v',4),('eee','1995-12-20',410,'m',5),('ffq','1976-06-12 20:02:56',NULL,'POKNC',6),('dddd','0000-00-00',-328,'hgsu',7); +explain +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,f,f2 PRIMARY 4 NULL 1 Using index condition +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +b +drop table t1,t2; set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/mysql-test/main/long_unique.test b/mysql-test/main/long_unique.test index a6bc68f54dc..c0bd77ca5c9 100644 --- a/mysql-test/main/long_unique.test +++ b/mysql-test/main/long_unique.test @@ -542,4 +542,18 @@ alter table t1 modify a varchar(1000); show create table t1; drop table t1; +--echo # +--echo # MDEV-19705: Assertion `tmp >= 0' failed in best_access_path +--echo # + +CREATE TABLE t1 (d varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a'),('q'); + +CREATE TABLE t2 (f varchar(10), a2 datetime, b int, a1 varchar(1024), pk int NOT NULL, PRIMARY KEY (pk), UNIQUE KEY (f,a1,a2), KEY f2 (f(4),a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('aaa','1985-09-06',-163,'s',1),('bbb','1995-01-05',3,'pucaz',2),('ccc','0000-00-00',NULL,'help',3),('ddd',NULL,618,'v',4),('eee','1995-12-20',410,'m',5),('ffq','1976-06-12 20:02:56',NULL,'POKNC',6),('dddd','0000-00-00',-328,'hgsu',7); +explain +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +SELECT t2.b FROM t1 JOIN t2 ON t1.d = t2.f WHERE t2.pk >= 20; +drop table t1,t2; + set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/sql/table.cc b/sql/table.cc index 699102885c2..d8739b75af5 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -800,7 +800,8 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end, { if (strpos + (new_frm_ver >= 1 ? 9 : 7) >= frm_image_end) return 1; - *rec_per_key++=0; + if (!(keyinfo->algorithm == HA_KEY_ALG_LONG_HASH)) + *rec_per_key++=0; key_part->fieldnr= (uint16) (uint2korr(strpos) & FIELD_NR_MASK); key_part->offset= (uint) uint2korr(strpos+2)-1; key_part->key_type= (uint) uint2korr(strpos+5); @@ -828,6 +829,7 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end, { keyinfo->key_length= HA_HASH_KEY_LENGTH_WITHOUT_NULL; key_part++; // reserved for the hash value + *rec_per_key++=0; } /*

1 0

[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