diff options
Diffstat (limited to 'mysql-test/suite')
31 files changed, 1201 insertions, 48 deletions
diff --git a/mysql-test/suite/galera/r/galera#500.result b/mysql-test/suite/galera/r/galera#500.result index d983e844d76..a5ab0b19718 100644 --- a/mysql-test/suite/galera/r/galera#500.result +++ b/mysql-test/suite/galera/r/galera#500.result @@ -1,5 +1,3 @@ -connection node_1; -connection node_2; connection node_2; connection node_1; connection node_1; diff --git a/mysql-test/suite/galera/r/galera_as_slave_replay.result b/mysql-test/suite/galera/r/galera_as_slave_replay.result index 760617be5f7..3c2cea19179 100644 --- a/mysql-test/suite/galera/r/galera_as_slave_replay.result +++ b/mysql-test/suite/galera/r/galera_as_slave_replay.result @@ -1,10 +1,13 @@ connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; connection node_2a; +connection node_2; connection node_1; +connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3; +connection node_3; RESET MASTER; connection node_2a; START SLAVE; -connection node_1; +connection node_3; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)) engine=innodb; INSERT INTO t1 VALUES (1, 'a'); INSERT INTO t1 VALUES (3, 'a'); @@ -18,15 +21,14 @@ f1 f2 UPDATE t1 SET f2 = 'c' WHERE f1 > 1; connection node_2a; SET SESSION wsrep_sync_wait = 0; -connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3; -connection node_3; +connection node_1; SET SESSION wsrep_sync_wait = 0; connection node_2a; -SET GLOBAL wsrep_provider_options = 'dbug=d,commit_monitor_enter_sync'; +SET GLOBAL wsrep_provider_options = 'dbug=d,commit_monitor_master_enter_sync'; SET GLOBAL debug_dbug = "d,sync.wsrep_apply_cb"; -connection node_3; -INSERT INTO test.t1 VALUES (2, 'b'); connection node_1; +INSERT INTO test.t1 VALUES (2, 'b'); +connection node_3; COMMIT; connection node_2a; SET SESSION wsrep_on = 0; @@ -35,8 +37,8 @@ SET GLOBAL debug_dbug = ""; SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; connection node_2a; SET GLOBAL wsrep_provider_options = 'dbug='; -SET GLOBAL wsrep_provider_options = 'signal=commit_monitor_enter_sync'; -connection node_1; +SET GLOBAL wsrep_provider_options = 'signal=commit_monitor_master_enter_sync'; +connection node_3; SELECT COUNT(*) = 1 FROM t1 WHERE f2 = 'a'; COUNT(*) = 1 1 @@ -61,7 +63,7 @@ SET DEBUG_SYNC = "RESET"; # # test phase with real abort # -connection node_1; +connection node_3; set binlog_format=ROW; insert into t1 values (4, 'd'); SET AUTOCOMMIT=ON; @@ -70,9 +72,9 @@ UPDATE t1 SET f2 = 'd' WHERE f1 = 3; connection node_2a; SET GLOBAL wsrep_provider_options = 'dbug=d,commit_monitor_enter_sync'; SET GLOBAL debug_dbug = "d,sync.wsrep_apply_cb"; -connection node_3; -UPDATE test.t1 SET f2 = 'e' WHERE f1 = 3; connection node_1; +UPDATE test.t1 SET f2 = 'e' WHERE f1 = 3; +connection node_3; COMMIT; connection node_2a; SET GLOBAL debug_dbug = ""; @@ -90,6 +92,6 @@ set session wsrep_sync_wait=0; STOP SLAVE; RESET SLAVE; DROP TABLE t1; -connection node_1; +connection node_3; DROP TABLE t1; RESET MASTER; diff --git a/mysql-test/suite/galera/r/galera_bf_abort_ps.result b/mysql-test/suite/galera/r/galera_bf_abort_ps.result new file mode 100644 index 00000000000..42292cb20a0 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_bf_abort_ps.result @@ -0,0 +1,16 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(6)) ENGINE=InnoDB; +connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; +connection node_2; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'node_2'); +connection node_1; +INSERT INTO t1 VALUES (1,'node_1'); +connection node_2a; +connection node_2; +INSERT INTO t1 VALUES (2, 'node_2'); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +wsrep_local_aborts_increment +1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_bf_abort_ps_threadpool.result b/mysql-test/suite/galera/r/galera_bf_abort_ps_threadpool.result new file mode 100644 index 00000000000..7482e76778e --- /dev/null +++ b/mysql-test/suite/galera/r/galera_bf_abort_ps_threadpool.result @@ -0,0 +1,22 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(6)) ENGINE=InnoDB; +connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; +connection node_2; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'node_2'); +connection node_2a; +SET GLOBAL debug_dbug = "+d,sync.wsrep_apply_cb"; +connection node_1; +INSERT INTO t1 VALUES (1,'node_1'); +connection node_2a; +SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached"; +connection node_2; +SET DEBUG_SYNC = "wsrep_before_before_command SIGNAL signal.wsrep_apply_cb WAIT_FOR bf_abort"; +INSERT INTO t1 VALUES (2, 'node_2'); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +wsrep_local_aborts_increment +1 +SET DEBUG_SYNC = 'RESET'; +SET GLOBAL debug_dbug = DEFAULT; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MDEV-16509.test b/mysql-test/suite/galera/t/MDEV-16509.test index eecc0e5e4e6..c0a8988aecc 100644 --- a/mysql-test/suite/galera/t/MDEV-16509.test +++ b/mysql-test/suite/galera/t/MDEV-16509.test @@ -5,6 +5,7 @@ --source include/galera_cluster.inc --source include/have_debug.inc --source include/have_debug_sync.inc +--source include/galera_have_debug_sync.inc CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB; diff --git a/mysql-test/suite/galera/t/galera_as_slave_replay.test b/mysql-test/suite/galera/t/galera_as_slave_replay.test index 93f95349e6d..47f70bda721 100644 --- a/mysql-test/suite/galera/t/galera_as_slave_replay.test +++ b/mysql-test/suite/galera/t/galera_as_slave_replay.test @@ -18,9 +18,10 @@ #--source suite/galera/include/galera_have_debug_sync.inc # -# node 1 is native MariaDB server operating as async replication master +# node 3 is native MariaDB server operating as async replication master # ---connection node_1 +--connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3 +--connection node_3 RESET MASTER; --connection node_2a @@ -31,14 +32,14 @@ RESET MASTER; # -# nodes 2 and 3 form a galera cluster, node 2 operates as slave for native MariaDB naster in node 1 +# nodes 1 and 2 form a galera cluster, node 2 operates as slave for native MariaDB naster in node 3 # --disable_query_log ---eval CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PORT=$NODE_MYPORT_1; +--eval CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PORT=$NODE_MYPORT_3; --enable_query_log START SLAVE; ---connection node_1 +--connection node_3 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)) engine=innodb; INSERT INTO t1 VALUES (1, 'a'); INSERT INTO t1 VALUES (3, 'a'); @@ -63,15 +64,14 @@ SET SESSION wsrep_sync_wait = 0; --source include/wait_condition.inc # wait for create table and inserts to be replicated in cluster ---connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3 ---connection node_3 +--connection node_1 SET SESSION wsrep_sync_wait = 0; --let $wait_condition = SELECT COUNT(*) = 2 FROM test.t1; --source include/wait_condition.inc --connection node_2a # Block the future commit of async replication ---let $galera_sync_point = commit_monitor_enter_sync +--let $galera_sync_point = commit_monitor_master_enter_sync --source include/galera_set_sync_point.inc # block also the applier before applying begins @@ -81,13 +81,13 @@ SET GLOBAL debug_dbug = "d,sync.wsrep_apply_cb"; # now inject a conflicting insert from node 3, it will replicate with # earlier seqno (than async transaction) and pause before applying in node 2 # ---connection node_3 +--connection node_1 INSERT INTO test.t1 VALUES (2, 'b'); # # send the update from master, this will succeed here, beceuase of async replication. # async replication will apply this in node 2 and pause before commit phase, ---connection node_1 +--connection node_3 --error 0 COMMIT; @@ -108,7 +108,7 @@ SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; --source include/galera_clear_sync_point.inc --source include/galera_signal_sync_point.inc ---connection node_1 +--connection node_3 SELECT COUNT(*) = 1 FROM t1 WHERE f2 = 'a'; SELECT COUNT(*) = 1 FROM t1 WHERE f2 = 'c'; @@ -139,7 +139,7 @@ SET DEBUG_SYNC = "RESET"; --echo # test phase with real abort --echo # ---connection node_1 +--connection node_3 set binlog_format=ROW; @@ -163,11 +163,11 @@ UPDATE t1 SET f2 = 'd' WHERE f1 = 3; SET GLOBAL debug_dbug = "d,sync.wsrep_apply_cb"; # Inject a conflicting update from node 3 ---connection node_3 +--connection node_1 UPDATE test.t1 SET f2 = 'e' WHERE f1 = 3; # send the update from master ---connection node_1 +--connection node_3 --error 0 COMMIT; @@ -195,6 +195,6 @@ RESET SLAVE; DROP TABLE t1; ---connection node_1 +--connection node_3 DROP TABLE t1; RESET MASTER; diff --git a/mysql-test/suite/galera/t/galera_bf_abort_ps.cnf b/mysql-test/suite/galera/t/galera_bf_abort_ps.cnf new file mode 100644 index 00000000000..34c1a8cc3cf --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_abort_ps.cnf @@ -0,0 +1,3 @@ +!include ../galera_2nodes.cnf +[mysqltest] +ps-protocol
\ No newline at end of file diff --git a/mysql-test/suite/galera/t/galera_bf_abort_ps.test b/mysql-test/suite/galera/t/galera_bf_abort_ps.test new file mode 100644 index 00000000000..d2dfb92651e --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_abort_ps.test @@ -0,0 +1,34 @@ +# +# MDEV-24255 +# Test BF abort of a transaction that has ps-protocol enabled +# + +--source include/galera_cluster.inc + +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(6)) ENGINE=InnoDB; +--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 + +--connection node_2 +--let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +START TRANSACTION; +INSERT INTO t1 VALUES (1,'node_2'); + +--connection node_1 +INSERT INTO t1 VALUES (1,'node_1'); + +--connection node_2a +--let $wait_condition = SELECT COUNT(*) = 1 FROM t1 WHERE f2 = 'node_1' +--source include/wait_condition.inc + +--connection node_2 +--error ER_LOCK_DEADLOCK +INSERT INTO t1 VALUES (2, 'node_2'); + +--let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +--disable_query_log +--eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; +--enable_query_log + +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_bf_abort_ps_threadpool.cnf b/mysql-test/suite/galera/t/galera_bf_abort_ps_threadpool.cnf new file mode 100644 index 00000000000..83baa995c17 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_abort_ps_threadpool.cnf @@ -0,0 +1,7 @@ +!include ../galera_2nodes.cnf + +[mysqld] +thread-handling=pool-of-threads + +[mysqltest] +ps-protocol diff --git a/mysql-test/suite/galera/t/galera_bf_abort_ps_threadpool.test b/mysql-test/suite/galera/t/galera_bf_abort_ps_threadpool.test new file mode 100644 index 00000000000..56348a6f527 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_abort_ps_threadpool.test @@ -0,0 +1,54 @@ +# +# MDEV-24255 +# Test BF abort of a transaction that has ps-protocol enabled +# This test stresses the case where wsrep_before_command() +# finds the transaction in state s_must_abort. This only +# possible when the server is using the thread pool. +# + +--source include/galera_cluster.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(6)) ENGINE=InnoDB; + +--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 + +--connection node_2 +--let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +START TRANSACTION; +INSERT INTO t1 VALUES (1,'node_2'); + +--connection node_2a +SET GLOBAL debug_dbug = "+d,sync.wsrep_apply_cb"; + +--connection node_1 +INSERT INTO t1 VALUES (1,'node_1'); + +--connection node_2a +SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached"; + +--connection node_2 +SET DEBUG_SYNC = "wsrep_before_before_command SIGNAL signal.wsrep_apply_cb WAIT_FOR bf_abort"; + +# +# The following INSERT is expected to enter +# wsrep_before_command() and find its transaction +# in state s_must_abort. +# Notice that the test appears more complicated +# than it needs to... however we cannot use +# --send for this INSERT, otherwise mysqltest +# will not use ps-protocol +# +--error ER_LOCK_DEADLOCK +INSERT INTO t1 VALUES (2, 'node_2'); + +--let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +--disable_query_log +--eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; +--enable_query_log + +SET DEBUG_SYNC = 'RESET'; +SET GLOBAL debug_dbug = DEFAULT; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_rsu_error.test b/mysql-test/suite/galera/t/galera_rsu_error.test index cad8154ac76..6de7607b6ec 100644 --- a/mysql-test/suite/galera/t/galera_rsu_error.test +++ b/mysql-test/suite/galera/t/galera_rsu_error.test @@ -9,6 +9,9 @@ CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; INSERT INTO t1 VALUES (1), (1); --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc + SET SESSION wsrep_OSU_method = "RSU"; --error ER_DUP_ENTRY ALTER TABLE t1 ADD PRIMARY KEY (f1); diff --git a/mysql-test/suite/galera/t/galera_toi_ddl_sequential.test b/mysql-test/suite/galera/t/galera_toi_ddl_sequential.test index 51eae7005df..89a1af845c9 100644 --- a/mysql-test/suite/galera/t/galera_toi_ddl_sequential.test +++ b/mysql-test/suite/galera/t/galera_toi_ddl_sequential.test @@ -9,6 +9,9 @@ CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc + ALTER TABLE t1 ADD COLUMN f2 INTEGER; INSERT INTO t1 VALUES (2, 3); diff --git a/mysql-test/suite/galera/t/galera_toi_lock_shared.test b/mysql-test/suite/galera/t/galera_toi_lock_shared.test index 6857a0e08ca..6b7feec6031 100644 --- a/mysql-test/suite/galera/t/galera_toi_lock_shared.test +++ b/mysql-test/suite/galera/t/galera_toi_lock_shared.test @@ -10,6 +10,9 @@ CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc + ALTER TABLE t1 ADD COLUMN f2 INTEGER, LOCK=SHARED; --connection node_1 diff --git a/mysql-test/suite/galera/t/galera_truncate.test b/mysql-test/suite/galera/t/galera_truncate.test index 79f9bad1f1b..3c3ee56a23f 100644 --- a/mysql-test/suite/galera/t/galera_truncate.test +++ b/mysql-test/suite/galera/t/galera_truncate.test @@ -14,6 +14,9 @@ CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) Engine=InnoDB; INSERT INTO t1 VALUES (1); --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc + TRUNCATE TABLE t1; SELECT COUNT(*) = 0 FROM t1; diff --git a/mysql-test/suite/galera/t/galera_truncate_temporary.test b/mysql-test/suite/galera/t/galera_truncate_temporary.test index 3ad94eb9930..ea20911bd5d 100644 --- a/mysql-test/suite/galera/t/galera_truncate_temporary.test +++ b/mysql-test/suite/galera/t/galera_truncate_temporary.test @@ -67,6 +67,9 @@ CREATE TEMPORARY TABLE t1 (f1 INTEGER) ENGINE=InnoDB; INSERT INTO t1 VALUES (2); --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc + TRUNCATE TABLE t1; SELECT COUNT(*) = 0 FROM t1; diff --git a/mysql-test/suite/galera_sr/t/GCF-900.test b/mysql-test/suite/galera_sr/t/GCF-900.test index 3f1b53630b6..b44423c5013 100644 --- a/mysql-test/suite/galera_sr/t/GCF-900.test +++ b/mysql-test/suite/galera_sr/t/GCF-900.test @@ -15,6 +15,9 @@ START TRANSACTION; INSERT INTO t1 VALUES (2, 0); --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc + ALTER TABLE t1 DROP COLUMN f2; --connection node_1 diff --git a/mysql-test/suite/gcol/r/innodb_virtual_stats.result b/mysql-test/suite/gcol/r/innodb_virtual_stats.result index c0f595263df..c11b02ff8cb 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_stats.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_stats.result @@ -121,19 +121,5 @@ SELECT index_name, stat_name, stat_description FROM mysql.innodb_index_stats WHERE database_name = 'test' AND table_name = 't'; index_name stat_name stat_description -GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID -GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index -GEN_CLUST_INDEX size Number of pages in the index -idxb n_diff_pfx01 b -idxb n_diff_pfx02 b,DB_ROW_ID -idxb n_leaf_pages Number of leaf pages in the index -idxb size Number of pages in the index -vidxe n_diff_pfx01 e -vidxe n_diff_pfx02 e,DB_ROW_ID -vidxe n_leaf_pages Number of leaf pages in the index -vidxe size Number of pages in the index -vidxf n_diff_pfx01 f -vidxf n_diff_pfx02 f,DB_ROW_ID -vidxf n_leaf_pages Number of leaf pages in the index -vidxf size Number of pages in the index +# MDEV-24564 FIXME: Do reload statistics after the above ALTER TABLE! DROP TABLE t; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_stats.test b/mysql-test/suite/gcol/t/innodb_virtual_stats.test index 69c67af8ed1..728f84de72c 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_stats.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_stats.test @@ -52,5 +52,6 @@ ALTER TABLE t DROP INDEX vidxcd; SELECT index_name, stat_name, stat_description FROM mysql.innodb_index_stats WHERE database_name = 'test' AND table_name = 't'; +-- echo # MDEV-24564 FIXME: Do reload statistics after the above ALTER TABLE! DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns2.result b/mysql-test/suite/innodb/r/innodb-virtual-columns2.result index 3574ba72849..99a1c610bd3 100644 --- a/mysql-test/suite/innodb/r/innodb-virtual-columns2.result +++ b/mysql-test/suite/innodb/r/innodb-virtual-columns2.result @@ -62,3 +62,28 @@ INSERT INTO t1 (i) VALUES (1),(2); SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE; y i b vi DROP TABLE t1; +# +# MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column +# +CREATE TABLE t1(a INT PRIMARY KEY, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1,default); +ALTER TABLE t1 ADD COLUMN c INT; +ALTER TABLE t1 ADD KEY(g); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT g FROM t1 FORCE INDEX (g); +g +1 +DROP TABLE t1; +CREATE TABLE t1(a INT, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1,default); +ALTER TABLE t1 ADD COLUMN c INT PRIMARY KEY; +ALTER TABLE t1 ADD KEY(g); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT g FROM t1 FORCE INDEX (g); +g +1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 9053cbacec6..110ef5b64e1 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -327,7 +327,6 @@ FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants 22 -SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; # # MDEV-21045 AddressSanitizer: use-after-poison in mem_heap_dup / row_log_table_get_pk_col # @@ -371,6 +370,20 @@ SET DEBUG_SYNC='RESET'; disconnect con2; DROP TABLE t1; # +# MDEV-24512 Assertion failed in rec_is_metadata() +# in btr_discard_only_page_on_level() +# +SET @save_limit= @@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +CREATE TABLE t1 (c CHAR(1) UNIQUE) ENGINE=InnoDB; +ALTER TABLE t1 ADD c2 INT NOT NULL DEFAULT 0 FIRST; +INSERT INTO t1 (c) VALUES ('x'),('d'),('r'),('f'),('y'),('u'),('m'),('d'); +ERROR 23000: Duplicate entry 'd' for key 'c' +SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; +SELECT * FROM t1; +c2 c +DROP TABLE t1; +# # MDEV-22867 Assertion instant.n_core_fields == n_core_fields # in dict_index_t::instant_add_field # @@ -408,3 +421,4 @@ Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; SET DEBUG_SYNC=RESET; +SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; diff --git a/mysql-test/suite/innodb/t/innodb-virtual-columns2.test b/mysql-test/suite/innodb/t/innodb-virtual-columns2.test index 474a6354576..13ecffcc896 100644 --- a/mysql-test/suite/innodb/t/innodb-virtual-columns2.test +++ b/mysql-test/suite/innodb/t/innodb-virtual-columns2.test @@ -52,3 +52,23 @@ SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE; INSERT INTO t1 (i) VALUES (1),(2); SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE; DROP TABLE t1; + +--echo # +--echo # MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1,default); +ALTER TABLE t1 ADD COLUMN c INT; +ALTER TABLE t1 ADD KEY(g); +CHECK TABLE t1; +SELECT g FROM t1 FORCE INDEX (g); +DROP TABLE t1; + +CREATE TABLE t1(a INT, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1,default); +ALTER TABLE t1 ADD COLUMN c INT PRIMARY KEY; # Triggers `new_clustered` +ALTER TABLE t1 ADD KEY(g); +CHECK TABLE t1; +SELECT g FROM t1 FORCE INDEX (g); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index 86273bcddd5..b4ba8794041 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -361,8 +361,6 @@ SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; -SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; - --echo # --echo # MDEV-21045 AddressSanitizer: use-after-poison in mem_heap_dup / row_log_table_get_pk_col --echo # @@ -416,6 +414,22 @@ SET DEBUG_SYNC='RESET'; DROP TABLE t1; --echo # +--echo # MDEV-24512 Assertion failed in rec_is_metadata() +--echo # in btr_discard_only_page_on_level() +--echo # + +SET @save_limit= @@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +CREATE TABLE t1 (c CHAR(1) UNIQUE) ENGINE=InnoDB; + +ALTER TABLE t1 ADD c2 INT NOT NULL DEFAULT 0 FIRST; +--error ER_DUP_ENTRY +INSERT INTO t1 (c) VALUES ('x'),('d'),('r'),('f'),('y'),('u'),('m'),('d'); +SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # --echo # MDEV-22867 Assertion instant.n_core_fields == n_core_fields --echo # in dict_index_t::instant_add_field --echo # @@ -460,3 +474,5 @@ INSERT INTO t1 VALUES (2),(3),(4); CHECK TABLE t1; DROP TABLE t1; SET DEBUG_SYNC=RESET; + +SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result index 6ded4f687a6..2c06b70c509 100644 --- a/mysql-test/suite/plugins/r/server_audit.result +++ b/mysql-test/suite/plugins/r/server_audit.result @@ -227,6 +227,7 @@ set global server_audit_logging= on; disconnect cn1; drop user user1@localhost; set global server_audit_events=''; +set global server_audit_incl_users='root, plug_dest'; CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; connect(localhost,plug,plug_dest,test,MYSQL_PORT,MYSQL_SOCK); @@ -277,7 +278,7 @@ server_audit_file_path server_audit_file_rotate_now OFF server_audit_file_rotate_size 1000000 server_audit_file_rotations 9 -server_audit_incl_users root +server_audit_incl_users root, plug_dest server_audit_logging ON server_audit_mode 1 server_audit_output_type file @@ -431,6 +432,7 @@ TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_events=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_incl_users=\'root, plug_dest\'',0 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, @@ -454,6 +456,7 @@ TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT PROXY ON plug_dest TO plug',0 TIME,HOSTNAME,plug,localhost,ID,0,CONNECT,test,,0 TIME,HOSTNAME,plug,localhost,ID,0,PROXY_CONNECT,test,`plug_dest`@`%`,0 +TIME,HOSTNAME,plug,localhost,ID,ID,QUERY,test,'select USER(),CURRENT_USER()',0 TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,test,,0 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, diff --git a/mysql-test/suite/plugins/t/server_audit.test b/mysql-test/suite/plugins/t/server_audit.test index 787541f7ca0..0d65c451d08 100644 --- a/mysql-test/suite/plugins/t/server_audit.test +++ b/mysql-test/suite/plugins/t/server_audit.test @@ -173,6 +173,7 @@ source include/wait_until_count_sessions.inc; drop user user1@localhost; set global server_audit_events=''; +set global server_audit_incl_users='root, plug_dest'; CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 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/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test b/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test index 52cd9e31753..f67c6e2ac0a 100644 --- a/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test +++ b/mysql-test/suite/rpl/t/rpl_semisync_ali_issues.test @@ -31,8 +31,12 @@ echo [ enable semi-sync on slave ]; stop slave; set global rpl_semi_sync_slave_enabled = 1; start slave; +let $status_var= rpl_semi_sync_slave_status; +let $status_var_value= ON; +source include/wait_for_status_var.inc; show status like 'rpl_semi_sync_slave%'; + connection master; CREATE TABLE t1(a INT) ENGINE=InnoDB; sync_slave_with_master; @@ -190,6 +194,12 @@ connection con1; INSERT INTO t1 values (2); sync_slave_with_master; connection con1; +let $status_var= Rpl_semi_sync_master_clients; +let $status_var_value= 1; +source include/wait_for_status_var.inc; +let $status_var= Rpl_semi_sync_master_status; +let $status_var_value= ON; +source include/wait_for_status_var.inc; show status like 'Rpl_semi_sync_master_clients'; show status like 'Rpl_semi_sync_master_status'; @@ -259,7 +269,12 @@ START SLAVE IO_THREAD; --echo ######################################################### connection con1; SET GLOBAL rpl_semi_sync_master_enabled = 0; + +let $status_var= Rpl_semi_sync_master_clients; +let $status_var_value= 1; +source include/wait_for_status_var.inc; show status like 'Rpl_semi_sync_master_clients'; + INSERT INTO t1 VALUES (1); SET GLOBAL rpl_semi_sync_master_enabled = 1; INSERT INTO t1 VALUES (2); diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 99d1c01cc49..98d98ff4034 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -912,6 +912,7 @@ t1 CREATE TABLE `t1` ( PARTITION BY SYSTEM_TIME INTERVAL 7 SECOND STARTS TIMESTAMP'2018-04-11 17:00:04' (PARTITION `ver_p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `ver_pn` CURRENT ENGINE = DEFAULT_ENGINE) +set timestamp= default; # # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table # @@ -1079,6 +1080,41 @@ create table t1 (a int) with system versioning partition by system_time (partition p1 history, partition pn current); alter table t1 add partition (partition p2); ERROR HY000: Wrong partitioning type, expected type: `SYSTEM_TIME` +# MDEV-17891 Assertion failures in select_insert::abort_result_set and +# mysql_load upon attempt to replace into a full table +set @@max_heap_table_size= 1024*1024; +create or replace table t1 ( +pk integer auto_increment, +primary key (pk), +f varchar(45000) +) with system versioning engine=memory +partition by system_time interval 1 year (partition p1 history, +partition pn current); +# fill the table until full +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +insert into t1 (f) select f from t1; +ERROR HY000: The table 't1' is full +# leave space for exactly one record in current partition +delete from t1 where pk = 1; +# copy all data into history partition +replace into t1 select * from t1; +replace into t1 select * from t1; +ERROR HY000: The table 't1' is full +create or replace table t1 ( +pk integer auto_increment, +primary key (pk), +f varchar(45000) +) with system versioning engine=memory +partition by system_time interval 1 year (partition p1 history, +partition pn current); +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +select * into outfile 'load.data' from t1; +load data infile 'load.data' replace into table t1; +load data infile 'load.data' replace into table t1; +ERROR HY000: The table 't1' is full +load data infile 'load.data' replace into table t1; +ERROR HY000: The table 't1' is full +set @@max_heap_table_size= 1048576; drop table t1; # # MDEV-22178 Assertion `info->alias.str' failed in partition_info::check_partition_info instead of ER_VERS_WRONG_PARTS diff --git a/mysql-test/suite/versioning/r/replace.result b/mysql-test/suite/versioning/r/replace.result index bda61f118b0..57a992cce49 100644 --- a/mysql-test/suite/versioning/r/replace.result +++ b/mysql-test/suite/versioning/r/replace.result @@ -48,3 +48,16 @@ INSERT INTO t1 () VALUES (),(),(),(),(),(); UPDATE IGNORE t1 SET f = 1; REPLACE t1 SELECT * FROM t1; DROP TABLE t1; +# MDEV-22540 ER_DUP_ENTRY upon REPLACE or Assertion failed +set timestamp=1589245268.41934; +create table t1 (a int primary key) with system versioning; +insert into t1 values (1),(2); +connect con1,localhost,root,,test; +set timestamp=1589245268.52093; +replace into t1 values (1),(2); +connection default; +replace into t1 values (1),(2); +connection con1; +replace into t1 values (1),(2); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +drop table t1; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index b4c2a058147..f9f12f4c6af 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -769,6 +769,7 @@ partition by system_time interval column_get(column_create(7,7), 7 as int) secon partition ver_pn current); --replace_result $default_engine DEFAULT_ENGINE show create table t1; +set timestamp= default; --echo # --echo # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table @@ -936,7 +937,51 @@ create table t1 (a int) with system versioning partition by system_time --error ER_PARTITION_WRONG_TYPE alter table t1 add partition (partition p2); +--echo # MDEV-17891 Assertion failures in select_insert::abort_result_set and +--echo # mysql_load upon attempt to replace into a full table + +--let $max_heap_table_size_orig= `select @@max_heap_table_size;` +set @@max_heap_table_size= 1024*1024; +create or replace table t1 ( + pk integer auto_increment, + primary key (pk), + f varchar(45000) +) with system versioning engine=memory + partition by system_time interval 1 year (partition p1 history, + partition pn current); + +--echo # fill the table until full +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +--error ER_RECORD_FILE_FULL +insert into t1 (f) select f from t1; +--echo # leave space for exactly one record in current partition +delete from t1 where pk = 1; +--echo # copy all data into history partition +replace into t1 select * from t1; +--error ER_RECORD_FILE_FULL +replace into t1 select * from t1; + +create or replace table t1 ( + pk integer auto_increment, + primary key (pk), + f varchar(45000) +) with system versioning engine=memory + partition by system_time interval 1 year (partition p1 history, + partition pn current); + +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); + +select * into outfile 'load.data' from t1; +load data infile 'load.data' replace into table t1; +--error ER_RECORD_FILE_FULL +load data infile 'load.data' replace into table t1; +--error ER_RECORD_FILE_FULL +load data infile 'load.data' replace into table t1; + # Cleanup +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load.data +eval set @@max_heap_table_size= $max_heap_table_size_orig; drop table t1; --echo # diff --git a/mysql-test/suite/versioning/t/replace.test b/mysql-test/suite/versioning/t/replace.test index 392c0ffcf35..83489f4a4b9 100644 --- a/mysql-test/suite/versioning/t/replace.test +++ b/mysql-test/suite/versioning/t/replace.test @@ -59,4 +59,22 @@ UPDATE IGNORE t1 SET f = 1; REPLACE t1 SELECT * FROM t1; DROP TABLE t1; +--echo # MDEV-22540 ER_DUP_ENTRY upon REPLACE or Assertion failed +set timestamp=1589245268.41934; +create table t1 (a int primary key) with system versioning; +insert into t1 values (1),(2); + +--connect (con1,localhost,root,,test) +set timestamp=1589245268.52093; +replace into t1 values (1),(2); + +--connection default +replace into t1 values (1),(2); + +--connection con1 +--error ER_DUP_ENTRY +replace into t1 values (1),(2); + +drop table t1; + --source suite/versioning/common_finish.inc |