diff options
Diffstat (limited to 'mysql-test')
31 files changed, 470 insertions, 99 deletions
diff --git a/mysql-test/main/connect.test b/mysql-test/main/connect.test index 9bc067f3236..b46913c3cb4 100644 --- a/mysql-test/main/connect.test +++ b/mysql-test/main/connect.test @@ -251,11 +251,8 @@ let $wait_condition = --echo --echo # -- Waiting for connections to close... -let $wait_condition = - SELECT COUNT(*) = 1 - FROM information_schema.processlist - WHERE db = 'test'; ---source include/wait_condition.inc +let $count_sessions=1; +--source include/wait_until_count_sessions.inc --echo DROP USER mysqltest_u1@localhost; diff --git a/mysql-test/main/failed_auth_3909.result b/mysql-test/main/failed_auth_3909.result index 4c3c0aba9df..bc82492c9ed 100644 --- a/mysql-test/main/failed_auth_3909.result +++ b/mysql-test/main/failed_auth_3909.result @@ -10,15 +10,15 @@ Warning 1364 Field 'authentication_string' doesn't have a default value flush privileges; connect(localhost,u1,,test,MASTER_PORT,MASTER_SOCKET); connect fail,localhost,u1; -ERROR HY000: Server is running in --secure-auth mode, but 'u1'@'localhost' has a password in the old format; please change the password to the new format +ERROR 28000: Access denied for user 'u1'@'localhost' (using password: NO) connect(localhost,u2,,test,MASTER_PORT,MASTER_SOCKET); connect fail,localhost,u2; -ERROR 28000: Access denied for user 'u2'@'localhost' (using password: NO) +ERROR HY000: Server is running in --secure-auth mode, but 'u2'@'localhost' has a password in the old format; please change the password to the new format connect(localhost,u2,password,test,MASTER_PORT,MASTER_SOCKET); connect fail,localhost,u2,password; -ERROR 28000: Access denied for user 'u2'@'localhost' (using password: YES) -ERROR HY000: Server is running in --secure-auth mode, but 'u1'@'localhost' has a password in the old format; please change the password to the new format -ERROR 28000: Access denied for user 'u2'@'localhost' (using password: NO) -ERROR 28000: Access denied for user 'u2'@'localhost' (using password: YES) +ERROR HY000: Server is running in --secure-auth mode, but 'u2'@'localhost' has a password in the old format; please change the password to the new format +ERROR 28000: Access denied for user 'u1'@'localhost' (using password: NO) +ERROR HY000: Server is running in --secure-auth mode, but 'u2'@'localhost' has a password in the old format; please change the password to the new format +ERROR HY000: Server is running in --secure-auth mode, but 'u2'@'localhost' has a password in the old format; please change the password to the new format delete from mysql.user where plugin = 'mysql_old_password'; flush privileges; diff --git a/mysql-test/main/failed_auth_3909.test b/mysql-test/main/failed_auth_3909.test index fb104cf4b81..45267d628b2 100644 --- a/mysql-test/main/failed_auth_3909.test +++ b/mysql-test/main/failed_auth_3909.test @@ -11,24 +11,24 @@ insert ignore mysql.user (user,plugin) values ('foo','mysql_old_password'),('bar flush privileges; --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error ER_SERVER_IS_IN_SECURE_AUTH_MODE +--error ER_ACCESS_DENIED_ERROR connect (fail,localhost,u1); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error ER_ACCESS_DENIED_ERROR +--error ER_SERVER_IS_IN_SECURE_AUTH_MODE connect (fail,localhost,u2); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error ER_ACCESS_DENIED_ERROR +--error ER_SERVER_IS_IN_SECURE_AUTH_MODE connect (fail,localhost,u2,password); ---error ER_SERVER_IS_IN_SECURE_AUTH_MODE +--error ER_ACCESS_DENIED_ERROR change_user u1; ---error ER_ACCESS_DENIED_ERROR +--error ER_SERVER_IS_IN_SECURE_AUTH_MODE change_user u2; ---error ER_ACCESS_DENIED_ERROR +--error ER_SERVER_IS_IN_SECURE_AUTH_MODE change_user u2,password; delete from mysql.user where plugin = 'mysql_old_password'; diff --git a/mysql-test/main/mysql_install_db_win.result b/mysql-test/main/mysql_install_db_win.result new file mode 100644 index 00000000000..1fba59086a4 --- /dev/null +++ b/mysql-test/main/mysql_install_db_win.result @@ -0,0 +1,9 @@ +Running bootstrap +Removing default user +Creating my.ini file +Creation of the database was successful +# Kill the server +SELECT @@datadir; +@@datadir +DATADIR/ +# Kill the server diff --git a/mysql-test/main/mysql_install_db_win.test b/mysql-test/main/mysql_install_db_win.test new file mode 100644 index 00000000000..ccc08262436 --- /dev/null +++ b/mysql-test/main/mysql_install_db_win.test @@ -0,0 +1,22 @@ +--source include/windows.inc + +# Create database in tmp directory using mysql_install_db.exe, +# and start server from this directory. +let $ddir= $MYSQLTEST_VARDIR/tmp/ddir; +exec $MYSQL_INSTALL_DB_EXE --datadir=$ddir; + +--source include/kill_mysqld.inc +let $restart_parameters=--datadir=$ddir --loose-innodb; +--source include/start_mysqld.inc + +# Smoke test - check that we're actually using datadir +# we've created (i.e restart_parameters worked) +--replace_result $ddir DATADIR +SELECT @@datadir; + +# restart in the original datadir again +--source include/kill_mysqld.inc +rmdir $ddir; +let $restart_parameters=; +--source include/start_mysqld.inc + diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index cf25f377142..094a63cf50a 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -294,6 +294,15 @@ The following specify which files/extra groups are read (specified before remain --group-concat-max-len=# The maximum length of the result of function GROUP_CONCAT() + --gtid-cleanup-batch-size=# + Normally does not need tuning. How many old rows must + accumulate in the mysql.gtid_slave_pos table before a + background job will be run to delete them. Can be + increased to reduce number of commits if using many + different engines with --gtid_pos_auto_engines, or to + reduce CPU overhead if using a huge number of different + gtid_domain_ids. Can be decreased to reduce number of old + rows in the table. --gtid-domain-id=# Used with global transaction ID to identify logically independent replication streams. When events can propagate through multiple parallel paths (for example @@ -1434,6 +1443,7 @@ gdb FALSE general-log FALSE getopt-prefix-matching FALSE group-concat-max-len 1048576 +gtid-cleanup-batch-size 64 gtid-domain-id 0 gtid-ignore-duplicates FALSE gtid-pos-auto-engines diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 40290ca9879..3ebc3b47833 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -591,6 +591,25 @@ id select_type table type possible_keys key key_len ref rows Extra set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; # +# MDEV-17734: AddressSanitizer: use-after-poison in create_key_parts_for_pseudo_indexes +# +set @@use_stat_tables= PREFERABLY; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @save_use_stat_tables= @@use_stat_tables; +create table t1 (a int, b int); +insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10); +analyze table t1 persistent for columns (a) indexes (); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select * from t1 where a=1 and b=3; +a b +1 3 +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; +drop table t1; +# # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column # SET use_stat_tables= PREFERABLY; diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test index 19bc0fa2f46..0005e48e0e7 100644 --- a/mysql-test/main/stat_tables.test +++ b/mysql-test/main/stat_tables.test @@ -370,6 +370,23 @@ set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectiv set use_stat_tables=@save_use_stat_tables; --echo # +--echo # MDEV-17734: AddressSanitizer: use-after-poison in create_key_parts_for_pseudo_indexes +--echo # + +set @@use_stat_tables= PREFERABLY; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @save_use_stat_tables= @@use_stat_tables; +create table t1 (a int, b int); +insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10); + +analyze table t1 persistent for columns (a) indexes (); +select * from t1 where a=1 and b=3; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; +drop table t1; + +--echo # --echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column --echo # diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 070d13d9bb1..a6c5525a0d3 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -618,6 +618,25 @@ id select_type table type possible_keys key key_len ref rows Extra set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; # +# MDEV-17734: AddressSanitizer: use-after-poison in create_key_parts_for_pseudo_indexes +# +set @@use_stat_tables= PREFERABLY; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @save_use_stat_tables= @@use_stat_tables; +create table t1 (a int, b int); +insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10); +analyze table t1 persistent for columns (a) indexes (); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select * from t1 where a=1 and b=3; +a b +1 3 +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; +drop table t1; +# # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column # SET use_stat_tables= PREFERABLY; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 361724e5c57..b783d815222 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2292,6 +2292,10 @@ sub environment_setup { $ENV{'EXE_MYSQL'}= $exe_mysql; $ENV{'MYSQL_PLUGIN'}= $exe_mysql_plugin; $ENV{'MYSQL_EMBEDDED'}= $exe_mysql_embedded; + if(IS_WINDOWS) + { + $ENV{'MYSQL_INSTALL_DB_EXE'}= mtr_exe_exists("$bindir/sql$opt_vs_config/mysql_install_db"); + } my $client_config_exe= mtr_exe_maybe_exists( diff --git a/mysql-test/std_data/rpl/mysql-5.7.11-stm-temporal-round-binlog.000001 b/mysql-test/std_data/rpl/mysql-5.7.11-stm-temporal-round-binlog.000001 Binary files differnew file mode 100644 index 00000000000..5010e164e43 --- /dev/null +++ b/mysql-test/std_data/rpl/mysql-5.7.11-stm-temporal-round-binlog.000001 diff --git a/mysql-test/std_data/rpl/mysql-8.0.13-stm-temporal-round-binlog.000001 b/mysql-test/std_data/rpl/mysql-8.0.13-stm-temporal-round-binlog.000001 Binary files differnew file mode 100644 index 00000000000..4d582fdf5bb --- /dev/null +++ b/mysql-test/std_data/rpl/mysql-8.0.13-stm-temporal-round-binlog.000001 diff --git a/mysql-test/suite/encryption/disabled.def b/mysql-test/suite/encryption/disabled.def index d92d3495cb8..746faf49873 100644 --- a/mysql-test/suite/encryption/disabled.def +++ b/mysql-test/suite/encryption/disabled.def @@ -12,3 +12,4 @@ innodb_scrub : MDEV-8139 scrubbing does not work reliably innodb_scrub_background : MDEV-8139 scrubbing does not work reliably +innodb-redo-badkey : MDEV-13893/MDEV-12699 fix recovery of corrupted pages diff --git a/mysql-test/suite/innodb_fts/r/create.result b/mysql-test/suite/innodb_fts/r/create.result index f3650fb3dc9..896291854a8 100644 --- a/mysql-test/suite/innodb_fts/r/create.result +++ b/mysql-test/suite/innodb_fts/r/create.result @@ -162,3 +162,19 @@ SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' len COUNT(*) 84 6 DROP TABLE t; +# +# MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in +# trx_undo_page_report_modify upon optimizing table +# under innodb_optimize_fulltext_only +# +CREATE TABLE t1 (f1 TEXT, f2 TEXT, FULLTEXT KEY (f2)) ENGINE=InnoDB; +INSERT INTO t1 (f1) VALUES ('foo'),('bar'); +DELETE FROM t1 LIMIT 1; +ALTER TABLE t1 ADD FULLTEXT KEY (f1); +SET @optimize_fulltext.save= @@innodb_optimize_fulltext_only; +SET GLOBAL innodb_optimize_fulltext_only= 1; +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; +SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save; diff --git a/mysql-test/suite/innodb_fts/t/create.test b/mysql-test/suite/innodb_fts/t/create.test index fe0273af750..4e522994fcc 100644 --- a/mysql-test/suite/innodb_fts/t/create.test +++ b/mysql-test/suite/innodb_fts/t/create.test @@ -90,3 +90,19 @@ ENGINE=InnoDB; # The column length should be 84 bytes (84 characters * 1 byte/character). SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len; DROP TABLE t; + +--echo # +--echo # MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in +--echo # trx_undo_page_report_modify upon optimizing table +--echo # under innodb_optimize_fulltext_only +--echo # + +CREATE TABLE t1 (f1 TEXT, f2 TEXT, FULLTEXT KEY (f2)) ENGINE=InnoDB; +INSERT INTO t1 (f1) VALUES ('foo'),('bar'); +DELETE FROM t1 LIMIT 1; +ALTER TABLE t1 ADD FULLTEXT KEY (f1); +SET @optimize_fulltext.save= @@innodb_optimize_fulltext_only; +SET GLOBAL innodb_optimize_fulltext_only= 1; +OPTIMIZE TABLE t1; +DROP TABLE t1; +SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save; diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result index a172dff7935..7963bed8213 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result @@ -118,7 +118,7 @@ Con4 is alive Con4 is alive select current_user(); current_user() -root@192.0.2.4 +root@santa.claus.ipv4.example.com disconnect con4; connection default; "Dumping performance_schema.host_cache" @@ -155,7 +155,7 @@ Con5 is alive Con5 is alive select current_user(); current_user() -root@192.0.2.4 +root@santa.claus.ipv4.example.com disconnect con5; connection default; "Dumping performance_schema.host_cache" diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result index 4fdc6ef1b4c..baddc88b116 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result @@ -118,7 +118,7 @@ Con4 is alive Con4 is alive select current_user(); current_user() -root@2001:db8::6:6 +root@santa.claus.ipv6.example.com disconnect con4; connection default; "Dumping performance_schema.host_cache" @@ -155,7 +155,7 @@ Con5 is alive Con5 is alive select current_user(); current_user() -root@2001:db8::6:6 +root@santa.claus.ipv6.example.com disconnect con5; connection default; "Dumping performance_schema.host_cache" diff --git a/mysql-test/suite/perfschema/r/socket_connect.result b/mysql-test/suite/perfschema/r/socket_connect.result index 1ac22f6ca34..304521b044f 100644 --- a/mysql-test/suite/perfschema/r/socket_connect.result +++ b/mysql-test/suite/perfschema/r/socket_connect.result @@ -167,19 +167,6 @@ connection default; # 6.1 Verify that there are no TCP/IP connections in the socket instance table -SELECT COUNT(*) = 0 AS 'Expect 1' -FROM performance_schema.socket_instances -WHERE EVENT_NAME LIKE '%client_connection%' - AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin -AND (IP LIKE '%127.0.0.1' OR IP LIKE '%::1'); -Expect 1 -1 # 6.2 Verify that there are no TCP/IP connections in the summary instance table -SELECT COUNT(*) = 0 AS 'Expect 1' -FROM performance_schema.socket_summary_by_instance -WHERE EVENT_NAME LIKE '%client_connection%' - AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin; -Expect 1 -1 diff --git a/mysql-test/suite/perfschema/t/socket_connect.test b/mysql-test/suite/perfschema/t/socket_connect.test index 909840144ef..b4579605eb5 100644 --- a/mysql-test/suite/perfschema/t/socket_connect.test +++ b/mysql-test/suite/perfschema/t/socket_connect.test @@ -273,18 +273,21 @@ WHERE EVENT_NAME LIKE '%client_connection%' --echo --echo # 6.1 Verify that there are no TCP/IP connections in the socket instance table --echo -eval SELECT COUNT(*) = 0 AS 'Expect 1' +let $wait_condition= +SELECT COUNT(*) = 0 AS 'Expect 1' FROM performance_schema.socket_instances WHERE EVENT_NAME LIKE '%client_connection%' AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin AND $ip_localhost; +--source include/wait_condition.inc --echo --echo # 6.2 Verify that there are no TCP/IP connections in the summary instance table --echo -eval SELECT COUNT(*) = 0 AS 'Expect 1' +let $wait_condition= +SELECT COUNT(*) = 0 AS 'Expect 1' FROM performance_schema.socket_summary_by_instance WHERE EVENT_NAME LIKE '%client_connection%' AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin; - +--source include/wait_condition.inc exit; diff --git a/mysql-test/suite/roles/flush_roles-17898.result b/mysql-test/suite/roles/flush_roles-17898.result new file mode 100644 index 00000000000..dbe6ea24afd --- /dev/null +++ b/mysql-test/suite/roles/flush_roles-17898.result @@ -0,0 +1,13 @@ +use mysql; +insert db (db,user,select_priv) values ('foo','dwr_foo','Y'), ('bar','dwr_bar','Y'); +insert roles_mapping (user,role) values ('dwr_qux_dev','dwr_foo'),('dwr_qux_dev','dwr_bar'); +insert ignore user (user,show_db_priv,is_role) values ('dwr_foo','N','Y'), ('dwr_bar','N','Y'), ('dwr_qux_dev','Y','Y'); +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value +Warning 1364 Field 'authentication_string' doesn't have a default value +flush privileges; +drop role dwr_foo; +drop role dwr_bar; +drop role dwr_qux_dev; diff --git a/mysql-test/suite/roles/flush_roles-17898.test b/mysql-test/suite/roles/flush_roles-17898.test new file mode 100644 index 00000000000..6a3b8d6f345 --- /dev/null +++ b/mysql-test/suite/roles/flush_roles-17898.test @@ -0,0 +1,11 @@ +# +# MDEV-17898 FLUSH PRIVILEGES crashes server with segfault +# +use mysql; +insert db (db,user,select_priv) values ('foo','dwr_foo','Y'), ('bar','dwr_bar','Y'); +insert roles_mapping (user,role) values ('dwr_qux_dev','dwr_foo'),('dwr_qux_dev','dwr_bar'); +insert ignore user (user,show_db_priv,is_role) values ('dwr_foo','N','Y'), ('dwr_bar','N','Y'), ('dwr_qux_dev','Y','Y'); +flush privileges; +drop role dwr_foo; +drop role dwr_bar; +drop role dwr_qux_dev; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_mdev4484.result b/mysql-test/suite/rpl/r/rpl_gtid_mdev4484.result index 00307a8c104..5dffdd9809c 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_mdev4484.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_mdev4484.result @@ -16,36 +16,32 @@ INSERT INTO t1 VALUES (1); connection slave; connection slave; include/stop_slave.inc +SET @old_gtid_cleanup_batch_size= @@GLOBAL.gtid_cleanup_batch_size; +SET GLOBAL gtid_cleanup_batch_size= 2; SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,gtid_slave_pos_simulate_failed_delete"; SET sql_log_bin= 0; -CALL mtr.add_suppression("Can't find file"); +CALL mtr.add_suppression("<DEBUG> Error deleting old GTID row"); SET sql_log_bin= 1; include/start_slave.inc connection master; -INSERT INTO t1 VALUES (2); -connection slave; -include/wait_for_slave_sql_error.inc [errno=1942] -STOP SLAVE IO_THREAD; -SELECT domain_id, server_id, seq_no FROM mysql.gtid_slave_pos -ORDER BY domain_id, sub_id DESC LIMIT 1; -domain_id server_id seq_no -0 1 3 +connection slave; +SELECT COUNT(*), MAX(seq_no) INTO @pre_count, @pre_max_seq_no +FROM mysql.gtid_slave_pos; +SELECT IF(@pre_count >= 20, "OK", CONCAT("Error: too few rows seen while errors injected: ", @pre_count)); +IF(@pre_count >= 20, "OK", CONCAT("Error: too few rows seen while errors injected: ", @pre_count)) +OK SET GLOBAL debug_dbug= @old_dbug; -include/start_slave.inc connection master; -INSERT INTO t1 VALUES (3); -connection slave; -connection slave; -SELECT domain_id, server_id, seq_no FROM mysql.gtid_slave_pos -ORDER BY domain_id, sub_id DESC LIMIT 1; -domain_id server_id seq_no -0 1 4 -SELECT * FROM t1 ORDER BY i; -i -1 -2 -3 +connection slave; +connection slave; +SELECT IF(COUNT(*) >= 1, "OK", CONCAT("Error: too few rows seen after errors no longer injected: ", COUNT(*))) +FROM mysql.gtid_slave_pos +WHERE seq_no <= @pre_max_seq_no; +IF(COUNT(*) >= 1, "OK", CONCAT("Error: too few rows seen after errors no longer injected: ", COUNT(*))) +OK connection master; DROP TABLE t1; +connection slave; +SET GLOBAL gtid_cleanup_batch_size= @old_gtid_cleanup_batch_size; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result b/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result index 1647b6c998c..50f24d56e9a 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result @@ -171,7 +171,7 @@ include/start_slave.inc *** MDEV-4692: mysql.gtid_slave_pos accumulates values for a domain *** SELECT domain_id, COUNT(*) FROM mysql.gtid_slave_pos GROUP BY domain_id; domain_id COUNT(*) -0 2 +0 3 1 2 connection server_1; INSERT INTO t1 VALUES (11); @@ -179,7 +179,7 @@ connection server_2; FLUSH NO_WRITE_TO_BINLOG TABLES; SELECT domain_id, COUNT(*) FROM mysql.gtid_slave_pos GROUP BY domain_id; domain_id COUNT(*) -0 2 +0 4 1 2 include/start_slave.inc connection server_1; @@ -189,8 +189,8 @@ connection server_2; FLUSH NO_WRITE_TO_BINLOG TABLES; SELECT domain_id, COUNT(*) FROM mysql.gtid_slave_pos GROUP BY domain_id; domain_id COUNT(*) -0 2 -1 2 +0 3 +1 1 *** MDEV-4650: show variables; ERROR 1946 (HY000): Failed to load replication slave GTID position *** connection server_2; SET sql_log_bin=0; diff --git a/mysql-test/suite/rpl/r/rpl_mysql57_stm_temporal_round.result b/mysql-test/suite/rpl/r/rpl_mysql57_stm_temporal_round.result new file mode 100644 index 00000000000..bedd103c2a0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mysql57_stm_temporal_round.result @@ -0,0 +1,22 @@ +# +# MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave +# +include/master-slave.inc +[connection master] +connection slave; +CREATE TABLE t1 (id SERIAL, a DATETIME(3)); +include/stop_slave.inc +connection master; +include/rpl_stop_server.inc [server_number=1] +include/rpl_start_server.inc [server_number=1] +connection slave; +CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_log_file='master-bin.000001', master_log_pos=4; +include/start_slave.inc +connection master; +connection slave; +SELECT * FROM t1 ORDER BY id; +id a +1 2001-01-01 00:00:01.000 +include/stop_slave.inc +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_mysql80_stm_temporal_round.result b/mysql-test/suite/rpl/r/rpl_mysql80_stm_temporal_round.result new file mode 100644 index 00000000000..23b3217895a --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mysql80_stm_temporal_round.result @@ -0,0 +1,23 @@ +# +# MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave +# +include/master-slave.inc +[connection master] +connection slave; +CREATE TABLE t1 (id SERIAL, a DATETIME(3)); +include/stop_slave.inc +connection master; +include/rpl_stop_server.inc [server_number=1] +include/rpl_start_server.inc [server_number=1] +connection slave; +CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_log_file='master-bin.000001', master_log_pos=4; +include/start_slave.inc +connection master; +connection slave; +SELECT * FROM t1 ORDER BY id; +id a +1 2001-01-01 00:00:01.000 +2 2001-01-01 00:00:00.999 +include/stop_slave.inc +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result index ca202a66b0e..83343e52cab 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result @@ -12,6 +12,8 @@ SET GLOBAL slave_parallel_threads=10; CHANGE MASTER TO master_use_gtid=slave_pos; SET @old_parallel_mode=@@GLOBAL.slave_parallel_mode; SET GLOBAL slave_parallel_mode='optimistic'; +SET @old_gtid_cleanup_batch_size= @@GLOBAL.gtid_cleanup_batch_size; +SET GLOBAL gtid_cleanup_batch_size= 1000000; connection server_1; INSERT INTO t1 VALUES(1,1); BEGIN; @@ -131,6 +133,11 @@ c 204 205 206 +SELECT IF(COUNT(*) >= 30, "OK", CONCAT("Error: too few old rows found: ", COUNT(*))) +FROM mysql.gtid_slave_pos; +IF(COUNT(*) >= 30, "OK", CONCAT("Error: too few old rows found: ", COUNT(*))) +OK +SET GLOBAL gtid_cleanup_batch_size=1; *** Test @@skip_parallel_replication. *** connection server_2; include/stop_slave.inc @@ -651,9 +658,10 @@ DROP TABLE t1, t2, t3; include/save_master_gtid.inc connection server_2; include/sync_with_master_gtid.inc -Check that no more than the expected last four GTIDs are in mysql.gtid_slave_pos -select count(4) <= 4 from mysql.gtid_slave_pos order by domain_id, sub_id; -count(4) <= 4 +SELECT COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size +FROM mysql.gtid_slave_pos; +COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size 1 +SET GLOBAL gtid_cleanup_batch_size= @old_gtid_cleanup_batch_size; connection server_1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_gtid_mdev4484.test b/mysql-test/suite/rpl/t/rpl_gtid_mdev4484.test index 1e5d1abbb3b..5c17653da8a 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_mdev4484.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_mdev4484.test @@ -28,37 +28,79 @@ INSERT INTO t1 VALUES (1); # Inject an artificial error deleting entries, and check that the error handling code works. --connection slave --source include/stop_slave.inc +SET @old_gtid_cleanup_batch_size= @@GLOBAL.gtid_cleanup_batch_size; +SET GLOBAL gtid_cleanup_batch_size= 2; SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,gtid_slave_pos_simulate_failed_delete"; SET sql_log_bin= 0; -CALL mtr.add_suppression("Can't find file"); +CALL mtr.add_suppression("<DEBUG> Error deleting old GTID row"); SET sql_log_bin= 1; --source include/start_slave.inc --connection master -INSERT INTO t1 VALUES (2); +--disable_query_log +let $i = 20; +while ($i) { + eval INSERT INTO t1 VALUES ($i+10); + dec $i; +} +--enable_query_log +--save_master_pos --connection slave ---let $slave_sql_errno= 1942 ---source include/wait_for_slave_sql_error.inc -STOP SLAVE IO_THREAD; -SELECT domain_id, server_id, seq_no FROM mysql.gtid_slave_pos - ORDER BY domain_id, sub_id DESC LIMIT 1; +--sync_with_master + +# Now wait for the slave background thread to try to delete old rows and +# hit the error injection. +--let _TEST_MYSQLD_ERROR_LOG=$MYSQLTEST_VARDIR/log/mysqld.2.err +--perl + open F, '<', $ENV{'_TEST_MYSQLD_ERROR_LOG'} or die; + outer: while (1) { + inner: while (<F>) { + last outer if /<DEBUG> Error deleting old GTID row/; + } + # Easy way to do sub-second sleep without extra modules. + select(undef, undef, undef, 0.1); + } +EOF + +# Since we injected error in the cleanup code, the rows should remain in +# mysql.gtid_slave_pos. Check that we have at least 20 (more robust against +# non-deterministic cleanup and future changes than checking for exact number). +SELECT COUNT(*), MAX(seq_no) INTO @pre_count, @pre_max_seq_no + FROM mysql.gtid_slave_pos; +SELECT IF(@pre_count >= 20, "OK", CONCAT("Error: too few rows seen while errors injected: ", @pre_count)); SET GLOBAL debug_dbug= @old_dbug; ---source include/start_slave.inc --connection master -INSERT INTO t1 VALUES (3); +--disable_query_log +let $i = 20; +while ($i) { + eval INSERT INTO t1 VALUES ($i+40); + dec $i; +} +--enable_query_log --sync_slave_with_master --connection slave -SELECT domain_id, server_id, seq_no FROM mysql.gtid_slave_pos - ORDER BY domain_id, sub_id DESC LIMIT 1; -SELECT * FROM t1 ORDER BY i; - +# Now check that 1) rows are being deleted again after removing error +# injection, and 2) old rows are left that failed their delete while errors +# where injected (again compensating for non-deterministic deletion). +# Deletion is async and slightly non-deterministic, so we wait for at +# least 10 of the 20 new rows to be deleted. +let $wait_condition= + SELECT COUNT(*) <= 20-10 + FROM mysql.gtid_slave_pos + WHERE seq_no > @pre_max_seq_no; +--source include/wait_condition.inc +SELECT IF(COUNT(*) >= 1, "OK", CONCAT("Error: too few rows seen after errors no longer injected: ", COUNT(*))) + FROM mysql.gtid_slave_pos + WHERE seq_no <= @pre_max_seq_no; # Clean up --connection master DROP TABLE t1; +--connection slave +SET GLOBAL gtid_cleanup_batch_size= @old_gtid_cleanup_batch_size; --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mysql57_stm_temporal_round.test b/mysql-test/suite/rpl/t/rpl_mysql57_stm_temporal_round.test new file mode 100644 index 00000000000..675b7db0603 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mysql57_stm_temporal_round.test @@ -0,0 +1,58 @@ +--source include/have_binlog_format_statement.inc + +--echo # +--echo # MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave +--echo # + +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection slave +CREATE TABLE t1 (id SERIAL, a DATETIME(3)); +--source include/stop_slave.inc + +--connection master +--let $datadir= `SELECT @@datadir` + +--let $rpl_server_number= 1 +--source include/rpl_stop_server.inc + +--remove_file $datadir/master-bin.000001 + +# +# Simulate MySQL 5.7.x master +# +# mysql-5.7.11-stm-temporal-round-binlog.000001 was recorded against a +# table with this structure: +#CREATE TABLE t1 (id SERIAL, a DATETIME(3)); +# (note, the CREATE statement is not inside the binary log) +# +# using this command line: +# mysqld --log-bin --binlog-format=statement +# with the following single SQL statement: +# +#INSERT INTO t1 (a) VALUES ('2001-01-01 00:00:00.999999'); +# + +--copy_file $MYSQL_TEST_DIR/std_data/rpl/mysql-5.7.11-stm-temporal-round-binlog.000001 $datadir/master-bin.000001 + +--let $rpl_server_number= 1 +--source include/rpl_start_server.inc + +--source include/wait_until_connected_again.inc + +--connection slave +--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 +eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_log_file='master-bin.000001', master_log_pos=4; + +--source include/start_slave.inc + +--connection master +--sync_slave_with_master +SELECT * FROM t1 ORDER BY id; + +--source include/stop_slave.inc +DROP TABLE t1; + +--let $rpl_only_running_threads= 1 +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mysql80_stm_temporal_round.test b/mysql-test/suite/rpl/t/rpl_mysql80_stm_temporal_round.test new file mode 100644 index 00000000000..ad6df9d9993 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mysql80_stm_temporal_round.test @@ -0,0 +1,62 @@ +--source include/have_binlog_format_statement.inc + +--echo # +--echo # MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave +--echo # + +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection slave +CREATE TABLE t1 (id SERIAL, a DATETIME(3)); +--source include/stop_slave.inc + +--connection master +--let $datadir= `SELECT @@datadir` + +--let $rpl_server_number= 1 +--source include/rpl_stop_server.inc + +--remove_file $datadir/master-bin.000001 + +# +# Simulate MySQL 8.0.x master +# +# mysql-8.0.13-stm-temporal-round-binlog.000001 was recorded against a +# table with this structure: +#CREATE TABLE t1 (id SERIAL, a DATETIME(3)); +# (note, the CREATE statement is not inside the binary log) +# +# using this command line: +# mysqld --log-bin --binlog-format=statement --server-id=1 --character-set-server=latin1 +# with the following SQL script: +# +#SET NAMES latin1 COLLATE latin1_swedish_ci; +#SET sql_mode=''; +#INSERT INTO t1 (a) VALUES ('2001-01-01 00:00:00.999999'); +#SET sql_mode=TIME_TRUNCATE_FRACTIONAL; +#INSERT INTO t1 (a) VALUES ('2001-01-01 00:00:00.999999'); +# + +--copy_file $MYSQL_TEST_DIR/std_data/rpl/mysql-8.0.13-stm-temporal-round-binlog.000001 $datadir/master-bin.000001 + +--let $rpl_server_number= 1 +--source include/rpl_start_server.inc + +--source include/wait_until_connected_again.inc + +--connection slave +--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 +eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_log_file='master-bin.000001', master_log_pos=4; + +--source include/start_slave.inc + +--connection master +--sync_slave_with_master +SELECT * FROM t1 ORDER BY id; + +--source include/stop_slave.inc +DROP TABLE t1; + +--let $rpl_only_running_threads= 1 +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test index e08472d5f51..0060cf4416c 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test @@ -21,6 +21,10 @@ SET GLOBAL slave_parallel_threads=10; CHANGE MASTER TO master_use_gtid=slave_pos; SET @old_parallel_mode=@@GLOBAL.slave_parallel_mode; SET GLOBAL slave_parallel_mode='optimistic'; +# Run the first part of the test with high batch size and see that +# old rows remain in the table. +SET @old_gtid_cleanup_batch_size= @@GLOBAL.gtid_cleanup_batch_size; +SET GLOBAL gtid_cleanup_batch_size= 1000000; --connection server_1 @@ -108,7 +112,12 @@ SELECT * FROM t3 ORDER BY c; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY c; -#SHOW STATUS LIKE 'Slave_retried_transactions'; +# Check that we have a bunch of old rows left-over - they were not deleted +# due to high @@gtid_cleanup_batch_size. Then set a low +# @@gtid_cleanup_batch_size so we can test that rows start being deleted. +SELECT IF(COUNT(*) >= 30, "OK", CONCAT("Error: too few old rows found: ", COUNT(*))) + FROM mysql.gtid_slave_pos; +SET GLOBAL gtid_cleanup_batch_size=1; --echo *** Test @@skip_parallel_replication. *** @@ -557,25 +566,18 @@ DROP TABLE t1, t2, t3; --connection server_2 --source include/sync_with_master_gtid.inc -# Check for left-over rows in table mysql.gtid_slave_pos (MDEV-12147). -# -# There was a bug when a transaction got a conflict and was rolled back. It -# might have also handled deletion of some old rows, and these deletions would -# then also be rolled back. And since the deletes were never re-tried, old no -# longer needed rows would accumulate in the table without limit. -# -# The earlier part of this test file have plenty of transactions being rolled -# back. But the last DROP TABLE statement runs on its own and should never -# conflict, thus at this point the mysql.gtid_slave_pos table should be clean. -# -# To support @@gtid_pos_auto_engines, when a row is inserted in the table, it -# is associated with the engine of the table at insertion time, and it will -# only be deleted during record_gtid from a table of the same engine. Since we -# alter the table from MyISAM to InnoDB at the start of this test, we should -# end up with 4 rows: two left-over from when the table was MyISAM, and two -# left-over from the InnoDB part. ---echo Check that no more than the expected last four GTIDs are in mysql.gtid_slave_pos -select count(4) <= 4 from mysql.gtid_slave_pos order by domain_id, sub_id; +# Check that old rows are deleted from mysql.gtid_slave_pos. +# Deletion is asynchronous, so use wait_condition.inc. +# Also, there is a small amount of non-determinism in the deletion of old +# rows, so it is not guaranteed that there can never be more than +# @@gtid_cleanup_batch_size rows in the table; so allow a bit of slack +# here. +let $wait_condition= + SELECT COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size + FROM mysql.gtid_slave_pos; +--source include/wait_condition.inc +eval $wait_condition; +SET GLOBAL gtid_cleanup_batch_size= @old_gtid_cleanup_batch_size; --connection server_1 --source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 56f7a136983..71761df1ab3 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -1202,6 +1202,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME GTID_CLEANUP_BATCH_SIZE +SESSION_VALUE NULL +GLOBAL_VALUE 64 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 64 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Normally does not need tuning. How many old rows must accumulate in the mysql.gtid_slave_pos table before a background job will be run to delete them. Can be increased to reduce number of commits if using many different engines with --gtid_pos_auto_engines, or to reduce CPU overhead if using a huge number of different gtid_domain_ids. Can be decreased to reduce number of old rows in the table. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 2147483647 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME GTID_CURRENT_POS SESSION_VALUE NULL GLOBAL_VALUE |