From 403e6615fdd3f5f4de6d159457b2bd2fe566e0ad Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Fri, 26 Jul 2019 18:17:55 +0300 Subject: List of unstable tests for 10.1.41 release (updated) --- mysql-test/unstable-tests | 38 +++++++++++++++++++++++++++----------- 1 file changed, 27 insertions(+), 11 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/unstable-tests b/mysql-test/unstable-tests index 0c7756d70ae..f09647523af 100644 --- a/mysql-test/unstable-tests +++ b/mysql-test/unstable-tests @@ -23,13 +23,14 @@ # ############################################################################## # -# Based on 10.1 1f498f901b394e869c430b777fdc233ab98d428f +# Based on 10.1 2382cd14a8c96cf8b4375d7338abfa6e1b333a86 main.alter_table_trans : MDEV-12084 - timeout main.analyze_stmt : Modified in 10.1.39 main.analyze_stmt_slow_query_log : MDEV-12237 - Wrong result main.auth_named_pipe : MDEV-14724 - System error 2 main.bootstrap : Modified in 10.1.41 +main.connect_debug : Modified in 10.1.41 main.count_distinct2 : MDEV-11768 - timeout main.create_delayed : MDEV-10605 - failed with timeout main.create_drop_event : MDEV-16271 - Wrong result @@ -81,11 +82,14 @@ main.host_cache_size_functionality : MDEV-10606 - sporadic failure on shutdown main.index_intersect_innodb : MDEV-10643 - failed with timeout main.index_merge_innodb : MDEV-7142 - Wrong execution plan, timeout with valgrind; modified in 10.1.39 main.index_merge_myisam : Include files modified in 10.1.39 +main.information_schema_parameters : Modified in 10.1.41 main.information_schema_prepare : Added in 10.1.39 main.innodb_mysql_lock : MDEV-7861 - sporadic lock detection failure +main.join : Modified in 10.1.41 main.join_cache : Modified in 10.1.41 main.join_nested : Modified in 10.1.41 main.join_outer : Modified in 10.1.41 +main.join_outer_innodb : Modified in 10.1.41 main.kill_processlist-6619 : MDEV-10793 - wrong result in processlist; modified in 10.1.39 main.loaddata : MDEV-19368 - mysqltest failed but provided no output; modified in 10.1.39 main.log_slow : MDEV-13263 - Wrong result; modified in 10.1.39 @@ -117,7 +121,10 @@ main.partition : Modified in 10.1.39 main.partition_debug_sync : MDEV-15669 - Deadlock found when trying to get lock main.partition_innodb : Modified in 10.1.39 main.partition_innodb_plugin : MDEV-12901 - Valgrind warnings -main.ps : MDEV-11017 - sporadic wrong Prepared_stmt_count; modified in 10.1.39 +main.plugin : Modified in 10.1.41 +main.plugin_not_embedded : Modified in 10.1.41 +main.ps : MDEV-11017 - sporadic wrong Prepared_stmt_count; modified in 10.1.41 +main.ps_innodb : Added in 10.1.41 main.query_cache : MDEV-12895 - Wrong result main.query_cache_debug : MDEV-15281 - Resize or similar command in progress main.range_vs_index_merge_innodb : MDEV-15283 - Server has gone away @@ -135,6 +142,7 @@ main.stat_tables_par_innodb : MDEV-14155 - wrong rounding main.statistics : Modified in 10.1.39 main.status : MDEV-8510 - sporadic wrong result main.status2 : Modified in 10.1.39 +main.subselect : Modified in 10.1.41 main.subselect_innodb : MDEV-10614 - sporadic wrong results main.subselect_sj : Modified in 10.1.41 main.subselect_sj_mat : Modified in 10.1.41 @@ -150,7 +158,7 @@ main.type_decimal : Modified in 10.1.39 main.type_year : Modified in 10.1.39 main.update_innodb : Modified in 10.1.39 main.userstat : Modified in 10.1.39 -main.view : Modified in 10.1.39 +main.view : Modified in 10.1.41 main.view_grant : Modified in 10.1.39 main.wait_timeout : Lost connection to MySQL server during query main.xa : MDEV-11769 - lock wait timeout @@ -169,13 +177,17 @@ archive-test_sql_discovery.discover : MDEV-16817 - Table marked as crashed #----------------------------------------------------------------------- -binlog.binlog_commit_wait : MDEV-10150 - Error: too much time elapsed -binlog.binlog_innodb_stm : Added in 10.1.39 -binlog.binlog_killed : MDEV-12925 - Wrong result -binlog.binlog_mysqlbinlog2 : Modified in 10.1.39 -binlog.binlog_mysqlbinlog_stop_never : Added in 10.1.41 -binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint -binlog.load_data_stm_view : MDEV-16948 - Wrong result +binlog.binlog_commit_wait : MDEV-10150 - Error: too much time elapsed +binlog.binlog_innodb_stm : Added in 10.1.39 +binlog.binlog_killed : MDEV-12925 - Wrong result +binlog.binlog_mysqlbinlog2 : Modified in 10.1.39 +binlog.binlog_mysqlbinlog_stop_never : Added in 10.1.41 +binlog.binlog_parallel_replication_marks_row : Include file modified in 10.1.41 +binlog.binlog_parallel_replication_marks_stm_mix : Include file modified in 10.1.41 +binlog.binlog_row_drop_tmp_tbl : Include file modified in 10.1.41 +binlog.binlog_stm_drop_tmp_tbl : MDEV-20188 - Unknown table on exec; include file modified in 10.1.41 +binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint +binlog.load_data_stm_view : MDEV-16948 - Wrong result #----------------------------------------------------------------------- @@ -279,6 +291,7 @@ innodb.innodb-64k-crash : MDEV-13872 - Failure and crash on innodb.innodb-alter-debug : MDEV-13182 - InnoDB: adjusting FSP_SPACE_FLAGS innodb.innodb-alter-nullable : Modified in 10.1.39 innodb.innodb-alter-table : MDEV-10619 - Testcase timeout +innodb.innodb-autoinc : Modified in 10.1.41 innodb.innodb-blob : MDEV-12053 - Client crash innodb.innodb-corrupted-table : Modified in 10.1.39 innodb.innodb-fk : MDEV-13832 - Assertion failure on shutdown @@ -309,7 +322,6 @@ innodb.xa_recovery : MDEV-15279 - mysqld got exception #----------------------------------------------------------------------- -innodb_fts.innodb-fts-fic : MDEV-14154 - Assertion failure innodb_fts.innodb_ft_aux_table : Added in 10.1.41 innodb_fts.innodb_fts_misc_debug : MDEV-14156 - Unexpected warning @@ -397,6 +409,9 @@ roles.flush_roles-17898 : Modified in 10.1.39 #----------------------------------------------------------------------- rpl.circular_serverid0 : MDEV-19372 - ASAN heap-use-after-free +rpl.create_or_replace_mix : Include file modified in 10.1.41 +rpl.create_or_replace_row : Include file modified in 10.1.41 +rpl.create_or_replace_statement : Include file modified in 10.1.41 rpl.create_select : MDEV-14121 - Assertion failure rpl.kill_race_condition : Modified in 10.1.41 rpl.last_insert_id : MDEV-10625 - warnings in error log @@ -447,6 +462,7 @@ rpl.rpl_rewrt_db : Modified in 10.1.39 rpl.rpl_row_basic_11bugs : MDEV-12171 - Server failed to start rpl.rpl_row_basic_2myisam : MDEV-13875 - command "diff_files" failed rpl.rpl_row_drop_create_temp_table : MDEV-14487 - Wrong result +rpl.rpl_row_drop_temp_table : Added in 10.1.41 rpl.rpl_row_img_blobs : MDEV-13875 - command "diff_files" failed rpl.rpl_row_img_eng_min : MDEV-13875 - command "diff_files" failed rpl.rpl_row_img_eng_noblob : MDEV-13875 - command "diff_files" failed -- cgit v1.2.1 From d6886b95d0d48d80deffa7f2190b8abd5ee8236b Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Sat, 27 Jul 2019 00:19:28 +0300 Subject: List of unstable tests for 10.2.26 release (updated) --- mysql-test/unstable-tests | 43 ++++++++++++++++++++++++++++++------------- 1 file changed, 30 insertions(+), 13 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/unstable-tests b/mysql-test/unstable-tests index 8d6019eca9a..6d71041785e 100644 --- a/mysql-test/unstable-tests +++ b/mysql-test/unstable-tests @@ -23,7 +23,7 @@ # ############################################################################## # -# Based on 10.2 97055e6b11508b53203aaadfe4618f543891c575 +# Based on 10.2 32c6f40a6319d493e5270c72ac5d27dc99d1b242 main.alter_table_trans : MDEV-12084 - timeout main.analyze_stmt_slow_query_log : MDEV-12237 - Wrong result @@ -31,6 +31,7 @@ main.auth_named_pipe : MDEV-14724 - System error 2 main.charset_client_win : Added in 10.2.26 main.connect : MDEV-17282 - Wrong result main.connect2 : MDEV-13885 - Server crash +main.connect_debug : Modified in 10.2.26 main.count_distinct2 : MDEV-11768 - timeout main.create_delayed : MDEV-10605 - failed with timeout main.create_drop_event : MDEV-16271 - Wrong result @@ -39,10 +40,10 @@ main.ctype_upgrade : MDEV-16945 - Error upon mysql_upgrade main.ctype_utf16 : MDEV-10675: timeout or extra warnings main.ctype_utf8_def_upgrade : Added in 10.2.25 main.debug_sync : MDEV-10607 - internal error -main.derived : Modified in 10.2.25 +main.derived : Modified in 10.2.26 main.derived_cond_pushdown : Modified in 10.2.25 main.derived_opt : MDEV-11768 - timeout -main.derived_view : Modified in 10.2.25 +main.derived_view : Modified in 10.2.26 main.dirty_close : MDEV-19368 - mysqltest failed but provided no output main.distinct : MDEV-14194 - Crash main.drop_bad_db_type : MDEV-15676 - Wrong result @@ -56,10 +57,13 @@ main.gis : MDEV-13411 - wrong result on P8 main.host_cache_size_functionality : MDEV-10606 - sporadic failure on shutdown main.index_intersect_innodb : MDEV-10643 - failed with timeout main.index_merge_innodb : MDEV-7142 - Plan mismatch +main.information_schema_parameters : Modified in 10.2.26 main.innodb_mysql_lock : MDEV-7861 - Wrong result +main.join : Modified in 10.2.26 main.join_cache : Modified in 10.2.25 main.join_nested : Modified in 10.2.25 main.join_outer : Modified in 10.2.26 +main.join_outer_innodb : Modified in 10.2.26 main.kill-2 : MDEV-13257 - Wrong result main.kill_processlist-6619 : MDEV-10793 - Wrong result main.loaddata : MDEV-19368 - mysqltest failed but provided no output @@ -88,8 +92,11 @@ main.order_by_optimizer_innodb : MDEV-10683 - Wrong result main.partition_debug_sync : MDEV-15669 - Deadlock found when trying to get lock main.partition_innodb_plugin : MDEV-12901 - Valgrind warnings main.partition_innodb_semi_consistent : MDEV-19411 - Failed to start mysqld.1 +main.plugin : Modified in 10.2.26 main.plugin_auth : Modified in 10.2.26 -main.ps : MDEV-11017 - Wrong result +main.plugin_not_embedded : Modified in 10.2.26 +main.ps : MDEV-11017 - Wrong result; modified in 10.2.26 +main.ps_innodb : Added in 10.2.26 main.query_cache : MDEV-16180 - Wrong result main.query_cache_debug : MDEV-15281 - Query cache is disabled main.range_vs_index_merge_innodb : MDEV-15283 - Server has gone away @@ -111,6 +118,7 @@ main.stat_tables : Modified in 10.2.25 main.stat_tables_par : MDEV-13266 - Wrong result main.stat_tables_par_innodb : MDEV-14155 - Wrong rounding main.status : MDEV-13255 - Wrong result +main.subselect : Modified in 10.2.26 main.subselect_innodb : MDEV-10614 - Sporadic wrong results main.subselect_no_semijoin : Modified in 10.2.25 main.subselect_sj : Modified in 10.2.25 @@ -122,6 +130,7 @@ main.type_blob : MDEV-15195 - Wrong result main.type_datetime : Modified in 10.2.26 main.type_datetime_hires : MDEV-10687 - Timeout main.userstat : MDEV-12904 - SSL errors +main.view : Modified in 10.2.26 main.wait_timeout : MDEV-19023 - Lost connection to MySQL server during query main.win : Modified in 10.2.25 main.xa : MDEV-11769 - lock wait timeout @@ -139,13 +148,17 @@ archive-test_sql_discovery.discover : MDEV-16817 - Table marked as crashed #----------------------------------------------------------------------- -binlog.binlog_commit_wait : MDEV-10150 - Mismatch -binlog.binlog_killed : MDEV-12925 - Wrong result -binlog.binlog_max_extension : MDEV-19762 - Crash on shutdown -binlog.binlog_mysqlbinlog_stop_never : Added in 10.2.26 -binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint -binlog.flashback-largebinlog : MDEV-19764 - Out of memory; added in 10.2.25 -binlog.load_data_stm_view : MDEV-16948 - Wrong result +binlog.binlog_commit_wait : MDEV-10150 - Mismatch +binlog.binlog_killed : MDEV-12925 - Wrong result +binlog.binlog_max_extension : MDEV-19762 - Crash on shutdown +binlog.binlog_mysqlbinlog_stop_never : Added in 10.2.26 +binlog.binlog_parallel_replication_marks_row : Include file modified in 10.2.26 +binlog.binlog_parallel_replication_marks_stm_mix : Include file modified in 10.2.26 +binlog.binlog_row_drop_tmp_tbl : Include file modified in 10.2.26 +binlog.binlog_stm_drop_tmp_tbl : MDEV-20188 - Unknown table on exec; include file modified in 10.2.26 +binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint +binlog.flashback-largebinlog : MDEV-19764 - Out of memory; added in 10.2.25 +binlog.load_data_stm_view : MDEV-16948 - Wrong result #----------------------------------------------------------------------- @@ -285,6 +298,7 @@ innodb.innodb-64k-crash : MDEV-13872 - Failure and crash on innodb.innodb-alter-debug : MDEV-13182 - InnoDB: adjusting FSP_SPACE_FLAGS innodb.innodb-alter-table : MDEV-10619 - Testcase timeout innodb.innodb-alter-tempfile : MDEV-15285 - Table already exists +innodb.innodb-autoinc : Modified in 10.2.26 innodb.innodb-blob : MDEV-12053 - Client crash innodb.innodb-change-buffer-recovery : MDEV-19115 - Lost connection to MySQL server during query innodb.innodb-fk : MDEV-13832 - Assertion failure on shutdown @@ -348,7 +362,6 @@ innodb.xa_recovery : MDEV-15279 - mysqld got exception #----------------------------------------------------------------------- innodb_fts.fulltext2 : Modified in 10.2.26 -innodb_fts.innodb-fts-fic : MDEV-14154 - Assertion failure innodb_fts.innodb_ft_aux_table : Added in 10.2.25 innodb_fts.innodb_fts_misc_debug : MDEV-14156 - Unexpected warning innodb_fts.innodb_fts_plugin : MDEV-13888 - Errors in server log @@ -539,6 +552,9 @@ roles.create_and_grant_role : MDEV-11772 - wrong result rpl.circular_serverid0 : MDEV-19372 - ASAN heap-use-after-free rpl.create_or_replace2 : MDEV-19412 - Lost connection to MySQL server +rpl.create_or_replace_mix : Include file modified in 10.2.26 +rpl.create_or_replace_row : Include file modified in 10.2.26 +rpl.create_or_replace_statement : Include file modified in 10.2.26 rpl.create_select : MDEV-14121 - Assertion failure rpl.kill_race_condition : Modified in 10.2.26 rpl.last_insert_id : MDEV-10625 - warnings in error log @@ -598,6 +614,7 @@ rpl.rpl_row_001 : MDEV-16653 - Internal check fails rpl.rpl_row_basic_11bugs : MDEV-12171 - Server failed to start rpl.rpl_row_basic_2myisam : MDEV-13875 - command "diff_files" failed rpl.rpl_row_drop_create_temp_table : MDEV-14487 - Wrong result +rpl.rpl_row_drop_temp_table : Added in 10.2.26 rpl.rpl_row_img_blobs : MDEV-13875 - command "diff_files" failed rpl.rpl_row_img_eng_min : MDEV-13875 - diff_files failed rpl.rpl_row_img_eng_noblob : MDEV-13875 - command "diff_files" failed @@ -678,7 +695,7 @@ stress.ddl_innodb : MDEV-10635 - Testcase timeout sys_vars.autocommit_func2 : MDEV-9329 - Fails on Ubuntu/s390x sys_vars.delayed_insert_limit_func : MDEV-17683 - Wrong result sys_vars.innodb_buffer_pool_dump_at_shutdown_basic : MDEV-14280 - Unexpected error -sys_vars.innodb_ft_result_cache_limit : Modified in 10.2.25 +sys_vars.innodb_ft_result_cache_limit : Modified in 10.2.26 sys_vars.keep_files_on_create_basic : MDEV-10676 - timeout sys_vars.log_slow_admin_statements_func : MDEV-12235 - Server crash sys_vars.rpl_init_slave_func : MDEV-10149 - Test assertion -- cgit v1.2.1 From f79212f96d2b9ad23037201f6896ba303a222b5d Mon Sep 17 00:00:00 2001 From: Anel Husakovic Date: Wed, 31 Jul 2019 02:49:15 -0700 Subject: Fix extra space in mysql-test README --- mysql-test/README | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/README b/mysql-test/README index c4ded4e8e79..08a8200e27f 100644 --- a/mysql-test/README +++ b/mysql-test/README @@ -60,7 +60,7 @@ In the file, put a set of SQL statements that create some tables, load test data, and run some queries to manipulate it. Your test should begin by dropping the tables you are going to create and -end by dropping them again. This ensures that you can run the test over +end by dropping them again. This ensures that you can run the test over and over again. If you are using mysqltest commands in your test case, you should create @@ -81,7 +81,7 @@ comments, you can create the result file in one of the following ways: # mysqltest --record --database test --result-file=r/test_case_name.result < t/test_case_name.test -When this is done, take a look at r/test_case_name.result . +When this is done, take a look at r/test_case_name.result. If the result is incorrect, you have found a bug. In this case, you should edit the test result to the correct results so that we can verify that the bug is corrected in future releases. -- cgit v1.2.1 From eef7540405849287abf94c0332adfc50cf4f13c5 Mon Sep 17 00:00:00 2001 From: Sujatha Date: Mon, 5 Aug 2019 14:34:13 +0530 Subject: MDEV-18930: Failed CREATE OR REPLACE TEMPORARY not written into binary log makes data on master and slave diverge Problem: ======= Failed CREATE OR REPLACE TEMPORARY TABLE statement which dropped the table but failed at a later stage of creation of temporary table is not written to binarylog in row based replication. This causes the slave to diverge. Analysis: ======== CREATE OR REPLACE statements work as shown below. CREATE OR REPLACE TABLE table_name (a int); is basically the same as: DROP TABLE IF EXISTS table_name; CREATE TABLE table_name (a int); Hence every CREATE OR REPLACE TABLE command which dropped the table should be written to binary log, even when following CREATE TABLE part fails. In order to achieve this, during the execution of CREATE OR REPLACE command, when a table is dropped 'thd->log_current_statement' flag is set. When table creation results in an error within 'mysql_create_table' code, the error handling part looks for this flag. If it is set the failed CREATE OR REPLACE statement is written into the binary log inspite of error. This ensure that slave doesn't diverge from the master. In case of row based replication the error handling code returns very early, if the table is of type temporary. This is done based on the assumption that temporary tables are not replicated in row based replication. It fails to handle the cases where a temporary table was created as part of statement based replication at an earlier stage and the binary log format was changed to row because of an unsafe statement. In this case when a CREATE OR REPLACE statement is executed on this temporary table it will dropped but the query will not be written to binary log. Hence slave diverges. Fix: === In error handling code check the return status of create table operation. If it is successful and replication mode is row based and table is of type temporary then return. Other wise proceed further to the code which checks for thd->log_current_statement flag and does appropriate logging. --- .../suite/rpl/r/rpl_create_or_replace_fail.result | 18 +++++++ .../suite/rpl/t/rpl_create_or_replace_fail.test | 56 ++++++++++++++++++++++ 2 files changed, 74 insertions(+) create mode 100644 mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result create mode 100644 mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result b/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result new file mode 100644 index 00000000000..57178f0efbe --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result @@ -0,0 +1,18 @@ +include/master-slave.inc +[connection master] +CREATE TEMPORARY TABLE t1 (a INT NOT NULL); +LOAD DATA INFILE 'x' INTO TABLE x; +ERROR 42S02: Table 'test.x' doesn't exist +CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x); +ERROR HY000: Cannot create temporary table with partitions +"************** DROP TEMPORARY TABLE Should be present in Binary log **************" +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t1 (a INT NOT NULL) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x) +CREATE TABLE t1 (b INT); +INSERT INTO t1 VALUES (NULL); +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test b/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test new file mode 100644 index 00000000000..e75f34b0b56 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test @@ -0,0 +1,56 @@ +# ==== Purpose ==== +# +# Test verifies that failed CREATE OR REPLACE TEMPORARY TABLE statement which +# dropped the table but failed at a later stage of creation of temporary table +# is written to binarylog in row based replication. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Have mixed based replication mode. +# 1 - Create a temporary table. It will be replicated as mixed replication +# mode is in use. +# 2 - Execute an unsafe statement which will switch current statement +# binlog format to 'ROW'. i.e If binlog_format=MIXED, there are open +# temporary tables, and an unsafe statement is executed, then subsequent +# statements are logged in row format. +# 3 - Execute a CREATE OR REPLACE TEMPORARY TABLE statement which tries to +# create partitions on temporary table. Since it is not supported it will +# fail. +# 4 - Check the binary log output to ensure that the failed statement is +# written to the binary log. +# 5 - Slave should be up and running and in sync with master. +# +# ==== References ==== +# +# MDEV-18930: Failed CREATE OR REPLACE TEMPORARY not written into binary log +# makes data on master and slave diverge +# + +--source include/have_partition.inc +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc + +CREATE TEMPORARY TABLE t1 (a INT NOT NULL); + +# Execute an unsafe statement which switches replication mode internally from +# "STATEMENT" to "ROW". +--error ER_NO_SUCH_TABLE +LOAD DATA INFILE 'x' INTO TABLE x; + +--error ER_PARTITION_NO_TEMPORARY +CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x); + +--echo "************** DROP TEMPORARY TABLE Should be present in Binary log **************" +--source include/show_binlog_events.inc + +CREATE TABLE t1 (b INT); +INSERT INTO t1 VALUES (NULL); +--sync_slave_with_master + +# Cleanup +--connection master +DROP TABLE t1; + +--source include/rpl_end.inc + -- cgit v1.2.1 From 47f8a18fec604983e47fdf7c822d94b26d85cade Mon Sep 17 00:00:00 2001 From: Thirunarayanan Balathandayuthapani Date: Wed, 7 Aug 2019 12:35:04 +0530 Subject: MDEV-20247 Replication hangs with "preparing" and never starts - The commit ab6dd774082c57f48d998e03655c06b672799b2d wrongly sets the condition inside innobase_srv_conc_enter_innodb(). Problem is that InnoDB makes the thread to sleep indefinitely if it is a replication slave thread. Thanks to Sujatha Sivakumar for contributing the replication test case. --- .../rpl/r/rpl_sync_with_innodb_thd_conc.result | 13 +++++++ .../suite/rpl/t/rpl_sync_with_innodb_thd_conc.test | 41 ++++++++++++++++++++++ 2 files changed, 54 insertions(+) create mode 100644 mysql-test/suite/rpl/r/rpl_sync_with_innodb_thd_conc.result create mode 100644 mysql-test/suite/rpl/t/rpl_sync_with_innodb_thd_conc.test (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_sync_with_innodb_thd_conc.result b/mysql-test/suite/rpl/r/rpl_sync_with_innodb_thd_conc.result new file mode 100644 index 00000000000..0ed894336a1 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_sync_with_innodb_thd_conc.result @@ -0,0 +1,13 @@ +include/master-slave.inc +[connection master] +SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency; +SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; +SET GLOBAL innodb_thread_concurrency = 100; +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +include/diff_tables.inc [master:t, slave:t] +"===== Clean up=======" +DROP TABLE t; +SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency; +SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_sync_with_innodb_thd_conc.test b/mysql-test/suite/rpl/t/rpl_sync_with_innodb_thd_conc.test new file mode 100644 index 00000000000..b4c2971d2fb --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_sync_with_innodb_thd_conc.test @@ -0,0 +1,41 @@ +# ==== Purpose ==== +# +# Test verifies that replication shouldn't hang when number of active threads +# on the slave server are less than the allowed innodb_thread_concurrency value. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Have master slave replication setup with engine being Innodb. +# 1 - Configure innodb_thread_concurrency = 100. +# 2 - Do some DML on master and sync the slave with master. +# 3 - Ensure replication doesn't hang. +# +# ==== References ==== +# +# MDEV-20247: Replication hangs with "preparing" and never starts +# + +--source include/master-slave.inc +--source include/have_innodb.inc + +--connection slave +SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency; +SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; +SET GLOBAL innodb_thread_concurrency = 100; + +--connection master +CREATE TABLE t(f INT) ENGINE=INNODB; +INSERT INTO t VALUES (10); +--sync_slave_with_master + +--let $diff_tables=master:t, slave:t +--source include/diff_tables.inc + +--echo "===== Clean up=======" +--connection master +DROP TABLE t; +--sync_slave_with_master +SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency; +SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay; +--source include/rpl_end.inc -- cgit v1.2.1 From d39d5dd2bc4aecd31adaa20e428425b75c972af3 Mon Sep 17 00:00:00 2001 From: Vlad Lesin Date: Mon, 29 Jul 2019 14:12:19 +0300 Subject: MDEV-20060: Failing assertion: srv_log_file_size <= 512ULL << 30 while preparing backup The general reason why innodb redo log file is limited by 512G is that log_block_convert_lsn_to_no() returns value limited by 1G. But there is no need to have unique log block numbers in log group. The fix removes 512G limit and limits log group size by (uint32_t maximum value) * (minimum page size), which, in turns, can be removed if fil_io() is no longer used for innodb redo log io. --- mysql-test/mysql-test-run.pl | 1 + mysql-test/suite/mariabackup/big_innodb_log.result | 30 ++++++++ mysql-test/suite/mariabackup/big_innodb_log.test | 87 ++++++++++++++++++++++ mysql-test/suite/sys_vars/r/sysvars_innodb.result | 2 +- 4 files changed, 119 insertions(+), 1 deletion(-) create mode 100644 mysql-test/suite/mariabackup/big_innodb_log.result create mode 100644 mysql-test/suite/mariabackup/big_innodb_log.test (limited to 'mysql-test') diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index a29ae0b67ae..47f697ae0de 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3333,6 +3333,7 @@ sub mysql_install_db { # Create the bootstrap.sql file # ---------------------------------------------------------------------- my $bootstrap_sql_file= "$opt_vardir/log/bootstrap.sql"; + $ENV{'MYSQL_BOOTSTRAP_SQL_FILE'}= $bootstrap_sql_file; if (! -e $bootstrap_sql_file) { diff --git a/mysql-test/suite/mariabackup/big_innodb_log.result b/mysql-test/suite/mariabackup/big_innodb_log.result new file mode 100644 index 00000000000..6577c0c9aae --- /dev/null +++ b/mysql-test/suite/mariabackup/big_innodb_log.result @@ -0,0 +1,30 @@ +# Kill the server +CREATE TABLE t(i INT) ENGINE InnoDB; +INSERT INTO t VALUES +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), +(0), (1), (2), (3), (4), (5), (6), (7), (8), (9); +# xtrabackup backup, execute the following query after test.t is copied: +# BEGIN NOT ATOMIC INSERT INTO test.t SELECT * FROM test.t; UPDATE test.t SET i = 10 WHERE i = 0; DELETE FROM test.t WHERE i = 1; END +SELECT count(*) FROM t WHERE i = 0; +count(*) +0 +# xtrabackup prepare +# shutdown server +# remove datadir +# xtrabackup move back +# restart server +SELECT count(*) FROM t WHERE i = 0; +count(*) +0 +Ok +Ok +DROP TABLE t; +# Kill the server diff --git a/mysql-test/suite/mariabackup/big_innodb_log.test b/mysql-test/suite/mariabackup/big_innodb_log.test new file mode 100644 index 00000000000..05dc3aa39c5 --- /dev/null +++ b/mysql-test/suite/mariabackup/big_innodb_log.test @@ -0,0 +1,87 @@ +# The general reason why innodb redo log file is limited by 512G is that +# log_block_convert_lsn_to_no() returns value limited by 1G. But there is no +# need to have unique log block numbers in log group. This test forces innodb +# to generate redo log files with non-unique log block numbers and tests +# recovery process with such numbers. +--source include/have_innodb.inc +--source include/have_debug.inc + +--let MYSQLD_DATADIR= `select @@datadir` +let $MYSQLD_BOOTSTRAP_CMD= $MYSQLD_BOOTSTRAP_CMD --datadir=$MYSQLD_DATADIR --debug-dbug=+d,innodb_small_log_block_no_limit; + +--source include/kill_mysqld.inc +--rmdir $MYSQLD_DATADIR +--mkdir $MYSQLD_DATADIR +--mkdir $MYSQLD_DATADIR/mysql +--mkdir $MYSQLD_DATADIR/test +--exec $MYSQLD_BOOTSTRAP_CMD < $MYSQL_BOOTSTRAP_SQL_FILE >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 +let $old_restart_parameters=$restart_parameters; +let $restart_parameters= $old_restart_parameters --debug-dbug=+d,innodb_small_log_block_no_limit; +--source include/start_mysqld.inc + +CREATE TABLE t(i INT) ENGINE InnoDB; +INSERT INTO t VALUES + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), + (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); + +--let after_copy_test_t=BEGIN NOT ATOMIC INSERT INTO test.t SELECT * FROM test.t; UPDATE test.t SET i = 10 WHERE i = 0; DELETE FROM test.t WHERE i = 1; END + +--echo # xtrabackup backup, execute the following query after test.t is copied: +--echo # $after_copy_test_t +let $targetdir=$MYSQLTEST_VARDIR/tmp/backup; + +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --parallel=10 --target-dir=$targetdir --dbug=+d,mariabackup_events,innodb_small_log_block_no_limit; +--enable_result_log + +--let $total_before=`SELECT count(*) FROM t` +SELECT count(*) FROM t WHERE i = 0; +--let $updated_before=`SELECT count(*) FROM t WHERE i = 10` + +echo # xtrabackup prepare; +--disable_result_log +exec $XTRABACKUP --prepare --target-dir=$targetdir --dbug=+d,innodb_small_log_block_no_limit; +--source include/restart_and_restore.inc +--enable_result_log + +--let $total_after=`SELECT count(*) FROM t` +SELECT count(*) FROM t WHERE i = 0; +--let $updated_after=`SELECT count(*) FROM t WHERE i = 10` + +if ($total_before == $total_after) { +--echo Ok +} +if ($total_before != $total_after) { +--echo Failed +} +if ($updated_before == $updated_after) { +--echo Ok +} +if ($updated_before != $updated_after) { +--echo Failed +} + +DROP TABLE t; +rmdir $targetdir; +--source include/kill_mysqld.inc +--rmdir $MYSQLD_DATADIR + +perl; +use lib "lib"; +use My::File::Path; +my $install_db_dir = ($ENV{MTR_PARALLEL} == 1) ? + "$ENV{'MYSQLTEST_VARDIR'}/install.db" : + "$ENV{'MYSQLTEST_VARDIR'}/../install.db"; +copytree($install_db_dir, $ENV{'MYSQLD_DATADIR'}); +EOF + +--let $restart_parameters= $old_restart_parameters +--source include/start_mysqld.inc diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 497366ebf3d..20f2db8ba70 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -1793,7 +1793,7 @@ VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Size of each log file in a log group. NUMERIC_MIN_VALUE 1048576 -NUMERIC_MAX_VALUE 549755813888 +NUMERIC_MAX_VALUE 17592186044415 NUMERIC_BLOCK_SIZE 65536 ENUM_VALUE_LIST NULL READ_ONLY YES -- cgit v1.2.1 From 5fa2eb6f3d8210ce73d18e518108f32c29b11d63 Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 8 Aug 2019 20:03:21 +0300 Subject: Fixed connect_debug.test to not depend on system error message --- mysql-test/t/connect_debug.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/t/connect_debug.test b/mysql-test/t/connect_debug.test index 7a2f2872b79..300a2de0fbd 100644 --- a/mysql-test/t/connect_debug.test +++ b/mysql-test/t/connect_debug.test @@ -17,6 +17,7 @@ drop user bad; # set global debug_dbug='+d,auth_invalid_plugin'; create user 'bad' identified by 'worse'; +--replace_regex /loaded: [^\n]*/loaded: invalid plugin name/ --error 1 --exec $MYSQL --default-auth=mysql_old_password --user=bad --password=worse 2>&1 set global debug_dbug=@old_dbug; -- cgit v1.2.1 From 6765cc607764c920bc5fbf4128bb526b73f15c2e Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 8 Aug 2019 20:10:00 +0300 Subject: Fixed assertion Assertion `!table->pos_in_locked_tables' failed MDEV-17717 Assertion `!table->pos_in_locked_tables' failed in tc_release_table on flushing RocksDB table under SERIALIZABLE MDEV-17998 Deadlock and eventual Assertion `!table->pos_in_locked_tables' failed in tc_release_table on KILL_TIMEOUT MDEV-19591 Assertion `!table->pos_in_locked_tables' failed in tc_release_table upon altering table into S3 under lock. The problem was that thd->open_tables->pos_in_locked_tables was not reset when alter table failed to reopen a locked table. --- mysql-test/r/kill.result | 17 +++++++++++++++++ mysql-test/t/kill.test | 21 +++++++++++++++++++++ 2 files changed, 38 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result index dc1cb9252da..c2ad72e4240 100644 --- a/mysql-test/r/kill.result +++ b/mysql-test/r/kill.result @@ -399,3 +399,20 @@ DROP USER u1@localhost; SET DEBUG_SYNC = 'RESET'; DROP FUNCTION MY_KILL; set global sql_mode=default; +# +# MDEV-17998 +# Deadlock and eventual Assertion `!table->pos_in_locked_tables' failed +# in tc_release_table on KILL_TIMEOUT +# +SET max_statement_time= 2; +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (b INT, c INT); +LOCK TABLES v1 READ, t2 WRITE, t1 WRITE; +ALTER TABLE t1 CHANGE f1 f2 DOUBLE; +Got one of the listed errors +ALTER TABLE t2 DROP c; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +UNLOCK TABLES; +DROP VIEW v1; +DROP TABLE t1, t2; diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index b6000ffced1..a188cee52f2 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -642,3 +642,24 @@ SET DEBUG_SYNC = 'RESET'; DROP FUNCTION MY_KILL; set global sql_mode=default; + +--echo # +--echo # MDEV-17998 +--echo # Deadlock and eventual Assertion `!table->pos_in_locked_tables' failed +--echo # in tc_release_table on KILL_TIMEOUT +--echo # + +SET max_statement_time= 2; + +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (b INT, c INT); + +LOCK TABLES v1 READ, t2 WRITE, t1 WRITE; +--error ER_BAD_FIELD_ERROR,ER_STATEMENT_TIMEOUT +ALTER TABLE t1 CHANGE f1 f2 DOUBLE; +--error ER_STATEMENT_TIMEOUT +ALTER TABLE t2 DROP c; +UNLOCK TABLES; +DROP VIEW v1; +DROP TABLE t1, t2; -- cgit v1.2.1 From 284c72eacf2074bf50c1930d5e8eb333367eba2f Mon Sep 17 00:00:00 2001 From: Sachin Date: Wed, 17 Jul 2019 15:56:29 +0530 Subject: MDEV-17614 INSERT on dup key update is replication unsafe Problem:- When mysql executes INSERT ON DUPLICATE KEY INSERT, the storage engine checks if the inserted row would generate a duplicate key error. If yes, it returns the existing row to mysql, mysql updates it and sends it back to the storage engine.When the table has more than one unique or primary key, this statement is sensitive to the order in which the storage engines checks the keys. Depending on this order, the storage engine may determine different rows to mysql, and hence mysql can update different rows.The order that the storage engine checks keys is not deterministic. For example, InnoDB checks keys in an order that depends on the order in which indexes were added to the table. The first added index is checked first. So if master and slave have added indexes in different orders, then slave may go out of sync. Solution:- Make INSERT...ON DUPLICATE KEY UPDATE unsafe while using stmt or mixed format When there is more then one unique key. Although there is two exception. 1. Auto Increment key is not counted because Innodb will get gap lock for failed Insert and concurrent insert will get a next increment value. But if user supplies auto inc value it can be unsafe. 2. Count only unique keys for which insertion is performed. So this patch also addresses the bug id #72921 --- .../suite/rpl/r/rpl_known_bugs_detection.result | 20 ---- mysql-test/suite/rpl/r/rpl_mdev_17614.result | 98 +++++++++++++++++ .../suite/rpl/r/rpl_unsafe_statements.result | 5 + .../suite/rpl/t/rpl_known_bugs_detection.test | 39 ------- mysql-test/suite/rpl/t/rpl_mdev_17614.test | 121 +++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_unsafe_statements.test | 2 +- 6 files changed, 225 insertions(+), 60 deletions(-) create mode 100644 mysql-test/suite/rpl/r/rpl_mdev_17614.result create mode 100644 mysql-test/suite/rpl/t/rpl_mdev_17614.test (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result index ea738b710fd..adef091ea3e 100644 --- a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result +++ b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result @@ -1,26 +1,6 @@ call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); include/master-slave.inc [connection master] -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, -UNIQUE(b)); -INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; -SELECT * FROM t1; -a b -1 10 -2 2 -call mtr.add_suppression("Slave SQL.*suffer.*http:..bugs.mysql.com.bug.php.id=24432"); -include/wait_for_slave_sql_error.inc [errno=1105] -Last_SQL_Error = 'Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10'' -SELECT * FROM t1; -a b -stop slave; -include/wait_for_slave_to_stop.inc -reset slave; -reset master; -drop table t1; -start slave; -include/wait_for_slave_to_start.inc CREATE TABLE t1 ( id bigint(20) unsigned NOT NULL auto_increment, field_1 int(10) unsigned NOT NULL, diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17614.result b/mysql-test/suite/rpl/r/rpl_mdev_17614.result new file mode 100644 index 00000000000..28de23e28c9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mdev_17614.result @@ -0,0 +1,98 @@ +include/master-slave.inc +[connection master] +call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT, +UNIQUE(b), c int) engine=innodb; +INSERT INTO t1 VALUES (1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +COMMIT; +SELECT * FROM t1; +a b c +1 1 2 +2 2 3 +include/wait_for_slave_sql_error.inc [errno=1062] +Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)'' +#Different value from server +SELECT * FROM t1; +a b c +1 1 1 +2 2 3 +stop slave; +include/wait_for_slave_to_stop.inc +reset slave; +reset master; +drop table t1; +start slave; +include/wait_for_slave_to_start.inc +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +INSERT INTO t1 VALUES (default, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +COMMIT; +SELECT * FROM t1; +a b c +1 1 2 +3 2 3 +#same data as master +SELECT * FROM t1; +a b c +1 1 2 +3 2 3 +drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +COMMIT; +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +#same data as master +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +INSERT INTO t1 VALUES (1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +COMMIT; +SELECT * FROM t1; +a b c +1 1 2 +2 2 3 +include/wait_for_slave_sql_error.inc [errno=1062] +Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)'' +#Different value from server +SELECT * FROM t1; +a b c +1 1 1 +2 2 3 +stop slave; +include/wait_for_slave_to_stop.inc +reset slave; +reset master; +drop table t1; +start slave; +include/wait_for_slave_to_start.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_unsafe_statements.result b/mysql-test/suite/rpl/r/rpl_unsafe_statements.result index 2efb3eba2b1..55ff09f6fad 100644 --- a/mysql-test/suite/rpl/r/rpl_unsafe_statements.result +++ b/mysql-test/suite/rpl/r/rpl_unsafe_statements.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); CREATE TABLE t1(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TRIGGER trig1 AFTER INSERT ON t1 @@ -43,9 +44,13 @@ include/diff_tables.inc [master:t1, slave:t1] DROP TABLE t1; CREATE TABLE t1(i INT, j INT, UNIQUE KEY(i), UNIQUE KEY(j)) ENGINE=INNODB; INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe START TRANSACTION; LOCK TABLES t1 WRITE; INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe UNLOCK TABLES; COMMIT; include/diff_tables.inc [master:t1, slave:t1] diff --git a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test index ab263ece407..5ea056d5f14 100644 --- a/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test +++ b/mysql-test/suite/rpl/t/rpl_known_bugs_detection.test @@ -14,45 +14,6 @@ source include/have_binlog_checksum_off.inc; source include/master-slave.inc; -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); - -# -# This is to test that slave properly detects if -# master may suffer from: -# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values" -# (i.e. on master, INSERT ON DUPLICATE KEY UPDATE is used and manipulates -# an auto_increment column, and is binlogged statement-based). -# - -# testcase with INSERT VALUES -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, -UNIQUE(b)); -sync_slave_with_master; -connection master; -INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; -SELECT * FROM t1; -connection slave; - -# show the error message -#1105 = ER_UNKNOWN_ERROR ---let $slave_sql_errno= 1105 ---let $show_slave_sql_error= 1 -call mtr.add_suppression("Slave SQL.*suffer.*http:..bugs.mysql.com.bug.php.id=24432"); ---source include/wait_for_slave_sql_error.inc -# show that it was not replicated -SELECT * FROM t1; - -# restart replication for the next testcase -stop slave; ---source include/wait_for_slave_to_stop.inc -reset slave; -connection master; -reset master; -drop table t1; -connection slave; -start slave; ---source include/wait_for_slave_to_start.inc - # testcase with INSERT SELECT connection master; CREATE TABLE t1 ( diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17614.test b/mysql-test/suite/rpl/t/rpl_mdev_17614.test new file mode 100644 index 00000000000..9b86c8c15b5 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mdev_17614.test @@ -0,0 +1,121 @@ +source include/have_debug.inc; +source include/have_innodb.inc; +-- source include/have_binlog_format_statement.inc +source include/master-slave.inc; +# MDEV-17614 +# INSERT on dup key update is replication unsafe +# There can be three case +# 1. 2 unique key, Replication is unsafe. +# 2. 2 unique key , with one auto increment key, Safe to replicate because Innodb will acquire gap lock +# 3. n no of unique keys (n>1) but insert is only in 1 unique key +# 4. 2 unique key , with one auto increment key(but user gives auto inc value), unsafe to replicate + +# Case 1 +call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT, +UNIQUE(b), c int) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--connection slave +# show the error message +--let $slave_sql_errno= 1062 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc +--echo #Different value from server +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +--source include/wait_for_slave_to_stop.inc +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; +--source include/wait_for_slave_to_start.inc +# Case 2 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (default, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; + +connection master; +drop table t1; +--sync_slave_with_master + +# Case 3 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; +connection master; +drop table t1; +--sync_slave_with_master + +# Case 4 +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, +UNIQUE(b), c int) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--connection slave +# show the error message +--let $slave_sql_errno= 1062 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc +--echo #Different value from server +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +--source include/wait_for_slave_to_stop.inc +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; +--source include/wait_for_slave_to_start.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_unsafe_statements.test b/mysql-test/suite/rpl/t/rpl_unsafe_statements.test index cbb4b54a220..aa0bd076398 100644 --- a/mysql-test/suite/rpl/t/rpl_unsafe_statements.test +++ b/mysql-test/suite/rpl/t/rpl_unsafe_statements.test @@ -24,7 +24,7 @@ --source include/have_innodb.inc --source include/have_binlog_format_mixed.inc --source include/master-slave.inc - +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); # Case-1: BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS # Statement is unsafe because it invokes a trigger or a # stored function that inserts into an AUTO_INCREMENT column. -- cgit v1.2.1 From cdbac54df0bd857a053decd66b6067abf15a6801 Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Wed, 26 Dec 2018 13:24:45 +0300 Subject: MDEV-17613 MIN/MAX Optimization (Select tables optimized away) does not work Prune to now-partition when there is no FOR SYSTEM_TIME clause. --- mysql-test/suite/versioning/r/partition.result | 11 +++++++++++ .../suite/versioning/r/partition_rotation.result | 2 +- mysql-test/suite/versioning/t/partition.test | 21 +++++++++++++++++++++ 3 files changed, 33 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index c0a163ed027..588e6432c4a 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -546,6 +546,17 @@ t1 CREATE TABLE `t1` ( # create or replace table t1 (f int) with system versioning partition by hash(f); insert delayed into t1 values (1); +# +# MDEV-17613 MIN/MAX Optimization (Select tables optimized away) does not work +# +create or replace table t1 (pk int primary key) with system versioning +partition by system_time ( +partition p1 history, +partition pn current); +insert into t1 values (1), (2); +explain select max(pk) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away # Test cleanup drop database test; create database test; diff --git a/mysql-test/suite/versioning/r/partition_rotation.result b/mysql-test/suite/versioning/r/partition_rotation.result index 7e25f122238..69b30a56bd6 100644 --- a/mysql-test/suite/versioning/r/partition_rotation.result +++ b/mysql-test/suite/versioning/r/partition_rotation.result @@ -44,7 +44,7 @@ i 6 explain partitions select * from t1; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t1 pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 2 explain partitions select * from t1 for system_time as of '2001-02-04 10:20:30'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1_p1sp0,p1_p1sp1,p0_p0sp0,p0_p0sp1,p2_p2sp0,p2_p2sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL # Using where diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index cd16ab7c466..29e4c413f77 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -497,6 +497,27 @@ create or replace table t1 (f int) with system versioning partition by hash(f); --error 0,ER_DELAYED_NOT_SUPPORTED insert delayed into t1 values (1); +--echo # +--echo # MDEV-17613 MIN/MAX Optimization (Select tables optimized away) does not work +--echo # +--disable_query_log +set @saved_storage_engine= @@default_storage_engine; +if ($MTR_COMBINATION_HEAP) +{ + # This case does not work with HEAP + set default_storage_engine= myisam; +} +--enable_query_log +create or replace table t1 (pk int primary key) with system versioning +partition by system_time ( + partition p1 history, + partition pn current); +insert into t1 values (1), (2); +explain select max(pk) from t1; +--disable_query_log +set default_storage_engine= @saved_storage_engine; +--enable_query_log + --echo # Test cleanup drop database test; create database test; -- cgit v1.2.1 From 98758b52b3a3b0ede3cb8f93bcada5e5af51254b Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Tue, 16 Jul 2019 19:40:38 +0300 Subject: MDEV-20068 History partition rotation is not done under LOCK TABLES Wrong value F_WRLCK for thr_lock_type. --- mysql-test/suite/versioning/r/partition.result | 12 ++++++++++++ mysql-test/suite/versioning/t/partition.test | 11 +++++++++++ 2 files changed, 23 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 588e6432c4a..da851791640 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -557,6 +557,18 @@ insert into t1 values (1), (2); explain select max(pk) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +# +# MDEV-20068 History partition rotation is not done under LOCK TABLES +# +create or replace table t1 (x int) with system versioning partition by system_time limit 1 +(partition p1 history, partition pn current); +lock tables t1 write; +insert into t1 values (0), (1), (2), (3); +delete from t1 where x < 3; +delete from t1; +Warnings: +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +unlock tables; # Test cleanup drop database test; create database test; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index 29e4c413f77..f602c49d7c1 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -518,6 +518,17 @@ explain select max(pk) from t1; set default_storage_engine= @saved_storage_engine; --enable_query_log +--echo # +--echo # MDEV-20068 History partition rotation is not done under LOCK TABLES +--echo # +create or replace table t1 (x int) with system versioning partition by system_time limit 1 +(partition p1 history, partition pn current); +lock tables t1 write; +insert into t1 values (0), (1), (2), (3); +delete from t1 where x < 3; +delete from t1; +unlock tables; + --echo # Test cleanup drop database test; create database test; -- cgit v1.2.1 From 5851e668d7a0d51034c7d74779c39ef0f7b8d002 Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Mon, 27 May 2019 15:03:15 +0300 Subject: MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT For MODE_SIMULTANEOUS_ASSIGNMENT it is required to return back field offsets from record[1] to record[0]. 'continue' in warning branch did skip of rfield->move_field_offset() call. --- mysql-test/suite/versioning/r/alter.result | 13 +++++++++++++ mysql-test/suite/versioning/t/alter.test | 11 +++++++++++ 2 files changed, 24 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 528c6ca7c2f..d272d53f407 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -603,5 +603,18 @@ select * from t1; a b 1 0 affected rows: 1 +# +# MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT +# +create or replace table t1 (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) engine=myisam with system versioning; +insert into t1 values (null, null, null); +insert into t1 values (null, null, null); +set sql_mode= 'simultaneous_assignment'; +update t1 set e= 1; +Warnings: +Warning 1906 The value specified for generated column 'e' in table 't1' has been ignored +Warning 1906 The value specified for generated column 'e' in table 't1' has been ignored +alter table t1 force; +set sql_mode= default; drop database test; create database test; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index 4af937b96e7..f11eee59641 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -503,5 +503,16 @@ alter table t1 modify a int with system versioning; select * from t1; --disable_info +--echo # +--echo # MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT +--echo # +create or replace table t1 (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) engine=myisam with system versioning; +insert into t1 values (null, null, null); +insert into t1 values (null, null, null); +set sql_mode= 'simultaneous_assignment'; +update t1 set e= 1; +alter table t1 force; +set sql_mode= default; + drop database test; create database test; -- cgit v1.2.1 From 638e78853f8d7c0e2c1cf580f3847c5789f165e1 Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Wed, 20 Mar 2019 20:45:54 +0300 Subject: MDEV-18862 Unfortunate error message upon attempt to drop system versioning Special case for DROP PERIOD when system fields are implicit. --- mysql-test/suite/versioning/r/alter.result | 13 +++++++++++++ mysql-test/suite/versioning/t/alter.test | 14 ++++++++++++++ 2 files changed, 27 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index d272d53f407..0c79e6d4a39 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -616,5 +616,18 @@ Warning 1906 The value specified for generated column 'e' in table 't1' has been Warning 1906 The value specified for generated column 'e' in table 't1' has been ignored alter table t1 force; set sql_mode= default; +# +# MDEV-18862 Unfortunate error message upon attempt to drop system versioning +# +set system_versioning_alter_history= keep; +create or replace table t1 (x int) with system versioning; +alter table t1 drop column `row_start`, drop column `row_end`, drop period for system_time, drop system versioning; +ERROR HY000: No 'PERIOD FOR SYSTEM_TIME' in system-versioned `t1` +alter table t1 drop period for system_time; +ERROR HY000: No 'PERIOD FOR SYSTEM_TIME' in system-versioned `t1` +alter table t1 drop column `row_start`, drop column `row_end`, drop system versioning; +ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists +alter table t1 drop column `row_end`; +ERROR 42000: Can't DROP COLUMN `row_end`; check that it exists drop database test; create database test; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index f11eee59641..ea699104eac 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -514,5 +514,19 @@ update t1 set e= 1; alter table t1 force; set sql_mode= default; +--echo # +--echo # MDEV-18862 Unfortunate error message upon attempt to drop system versioning +--echo # +set system_versioning_alter_history= keep; +create or replace table t1 (x int) with system versioning; +--error ER_VERS_NO_PERIOD +alter table t1 drop column `row_start`, drop column `row_end`, drop period for system_time, drop system versioning; +--error ER_VERS_NO_PERIOD +alter table t1 drop period for system_time; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t1 drop column `row_start`, drop column `row_end`, drop system versioning; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t1 drop column `row_end`; + drop database test; create database test; -- cgit v1.2.1 From 0b74c8832d45340a455f27c3b4aa20db5d8313db Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Thu, 11 Apr 2019 10:04:34 +0300 Subject: MDEV-19127 Assertion `row_start_field' failed in vers_prepare_keys upon ALTER TABLE Prevent conflicting clauses at parser level. Clear HA_VERSIONED_TABLE flag for DROP SYSTEM VERSIONING (for the sake of strictness). --- mysql-test/suite/versioning/r/alter.result | 9 +++++++++ mysql-test/suite/versioning/t/alter.test | 10 ++++++++++ 2 files changed, 19 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 0c79e6d4a39..2afd3921154 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -629,5 +629,14 @@ alter table t1 drop column `row_start`, drop column `row_end`, drop system versi ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists alter table t1 drop column `row_end`; ERROR 42000: Can't DROP COLUMN `row_end`; check that it exists +# +# MDEV-19127 Assertion `row_start_field' failed in vers_prepare_keys upon ALTER TABLE +# +set system_versioning_alter_history=keep; +create or replace table t1 (f1 int) with system versioning; +alter table t1 add f2 int with system versioning, drop system versioning; +create or replace table t1 (f1 int) with system versioning; +alter table t1 drop system versioning, add f2 int with system versioning; +ERROR HY000: Table `t1` is not system-versioned drop database test; create database test; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index ea699104eac..c04c2020460 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -528,5 +528,15 @@ alter table t1 drop column `row_start`, drop column `row_end`, drop system versi --error ER_CANT_DROP_FIELD_OR_KEY alter table t1 drop column `row_end`; +--echo # +--echo # MDEV-19127 Assertion `row_start_field' failed in vers_prepare_keys upon ALTER TABLE +--echo # +set system_versioning_alter_history=keep; +create or replace table t1 (f1 int) with system versioning; +alter table t1 add f2 int with system versioning, drop system versioning; +create or replace table t1 (f1 int) with system versioning; +--error ER_VERS_NOT_VERSIONED +alter table t1 drop system versioning, add f2 int with system versioning; + drop database test; create database test; -- cgit v1.2.1 From 22914ec793b850438c77acf070f8441cf307ebf6 Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Tue, 5 Feb 2019 00:57:31 +0300 Subject: MDEV-18154 Deadlock and assertion upon no-op ALTER under LOCK TABLES 1. Fix DBUG_ASSERT(!table->pos_in_locked_tables) in tc_release_table(); 2. Fix access of prematurely freed MDL_ticket: don't close ticket if table was not closed; 3. Fix deadlock after erroneous ALTER. mysql_alter_table() leaves dirty table->m_needs_reopen in case of error exit which then incorrectly treated by mysql_lock_tables(). --- mysql-test/main/alter_table.result | 18 ++++++++++++++++++ mysql-test/main/alter_table.test | 18 ++++++++++++++++++ 2 files changed, 36 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index 7a88f9165da..c5d974f4469 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -2539,3 +2539,21 @@ test.t1 analyze status OK set @@use_stat_tables= @save_use_stat_tables; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1; +# +# MDEV-18154 Deadlock and assertion upon no-op ALTER under LOCK TABLES +# +create or replace table t1 (pk int, i int, primary key (pk)) engine myisam; +create or replace view v1 as select * from t1; +lock table v1 read, t1 write; +alter table t1 change f1 f2 int; +ERROR 42S22: Unknown column 'f1' in 't1' +set max_statement_time= 1; +alter table t1 add column if not exists i int after pk; +Warnings: +Note 1060 Duplicate column name 'i' +set max_statement_time= 0; +drop table t1; +drop view v1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index 0fab5575f13..5c29dfae6b3 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -2057,3 +2057,21 @@ analyze table t1; set @@use_stat_tables= @save_use_stat_tables; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1; + +--echo # +--echo # MDEV-18154 Deadlock and assertion upon no-op ALTER under LOCK TABLES +--echo # +create or replace table t1 (pk int, i int, primary key (pk)) engine myisam; +create or replace view v1 as select * from t1; +lock table v1 read, t1 write; +--error ER_BAD_FIELD_ERROR +alter table t1 change f1 f2 int; +set max_statement_time= 1; +alter table t1 add column if not exists i int after pk; +set max_statement_time= 0; +drop table t1; +drop view v1; + +--echo # +--echo # End of 10.3 tests +--echo # -- cgit v1.2.1 From 7a9e1fcd455bfa8e3d71fabb6b76769433567508 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Mon, 12 Aug 2019 14:45:28 +0300 Subject: MDEV-17614: Re-record a result --- mysql-test/suite/binlog/r/binlog_unsafe.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index 48d9db44b05..e8de90d3422 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -2706,6 +2706,8 @@ Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... REPLACE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. INSERT INTO insert_2_keys VALUES (1, 2) ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe DROP TABLE filler_table; DROP TABLE insert_table; DROP TABLE update_table; -- cgit v1.2.1 From fe8181aca13cf7ba1835fa6c89f297a1d0e79856 Mon Sep 17 00:00:00 2001 From: Monty Date: Mon, 12 Aug 2019 15:40:57 +0300 Subject: Fixed issues found by valgrind - mysqltest didn't free read_command_buf - wait_for_slave_param did write different things to the log if valgrind was used. - Table open cache should not write the initial variable value as it can depend on the configuration or if valgrind is used - A variable in GetResult was used uninitalized --- mysql-test/include/wait_for_slave_param.inc | 2 +- .../suite/rpl/r/rpl_gtid_delete_domain.result | 4 ++-- .../suite/sys_vars/r/table_open_cache_basic.result | 20 +++---------------- .../suite/sys_vars/t/table_open_cache_basic.test | 23 +++------------------- 4 files changed, 9 insertions(+), 40 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/include/wait_for_slave_param.inc b/mysql-test/include/wait_for_slave_param.inc index 25020d46ed9..b06dee3c640 100644 --- a/mysql-test/include/wait_for_slave_param.inc +++ b/mysql-test/include/wait_for_slave_param.inc @@ -69,7 +69,7 @@ if (!$_slave_param_comparison) if ($rpl_debug) { - --echo Waiting until '$slave_param' $_slave_param_comparison '$slave_param_value' [timeout='$_slave_timeout', \$slave_error_param='$slave_error_param'] + --echo Waiting until '$slave_param' $_slave_param_comparison '$slave_param_value' [\$slave_error_param='$slave_error_param'] } --let $_slave_check_configured= query_get_value("SHOW SLAVE STATUS", Slave_IO_Running, 1) diff --git a/mysql-test/suite/rpl/r/rpl_gtid_delete_domain.result b/mysql-test/suite/rpl/r/rpl_gtid_delete_domain.result index 75a22b78a32..74648501fbe 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_delete_domain.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_delete_domain.result @@ -41,7 +41,7 @@ START SLAVE; .. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' ...==== BEGIN include/wait_for_slave_param.inc [Slave_IO_Running] ==== ... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' -Waiting until 'Slave_IO_Running' = 'Yes' [timeout='300', $slave_error_param='Last_IO_Errno'] +Waiting until 'Slave_IO_Running' = 'Yes' [$slave_error_param='Last_IO_Errno'] [connection slave] ...==== END include/wait_for_slave_param.inc [Slave_IO_Running] ==== ... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' @@ -52,7 +52,7 @@ Waiting until 'Slave_IO_Running' = 'Yes' [timeout='300', $slave_error_param='Las .. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' ...==== BEGIN include/wait_for_slave_param.inc [Slave_SQL_Running] ==== ... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' -Waiting until 'Slave_SQL_Running' = 'Yes' [timeout='300', $slave_error_param='1'] +Waiting until 'Slave_SQL_Running' = 'Yes' [$slave_error_param='1'] [connection slave] ...==== END include/wait_for_slave_param.inc [Slave_SQL_Running] ==== ... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' diff --git a/mysql-test/suite/sys_vars/r/table_open_cache_basic.result b/mysql-test/suite/sys_vars/r/table_open_cache_basic.result index bc373003e1d..2d8e9005f81 100644 --- a/mysql-test/suite/sys_vars/r/table_open_cache_basic.result +++ b/mysql-test/suite/sys_vars/r/table_open_cache_basic.result @@ -1,18 +1,9 @@ SET @start_value = @@global.table_open_cache ; -SELECT @start_value; -@start_value -421 -'#--------------------FN_DYNVARS_001_01------------------------#' -SET @@global.table_open_cache = 99; -SET @@global.table_open_cache = DeFAULT; -SELECT @@global.table_open_cache; -@@global.table_open_cache -2000 '#---------------------FN_DYNVARS_001_02-------------------------#' SET @@global.table_open_cache = Default; -SELECT @@global.table_open_cache = 400; -@@global.table_open_cache = 400 -0 +SELECT @@global.table_open_cache > 0; +@@global.table_open_cache > 0 +1 '#--------------------FN_DYNVARS_001_03------------------------#' SET @@global.table_open_cache = 8; Warnings: @@ -101,13 +92,8 @@ SELECT @@table_open_cache = @@global.table_open_cache ; '#---------------------FN_DYNVARS_001_11----------------------#' SET table_open_cache = 8; ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable and should be set with SET GLOBAL -SET global.table_open_cache = 10; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table_open_cache = 10' at line 1 SELECT global.table_open_cache ; ERROR 42S02: Unknown table 'global' in field list SELECT table_open_cache = @@session.table_open_cache ; ERROR 42S22: Unknown column 'table_open_cache' in 'field list' SET @@global.table_open_cache = @start_value; -SELECT @@global.table_open_cache ; -@@global.table_open_cache -421 diff --git a/mysql-test/suite/sys_vars/t/table_open_cache_basic.test b/mysql-test/suite/sys_vars/t/table_open_cache_basic.test index 7d2549cd87f..fc838d1a28b 100644 --- a/mysql-test/suite/sys_vars/t/table_open_cache_basic.test +++ b/mysql-test/suite/sys_vars/t/table_open_cache_basic.test @@ -4,8 +4,8 @@ # Scope: GLOBAL # # Access Type: Dynamic # # Data Type: numeric # -# Default Value: 400 # -# Range: 64-524288 # +# Default Value: 400 # +# Range: 64-524288 # # # # # # Creation Date: 2008-02-13 # @@ -35,18 +35,6 @@ ########################################################################## SET @start_value = @@global.table_open_cache ; -SELECT @start_value; - - ---echo '#--------------------FN_DYNVARS_001_01------------------------#' -######################################################################## -# Display the DEFAULT value of table_open_cache # -######################################################################## - -SET @@global.table_open_cache = 99; -SET @@global.table_open_cache = DeFAULT; -SELECT @@global.table_open_cache; - --echo '#---------------------FN_DYNVARS_001_02-------------------------#' ############################################### @@ -54,7 +42,7 @@ SELECT @@global.table_open_cache; ############################################### SET @@global.table_open_cache = Default; -SELECT @@global.table_open_cache = 400; +SELECT @@global.table_open_cache > 0; --echo '#--------------------FN_DYNVARS_001_03------------------------#' ######################################################################## @@ -152,8 +140,6 @@ SELECT @@table_open_cache = @@global.table_open_cache ; --Error ER_GLOBAL_VARIABLE SET table_open_cache = 8; ---Error ER_PARSE_ERROR -SET global.table_open_cache = 10; --Error ER_UNKNOWN_TABLE SELECT global.table_open_cache ; --Error ER_BAD_FIELD_ERROR @@ -165,10 +151,7 @@ SELECT table_open_cache = @@session.table_open_cache ; ############################## SET @@global.table_open_cache = @start_value; -SELECT @@global.table_open_cache ; - ################################################################## # END OF table_open_cache TESTS # ################################################################## - -- cgit v1.2.1 From 609ea2f37b8169a7c282fe2d607c2412467ccbbb Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Mon, 12 Aug 2019 18:50:54 +0300 Subject: MDEV-17614: After-merge fix MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit MDEV-17614 flags INSERT…ON DUPLICATE KEY UPDATE unsafe for statement-based replication when there are multiple unique indexes. This correctly fixes something whose attempted fix in MySQL 5.7 in mysql/mysql-server@c93b0d9a972cb6f98fd445f2b69d924350f9128a caused lock conflicts. That change was reverted in MySQL 5.7.26 in mysql/mysql-server@066b6fdd433aa6673622341f1a2f0a3a20018043 (with a substantial amount of other changes). In MDEV-17073 we already disabled the unfortunate MySQL change when statement-based replication was not being used. Now, thanks to MDEV-17614, we can actually remove the change altogether. This reverts commit 8a346f31b913daa011085afec2b2d38450c73e00 (MDEV-17073) and mysql/mysql-server@c93b0d9a972cb6f98fd445f2b69d924350f9128a while keeping the test cases. --- .../innodb/r/auto_increment_dup,skip-log-bin.rdiff | 51 ---------------------- .../suite/innodb/r/auto_increment_dup.result | 27 +++++++----- mysql-test/suite/innodb/t/auto_increment_dup.test | 8 ---- mysql-test/suite/rpl/disabled.def | 1 - mysql-test/suite/rpl/r/rpl_mdev_17614.result | 31 +++++++++++++ 5 files changed, 46 insertions(+), 72 deletions(-) delete mode 100644 mysql-test/suite/innodb/r/auto_increment_dup,skip-log-bin.rdiff (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/auto_increment_dup,skip-log-bin.rdiff b/mysql-test/suite/innodb/r/auto_increment_dup,skip-log-bin.rdiff deleted file mode 100644 index 7b4ec54eed8..00000000000 --- a/mysql-test/suite/innodb/r/auto_increment_dup,skip-log-bin.rdiff +++ /dev/null @@ -1,51 +0,0 @@ ---- auto_increment_dup.result -+++ auto_increment_dup,skip-log-bin.reject -@@ -89,13 +89,14 @@ - SET DEBUG_SYNC='execute_command_after_close_tables SIGNAL continue'; - affected rows: 0 - INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; --ERROR HY000: Lock wait timeout exceeded; try restarting transaction -+affected rows: 3 -+info: Records: 3 Duplicates: 0 Warnings: 0 - connection con1; - # - # 2 duplicates - # --affected rows: 3 --info: Records: 3 Duplicates: 0 Warnings: 0 -+affected rows: 4 -+info: Records: 3 Duplicates: 1 Warnings: 0 - connection default; - # - # 3 rows -@@ -103,19 +104,21 @@ - SELECT * FROM t1 order by k; - id k c - 1 1 NULL --2 2 NULL --3 3 NULL --affected rows: 3 -+4 2 1 -+2 3 NULL -+5 4 NULL -+6 5 NULL -+affected rows: 5 - INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; --affected rows: 4 --info: Records: 3 Duplicates: 1 Warnings: 0 -+affected rows: 6 -+info: Records: 3 Duplicates: 3 Warnings: 0 - SELECT * FROM t1 order by k; - id k c - 1 1 NULL --2 2 2 --3 3 NULL --7 4 NULL --8 5 NULL -+4 2 2 -+2 3 NULL -+5 4 2 -+6 5 2 - affected rows: 5 - disconnect con1; - disconnect con2; diff --git a/mysql-test/suite/innodb/r/auto_increment_dup.result b/mysql-test/suite/innodb/r/auto_increment_dup.result index 1467a459fc1..9926047b665 100644 --- a/mysql-test/suite/innodb/r/auto_increment_dup.result +++ b/mysql-test/suite/innodb/r/auto_increment_dup.result @@ -89,13 +89,14 @@ affected rows: 0 SET DEBUG_SYNC='execute_command_after_close_tables SIGNAL continue'; affected rows: 0 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 connection con1; # # 2 duplicates # -affected rows: 3 -info: Records: 3 Duplicates: 0 Warnings: 0 +affected rows: 4 +info: Records: 3 Duplicates: 1 Warnings: 0 connection default; # # 3 rows @@ -103,19 +104,21 @@ connection default; SELECT * FROM t1 order by k; id k c 1 1 NULL -2 2 NULL -3 3 NULL -affected rows: 3 +4 2 1 +2 3 NULL +5 4 NULL +6 5 NULL +affected rows: 5 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; -affected rows: 4 -info: Records: 3 Duplicates: 1 Warnings: 0 +affected rows: 6 +info: Records: 3 Duplicates: 3 Warnings: 0 SELECT * FROM t1 order by k; id k c 1 1 NULL -2 2 2 -3 3 NULL -7 4 NULL -8 5 NULL +4 2 2 +2 3 NULL +5 4 2 +6 5 2 affected rows: 5 disconnect con1; disconnect con2; diff --git a/mysql-test/suite/innodb/t/auto_increment_dup.test b/mysql-test/suite/innodb/t/auto_increment_dup.test index aa399e5966d..9e54a6a8a66 100644 --- a/mysql-test/suite/innodb/t/auto_increment_dup.test +++ b/mysql-test/suite/innodb/t/auto_increment_dup.test @@ -8,8 +8,6 @@ --source include/have_debug_sync.inc --source include/innodb_binlog.inc -let $stmt= `SELECT @@GLOBAL.log_bin`; - set global transaction isolation level repeatable read; CREATE TABLE t1( @@ -84,13 +82,7 @@ SET DEBUG_SYNC='ha_write_row_end SIGNAL write_row_done WAIT_FOR continue'; --reap SET DEBUG_SYNC='execute_command_after_close_tables SIGNAL continue'; -if ($stmt) { ---error ER_LOCK_WAIT_TIMEOUT -INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; -} -if (!$stmt) { INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; -} --connection con1 --echo # diff --git a/mysql-test/suite/rpl/disabled.def b/mysql-test/suite/rpl/disabled.def index cfebf0f6ff1..59ca2958e80 100644 --- a/mysql-test/suite/rpl/disabled.def +++ b/mysql-test/suite/rpl/disabled.def @@ -15,4 +15,3 @@ rpl_get_master_version_and_clock : Bug#11766137 Jan 05 2011 joro Valgrind warnin rpl_partition_archive : MDEV-5077 2013-09-27 svoj Cannot exchange partition with archive table rpl_row_binlog_max_cache_size : MDEV-11092 rpl_row_index_choice : MDEV-11666 -rpl_mdev_17614 : MDEV-17614/MDEV-17073 Unexpected lock conflict diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17614.result b/mysql-test/suite/rpl/r/rpl_mdev_17614.result index 28de23e28c9..39057334926 100644 --- a/mysql-test/suite/rpl/r/rpl_mdev_17614.result +++ b/mysql-test/suite/rpl/r/rpl_mdev_17614.result @@ -3,19 +3,24 @@ include/master-slave.inc call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT, UNIQUE(b), c int) engine=innodb; +connection slave; +connection master; INSERT INTO t1 VALUES (1, 1, 1); BEGIN; INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +connection master1; INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +connection master; COMMIT; SELECT * FROM t1; a b c 1 1 2 2 2 3 +connection slave; include/wait_for_slave_sql_error.inc [errno=1062] Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)'' #Different value from server @@ -26,61 +31,85 @@ a b c stop slave; include/wait_for_slave_to_stop.inc reset slave; +connection master; reset master; drop table t1; +connection slave; start slave; include/wait_for_slave_to_start.inc +connection master; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, UNIQUE(b), c int) engine=innodb; +connection slave; +connection master; INSERT INTO t1 VALUES (default, 1, 1); BEGIN; INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +connection master1; INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +connection master; COMMIT; SELECT * FROM t1; a b c 1 1 2 3 2 3 +connection slave; #same data as master SELECT * FROM t1; a b c 1 1 2 3 2 3 +connection master; drop table t1; +connection slave; +connection master; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, UNIQUE(b), c int, d int ) engine=innodb; +connection slave; +connection master; INSERT INTO t1 VALUES (1, 1, 1, 1); BEGIN; INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +connection master1; INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +connection master; COMMIT; SELECT * FROM t1; a b c d 1 1 1 1 2 NULL 2 2 3 NULL 2 3 +connection slave; #same data as master SELECT * FROM t1; a b c d 1 1 1 1 2 NULL 2 2 3 NULL 2 3 +connection master; drop table t1; +connection slave; +connection master; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, UNIQUE(b), c int) engine=innodb; +connection slave; +connection master; INSERT INTO t1 VALUES (1, 1, 1); BEGIN; INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +connection master1; INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +connection master; COMMIT; SELECT * FROM t1; a b c 1 1 2 2 2 3 +connection slave; include/wait_for_slave_sql_error.inc [errno=1062] Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)'' #Different value from server @@ -91,8 +120,10 @@ a b c stop slave; include/wait_for_slave_to_stop.inc reset slave; +connection master; reset master; drop table t1; +connection slave; start slave; include/wait_for_slave_to_start.inc include/rpl_end.inc -- cgit v1.2.1 From 3cee665a0444bd2d0bd881e0e2196d680eb58e64 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Mon, 12 Aug 2019 13:01:33 +0300 Subject: MDEV-17847 Galera test failure on MW-328[A|B|C] Test changes only. --- mysql-test/suite/galera/disabled.def | 3 --- mysql-test/suite/galera/r/MW-328A.result | 25 +++++++++++++---- mysql-test/suite/galera/r/MW-328B.result | 2 +- mysql-test/suite/galera/r/MW-328C.result | 2 +- mysql-test/suite/galera/t/MW-328-footer.inc | 2 +- mysql-test/suite/galera/t/MW-328A.test | 42 +++++++++++++++++++++-------- mysql-test/suite/galera/t/MW-328B.test | 2 -- mysql-test/suite/galera/t/MW-328C.test | 3 +-- mysql-test/suite/galera/t/MW-328D.test | 1 - mysql-test/suite/galera/t/MW-328E.test | 1 - 10 files changed, 55 insertions(+), 28 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index fcb4985ebc9..90b13bd7aa4 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -11,9 +11,6 @@ ############################################################################## MW-286 : MDEV-19992 Galera test failure on MW-286 -MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C] -MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C] -MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C] MW-329 : MDEV-19962 Galera test failure on MW-329 MW-388: MDEV-19803 Long semaphore wait error on galera.MW-388 galera_account_management : MariaDB 10.0 does not support ALTER USER diff --git a/mysql-test/suite/galera/r/MW-328A.result b/mysql-test/suite/galera/r/MW-328A.result index db0301b6bf2..535f04bfbab 100644 --- a/mysql-test/suite/galera/r/MW-328A.result +++ b/mysql-test/suite/galera/r/MW-328A.result @@ -14,14 +14,29 @@ connection node_1X; CALL proc_update();; connection node_2; SET SESSION wsrep_retry_autocommit = 0; -have_successes -1 -have_deadlocks -1 connection node_1; connection node_1X; Got one of the listed errors connection node_1; DROP PROCEDURE proc_update; DROP TABLE t1, t2; -CALL mtr.add_suppression("conflict state 3 after post commit"); +CALL mtr.add_suppression("conflict state ABORTED after post commit"); +connection node_1; +CREATE TABLE t1 (i int primary key, j int) engine=innodb; +INSERT INTO t1 values (1,0); +BEGIN; +UPDATE t1 SET j=1 WHERE i=1; +connection node_2; +UPDATE t1 SET j=2 WHERE i=1; +connection node_1; +COMMIT; +ERROR 40001: Deadlock: wsrep aborted transaction +SELECT * FROM t1; +i j +1 2 +connection node_2; +SELECT * FROM t1; +i j +1 2 +connection node_1; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/MW-328B.result b/mysql-test/suite/galera/r/MW-328B.result index e898e315ca8..531c5de8029 100644 --- a/mysql-test/suite/galera/r/MW-328B.result +++ b/mysql-test/suite/galera/r/MW-328B.result @@ -20,4 +20,4 @@ Got one of the listed errors connection node_1; DROP PROCEDURE proc_update; DROP TABLE t1, t2; -CALL mtr.add_suppression("conflict state 3 after post commit"); +CALL mtr.add_suppression("conflict state ABORTED after post commit"); diff --git a/mysql-test/suite/galera/r/MW-328C.result b/mysql-test/suite/galera/r/MW-328C.result index d8e164e7b4a..f8d747c5df1 100644 --- a/mysql-test/suite/galera/r/MW-328C.result +++ b/mysql-test/suite/galera/r/MW-328C.result @@ -20,4 +20,4 @@ Got one of the listed errors connection node_1; DROP PROCEDURE proc_update; DROP TABLE t1, t2; -CALL mtr.add_suppression("conflict state 3 after post commit"); +CALL mtr.add_suppression("conflict state ABORTED after post commit"); diff --git a/mysql-test/suite/galera/t/MW-328-footer.inc b/mysql-test/suite/galera/t/MW-328-footer.inc index 5b736df220f..12a4bf12590 100644 --- a/mysql-test/suite/galera/t/MW-328-footer.inc +++ b/mysql-test/suite/galera/t/MW-328-footer.inc @@ -15,4 +15,4 @@ DROP PROCEDURE proc_update; DROP TABLE t1, t2; -CALL mtr.add_suppression("conflict state 3 after post commit"); +CALL mtr.add_suppression("conflict state ABORTED after post commit"); diff --git a/mysql-test/suite/galera/t/MW-328A.test b/mysql-test/suite/galera/t/MW-328A.test index 09aad1bcf60..da1040b3e5d 100644 --- a/mysql-test/suite/galera/t/MW-328A.test +++ b/mysql-test/suite/galera/t/MW-328A.test @@ -3,13 +3,16 @@ # # -# Attempt to insert into t2 and check if insert actually inserted rows if -# a success was reported. +# test phase 1 is not deterministic +# +# Here we attempt to insert into t2 and check if insert actually +# inserted rows if a success was reported. +# +# However, deadlocks may or may not happen in this test execution +# it all depends on timing. # ---source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc --connection node_2 @@ -25,7 +28,7 @@ while ($count) { TRUNCATE TABLE t2; - --error 0,1213 + --error 0,ER_LOCK_DEADLOCK INSERT IGNORE INTO t2 SELECT f2 FROM t1; if ($mysql_errno != 1213) { --inc $successes @@ -44,14 +47,31 @@ while ($count) --enable_query_log + +--source suite/galera/t/MW-328-footer.inc + # -# Check that the test produced both deadlocks and successes +# Test phase 2 is deterministic +# Here we generate a sure conflict in node 1 and verify that +# insert failed in both nodes # +--connection node_1 +CREATE TABLE t1 (i int primary key, j int) engine=innodb; +INSERT INTO t1 values (1,0); ---disable_query_log ---eval SELECT $successes > 0 AS have_successes ---eval SELECT $deadlocks > 0 AS have_deadlocks ---enable_query_log +BEGIN; +UPDATE t1 SET j=1 WHERE i=1; +--connection node_2 +UPDATE t1 SET j=2 WHERE i=1; ---source suite/galera/t/MW-328-footer.inc +--connection node_1 +--error ER_LOCK_DEADLOCK +COMMIT; + +SELECT * FROM t1; +--connection node_2 +SELECT * FROM t1; +--connection node_1 + +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MW-328B.test b/mysql-test/suite/galera/t/MW-328B.test index 000b0d8a9ab..11969dd0b47 100644 --- a/mysql-test/suite/galera/t/MW-328B.test +++ b/mysql-test/suite/galera/t/MW-328B.test @@ -7,9 +7,7 @@ # gets the deadlock error # ---source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc --connection node_2 diff --git a/mysql-test/suite/galera/t/MW-328C.test b/mysql-test/suite/galera/t/MW-328C.test index 72a8480923c..1594547d0de 100644 --- a/mysql-test/suite/galera/t/MW-328C.test +++ b/mysql-test/suite/galera/t/MW-328C.test @@ -7,9 +7,8 @@ # masks all deadlock errors # ---source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc +--source include/big_test.inc --source suite/galera/t/MW-328-header.inc --connection node_2 diff --git a/mysql-test/suite/galera/t/MW-328D.test b/mysql-test/suite/galera/t/MW-328D.test index d5cffdb8f47..e8a22f22a99 100644 --- a/mysql-test/suite/galera/t/MW-328D.test +++ b/mysql-test/suite/galera/t/MW-328D.test @@ -7,7 +7,6 @@ # --source include/galera_cluster.inc ---source include/have_innodb.inc CREATE TABLE t1 (i INT) ENGINE = InnoDB; INSERT INTO t1 (i) VALUES(1); diff --git a/mysql-test/suite/galera/t/MW-328E.test b/mysql-test/suite/galera/t/MW-328E.test index fd4b0bf9039..34b17be7b08 100644 --- a/mysql-test/suite/galera/t/MW-328E.test +++ b/mysql-test/suite/galera/t/MW-328E.test @@ -7,7 +7,6 @@ # --source include/galera_cluster.inc ---source include/have_innodb.inc create table t1 (i int primary key, j int) engine=innodb; create table t2 (i int primary key, j int) engine=innodb; -- cgit v1.2.1 From 5edc4ea4d968e81408ab9e343a0b3940797f98fc Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Tue, 13 Aug 2019 09:24:31 +0300 Subject: MDEV-20324: Galera threads are not registered to performance schema Galera threads were not registered to performance schema and used pthread_create when mysql_thread_create should have been used. Added test case to verify current galera performance schema instrumentation does work. --- .../galera/r/galera_performance_schema.result | 44 ++++++++++++++++ mysql-test/suite/galera/t/MW-328A.test | 1 + .../suite/galera/t/galera_performance_schema.test | 58 ++++++++++++++++++++++ 3 files changed, 103 insertions(+) create mode 100644 mysql-test/suite/galera/r/galera_performance_schema.result create mode 100644 mysql-test/suite/galera/t/galera_performance_schema.test (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/galera_performance_schema.result b/mysql-test/suite/galera/r/galera_performance_schema.result new file mode 100644 index 00000000000..5b4994556d6 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_performance_schema.result @@ -0,0 +1,44 @@ +use performance_schema; +SELECT name +FROM threads +WHERE name LIKE 'thread/sql/wsrep%' +ORDER BY name; +name thread/sql/wsrep_applier_thread +name thread/sql/wsrep_rollbacker_thread +use test; +create table t1 (a int not null primary key) engine=innodb; +insert into t1 values (1),(2); +use performance_schema; +select name from mutex_instances where name like 'wait/synch/mutex/sql/LOCK_wsrep%' order by name; +name wait/synch/mutex/sql/LOCK_wsrep_config_state +name wait/synch/mutex/sql/LOCK_wsrep_desync +name wait/synch/mutex/sql/LOCK_wsrep_ready +name wait/synch/mutex/sql/LOCK_wsrep_replaying +name wait/synch/mutex/sql/LOCK_wsrep_rollback +name wait/synch/mutex/sql/LOCK_wsrep_slave_threads +name wait/synch/mutex/sql/LOCK_wsrep_sst +name wait/synch/mutex/sql/LOCK_wsrep_sst_init +select name from cond_instances where name like 'wait/synch/cond/sql/COND_wsrep%' order by name; +name wait/synch/cond/sql/COND_wsrep_ready +name wait/synch/cond/sql/COND_wsrep_replaying +name wait/synch/cond/sql/COND_wsrep_rollback +name wait/synch/cond/sql/COND_wsrep_sst +name wait/synch/cond/sql/COND_wsrep_sst_init +connection node_2; +use test; +SET SESSION wsrep_on=OFF; +CREATE TABLE t2 (f1 INTEGER) engine=innodb; +connection node_1; +use test; +CREATE TABLE t2 (f1 INTEGER) engine=innodb; +connection node_2; +SET SESSION wsrep_on=ON; +SELECT COUNT(*) FROM t1; +COUNT(*) 2 +use performance_schema; +select count(*)>=1 from file_instances where file_name like '%GRA_%.log'; +count(*)>=1 1 +CALL mtr.add_suppression("Slave SQL: Error 'Table 't2' already exists' on query"); +use test; +drop table t1; +drop table t2; diff --git a/mysql-test/suite/galera/t/MW-328A.test b/mysql-test/suite/galera/t/MW-328A.test index da1040b3e5d..dd692a292b8 100644 --- a/mysql-test/suite/galera/t/MW-328A.test +++ b/mysql-test/suite/galera/t/MW-328A.test @@ -13,6 +13,7 @@ # --source include/galera_cluster.inc +--source include/big_test.inc --source suite/galera/t/MW-328-header.inc --connection node_2 diff --git a/mysql-test/suite/galera/t/galera_performance_schema.test b/mysql-test/suite/galera/t/galera_performance_schema.test new file mode 100644 index 00000000000..d54555ea301 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_performance_schema.test @@ -0,0 +1,58 @@ +# +# Test that wsrep mutexes, condition variables, files and +# threads are shown in performance schema +# +--source include/galera_cluster.inc +--source include/have_perfschema.inc + +use performance_schema; + +--vertical_results +--disable_ps_protocol +SELECT name +FROM threads +WHERE name LIKE 'thread/sql/wsrep%' +ORDER BY name; +--enable_ps_protocol + +use test; +create table t1 (a int not null primary key) engine=innodb; +insert into t1 values (1),(2); + +use performance_schema; +select name from mutex_instances where name like 'wait/synch/mutex/sql/LOCK_wsrep%' order by name; +select name from cond_instances where name like 'wait/synch/cond/sql/COND_wsrep%' order by name; +# Whenever a node fails to apply an event on a slave node, the database server creates a +# special binary log file of the event in the data directory. The naming convention the +# node uses for the filename is GRA_*.log. +# Thus, we need to produce a applier failure + +--connection node_2 +--exec rm -rf $MYSQLTEST_VARDIR/mysqld.2/data/GRA_*.log + +# Create applier failure + +use test; +SET SESSION wsrep_on=OFF; +CREATE TABLE t2 (f1 INTEGER) engine=innodb; + +--connection node_1 +use test; +CREATE TABLE t2 (f1 INTEGER) engine=innodb; + +--connection node_2 +SET SESSION wsrep_on=ON; +SELECT COUNT(*) FROM t1; + +use performance_schema; +# +# Below we can't just count number of files as if you run this test more +# than once, test will create more files +# +select count(*)>=1 from file_instances where file_name like '%GRA_%.log'; +CALL mtr.add_suppression("Slave SQL: Error 'Table 't2' already exists' on query"); + +use test; +drop table t1; +drop table t2; + -- cgit v1.2.1 From f25e9aa4ba9c7d3a4fbeaa280d2bb39abe023e80 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 13 Aug 2019 11:37:01 +0300 Subject: MDEV-20310: Make InnoDB crash tests Valgrind-friendly Use DEBUG_SYNC to hang the execution at the interesting point, and then kill and restart the server externally. This will work also with Valgrind. DBUG_SUICIDE() causes Valgrind to hang, and it could also cause uninteresting reports about memory leaks. While we are at it, let us clean up innodb.innodb_bulk_create_index_debug so that it will actually test the desired functionality also in future versions (with instant ADD COLUMN and DROP COLUMN) and avoid some unnecessary restarts. We are adding two DEBUG_SYNC points for ALTER TABLE, because there were none that would be executed right before ha_commit_trans(). --- .../include/innodb_bulk_create_index_debug.inc | 89 ++++---- mysql-test/suite/innodb/r/alter_copy.result | 8 +- .../innodb/r/innodb_bulk_create_index_debug.result | 229 +++++++++++++-------- mysql-test/suite/innodb/t/alter_copy.test | 15 +- 4 files changed, 195 insertions(+), 146 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc index 48de3a1962d..85466e5e4ae 100644 --- a/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc +++ b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc @@ -4,17 +4,8 @@ # Not supported in embedded -- source include/not_embedded.inc - -# This test case needs to crash the server. Needs a debug server. -- source include/have_debug.inc - -# Don't test this under valgrind, memory leaks will occur. --- source include/not_valgrind.inc - -# Avoid CrashReporter popup on Mac --- source include/not_crashrep.inc - --- source include/have_innodb.inc +-- source include/have_debug_sync.inc # Create Insert Procedure DELIMITER |; @@ -62,21 +53,11 @@ CALL populate_t1(); SELECT COUNT(*) FROM t1; +--enable_info CREATE INDEX idx_title ON t1(title); +--disable_info ---source include/restart_mysqld.inc - -CHECK TABLE t1; - -SELECT * FROM t1 WHERE title = 'a10'; - -SELECT * FROM t1 WHERE title = 'a5000'; - -SELECT * FROM t1 WHERE title = 'a10000'; - -SELECT * FROM t1 WHERE title = 'a10010'; - -DROP TABLE t1; +RENAME TABLE t1 TO t0; -- echo # Test Blob @@ -104,16 +85,32 @@ INSERT INTO t1 VALUES SELECT CHAR_LENGTH(b) FROM t1; -ALTER TABLE t1 DROP COLUMN c; +--enable_info +ALTER TABLE t1 DROP COLUMN c, FORCE; +--disable_info --source include/restart_mysqld.inc -CHECK TABLE t1; +CHECK TABLE t0,t1; SELECT CHAR_LENGTH(b) FROM t1; DROP TABLE t1; +RENAME TABLE t0 to t1; + +CHECK TABLE t1; + +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE title = 'a10010'; + +DROP TABLE t1; + # Test Crash Recovery if ($row_format != 'COMPRESSED') @@ -140,17 +137,16 @@ if ($row_format == 'COMPRESSED') CALL populate_t1(); -- enable_query_log -SET debug_dbug='+d,crash_commit_before'; - -# Write file to make mysql-test-run.pl start up the server again ---exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect - ---error 2013 +connect (hang,localhost,root); +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +send CREATE INDEX idx_title ON t1(title); ---enable_reconnect ---source include/wait_until_connected_again.inc ---disable_reconnect +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +let $shutdown_timeout=0; +--source include/restart_mysqld.inc +disconnect hang; SELECT COUNT(*) FROM t1; @@ -194,17 +190,16 @@ INSERT INTO t1 VALUES SELECT CHAR_LENGTH(b) FROM t1; -SET debug_dbug='+d,crash_commit_before'; - -# Write file to make mysql-test-run.pl start up the server again ---exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect - ---error 2013 -ALTER TABLE t1 DROP COLUMN c; +connect (hang,localhost,root); +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +send +ALTER TABLE t1 DROP COLUMN c, FORCE; ---enable_reconnect ---source include/wait_until_connected_again.inc ---disable_reconnect +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +--source include/restart_mysqld.inc +disconnect hang; +let $shutdown_timeout=60; CHECK TABLE t1; @@ -212,10 +207,4 @@ SELECT CHAR_LENGTH(b) FROM t1; DROP TABLE t1; -# Restore global variables -if ($row_format == 'COMPRESSED') -{ - SET GLOBAL innodb_file_per_table=default; -} - DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/r/alter_copy.result b/mysql-test/suite/innodb/r/alter_copy.result index 286c5152ded..659b8ae03b0 100644 --- a/mysql-test/suite/innodb/r/alter_copy.result +++ b/mysql-test/suite/innodb/r/alter_copy.result @@ -37,7 +37,9 @@ t1 CREATE TABLE `t1` ( FULLTEXT KEY `b_2` (`b`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t1 FORCE, ALGORITHM=COPY; -SET DEBUG_DBUG='+d,crash_commit_before'; +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_copy_trans_commit SIGNAL hung WAIT_FOR ever'; +# create 32 secondary indexes ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX(b,a,d,c), ADD INDEX(b,d,a,c),ADD INDEX(b,d,c,a),ADD INDEX(a,b,c,d),ADD INDEX(a,b,d,c), ADD INDEX(a,c,b,d),ADD INDEX(a,c,d,b),ADD INDEX(a,d,b,c),ADD INDEX(a,d,c,b), @@ -47,7 +49,9 @@ ADD INDEX(d,b,a,c),ADD INDEX(d,b,c,a),ADD INDEX(d,c,a,b),ADD INDEX(d,c,b,a), ADD INDEX(a,b,c), ADD INDEX(a,c,b), ADD INDEX(a,c,d), ADD INDEX(a,d,c), ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c), ALGORITHM=COPY; -ERROR HY000: Lost connection to MySQL server during query +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; #sql-temporary.frm #sql-temporary.ibd FTS_INDEX_1.ibd diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result index cd5a3c340da..295a9f1bed8 100644 --- a/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result @@ -17,21 +17,9 @@ SELECT COUNT(*) FROM t1; COUNT(*) 10000 CREATE INDEX idx_title ON t1(title); -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check status OK -SELECT * FROM t1 WHERE title = 'a10'; -class id title -10 10 a10 -SELECT * FROM t1 WHERE title = 'a5000'; -class id title -5000 5000 a5000 -SELECT * FROM t1 WHERE title = 'a10000'; -class id title -10000 10000 a10000 -SELECT * FROM t1 WHERE title = 'a10010'; -class id title -DROP TABLE t1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +RENAME TABLE t1 TO t0; # Test Blob CREATE TABLE t1( a INT PRIMARY KEY, @@ -48,9 +36,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -ALTER TABLE t1 DROP COLUMN c; -CHECK TABLE t1; +ALTER TABLE t1 DROP COLUMN c, FORCE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +CHECK TABLE t0,t1; Table Op Msg_type Msg_text +test.t0 check status OK test.t1 check status OK SELECT CHAR_LENGTH(b) FROM t1; CHAR_LENGTH(b) @@ -59,14 +50,33 @@ CHAR_LENGTH(b) 40000 60000 DROP TABLE t1; +RENAME TABLE t0 to t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; CREATE TABLE t1( class INT, id INT, title VARCHAR(100) ) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; -SET debug_dbug='+d,crash_commit_before'; +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; CREATE INDEX idx_title ON t1(title); -ERROR HY000: Lost connection to MySQL server during query +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; SELECT COUNT(*) FROM t1; COUNT(*) 10000 @@ -104,9 +114,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -SET debug_dbug='+d,crash_commit_before'; -ALTER TABLE t1 DROP COLUMN c; -ERROR HY000: Lost connection to MySQL server during query +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t1 DROP COLUMN c, FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK @@ -137,21 +150,9 @@ SELECT COUNT(*) FROM t1; COUNT(*) 10000 CREATE INDEX idx_title ON t1(title); -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check status OK -SELECT * FROM t1 WHERE title = 'a10'; -class id title -10 10 a10 -SELECT * FROM t1 WHERE title = 'a5000'; -class id title -5000 5000 a5000 -SELECT * FROM t1 WHERE title = 'a10000'; -class id title -10000 10000 a10000 -SELECT * FROM t1 WHERE title = 'a10010'; -class id title -DROP TABLE t1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +RENAME TABLE t1 TO t0; # Test Blob CREATE TABLE t1( a INT PRIMARY KEY, @@ -168,9 +169,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -ALTER TABLE t1 DROP COLUMN c; -CHECK TABLE t1; +ALTER TABLE t1 DROP COLUMN c, FORCE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +CHECK TABLE t0,t1; Table Op Msg_type Msg_text +test.t0 check status OK test.t1 check status OK SELECT CHAR_LENGTH(b) FROM t1; CHAR_LENGTH(b) @@ -179,14 +183,33 @@ CHAR_LENGTH(b) 40000 60000 DROP TABLE t1; +RENAME TABLE t0 to t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; CREATE TABLE t1( class INT, id INT, title VARCHAR(100) ) ENGINE=InnoDB ROW_FORMAT=COMPACT; -SET debug_dbug='+d,crash_commit_before'; +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; CREATE INDEX idx_title ON t1(title); -ERROR HY000: Lost connection to MySQL server during query +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; SELECT COUNT(*) FROM t1; COUNT(*) 10000 @@ -224,9 +247,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -SET debug_dbug='+d,crash_commit_before'; -ALTER TABLE t1 DROP COLUMN c; -ERROR HY000: Lost connection to MySQL server during query +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t1 DROP COLUMN c, FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK @@ -257,21 +283,9 @@ SELECT COUNT(*) FROM t1; COUNT(*) 10000 CREATE INDEX idx_title ON t1(title); -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check status OK -SELECT * FROM t1 WHERE title = 'a10'; -class id title -10 10 a10 -SELECT * FROM t1 WHERE title = 'a5000'; -class id title -5000 5000 a5000 -SELECT * FROM t1 WHERE title = 'a10000'; -class id title -10000 10000 a10000 -SELECT * FROM t1 WHERE title = 'a10010'; -class id title -DROP TABLE t1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +RENAME TABLE t1 TO t0; # Test Blob CREATE TABLE t1( a INT PRIMARY KEY, @@ -288,9 +302,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -ALTER TABLE t1 DROP COLUMN c; -CHECK TABLE t1; +ALTER TABLE t1 DROP COLUMN c, FORCE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +CHECK TABLE t0,t1; Table Op Msg_type Msg_text +test.t0 check status OK test.t1 check status OK SELECT CHAR_LENGTH(b) FROM t1; CHAR_LENGTH(b) @@ -299,14 +316,33 @@ CHAR_LENGTH(b) 40000 60000 DROP TABLE t1; +RENAME TABLE t0 to t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; CREATE TABLE t1( class INT, id INT, title VARCHAR(100) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -SET debug_dbug='+d,crash_commit_before'; +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; CREATE INDEX idx_title ON t1(title); -ERROR HY000: Lost connection to MySQL server during query +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; SELECT COUNT(*) FROM t1; COUNT(*) 10000 @@ -344,9 +380,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -SET debug_dbug='+d,crash_commit_before'; -ALTER TABLE t1 DROP COLUMN c; -ERROR HY000: Lost connection to MySQL server during query +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t1 DROP COLUMN c, FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK @@ -378,21 +417,9 @@ SELECT COUNT(*) FROM t1; COUNT(*) 10000 CREATE INDEX idx_title ON t1(title); -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check status OK -SELECT * FROM t1 WHERE title = 'a10'; -class id title -10 10 a10 -SELECT * FROM t1 WHERE title = 'a5000'; -class id title -5000 5000 a5000 -SELECT * FROM t1 WHERE title = 'a10000'; -class id title -10000 10000 a10000 -SELECT * FROM t1 WHERE title = 'a10010'; -class id title -DROP TABLE t1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +RENAME TABLE t1 TO t0; # Test Blob SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( @@ -410,9 +437,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -ALTER TABLE t1 DROP COLUMN c; -CHECK TABLE t1; +ALTER TABLE t1 DROP COLUMN c, FORCE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +CHECK TABLE t0,t1; Table Op Msg_type Msg_text +test.t0 check status OK test.t1 check status OK SELECT CHAR_LENGTH(b) FROM t1; CHAR_LENGTH(b) @@ -421,15 +451,34 @@ CHAR_LENGTH(b) 40000 60000 DROP TABLE t1; +RENAME TABLE t0 to t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( class INT, id INT, title VARCHAR(100) ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; -SET debug_dbug='+d,crash_commit_before'; +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; CREATE INDEX idx_title ON t1(title); -ERROR HY000: Lost connection to MySQL server during query +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; SELECT COUNT(*) FROM t1; COUNT(*) 10000 @@ -468,9 +517,12 @@ CHAR_LENGTH(b) 20000 40000 60000 -SET debug_dbug='+d,crash_commit_before'; -ALTER TABLE t1 DROP COLUMN c; -ERROR HY000: Lost connection to MySQL server during query +connect hang,localhost,root; +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t1 DROP COLUMN c, FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +disconnect hang; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK @@ -481,5 +533,4 @@ CHAR_LENGTH(b) 40000 60000 DROP TABLE t1; -SET GLOBAL innodb_file_per_table=default; DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/t/alter_copy.test b/mysql-test/suite/innodb/t/alter_copy.test index 0dce61994b5..b7ab05a061a 100644 --- a/mysql-test/suite/innodb/t/alter_copy.test +++ b/mysql-test/suite/innodb/t/alter_copy.test @@ -37,11 +37,11 @@ SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column'); SHOW CREATE TABLE t1; ALTER TABLE t1 FORCE, ALGORITHM=COPY; -# crash right after the last write_row(), before the first commit of ALTER TABLE ---source include/expect_crash.inc +# kill right after the last write_row(), before the first commit of ALTER TABLE +connect (hang,localhost,root); -SET DEBUG_DBUG='+d,crash_commit_before'; ---error 2013 +SET DEBUG_SYNC='alter_table_copy_trans_commit SIGNAL hung WAIT_FOR ever'; +send # create 32 secondary indexes ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX(b,a,d,c), ADD INDEX(b,d,a,c),ADD INDEX(b,d,c,a),ADD INDEX(a,b,c,d),ADD INDEX(a,b,d,c), @@ -53,8 +53,13 @@ ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c), ALGORITHM=COPY; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +let $shutdown_timeout=0; --let $restart_parameters= --innodb-force-recovery=3 ---source include/start_mysqld.inc +--source include/restart_mysqld.inc +disconnect hang; +let $shutdown_timeout=; let $datadir=`select @@datadir`; --replace_regex /#sql-[0-9a-f_]*/#sql-temporary/ /FTS_[0-9a-f]*_[0-9a-f]*/FTS/ --list_files $datadir/test -- cgit v1.2.1 From eedd6179c1e5e8651270b353a693887179ad7bf3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 13 Aug 2019 14:54:24 +0300 Subject: MDEV-20138 innodb.trx_id_future fails on 10.3+ Starting with MDEV-12288 in MariaDB Server 10.3, the transaction identifiers on records will be reset on purge. Because purge might or might not run to completion before shutdown, it could happen that the bogus transaction identifier that our test is writing will be reset by purge after restart, and the expected warning message on SELECT will fail to appear. We resolve the race condition by ensuring that purge runs to completion before the shutdown. --- mysql-test/suite/innodb/r/trx_id_future.result | 2 ++ mysql-test/suite/innodb/t/trx_id_future.test | 3 ++- 2 files changed, 4 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/trx_id_future.result b/mysql-test/suite/innodb/r/trx_id_future.result index 887d11ddcc2..17b76b4b1c1 100644 --- a/mysql-test/suite/innodb/r/trx_id_future.result +++ b/mysql-test/suite/innodb/r/trx_id_future.result @@ -2,8 +2,10 @@ # Bug #20445525 ADD A CONSISTENCY CHECK AGAINST DB_TRX_ID BEING # IN THE FUTURE # +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; CREATE TABLE t1(a INT) row_format=redundant engine=innoDB; INSERT INTO t1 VALUES(1); +InnoDB 0 transactions not purged NOT FOUND /\[Warning\] InnoDB: A transaction id in a record of table `test`\.`t1` is newer than the system-wide maximum/ in mysqld.1.err call mtr.add_suppression("\\[Warning\\] InnoDB: A transaction id in a record of table `test`\\.`t1` is newer than the system-wide maximum"); SELECT * FROM t1; diff --git a/mysql-test/suite/innodb/t/trx_id_future.test b/mysql-test/suite/innodb/t/trx_id_future.test index deba753caca..5ae0d0093ba 100644 --- a/mysql-test/suite/innodb/t/trx_id_future.test +++ b/mysql-test/suite/innodb/t/trx_id_future.test @@ -7,13 +7,14 @@ --source include/have_innodb.inc --source include/not_embedded.inc +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; let PAGE_SIZE=`select @@innodb_page_size`; CREATE TABLE t1(a INT) row_format=redundant engine=innoDB; INSERT INTO t1 VALUES(1); let MYSQLD_DATADIR=`select @@datadir`; - +--source include/wait_all_purged.inc --source include/shutdown_mysqld.inc perl; -- cgit v1.2.1 From 39db116562f7e5bdd4b8c5dbbdc21a06c7ae09ad Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Tue, 13 Aug 2019 14:31:34 +0300 Subject: MDEV-18862 Unfortunate error message upon attempt to drop system versioning Fix error code. --- mysql-test/suite/versioning/r/alter.result | 4 ++-- mysql-test/suite/versioning/t/alter.test | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 2afd3921154..8cc63403bb0 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -622,9 +622,9 @@ set sql_mode= default; set system_versioning_alter_history= keep; create or replace table t1 (x int) with system versioning; alter table t1 drop column `row_start`, drop column `row_end`, drop period for system_time, drop system versioning; -ERROR HY000: No 'PERIOD FOR SYSTEM_TIME' in system-versioned `t1` +ERROR 42000: Can't DROP PERIOD FOR SYSTEM_TIME on `t1`; check that it exists alter table t1 drop period for system_time; -ERROR HY000: No 'PERIOD FOR SYSTEM_TIME' in system-versioned `t1` +ERROR 42000: Can't DROP PERIOD FOR SYSTEM_TIME on `t1`; check that it exists alter table t1 drop column `row_start`, drop column `row_end`, drop system versioning; ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists alter table t1 drop column `row_end`; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index c04c2020460..7ad3c97e399 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -519,9 +519,9 @@ set sql_mode= default; --echo # set system_versioning_alter_history= keep; create or replace table t1 (x int) with system versioning; ---error ER_VERS_NO_PERIOD +--error ER_CANT_DROP_FIELD_OR_KEY alter table t1 drop column `row_start`, drop column `row_end`, drop period for system_time, drop system versioning; ---error ER_VERS_NO_PERIOD +--error ER_CANT_DROP_FIELD_OR_KEY alter table t1 drop period for system_time; --error ER_CANT_DROP_FIELD_OR_KEY alter table t1 drop column `row_start`, drop column `row_end`, drop system versioning; -- cgit v1.2.1 From a20f6f9853e522ad388f5b968ce11af3c5d1fc10 Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Wed, 14 Aug 2019 15:52:08 +0300 Subject: MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table Exclude SELECT and INSERT SELECT from vers_set_hist_part(). We cannot likewise exclude REPLACE SELECT because it may REPLACE into itself (and REPLACE generates history). INSERT also does not generate history, but we have history modification setting which might be interfered. --- mysql-test/suite/versioning/r/partition.result | 7 +++++++ mysql-test/suite/versioning/t/partition.test | 7 +++++++ 2 files changed, 14 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index da851791640..b029efa7479 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -569,6 +569,13 @@ delete from t1; Warnings: Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions unlock tables; +# +# MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table +# +create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current); +execute immediate 'select * from t1 for update'; +pk +drop table t1; # Test cleanup drop database test; create database test; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index f602c49d7c1..a3feadee1b5 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -529,6 +529,13 @@ delete from t1 where x < 3; delete from t1; unlock tables; +--echo # +--echo # MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table +--echo # +create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current); +execute immediate 'select * from t1 for update'; +drop table t1; + --echo # Test cleanup drop database test; create database test; -- cgit v1.2.1