January 2021 - developers - lists.mariadb.org

[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