- developers - lists.mariadb.org

[Maria-developers] 4f9c5a8cf54: MDEV-24773: slave_compressed_protocol doesn't work properly with semi-sync replication
by sujatha 08 Feb '21

08 Feb '21

revision-id: 4f9c5a8cf545133c3d29c0de3f7e2e57564607f2 (mariadb-10.3.26-78-g4f9c5a8cf54) parent(s): 59eda73eff1a22ac0373d818bc802c05e82b5449 author: Sujatha committer: Sujatha timestamp: 2021-02-05 16:45:35 +0530 message: MDEV-24773: slave_compressed_protocol doesn't work properly with semi-sync replication Back port upstream fix commit 1800b015a1d487330f7b15f2020b887be348a66b Author: Venkatesh Duggirala <venkatesh.duggirala(a)oracle.com> Date: Fri Sep 8 20:29:22 2017 +0530 Bug#26027024 SLAVE_COMPRESSED_PROTOCOL DOESN'T WORK WITH SEMI-SYNC REPLICATION IN MYSQL-5.7 Analysis: In mysql-5.6, dump thread (the thread that is created on Master after Slave requested for a binlog dump) is also used to receive acknowledgements from the Slave and act on them accordingly. For performance reasons, a special thread called Ack Receiver thread is added in mysql-5.7 Semi synchronous replication plugin. This thread does not have special handling to receive acknowledgements if Slave has enabled compression in the protocol. Hence Master is unable to handle any slave if Slave_compressed_protocol is enabled on it. Fix: Enable compress flag on the communication channels if the Slave has Slave_compressed_protocol ON. --- .../rpl_semi_sync_slave_compressed_protocol.result | 19 ++++++++ ...l_semi_sync_slave_compressed_protocol-slave.opt | 1 + .../t/rpl_semi_sync_slave_compressed_protocol.test | 55 ++++++++++++++++++++++ sql/semisync_master_ack_receiver.cc | 5 ++ 4 files changed, 80 insertions(+) diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_slave_compressed_protocol.result b/mysql-test/suite/rpl/r/rpl_semi_sync_slave_compressed_protocol.result new file mode 100644 index 00000000000..86816652565 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_slave_compressed_protocol.result @@ -0,0 +1,19 @@ +include/master-slave.inc +[connection master] +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +connection slave; +include/stop_slave.inc +SET @@GLOBAL.rpl_semi_sync_slave_enabled = 1; +include/start_slave.inc +connection master; +CREATE TABLE t1 (i INT); +DROP TABLE t1; +include/rpl_sync.inc +include/assert_grep.inc [Check that there is no 'Read semi-sync reply magic number error' in error log.] +connection master; +SET @@GLOBAL. rpl_semi_sync_master_enabled = 0; +connection slave; +include/stop_slave.inc +SET @@GLOBAL. rpl_semi_sync_slave_enabled = 0; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_slave_compressed_protocol-slave.opt b/mysql-test/suite/rpl/t/rpl_semi_sync_slave_compressed_protocol-slave.opt new file mode 100644 index 00000000000..a1b687d691e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_slave_compressed_protocol-slave.opt @@ -0,0 +1 @@ +--slave_compressed_protocol diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_slave_compressed_protocol.test b/mysql-test/suite/rpl/t/rpl_semi_sync_slave_compressed_protocol.test new file mode 100644 index 00000000000..6343fb29ffe --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_slave_compressed_protocol.test @@ -0,0 +1,55 @@ +################################################################################ +# Bug#26027024 SLAVE_COMPRESSED_PROTOCOL DOESN'T WORK WITH SEMI-SYNC +# REPLICATION IN MYSQL-5.7 +# +# Steps to reproduce: +# 1) Set slave_compressed_protocol ON on Slave. +# 2) Do some sample work on Master +# 3) After the work is synced on Slave, check that there is no error +# (Read semi-sync reply magic number error) on Slave. +# 4) Cleanup +################################################################################ +# Test is independent of Binlog format. One of the three formats is enough +# for testing. Choosing 'Row' format. +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--let $sav_enabled_master=`SELECT @@GLOBAL.rpl_semi_sync_master_enabled ` +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; + +--connection slave +source include/stop_slave.inc; +--let $sav_enabled_slave=`SELECT @@GLOBAL.rpl_semi_sync_slave_enabled ` +SET @@GLOBAL.rpl_semi_sync_slave_enabled = 1; +source include/start_slave.inc; + +--connection master +# Do some sample work on Master with slave_compressed_protocol ON. +# (slave_compressed_protocol is set to ON in -slave.opt file of this test.) +CREATE TABLE t1 (i INT); +DROP TABLE t1; + +# Make sure sync is done, so that next 'assert' step can be executed without +# any issues. +--source include/rpl_sync.inc + +# Without the fix, the test would have generated few +# errors in the error log. With the fix, test will +# pass without any errors in the error log. +--let $assert_text= Check that there is no 'Read semi-sync reply magic number error' in error log. +--let $assert_select=Read semi-sync reply magic number error +--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_count= 0 +--let $assert_only_after = CURRENT_TEST:rpl.rpl_semi_sync_slave_compressed_protocol.test +--source include/assert_grep.inc + +--connection master +--eval SET @@GLOBAL. rpl_semi_sync_master_enabled = $sav_enabled_master + +--connection slave +source include/stop_slave.inc; +--eval SET @@GLOBAL. rpl_semi_sync_slave_enabled = $sav_enabled_slave +source include/start_slave.inc; + +# Cleanup +--source include/rpl_end.inc diff --git a/sql/semisync_master_ack_receiver.cc b/sql/semisync_master_ack_receiver.cc index 81f494c9d34..cc3a56a56b2 100644 --- a/sql/semisync_master_ack_receiver.cc +++ b/sql/semisync_master_ack_receiver.cc @@ -268,6 +268,11 @@ void Ack_receiver::run() net_clear(&net, 0); net.vio= &slave->vio; + /* + Set compress flag. This is needed to support + Slave_compress_protocol flag enabled Slaves + */ + net.compress= slave->thd->net.compress; len= my_net_read(&net); if (likely(len != packet_error))

1 0

[Maria-developers] RFC: incompatible changes to mtr debugging flags
by Sergei Golubchik 27 Jan '21

27 Jan '21

Hi, all, Here's a feature I want to discuss, as it's rather incompatibly removes a bunch of mtr command-line options. It's a unified handling of all debuggers. I wanted to do it for quite a while, but the actual trigger was me trying to add support for --rr (https://rr-project.org/) in embedded. It was supported only for non-embedded server, and making it work for embedded needed way more copy-pasting than I was comfortable with. So, in my branch all debuggers are not haphazardly added using whatever syntax one thought of at the moment and copy-pasted all over. They've handled by a common module, all support the same set of options (using gdb as an example): --gdb, --boot-gdb, --client-gdb, --manual-gdb. Both --gdb and --client-gdb work for embedded. All four of them accept an optional argument that can be a semicolon separated list of gdb commands, like in ./mtr 1st --gdb='b mysql_parse;r' but can start from command-line options too: ./mtr 1st --boot-gdb='-nh -q;b mysql_parse;r' A "debugger" is anything that wraps mysqld or mysqltest execution, the current list of debuggers is: gdb, ddd, dbx, lldb, valgrind, strace, ktrace, rr, devenv, windbg, vc_express, vc, vsjitdebugger. There's also "valgdb" that does what '--valgrind --gdb' was doing before. This removes the following mtr options: --rr-args --rr-dir --manual-debug --debugger --strace-option --stracer --valgrind-all --valgrind-mysqltest --valgrind-mysqld --valgrind-options --valgrind-option --valgrind-path --callgrind, and --valgrind only enables it for the server, not for everything. If pushed, it'll definitely break backward compatibility. But I don't think mtr backward compatibility is all that important, it might only affect a handful of developers who use scripts to start mtr with specific hard-coded settings, those scripts could be easily adjusted. It's much more important to have same mtr features in all branches, so that when switching between branches, one wouldn't need to guess what command line options mtr supports now. That is, if pushed, this should go into 10.2. Opinions? Missing features? Push / not push? for the reference, it's in bb-10.2-serg branch at the moment. Regards, Sergei

5 7

Re: [Maria-developers] 3e01da668d1: MDEV-21785: sequences used as default by other table not dumped in right order by mysqldump
by Sergei Golubchik 22 Jan '21

22 Jan '21

Hi, Oleksandr! On Jan 21, Oleksandr Byelkin wrote: > revision-id: 3e01da668d1 (mariadb-10.3.26-64-g3e01da668d1) > parent(s): 59998d3480f > author: Oleksandr Byelkin <sanja(a)mariadb.com> > committer: Oleksandr Byelkin <sanja(a)mariadb.com> > timestamp: 2021-01-14 10:45:51 +0100 > message: > > MDEV-21785: sequences used as default by other table not dumped in right order by mysqldump > > Dump sequences first. > > This atch made to keep it small and > to keep number of queries to the server the same. > > Order of tables in a dump can not be changed > (except sequences first) because mysql_list_tables > uses SHOW TABLES and I used SHOW FULL TABLES. it looks like a rather overengineered implementation. I'd suggest to simplify it as: > diff --git a/client/mysqldump.c b/client/mysqldump.c > index a964f96437d..17837713c01 100644 > --- a/client/mysqldump.c > +++ b/client/mysqldump.c > @@ -42,6 +42,11 @@ > /* on merge conflict, bump to a higher version again */ > #define DUMP_VERSION "10.19" > > +/** > + First mysql version supporting sequences. > +*/ > +#define FIRST_SEQUENCE_VERSION 100300 seems unnecessary, you implemented SHOW FULL TABLES in 5.0.2-alpha. Oct 2004. You can safely use SHOW FULL TABLES unconditionally. > + > #include <my_global.h> > #include <my_sys.h> > #include <my_user.h> > @@ -92,6 +97,13 @@ > /* Max length GTID position that we will output. */ > #define MAX_GTID_LENGTH 1024 > > + > +/* Dump sequence/tables control */ > +#define DUMP_TABLE_TABLE 1 > +#define DUMP_TABLE_SEQUENCE 2 > +#define DUMP_TABLE_ALL (DUMP_TABLE_TABLE | DUMP_TABLE_SEQUENCE) > + > + > static my_bool ignore_table_data(const uchar *hash_key, size_t len); > static void add_load_option(DYNAMIC_STRING *str, const char *option, > const char *option_value); > @@ -171,6 +183,7 @@ static DYNAMIC_STRING dynamic_where; > static MYSQL_RES *get_table_name_result= NULL; > static MEM_ROOT glob_root; > static MYSQL_RES *routine_res, *routine_list_res; > +static int get_table_name_result_short= 1; that'll be unnecessary > > > #include <sslopt-vars.h> > @@ -3853,13 +3866,16 @@ static char *alloc_query_str(size_t size) > ARGS > table - table name > db - db name > + type_ctrl - dump table or sequences or both > > RETURNS > void > */ > > > -static void dump_table(const char *table, const char *db, const uchar *hash_key, size_t len) > +static void dump_table(const char *table, const char *db, > + const uchar *hash_key, size_t len, > + const uint type_ctrl) > { > char ignore_flag; > char buf[200], table_buff[NAME_LEN+3]; > @@ -3881,9 +3897,11 @@ static void dump_table(const char *table, const char *db, const uchar *hash_key, > */ > if (check_if_ignore_table(table, table_type) & IGNORE_SEQUENCE_TABLE) > { > - get_sequence_structure(table, db); > + if (type_ctrl & DUMP_TABLE_SEQUENCE) > + get_sequence_structure(table, db); > + DBUG_VOID_RETURN; > + } else if (!(type_ctrl & DUMP_TABLE_TABLE)) > DBUG_VOID_RETURN; > - } here I'd remove the whole if(). Let's say the convention is that dump_table() should never be invoked for sequences, it's the caller's job to ensure it. > /* > Make sure you get the create table info before the following check for > --no-data flag below. Otherwise, the create table info won't be printed. > @@ -4368,18 +4386,49 @@ static void dump_table(const char *table, const char *db, const uchar *hash_key, > } /* dump_table */ > > > -static char *getTableName(int reset) > +static char *getTableName(int reset, uint table_control) > { > MYSQL_ROW row; > > if (!get_table_name_result) > { > - if (!(get_table_name_result= mysql_list_tables(mysql,NullS))) > - return(NULL); > + if (mysql_get_server_version(mysql) >= FIRST_SEQUENCE_VERSION) > + { > + const char *query= > + "SHOW FULL TABLES"; > + > + if (mysql_query_with_error_report(mysql, 0, query)) > + return (NULL); > + > + if (!(get_table_name_result= mysql_store_result(mysql))) > + return (NULL); > + > + get_table_name_result_short= 0; > + } > + else > + { > + if (!(get_table_name_result= mysql_list_tables(mysql,NullS))) > + return(NULL); > + get_table_name_result_short= 1; > + } this will be twice as small, if you won't try to support before-5.0 servers. > } > if ((row= mysql_fetch_row(get_table_name_result))) > - return((char*) row[0]); > - > + { > + int sequence; > + if (get_table_name_result_short || > + table_control == DUMP_TABLE_ALL) > + return((char*) row[0]); > + while (row && > + (((sequence= (strcmp(row[1], "SEQUENCE") == 0)) && > + (table_control & DUMP_TABLE_TABLE)) || > + ((!sequence) && > + (table_control & DUMP_TABLE_SEQUENCE)))) I'd rather just do static char *getTableName(int reset, uint want_sequence) and while (row && strcmp(row[1], "SEQUENCE") == want_sequence) row= mysql_fetch_row(get_table_name_result); > + { > + row= mysql_fetch_row(get_table_name_result); > + } > + if (row) > + return((char*) row[0]); > + } > if (reset) > mysql_data_seek(get_table_name_result,0); /* We want to read again */ > else > @@ -4785,17 +4834,17 @@ static int dump_all_stats() > /* EITS added in 10.0.1 */ > if (mysql_get_server_version(mysql) >= 100001) > { > - dump_table("column_stats", "mysql", NULL, 0); > - dump_table("index_stats", "mysql", NULL, 0); > - dump_table("table_stats", "mysql", NULL, 0); > + dump_table("column_stats", "mysql", NULL, 0, DUMP_TABLE_TABLE); > + dump_table("index_stats", "mysql", NULL, 0, DUMP_TABLE_TABLE); > + dump_table("table_stats", "mysql", NULL, 0, DUMP_TABLE_TABLE); this wouldn't be needed if the caller will only use dump_table for tables > } > /* Innodb may be disabled */ > if (!mysql_query(mysql, "show fields from innodb_index_stats")) > { > MYSQL_RES *tableres= mysql_store_result(mysql); > mysql_free_result(tableres); > - dump_table("innodb_index_stats", "mysql", NULL, 0); > - dump_table("innodb_table_stats", "mysql", NULL, 0); > + dump_table("innodb_index_stats", "mysql", NULL, 0, DUMP_TABLE_TABLE); > + dump_table("innodb_table_stats", "mysql", NULL, 0, DUMP_TABLE_TABLE); > } > opt_no_create_info= prev_no_create_info; > return 0; > @@ -4817,11 +4866,11 @@ static int dump_all_timezones() > opt_prev_no_create_info= opt_no_create_info; > opt_no_create_info= 1; > fprintf(md_result_file,"\nUSE mysql;\n"); > - dump_table("time_zone", "mysql", NULL, 0); > - dump_table("time_zone_name", "mysql", NULL, 0); > - dump_table("time_zone_leap_second", "mysql", NULL, 0); > - dump_table("time_zone_transition", "mysql", NULL, 0); > - dump_table("time_zone_transition_type", "mysql", NULL, 0); > + dump_table("time_zone", "mysql", NULL, 0, DUMP_TABLE_TABLE); > + dump_table("time_zone_name", "mysql", NULL, 0, DUMP_TABLE_TABLE); > + dump_table("time_zone_leap_second", "mysql", NULL, 0, DUMP_TABLE_TABLE); > + dump_table("time_zone_transition", "mysql", NULL, 0, DUMP_TABLE_TABLE); > + dump_table("time_zone_transition_type", "mysql", NULL, 0, DUMP_TABLE_TABLE); > opt_no_create_info= opt_prev_no_create_info; > return 0; > } > @@ -5346,12 +5396,29 @@ static int dump_all_tables_in_db(char *database) > DBUG_RETURN(1); > } > } > - while ((table= getTableName(0))) > + > + if (mysql_get_server_version(mysql) >= FIRST_SEQUENCE_VERSION && > + !opt_no_create_info) > + { > + // First process sequences > + while ((table= getTableName(1, DUMP_TABLE_SEQUENCE))) > + { > + char *end= strmov(afterdot, table); > + if (include_table((uchar*) hash_key, end - hash_key)) > + { > + dump_table(table, database, (uchar*) hash_key, end - hash_key, > + DUMP_TABLE_SEQUENCE); > + } > + } > + table_ctrl= DUMP_TABLE_TABLE; // next pass > + } and instead of all the above, you could do just for (int table_ctrl= 1; table_ctrl >= 0; table_ctrl--) > + while ((table= getTableName(0, table_ctrl))) > { > char *end= strmov(afterdot, table); > if (include_table((uchar*) hash_key, end - hash_key)) > { > - dump_table(table, database, (uchar*) hash_key, end - hash_key); > + dump_table(table, database, (uchar*) hash_key, end - hash_key, > + table_ctrl); > my_free(order_by); > order_by= 0; > if (opt_dump_triggers && mysql_get_server_version(mysql) >= 50009) > @@ -5745,11 +5813,24 @@ static int dump_selected_tables(char *db, char **table_names, int tables) > DBUG_RETURN(1); > } > } > + > + if (mysql_get_server_version(mysql) >= FIRST_SEQUENCE_VERSION && > + !opt_no_create_info) > + { > + /* Dump Sequence first */ > + for (pos= dump_tables; pos < end; pos++) > + { > + DBUG_PRINT("info",("Dumping sequence(?) %s", *pos)); > + dump_table(*pos, db, NULL, 0, DUMP_TABLE_SEQUENCE); > + } > + } > + else > + table_ctrl|= DUMP_TABLE_SEQUENCE; // dump all on next pass and here, in dump_selected_tables(), you'd need to do check_if_ignore_table() == IGNORE_SEQUENCE_TABLE. > /* Dump each selected table */ > for (pos= dump_tables; pos < end; pos++) > { > DBUG_PRINT("info",("Dumping table %s", *pos)); > - dump_table(*pos, db, NULL, 0); > + dump_table(*pos, db, NULL, 0, table_ctrl); > if (opt_dump_triggers && > mysql_get_server_version(mysql) >= 50009) > { Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

[Maria-developers] eb75e8705d9: MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 showed
by sujatha 21 Jan '21

21 Jan '21

revision-id: eb75e8705d9a444e10057967eaebf947b1115ff8 (mariadb-10.2.31-695-geb75e8705d9) parent(s): 53acd1c1d88be82190c56af3e4cc11fb2770a169 author: Sujatha committer: Sujatha timestamp: 2021-01-21 13:00:02 +0530 message: MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 showed Problem: ======== Auto purge of relaylogs stops when relay-log-file is 'slave-relay-log.999999' and slave_parallel_threads is enabled. Analysis: ========= The problem is that in Relay_log_info::inc_group_relay_log_pos() function, when two log names are compared via strcmp() function, it gives correct result, when log name sequence numbers are of same digits(6 digits), But when the number goes to 7 digits, a 999999 compares greater than 1000000, which is wrong, hence the bug. Fix: ==== Extract the numeric extension part of the file name, convert it into unsigned long and compare. Thanks to David Zhao for the contribution. --- .../suite/rpl/r/rpl_relay_max_extension.result | 37 +++++++ .../suite/rpl/t/rpl_relay_max_extension.test | 109 +++++++++++++++++++++ sql/rpl_parallel.cc | 5 +- sql/rpl_rli.cc | 4 +- sql/sql_repl.cc | 17 ++++ sql/sql_repl.h | 1 + 6 files changed, 169 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_relay_max_extension.result b/mysql-test/suite/rpl/r/rpl_relay_max_extension.result new file mode 100644 index 00000000000..4444398203e --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_relay_max_extension.result @@ -0,0 +1,37 @@ +include/rpl_init.inc [topology=1->2] +connection server_2; +include/stop_slave.inc +RESET SLAVE; +include/start_slave.inc +include/stop_slave.inc +# +# Stop slave server +# +# +# Simulate file number get close to 999997 +# by renaming relay logs and modifying index/info files +# +# Restart slave server +# +SET @save_slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @save_max_relay_log_size= @@GLOBAL.max_relay_log_size; +SET GLOBAL slave_parallel_threads=1; +SET GLOBAL max_relay_log_size=100 * 1024; +include/start_slave.inc +connection server_1; +create table t1 (i int, c varchar(1024)); +# +# Insert some data to generate enough amount of binary logs +# +connection server_2; +# +# Assert that 'slave-relay-bin.999999' is purged. +# +NOT FOUND /slave-relay-bin.999999/ in slave-relay-bin.index +include/stop_slave.inc +SET GLOBAL slave_parallel_threads= @save_slave_parallel_threads; +SET GLOBAL max_relay_log_size= @save_max_relay_log_size; +include/start_slave.inc +connection server_1; +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_relay_max_extension.test b/mysql-test/suite/rpl/t/rpl_relay_max_extension.test new file mode 100644 index 00000000000..e1e087f2e0e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_relay_max_extension.test @@ -0,0 +1,109 @@ +# ==== Purpose ==== +# +# Test verifies that auto purging mechanism of relay logs works fine when the +# file extension grows beyond 999999. +# +# ==== Implementation ==== +# +# Steps: +# 0 - In master-slave setup clear all the relay logs on the slave server. +# 1 - Start the slave so that new relay logs starting from +# 'slave-relay-bin.000001' are created. +# 2 - Get the active relay-log file name by using SHOW SLAVE STATUS. +# Shutdown the slave server. +# 3 - Rename active relay log to '999997' in both 'relay-log.info' and +# 'slave-relay-bin.index' files. +# 4 - Restart the slave server by configuring 'slave_parallel_threads=1' +# and 'max_relay_log_size=100K'. +# 5 - Generate load on master such that few relay logs are generated on +# slave. The relay log sequence number will change to 7 digits. +# 6 - Sync slave with master to ensure that relay logs are applied on +# slave. They should have been automatically purged. +# 7 - Assert that there is no 'slave-relay-bin.999999' file in +# 'relay-log.info'. +# +# ==== References ==== +# +# MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 +# showed +# + +--source include/have_innodb.inc +--source include/have_binlog_format_row.inc +--let $rpl_topology=1->2 +--source include/rpl_init.inc + +--connection server_2 +--source include/stop_slave.inc +RESET SLAVE; +--source include/start_slave.inc +--source include/stop_slave.inc +--let $relay_log=query_get_value(SHOW SLAVE STATUS, Relay_Log_File, 1) + +--echo # +--echo # Stop slave server +--echo # + +--let $datadir = `select @@datadir` +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +--shutdown_server 10 +--source include/wait_until_disconnected.inc + +--exec sed -i "s/$relay_log/slave-relay-bin.999997/g" $datadir/relay-log.info +--exec sed -i "s/$relay_log/slave-relay-bin.999997/g" $datadir/slave-relay-bin.index + +--echo # +--echo # Simulate file number get close to 999997 +--echo # by renaming relay logs and modifying index/info files + +--move_file $datadir/$relay_log $datadir/slave-relay-bin.999997 + +--echo # +--echo # Restart slave server +--echo # + +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +SET @save_slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @save_max_relay_log_size= @@GLOBAL.max_relay_log_size; + +SET GLOBAL slave_parallel_threads=1; +SET GLOBAL max_relay_log_size=100 * 1024; +--source include/start_slave.inc + +--connection server_1 +create table t1 (i int, c varchar(1024)); +--echo # +--echo # Insert some data to generate enough amount of binary logs +--echo # +--let $count = 1000 +--disable_query_log +while ($count) +{ + eval insert into t1 values (1001 - $count, repeat('a',1000)); + dec $count; +} +--enable_query_log +--save_master_pos + +--connection server_2 +--sync_with_master + +--let $relay_log=query_get_value(SHOW SLAVE STATUS, Relay_Log_File, 1) + +--echo # +--echo # Assert that 'slave-relay-bin.999999' is purged. +--echo # +let SEARCH_FILE=$datadir/slave-relay-bin.index; +let SEARCH_PATTERN=slave-relay-bin.999999; +source include/search_pattern_in_file.inc; + +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads= @save_slave_parallel_threads; +SET GLOBAL max_relay_log_size= @save_max_relay_log_size; +--source include/start_slave.inc + +--connection server_1 +DROP TABLE t1; +--source include/rpl_end.inc diff --git a/sql/rpl_parallel.cc b/sql/rpl_parallel.cc index 4cf87ba73b7..869640fd46f 100644 --- a/sql/rpl_parallel.cc +++ b/sql/rpl_parallel.cc @@ -4,6 +4,7 @@ #include "rpl_mi.h" #include "sql_parse.h" #include "debug_sync.h" +#include "sql_repl.h" /* Code for optional parallel execution of replicated events on the slave. @@ -82,7 +83,7 @@ handle_queued_pos_update(THD *thd, rpl_parallel_thread::queued_event *qev) return; mysql_mutex_lock(&rli->data_lock); - cmp= strcmp(rli->group_relay_log_name, qev->event_relay_log_name); + cmp= compare_log_name(rli->group_relay_log_name, qev->event_relay_log_name); if (cmp < 0) { rli->group_relay_log_pos= qev->future_event_relay_log_pos; @@ -91,7 +92,7 @@ handle_queued_pos_update(THD *thd, rpl_parallel_thread::queued_event *qev) rli->group_relay_log_pos < qev->future_event_relay_log_pos) rli->group_relay_log_pos= qev->future_event_relay_log_pos; - cmp= strcmp(rli->group_master_log_name, qev->future_event_master_log_name); + cmp= compare_log_name(rli->group_master_log_name, qev->future_event_master_log_name); if (cmp < 0) { strcpy(rli->group_master_log_name, qev->future_event_master_log_name); diff --git a/sql/rpl_rli.cc b/sql/rpl_rli.cc index 40ab375571a..5273b33c728 100644 --- a/sql/rpl_rli.cc +++ b/sql/rpl_rli.cc @@ -989,7 +989,7 @@ void Relay_log_info::inc_group_relay_log_pos(ulonglong log_pos, if (rgi->is_parallel_exec) { /* In case of parallel replication, do not update the position backwards. */ - int cmp= strcmp(group_relay_log_name, rgi->event_relay_log_name); + int cmp= compare_log_name(group_relay_log_name, rgi->event_relay_log_name); if (cmp < 0) { group_relay_log_pos= rgi->future_event_relay_log_pos; @@ -1001,7 +1001,7 @@ void Relay_log_info::inc_group_relay_log_pos(ulonglong log_pos, In the parallel case we need to update the master_log_name here, rather than in Rotate_log_event::do_update_pos(). */ - cmp= strcmp(group_master_log_name, rgi->future_event_master_log_name); + cmp= compare_log_name(group_master_log_name, rgi->future_event_master_log_name); if (cmp <= 0) { if (cmp < 0) diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 4af8ebc2dd8..59a3f686e45 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -4541,5 +4541,22 @@ rpl_gtid_pos_update(THD *thd, char *str, size_t len) return false; } +int compare_log_name(const char *log_1, const char *log_2) { + int res= 1; + const char *ext1_str= strrchr(log_1, '.'); + const char *ext2_str= strrchr(log_2, '.'); + char file_name_1[255], file_name_2[255]; + strmake(file_name_1, log_1, (ext1_str - log_1)); + strmake(file_name_2, log_2, (ext2_str - log_2)); + char *endptr = NULL; + res= strcmp(file_name_1, file_name_2); + if (!res) + { + ulong ext1= strtoul(++ext1_str, &endptr, 10); + ulong ext2= strtoul(++ext2_str, &endptr, 10); + res= (ext1 > ext2 ? 1 : ((ext1 == ext2) ? 0 : -1)); + } + return res; +} #endif /* HAVE_REPLICATION */ diff --git a/sql/sql_repl.h b/sql/sql_repl.h index 8ddfa9239f6..9129aaeed5e 100644 --- a/sql/sql_repl.h +++ b/sql/sql_repl.h @@ -56,6 +56,7 @@ bool show_binlogs(THD* thd); extern int init_master_info(Master_info* mi); void kill_zombie_dump_threads(uint32 slave_server_id); int check_binlog_magic(IO_CACHE* log, const char** errmsg); +int compare_log_name(const char *log_1, const char *log_2); struct LOAD_FILE_IO_CACHE : public IO_CACHE {

1 0

[Maria-developers] How to change max key length for MyISAM?
by Witold Filipczyk 15 Jan '21

15 Jan '21

Sorry, for private question, but I did not know where to ask. mariadb-10.3.27. I changed in include/myisam.h: #define MI_MAX_KEY_LENGTH 3072 /* Max length in bytes */ but, it did not help, still this ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes. What more to change to get key length 3072 for MyISAM tables? And what can be negative impact of such a change? Witold Filipczyk

5 8

[Maria-developers] MDEV-13467 review.
by Alexey Botchkov 09 Jan '21

09 Jan '21

Hi, Anel! Sorry for that long delay with this review. Had real difficult weeks of my life :( Now it seems to be over, so getting back to normal. Below is my opinions of the code i see in the branch bb-10.1-anel-MDEV-13467-gis-feature-st_sphere-v2 Hope it's the recent version. + Adapter for functions that takes two or three arguments. +*/ + +class Create_func_arg2_or_3 : public Create_func +{ Do you think we need the separate Create_fund_arg2_or_3 class? If you don't see any other class to inherit from it, i'd recommend to leave the Create_func_distance_sphere alone. No need to have two constructors in the Item_func_sphere_distance too. Just use the list argument as it's done for the Item_func_json_contains (in 10.2). Anyway i belive we should get rid of duplicating code about the 'param1' and 'param2'. They can be popped/checked with no if (arg_count == 2) condition. + double distance= 0.0; + null_value= (args[0]->null_value || args[1]->null_value); + if (null_value) + { + // Returned item must be set to Null + DBUG_ASSERT(maybe_null); don't think it's needed. + return 0; Should be return 0.0 i belive. Or goto handle_errors. Thet goes to all but one 'return' statements in this function. + } > ... > if (!arg1 || !arg2) This last condition should never happen since we have not-null_value. So i'd remove that 'if' section completely. And anyway it should do the 'return' in that branch. + null_value= args[2]->null_value; + if (null_value) + { + return 0; + } seems nicer to me this way if (args[2]->null_value) { null_value= true; goto handle_errors; } + if (!(g1= Geometry::construct(&buffer1, arg1->ptr(), arg1->length())) || + !(g2= Geometry::construct(&buffer2, arg2->ptr(), arg2->length()))) + { + goto handle_errors; we should launch the ER_GIS_INVALID_DATA here. + // Generate error message in case different geometry is used? Yes, i think the explainig message here is good to have. +double Item_func_sphere_distance::spherical_distance(Geometry *g1, + Geometry *g2, + const double sphere_radius) +double Item_func_sphere_distance::spherical_distance_points(Geometry *g1, + Geometry *g2, + const double r) Why these two are member static functions, not simply static? If you plan any 'external' use for them, please add comments about what exacly they do. +double Item_func_sphere_distance::spherical_distance(Geometry *g1, + Geometry *g2, + const double sphere_radius) +{ + double res= 0.0; + switch (g1->get_class_info()->m_type_id) + { + case Geometry::wkb_point: + res= spherical_distance_points(g1, g2, sphere_radius); + break; + + case Geometry::wkb_multipoint: + res= spherical_distance_points(g1, g2, sphere_radius); + break; Why do we need this function? We confirmed already that the type is either wkb_point or wkb_multipolygon, so just can call spherical_distance_points. No? >>>>> +class Item_func_sphere_distance: public Item_real_func +{ + double sphere_radius= 6370986.0; // Default radius equals Earth radius Some compilers don't like this syntax, and we didn't use it yet, so i'd recomment to move this to the constructor or even better to the ::val_real(). +double Gis_point::calculate_haversine(const Geometry *g, + const double sphere_radius) +{ + DBUG_ASSERT(sphere_radius > 0); + double x1r= 0.0; + double x2r= 0.0; + double y1r= 0.0; + double y2r= 0.0; That lot of unnecessary initializations seems excessive. + if (g->get_class_info()->m_type_id == Geometry::wkb_multipoint) + { + const char point_size= 4 + WKB_HEADER_SIZE + POINT_DATA_SIZE+1; //1 for the type + char point_temp[point_size]; + memset(point_temp+4, Geometry::wkb_point, 1); + memcpy(point_temp+5, static_cast<const Gis_multi_point *>(g)->get_data_ptr()+5, 4); + memcpy(point_temp+4+WKB_HEADER_SIZE, g->get_data_ptr()+4+WKB_HEADER_SIZE, + POINT_DATA_SIZE); + point_temp[point_size-1]= '\0'; + Geometry_buffer gbuff; + Geometry *gg= Geometry::construct(&gbuff, point_temp, point_size-1); There is the Gis_multi_point::geometry_n() function, should be used here to get the point. I don't like that lot of static_cast-s. Isn't it nicer to have just the function of double calculate_harvesine_points(Geometry *p1, Geometry *p2, double radius) double calculate_harvesine_multipoint_point(Geometry *mp, Geometry *p, double radius) double calculate_harvesine_multipoint_multipoint(Geometry *mp1, Geometry *mp2, double radius) which would call each other? The ::get_xy_radian() method looks unneeded to me. It's enough to have the to_radian(double d) function. BTW it's possible to do the multipoint/multipoint calculations faster than n^2. Not necessary to do it right now, just not to remember. Best regards. HF

1 0

[Maria-developers] 608b0ee52ef: MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
by sujatha 04 Jan '21

04 Jan '21

revision-id: 608b0ee52ef3e854ce14a407e64e936adbbeba23 (mariadb-10.2.31-648-g608b0ee52ef) parent(s): 25db9ffa8bdab8a2f2af3c7f154343dd6c6d238f author: Sujatha committer: Sujatha timestamp: 2021-01-04 15:06:12 +0530 message: MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11 Problem: ======= Upon deleting or updating a row in a parent table (with primary key), if the child table has virtual column and an associated key with ON UPDATE CASCADE/ON DELETE CASCADE, it will result in slave crash. Analysis: ======== Tables which are related through foreign key require prelocking similar to triggers. i.e If a table has triggers/foreign keys we should add all tables and routines used by them to the prelocking set. This prelocking happens during 'open_and_lock_tables' call. Each table being opened is checked for foreign key references. If foreign key reference exists then the child table is opened and it is linked to the table_list. Upon any modification to parent table its corresponding child tables are retried from table_list and they are updated accordingly. This prelocking work fine on master. On slave prelocking works for following cases. - Statement/mixed based replication - In row based replication when trigger execution is enabled through 'slave_run_triggers_for_rbr=YES/LOGGING/ENFORCE' Otherwise it results in an assert/crash, as the parent table will not find the corresponding child table and it will be NULL. Dereferencing NULL pointer leads to slave server exit. Fix: === Introduce a new 'slave_fk_event_map' flag similar to 'trg_event_map'. This flag will ensure that when foreign key is enabled in row based replication all the parent and child tables are prelocked, so that parent is able to locate the child table. Note: This issue is specific to slave, hence only slave needs to be upgraded. --- mysql-test/suite/rpl/r/rpl_row_vcol_crash.result | 380 ++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_row_vcol_crash.test | 425 +++++++++++++++++++++++ sql/log_event.cc | 42 +-- sql/sql_base.cc | 118 ++++--- sql/table.h | 6 +- 5 files changed, 906 insertions(+), 65 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result new file mode 100644 index 00000000000..f76d8935fa8 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result @@ -0,0 +1,380 @@ +include/master-slave.inc +[connection master] +# +# Test case 1: KEY on a virtual column with ON DELETE CASCADE +# +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3); +CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, +t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (90,1,NULL); +INSERT INTO t2 VALUES (91,2,default); +DELETE FROM t1 WHERE id=1; +connection slave; +# +# Verify data consistency on slave +# +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +connection master; +DROP TABLE t2,t1; +connection slave; +# +# Test case 2: Verify "ON DELETE CASCADE" for parent->child->child scenario +# Parent table: users +# Child tables: matchmaking_groups, matchmaking_group_users +# Parent table: matchmaking_groups +# Child tables: matchmaking_group_users, matchmaking_group_maps +# +# Deleting a row from parent table should be reflected in +# child tables. +# matchmaking_groups->matchmaking_group_users->matchmaking_group_maps +# users->matchmaking_group_users->matchmaking_group_maps +# +connection master; +CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +name VARCHAR(32) NOT NULL DEFAULT '' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE matchmaking_groups ( +id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +host_user_id INT UNSIGNED NOT NULL UNIQUE, +v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col), +CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id) +ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE matchmaking_group_users ( +matchmaking_group_id BIGINT UNSIGNED NOT NULL, +user_id INT UNSIGNED NOT NULL, +v_col1 int as (user_id+1) virtual, KEY (v_col1), +PRIMARY KEY (matchmaking_group_id,user_id), +UNIQUE KEY user_id (user_id), +CONSTRAINT FOREIGN KEY (matchmaking_group_id) +REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT FOREIGN KEY (user_id) +REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE matchmaking_group_maps ( +matchmaking_group_id BIGINT UNSIGNED NOT NULL, +map_id TINYINT UNSIGNED NOT NULL, +v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2), +PRIMARY KEY (matchmaking_group_id,map_id), +CONSTRAINT FOREIGN KEY (matchmaking_group_id) +REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +connection slave; +connection master; +INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar'); +INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default); +DELETE FROM matchmaking_groups WHERE id = 10; +connection slave; +# +# No rows should be returned as ON DELETE CASCASE should have removed +# corresponding rows from child tables. There should not any mismatch +# of 'id' field between parent->child. +# +SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); +matchmaking_group_id user_id v_col1 +SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); +matchmaking_group_id map_id v_col2 +# +# Rows with id=11 should be present +# +SELECT * FROM matchmaking_group_users; +matchmaking_group_id user_id v_col1 +11 2 3 +SELECT * FROM matchmaking_group_maps; +matchmaking_group_id map_id v_col2 +11 66 67 +connection master; +DELETE FROM users WHERE id = 2; +connection slave; +# +# No rows should be present in both the child tables +# +SELECT * FROM matchmaking_group_users; +matchmaking_group_id user_id v_col1 +SELECT * FROM matchmaking_group_maps; +matchmaking_group_id map_id v_col2 +connection master; +DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users; +connection slave; +# +# Test case 3: KEY on a virtual column with ON UPDATE CASCADE +# +connection master; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 80); +CREATE TABLE t2 (a INT KEY, b INT, +v_col int as (b+1) virtual, KEY (v_col), +CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (51, 1, default); +connection slave; +connection master; +UPDATE t1 SET a = 50 WHERE a = 1; +# +# Master: Verify that ON UPDATE CASCADE works fine +# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +# +SELECT * FROM t2 WHERE b=50; +a b v_col +51 50 51 +connection slave; +# +# Slave: Verify that ON UPDATE CASCADE works fine +# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +# +SELECT * FROM t2 WHERE b=50; +a b v_col +51 50 51 +connection master; +DROP TABLE t2, t1; +connection slave; +# +# Test case 4: Define triggers on master, their results should be +# replicated as part of row events and they should be +# applied on slave with the default +# slave_run_triggers_for_rbr=NO +# +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (2),(3); +connection slave; +SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr'; +Variable_name Value +slave_run_triggers_for_rbr NO +# +# As two rows are inserted in table 't1', two rows should get inserted +# into table 't2' as part of trigger. +# +include/assert.inc [Table t2 should have two rows.] +connection master; +DROP TABLE t1,t2; +connection slave; +# +# Test case 5: Define triggers + Foreign Keys on master, their results +# should be replicated as part of row events and master +# and slave should be in sync. +# +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1); +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# As two rows are inserted in table 't1', two rows should get inserted +# into table 't3' as part of trigger. +# +include/assert.inc [Table t3 should have two rows.] +# +# Verify ON DELETE CASCASE correctness +# +connection master; +DELETE FROM t1 WHERE id=2; +connection slave; +connection master; +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +include/diff_tables.inc [master:test.t3, slave:test.t3] +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case 6: Triggers are present only on slave and +# 'slave_run_triggers_for_rbr=NO' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= NO;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr NO +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), +KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 0 +# +include/assert.inc [Table t3 should have zero rows.] +connection master; +DELETE FROM t1 WHERE id=2; +connection slave; +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +# +# Verify t1, t2 are consistent on slave. +# +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +connection master; +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case 7: Triggers are present only on slave and +# 'slave_run_triggers_for_rbr=YES' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= YES;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr YES +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), +KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 2 +# +include/assert.inc [Table t3 should have two rows.] +connection master; +DELETE FROM t1 WHERE id=2; +connection slave; +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +# +# Verify t1, t2 are consistent on slave. +# +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +connection master; +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case 8: Triggers and Foreign Keys are present only on slave and +# 'slave_run_triggers_for_rbr=NO' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= NO;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr NO +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +SET sql_log_bin=0; +CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB; +SET sql_log_bin=1; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 0 +# +include/assert.inc [Table t3 should have zero rows.] +connection master; +DELETE FROM t1 WHERE id=2; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have two rows +SELECT * FROM t2; +t1_id v_col +2 3 +3 4 +connection slave; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have one row on slave due to ON DELETE CASCASE +SELECT * FROM t2; +t1_id v_col +3 4 +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +connection master; +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case 9: Triggers are Foreign Keys are present only on slave and +# 'slave_run_triggers_for_rbr=YES' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= YES;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr YES +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +SET sql_log_bin=0; +CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB; +SET sql_log_bin=1; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 2 +# +include/assert.inc [Table t3 should have two rows.] +connection master; +DELETE FROM t1 WHERE id=2; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have two rows +SELECT * FROM t2; +t1_id v_col +2 3 +3 4 +connection slave; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have one row on slave due to ON DELETE CASCASE +SELECT * FROM t2; +t1_id v_col +3 4 +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +connection master; +DROP TABLE t3,t2,t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test new file mode 100644 index 00000000000..84ee14977f3 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test @@ -0,0 +1,425 @@ +# ==== Purpose ==== +# +# Test verifies that, slave doesn't report any assert on UPDATE or DELETE of +# row which tries to update the virtual columns with associated KEYs. +# +# Test scenarios are listed below. +# 1) KEY on a virtual column with ON DELETE CASCADE +# 2) Verify "ON DELETE CASCADE" for parent->child->child scenario +# 3) KEY on a virtual column with ON UPDATE CASCADE +# 4) Define triggers on master, their results should be replicated +# as part of row events and they should be applied on slave with +# the default slave_run_triggers_for_rbr=NO +# 5) Define triggers + Foreign Keys on master, their results should be +# replicated as part of row events and master and slave should be in sync. +# 6) Triggers are present only on slave and 'slave_run_triggers_for_rbr=NO' +# 7) Triggers are present only on slave and 'slave_run_triggers_for_rbr=YES' +# 8) Triggers and Foreign Keys are present only on slave and +# 'slave_run_triggers_for_rbr=NO' +# 9) Triggers are Foreign Keys are present only on slave and +# 'slave_run_triggers_for_rbr=YES' +# +# ==== References ==== +# +# MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11 +# + +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc +--source include/master-slave.inc + + +--echo # +--echo # Test case 1: KEY on a virtual column with ON DELETE CASCADE +--echo # +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3); + +CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, + t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), + CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (90,1,NULL); +INSERT INTO t2 VALUES (91,2,default); + +# Following query results in an assert on slave +DELETE FROM t1 WHERE id=1; +--sync_slave_with_master + +--echo # +--echo # Verify data consistency on slave +--echo # +--let $diff_tables= master:test.t1, slave:test.t1 +--source include/diff_tables.inc +--let $diff_tables= master:test.t2, slave:test.t2 +--source include/diff_tables.inc + +--connection master +DROP TABLE t2,t1; +--sync_slave_with_master + +--echo # +--echo # Test case 2: Verify "ON DELETE CASCADE" for parent->child->child scenario +--echo # Parent table: users +--echo # Child tables: matchmaking_groups, matchmaking_group_users +--echo # Parent table: matchmaking_groups +--echo # Child tables: matchmaking_group_users, matchmaking_group_maps +--echo # +--echo # Deleting a row from parent table should be reflected in +--echo # child tables. +--echo # matchmaking_groups->matchmaking_group_users->matchmaking_group_maps +--echo # users->matchmaking_group_users->matchmaking_group_maps +--echo # + +--connection master +CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, + name VARCHAR(32) NOT NULL DEFAULT '' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE matchmaking_groups ( + id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, + host_user_id INT UNSIGNED NOT NULL UNIQUE, + v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col), + CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE matchmaking_group_users ( + matchmaking_group_id BIGINT UNSIGNED NOT NULL, + user_id INT UNSIGNED NOT NULL, + v_col1 int as (user_id+1) virtual, KEY (v_col1), + PRIMARY KEY (matchmaking_group_id,user_id), + UNIQUE KEY user_id (user_id), + CONSTRAINT FOREIGN KEY (matchmaking_group_id) + REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FOREIGN KEY (user_id) + REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE matchmaking_group_maps ( + matchmaking_group_id BIGINT UNSIGNED NOT NULL, + map_id TINYINT UNSIGNED NOT NULL, + v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2), + PRIMARY KEY (matchmaking_group_id,map_id), + CONSTRAINT FOREIGN KEY (matchmaking_group_id) + REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +--sync_slave_with_master + +--connection master +INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar'); +INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default); + +DELETE FROM matchmaking_groups WHERE id = 10; +--sync_slave_with_master + +--echo # +--echo # No rows should be returned as ON DELETE CASCASE should have removed +--echo # corresponding rows from child tables. There should not any mismatch +--echo # of 'id' field between parent->child. +--echo # +SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); +SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); + +--echo # +--echo # Rows with id=11 should be present +--echo # +SELECT * FROM matchmaking_group_users; +SELECT * FROM matchmaking_group_maps; + +--connection master +DELETE FROM users WHERE id = 2; +--sync_slave_with_master + +--echo # +--echo # No rows should be present in both the child tables +--echo # +SELECT * FROM matchmaking_group_users; +SELECT * FROM matchmaking_group_maps; + +--connection master +DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users; +--sync_slave_with_master + +--echo # +--echo # Test case 3: KEY on a virtual column with ON UPDATE CASCADE +--echo # + +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 80); + +CREATE TABLE t2 (a INT KEY, b INT, + v_col int as (b+1) virtual, KEY (v_col), + CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (51, 1, default); +--sync_slave_with_master + +--connection master +UPDATE t1 SET a = 50 WHERE a = 1; + +--echo # +--echo # Master: Verify that ON UPDATE CASCADE works fine +--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +--echo # +SELECT * FROM t2 WHERE b=50; +--sync_slave_with_master + +--echo # +--echo # Slave: Verify that ON UPDATE CASCADE works fine +--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +--echo # +SELECT * FROM t2 WHERE b=50; + +--connection master +DROP TABLE t2, t1; +--sync_slave_with_master + +--echo # +--echo # Test case 4: Define triggers on master, their results should be +--echo # replicated as part of row events and they should be +--echo # applied on slave with the default +--echo # slave_run_triggers_for_rbr=NO +--echo # + +# In row-based replication, the binary log contains row changes. It will have +# both the changes made by the statement itself, and the changes made by the +# triggers that were invoked by the statement. Slave server(s) do not need to +# run triggers for row changes they are applying. Hence verify that this +# property remains the same and data should be available as if trigger was +# executed. Please note by default slave_run_triggers_for_rbr=NO. + +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (2),(3); +--sync_slave_with_master + +SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr'; +--echo # +--echo # As two rows are inserted in table 't1', two rows should get inserted +--echo # into table 't2' as part of trigger. +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t2 +--let $assert_text= Table t2 should have two rows. +--source include/assert.inc + +--connection master +DROP TABLE t1,t2; +--sync_slave_with_master + +--echo # +--echo # Test case 5: Define triggers + Foreign Keys on master, their results +--echo # should be replicated as part of row events and master +--echo # and slave should be in sync. +--echo # +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), + CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1); + +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +--sync_slave_with_master + +--echo # +--echo # As two rows are inserted in table 't1', two rows should get inserted +--echo # into table 't3' as part of trigger. +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t3 +--let $assert_text= Table t3 should have two rows. +--source include/assert.inc + +--echo # +--echo # Verify ON DELETE CASCASE correctness +--echo # +--connection master +DELETE FROM t1 WHERE id=2; +--sync_slave_with_master + +--connection master +--let $diff_tables= master:test.t1, slave:test.t1 +--source include/diff_tables.inc +--let $diff_tables= master:test.t2, slave:test.t2 +--source include/diff_tables.inc +--let $diff_tables= master:test.t3, slave:test.t3 +--source include/diff_tables.inc + +DROP TABLE t3,t2,t1; +--sync_slave_with_master + +# +# Test case: Triggers only on slave +# +--write_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc PROCEDURE + if ($slave_run_triggers_for_rbr == '') { + --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr + } + +--connection slave +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; + +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), + KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +--sync_slave_with_master + +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); + +--connection master +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +--sync_slave_with_master + +if ($slave_run_triggers_for_rbr == 'NO') { +--echo # +--echo # Count must be 0 +--echo # +--let $assert_cond= COUNT(*) = 0 FROM t3 +--let $assert_text= Table t3 should have zero rows. +--source include/assert.inc +} +if ($slave_run_triggers_for_rbr == 'YES') { +--echo # +--echo # Count must be 2 +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t3 +--let $assert_text= Table t3 should have two rows. +--source include/assert.inc +} + +--connection master +DELETE FROM t1 WHERE id=2; +--sync_slave_with_master +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; + +--echo # +--echo # Verify t1, t2 are consistent on slave. +--echo # +--let $diff_tables= master:test.t1, slave:test.t1 +--source include/diff_tables.inc +--let $diff_tables= master:test.t2, slave:test.t2 +--source include/diff_tables.inc + +--connection master +DROP TABLE t3,t2,t1; +--sync_slave_with_master +#END OF +PROCEDURE + +--echo # +--echo # Test case 6: Triggers are present only on slave and +--echo # 'slave_run_triggers_for_rbr=NO' +--echo # +--let $slave_run_triggers_for_rbr=NO +--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc + +--echo # +--echo # Test case 7: Triggers are present only on slave and +--echo # 'slave_run_triggers_for_rbr=YES' +--echo # +--let $slave_run_triggers_for_rbr=YES +--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc +--remove_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc + +# +# Test case: Trigger and Foreign Key are present only on slave +# +--write_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc PROCEDURE + if ($slave_run_triggers_for_rbr == '') { + --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr + } + +--connection slave +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; + +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +SET sql_log_bin=0; +CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB; +SET sql_log_bin=1; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +--sync_slave_with_master + +# Have foreign key and trigger on slave. +CREATE TABLE t2 (t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), + CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); + +--connection master +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +--sync_slave_with_master + +if ($slave_run_triggers_for_rbr == 'NO') { +--echo # +--echo # Count must be 0 +--echo # +--let $assert_cond= COUNT(*) = 0 FROM t3 +--let $assert_text= Table t3 should have zero rows. +--source include/assert.inc +} +if ($slave_run_triggers_for_rbr == 'YES') { +--echo # +--echo # Count must be 2 +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t3 +--let $assert_text= Table t3 should have two rows. +--source include/assert.inc +} + +--connection master +DELETE FROM t1 WHERE id=2; +--echo # t1: Should have one row +SELECT * FROM t1; +--echo # t2: Should have two rows +SELECT * FROM t2; +--sync_slave_with_master +--echo # t1: Should have one row +SELECT * FROM t1; +--echo # t2: Should have one row on slave due to ON DELETE CASCASE +SELECT * FROM t2; +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; + +--connection master +DROP TABLE t3,t2,t1; +--sync_slave_with_master +#END OF +PROCEDURE + +--echo # +--echo # Test case 8: Triggers and Foreign Keys are present only on slave and +--echo # 'slave_run_triggers_for_rbr=NO' +--echo # +--let $slave_run_triggers_for_rbr=NO +--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc + +--echo # +--echo # Test case 9: Triggers are Foreign Keys are present only on slave and +--echo # 'slave_run_triggers_for_rbr=YES' +--echo # +--let $slave_run_triggers_for_rbr=YES +--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc +--remove_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc + +--source include/rpl_end.inc diff --git a/sql/log_event.cc b/sql/log_event.cc index c649e1f64fa..c32f31db1f6 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -10718,7 +10718,7 @@ int Rows_log_event::do_add_row_data(uchar *row_data, size_t length) There was the same problem with MERGE MYISAM tables and so here we try to go the same way. */ -static void restore_empty_query_table_list(LEX *lex) +inline void restore_empty_query_table_list(LEX *lex) { if (lex->first_not_own_table()) (*lex->first_not_own_table()->prev_global)= NULL; @@ -10733,6 +10733,8 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) TABLE* table; DBUG_ENTER("Rows_log_event::do_apply_event(Relay_log_info*)"); int error= 0; + LEX *lex= thd->lex; + uint8 new_trg_event_map= get_trg_event_map(); /* If m_table_id == ~0ULL, then we have a dummy event that does not contain any data. In that case, we just remove all tables in the @@ -10823,27 +10825,29 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) DBUG_ASSERT(!debug_sync_set_action(thd, STRING_WITH_LEN(action))); };); - if (slave_run_triggers_for_rbr) - { - LEX *lex= thd->lex; - uint8 new_trg_event_map= get_trg_event_map(); - - /* - Trigger's procedures work with global table list. So we have to add - rgi->tables_to_lock content there to get trigger's in the list. + /* + Trigger's procedures work with global table list. So we have to add + rgi->tables_to_lock content there to get trigger's in the list. - Then restore_empty_query_table_list() restore the list as it was - */ - DBUG_ASSERT(lex->query_tables == NULL); - if ((lex->query_tables= rgi->tables_to_lock)) - rgi->tables_to_lock->prev_global= &lex->query_tables; + Then restore_empty_query_table_list() restore the list as it was + */ + DBUG_ASSERT(lex->query_tables == NULL); + if ((lex->query_tables= rgi->tables_to_lock)) + rgi->tables_to_lock->prev_global= &lex->query_tables; - for (TABLE_LIST *tables= rgi->tables_to_lock; tables; - tables= tables->next_global) + for (TABLE_LIST *tables= rgi->tables_to_lock; tables; + tables= tables->next_global) + { + if (slave_run_triggers_for_rbr) { tables->trg_event_map= new_trg_event_map; lex->query_tables_last= &tables->next_global; } + else if (!WSREP_ON) + { + tables->slave_fk_event_map= new_trg_event_map; + lex->query_tables_last= &tables->next_global; + } } if (open_and_lock_tables(thd, rgi->tables_to_lock, FALSE, 0)) { @@ -11193,8 +11197,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) } /* remove trigger's tables */ - if (slave_run_triggers_for_rbr) - restore_empty_query_table_list(thd->lex); + restore_empty_query_table_list(thd->lex); #if defined(WITH_WSREP) && defined(HAVE_QUERY_CACHE) if (WSREP(thd) && thd->wsrep_exec_mode == REPL_RECV) @@ -11212,8 +11215,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) DBUG_RETURN(error); err: - if (slave_run_triggers_for_rbr) - restore_empty_query_table_list(thd->lex); + restore_empty_query_table_list(thd->lex); rgi->slave_close_thread_tables(thd); DBUG_RETURN(error); } diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 8e57ea437b6..b8d18abb50c 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4341,6 +4341,70 @@ bool table_already_fk_prelocked(TABLE_LIST *tl, LEX_STRING *db, return false; } +/** + Extend the table_list to include foreign tables for prelocking. + + @param[in] thd Thread context. + @param[in] prelocking_ctx Prelocking context of the statement. + @param[in] table_list Table list element for table. + @param[in] sp Routine body. + @param[out] need_prelocking Set to TRUE if method detects that prelocking + required, not changed otherwise. + + @retval FALSE Success. + @retval TRUE Failure (OOM). +*/ +inline bool +prepare_fk_prelocking_list(THD *thd, Query_tables_list *prelocking_ctx, + TABLE_LIST *table_list, bool *need_prelocking, + uint8 op) +{ + List <FOREIGN_KEY_INFO> fk_list; + List_iterator<FOREIGN_KEY_INFO> fk_list_it(fk_list); + FOREIGN_KEY_INFO *fk; + Query_arena *arena, backup; + + arena= thd->activate_stmt_arena_if_needed(&backup); + + table_list->table->file->get_parent_foreign_key_list(thd, &fk_list); + if (thd->is_error()) + { + if (arena) + thd->restore_active_arena(arena, &backup); + return TRUE; + } + + *need_prelocking= TRUE; + + while ((fk= fk_list_it++)) + { + // FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access + static bool can_write[]= { true, false, true, true, false, true }; + thr_lock_type lock_type; + + if ((op & (1 << TRG_EVENT_DELETE) && can_write[fk->delete_method]) + || (op & (1 << TRG_EVENT_UPDATE) && can_write[fk->update_method])) + lock_type= TL_WRITE_ALLOW_WRITE; + else + lock_type= TL_READ; + + if (table_already_fk_prelocked(prelocking_ctx->query_tables, + fk->foreign_db, fk->foreign_table, + lock_type)) + continue; + + TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST)); + tl->init_one_table_for_prelocking(fk->foreign_db->str, fk->foreign_db->length, + fk->foreign_table->str, fk->foreign_table->length, + NULL, lock_type, false, table_list->belong_to_view, + op, &prelocking_ctx->query_tables_last); + } + if (arena) + thd->restore_active_arena(arena, &backup); + + return FALSE; +} + /** Defines how prelocking algorithm for DML statements should handle table list @@ -4381,55 +4445,21 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx, add_tables_and_routines_for_triggers(thd, prelocking_ctx, table_list)) return TRUE; } - if (table_list->table->file->referenced_by_foreign_key()) { - List <FOREIGN_KEY_INFO> fk_list; - List_iterator<FOREIGN_KEY_INFO> fk_list_it(fk_list); - FOREIGN_KEY_INFO *fk; - Query_arena *arena, backup; - - arena= thd->activate_stmt_arena_if_needed(&backup); - - table_list->table->file->get_parent_foreign_key_list(thd, &fk_list); - if (thd->is_error()) - { - if (arena) - thd->restore_active_arena(arena, &backup); - return TRUE; - } - - *need_prelocking= TRUE; - - while ((fk= fk_list_it++)) - { - // FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access - static bool can_write[]= { true, false, true, true, false, true }; - uint8 op= table_list->trg_event_map; - thr_lock_type lock_type; - - if ((op & (1 << TRG_EVENT_DELETE) && can_write[fk->delete_method]) - || (op & (1 << TRG_EVENT_UPDATE) && can_write[fk->update_method])) - lock_type= TL_WRITE_ALLOW_WRITE; - else - lock_type= TL_READ; - - if (table_already_fk_prelocked(prelocking_ctx->query_tables, - fk->foreign_db, fk->foreign_table, - lock_type)) - continue; - - TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST)); - tl->init_one_table_for_prelocking(fk->foreign_db->str, fk->foreign_db->length, - fk->foreign_table->str, fk->foreign_table->length, - NULL, lock_type, false, table_list->belong_to_view, - op, &prelocking_ctx->query_tables_last); - } - if (arena) - thd->restore_active_arena(arena, &backup); + return (prepare_fk_prelocking_list(thd, prelocking_ctx, table_list, + need_prelocking, + table_list->trg_event_map)); } } + else if (table_list->slave_fk_event_map && + table_list->table->file->referenced_by_foreign_key()) + { + return (prepare_fk_prelocking_list(thd, prelocking_ctx, + table_list, need_prelocking, + table_list->slave_fk_event_map)); + } return FALSE; } diff --git a/sql/table.h b/sql/table.h index 9a864f7ce9f..57706655d9b 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2277,8 +2277,12 @@ struct TABLE_LIST Indicates what triggers we need to pre-load for this TABLE_LIST when opening an associated TABLE. This is filled after the parsed tree is created. + + slave_fk_event_map is filled on the slave side with bitmaps value + representing row-based event operation to help find and prelock + possible FK constrain-related child tables. */ - uint8 trg_event_map; + uint8 trg_event_map, slave_fk_event_map; /* TRUE <=> this table is a const one and was optimized away. */ bool optimized_away;

2 2

[Maria-developers] 3f48bc08b92: MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11
by sujatha 31 Dec '20

31 Dec '20

revision-id: 3f48bc08b92b2734c2af7408c246b96338675205 (mariadb-10.2.31-646-g3f48bc08b92) parent(s): 78292047a4747ccd9210ba36a185a1dbe825de89 author: Sujatha committer: Sujatha timestamp: 2020-12-31 15:43:53 +0530 message: MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11 Problem: ======= Upon deleting or updating a row in a parent table (with primary key), if the child table has virtual column and an associated key with ON UPDATE CASCADE/ON DELETE CASCADE, it will result in slave crash. Analysis: ======== Tables which are related through foreign key require prelocking similar to triggers. i.e If a table has triggers/foreign keys we should add all tables and routines used by them to the prelocking set. This prelocking happens during 'open_and_lock_tables' call. Each table being opened is checked for foreign key references. If foreign key reference exists then the child table is opened and it is linked to the table_list. Upon any modification to parent table its corresponding child tables are retried from table_list and they are updated accordingly. This prelocking work fine on master. On slave prelocking works for following cases. - Statement/mixed based replication - In row based replication when trigger execution is enabled through 'slave_run_triggers_for_rbr=YES/LOGGING/ENFORCE' Otherwise it results in an assert/crash, as the parent table will not find the corresponding child table and it will be NULL. Dereferencing NULL pointer leads to slave server exit. Fix: === Introduce a new 'slave_fk_event_map' flag similar to 'trg_event_map'. This flag will ensure that when foreign key is enabled in row based replication all the parent and child tables are prelocked, so that parent is able to locate the child table. --- mysql-test/suite/rpl/r/rpl_row_vcol_crash.result | 381 +++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_row_vcol_crash.test | 410 +++++++++++++++++++++++ sql/log_event.cc | 44 ++- sql/sql_base.cc | 6 +- sql/table.h | 6 +- 5 files changed, 821 insertions(+), 26 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result new file mode 100644 index 00000000000..e2a5d9b65e5 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_vcol_crash.result @@ -0,0 +1,381 @@ +include/master-slave.inc +[connection master] +# +# Test case: KEY on a virtual column with ON DELETE CASCADE +# +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3); +CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, +t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (90,1,NULL); +INSERT INTO t2 VALUES (91,2,default); +DELETE FROM t1 WHERE id=1; +connection slave; +# +# Verify data consistency on slave +# +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +connection master; +DROP TABLE t2,t1; +connection slave; +# +# Test case: Verify cascading on delete with multiple levels of child +# tables works fine. +# Parent table: users +# Child tables: matchmaking_groups, matchmaking_group_users +# Parent table: matchmaking_groups +# Child tables: matchmaking_group_users, matchmaking_group_maps +# +# Verify ON DELETE CASCASE works fine when a row is deleted +# from parent table users +# matchmaking_groups->matchmaking_group_users->matchmaking_group_maps +# users->matchmaking_group_users->matchmaking_group_maps +# +connection master; +CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +name VARCHAR(32) NOT NULL DEFAULT '' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE matchmaking_groups ( +id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +host_user_id INT UNSIGNED NOT NULL UNIQUE, +v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col), +CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id) +ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE matchmaking_group_users ( +matchmaking_group_id BIGINT UNSIGNED NOT NULL, +user_id INT UNSIGNED NOT NULL, +v_col1 int as (user_id+1) virtual, KEY (v_col1), +PRIMARY KEY (matchmaking_group_id,user_id), +UNIQUE KEY user_id (user_id), +CONSTRAINT FOREIGN KEY (matchmaking_group_id) +REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT FOREIGN KEY (user_id) +REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE matchmaking_group_maps ( +matchmaking_group_id BIGINT UNSIGNED NOT NULL, +map_id TINYINT UNSIGNED NOT NULL, +v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2), +PRIMARY KEY (matchmaking_group_id,map_id), +CONSTRAINT FOREIGN KEY (matchmaking_group_id) +REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +connection slave; +connection master; +INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar'); +INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default); +DELETE FROM matchmaking_groups WHERE id = 10; +connection slave; +# +# No rows should be returned as ON DELETE CASCASE should have removed +# corresponding rows from child tables. There should not any mismatch +# of 'id' field between parent->child. +# +SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); +matchmaking_group_id user_id v_col1 +SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); +matchmaking_group_id map_id v_col2 +# +# Rows with id=11 should be present +# +SELECT * FROM matchmaking_group_users; +matchmaking_group_id user_id v_col1 +11 2 3 +SELECT * FROM matchmaking_group_maps; +matchmaking_group_id map_id v_col2 +11 66 67 +connection master; +DELETE FROM users WHERE id = 2; +connection slave; +# +# No rows should be present in both the child tables +# +SELECT * FROM matchmaking_group_users; +matchmaking_group_id user_id v_col1 +SELECT * FROM matchmaking_group_maps; +matchmaking_group_id map_id v_col2 +connection master; +DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users; +connection slave; +# +# Test case: ON UPDATE CASCADE scenario +# +connection master; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 80); +CREATE TABLE t2 (a INT KEY, b INT, +v_col int as (b+1) virtual, KEY (v_col), +CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (51, 1, default); +connection slave; +connection master; +UPDATE t1 SET a = 50 WHERE a = 1; +# +# Master: Verify that ON UPDATE CASCADE works fine +# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +# +SELECT * FROM t2 WHERE b=50; +a b v_col +51 50 51 +connection slave; +# +# Slave: Verify that ON UPDATE CASCADE works fine +# old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +# +SELECT * FROM t2 WHERE b=50; +a b v_col +51 50 51 +connection master; +DROP TABLE t2, t1; +connection slave; +# +# Test case: When triggers are defined on master they should be +# replicated as part of row events and they should be +# applied on slave with the default +# slave_run_tiggers_for_rbr=NO +# +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (2),(3); +connection slave; +SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr'; +Variable_name Value +slave_run_triggers_for_rbr NO +# +# As two rows are inserted in table 't1', two rows should get inserted +# into table 't2' as part of trigger. +# +include/assert.inc [Table t2 should have two rows.] +connection master; +DROP TABLE t1,t2; +connection slave; +# +# Test case: On master create triggers and tables with foreign key +# relation. Upon replication to slave verify their results +# are fine. Master and Slave should be in sync. +# +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1); +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# As two rows are inserted in table 't1', two rows should get inserted +# into table 't3' as part of trigger. +# +include/assert.inc [Table t3 should have two rows.] +# +# Verify ON DELETE CASCASE correctness +# +connection master; +DELETE FROM t1 WHERE id=2; +connection slave; +connection master; +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +include/diff_tables.inc [master:test.t3, slave:test.t3] +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case: Triggers are present only on slave and +# 'slave_run_triggers_for_rbr=NO' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= NO;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr NO +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), +KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 0 +# +include/assert.inc [Table t3 should have zero rows.] +connection master; +DELETE FROM t1 WHERE id=2; +connection slave; +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +# +# Verify t1,t2 are consistent on slave. +# +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +connection master; +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case: Triggers are present only on slave and +# 'slave_run_triggers_for_rbr=YES' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= YES;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr YES +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), +KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 2 +# +include/assert.inc [Table t3 should have two rows.] +connection master; +DELETE FROM t1 WHERE id=2; +connection slave; +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +# +# Verify t1,t2 are consistent on slave. +# +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +connection master; +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case: Triggers and Foreign Keys are present only on slave and +# 'slave_run_triggers_for_rbr=NO' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= NO;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr NO +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +SET sql_log_bin=0; +CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB; +SET sql_log_bin=1; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 0 +# +include/assert.inc [Table t3 should have zero rows.] +connection master; +DELETE FROM t1 WHERE id=2; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have two rows +SELECT * FROM t2; +t1_id v_col +2 3 +3 4 +connection slave; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have one row on slave due to ON DELETE CASCASE +SELECT * FROM t2; +t1_id v_col +3 4 +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +connection master; +DROP TABLE t3,t2,t1; +connection slave; +# +# Test case: Triggers are Foreign Keys are present only on slave and +# 'slave_run_triggers_for_rbr=YES' +# +connection slave; +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr= YES;; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; +Variable_name Value +slave_run_triggers_for_rbr YES +connection master; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +SET sql_log_bin=0; +CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB; +SET sql_log_bin=1; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +connection slave; +CREATE TABLE t2 (t1_id INT NOT NULL, +v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), +CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); +connection master; +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +connection slave; +# +# Count must be 2 +# +include/assert.inc [Table t3 should have two rows.] +connection master; +DELETE FROM t1 WHERE id=2; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have two rows +SELECT * FROM t2; +t1_id v_col +2 3 +3 4 +connection slave; +# t1: Should have one row +SELECT * FROM t1; +id +3 +# t2: Should have one row on slave due to ON DELETE CASCASE +SELECT * FROM t2; +t1_id v_col +3 4 +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; +connection master; +DROP TABLE t3,t2,t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test new file mode 100644 index 00000000000..2ed7c219aeb --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_vcol_crash.test @@ -0,0 +1,410 @@ +# ==== Purpose ==== +# +# Test verifies that, slave doesn't report any assert on UPDATE or DELETE of +# row which tries to update the virtual columns with associated KEYs. +# +# ==== References ==== +# +# MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11 +# + +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc +--source include/master-slave.inc + + +--echo # +--echo # Test case: KEY on a virtual column with ON DELETE CASCADE +--echo # +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3); + +CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, + t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), + CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (90,1,NULL); +INSERT INTO t2 VALUES (91,2,default); + +# Following query results in an assert on slave +DELETE FROM t1 WHERE id=1; +--sync_slave_with_master + +--echo # +--echo # Verify data consistency on slave +--echo # +--let $diff_tables= master:test.t1, slave:test.t1 +--source include/diff_tables.inc +--let $diff_tables= master:test.t2, slave:test.t2 +--source include/diff_tables.inc + +--connection master +DROP TABLE t2,t1; +--sync_slave_with_master + +--echo # +--echo # Test case: Verify cascading on delete with multiple levels of child +--echo # tables works fine. +--echo # Parent table: users +--echo # Child tables: matchmaking_groups, matchmaking_group_users +--echo # Parent table: matchmaking_groups +--echo # Child tables: matchmaking_group_users, matchmaking_group_maps +--echo # +--echo # Verify ON DELETE CASCASE works fine when a row is deleted +--echo # from parent table users +--echo # matchmaking_groups->matchmaking_group_users->matchmaking_group_maps +--echo # users->matchmaking_group_users->matchmaking_group_maps +--echo # + +--connection master +CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, + name VARCHAR(32) NOT NULL DEFAULT '' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE matchmaking_groups ( + id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, + host_user_id INT UNSIGNED NOT NULL UNIQUE, + v_col INT AS (host_user_id+1) VIRTUAL, KEY (v_col), + CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE matchmaking_group_users ( + matchmaking_group_id BIGINT UNSIGNED NOT NULL, + user_id INT UNSIGNED NOT NULL, + v_col1 int as (user_id+1) virtual, KEY (v_col1), + PRIMARY KEY (matchmaking_group_id,user_id), + UNIQUE KEY user_id (user_id), + CONSTRAINT FOREIGN KEY (matchmaking_group_id) + REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FOREIGN KEY (user_id) + REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE matchmaking_group_maps ( + matchmaking_group_id BIGINT UNSIGNED NOT NULL, + map_id TINYINT UNSIGNED NOT NULL, + v_col2 INT AS (map_id+1) VIRTUAL, KEY (v_col2), + PRIMARY KEY (matchmaking_group_id,map_id), + CONSTRAINT FOREIGN KEY (matchmaking_group_id) + REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +--sync_slave_with_master + +--connection master +INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar'); +INSERT INTO matchmaking_groups VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_users VALUES (10,1,default),(11,2,default); +INSERT INTO matchmaking_group_maps VALUES (10,55,default),(11,66,default); + +DELETE FROM matchmaking_groups WHERE id = 10; +--sync_slave_with_master + +--echo # +--echo # No rows should be returned as ON DELETE CASCASE should have removed +--echo # corresponding rows from child tables. There should not any mismatch +--echo # of 'id' field between parent->child. +--echo # +SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); +SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups); + +--echo # +--echo # Rows with id=11 should be present +--echo # +SELECT * FROM matchmaking_group_users; +SELECT * FROM matchmaking_group_maps; + +--connection master +DELETE FROM users WHERE id = 2; +--sync_slave_with_master + +--echo # +--echo # No rows should be present in both the child tables +--echo # +SELECT * FROM matchmaking_group_users; +SELECT * FROM matchmaking_group_maps; + +--connection master +DROP TABLE matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users; +--sync_slave_with_master + +--echo # +--echo # Test case: ON UPDATE CASCADE scenario +--echo # + +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 80); + +CREATE TABLE t2 (a INT KEY, b INT, + v_col int as (b+1) virtual, KEY (v_col), + CONSTRAINT b FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (51, 1, default); +--sync_slave_with_master + +--connection master +UPDATE t1 SET a = 50 WHERE a = 1; + +--echo # +--echo # Master: Verify that ON UPDATE CASCADE works fine +--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +--echo # +SELECT * FROM t2 WHERE b=50; +--sync_slave_with_master + +--echo # +--echo # Slave: Verify that ON UPDATE CASCADE works fine +--echo # old_row: (51, 1, 2) ON UPDATE New_row: (51, 50, 51) +--echo # +SELECT * FROM t2 WHERE b=50; + +--connection master +DROP TABLE t2, t1; +--sync_slave_with_master + +--echo # +--echo # Test case: When triggers are defined on master they should be +--echo # replicated as part of row events and they should be +--echo # applied on slave with the default +--echo # slave_run_tiggers_for_rbr=NO +--echo # + +# In row-based replication, the binary log contains row changes. It will have +# both the changes made by the statement itself, and the changes made by the +# triggers that were invoked by the statement. Slave server(s) do not need to +# run triggers for row changes they are applying. Hence verify that this +# property remains the same and data should be available as if trigger was +# executed. Please note by default slave_run_tiggers_for_rbr=NO. + +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (2),(3); +--sync_slave_with_master + +SHOW GLOBAL VARIABLES LIKE 'slave_run_triggers_for_rbr'; +--echo # +--echo # As two rows are inserted in table 't1', two rows should get inserted +--echo # into table 't2' as part of trigger. +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t2 +--let $assert_text= Table t2 should have two rows. +--source include/assert.inc + +--connection master +DROP TABLE t1,t2; +--sync_slave_with_master + +--echo # +--echo # Test case: On master create triggers and tables with foreign key +--echo # relation. Upon replication to slave verify their results +--echo # are fine. Master and Slave should be in sync. +--echo # +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), + CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t3 VALUES (1); + +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +--sync_slave_with_master + +--echo # +--echo # As two rows are inserted in table 't1', two rows should get inserted +--echo # into table 't3' as part of trigger. +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t3 +--let $assert_text= Table t3 should have two rows. +--source include/assert.inc + +--echo # +--echo # Verify ON DELETE CASCASE correctness +--echo # +--connection master +DELETE FROM t1 WHERE id=2; +--sync_slave_with_master + +--connection master +--let $diff_tables= master:test.t1, slave:test.t1 +--source include/diff_tables.inc +--let $diff_tables= master:test.t2, slave:test.t2 +--source include/diff_tables.inc +--let $diff_tables= master:test.t3, slave:test.t3 +--source include/diff_tables.inc + +DROP TABLE t3,t2,t1; +--sync_slave_with_master + +# +# Test case: Triggers only on slave +# +--write_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc PROCEDURE + if ($slave_run_triggers_for_rbr == '') { + --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr + } + +--connection slave +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; + +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), + KEY (t1_id), CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +--sync_slave_with_master + +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); + +--connection master +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +--sync_slave_with_master + +if ($slave_run_triggers_for_rbr == 'NO') { +--echo # +--echo # Count must be 0 +--echo # +--let $assert_cond= COUNT(*) = 0 FROM t3 +--let $assert_text= Table t3 should have zero rows. +--source include/assert.inc +} +if ($slave_run_triggers_for_rbr == 'YES') { +--echo # +--echo # Count must be 2 +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t3 +--let $assert_text= Table t3 should have two rows. +--source include/assert.inc +} + +--connection master +DELETE FROM t1 WHERE id=2; +--sync_slave_with_master +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; + +--echo # +--echo # Verify t1,t2 are consistent on slave. +--echo # +--let $diff_tables= master:test.t1, slave:test.t1 +--source include/diff_tables.inc +--let $diff_tables= master:test.t2, slave:test.t2 +--source include/diff_tables.inc + +--connection master +DROP TABLE t3,t2,t1; +--sync_slave_with_master +#END OF +PROCEDURE + +--echo # +--echo # Test case: Triggers are present only on slave and +--echo # 'slave_run_triggers_for_rbr=NO' +--echo # +--let $slave_run_triggers_for_rbr=NO +--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc + +--echo # +--echo # Test case: Triggers are present only on slave and +--echo # 'slave_run_triggers_for_rbr=YES' +--echo # +--let $slave_run_triggers_for_rbr=YES +--source $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc +--remove_file $MYSQLTEST_VARDIR/tmp/trig_on_slave.inc + +# +# Test case: Trigger and Foreign Key are present only on slave +# +--write_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc PROCEDURE + if ($slave_run_triggers_for_rbr == '') { + --die !!!ERROR IN TEST: you must set $slave_run_triggers_for_rbr + } + +--connection slave +SET @save_slave_run_triggers_for_rbr= @@GLOBAL.slave_run_triggers_for_rbr; +--eval SET GLOBAL slave_run_triggers_for_rbr= $slave_run_triggers_for_rbr; +SHOW GLOBAL VARIABLES LIKE '%slave_run_triggers_for_rbr%'; + +--connection master +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +SET sql_log_bin=0; +CREATE TABLE t2 (t1_id INT NOT NULL,v_col INT AS (t1_id+1) VIRTUAL) ENGINE=INNODB; +SET sql_log_bin=1; +CREATE TABLE t3 (count INT NOT NULL) ENGINE=InnoDB; +--sync_slave_with_master + +# Have foreign key and trigger on slave. +CREATE TABLE t2 (t1_id INT NOT NULL, + v_col INT AS (t1_id+1) VIRTUAL, KEY (v_col), KEY (t1_id), + CONSTRAINT a FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TRIGGER trg AFTER INSERT ON t2 FOR EACH ROW INSERT INTO t3 VALUES (1); + +--connection master +INSERT INTO t1 VALUES (2),(3); +INSERT INTO t2 VALUES (2, default), (3, default); +--sync_slave_with_master + +if ($slave_run_triggers_for_rbr == 'NO') { +--echo # +--echo # Count must be 0 +--echo # +--let $assert_cond= COUNT(*) = 0 FROM t3 +--let $assert_text= Table t3 should have zero rows. +--source include/assert.inc +} +if ($slave_run_triggers_for_rbr == 'YES') { +--echo # +--echo # Count must be 2 +--echo # +--let $assert_cond= COUNT(*) = 2 FROM t3 +--let $assert_text= Table t3 should have two rows. +--source include/assert.inc +} + +--connection master +DELETE FROM t1 WHERE id=2; +--echo # t1: Should have one row +SELECT * FROM t1; +--echo # t2: Should have two rows +SELECT * FROM t2; +--sync_slave_with_master +--echo # t1: Should have one row +SELECT * FROM t1; +--echo # t2: Should have one row on slave due to ON DELETE CASCASE +SELECT * FROM t2; +SET GLOBAL slave_run_triggers_for_rbr= @save_slave_run_triggers_for_rbr; + +--connection master +DROP TABLE t3,t2,t1; +--sync_slave_with_master +#END OF +PROCEDURE + +--echo # +--echo # Test case: Triggers and Foreign Keys are present only on slave and +--echo # 'slave_run_triggers_for_rbr=NO' +--echo # +--let $slave_run_triggers_for_rbr=NO +--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc + +--echo # +--echo # Test case: Triggers are Foreign Keys are present only on slave and +--echo # 'slave_run_triggers_for_rbr=YES' +--echo # +--let $slave_run_triggers_for_rbr=YES +--source $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc +--remove_file $MYSQLTEST_VARDIR/tmp/trig_fk_on_slave.inc + +--source include/rpl_end.inc diff --git a/sql/log_event.cc b/sql/log_event.cc index c649e1f64fa..10aa0afbad8 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -10718,7 +10718,7 @@ int Rows_log_event::do_add_row_data(uchar *row_data, size_t length) There was the same problem with MERGE MYISAM tables and so here we try to go the same way. */ -static void restore_empty_query_table_list(LEX *lex) +inline void restore_empty_query_table_list(LEX *lex) { if (lex->first_not_own_table()) (*lex->first_not_own_table()->prev_global)= NULL; @@ -10733,6 +10733,8 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) TABLE* table; DBUG_ENTER("Rows_log_event::do_apply_event(Relay_log_info*)"); int error= 0; + LEX *lex= thd->lex; + uint8 new_trg_event_map= get_trg_event_map(); /* If m_table_id == ~0ULL, then we have a dummy event that does not contain any data. In that case, we just remove all tables in the @@ -10823,27 +10825,25 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) DBUG_ASSERT(!debug_sync_set_action(thd, STRING_WITH_LEN(action))); };); - if (slave_run_triggers_for_rbr) - { - LEX *lex= thd->lex; - uint8 new_trg_event_map= get_trg_event_map(); - - /* - Trigger's procedures work with global table list. So we have to add - rgi->tables_to_lock content there to get trigger's in the list. + /* + Trigger's procedures work with global table list. So we have to add + rgi->tables_to_lock content there to get trigger's in the list. - Then restore_empty_query_table_list() restore the list as it was - */ - DBUG_ASSERT(lex->query_tables == NULL); - if ((lex->query_tables= rgi->tables_to_lock)) - rgi->tables_to_lock->prev_global= &lex->query_tables; + Then restore_empty_query_table_list() restore the list as it was + */ + DBUG_ASSERT(lex->query_tables == NULL); + if ((lex->query_tables= rgi->tables_to_lock)) + rgi->tables_to_lock->prev_global= &lex->query_tables; - for (TABLE_LIST *tables= rgi->tables_to_lock; tables; - tables= tables->next_global) - { + for (TABLE_LIST *tables= rgi->tables_to_lock; tables; + tables= tables->next_global) + { + if (slave_run_triggers_for_rbr) tables->trg_event_map= new_trg_event_map; - lex->query_tables_last= &tables->next_global; - } + else + tables->slave_fk_event_map= new_trg_event_map; + + lex->query_tables_last= &tables->next_global; } if (open_and_lock_tables(thd, rgi->tables_to_lock, FALSE, 0)) { @@ -11193,8 +11193,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) } /* remove trigger's tables */ - if (slave_run_triggers_for_rbr) - restore_empty_query_table_list(thd->lex); + restore_empty_query_table_list(thd->lex); #if defined(WITH_WSREP) && defined(HAVE_QUERY_CACHE) if (WSREP(thd) && thd->wsrep_exec_mode == REPL_RECV) @@ -11212,8 +11211,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) DBUG_RETURN(error); err: - if (slave_run_triggers_for_rbr) - restore_empty_query_table_list(thd->lex); + restore_empty_query_table_list(thd->lex); rgi->slave_close_thread_tables(thd); DBUG_RETURN(error); } diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 8e57ea437b6..a7cddbca16a 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4371,7 +4371,8 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx, /* We rely on a caller to check that table is going to be changed. */ DBUG_ASSERT(table_list->lock_type >= TL_WRITE_ALLOW_WRITE); - if (table_list->trg_event_map) + if (table_list->trg_event_map || + (thd->rgi_slave && thd->is_current_stmt_binlog_format_row())) { if (table_list->table->triggers) { @@ -4405,7 +4406,8 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx, { // FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access static bool can_write[]= { true, false, true, true, false, true }; - uint8 op= table_list->trg_event_map; + uint8 op= ((table_list->trg_event_map) ? table_list->trg_event_map : + table_list->slave_fk_event_map); thr_lock_type lock_type; if ((op & (1 << TRG_EVENT_DELETE) && can_write[fk->delete_method]) diff --git a/sql/table.h b/sql/table.h index 9a864f7ce9f..57706655d9b 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2277,8 +2277,12 @@ struct TABLE_LIST Indicates what triggers we need to pre-load for this TABLE_LIST when opening an associated TABLE. This is filled after the parsed tree is created. + + slave_fk_event_map is filled on the slave side with bitmaps value + representing row-based event operation to help find and prelock + possible FK constrain-related child tables. */ - uint8 trg_event_map; + uint8 trg_event_map, slave_fk_event_map; /* TRUE <=> this table is a const one and was optimized away. */ bool optimized_away;

1 0

Re: [Maria-developers] [Commits] cdc305c8dd8: MDEV-19620: Changing join_buffer_size causes different results
by Sergey Petrunia 30 Dec '20

30 Dec '20

Hi Varun, Please find input below. On Mon, Dec 28, 2020 at 02:34:40PM +0530, varun wrote: > revision-id: cdc305c8dd89a726e09e5fe70ff890d06609cbfb (mariadb-10.3.21-309-gcdc305c8dd8) > parent(s): 043bd85a574a88856ab9c6d497e682ed06fe45e9 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2020-12-28 14:12:14 +0530 > message: > > MDEV-19620: Changing join_buffer_size causes different results > > The scenario here is that query refinement phase decides to use a hash join. > When the join buffers are allocated in the JOIN::init_join_caches, for a table > the size exceeds the value for join_buffer_space_limit (which is the limit of the > space available for all join buffers). When this happens then we disallow join > buffering for the table, this is done in the revise_cache_usage and set_join_cache_denial. > > In this issue the hash key is created on an index for which ref access is possible, so > when we disallow hash join then instead of switching to REF access we switch to a table > scan. This is a problem because the equijoin conditions for which a lookup can be made > are not attached to the table(or are not evaluated for the table). This leads to incorrect > results. > > The fix here would be to switch to using a lookup because it was picked by the join planner > to be more efficient than the table scan. > > --- > mysql-test/main/join_cache.result | 138 ++++++++++++++++++++++++++++++++++++++ > mysql-test/main/join_cache.test | 105 +++++++++++++++++++++++++++++ > sql/sql_select.cc | 69 +++++++++++++++---- > sql/sql_select.h | 6 ++ > 4 files changed, 304 insertions(+), 14 deletions(-) > > diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result > index 3d1d91df997..e58503f422f 100644 > --- a/mysql-test/main/join_cache.result > +++ b/mysql-test/main/join_cache.result > @@ -6128,4 +6128,142 @@ EXPLAIN > } > } > drop table t1,t2,t3; > +# > +# MDEV-19620: Changing join_buffer_size causes different results > +# > +SET @save_join_cache_level= @@join_cache_level; > +SET @save_join_buffer_size= @@join_buffer_size; > +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; > +SET join_cache_level = 3; > +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('1','v'),('7','s'); > +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500)) ENGINE=MyISAM; > +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','p'),(3,'1','q'); > +INSERT INTO t2 VALUES (4,'7','g'),(5,'4','p'),(6,'1','q'); > +INSERT INTO t2 VALUES (16,'7','g'),(17,'4','p'),(28,'1','q'); > +# > +# Hash join + table Scan on t2 > +# > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 ALL NULL NULL NULL NULL 9 Using where > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +i2 c2 pk3 i3 c3 > +1 v NULL NULL NULL > +7 s NULL NULL NULL > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 hash_ALL NULL #hash#$hj 503 test.t1.c2 9 Using where; Using join buffer (flat, BNLH join) > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +i2 c2 pk3 i3 c3 > +1 v NULL NULL NULL > +7 s NULL NULL NULL > +# > +# HASH join + ref access on t2 > +# > +ALTER TABLE t2 ADD KEY k1(c3); > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 ref k1 k1 503 test.t1.c2 2 Using where > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +i2 c2 pk3 i3 c3 > +1 v NULL NULL NULL > +7 s NULL NULL NULL > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 hash_ALL k1 #hash#k1 503 test.t1.c2 9 Using where; Using join buffer (flat, BNLH join) > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +i2 c2 pk3 i3 c3 > +1 v NULL NULL NULL > +7 s NULL NULL NULL > +# > +# Hash join + index scan on t2 > +# > +ALTER TABLE t2 DROP KEY k1; > +ALTER TABLE t2 ADD KEY k1(i3,c3); > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 index NULL k1 806 NULL 9 Using where; Using index > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +c3 i3 i2 c2 > +NULL NULL 1 v > +NULL NULL 7 s > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 hash_index NULL #hash#$hj:k1 503:806 test.t1.c2 9 Using where; Using index; Using join buffer (flat, BNLH join) > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +c3 i3 i2 c2 > +NULL NULL 1 v > +NULL NULL 7 s > +DROP TABLE t1,t2; > +# > +# Hash join + range scan on t2 > +# > +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); > +INSERT INTO t1 VALUES ('1','v'),('7','s'); > +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500), INDEX(i3,c3)); > +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where > +1 SIMPLE t2 range i3 i3 303 NULL 2 Using index condition; Using where > +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > +i2 c2 pk3 i3 c3 > +7 s 2 4 s > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where > +1 SIMPLE t2 hash_range i3 #hash#$hj:i3 503:303 test.t1.c2 2 Using where; Using join buffer (flat, BNLH join) > +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > +i2 c2 pk3 i3 c3 > +7 s 2 4 s > +DROP TABLE t1,t2; > +# > +# Hash join + eq ref access on t2 > +# > +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); > +INSERT INTO t1 VALUES ('1','v'),('7','s'); > +CREATE TABLE t2 (pk3 INT, i3 VARCHAR(300), c3 VARCHAR(500) PRIMARY KEY); > +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 502 test.t1.c2 1 Using where > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +c3 i3 i2 c2 > +NULL NULL 1 v > +s 4 7 s > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 502 test.t1.c2 3 Using where; Using join buffer (flat, BNLH join) > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +c3 i3 i2 c2 > +NULL NULL 1 v > +s 4 7 s > +DROP TABLE t1,t2; > +SET @save_join_cache_level= @@join_cache_level; > +SET @save_join_buffer_size= @@join_buffer_size; > +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; > set @@optimizer_switch=@save_optimizer_switch; > diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test > index 91339c2cb21..6670c62516b 100644 > --- a/mysql-test/main/join_cache.test > +++ b/mysql-test/main/join_cache.test > @@ -4054,5 +4054,110 @@ where > > drop table t1,t2,t3; > > +--echo # > +--echo # MDEV-19620: Changing join_buffer_size causes different results > +--echo # > + > +SET @save_join_cache_level= @@join_cache_level; > +SET @save_join_buffer_size= @@join_buffer_size; > +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; > +SET join_cache_level = 3; > + > +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('1','v'),('7','s'); > +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500)) ENGINE=MyISAM; > +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','p'),(3,'1','q'); > +INSERT INTO t2 VALUES (4,'7','g'),(5,'4','p'),(6,'1','q'); > +INSERT INTO t2 VALUES (16,'7','g'),(17,'4','p'),(28,'1','q'); > + > +--echo # > +--echo # Hash join + table Scan on t2 > +--echo # > + > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > + > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > + > +--echo # > +--echo # HASH join + ref access on t2 > +--echo # > + > +ALTER TABLE t2 ADD KEY k1(c3); > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > + > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); > + > +--echo # > +--echo # Hash join + index scan on t2 > +--echo # > +ALTER TABLE t2 DROP KEY k1; > +ALTER TABLE t2 ADD KEY k1(i3,c3); > + > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > + > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > + > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +DROP TABLE t1,t2; > + > +--echo # > +--echo # Hash join + range scan on t2 > +--echo # > + > +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); > +INSERT INTO t1 VALUES ('1','v'),('7','s'); > +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500), INDEX(i3,c3)); > +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); > + > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > + > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > + > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; > + > +DROP TABLE t1,t2; > + > +--echo # > +--echo # Hash join + eq ref access on t2 > +--echo # > + > +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); > +INSERT INTO t1 VALUES ('1','v'),('7','s'); > +CREATE TABLE t2 (pk3 INT, i3 VARCHAR(300), c3 VARCHAR(500) PRIMARY KEY); > +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); > + > +set join_buffer_size=1024; > +set join_buffer_space_limit=2048; > + > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > + > +set join_buffer_space_limit=262144; > +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); > +DROP TABLE t1,t2; > + > +SET @save_join_cache_level= @@join_cache_level; > +SET @save_join_buffer_size= @@join_buffer_size; > +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; > + > # The following command must be the last one in the file > set @@optimizer_switch=@save_optimizer_switch; > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 203422f0b43..8b7deb98dc6 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -111,6 +111,7 @@ static bool best_extension_by_limited_search(JOIN *join, > uint prune_level, > uint use_cond_selectivity); > static uint determine_search_depth(JOIN* join); > +static void pick_table_access_method(JOIN_TAB *tab); > C_MODE_START > static int join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2); > static int join_tab_cmp_straight(const void *dummy, const void* ptr1, const void* ptr2); > @@ -10081,6 +10082,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, > j->ref.disable_cache= FALSE; > j->ref.null_ref_part= NO_REF_PART; > j->ref.const_ref_part_map= 0; > + j->ref.not_null_keyparts= 0; > keyuse=org_keyuse; > > store_key **ref_key= j->ref.key_copy; > @@ -10173,23 +10175,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, > } > } /* not ftkey */ > *ref_key=0; // end_marker > + j->ref.not_null_keyparts= not_null_keyparts; > if (j->type == JT_FT) > DBUG_RETURN(0); > - ulong key_flags= j->table->actual_key_flags(keyinfo); > if (j->type == JT_CONST) > j->table->const_table= 1; > - else if (!((keyparts == keyinfo->user_defined_key_parts && > - ( > - (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || > - /* Unique key and all keyparts are NULL rejecting */ > - ((key_flags & HA_NOSAME) && keyparts == not_null_keyparts) > - )) || > - /* true only for extended keys */ > - (keyparts > keyinfo->user_defined_key_parts && > - MY_TEST(key_flags & HA_EXT_NOSAME) && > - keyparts == keyinfo->ext_key_parts) > - ) || > - null_ref_key) > + else if (!j->is_eq_ref_access()|| null_ref_key) > { > /* Must read with repeat */ > j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; > @@ -11582,11 +11573,25 @@ void set_join_cache_denial(JOIN_TAB *join_tab) > don't do join buffering for the first table in sjm nest. > */ > join_tab[-1].next_select= sub_select; > - if (join_tab->type == JT_REF && join_tab->is_ref_for_hash_join()) > + if ((join_tab->type == JT_REF || join_tab->type == JT_HASH) && > + join_tab->is_ref_for_hash_join()) > { > join_tab->type= JT_ALL; > join_tab->ref.key_parts= 0; Do we ever get here if join_tab->type == JT_REF? I think we don't. If this is so, please remove it. > } > + > + if (join_tab->type == JT_HASH && !join_tab->is_ref_for_hash_join()) > + { > + join_tab->type= join_tab->is_eq_ref_access() ? JT_EQ_REF : JT_REF; > + pick_table_access_method(join_tab); > + } Also please add a comment explaining that the join optimizer's choice was ref access and we fall back to that. > + > + if (join_tab->type == JT_HASH_NEXT) > + { > + join_tab->type = JT_NEXT; > + DBUG_ASSERT(join_tab->ref.key_parts == 0); > + } > + > join_tab->join->return_tab= join_tab; > } > } > @@ -27954,6 +27959,42 @@ void JOIN_TAB::partial_cleanup() > } > > > +/* > + @brief > + Check if the access method for the table is EQ_REF access or not > + > + @retval > + TRUE EQ_REF access > + FALSE Otherwise > +*/ > +bool JOIN_TAB::is_eq_ref_access() > +{ > + > + KEY *keyinfo; > + if (!is_hash_join_key_no(ref.key)) > + keyinfo= table->key_info + ref.key; > + else > + keyinfo= hj_key; This code now looks as if it was possible that hash join is used and also eq_ref is used. Is this really possible? I've tried catching an example of this with MTR and I wasn't able to do so. Maybe we should just return false if hash join is used and also a comment about this? > + > + uint keyparts= ref.key_parts; > + ulong key_flags= table->actual_key_flags(keyinfo); > + if ( (keyparts == keyinfo->user_defined_key_parts && > + ( > + (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || > + /* Unique key and all keyparts are NULL rejecting */ > + ((key_flags & HA_NOSAME) && keyparts == ref.not_null_keyparts) > + ) > + ) || > + /* true only for extended keys */ > + (keyparts > keyinfo->user_defined_key_parts && > + MY_TEST(key_flags & HA_EXT_NOSAME) && > + keyparts == keyinfo->ext_key_parts) > + ) > + return true; > + return false; > +} > + > + > /** > @} (end of group Query_Optimizer) > */ BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

2 1

Re: [Maria-developers] 33787ea47a3: MDEV-6899: Database Dump has Syntax Error When Importing
by Sergei Golubchik 24 Dec '20

24 Dec '20

Hi, Anel! On Dec 23, Anel Husakovic wrote: > revision-id: 33787ea47a3 (mariadb-10.2.31-588-g33787ea47a3) > parent(s): ceef26cf866 > author: Anel Husakovic <anel(a)mariadb.org> > committer: Anel Husakovic <anel(a)mariadb.org> > timestamp: 2020-11-17 15:11:14 +0100 > message: > > MDEV-6899: Database Dump has Syntax Error When Importing > > --- > mysql-test/r/mysqldump.result | 32 ++++++++++++++++++++++++++++++++ > mysql-test/t/mysqldump.test | 26 ++++++++++++++++++++++++++ > sql/events.cc | 10 ++++++++-- > sql/sql_show.cc | 4 +++- > 4 files changed, 69 insertions(+), 3 deletions(-) > > diff --git a/sql/events.cc b/sql/events.cc > index abac2833833..2880113bc75 100644 > --- a/sql/events.cc > +++ b/sql/events.cc > @@ -680,6 +680,12 @@ send_show_create_event(THD *thd, Event_timed *et, Protocol *protocol) > if (et->get_create_event(thd, &show_str)) > DBUG_RETURN(TRUE); > > + LEX_STRING def_show_str= show_str.lex_string(); > + if (!strcmp(&def_show_str.str[def_show_str.length -1], ";")) > + { > + --def_show_str.length; > + def_show_str.str[def_show_str.length]='\0'; > + } No, this is clearly wrong. The bug is that semicolon even gets into the event definition, see other commands in the bug report. You should fix that, not strip it away in SHOW CREATE, which will only hide the bug. Why is the semicolon parsed as a part of the event anyway? > field_list.push_back(new (mem_root) > Item_empty_string(thd, "Event", NAME_CHAR_LEN), > mem_root); Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0