From 149b75476837fb96c28739d5368e977e39fd671b Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Thu, 7 Mar 2019 13:43:53 +0400 Subject: MDEV-17595 - ALTER TABLE ADD FOREIGN KEY crash ALTER TABLE ... ADD FOREIGN KEY may trigger assertion failure when it has LOCK=EXCLUSIVE clause or concurrent FLUSH TABLES is being executed. In both cases being altered table is marked as flushed, which forces subsequent attempt to open parent table to re-open. Which in turn is not allowed while transaction is running. Rather than opening parent table, just take appropriate MDL lock. Also removed table_already_fk_prelocked() check: MDL itself has much better methods to handle duplicate locks. E.g. the former won't acquire MDL_SHARED_NO_WRITE if it already has MDL_SHARED_READ. --- mysql-test/suite/innodb/r/foreign-keys.result | 13 +++++++++++++ mysql-test/suite/innodb/t/foreign-keys.test | 15 +++++++++++++++ 2 files changed, 28 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result index 66fc00e34d0..68528521fb6 100644 --- a/mysql-test/suite/innodb/r/foreign-keys.result +++ b/mysql-test/suite/innodb/r/foreign-keys.result @@ -87,3 +87,16 @@ drop table t3; drop table t2; drop table t1; set debug_sync='reset'; +# +# MDEV-17595 - Server crashes in copy_data_between_tables or +# Assertion `thd->transaction.stmt.is_empty() || +# (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' +# fails in close_tables_for_reopen upon concurrent +# ALTER TABLE and FLUSH +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2); +CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t2 VALUES(2); +ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE; +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test index 7ef440b260b..ced44a89d7c 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -111,3 +111,18 @@ drop table t3; drop table t2; drop table t1; set debug_sync='reset'; + + +--echo # +--echo # MDEV-17595 - Server crashes in copy_data_between_tables or +--echo # Assertion `thd->transaction.stmt.is_empty() || +--echo # (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' +--echo # fails in close_tables_for_reopen upon concurrent +--echo # ALTER TABLE and FLUSH +--echo # +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2); +CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t2 VALUES(2); +ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE; +DROP TABLE t2, t1; -- cgit v1.2.1 From acb4a872049e0ac23a1fdf7a1aaf12ed2bf858a2 Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Tue, 12 Mar 2019 01:09:55 +0400 Subject: MDEV-18886 JSON_ARRAY() does not recognise JSON argument. JSON_ARRAY and JSON_OBJECT functions with no arguments now get the connection charset. Item_func_convert_charset returns the correct is_json() flag. --- mysql-test/r/func_json.result | 12 ++++++++++++ mysql-test/t/func_json.test | 7 +++++++ 2 files changed, 19 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index 74798b13e3a..e74d6292476 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -830,3 +830,15 @@ SET sql_mode=default; select JSON_VALID( '{"a":1]' ); JSON_VALID( '{"a":1]' ) 0 +# +# MDEV-18886 JSON_ARRAY() does not recognise JSON argument. +# +SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket')); +JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket')) +["str", {"plugin": "unix_socket"}] +SELECT CHARSET(JSON_ARRAY()); +CHARSET(JSON_ARRAY()) +latin1 +SELECT CHARSET(JSON_OBJECT()); +CHARSET(JSON_OBJECT()) +latin1 diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index 43bd19801d5..ef3fdc685fe 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -487,3 +487,10 @@ SET sql_mode=default; select JSON_VALID( '{"a":1]' ); +--echo # +--echo # MDEV-18886 JSON_ARRAY() does not recognise JSON argument. +--echo # +SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket')); +SELECT CHARSET(JSON_ARRAY()); +SELECT CHARSET(JSON_OBJECT()); + -- cgit v1.2.1 From 7025a51a7b85376b2f01b1f32e737278e9aa659b Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 8 Mar 2019 18:14:03 +0100 Subject: fix the typo OPTION_NO_CHECK_CONSTRAINT_CHECKS --- mysql-test/r/check_constraint.result | 13 +++++++++++++ mysql-test/r/mysqlbinlog.result | 16 ++++++++-------- mysql-test/suite/binlog/r/binlog_base64_flag.result | 2 +- mysql-test/t/check_constraint.test | 14 ++++++++++++++ 4 files changed, 36 insertions(+), 9 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/check_constraint.result b/mysql-test/r/check_constraint.result index 8cb1066ba9a..3511af84166 100644 --- a/mysql-test/r/check_constraint.result +++ b/mysql-test/r/check_constraint.result @@ -222,3 +222,16 @@ ERROR 23000: CONSTRAINT `t.b` failed for `test`.`t` insert into t values (1,1); ERROR 23000: CONSTRAINT `b` failed for `test`.`t` drop table t; +create table t1 (a int auto_increment primary key, b int, check (b > 5)); +insert t1 (b) values (1); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` +insert t1 (b) values (10); +select * from t1 where a is null; +a b +set sql_auto_is_null=1; +select * from t1 where a is null; +a b +1 10 +insert t1 (b) values (1); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` +drop table t1; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index bac33753d4c..35ab4a5b54f 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -724,7 +724,7 @@ ROLLBACK/*!*/; use `test`/*!*/; SET TIMESTAMP=1253783037/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; @@ -778,7 +778,7 @@ DELIMITER /*!*/; ROLLBACK/*!*/; SET TIMESTAMP=1253783037/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; @@ -813,7 +813,7 @@ ROLLBACK /* added by mysqlbinlog */; DELIMITER /*!*/; SET TIMESTAMP=1266652094/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; @@ -855,7 +855,7 @@ ROLLBACK /* added by mysqlbinlog */; DELIMITER /*!*/; SET TIMESTAMP=1266652094/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; @@ -964,7 +964,7 @@ AAAAAAAAAAAAAAAAAAAgrgJSFzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC # Event: Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1375907364/*!*/; SET @@session.pseudo_thread_id=1/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; @@ -1047,7 +1047,7 @@ AAAAAAAAAAAAAAAAAAA/rQJSGzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC # Event: Query thread_id=1 exec_time=1 error_code=0 SET TIMESTAMP=1375907141/*!*/; SET @@session.pseudo_thread_id=1/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; @@ -1130,7 +1130,7 @@ AAAAAAAAAAAAAAAAAAAnrAJSHzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC # Event: Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1375906879/*!*/; SET @@session.pseudo_thread_id=1/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; @@ -1213,7 +1213,7 @@ AAAAAAAAAAAAAAAAAABbsAJSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC # Event: Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1375907933/*!*/; SET @@session.pseudo_thread_id=1/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; diff --git a/mysql-test/suite/binlog/r/binlog_base64_flag.result b/mysql-test/suite/binlog/r/binlog_base64_flag.result index 255f2b3b09b..553206cab86 100644 --- a/mysql-test/suite/binlog/r/binlog_base64_flag.result +++ b/mysql-test/suite/binlog/r/binlog_base64_flag.result @@ -57,7 +57,7 @@ DELIMITER /*!*/; # at 102 <#>use `test`/*!*/; SET TIMESTAMP=1196959712/*!*/; -<#>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/; +<#>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; diff --git a/mysql-test/t/check_constraint.test b/mysql-test/t/check_constraint.test index 37fdc7203b1..93538fd1666 100644 --- a/mysql-test/t/check_constraint.test +++ b/mysql-test/t/check_constraint.test @@ -162,3 +162,17 @@ insert into t values (-1, 0); insert into t values (1,1); drop table t; + +# +# check constraints and auto_is_null typo +# +create table t1 (a int auto_increment primary key, b int, check (b > 5)); +--error ER_CONSTRAINT_FAILED +insert t1 (b) values (1); +insert t1 (b) values (10); +select * from t1 where a is null; +set sql_auto_is_null=1; +select * from t1 where a is null; +--error ER_CONSTRAINT_FAILED +insert t1 (b) values (1); +drop table t1; -- cgit v1.2.1 From 69abd43703fcf68c4cf1056bf5bd56c690de5b4e Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sun, 10 Mar 2019 18:55:35 +0100 Subject: MDEV-17070 Table corruption or Assertion `table->file->stats.records > 0 || error' or Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed upon actions on temporary table This was caused by a combination of factors: * MyISAM/Aria temporary tables historically never saved the state to disk (MYI/MAI), because the state never needed to persist * certain ALTER TABLE operations modify the original TABLE structure and if they fail, the original table has to be reopened to revert all changes (m_needs_reopen=1) as a result, when ALTER fails and MyISAM/Aria temp table gets reopened, it reads the stale state from the disk. As a fix, MyISAM/Aria tables now *always* write the state to disk on close, *unless* HA_EXTRA_PREPARE_FOR_DROP was done first. And the server now always does HA_EXTRA_PREPARE_FOR_DROP before dropping a temporary table. --- mysql-test/r/reopen_temp_table.result | 53 +++++++++++++++++++++++++++++++++++ mysql-test/t/reopen_temp_table.test | 40 ++++++++++++++++++++++++++ 2 files changed, 93 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/reopen_temp_table.result b/mysql-test/r/reopen_temp_table.result index 217aa3c8bad..ef215366db7 100644 --- a/mysql-test/r/reopen_temp_table.result +++ b/mysql-test/r/reopen_temp_table.result @@ -190,3 +190,56 @@ NULL NULL DROP TABLE t; # Cleanup DROP DATABASE temp_db; +USE test; +create temporary table t1 (f char(255), b int, index(b)) engine=MyISAM; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +alter table t1 change if exists a b int, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +select * from t1; +f b +NULL 1 +NULL 2 +drop table t1; +create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=1; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +alter table t1 change if exists a b int, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +select * from t1; +f b +NULL 1 +NULL 2 +drop table t1; +create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=page; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +alter table t1 change if exists a b int, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +select * from t1; +f b +NULL 1 +NULL 2 +drop table t1; +create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=dynamic; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +alter table t1 change if exists a b int, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +select * from t1; +f b +NULL 1 +NULL 2 +drop table t1; diff --git a/mysql-test/t/reopen_temp_table.test b/mysql-test/t/reopen_temp_table.test index 2aa6caa1655..2b3ff3bab5e 100644 --- a/mysql-test/t/reopen_temp_table.test +++ b/mysql-test/t/reopen_temp_table.test @@ -182,3 +182,43 @@ DROP TABLE t; --echo # Cleanup DROP DATABASE temp_db; +USE test; + +# +# MDEV-17070 Table corruption or Assertion `table->file->stats.records > 0 || error' or Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed upon actions on temporary table +# +create temporary table t1 (f char(255), b int, index(b)) engine=MyISAM; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t1 change if exists a b int, algorithm=inplace; +check table t1; +select * from t1; +drop table t1; + +create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=1; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t1 change if exists a b int, algorithm=inplace; +check table t1; +select * from t1; +drop table t1; + +create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=page; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t1 change if exists a b int, algorithm=inplace; +check table t1; +select * from t1; +drop table t1; + +create temporary table t1 (f char(255), b int, index(b)) engine=aria transactional=0 row_format=dynamic; +replace into t1 values (null,1),(null,2); +alter table t1 add fulltext key(f); +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t1 change if exists a b int, algorithm=inplace; +check table t1; +select * from t1; +drop table t1; -- cgit v1.2.1 From f72760df3390407c3cbe876e827818bc52849b71 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 12 Mar 2019 14:20:01 +0200 Subject: Add an end-of-tests marker to ease merges --- mysql-test/r/func_json.result | 3 +++ mysql-test/t/func_json.test | 3 +++ 2 files changed, 6 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index e74d6292476..752a745b3b4 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -842,3 +842,6 @@ latin1 SELECT CHARSET(JSON_OBJECT()); CHARSET(JSON_OBJECT()) latin1 +# +# End of 10.2 tests +# diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index ef3fdc685fe..8e924fb7f9e 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -494,3 +494,6 @@ SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket SELECT CHARSET(JSON_ARRAY()); SELECT CHARSET(JSON_OBJECT()); +--echo # +--echo # End of 10.2 tests +--echo # -- cgit v1.2.1