diff options
Diffstat (limited to 'mysql-test')
47 files changed, 3404 insertions, 59 deletions
diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc index 0031cb68647..98988ab55ba 100644 --- a/mysql-test/include/handler.inc +++ b/mysql-test/include/handler.inc @@ -1757,3 +1757,35 @@ disconnect con51355; --echo # Connection default connection default; + +--echo # +--echo # Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +delimiter |; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN + SELECT 1 FROM t2 INTO @a; + RETURN 1; +END| +delimiter ;| + +# Get f1() parsed and cached +--error ER_NO_SUCH_TABLE +SELECT f1(); + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +# This used to cause the assert +--error ER_NO_SUCH_TABLE +HANDLER t1 READ FIRST WHERE f1() = 1; +HANDLER t1 CLOSE; + +DROP FUNCTION f1; +DROP TABLE t1; diff --git a/mysql-test/lib/v1/mysql-test-run.pl b/mysql-test/lib/v1/mysql-test-run.pl index 489db060e99..5e785c86027 100755 --- a/mysql-test/lib/v1/mysql-test-run.pl +++ b/mysql-test/lib/v1/mysql-test-run.pl @@ -3113,7 +3113,6 @@ sub install_db ($$) { mtr_add_arg($args, "--bootstrap"); mtr_add_arg($args, "--basedir=%s", $path_my_basedir); mtr_add_arg($args, "--datadir=%s", $data_dir); - mtr_add_arg($args, "--loose-skip-innodb"); mtr_add_arg($args, "--loose-skip-ndbcluster"); mtr_add_arg($args, "--tmpdir=."); mtr_add_arg($args, "--core-file"); diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 90dde034e10..304f562d47d 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1370,3 +1370,16 @@ CREATE TABLE t1 (id int); INSERT INTO t1 VALUES (1), (2); ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2); DROP TABLE t1; +# +# Test for bug #53820 "ALTER a MEDIUMINT column table causes full +# table copy". +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT, b MEDIUMINT); +INSERT INTO t1 VALUES (1, 1), (2, 2); +# The below ALTER should not copy table and so no rows should +# be shown as affected. +ALTER TABLE t1 CHANGE a id INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +DROP TABLE t1; diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index 685e1e5ba4b..028c8b32f87 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12775,3 +12775,29 @@ a 1 2 DROP TABLE t1; +# +# Bug#45377: ARCHIVE tables aren't discoverable after OPTIMIZE +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a int) ENGINE=ARCHIVE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (1); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +FLUSH TABLES; +INSERT INTO t1 VALUES (2); +SELECT * FROM t1 ORDER BY a; +a +1 +2 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/r/handler_innodb.result b/mysql-test/r/handler_innodb.result index 08d2fc58e8a..121cfa89f1c 100644 --- a/mysql-test/r/handler_innodb.result +++ b/mysql-test/r/handler_innodb.result @@ -1710,3 +1710,23 @@ ERROR 42S02: Table 'test.t1' doesn't exist HANDLER t1 CLOSE; # Connection con51355 # Connection default +# +# Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +# +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +SELECT 1 FROM t2 INTO @a; +RETURN 1; +END| +SELECT f1(); +ERROR 42S02: Table 'test.t2' doesn't exist +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42S02: Table 'test.t2' doesn't exist +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; diff --git a/mysql-test/r/handler_myisam.result b/mysql-test/r/handler_myisam.result index 31bc828b0b9..fd08fd12f15 100644 --- a/mysql-test/r/handler_myisam.result +++ b/mysql-test/r/handler_myisam.result @@ -1707,6 +1707,26 @@ HANDLER t1 CLOSE; # Connection con51355 # Connection default # +# Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER +# +DROP TABLE IF EXISTS t1, t2; +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +SELECT 1 FROM t2 INTO @a; +RETURN 1; +END| +SELECT f1(); +ERROR 42S02: Table 'test.t2' doesn't exist +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42S02: Table 'test.t2' doesn't exist +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# # BUG #46456: HANDLER OPEN + TRUNCATE + DROP (temporary) TABLE, crash # CREATE TABLE t1 AS SELECT 1 AS f1; diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result index 7cc76611fb8..e3a3e0e9740 100644 --- a/mysql-test/r/heap_hash.result +++ b/mysql-test/r/heap_hash.result @@ -382,3 +382,14 @@ INSERT INTO t1 VALUES('A ', 'A '); ERROR 23000: Duplicate entry 'A -A ' for key 'key1' DROP TABLE t1; End of 5.0 tests +# +# Bug #55472: Assertion failed in heap_rfirst function of hp_rfirst.c +# on DELETE statement +# +CREATE TABLE t1 (col_int_nokey INT, +col_int_key INT, +INDEX(col_int_key) USING HASH) ENGINE = HEAP; +INSERT INTO t1 (col_int_nokey, col_int_key) VALUES (3, 0), (4, 0), (3, 1); +DELETE FROM t1 WHERE col_int_nokey = 5 ORDER BY col_int_key LIMIT 2; +DROP TABLE t1; +End of 5.5 tests diff --git a/mysql-test/r/innodb_mysql_lock.result b/mysql-test/r/innodb_mysql_lock.result index 95adf712cb4..bf1c3a89f40 100644 --- a/mysql-test/r/innodb_mysql_lock.result +++ b/mysql-test/r/innodb_mysql_lock.result @@ -116,3 +116,35 @@ Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK DROP TABLE t1; +# +# Bug#49891 View DDL breaks REPEATABLE READ +# +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v2; +CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb; +CREATE TABLE t2 ( f1 INTEGER ); +CREATE VIEW v1 AS SELECT 1 FROM t1; +# Connection con3 +LOCK TABLE t1 WRITE; +# Connection default +START TRANSACTION; +# Sending: +SELECT * FROM v1; +# Connection con2 +# Waiting for 'SELECT * FROM v1' to sync in. +# Sending: +ALTER VIEW v1 AS SELECT 2 FROM t2; +# Connection con3 +# Waiting for 'ALTER VIEW v1 AS SELECT 2 FROM t2' to sync in. +UNLOCK TABLES; +# Connection default; +# Reaping: SELECT * FROM v1 +1 +SELECT * FROM v1; +1 +COMMIT; +# Connection con2 +# Reaping: ALTER VIEW v1 AS SELECT 2 FROM t2 +# Connection default +DROP TABLE t1, t2; +DROP VIEW v1; diff --git a/mysql-test/r/innodb_mysql_sync.result b/mysql-test/r/innodb_mysql_sync.result index 0e75e62b13a..43a98829d4e 100644 --- a/mysql-test/r/innodb_mysql_sync.result +++ b/mysql-test/r/innodb_mysql_sync.result @@ -48,3 +48,21 @@ Warnings: Error 1146 Table 'test.t1' doesn't exist # Connection default SET DEBUG_SYNC= "RESET"; +# +# Bug#53757 assert in mysql_truncate_by_delete +# +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1(a INT) Engine=InnoDB; +CREATE TABLE t2(id INT); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES(connection_id()); +SET DEBUG_SYNC= "open_and_process_table SIGNAL opening WAIT_FOR killed"; +# Sending: (not reaped since connection is killed later) +TRUNCATE t1; +SET DEBUG_SYNC= "now WAIT_FOR opening"; +SELECT ((@id := id) - id) FROM t2; +((@id := id) - id) +0 +KILL @id; +SET DEBUG_SYNC= "now SIGNAL killed"; +DROP TABLE t1, t2; diff --git a/mysql-test/r/lowercase_table2.result b/mysql-test/r/lowercase_table2.result index 26a151b55aa..caee7a7662b 100644 --- a/mysql-test/r/lowercase_table2.result +++ b/mysql-test/r/lowercase_table2.result @@ -56,6 +56,7 @@ CREATE DATABASE `TEST_$1`; SHOW DATABASES LIKE "TEST%"; Database (TEST%) TEST_$1 +test DROP DATABASE `test_$1`; CREATE TABLE T1 (a int) engine=innodb; INSERT INTO T1 VALUES (1); @@ -171,7 +172,7 @@ create table myUC (i int); select TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA ='mysqltest_LC2'; TABLE_SCHEMA TABLE_NAME -mysqltest_LC2 myUC +mysqltest_lc2 myUC use test; drop database mysqltest_LC2; # End of 5.1 tests diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result index 67d778211dd..0fd408b0208 100644 --- a/mysql-test/r/mdl_sync.result +++ b/mysql-test/r/mdl_sync.result @@ -2527,3 +2527,240 @@ SET DEBUG_SYNC= "now SIGNAL completed"; Field Type Collation Null Key Default Extra Privileges Comment a char(255) latin1_swedish_ci YES NULL # DROP TABLE t1; +# +# Tests for schema-scope locks +# +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +# Test 1: +# CREATE DATABASE blocks database DDL on the same database, but +# not database DDL on different databases. Tests X vs X lock. +# +# Connection default +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +CREATE DATABASE db1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +CREATE DATABASE db1; +# Connection con3 +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: CREATE DATABASE db1 +# Connection con2 +# Reaping: CREATE DATABASE db1 +ERROR HY000: Can't create database 'db1'; database exists +# Test 2: +# ALTER DATABASE blocks database DDL on the same database, but +# not database DDL on different databases. Tests X vs X lock. +# +# Connection default +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; +# Connection con3 +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +# Connection con2 +# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +# Connection default +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +DROP DATABASE db1; +# Connection con3 +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +# Connection con2 +# Reaping: DROP DATABASE db1 +CREATE DATABASE db1; +# Test 3: +# Two ALTER..UPGRADE of the same database are mutually exclusive, but +# two ALTER..UPGRADE of different databases are not. Tests X vs X lock. +# +# Connection default +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; +# Connection con3 +ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME; +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME +# Connection con2 +# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME +ERROR 42000: Unknown database '#mysql50#a-b-c' +DROP DATABASE `a-b-c`; +DROP DATABASE `a-b-c-d`; +# Test 4: +# DROP DATABASE blocks database DDL on the same database, but +# not database DDL on different databases. Tests X vs X lock. +# +# Connection default +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +DROP DATABASE db1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +DROP DATABASE db1; +# Connection con3 +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: DROP DATABASE db1 +# Connection con2 +# Reaping: DROP DATABASE db1 +ERROR HY000: Can't drop database 'db1'; database doesn't exist +# Connection default +CREATE DATABASE db1; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +DROP DATABASE db1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; +# Connection con3 +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: DROP DATABASE db1 +# Connection con2 +# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +Got one of the listed errors +# Test 5: +# Locked database name prevents CREATE of tables in that database. +# Tests X vs IX lock. +# +# Connection default +CREATE DATABASE db1; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +DROP DATABASE db1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +CREATE TABLE db1.t1 (a INT); +# Connection con3 +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: DROP DATABASE db1 +# Connection con2 +# Reaping: CREATE TABLE db1.t1 (a INT) +ERROR 42000: Unknown database 'db1' +# Test 6: +# Locked database name prevents RENAME of tables to/from that database. +# Tests X vs IX lock. +# +# Connection default +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +DROP DATABASE db1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +RENAME TABLE db1.t1 TO test.t1; +# Connection con3 +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: DROP DATABASE db1 +# Connection con2 +# Reaping: RENAME TABLE db1.t1 TO test.t1 +Got one of the listed errors +# Connection default +CREATE DATABASE db1; +CREATE TABLE test.t2 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +DROP DATABASE db1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +RENAME TABLE test.t2 TO db1.t2; +# Connection con3 +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: DROP DATABASE db1 +# Connection con2 +# Reaping: RENAME TABLE test.t2 TO db1.t2 +Got one of the listed errors +DROP TABLE test.t2; +# Test 7: +# Locked database name prevents DROP of tables in that database. +# Tests X vs IX lock. +# +# Connection default +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +# Sending: +DROP DATABASE db1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# Sending: +DROP TABLE db1.t1; +# Connection con3 +SET DEBUG_SYNC= 'now SIGNAL blocked'; +# Connection default +# Reaping: DROP DATABASE db1 +# Connection con2 +# Reaping: DROP TABLE db1.t1 +ERROR 42S02: Unknown table 't1' +# Connection default +SET DEBUG_SYNC= 'RESET'; +# +# End of tests for schema-scope locks +# +# +# Tests of granted global S lock (FLUSH TABLE WITH READ LOCK) +# +CREATE DATABASE db1; +CREATE TABLE db1.t1(a INT); +# Connection default +FLUSH TABLE WITH READ LOCK; +# Connection con2 +CREATE TABLE db1.t2(a INT); +# Connection default +UNLOCK TABLES; +# Connection con2 +# Reaping CREATE TABLE db1.t2(a INT) +# Connection default +FLUSH TABLE WITH READ LOCK; +# Connection con2 +ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; +# Connection default +UNLOCK TABLES; +# Connection con2 +# Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +# Connection default +FLUSH TABLE WITH READ LOCK; +# Connection con2 +FLUSH TABLE WITH READ LOCK; +UNLOCK TABLES; +# Connection default +UNLOCK TABLES; +DROP DATABASE db1; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 87d4f75dc8d..f7f0cea3b19 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -1,3 +1,5 @@ +set global storage_engine=myisam; +set session storage_engine=myisam; drop table if exists t1,t2,t3,t4,t5,t6; drop database if exists mysqltest; create table t1 (a int not null primary key auto_increment, message char(20)); @@ -584,7 +586,9 @@ INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); CREATE TEMPORARY TABLE t3 (c1 INT NOT NULL) ENGINE=MRG_MYISAM UNION=(t1,t2); SELECT * FROM t3; -ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +c1 +1 +2 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL); CREATE TEMPORARY TABLE t5 (c1 INT NOT NULL); INSERT INTO t4 VALUES (4); @@ -613,7 +617,9 @@ ERROR HY000: Unable to open underlying table which is differently defined or of drop table t3; create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); select * from t3; -ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +a +1 +2 drop table t3, t2, t1; # CREATE...SELECT is not implemented for MERGE tables. CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL); @@ -1196,12 +1202,13 @@ ERROR HY000: Table 't4' was not locked with LOCK TABLES # it can even be used. CREATE TEMPORARY TABLE t4 LIKE t3; SHOW CREATE TABLE t4; -ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +Table Create Table +t4 CREATE TEMPORARY TABLE `t4` ( + `c1` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) INSERT INTO t4 VALUES (4); -ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist UNLOCK TABLES; INSERT INTO t4 VALUES (4); -ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t4; # # Rename child. @@ -1229,6 +1236,8 @@ c1 2 3 4 +4 +4 RENAME TABLE t2 TO t5; SELECT * FROM t3 ORDER BY c1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist @@ -1239,6 +1248,8 @@ c1 2 3 4 +4 +4 # # 3. Normal rename with locked tables. LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; @@ -1248,6 +1259,8 @@ c1 2 3 4 +4 +4 RENAME TABLE t2 TO t5; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction SELECT * FROM t3 ORDER BY c1; @@ -1256,6 +1269,8 @@ c1 2 3 4 +4 +4 RENAME TABLE t5 TO t2; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction SELECT * FROM t3 ORDER BY c1; @@ -1264,6 +1279,8 @@ c1 2 3 4 +4 +4 UNLOCK TABLES; # # 4. Alter table rename. @@ -1277,6 +1294,8 @@ c1 2 3 4 +4 +4 # # 5. Alter table rename with locked tables. LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; @@ -1293,6 +1312,8 @@ c1 2 3 4 +4 +4 # # Rename parent. # @@ -1304,6 +1325,8 @@ c1 2 3 4 +4 +4 RENAME TABLE t3 TO t5; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction SELECT * FROM t3 ORDER BY c1; @@ -1312,6 +1335,8 @@ c1 2 3 4 +4 +4 RENAME TABLE t5 TO t3; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction SELECT * FROM t3 ORDER BY c1; @@ -1320,6 +1345,8 @@ c1 2 3 4 +4 +4 # # 5. Alter table rename with locked tables. ALTER TABLE t3 RENAME TO t5; @@ -1335,6 +1362,8 @@ c1 2 3 4 +4 +4 DROP TABLE t1, t2, t3; # # Drop locked tables. @@ -2650,6 +2679,705 @@ test.t1 optimize Error Unable to open underlying table which is differently defi test.t1 optimize note The storage engine for the table doesn't support optimize DROP TABLE t1; # +# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine +# More tests with TEMPORARY MERGE table and permanent children. +# First without locked tables. +# +DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2; +# +CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m1; +c1 c2 +INSERT INTO t1 VALUES (111, 121); +INSERT INTO m1 VALUES (211, 221); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +# +ALTER TABLE m1 RENAME m2; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TEMPORARY TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +# +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +ALTER TABLE m2 RENAME m1; +ERROR 42S01: Table 'm1' already exists +DROP TABLE m1; +ALTER TABLE m2 RENAME m1; +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +# +ALTER TABLE m1 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +SELECT * FROM m1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +ALTER TABLE t1 ADD COLUMN c3 INT; +ALTER TABLE t2 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +SELECT * FROM m1; +c1 c2 c3 +111 121 NULL +211 221 NULL +212 222 232 +# +ALTER TABLE m1 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +SELECT * FROM m1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +ALTER TABLE t1 DROP COLUMN c3; +ALTER TABLE t2 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +# +CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3); +INSERT INTO m1 VALUES (311, 321); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +311 321 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +212 222 +213 223 +SELECT * FROM t3; +c1 c2 +311 321 +# +CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3,t4); +INSERT INTO m1 VALUES (411, 421); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +311 321 +411 421 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +212 222 +213 223 +SELECT * FROM t3; +c1 c2 +311 321 +SELECT * FROM t4; +c1 c2 +411 421 +# +ALTER TABLE m1 ENGINE=MyISAM; +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO m1 VALUES (511, 521); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +311 321 +411 421 +511 521 +# +ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +212 222 +213 223 +# +CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (611, 621); +SELECT * FROM m1; +c1 c2 +611 621 +211 221 +212 222 +213 223 +DROP TABLE t1; +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +# +# +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +# +CREATE TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +DROP TABLE m2; +# +CREATE TEMPORARY TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TEMPORARY TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +DROP TABLE m2; +# +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST; +SELECT * FROM m2; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +DROP TABLE m2; +# +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST SELECT * FROM m1; +ERROR HY000: 'test.m2' is not BASE TABLE +# +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST SELECT * FROM m1; +ERROR HY000: 'test.m2' is not BASE TABLE +# +CREATE TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +DROP TABLE m2; +# +CREATE TEMPORARY TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TEMPORARY TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +DROP TABLE m2; +# +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 c2 +311 321 +411 421 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +# +# +LOCK TABLE m1 WRITE, m2 WRITE; +SELECT * FROM m1,m2 WHERE m1.c1=m2.c1; +c1 c2 c1 c2 +111 121 111 121 +111 121 111 121 +111 121 111 121 +111 121 111 121 +211 221 211 221 +211 221 211 221 +211 221 211 221 +211 221 211 221 +212 222 212 222 +212 222 212 222 +212 222 212 222 +212 222 212 222 +213 223 213 223 +213 223 213 223 +213 223 213 223 +213 223 213 223 +111 121 111 121 +111 121 111 121 +111 121 111 121 +111 121 111 121 +211 221 211 221 +211 221 211 221 +211 221 211 221 +211 221 211 221 +212 222 212 222 +212 222 212 222 +212 222 212 222 +212 222 212 222 +213 223 213 223 +213 223 213 223 +213 223 213 223 +213 223 213 223 +111 121 111 121 +111 121 111 121 +111 121 111 121 +111 121 111 121 +211 221 211 221 +211 221 211 221 +211 221 211 221 +211 221 211 221 +212 222 212 222 +212 222 212 222 +212 222 212 222 +212 222 212 222 +213 223 213 223 +213 223 213 223 +213 223 213 223 +213 223 213 223 +111 121 111 121 +111 121 111 121 +111 121 111 121 +111 121 111 121 +211 221 211 221 +211 221 211 221 +211 221 211 221 +211 221 211 221 +212 222 212 222 +212 222 212 222 +212 222 212 222 +212 222 212 222 +213 223 213 223 +213 223 213 223 +213 223 213 223 +213 223 213 223 +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4, m1, m2; +# +# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine +# More tests with TEMPORARY MERGE table and permanent children. +# (continued) Now the same with locked table. +# +CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m1; +c1 c2 +INSERT INTO t1 VALUES (111, 121); +INSERT INTO m1 VALUES (211, 221); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +# +LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE; +# +ALTER TABLE m1 RENAME m2; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TEMPORARY TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +# +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +ALTER TABLE m2 RENAME m1; +ERROR 42S01: Table 'm1' already exists +DROP TABLE m1; +ALTER TABLE m2 RENAME m1; +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +# +ALTER TABLE m1 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +SELECT * FROM m1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +ALTER TABLE t1 ADD COLUMN c3 INT; +ALTER TABLE t2 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +SELECT * FROM m1; +c1 c2 c3 +111 121 NULL +211 221 NULL +212 222 232 +# +ALTER TABLE m1 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +SELECT * FROM m1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +ALTER TABLE t1 DROP COLUMN c3; +ALTER TABLE t2 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +# +UNLOCK TABLES; +CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3); +LOCK TABLE m1 WRITE; +INSERT INTO m1 VALUES (311, 321); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +311 321 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +212 222 +213 223 +SELECT * FROM t3; +c1 c2 +311 321 +# +CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3,t4); +INSERT INTO m1 VALUES (411, 421); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +311 321 +411 421 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +212 222 +213 223 +SELECT * FROM t3; +c1 c2 +311 321 +SELECT * FROM t4; +c1 c2 +411 421 +# +ALTER TABLE m1 ENGINE=MyISAM; +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO m1 VALUES (511, 521); +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +311 321 +411 421 +511 521 +# +ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +SELECT * FROM t1; +c1 c2 +111 121 +SELECT * FROM t2; +c1 c2 +211 221 +212 222 +213 223 +# +CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (611, 621); +SELECT * FROM m1; +c1 c2 +611 621 +211 221 +212 222 +213 223 +DROP TABLE t1; +SELECT * FROM m1; +c1 c2 +111 121 +211 221 +212 222 +213 223 +# +# +SHOW CREATE TABLE m1; +Table Create Table +m1 CREATE TEMPORARY TABLE `m1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +CREATE TABLE m2 SELECT * FROM m1; +ERROR HY000: Table 'm2' was not locked with LOCK TABLES +# +CREATE TEMPORARY TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TEMPORARY TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +DROP TABLE m2; +# +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST; +SELECT * FROM m2; +c1 c2 +311 321 +411 421 +LOCK TABLE m1 WRITE, m2 WRITE; +UNLOCK TABLES; +DROP TABLE m2; +LOCK TABLE m1 WRITE; +# +# ER_TABLE_NOT_LOCKED is returned in ps-protocol +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST SELECT * FROM m1; +Got one of the listed errors +# +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST SELECT * FROM m1; +ERROR HY000: 'test.m2' is not BASE TABLE +# +CREATE TEMPORARY TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TEMPORARY TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +LOCK TABLE m1 WRITE, m2 WRITE; +SHOW CREATE TABLE m2; +Table Create Table +m2 CREATE TEMPORARY TABLE `m2` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 c2 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +DROP TABLE m2; +# +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) +INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 c2 +311 321 +411 421 +111 121 +211 221 +212 222 +213 223 +111 121 +211 221 +212 222 +213 223 +# +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4, m1, m2; +# # Bug47098 assert in MDL_context::destroy on HANDLER # <damaged merge table> OPEN # @@ -2745,4 +3473,106 @@ m2 CREATE TABLE `m2` ( `i` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`) drop tables m1, m2, t1; +# +# Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()" +# Check that unique_table() works correctly for merge tables. +# +drop table if exists t1, t2, t3, m1, m2; +create table t1 (a int); +create table t2 (a int); +create table t3 (b int); +create view v1 as select * from t3,t1; +create table m1 (a int) engine=merge union (t1, t2) insert_method=last; +create table m2 (a int) engine=merge union (t1, t2) insert_method=first; +create temporary table tmp (b int); +insert into tmp (b) values (1); +insert into t1 (a) values (1); +insert into t3 (b) values (1); +insert into m1 (a) values ((select max(a) from m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from t3, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from tmp, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +insert into m1 (a) values ((select max(a) from v1)); +ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. +insert into m1 (a) values ((select max(a) from tmp, v1)); +ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. +update m1 set a = ((select max(a) from m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from t3, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, m1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, m2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, t1)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from tmp, t2)); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +update m1 set a = ((select max(a) from v1)); +ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. +update m1 set a = ((select max(a) from tmp, v1)); +ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. +delete from m1 where a = (select max(a) from m1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from m2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, m1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, m2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, t1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from t3, t2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, m1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, m2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, t1); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from tmp, t2); +ERROR HY000: You can't specify target table 'm1' for update in FROM clause +delete from m1 where a = (select max(a) from v1); +ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. +delete from m1 where a = (select max(a) from tmp, v1); +ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. +drop view v1; +drop temporary table tmp; +drop table t1, t2, t3, m1, m2; End of 6.0 tests diff --git a/mysql-test/r/merge_mmap.result b/mysql-test/r/merge_mmap.result new file mode 100644 index 00000000000..e8014259a4a --- /dev/null +++ b/mysql-test/r/merge_mmap.result @@ -0,0 +1,190 @@ +SET GLOBAL storage_engine = MyISAM; +SET SESSION storage_engine = MyISAM; +DROP TABLE IF EXISTS t1, t2, m1, m2; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +SELECT * FROM t2; +c1 +2 +3 +4 +1 +2 +3 +4 +DROP TABLE m2, m1, t2, t1; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +DROP TABLE m2, m1, t2, t1; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +DROP TABLE m2, m1, t2, t1; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +DROP TABLE m2, m1, t2, t1; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +SELECT * FROM t2; +c1 +2 +3 +4 +1 +2 +3 +4 +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) +INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +c1 +1 +2 +3 +4 +1 +2 +3 +4 +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; +End of 6.0 tests diff --git a/mysql-test/r/partition_debug_sync.result b/mysql-test/r/partition_debug_sync.result index 5ab1044934b..0e3241cf88e 100644 --- a/mysql-test/r/partition_debug_sync.result +++ b/mysql-test/r/partition_debug_sync.result @@ -47,7 +47,7 @@ ENGINE = MYISAM PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (100), PARTITION p3 VALUES LESS THAN MAXVALUE ) */; -SET DEBUG_SYNC= 'open_tables_acquire_upgradable_mdl SIGNAL removing_partitions WAIT_FOR waiting_for_alter'; +SET DEBUG_SYNC= 'alter_table_before_open_tables SIGNAL removing_partitions WAIT_FOR waiting_for_alter'; SET DEBUG_SYNC= 'alter_table_before_rename_result_table WAIT_FOR delete_done'; ALTER TABLE t2 REMOVE PARTITIONING; # Con default diff --git a/mysql-test/r/schema.result b/mysql-test/r/schema.result index 2919606d74a..853c3bcf575 100644 --- a/mysql-test/r/schema.result +++ b/mysql-test/r/schema.result @@ -16,19 +16,21 @@ drop schema foo; # Bug #48940 MDL deadlocks against mysql_rm_db # DROP SCHEMA IF EXISTS schema1; +DROP SCHEMA IF EXISTS schema2; # Connection default CREATE SCHEMA schema1; +CREATE SCHEMA schema2; CREATE TABLE schema1.t1 (a INT); SET autocommit= FALSE; INSERT INTO schema1.t1 VALUES (1); # Connection 2 DROP SCHEMA schema1; # Connection default -ALTER SCHEMA schema1 DEFAULT CHARACTER SET utf8; -Got one of the listed errors +ALTER SCHEMA schema2 DEFAULT CHARACTER SET utf8; SET autocommit= TRUE; # Connection 2 # Connection default +DROP SCHEMA schema2; # # Bug #49988 MDL deadlocks with mysql_create_db, reload_acl_and_cache # @@ -48,3 +50,48 @@ ERROR HY000: Can't execute the given command because you have active locked tabl UNLOCK TABLES; # Connection con2 # Connection default +# +# Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER +# +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +INSERT INTO db1.t1 VALUES (1), (2); +# Connection con1 +HANDLER db1.t1 OPEN; +# Connection default +# Sending: +DROP DATABASE db1; +# Connection con2 +# Connection con1 +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +HANDLER t1 CLOSE; +# Connection default +# Reaping: DROP DATABASE db1 +# +# Tests for increased CREATE/ALTER/DROP DATABASE concurrency with +# database name locks. +# +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +# Connection default +CREATE DATABASE db1; +CREATE TABLE db1.t1 (id INT); +START TRANSACTION; +INSERT INTO db1.t1 VALUES (1); +# Connection 2 +# DROP DATABASE should block due to the active transaction +# Sending: +DROP DATABASE db1; +# Connection 3 +# But it should still be possible to CREATE/ALTER/DROP other databases. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +# Connection default +# End the transaction so DROP DATABASE db1 can continue +COMMIT; +# Connection 2 +# Reaping: DROP DATABASE db1 +# Connection default; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index ef2277fef38..c1a75281e0e 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1466,3 +1466,51 @@ t1 CREATE TABLE `t1` ( # Switching to connection 'default'. UNLOCK TABLES; DROP TABLE t1; +# +# Bug#54905 Connection with WRITE lock cannot ALTER table due to +# concurrent SHOW CREATE +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a INT); +# Connection con1 +LOCK TABLE t1 WRITE; +# Connection default +START TRANSACTION; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# Connection con1 +ALTER TABLE t1 CHARACTER SET = utf8; +UNLOCK TABLES; +# Connection default +COMMIT; +DROP TABLE t1; +# +# Bug#55498 SHOW CREATE TRIGGER takes wrong type of metadata lock. +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1; +# Test 1: SHOW CREATE TRIGGER with WRITE locked table. +# Connection con1 +LOCK TABLE t1 WRITE; +# Connection default +SHOW CREATE TRIGGER t1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci +# Connection con1 +UNLOCK TABLES; +# Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction +# Connection default +START TRANSACTION; +SHOW CREATE TRIGGER t1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci +# Connection con1 +ALTER TABLE t1 CHARACTER SET = utf8; +# Connection default +COMMIT; +DROP TRIGGER t1_bi; +DROP TABLE t1; diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index ba6b9f81a2d..33f5c6b5165 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -210,4 +210,16 @@ UPDATE t1,t2 SET t1.a = t2.a; INSERT INTO t2 SELECT f1(); DROP TABLE t1,t2,t3; DROP FUNCTION f1; +# +# Bug #48067: A temp table with the same name as an existing table, +# makes drop database fail. +# +DROP TEMPORARY TABLE IF EXISTS bug48067.t1; +DROP DATABASE IF EXISTS bug48067; +CREATE DATABASE bug48067; +CREATE TABLE bug48067.t1 (c1 int); +INSERT INTO bug48067.t1 values (1); +CREATE TEMPORARY TABLE bug48067.t1 (c1 int); +DROP DATABASE bug48067; +DROP TEMPORARY table bug48067.t1; End of 5.1 tests diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index b6ad1ff31bf..be81afe1a43 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1677,3 +1677,25 @@ SET @@sql_quote_show_create = @sql_quote_show_create_saved; # End of Bug#34828. +# Make sure we can manipulate with autocommit in the +# along with other variables. +drop table if exists t1; +drop function if exists t1_max; +drop function if exists t1_min; +create table t1 (a int) engine=innodb; +insert into t1(a) values (0), (1); +create function t1_max() returns int return (select max(a) from t1); +create function t1_min() returns int return (select min(a) from t1); +select t1_min(); +t1_min() +0 +select t1_max(); +t1_max() +1 +set @@session.autocommit=t1_min(), @@session.autocommit=t1_max(), +@@session.autocommit=t1_min(), @@session.autocommit=t1_max(), +@@session.autocommit=t1_min(), @@session.autocommit=t1_max(); +# Cleanup. +drop table t1; +drop function t1_min; +drop function t1_max; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index e1c1d6f4128..96b45f0d5bb 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1955,15 +1955,15 @@ CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text test.v1 check Error FUNCTION test.f1 does not exist test.v1 check Error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v1 check status Operation failed +test.v1 check error Corrupt test.v2 check status OK test.v3 check Error FUNCTION test.f1 does not exist test.v3 check Error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v3 check status Operation failed +test.v3 check error Corrupt test.v4 check status OK test.v5 check Error FUNCTION test.f1 does not exist test.v5 check Error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v5 check status Operation failed +test.v5 check error Corrupt test.v6 check status OK create function f1 () returns int return (select max(col1) from t1); DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug52199.result b/mysql-test/suite/innodb/r/innodb_bug52199.result new file mode 100644 index 00000000000..7e8c1ee46e0 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug52199.result @@ -0,0 +1,5 @@ +CREATE TABLE bug52199 (a INT NOT NULL, +b CHAR(125) CHARACTER SET utf32 COLLATE utf32_bin NOT NULL +)ENGINE=InnoDB; +CREATE UNIQUE INDEX idx ON bug52199(a); +DROP TABLE bug52199; diff --git a/mysql-test/suite/innodb/r/innodb_bug54679.result b/mysql-test/suite/innodb/r/innodb_bug54679.result new file mode 100644 index 00000000000..948696fb31d --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug54679.result @@ -0,0 +1,88 @@ +SET GLOBAL innodb_file_format='Barracuda'; +SET GLOBAL innodb_file_per_table=ON; +SET innodb_strict_mode=ON; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +bug54679 Compressed row_format=COMPRESSED +ALTER TABLE bug54679 ADD COLUMN b INT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +bug54679 Compressed row_format=COMPRESSED +DROP TABLE bug54679; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +bug54679 Compact +ALTER TABLE bug54679 KEY_BLOCK_SIZE=1; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +bug54679 Compressed KEY_BLOCK_SIZE=1 +ALTER TABLE bug54679 ROW_FORMAT=REDUNDANT; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +DROP TABLE bug54679; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +bug54679 Redundant row_format=REDUNDANT +ALTER TABLE bug54679 KEY_BLOCK_SIZE=2; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; +TABLE_NAME ROW_FORMAT CREATE_OPTIONS +bug54679 Compressed row_format=REDUNDANT KEY_BLOCK_SIZE=2 +SET GLOBAL innodb_file_format=Antelope; +ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +DROP TABLE bug54679; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table 'test.bug54679' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Error 1005 Can't create table 'test.bug54679' (errno: 1478) +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; +SET GLOBAL innodb_file_format=Barracuda; +SET GLOBAL innodb_file_per_table=OFF; +ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table '#sql-temporary' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Error 1005 Can't create table '#sql-temporary' (errno: 1478) +DROP TABLE bug54679; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ERROR HY000: Can't create table 'test.bug54679' (errno: 1478) +SHOW WARNINGS; +Level Code Message +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Error 1005 Can't create table 'test.bug54679' (errno: 1478) +SET GLOBAL innodb_file_per_table=ON; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +DROP TABLE bug54679; diff --git a/mysql-test/suite/innodb/r/innodb_information_schema.result b/mysql-test/suite/innodb/r/innodb_information_schema.result index 1737dab2ff0..13de084bc09 100644 --- a/mysql-test/suite/innodb/r/innodb_information_schema.result +++ b/mysql-test/suite/innodb/r/innodb_information_schema.result @@ -42,21 +42,7 @@ trx_isolation_level varchar(16) NO trx_unique_checks int(1) NO 0 trx_foreign_key_checks int(1) NO 0 trx_last_foreign_key_error varchar(256) YES NULL -trx_apative_hash_latched int(1) NO 0 -trx_adaptive_hash_timeout bigint(21) unsigned NO 0 -trx_operation_state varchar(64) YES NULL -trx_tables_in_use bigint(21) unsigned NO 0 -trx_tables_locked bigint(21) unsigned NO 0 -trx_lock_structs bigint(21) unsigned NO 0 -trx_lock_memory_bytes bigint(21) unsigned NO 0 -trx_rows_locked bigint(21) unsigned NO 0 -trx_rows_modified bigint(21) unsigned NO 0 -trx_concurrency_tickets bigint(21) unsigned NO 0 -trx_isolation_level varchar(16) NO -trx_unique_checks int(1) NO 0 -trx_foreign_key_checks int(1) NO 0 -trx_last_foreign_key_error varchar(256) YES NULL -trx_apative_hash_latched int(1) NO 0 +trx_adaptive_hash_latched int(1) NO 0 trx_adaptive_hash_timeout bigint(21) unsigned NO 0 trx_state trx_weight trx_tables_in_use trx_tables_locked trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks RUNNING 4 0 0 7 1 0 REPEATABLE READ 1 1 diff --git a/mysql-test/suite/innodb/t/innodb_bug52199.test b/mysql-test/suite/innodb/t/innodb_bug52199.test new file mode 100644 index 00000000000..0fec64ba243 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug52199.test @@ -0,0 +1,7 @@ +-- source include/have_innodb.inc + +CREATE TABLE bug52199 (a INT NOT NULL, +b CHAR(125) CHARACTER SET utf32 COLLATE utf32_bin NOT NULL +)ENGINE=InnoDB; +CREATE UNIQUE INDEX idx ON bug52199(a); +DROP TABLE bug52199; diff --git a/mysql-test/suite/innodb/t/innodb_bug54679.test b/mysql-test/suite/innodb/t/innodb_bug54679.test new file mode 100644 index 00000000000..c5e308acb5e --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug54679.test @@ -0,0 +1,101 @@ +# Test Bug #54679 alter table causes compressed row_format to revert to compact + +--source include/have_innodb.inc + +let $file_format=`select @@innodb_file_format`; +let $file_format_max=`select @@innodb_file_format_max`; +let $file_per_table=`select @@innodb_file_per_table`; +SET GLOBAL innodb_file_format='Barracuda'; +SET GLOBAL innodb_file_per_table=ON; +SET innodb_strict_mode=ON; + +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; + +# The ROW_FORMAT of the table should be preserved when it is not specified +# in ALTER TABLE. +ALTER TABLE bug54679 ADD COLUMN b INT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; + +DROP TABLE bug54679; + +# Check that the ROW_FORMAT conversion to/from COMPRESSED works. + +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; + +# KEY_BLOCK_SIZE implies COMPRESSED. +ALTER TABLE bug54679 KEY_BLOCK_SIZE=1; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; + +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE bug54679 ROW_FORMAT=REDUNDANT; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +DROP TABLE bug54679; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; + +ALTER TABLE bug54679 KEY_BLOCK_SIZE=2; +SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables +WHERE TABLE_NAME='bug54679'; + +# This prevents other than REDUNDANT or COMPACT ROW_FORMAT for new tables. +SET GLOBAL innodb_file_format=Antelope; + +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +DROP TABLE bug54679; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB; + +SET GLOBAL innodb_file_format=Barracuda; +# This will prevent ROW_FORMAT=COMPRESSED, because the system tablespace +# cannot be compressed. +SET GLOBAL innodb_file_per_table=OFF; + +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE bug54679 KEY_BLOCK_SIZE=4; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +ALTER TABLE bug54679 ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +DROP TABLE bug54679; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ +SHOW WARNINGS; +SET GLOBAL innodb_file_per_table=ON; +CREATE TABLE bug54679 (a INT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +DROP TABLE bug54679; + +# restore original values, quietly so the test does not fail if those +# defaults are changed +-- disable_query_log +EVAL SET GLOBAL innodb_file_format=$file_format; +EVAL SET GLOBAL innodb_file_format_max=$file_format_max; +EVAL SET GLOBAL innodb_file_per_table=$file_per_table; +-- enable_query_log diff --git a/mysql-test/suite/perfschema/r/server_init.result b/mysql-test/suite/perfschema/r/server_init.result index 70205c7f104..8c35425029e 100644 --- a/mysql-test/suite/perfschema/r/server_init.result +++ b/mysql-test/suite/perfschema/r/server_init.result @@ -40,18 +40,10 @@ where name like "wait/synch/cond/mysys/THR_COND_threads"; count(name) 1 select count(name) from MUTEX_INSTANCES -where name like "wait/synch/mutex/sql/LOCK_mysql_create_db"; -count(name) -1 -select count(name) from MUTEX_INSTANCES where name like "wait/synch/mutex/sql/LOCK_open"; count(name) 1 select count(name) from MUTEX_INSTANCES -where name like "wait/synch/mutex/sql/LOCK_lock_db"; -count(name) -1 -select count(name) from MUTEX_INSTANCES where name like "wait/synch/mutex/sql/LOCK_thread_count"; count(name) 1 diff --git a/mysql-test/suite/perfschema/t/server_init.test b/mysql-test/suite/perfschema/t/server_init.test index b0bbe7b1bae..2e19d2c843a 100644 --- a/mysql-test/suite/perfschema/t/server_init.test +++ b/mysql-test/suite/perfschema/t/server_init.test @@ -69,15 +69,9 @@ select count(name) from COND_INSTANCES # Verify that these global mutexes have been properly initilized in sql select count(name) from MUTEX_INSTANCES - where name like "wait/synch/mutex/sql/LOCK_mysql_create_db"; - -select count(name) from MUTEX_INSTANCES where name like "wait/synch/mutex/sql/LOCK_open"; select count(name) from MUTEX_INSTANCES - where name like "wait/synch/mutex/sql/LOCK_lock_db"; - -select count(name) from MUTEX_INSTANCES where name like "wait/synch/mutex/sql/LOCK_thread_count"; select count(name) from MUTEX_INSTANCES diff --git a/mysql-test/suite/rpl/r/rpl_conditional_comments.result b/mysql-test/suite/rpl/r/rpl_conditional_comments.result new file mode 100644 index 00000000000..f3de3e5eb70 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_conditional_comments.result @@ -0,0 +1,67 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +CREATE TABLE t1(c1 INT); +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; CREATE TABLE t1(c1 INT) + +# Case 1: +# ------------------------------------------------------------------ +# In a statement, some CCs are applied while others are not. The CCs +# which are not applied on master will be binlogged as common comments. +/*!99999 --- */INSERT /*!INTO*/ /*!10000 t1 */ VALUES(10) /*!99999 ,(11)*/; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; /* 99999 --- */INSERT /*!INTO*/ /*!10000 t1 */ VALUES(10) /* 99999 ,(11)*/ +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 + +# Case 2: +# ----------------------------------------------------------------- +# Verify whether it can be binlogged correctly when executing prepared +# statement. +PREPARE stmt FROM 'INSERT INTO /*!99999 blabla*/ t1 VALUES(60) /*!99999 ,(61)*/'; +EXECUTE stmt; +DROP TABLE t1; +CREATE TABLE t1(c1 INT); +EXECUTE stmt; +Comparing tables master:test.t1 and slave:test.t1 + +SET @value=62; +PREPARE stmt FROM 'INSERT INTO /*!99999 blabla */ t1 VALUES(?) /*!99999 ,(63)*/'; +EXECUTE stmt USING @value; +DROP TABLE t1; +CREATE TABLE t1(c1 INT); +EXECUTE stmt USING @value; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO /* 99999 blabla*/ t1 VALUES(60) /* 99999 ,(61)*/ +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # use `test`; DROP TABLE t1 +master-bin.000001 # Query # # use `test`; CREATE TABLE t1(c1 INT) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO /* 99999 blabla*/ t1 VALUES(60) /* 99999 ,(61)*/ +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO /* 99999 blabla */ t1 VALUES(62) /* 99999 ,(63)*/ +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # use `test`; DROP TABLE t1 +master-bin.000001 # Query # # use `test`; CREATE TABLE t1(c1 INT) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO /* 99999 blabla */ t1 VALUES(62) /* 99999 ,(63)*/ +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 + +# Case 3: +# ----------------------------------------------------------------- +# Verify it can restore the '!', if the it is an uncomplete conditional +# comments +SELECT c1 FROM /*!99999 t1 WHEREN; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/*!99999 t1 WHEREN' at line 1 +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_packet.result b/mysql-test/suite/rpl/r/rpl_packet.result index 0a9495751fe..1ec9259a1fb 100644 --- a/mysql-test/suite/rpl/r/rpl_packet.result +++ b/mysql-test/suite/rpl/r/rpl_packet.result @@ -49,6 +49,14 @@ Slave_IO_Running = No (expect No) SELECT "Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'" AS Last_IO_Error; Last_IO_Error Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' +STOP SLAVE; +RESET SLAVE; +RESET MASTER; +SET @max_allowed_packet_0= @@session.max_allowed_packet; +SHOW BINLOG EVENTS; +SET @max_allowed_packet_1= @@session.max_allowed_packet; +SHOW BINLOG EVENTS; +SET @max_allowed_packet_2= @@session.max_allowed_packet; ==== clean up ==== DROP TABLE t1; SET @@global.max_allowed_packet= 1024; diff --git a/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result b/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result index 896ba90b865..459dc83e01d 100644 --- a/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result +++ b/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result @@ -165,10 +165,6 @@ master-bin.000001 # Table_map # # table_id: # (test.tt_1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' -master-bin.000001 # Query # # BEGIN -master-bin.000001 # Table_map # # table_id: # (mysql.user) -master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # COMMIT -e-e-e-e-e-e-e-e-e-e-e- >> << -e-e-e-e-e-e-e-e-e-e-e- -b-b-b-b-b-b-b-b-b-b-b- >> << -b-b-b-b-b-b-b-b-b-b-b- diff --git a/mysql-test/suite/rpl/t/rpl_conditional_comments.test b/mysql-test/suite/rpl/t/rpl_conditional_comments.test new file mode 100644 index 00000000000..14251d5eb37 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_conditional_comments.test @@ -0,0 +1,74 @@ +############################################################################### +# After the patch for BUG#49124: +# - Use ' ' instead of '!' in the conditional comments which are not applied on +# master. So they become common comments and will not be applied on slave. +# +# - Example: +# 'INSERT INTO t1 VALUES (1) /*!10000, (2)*/ /*!99999 ,(3)*/ +# will be binlogged as +# 'INSERT INTO t1 VALUES (1) /*!10000, (2)*/ /* 99999 ,(3)*/'. +############################################################################### +source include/master-slave.inc; +source include/have_binlog_format_statement.inc; + +CREATE TABLE t1(c1 INT); +source include/show_binlog_events.inc; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +--echo +--echo # Case 1: +--echo # ------------------------------------------------------------------ +--echo # In a statement, some CCs are applied while others are not. The CCs +--echo # which are not applied on master will be binlogged as common comments. + +/*!99999 --- */INSERT /*!INTO*/ /*!10000 t1 */ VALUES(10) /*!99999 ,(11)*/; + +source include/show_binlog_events.inc; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +sync_slave_with_master; +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; + +--echo +--echo # Case 2: +--echo # ----------------------------------------------------------------- +--echo # Verify whether it can be binlogged correctly when executing prepared +--echo # statement. +PREPARE stmt FROM 'INSERT INTO /*!99999 blabla*/ t1 VALUES(60) /*!99999 ,(61)*/'; +EXECUTE stmt; +DROP TABLE t1; +CREATE TABLE t1(c1 INT); +EXECUTE stmt; + +sync_slave_with_master; +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; + +--echo +SET @value=62; +PREPARE stmt FROM 'INSERT INTO /*!99999 blabla */ t1 VALUES(?) /*!99999 ,(63)*/'; +EXECUTE stmt USING @value; +DROP TABLE t1; +CREATE TABLE t1(c1 INT); +EXECUTE stmt USING @value; + +source include/show_binlog_events.inc; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +sync_slave_with_master; +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; + +--echo +--echo # Case 3: +--echo # ----------------------------------------------------------------- +--echo # Verify it can restore the '!', if the it is an uncomplete conditional +--echo # comments +--error 1064 +SELECT c1 FROM /*!99999 t1 WHEREN; + +DROP TABLE t1; +source include/master-slave-end.inc; diff --git a/mysql-test/suite/rpl/t/rpl_packet.test b/mysql-test/suite/rpl/t/rpl_packet.test index 61a1ad9d987..38b868cb59d 100644 --- a/mysql-test/suite/rpl/t/rpl_packet.test +++ b/mysql-test/suite/rpl/t/rpl_packet.test @@ -1,7 +1,12 @@ +# ==== Purpose ==== # # Check replication protocol packet size handling -# Bug#19402 SQL close to the size of the max_allowed_packet fails on slave # +# ==== Related bugs ==== +# Bug#19402 SQL close to the size of the max_allowed_packet fails on slave +# BUG#23755: Replicated event larger that max_allowed_packet infinitely re-transmits +# BUG#42914: No LAST_IO_ERROR for max_allowed_packet errors +# BUG#55322: SHOW BINLOG EVENTS increases @@SESSION.MAX_ALLOWED_PACKET # max-out size db name source include/master-slave.inc; @@ -114,6 +119,38 @@ let $slave_io_running= query_get_value(SHOW SLAVE STATUS, Slave_IO_Running, 1); let $last_io_error= query_get_value(SHOW SLAVE STATUS, Last_IO_Error, 1); eval SELECT "$last_io_error" AS Last_IO_Error; +# Remove the bad binlog and clear error status on slave. +STOP SLAVE; +RESET SLAVE; +--connection master +RESET MASTER; + + +# +# BUG#55322: SHOW BINLOG EVENTS increases @@SESSION.MAX_ALLOWED_PACKET +# +# In BUG#55322, @@session.max_allowed_packet increased each time SHOW +# BINLOG EVENTS was issued. To verify that this bug is fixed, we +# execute SHOW BINLOG EVENTS twice and check that max_allowed_packet +# never changes. We turn off the result log because we don't care +# about the contents of the binlog. + +--disable_result_log +SET @max_allowed_packet_0= @@session.max_allowed_packet; +SHOW BINLOG EVENTS; +SET @max_allowed_packet_1= @@session.max_allowed_packet; +SHOW BINLOG EVENTS; +SET @max_allowed_packet_2= @@session.max_allowed_packet; +--enable_result_log +if (`SELECT NOT(@max_allowed_packet_0 = @max_allowed_packet_1 AND @max_allowed_packet_1 = @max_allowed_packet_2)`) +{ + --echo ERROR: max_allowed_packet changed after executing SHOW BINLOG EVENTS + --source include/show_rpl_debug_info.inc + SELECT @max_allowed_packet_0, @max_allowed_packet_1, @max_allowed_packet_2; + --die @max_allowed_packet changed after executing SHOW BINLOG EVENTS +} + + --echo ==== clean up ==== connection master; DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_sync-slave.opt b/mysql-test/suite/rpl/t/rpl_sync-slave.opt index 7d6147ed59a..fba451a5b3e 100644 --- a/mysql-test/suite/rpl/t/rpl_sync-slave.opt +++ b/mysql-test/suite/rpl/t/rpl_sync-slave.opt @@ -1 +1 @@ ---sync-relay-log-info=1 --relay-log-recovery=1 --innodb_file_format_check='ON' --default-storage-engine=MyISAM --innodb-file-per-table=0 +--sync-relay-log-info=1 --relay-log-recovery=1 --innodb_file_format_check=1 --default-storage-engine=MyISAM --innodb-file-per-table=0 diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index d7f7a12cbf8..5b5fdf50c16 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1128,3 +1128,19 @@ INSERT INTO t1 VALUES (1), (2); ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2); DROP TABLE t1; + +--echo # +--echo # Test for bug #53820 "ALTER a MEDIUMINT column table causes full +--echo # table copy". +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (a INT, b MEDIUMINT); +INSERT INTO t1 VALUES (1, 1), (2, 2); +--echo # The below ALTER should not copy table and so no rows should +--echo # be shown as affected. +--enable_info +ALTER TABLE t1 CHANGE a id INT; +--disable_info +DROP TABLE t1; diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index a3665e5f455..c3a080612a9 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1701,3 +1701,24 @@ SELECT * FROM t1; REPAIR TABLE t1 EXTENDED; SELECT * FROM t1; DROP TABLE t1; + + +--echo # +--echo # Bug#45377: ARCHIVE tables aren't discoverable after OPTIMIZE +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a int) ENGINE=ARCHIVE; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (1); +OPTIMIZE TABLE t1; +let $MYSQLD_DATADIR= `select @@datadir`; +remove_file $MYSQLD_DATADIR/test/t1.frm; +FLUSH TABLES; +INSERT INTO t1 VALUES (2); +SELECT * FROM t1 ORDER BY a; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/t/heap_hash.test b/mysql-test/t/heap_hash.test index 1e3491f89a9..748347021fc 100644 --- a/mysql-test/t/heap_hash.test +++ b/mysql-test/t/heap_hash.test @@ -284,3 +284,20 @@ INSERT INTO t1 VALUES('A ', 'A '); DROP TABLE t1; --echo End of 5.0 tests + +--echo # +--echo # Bug #55472: Assertion failed in heap_rfirst function of hp_rfirst.c +--echo # on DELETE statement +--echo # + +CREATE TABLE t1 (col_int_nokey INT, + col_int_key INT, + INDEX(col_int_key) USING HASH) ENGINE = HEAP; +INSERT INTO t1 (col_int_nokey, col_int_key) VALUES (3, 0), (4, 0), (3, 1); + +DELETE FROM t1 WHERE col_int_nokey = 5 ORDER BY col_int_key LIMIT 2; + +DROP TABLE t1; + +--echo End of 5.5 tests + diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test index 36d09b4c411..c8ece729b19 100644 --- a/mysql-test/t/innodb_mysql_lock.test +++ b/mysql-test/t/innodb_mysql_lock.test @@ -209,6 +209,74 @@ disconnect con1; DROP TABLE t1; +--echo # +--echo # Bug#49891 View DDL breaks REPEATABLE READ +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v2; +--enable_warnings + +CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb; +CREATE TABLE t2 ( f1 INTEGER ); +CREATE VIEW v1 AS SELECT 1 FROM t1; + +connect (con2, localhost, root); +connect (con3, localhost, root); + +--echo # Connection con3 +connection con3; +LOCK TABLE t1 WRITE; + +--echo # Connection default +connection default; +START TRANSACTION; +# This should block due to t1 being locked. +--echo # Sending: +--send SELECT * FROM v1 + +--echo # Connection con2 +connection con2; +--echo # Waiting for 'SELECT * FROM v1' to sync in. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table" AND info = "SELECT * FROM v1"; +--source include/wait_condition.inc +# This should block due to v1 being locked. +--echo # Sending: +--send ALTER VIEW v1 AS SELECT 2 FROM t2 + +--echo # Connection con3 +connection con3; +--echo # Waiting for 'ALTER VIEW v1 AS SELECT 2 FROM t2' to sync in. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table" AND info = "ALTER VIEW v1 AS SELECT 2 FROM t2"; +--source include/wait_condition.inc +# Unlock t1 allowing SELECT * FROM v1 to proceed. +UNLOCK TABLES; + +--echo # Connection default; +connection default; +--echo # Reaping: SELECT * FROM v1 +--reap +SELECT * FROM v1; +COMMIT; + +--echo # Connection con2 +connection con2; +--echo # Reaping: ALTER VIEW v1 AS SELECT 2 FROM t2 +--reap + +--echo # Connection default +connection default; +DROP TABLE t1, t2; +DROP VIEW v1; +disconnect con2; +disconnect con3; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/innodb_mysql_sync.test b/mysql-test/t/innodb_mysql_sync.test index ee92ee9f52e..07f75afec40 100644 --- a/mysql-test/t/innodb_mysql_sync.test +++ b/mysql-test/t/innodb_mysql_sync.test @@ -80,6 +80,32 @@ disconnect con1; SET DEBUG_SYNC= "RESET"; +--echo # +--echo # Bug#53757 assert in mysql_truncate_by_delete +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1(a INT) Engine=InnoDB; +CREATE TABLE t2(id INT); +INSERT INTO t1 VALUES (1), (2); + +connect (con1, localhost, root); +INSERT INTO t2 VALUES(connection_id()); +SET DEBUG_SYNC= "open_and_process_table SIGNAL opening WAIT_FOR killed"; +--echo # Sending: (not reaped since connection is killed later) +--send TRUNCATE t1 + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR opening"; +SELECT ((@id := id) - id) FROM t2; +KILL @id; +SET DEBUG_SYNC= "now SIGNAL killed"; +DROP TABLE t1, t2; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test index 19f9b396087..6b721ace07f 100644 --- a/mysql-test/t/mdl_sync.test +++ b/mysql-test/t/mdl_sync.test @@ -3705,6 +3705,481 @@ DROP TABLE t1; disconnect con1; +--echo # +--echo # Tests for schema-scope locks +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +--enable_warnings + +connect (con2, localhost, root); +connect (con3, localhost, root); + +--echo # Test 1: +--echo # CREATE DATABASE blocks database DDL on the same database, but +--echo # not database DDL on different databases. Tests X vs X lock. +--echo # + +--echo # Connection default +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send CREATE DATABASE db1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send CREATE DATABASE db1 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='CREATE DATABASE db1'; +--source include/wait_condition.inc +# This should not block. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: CREATE DATABASE db1 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: CREATE DATABASE db1 +--error ER_DB_CREATE_EXISTS +--reap + +--echo # Test 2: +--echo # ALTER DATABASE blocks database DDL on the same database, but +--echo # not database DDL on different databases. Tests X vs X lock. +--echo # + +--echo # Connection default +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' + AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; +--source include/wait_condition.inc +# This should not block. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +--echo # Connection default +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should also block. +--send DROP DATABASE db1 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='DROP DATABASE db1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: DROP DATABASE db1 +--reap +# Recreate the database +CREATE DATABASE db1; + +--echo # Test 3: +--echo # Two ALTER..UPGRADE of the same database are mutually exclusive, but +--echo # two ALTER..UPGRADE of different databases are not. Tests X vs X lock. +--echo # + +let $MYSQLD_DATADIR= `select @@datadir`; +# Manually make a 5.0 database from the template +--mkdir $MYSQLD_DATADIR/a-b-c +--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c/db.opt +--mkdir $MYSQLD_DATADIR/a-b-c-d +--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c-d/db.opt + +--echo # Connection default +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' + AND info='ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME'; +--source include/wait_condition.inc +# This should not block. +ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME +--error ER_BAD_DB_ERROR +--reap +DROP DATABASE `a-b-c`; +DROP DATABASE `a-b-c-d`; + +--echo # Test 4: +--echo # DROP DATABASE blocks database DDL on the same database, but +--echo # not database DDL on different databases. Tests X vs X lock. +--echo # + +--echo # Connection default +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send DROP DATABASE db1 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='DROP DATABASE db1'; +--source include/wait_condition.inc +# This should not block. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: DROP DATABASE db1 +--error ER_DB_DROP_EXISTS +--reap + +--echo # Connection default +connection default; +CREATE DATABASE db1; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should also block. +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' + AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--error 1,1 # Wrong error pending followup patch for bug#54360 +--reap + + +--echo # Test 5: +--echo # Locked database name prevents CREATE of tables in that database. +--echo # Tests X vs IX lock. +--echo # + +--echo # Connection default +connection default; +CREATE DATABASE db1; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send CREATE TABLE db1.t1 (a INT) + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='CREATE TABLE db1.t1 (a INT)'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: CREATE TABLE db1.t1 (a INT) +--error ER_BAD_DB_ERROR +--reap + +--echo # Test 6: +--echo # Locked database name prevents RENAME of tables to/from that database. +--echo # Tests X vs IX lock. +--echo # + +--echo # Connection default +connection default; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send RENAME TABLE db1.t1 TO test.t1 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='RENAME TABLE db1.t1 TO test.t1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: RENAME TABLE db1.t1 TO test.t1 +--error ER_FILE_NOT_FOUND, ER_FILE_NOT_FOUND +--reap + +--echo # Connection default +connection default; +CREATE DATABASE db1; +CREATE TABLE test.t2 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send RENAME TABLE test.t2 TO db1.t2 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='RENAME TABLE test.t2 TO db1.t2'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: RENAME TABLE test.t2 TO db1.t2 +--error 7, 7 # Wrong error pending followup patch for bug#54360 +--reap +DROP TABLE test.t2; + + +--echo # Test 7: +--echo # Locked database name prevents DROP of tables in that database. +--echo # Tests X vs IX lock. +--echo # + +--echo # Connection default +connection default; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send DROP TABLE db1.t1 + +--echo # Connection con3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='DROP TABLE db1.t1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: DROP TABLE db1.t1 +--error ER_BAD_TABLE_ERROR +--reap + +--echo # Connection default +connection default; +disconnect con2; +disconnect con3; +SET DEBUG_SYNC= 'RESET'; + +--echo # +--echo # End of tests for schema-scope locks +--echo # + +--echo # +--echo # Tests of granted global S lock (FLUSH TABLE WITH READ LOCK) +--echo # + +CREATE DATABASE db1; +CREATE TABLE db1.t1(a INT); +connect(con2, localhost, root); +connect(con3, localhost, root); + +--echo # Connection default +connection default; +FLUSH TABLE WITH READ LOCK; + +--echo # Connection con2 +connection con2; +# IX global lock should block +--send CREATE TABLE db1.t2(a INT) + +--echo # Connection default +connection default; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for release of readlock' + AND info='CREATE TABLE db1.t2(a INT)'; +--source include/wait_condition.inc +UNLOCK TABLES; + +--echo # Connection con2 +connection con2; +--echo # Reaping CREATE TABLE db1.t2(a INT) +--reap + +--echo # Connection default +connection default; +FLUSH TABLE WITH READ LOCK; + +--echo # Connection con2 +connection con2; +# X global lock should block +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +--echo # Connection default +connection default; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for release of readlock' + AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; +--source include/wait_condition.inc +UNLOCK TABLES; + +--echo # Connection con2 +connection con2; +--echo # Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +--echo # Connection default +connection default; +FLUSH TABLE WITH READ LOCK; + +--echo # Connection con2 +connection con2; +# S global lock should not block +FLUSH TABLE WITH READ LOCK; +UNLOCK TABLES; + +--echo # Connection default +connection default; +UNLOCK TABLES; +DROP DATABASE db1; +disconnect con2; +disconnect con3; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/merge-big.test b/mysql-test/t/merge-big.test index 33bd93791f1..509c7742dac 100644 --- a/mysql-test/t/merge-big.test +++ b/mysql-test/t/merge-big.test @@ -51,7 +51,7 @@ connection default; #--sleep 8 #SELECT ID,STATE,INFO FROM INFORMATION_SCHEMA.PROCESSLIST; let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST - WHERE ID = $con1_id AND STATE = 'Table lock'; + WHERE ID = $con1_id AND STATE = 'Waiting for table'; --source include/wait_condition.inc #SELECT NOW(); --echo # Kick INSERT out of thr_multi_lock(). @@ -61,7 +61,7 @@ FLUSH TABLES; #--sleep 8 #SELECT ID,STATE,INFO FROM INFORMATION_SCHEMA.PROCESSLIST; let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST - WHERE ID = $con1_id AND STATE = 'Table lock'; + WHERE ID = $con1_id AND STATE = 'Waiting for table'; --source include/wait_condition.inc #SELECT NOW(); --echo # Unlock and close table and wait for con1 to close too. diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index a06da03cbcd..31bc8a5e881 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -2,6 +2,12 @@ # Test of MERGE TABLES # +# MERGE tables require MyISAM tables +let $default=`select @@global.storage_engine`; +set global storage_engine=myisam; +set session storage_engine=myisam; + +# Clean up resources used in this test case. --disable_warnings drop table if exists t1,t2,t3,t4,t5,t6; drop database if exists mysqltest; @@ -222,7 +228,6 @@ CREATE TABLE t2 (c1 INT NOT NULL); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); CREATE TEMPORARY TABLE t3 (c1 INT NOT NULL) ENGINE=MRG_MYISAM UNION=(t1,t2); ---error ER_WRONG_MRG_TABLE SELECT * FROM t3; CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL); CREATE TEMPORARY TABLE t5 (c1 INT NOT NULL); @@ -254,7 +259,6 @@ create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); select * from t3; drop table t3; create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); ---error ER_WRONG_MRG_TABLE select * from t3; drop table t3, t2, t1; --echo # CREATE...SELECT is not implemented for MERGE tables. @@ -891,12 +895,9 @@ INSERT INTO t4 VALUES (4); --echo # If the temporary MERGE table uses the locked children only, --echo # it can even be used. CREATE TEMPORARY TABLE t4 LIKE t3; ---error ER_WRONG_MRG_TABLE SHOW CREATE TABLE t4; ---error ER_WRONG_MRG_TABLE INSERT INTO t4 VALUES (4); UNLOCK TABLES; ---error ER_WRONG_MRG_TABLE INSERT INTO t4 VALUES (4); DROP TABLE t4; # @@ -2117,6 +2118,325 @@ OPTIMIZE TABLE t1; DROP TABLE t1; --echo # +--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine +--echo # More tests with TEMPORARY MERGE table and permanent children. +--echo # First without locked tables. +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2; +--enable_warnings +# +--echo # +CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +INSERT INTO t1 VALUES (111, 121); +INSERT INTO m1 VALUES (211, 221); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +ALTER TABLE m1 RENAME m2; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +# +--echo # +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +--error ER_TABLE_EXISTS_ERROR +ALTER TABLE m2 RENAME m1; +DROP TABLE m1; +ALTER TABLE m2 RENAME m1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ADD COLUMN c3 INT; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (212, 222, 232); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 ADD COLUMN c3 INT; +ALTER TABLE t2 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 DROP COLUMN c3; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (213, 223); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 DROP COLUMN c3; +ALTER TABLE t2 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +SELECT * FROM m1; +# +--echo # +CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3); +INSERT INTO m1 VALUES (311, 321); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +# +--echo # +CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3,t4); +INSERT INTO m1 VALUES (411, 421); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +SELECT * FROM t4; +# +--echo # +ALTER TABLE m1 ENGINE=MyISAM; +SHOW CREATE TABLE m1; +INSERT INTO m1 VALUES (511, 521); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (611, 621); +SELECT * FROM m1; +DROP TABLE t1; +SELECT * FROM m1; +# +# +--echo # +--echo # +SHOW CREATE TABLE m1; +# +--echo # +CREATE TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +--error ER_WRONG_MRG_TABLE +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +--error ER_WRONG_OBJECT +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +--error ER_WRONG_OBJECT +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +CREATE TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +# +# +--echo # +--echo # +LOCK TABLE m1 WRITE, m2 WRITE; +SELECT * FROM m1,m2 WHERE m1.c1=m2.c1; +UNLOCK TABLES; +# +DROP TABLE t1, t2, t3, t4, m1, m2; +# +# +# +--echo # +--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine +--echo # More tests with TEMPORARY MERGE table and permanent children. +--echo # (continued) Now the same with locked table. +--echo # +CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +INSERT INTO t1 VALUES (111, 121); +INSERT INTO m1 VALUES (211, 221); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE; +# +--echo # +ALTER TABLE m1 RENAME m2; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +# +--echo # +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +--error ER_TABLE_EXISTS_ERROR +ALTER TABLE m2 RENAME m1; +DROP TABLE m1; +ALTER TABLE m2 RENAME m1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ADD COLUMN c3 INT; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (212, 222, 232); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 ADD COLUMN c3 INT; +ALTER TABLE t2 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 DROP COLUMN c3; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (213, 223); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 DROP COLUMN c3; +ALTER TABLE t2 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +SELECT * FROM m1; +# +--echo # +UNLOCK TABLES; +CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3); +LOCK TABLE m1 WRITE; +INSERT INTO m1 VALUES (311, 321); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +# +--echo # +CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3,t4); +INSERT INTO m1 VALUES (411, 421); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +SELECT * FROM t4; +# +--echo # +ALTER TABLE m1 ENGINE=MyISAM; +SHOW CREATE TABLE m1; +INSERT INTO m1 VALUES (511, 521); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (611, 621); +SELECT * FROM m1; +DROP TABLE t1; +SELECT * FROM m1; +# +# +--echo # +--echo # +SHOW CREATE TABLE m1; +--error ER_TABLE_NOT_LOCKED +CREATE TABLE m2 SELECT * FROM m1; +# +--echo # +CREATE TEMPORARY TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +SELECT * FROM m2; +LOCK TABLE m1 WRITE, m2 WRITE; +UNLOCK TABLES; +DROP TABLE m2; +LOCK TABLE m1 WRITE; +# +--echo # +--echo # ER_TABLE_NOT_LOCKED is returned in ps-protocol +--error ER_WRONG_OBJECT, ER_TABLE_NOT_LOCKED +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +--error ER_WRONG_OBJECT +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +CREATE TEMPORARY TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +LOCK TABLE m1 WRITE, m2 WRITE; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +# +--echo # +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4, m1, m2; + +--echo # --echo # Bug47098 assert in MDL_context::destroy on HANDLER --echo # <damaged merge table> OPEN --echo # @@ -2225,6 +2545,130 @@ show create table m1; show create table m2; drop tables m1, m2, t1; +--echo # +--echo # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()" +--echo # Check that unique_table() works correctly for merge tables. +--echo # +--disable_warnings +drop table if exists t1, t2, t3, m1, m2; +--enable_warnings +create table t1 (a int); +create table t2 (a int); +create table t3 (b int); +create view v1 as select * from t3,t1; +create table m1 (a int) engine=merge union (t1, t2) insert_method=last; +create table m2 (a int) engine=merge union (t1, t2) insert_method=first; +create temporary table tmp (b int); +insert into tmp (b) values (1); + +insert into t1 (a) values (1); +insert into t3 (b) values (1); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t2)); + +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, t2)); + +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, t2)); + +--error ER_VIEW_PREVENT_UPDATE +insert into m1 (a) values ((select max(a) from v1)); +--error ER_VIEW_PREVENT_UPDATE +insert into m1 (a) values ((select max(a) from tmp, v1)); + + +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from m1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from m2)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t2)); + +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, m1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, m2)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, t1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from t3, t2)); + +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, m1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, m2)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, t1)); +--error ER_UPDATE_TABLE_USED +update m1 set a = ((select max(a) from tmp, t2)); + +--error ER_VIEW_PREVENT_UPDATE +update m1 set a = ((select max(a) from v1)); +--error ER_VIEW_PREVENT_UPDATE +update m1 set a = ((select max(a) from tmp, v1)); + + +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from m1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from m2); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t2); + +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, m1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, m2); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, t1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from t3, t2); + +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, m1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, m2); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, t1); +--error ER_UPDATE_TABLE_USED +delete from m1 where a = (select max(a) from tmp, t2); + +--error ER_VIEW_PREVENT_UPDATE +delete from m1 where a = (select max(a) from v1); +--error ER_VIEW_PREVENT_UPDATE +delete from m1 where a = (select max(a) from tmp, v1); + +drop view v1; +drop temporary table tmp; +drop table t1, t2, t3, m1, m2; --echo End of 6.0 tests +--disable_result_log +--disable_query_log +eval set global storage_engine=$default; +--enable_result_log +--enable_query_log diff --git a/mysql-test/t/merge_mmap-master.opt b/mysql-test/t/merge_mmap-master.opt new file mode 100644 index 00000000000..9606fb57187 --- /dev/null +++ b/mysql-test/t/merge_mmap-master.opt @@ -0,0 +1 @@ +--myisam-use-mmap diff --git a/mysql-test/t/merge_mmap.test b/mysql-test/t/merge_mmap.test new file mode 100644 index 00000000000..c97b029754d --- /dev/null +++ b/mysql-test/t/merge_mmap.test @@ -0,0 +1,151 @@ +# +# Test of MERGE TABLES with MyISAM memory mapping enabled (--myisam-use-mmap) +# + +# MERGE tables require MyISAM tables +--let $default=`SELECT @@global.storage_engine` +SET GLOBAL storage_engine = MyISAM; +SET SESSION storage_engine = MyISAM; + +# Clean up resources used in this test case. +--disable_warnings +DROP TABLE IF EXISTS t1, t2, m1, m2; +--enable_warnings + +#################### +## No locked tables. +#################### +# +# INSERT-SELECT with no TEMPORARY table. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +SELECT * FROM t2; +DROP TABLE m2, m1, t2, t1; +# +# INSERT-SELECT with TEMPORARY select table. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2, m1, t2, t1; +# +# INSERT-SELECT with TEMPORARY insert table. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2, m1, t2, t1; +# +# INSERT-SELECT with TEMPORARY both tables. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2, m1, t2, t1; + +#################### +## With LOCK TABLES. +#################### +# +# INSERT-SELECT with no TEMPORARY table. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +SELECT * FROM t2; +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; +# +# INSERT-SELECT with TEMPORARY select table. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; +# +# INSERT-SELECT with TEMPORARY insert table. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; +# +# INSERT-SELECT with TEMPORARY both tables. +# +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2), (3), (4); +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +UNLOCK TABLES; +DROP TABLE m2, m1, t2, t1; + +--echo End of 6.0 tests + +--disable_result_log +--disable_query_log +eval SET GLOBAL storage_engine = $default; +--enable_result_log +--enable_query_log diff --git a/mysql-test/t/partition_debug_sync.test b/mysql-test/t/partition_debug_sync.test index 9165006f537..cde94856ae6 100644 --- a/mysql-test/t/partition_debug_sync.test +++ b/mysql-test/t/partition_debug_sync.test @@ -65,7 +65,7 @@ ENGINE = MYISAM PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (100), PARTITION p3 VALUES LESS THAN MAXVALUE ) */; -SET DEBUG_SYNC= 'open_tables_acquire_upgradable_mdl SIGNAL removing_partitions WAIT_FOR waiting_for_alter'; +SET DEBUG_SYNC= 'alter_table_before_open_tables SIGNAL removing_partitions WAIT_FOR waiting_for_alter'; SET DEBUG_SYNC= 'alter_table_before_rename_result_table WAIT_FOR delete_done'; --send ALTER TABLE t2 REMOVE PARTITIONING connection default; diff --git a/mysql-test/t/schema.test b/mysql-test/t/schema.test index f106b9e4865..ed3b98ec2f7 100644 --- a/mysql-test/t/schema.test +++ b/mysql-test/t/schema.test @@ -23,6 +23,7 @@ drop schema foo; --disable_warnings DROP SCHEMA IF EXISTS schema1; +DROP SCHEMA IF EXISTS schema2; --enable_warnings connect(con2, localhost, root); @@ -31,6 +32,7 @@ connect(con2, localhost, root); connection default; CREATE SCHEMA schema1; +CREATE SCHEMA schema2; CREATE TABLE schema1.t1 (a INT); SET autocommit= FALSE; @@ -46,9 +48,7 @@ let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist WHERE state= 'Waiting for table' AND info='DROP SCHEMA schema1'; --source include/wait_condition.inc -# Listing the error twice to prevent result diffences based on filename ---error 1,1 -ALTER SCHEMA schema1 DEFAULT CHARACTER SET utf8; +ALTER SCHEMA schema2 DEFAULT CHARACTER SET utf8; SET autocommit= TRUE; --echo # Connection 2 @@ -57,6 +57,7 @@ connection con2; --echo # Connection default connection default; +DROP SCHEMA schema2; disconnect con2; @@ -102,6 +103,98 @@ connection con2; connection default; disconnect con2; + +--echo # +--echo # Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER +--echo # + +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +INSERT INTO db1.t1 VALUES (1), (2); + +--echo # Connection con1 +connect (con1, localhost, root); +HANDLER db1.t1 OPEN; + +--echo # Connection default +connection default; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connect (con2, localhost, root); +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='DROP DATABASE db1'; +--source include/wait_condition.inc + +--echo # Connection con1 +connection con1; +# All these statements before resulted in deadlock. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +HANDLER t1 CLOSE; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap +disconnect con1; +disconnect con2; + + +--echo # +--echo # Tests for increased CREATE/ALTER/DROP DATABASE concurrency with +--echo # database name locks. +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +--enable_warnings + +connect (con2, localhost, root); +connect (con3, localhost, root); + +--echo # Connection default +connection default; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (id INT); +START TRANSACTION; +INSERT INTO db1.t1 VALUES (1); + +--echo # Connection 2 +connection con2; +--echo # DROP DATABASE should block due to the active transaction +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection 3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' and info='DROP DATABASE db1'; +--source include/wait_condition.inc +--echo # But it should still be possible to CREATE/ALTER/DROP other databases. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; + +--echo # Connection default +connection default; +--echo # End the transaction so DROP DATABASE db1 can continue +COMMIT; + +--echo # Connection 2 +connection con2; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection default; +connection default; +disconnect con2; +disconnect con3; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 3821701ad19..fa9dc7472fe 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -1246,6 +1246,85 @@ UNLOCK TABLES; DROP TABLE t1; +--echo # +--echo # Bug#54905 Connection with WRITE lock cannot ALTER table due to +--echo # concurrent SHOW CREATE +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT); + +--echo # Connection con1 +connect (con1,localhost,root); +LOCK TABLE t1 WRITE; + +--echo # Connection default +connection default; +START TRANSACTION; +SHOW CREATE TABLE t1; + +--echo # Connection con1 +connection con1; +# Used to block +ALTER TABLE t1 CHARACTER SET = utf8; +UNLOCK TABLES; + +--echo # Connection default +connection default; +COMMIT; +disconnect con1; +DROP TABLE t1; + + +--echo # +--echo # Bug#55498 SHOW CREATE TRIGGER takes wrong type of metadata lock. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1; + +--echo # Test 1: SHOW CREATE TRIGGER with WRITE locked table. + +--echo # Connection con1 +connect (con1, localhost, root); +LOCK TABLE t1 WRITE; + +--echo # Connection default +connection default; +# Should not block. +SHOW CREATE TRIGGER t1_bi; + +--echo # Connection con1 +connection con1; +UNLOCK TABLES; + +--echo # Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction + +--echo # Connection default +connection default; +START TRANSACTION; +SHOW CREATE TRIGGER t1_bi; + +--echo # Connection con1 +connection con1; +# Should not block. +ALTER TABLE t1 CHARACTER SET = utf8; + +--echo # Connection default +connection default; +COMMIT; +DROP TRIGGER t1_bi; +DROP TABLE t1; +disconnect con1; + + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index 2bfa4936c91..92c22242cdb 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -235,4 +235,19 @@ INSERT INTO t2 SELECT f1(); DROP TABLE t1,t2,t3; DROP FUNCTION f1; +--echo # +--echo # Bug #48067: A temp table with the same name as an existing table, +--echo # makes drop database fail. +--echo # +--disable_warnings +DROP TEMPORARY TABLE IF EXISTS bug48067.t1; +DROP DATABASE IF EXISTS bug48067; +--enable_warnings +CREATE DATABASE bug48067; +CREATE TABLE bug48067.t1 (c1 int); +INSERT INTO bug48067.t1 values (1); +CREATE TEMPORARY TABLE bug48067.t1 (c1 int); +DROP DATABASE bug48067; +DROP TEMPORARY table bug48067.t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index d865851841f..75099523062 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1405,4 +1405,30 @@ SET @@sql_quote_show_create = @sql_quote_show_create_saved; --echo # End of Bug#34828. --echo +--echo # Make sure we can manipulate with autocommit in the +--echo # along with other variables. + + +--disable_warnings +drop table if exists t1; +drop function if exists t1_max; +drop function if exists t1_min; +--enable_warnings + +create table t1 (a int) engine=innodb; +insert into t1(a) values (0), (1); +create function t1_max() returns int return (select max(a) from t1); +create function t1_min() returns int return (select min(a) from t1); +select t1_min(); +select t1_max(); +set @@session.autocommit=t1_min(), @@session.autocommit=t1_max(), + @@session.autocommit=t1_min(), @@session.autocommit=t1_max(), + @@session.autocommit=t1_min(), @@session.autocommit=t1_max(); + +--echo # Cleanup. +drop table t1; +drop function t1_min; +drop function t1_max; + + ########################################################################### |