diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2013-07-21 16:39:19 +0200 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2013-07-21 16:39:19 +0200 |
| commit | b7b5f6f1ab49948b0e15b762266d4640b3d6b7fb (patch) | |
| tree | 7c302c2025184dbd053aa6135f0ff28c8ce6f359 /mysql-test/t | |
| parent | 5f6380adde2dac3f32b40339b9b702c0135eb7d6 (diff) | |
| parent | c1d6a2d7e194225ccc19a68ea5d0f368632620d0 (diff) | |
| download | mariadb-git-b7b5f6f1ab49948b0e15b762266d4640b3d6b7fb.tar.gz | |
10.0-monty merge
includes:
* remove some remnants of "Bug#14521864: MYSQL 5.1 TO 5.5 BUGS PARTITIONING"
* introduce LOCK_share, now LOCK_ha_data is strictly for engines
* rea_create_table() always creates .par file (even in "frm-only" mode)
* fix a 5.6 bug, temp file leak on dummy ALTER TABLE
Diffstat (limited to 'mysql-test/t')
29 files changed, 2288 insertions, 184 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index d48b1687fa0..f40f8c11fac 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1,3 +1,4 @@ +--source include/have_innodb.inc # # Test of alter table # @@ -1215,6 +1216,24 @@ ALTER TABLE db1.t1 ADD baz INT; DROP DATABASE db1; +--echo # Additional coverage for refactoring which is made as part +--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +--echo # to allow temp table operations". +--echo # +--echo # At some point the below test case failed on assertion. + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM; + +--error ER_ILLEGAL_HA +ALTER TABLE t1 DISCARD TABLESPACE; + +DROP TABLE t1; + + --echo # --echo # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME --echo # CLAUSE FAILS OR ABORTS SERVER. @@ -1258,3 +1277,360 @@ CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); SHOW CREATE TABLE t1; DROP TABLE t1; +--echo # +--echo # Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT) engine=innodb; +INSERT INTO t1 VALUES (1), (2); + +--enable_info +--echo # This should not do anything +ALTER TABLE t1; +--echo # Check that we rebuild the table +ALTER TABLE t1 engine=innodb; +--echo # This should also rebuild the table +ALTER TABLE t1 FORCE; +--disable_info + +DROP TABLE t1; + +--echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't +--echo # identify correct column name. +--echo # + +CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default ''); +ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2, + MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # WL#5534 Online ALTER, Phase 1 +--echo # + +--echo # Single thread tests. +--echo # See innodb_mysql_sync.test for multi thread tests. + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; +CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +INSERT INTO m1 VALUES (1,1), (2,2); + +--echo # +--echo # 1: Test ALGORITHM keyword +--echo # + +--echo # --enable_info allows us to see how many rows were updated +--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. + +--enable_info +ALTER TABLE t1 ADD INDEX i1(b); +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +--error ER_UNKNOWN_ALTER_ALGORITHM +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID; + +ALTER TABLE m1 ENABLE KEYS; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE; +--disable_info + +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; + +--echo # +--echo # 2: Test ALGORITHM + old_alter_table +--echo # + +--enable_info +SET SESSION old_alter_table= 1; +ALTER TABLE t1 ADD INDEX i1(b); +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +SET SESSION old_alter_table= 0; +--disable_info + +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; + +--echo # +--echo # 3: Test unsupported in-place operation +--echo # + +ALTER TABLE t1 ADD COLUMN (c1 INT); +ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT; +ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY; +ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE; + +ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4; + +--echo # +--echo # 4: Test LOCK keyword +--echo # + +--enable_info +ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT; +ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE; +ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED; +ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE; +--error ER_UNKNOWN_ALTER_LOCK +ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID; +--disable_info + +ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, LOCK= NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED; +ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE; + +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; + +--echo # +--echo # 5: Test ALGORITHM + LOCK +--echo # + +--enable_info +ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; +ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; +# This works because the lock will be SNW for the copy phase. +# It will still require exclusive lock for actually enabling keys. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE; +--disable_info + +DROP TABLE t1, m1; + +--echo # +--echo # 6: Possible deadlock involving thr_lock.c +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); + +START TRANSACTION; +INSERT INTO t1 VALUES (3,3); + +--echo # Connection con1 +connect (con1, localhost, root); +--echo # Sending: +--send ALTER TABLE t1 DISABLE KEYS + +--echo # Connection default +connection default; +--echo # Waiting until ALTER TABLE is blocked. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "ALTER TABLE t1 DISABLE KEYS"; +--source include/wait_condition.inc +UPDATE t1 SET b = 4; +COMMIT; + +--echo # Connection con1 +connection con1; +--echo # Reaping: ALTER TABLE t1 DISABLE KEYS +--reap +disconnect con1; +--source include/wait_until_disconnected.inc + +--echo # Connection default +connection default; +DROP TABLE t1; + +--echo # +--echo # 7: Which operations require copy and which can be done in-place? +--echo # +--echo # Test which ALTER TABLE operations are done in-place and +--echo # which operations are done using temporary table copy. +--echo # +--echo # --enable_info allows us to see how many rows were updated +--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3; +--enable_warnings + +--echo # Single operation tests + +CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB; +CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM; +CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB; +CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM; +INSERT INTO ti1 VALUES (1,1,1), (2,2,2); +INSERT INTO ti2 VALUES (1,1,1), (2,2,2); +INSERT INTO tm1 VALUES (1,1,1), (2,2,2); +INSERT INTO tm2 VALUES (1,1,1), (2,2,2); + +--enable_info +ALTER TABLE ti1; +ALTER TABLE tm1; + +ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); +ALTER TABLE tm1 ADD COLUMN d VARCHAR(200); +ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200); +ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200); +ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST; +ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST; +ALTER TABLE ti1 ADD COLUMN f INT AFTER a; +ALTER TABLE tm1 ADD COLUMN f INT AFTER a; + +ALTER TABLE ti1 ADD INDEX ii1(b); +ALTER TABLE tm1 ADD INDEX im1(b); +ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c); +ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c); +ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); +ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); +ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2); +ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2); + +# Bug#14140038 INCONSISTENT HANDLING OF FULLTEXT INDEXES IN ALTER TABLE +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; +ALTER TABLE ti1 ADD PRIMARY KEY(a); +ALTER TABLE tm1 ADD PRIMARY KEY(a); + +ALTER TABLE ti1 DROP INDEX ii3; +ALTER TABLE tm1 DROP INDEX im3; + +ALTER TABLE ti1 DROP COLUMN d2; +ALTER TABLE tm1 DROP COLUMN d2; + +ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a); +ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a); + +ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1; +ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1; +ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT; +ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT; + +# This will set both ALTER_COLUMN_NAME and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 CHANGE COLUMN f g INT; +ALTER TABLE tm1 CHANGE COLUMN f g INT; +ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20); +ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20); +ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +ALTER TABLE ti1 MODIFY COLUMN e INT; +ALTER TABLE tm1 MODIFY COLUMN e INT; +# This will set both ALTER_COLUMN_ORDER and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h; +ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h; +ALTER TABLE ti1 MODIFY COLUMN e INT FIRST; +ALTER TABLE tm1 MODIFY COLUMN e INT FIRST; +# This will set both ALTER_COLUMN_NOT_NULLABLE and COLUMN_DEFAULT_VALUE +--disable_info +# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. +SET @orig_sql_mode = @@sql_mode; +SET @@sql_mode = 'STRICT_TRANS_TABLES'; +--enable_info +ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL; +--disable_info +SET @@sql_mode = @orig_sql_mode; +--enable_info +ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL; +# This will set both ALTER_COLUMN_NULLABLE and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 MODIFY COLUMN c INT NULL; +ALTER TABLE tm1 MODIFY COLUMN c INT NULL; +# This will set both ALTER_COLUMN_EQUAL_PACK_LENGTH and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30); +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30); +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d; +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d; + +ALTER TABLE ti1 DROP COLUMN h; +ALTER TABLE tm1 DROP COLUMN h; + +ALTER TABLE ti1 DROP INDEX ii2; +ALTER TABLE tm1 DROP INDEX im2; +ALTER TABLE ti1 DROP PRIMARY KEY; +ALTER TABLE tm1 DROP PRIMARY KEY; + +ALTER TABLE ti1 DROP FOREIGN KEY fi1; +ALTER TABLE tm1 DROP FOREIGN KEY fm1; + +ALTER TABLE ti1 RENAME TO ti3; +ALTER TABLE tm1 RENAME TO tm3; +ALTER TABLE ti3 RENAME TO ti1; +ALTER TABLE tm3 RENAME TO tm1; + +ALTER TABLE ti1 ORDER BY b; +ALTER TABLE tm1 ORDER BY b; + +ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16; +ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16; +ALTER TABLE ti1 DEFAULT CHARACTER SET utf8; +ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; + +ALTER TABLE ti1 FORCE; +ALTER TABLE tm1 FORCE; + +ALTER TABLE ti1 AUTO_INCREMENT 3; +ALTER TABLE tm1 AUTO_INCREMENT 3; +ALTER TABLE ti1 AVG_ROW_LENGTH 10; +ALTER TABLE tm1 AVG_ROW_LENGTH 10; +ALTER TABLE ti1 CHECKSUM 1; +ALTER TABLE tm1 CHECKSUM 1; +ALTER TABLE ti1 COMMENT 'test'; +ALTER TABLE tm1 COMMENT 'test'; +ALTER TABLE ti1 MAX_ROWS 100; +ALTER TABLE tm1 MAX_ROWS 100; +ALTER TABLE ti1 MIN_ROWS 1; +ALTER TABLE tm1 MIN_ROWS 1; +ALTER TABLE ti1 PACK_KEYS 1; +ALTER TABLE tm1 PACK_KEYS 1; + +--disable_info +DROP TABLE ti1, ti2, tm1, tm2; + +--echo # Tests of >1 operation (InnoDB) + +CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB; +INSERT INTO ti1(b) VALUES (1), (2); + +--enable_info +ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b); + +ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5; +--disable_info +INSERT INTO ti3(b) VALUES (5); +--enable_info +ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7; +--disable_info +INSERT INTO ti3(b) VALUES (7); +SELECT * FROM ti3; + +DROP TABLE ti3; + +--echo # +--echo # 8: Scenario in which ALTER TABLE was returning an unwarranted +--echo # ER_ILLEGAL_HA error at some point during work on this WL. +--echo # + +CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM; +ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT; +DROP TABLE tm1; diff --git a/mysql-test/t/alter_table_online.test b/mysql-test/t/alter_table_online.test index 19096efe0fa..a9ce77d1445 100644 --- a/mysql-test/t/alter_table_online.test +++ b/mysql-test/t/alter_table_online.test @@ -1,5 +1,5 @@ # -# Test of alter online table +# Test of ALTER ONLINE TABLE syntax # --source include/have_innodb.inc @@ -29,15 +29,15 @@ drop table t1; create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify b int default 5; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 change b new_name int; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify e enum('a','b','c'); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 comment "new comment"; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 rename to t2; drop table t1; @@ -49,52 +49,50 @@ drop table t1; create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 drop column b, add b int; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify b bigint; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify e enum('c','a','b'); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify c varchar(50); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify c varchar(100); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 add f int; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 engine=memory; alter table t1 engine=innodb; alter table t1 add index (b); ---error ER_CANT_DO_ONLINE alter online table t1 add index c (c); ---error ER_CANT_DO_ONLINE alter online table t1 drop index b; drop table t1; create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 drop column b, add b int; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify b bigint; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify e enum('c','a','b'); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify c varchar(50); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 modify c varchar(100); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 add f int; ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 engine=memory; alter table t1 engine=innodb; alter table t1 add index (b); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 add index c (c); ---error ER_CANT_DO_ONLINE +--error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 drop index b; drop table t1; diff --git a/mysql-test/t/ctype_utf8mb4.test b/mysql-test/t/ctype_utf8mb4.test index fda20ca0ec5..e4067245622 100644 --- a/mysql-test/t/ctype_utf8mb4.test +++ b/mysql-test/t/ctype_utf8mb4.test @@ -1812,8 +1812,7 @@ SHOW CREATE TABLE t2; DROP TABLE t1, t2; --echo # ---echo # Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH ---echo # LONGTEXT, UNION, USER VARIABLE +--echo # Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH LONGTEXT, UNION, USER VARIABLE --echo # Bug#14096619 UNABLE TO RESTORE DATABASE DUMP --echo # diff --git a/mysql-test/t/events_restart.test b/mysql-test/t/events_restart.test index 83d28c0812d..c6152e5d961 100644 --- a/mysql-test/t/events_restart.test +++ b/mysql-test/t/events_restart.test @@ -107,3 +107,25 @@ let $wait_condition= where db='events_test' and command = 'Connect' and user=current_user(); --source include/wait_condition.inc +--echo # +--echo # Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION +--echo # NOT PRESERVE IS DELETED AT SERVER +--echo # +SELECT @@event_scheduler; +USE test; +--disable_warnings +DROP EVENT IF EXISTS e1; +--enable_warnings +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1; +--replace_column 6 # 9 # 10 # +SHOW EVENTS; + +--echo "Now we restart the server" +--source include/restart_mysqld.inc +USE test; +SELECT @@event_scheduler; +--replace_column 6 # 9 # 10 # +SHOW EVENTS; +DROP EVENT e1; + +--echo # end test for bug#11748899 diff --git a/mysql-test/t/flush_read_lock.test b/mysql-test/t/flush_read_lock.test index f024bff0af7..e8ec07392b5 100644 --- a/mysql-test/t/flush_read_lock.test +++ b/mysql-test/t/flush_read_lock.test @@ -708,11 +708,10 @@ let $cleanup_stmt1= create table t2_base(j int); --source include/check_ftwrl_incompatible.inc --echo # 13.1.b) DROP TABLES which affects only temporary tables ---echo # in theory can be compatible with FTWRL. ---echo # In practice it is not yet. +--echo # is compatible with FTWRL. let $statement= drop table t2_temp; -let $cleanup_stmt1= create temporary table t2_temp(j int); ---source include/check_ftwrl_incompatible.inc +let $cleanup_stmt= create temporary table t2_temp(j int); +--source include/check_ftwrl_compatible.inc --echo # --echo # 13.1.c) DROP TEMPORARY TABLES should be compatible with FTWRL. @@ -1902,35 +1901,10 @@ let $statement= analyze table t3_temp_trans; let $cleanup_stmt= ; --source include/check_ftwrl_compatible.inc --echo # ---echo # 39.2.c) Some statements do implicit commit and not ---echo # considered read-only. As result they are ---echo # not compatible with FTWRL. ---echo # -flush tables with read lock; ---echo # Implicit commits are allowed under FTWRL. -alter table t3_temp_trans add column c1 int; -unlock tables; ---echo # ---echo # Switching to connection '$con_aux1'. -connection $con_aux1; -flush tables with read lock; ---echo # Switching to connection 'default'. -connection default; ---send alter table t3_temp_trans drop column c1 ---echo # Switching to connection '$con_aux1'. -connection $con_aux1; ---echo # Check that ALTER TABLE is blocked. -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for commit lock" and - info = "alter table t3_temp_trans drop column c1"; ---source include/wait_condition.inc -unlock tables; ---echo # Switching to connection 'default'. -connection default; ---echo # Reap ALTER TABLE ---reap - +--echo # And ALTER TABLE: +let $statement= alter table t3_temp_trans add column c1 int; +let $cleanup_stmt= alter table t3_temp_trans drop column c1; +--source include/check_ftwrl_compatible.inc --echo # --echo # 40) Test effect of implicit commit for DDL which is otherwise diff --git a/mysql-test/t/innodb_mysql_sync.test b/mysql-test/t/innodb_mysql_sync.test index 2f3bd643837..b1e21837404 100644 --- a/mysql-test/t/innodb_mysql_sync.test +++ b/mysql-test/t/innodb_mysql_sync.test @@ -168,7 +168,7 @@ connection default; CREATE DATABASE db1; CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb; INSERT INTO db1.t1(value) VALUES (1), (2); -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; --echo # Sending: --send ALTER TABLE db1.t1 ADD INDEX(value) @@ -186,26 +186,27 @@ connection default; --reap DROP DATABASE db1; ---echo # Test 2: Primary index (implicit), should block reads. +--echo # Test 2: Primary index (implicit), should block writes. CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; --echo # Sending: ---send ALTER TABLE t1 ADD UNIQUE INDEX(a) +--send ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED --echo # Connection con1 connection con1; SET DEBUG_SYNC= "now WAIT_FOR manage"; USE test; +SELECT * FROM t1; --echo # Sending: ---send SELECT * FROM t1 +--send UPDATE t1 SET a=NULL --echo # Connection con2 connection con2; --echo # Waiting for SELECT to be blocked by the metadata lock on t1 let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist WHERE state= 'Waiting for table metadata lock' - AND info='SELECT * FROM t1'; + AND info='UPDATE t1 SET a=NULL'; --source include/wait_condition.inc SET DEBUG_SYNC= "now SIGNAL query"; @@ -216,30 +217,31 @@ connection default; --echo # Connection con1 connection con1; ---echo # Reaping: SELECT * FROM t1 +--echo # Reaping: UPDATE t1 SET a=NULL --reap ---echo # Test 3: Primary index (explicit), should block reads. +--echo # Test 3: Primary index (explicit), should block writes. --echo # Connection default connection default; ALTER TABLE t1 DROP INDEX a; -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; --echo # Sending: ---send ALTER TABLE t1 ADD PRIMARY KEY (a) +--send ALTER TABLE t1 ADD PRIMARY KEY (a), LOCK=SHARED --echo # Connection con1 connection con1; SET DEBUG_SYNC= "now WAIT_FOR manage"; +SELECT * FROM t1; --echo # Sending: ---send SELECT * FROM t1 +--send UPDATE t1 SET a=NULL --echo # Connection con2 connection con2; --echo # Waiting for SELECT to be blocked by the metadata lock on t1 let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist WHERE state= 'Waiting for table metadata lock' - AND info='SELECT * FROM t1'; + AND info='UPDATE t1 SET a=NULL'; --source include/wait_condition.inc SET DEBUG_SYNC= "now SIGNAL query"; @@ -250,14 +252,14 @@ connection default; --echo # Connection con1 connection con1; ---echo # Reaping: SELECT * FROM t1 +--echo # Reaping: UPDATE t1 SET a=NULL --reap --echo # Test 4: Secondary unique index, should not block reads. --echo # Connection default connection default; -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; --echo # Sending: --send ALTER TABLE t1 ADD UNIQUE (b) @@ -304,6 +306,292 @@ DROP TABLE t1; disconnect con1; +--echo # +--echo # Bug#13417754 ASSERT IN ROW_DROP_DATABASE_FOR_MYSQL DURING DROP SCHEMA +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP DATABASE IF EXISTS db1; +--enable_warnings + +CREATE TABLE t1(a int) engine=InnoDB; +CREATE DATABASE db1; + +connect(con1, localhost, root); +connect(con2, localhost, root); + +--echo # Connection con1 +connection con1; +SET DEBUG_SYNC= 'after_innobase_rename_table SIGNAL locked WAIT_FOR continue'; +--echo # Sending: +--send ALTER TABLE t1 RENAME db1.t1 + +--echo # Connection con2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # DROP DATABASE db1 should now be blocked by ALTER TABLE +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection default +connection default; +--echo # Check that DROP DATABASE is blocked by IX lock on db1 +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for schema metadata lock" and + info = "DROP DATABASE db1"; +--source include/wait_condition.inc +--echo # Resume ALTER TABLE +SET DEBUG_SYNC= 'now SIGNAL continue'; + +--echo # Connection con1 +connection con1; +--echo # Reaping: ALTER TABLE t1 RENAME db1.t1; +--reap + +--echo # Connection con2 +connection con2; +--echo # Reaping: DROP DATABASE db1 +--reap + +--echo # Connection default; +connection default; +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +disconnect con2; + + +--echo # +--echo # WL#5534 Online ALTER, Phase 1 +--echo # + +--echo # Multi thread tests. +--echo # See alter_table.test for single thread tests. + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SET DEBUG_SYNC= 'RESET'; +connect (con1, localhost, root); +SET SESSION lock_wait_timeout= 1; + +--echo # +--echo # 1: In-place + writes blocked. +--echo # + +--echo # Connection default +--connection default +SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; +SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; +--echo # Sending: +--send ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= SHARED + +--echo # Connection con1; +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +--echo # At this point, neither reads nor writes should be blocked. +SELECT * FROM t1; +INSERT INTO t1 VALUES (3,3); + +SET DEBUG_SYNC= 'now SIGNAL continue1'; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +--echo # Now both reads and writes should be blocked +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (4,4); + +SET DEBUG_SYNC= 'now SIGNAL continue2'; +SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; +--echo # Still both reads and writes should be blocked. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (5,5); + +SET DEBUG_SYNC= 'now SIGNAL continue3'; +SET DEBUG_SYNC= 'now WAIT_FOR binlog'; +--echo # Same here. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (6,6); + +SET DEBUG_SYNC= 'now SIGNAL continue4'; +--echo # Connection default +--connection default +--echo # Reaping ALTER TABLE ... +--reap +SET DEBUG_SYNC= 'RESET'; +DELETE FROM t1 WHERE a= 3; + +--echo # +--echo # 2: Copy + writes blocked. +--echo # + +SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; +SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue3'; +--echo # Sending: +--send ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= COPY, LOCK= SHARED + +--echo # Connection con1; +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +--echo # At this point, neither reads nor writes should be blocked. +SELECT * FROM t1; +INSERT INTO t1 VALUES (3,3); + +SET DEBUG_SYNC= 'now SIGNAL continue1'; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +--echo # Now writes should be blocked, reads still allowed. +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (4,4); + +SET DEBUG_SYNC= 'now SIGNAL continue2'; +SET DEBUG_SYNC= 'now WAIT_FOR binlog'; +--echo # Now both reads and writes should be blocked. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1 limit 1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (5,5); + +SET DEBUG_SYNC= 'now SIGNAL continue3'; +--echo # Connection default +--connection default +--echo # Reaping ALTER TABLE ... +--reap +SET DEBUG_SYNC= 'RESET'; +DELETE FROM t1 WHERE a= 3; + +--echo # +--echo # 3: In-place + writes allowed. +--echo # + +--echo # TODO: Enable this test once WL#5526 is pushed +--disable_parsing + +--echo # Connection default +--connection default +SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue3'; +SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue4'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue5'; +--echo # Sending: +--send ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= NONE + +--echo # Connection con1; +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +--echo # At this point, neither reads nor writes should be blocked. +SELECT * FROM t1; +INSERT INTO t1 VALUES (3,3); + +SET DEBUG_SYNC= 'now SIGNAL continue1'; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +--echo # Now writes should be blocked, reads still allowed. +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (4,4); + +SET DEBUG_SYNC= 'now SIGNAL continue2'; +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +--echo # Now writes should be allowed again. +SELECT * FROM t1; +INSERT INTO t1 VALUES (5,5); + +SET DEBUG_SYNC= 'now SIGNAL continue3'; +SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; +--echo # Now both reads and writes should be blocked. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (6,6); + +SET DEBUG_SYNC= 'now SIGNAL continue4'; +SET DEBUG_SYNC= 'now WAIT_FOR binlog'; +--echo # Same here. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (7,7); + +SET DEBUG_SYNC= 'now SIGNAL continue5'; +--echo # Connection default +--connection default +--echo # Reaping ALTER TABLE ... +--reap +SET DEBUG_SYNC= 'RESET'; +DELETE FROM t1 WHERE a= 3 OR a= 4; + +--echo # TODO: Enable this test once WL#5526 is pushed +--enable_parsing + +--echo # +--echo # 4: In-place + reads and writes blocked. +--echo # + +--echo # Connection default +--connection default +SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; +SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; +--echo # Sending: +--send ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE + +--echo # Connection con1; +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +--echo # At this point, neither reads nor writes should be blocked. +SELECT * FROM t1; +INSERT INTO t1 VALUES (3,3); + +SET DEBUG_SYNC= 'now SIGNAL continue1'; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +--echo # Now both reads and writes should be blocked. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (4,4); + +SET DEBUG_SYNC= 'now SIGNAL continue2'; +SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; +--echo # Same here. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (5,5); + +SET DEBUG_SYNC= 'now SIGNAL continue3'; +SET DEBUG_SYNC= 'now WAIT_FOR binlog'; +--echo # Same here. +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (6,6); + +SET DEBUG_SYNC= 'now SIGNAL continue4'; +--echo # Connection default +--connection default +--echo # Reaping ALTER TABLE ... +--reap +SET DEBUG_SYNC= 'RESET'; + +--connection default +--disconnect con1 +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; + + # 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/log_state.test b/mysql-test/t/log_state.test index e8f0bf8c511..0b900b14b0b 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -48,7 +48,7 @@ connection con1; set @long_query_time = 2; set session long_query_time = @long_query_time; select sleep(@long_query_time + 1); ---replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME +--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; --echo # Switch to connection default connection default; @@ -58,7 +58,7 @@ set global slow_query_log= ON; connection con1; set session long_query_time = @long_query_time; select sleep(@long_query_time + 1); ---replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME +--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; --echo # Switch to connection default connection default; diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index eb652946672..0b9932c2c4d 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -180,7 +180,7 @@ drop table bug16905; truncate table mysql.slow_log; set session long_query_time=1; select sleep(2); ---replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME +--replace_column 1 TIMESTAMP 2 USER_HOST 3 QUERY_TIME 12 THREAD_ID select * from mysql.slow_log; set @@session.long_query_time = @saved_long_query_time; @@ -290,10 +290,10 @@ drop table mysql.slow_log; use mysql; CREATE TABLE `general_log` ( - `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP + `event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` BIGINT(21) UNSIGNED NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -311,7 +311,8 @@ CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` BIGINT(21) UNSIGNED NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; set global general_log='ON'; @@ -746,6 +747,7 @@ BEGIN DECLARE start_time, query_time, lock_time CHAR(28); DECLARE user_host MEDIUMTEXT; DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT; + DECLARE thread_id BIGINT UNSIGNED; DECLARE dbname MEDIUMTEXT; DECLARE sql_text BLOB; DECLARE done INT DEFAULT 0; @@ -763,7 +765,7 @@ BEGIN FETCH cur1 INTO start_time, user_host, query_time, lock_time, rows_set, rows_examined, dbname, last_insert_id, - insert_id, server_id, sql_text; + insert_id, server_id, sql_text, thread_id; END; IF NOT done THEN @@ -771,7 +773,7 @@ BEGIN INSERT INTO `db_17876.slow_log_data` VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined, - dbname, last_insert_id, insert_id, server_id, sql_text); + dbname, last_insert_id, insert_id, server_id, sql_text, thread_id); END; END IF; END; diff --git a/mysql-test/t/lowercase_table4.test b/mysql-test/t/lowercase_table4.test index 3758ab62214..d13b1a16be1 100644 --- a/mysql-test/t/lowercase_table4.test +++ b/mysql-test/t/lowercase_table4.test @@ -72,7 +72,7 @@ CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB;
ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1);
query_vertical SHOW CREATE TABLE `Table2`;
-query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
+query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test';
DROP TABLE `Table2`;
DROP TABLE `Table1`;
@@ -101,7 +101,7 @@ CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT, query_vertical SHOW CREATE TABLE Product_Order;
query_vertical SHOW CREATE TABLE Product;
query_vertical SHOW CREATE TABLE Customer;
-query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
+query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test';
DROP TABLE Product_Order;
DROP TABLE Product;
DROP TABLE Customer;
diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test index fef8a9848ca..57eea6c4fc1 100644 --- a/mysql-test/t/mdl_sync.test +++ b/mysql-test/t/mdl_sync.test @@ -38,7 +38,7 @@ lock tables t2 read; connection con1; --echo connection: con1 -set debug_sync='mdl_upgrade_shared_lock_to_exclusive SIGNAL parked WAIT_FOR go'; +set debug_sync='mdl_upgrade_lock SIGNAL parked WAIT_FOR go'; --send alter table t1 rename t3 connection default; @@ -110,8 +110,13 @@ select column_name from information_schema.columns where table_schema='test' and table_name='t1'; select count(*) from t1; insert into t1 values (1), (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXITS +alter table t1 add index (not_exist); --echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE ---echo # which will fail after opening the table and thus obtaining SNW metadata +--echo # which will fail during copying the table and thus obtaining SNW metadata --echo # lock. --error ER_DUP_ENTRY alter table t1 add primary key (c1); @@ -230,8 +235,13 @@ select column_name from information_schema.columns where table_schema='test' and table_name='t1'; select count(*) from t1; insert into t1 values (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXITS +alter table t1 add index (not_exist); --echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE ---echo # which will fail after opening the table and thus obtaining SNW metadata +--echo # which will fail during copying the table and thus obtaining SNW metadata --echo # lock. --error ER_DUP_ENTRY alter table t1 add primary key (c1); @@ -359,8 +369,13 @@ select column_name from information_schema.columns where table_schema='test' and table_name='t1'; select count(*) from t1; insert into t1 values (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXITS +alter table t1 add index (not_exist); --echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE ---echo # which will fail after opening the table and thus obtaining SNW metadata +--echo # which will fail during copying the table and thus obtaining SNW metadata --echo # lock. --error ER_DUP_ENTRY alter table t1 add primary key (c1); @@ -477,8 +492,13 @@ select column_name from information_schema.columns where select * from t1; --enable_result_log insert into t1 values (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXITS +alter table t1 add index (not_exist); --echo # Check that SNW lock is not compatible with SW lock. ---echo # Again we use ALTER TABLE which fails after opening +--echo # Again we use ALTER TABLE which fails during copying --echo # the table to avoid upgrade of SNW -> X. --echo # Sending: --send alter table t1 add primary key (c1); @@ -570,16 +590,144 @@ rename table t2 to t1; connection default; --echo # --echo # ---echo # 5) Acquire SNW lock on the table. We have to use DEBUG_SYNC for ---echo # this, to prevent SNW from being immediately upgraded to X. +--echo # 5) Acquire SU lock on the table. We have to use DEBUG_SYNC for +--echo # this, to prevent SU from being immediately upgraded to X. --echo # -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +delete from t1 limit 1; +--echo # Check that SU lock is incompatible with SU lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above ALTER is blocked because of SU lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTERs. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping first ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping another ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; --echo # Sending: --send alter table t1 add primary key (c1); --echo # --echo # Switching to connection 'mdl_con1'. connection mdl_con1; set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that SNRW lock is incompatible with SU lock. +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above LOCK TABLES is blocked because of SU lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus LOCK TABLES. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping LOCK TABLES +--reap +insert into t1 values (1); +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that X lock is incompatible with SU lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SU lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Now we have ALTER TABLE with SU->SNW and RENAME TABLE with pending +--echo # X-lock. In this case ALTER TABLE should be chosen as victim. +--echo # Reaping ALTER TABLE. +--error ER_LOCK_DEADLOCK +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME TABLE +--reap +--echo # Revert back to original state of things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of another SU lock because SNW/SNRW is +--echo # incompatible with SU anyway. +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # +--echo # 6) Acquire SNW lock on the table. We have to use DEBUG_SYNC for +--echo # this, to prevent SNW from being immediately upgraded to X. +--echo # +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; --echo # Check that S, SH and SR locks are compatible with it. handler t1 open; handler t1 close; @@ -614,14 +762,14 @@ connection mdl_con1; --echo # --echo # Switching to connection 'default'. connection default; -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; --echo # Sending: ---send alter table t1 add primary key (c1); +--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; --echo # --echo # Switching to connection 'mdl_con1'. connection mdl_con1; set debug_sync= 'now WAIT_FOR locked'; ---echo # Check that SNW lock is incompatible with SNW lock. +--echo # Check that SU lock is incompatible with SNW lock. --echo # Sending: --send alter table t1 add primary key (c1); --echo # @@ -648,11 +796,15 @@ connection mdl_con1; --error ER_DUP_ENTRY --reap --echo # +--echo # Note that we can't easily check SNW vs SNW locks since +--echo # SNW is only used by ALTER TABLE after upgrading from SU +--echo # and SU is also incompatible with SNW. +--echo # --echo # Switching to connection 'default'. connection default; -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; --echo # Sending: ---send alter table t1 add primary key (c1); +--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; --echo # --echo # Switching to connection 'mdl_con1'. connection mdl_con1; @@ -687,9 +839,9 @@ unlock tables; --echo # --echo # Switching to connection 'default'. connection default; -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; --echo # Sending: ---send alter table t1 add primary key (c1); +--send alter table t1 add primary key (c1), algorithm=copy, lock=shared; --echo # --echo # Switching to connection 'mdl_con1'. connection mdl_con1; @@ -730,7 +882,7 @@ rename table t2 to t1; connection default; --echo # --echo # ---echo # 6) Acquire SNRW lock on the table. +--echo # 7) Acquire SNRW lock on the table. --echo # --echo # lock table t1 write; @@ -794,13 +946,13 @@ lock table t1 write; --echo # --echo # Switching to connection 'mdl_con1'. connection mdl_con1; ---echo # Check that SNW lock is incompatible with SNRW lock. +--echo # Check that SU lock is incompatible with SNRW lock. --echo # Sending: --send alter table t1 add primary key (c1); --echo # --echo # Switching to connection 'default'. connection default; ---echo # Check that the above ALTER is blocked because of UNWR lock. +--echo # Check that the above ALTER is blocked because of SNRW lock. let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table metadata lock" and @@ -815,6 +967,10 @@ connection mdl_con1; --error ER_DUP_ENTRY --reap --echo # +--echo # Note that we can't easily check SNW vs SNRW locks since +--echo # SNW is only used by ALTER TABLE after upgrading from SU +--echo # and SU is also incompatible with SNRW. +--echo # --echo # Switching to connection 'default'. connection default; lock table t1 write; @@ -879,7 +1035,7 @@ rename table t2 to t1; connection default; --echo # --echo # ---echo # 7) Now do the same round of tests for X lock. We use additional +--echo # 8) Now do the same round of tests for X lock. We use additional --echo # table to get long-lived lock of this type. --echo # create table t2 (c1 int); @@ -1083,7 +1239,7 @@ select count(*) = 1 from information_schema.processlist where state = "Waiting for table metadata lock" and info = "rename table t1 to t2"; --source include/wait_condition.inc ---echo # Check that SNW lock is incompatible with X lock. +--echo # Check that SU lock is incompatible with X lock. --echo # Sending: --send alter table t1 add primary key (c1); --echo # @@ -1110,7 +1266,11 @@ connection mdl_con1; --echo # Reaping ALTER. --error ER_DUP_ENTRY --reap ---echo # +--echo # +--echo # Note that we can't easily check SNW vs X locks since +--echo # SNW is only used by ALTER TABLE after upgrading from SU +--echo # and SU is also incompatible with X. +--echo # --echo # Switching to connection 'mdl_con2'. connection mdl_con2; --echo # Prepare for blocking RENAME TABLE. @@ -1208,6 +1368,9 @@ rename table t3 to t1; --echo # are pending. I.e. let us test rules for priorities between --echo # different types of metadata locks. --echo # +--echo # Note: No tests for pending SU lock as this lock requires +--echo # even stronger active or pending lock. +--echo # --echo # --echo # Switching to connection 'mdl_con2'. @@ -1657,6 +1820,9 @@ connection default; --echo # transactional context. Obviously we are mostly interested --echo # in conflicting types of locks. --echo # +--echo # Note: No tests for active/pending SU lock since +--echo # ALTER TABLE is in its own transaction. +--echo # --echo # --echo # 1) Let us check how various locks used within transactional @@ -1673,9 +1839,9 @@ connection mdl_con1; --echo # We have to use DEBUG_SYNC facility as otherwise SNW lock --echo # will be immediately released (or upgraded to X lock). insert into t2 values (1), (1); -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; --echo # Sending: ---send alter table t2 add primary key (c1); +--send alter table t2 add primary key (c1), algorithm=copy, lock=shared; --echo # --echo # Switching to connection 'default'. connection default; @@ -1724,9 +1890,9 @@ select count(*) from t1; --echo # Switching to connection 'mdl_con1'. connection mdl_con1; --echo # Create an active SNW lock on t1. -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; --echo # Sending: ---send alter table t1 add primary key (c1); +--send alter table t1 add primary key (c1), algorithm=copy, lock=shared; --echo # --echo # Switching to connection 'default'. connection default; @@ -2845,7 +3011,7 @@ drop tables t1, t2; create table t1 (i int); --echo # Ensure that ALTER waits once it has acquired SNW lock. -set debug_sync='after_open_table_mdl_shared SIGNAL parked1 WAIT_FOR go1'; +set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL parked1 WAIT_FOR go1'; --echo # Sending: --send alter table t1 add column j int @@ -3345,18 +3511,33 @@ drop tables if exists t1, t2; --enable_warnings connect (con46044, localhost, root,,); connect (con46044_2, localhost, root,,); +connect (con46044_3, localhost, root,,); connection default; create table t1 (i int); +insert into t1 values(1); --echo # Let us check that we won't deadlock if during filling --echo # of I_S table we encounter conflicting metadata lock --echo # which owner is in its turn waiting for our connection. lock tables t1 read; +--echo # Switching to connection 'con46044_2'. +connection con46044_2; +--echo # Sending: +--send update t1 set i = 2 + --echo # Switching to connection 'con46044'. connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 2"; +--source include/wait_condition.inc + --echo # Sending: ---send create table t2 select * from t1 for update; +--send create table t2 select * from t1; --echo # Switching to connection 'default'. connection default; @@ -3364,7 +3545,7 @@ connection default; let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table level lock" and - info = "create table t2 select * from t1 for update"; + info = "create table t2 select * from t1"; --source include/wait_condition.inc --echo # First let us check that SHOW FIELDS/DESCRIBE doesn't @@ -3395,6 +3576,10 @@ connection con46044; --reap drop table t2; +connection con46044_2; +--echo # Reaping UPDATE t1 statement +--reap + --echo # --echo # Let us also check that queries to I_S wait for conflicting metadata --echo # locks to go away instead of skipping table with a warning in cases @@ -3407,10 +3592,23 @@ drop table t2; connection con46044_2; lock tables t1 read; +--echo # Switching to connection 'con46044_3'. +connection con46044_3; +--echo # Sending: +send update t1 set i = 3; + --echo # Switching to connection 'con46044'. connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 3"; +--source include/wait_condition.inc + --echo # Sending: ---send create table t2 select * from t1 for update; +--send create table t2 select * from t1; --echo # Switching to connection 'default'. connection default; @@ -3418,7 +3616,7 @@ connection default; let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table level lock" and - info = "create table t2 select * from t1 for update"; + info = "create table t2 select * from t1"; --source include/wait_condition.inc --echo # Let us check that SHOW FIELDS/DESCRIBE gets blocked. @@ -3447,14 +3645,31 @@ connection default; --reap drop table t2; +connection con46044_3; +--echo # Reaping UPDATE t1 statement +--reap + --echo # Switching to connection 'con46044_2'. connection con46044_2; lock tables t1 read; +--echo # Switching to connection 'con46044_3'. +connection con46044_3; +--echo # Sending: +--send update t1 set i = 4 + --echo # Switching to connection 'con46044'. connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 4"; +--source include/wait_condition.inc + --echo # Sending: ---send create table t2 select * from t1 for update; +--send create table t2 select * from t1; --echo # Switching to connection 'default'. connection default; @@ -3462,7 +3677,7 @@ connection default; let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table level lock" and - info = "create table t2 select * from t1 for update"; + info = "create table t2 select * from t1"; --source include/wait_condition.inc --echo # Check that I_S query which reads only .FRMs gets blocked. @@ -3491,14 +3706,31 @@ connection default; --reap drop table t2; +connection con46044_3; +--echo # Reaping UPDATE t1 statement +--reap + --echo # Switching to connection 'con46044_2'. connection con46044_2; lock tables t1 read; +--echo # Switching to connection 'con46044_3'. +connection con46044_3; +--echo # Sending: +--send update t1 set i = 5 + --echo # Switching to connection 'con46044'. connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 5"; +--source include/wait_condition.inc + --echo # Sending: ---send create table t2 select * from t1 for update; +--send create table t2 select * from t1; --echo # Switching to connection 'default'. connection default; @@ -3506,7 +3738,7 @@ connection default; let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Waiting for table level lock" and - info = "create table t2 select * from t1 for update"; + info = "create table t2 select * from t1"; --source include/wait_condition.inc --echo # Finally, check that I_S query which does full-blown table open @@ -3536,11 +3768,16 @@ connection default; --reap drop table t2; +connection con46044_3; +--echo # Reaping UPDATE t1 statement +--reap + --echo # Switching to connection 'default'. connection default; --echo # Clean-up. disconnect con46044; disconnect con46044_2; +disconnect con46044_3; drop table t1; @@ -3563,7 +3800,7 @@ select * from t1 where c2 = 3; --echo # --echo # Switching to connection 'con46273'. connection con46273; -set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR alter_go'; +set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL alter_table_locked WAIT_FOR alter_go'; --send alter table t1 add column e int, rename to t2; --echo # @@ -3826,9 +4063,9 @@ create table t1 (i int) engine=InnoDB; --echo # Switching to connection 'con50913_1'. connection con50913_1; -set debug_sync= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; --echo # Sending: ---send alter table t1 add column j int +--send alter table t1 add column j int, ALGORITHM=COPY --echo # Switching to connection 'default'. connection default; @@ -3897,7 +4134,7 @@ select * from t1; connection default; --echo # Start ALTER TABLE which will acquire SNW lock and --echo # table lock and get blocked on sync point. -set debug_sync= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; --echo # Sending: --send alter table t1 add column j int @@ -4562,7 +4799,9 @@ connect(con2, localhost, root); --echo # Connection con1 connection con1; -SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue'; +--echo # We need EXECUTE 2 since ALTER TABLE does SU => SNW => X and we want +--echo # to stop at the second upgrade. +SET DEBUG_SYNC= 'mdl_upgrade_lock SIGNAL upgrade WAIT_FOR continue EXECUTE 2'; --echo # Sending: --send ALTER TABLE m1 engine=MERGE UNION=(t2, t1) @@ -4570,6 +4809,8 @@ SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR co connection con2; --echo # Waiting for ALTER TABLE to try lock upgrade SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; +SET DEBUG_SYNC= 'now SIGNAL continue'; +SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; --echo # Sending: --send DELETE FROM t2 WHERE a = 3 diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index bad59ff09c3..713b3ed7347 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -89,6 +89,16 @@ AND A.c = 343; DROP TABLE t1; --echo # +--echo # Bug#59503: explain extended crash in get_mm_leaf +--echo # +CREATE TABLE t1 (a VARCHAR(51) CHARACTER SET latin1) +ENGINE=MyISAM +PARTITION BY KEY (a) PARTITIONS 1; +INSERT INTO t1 VALUES ('a'),('b'),('c'); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a > 1; +DROP TABLE t1; + +--echo # --echo # Bug#57778: failed primary key add to partitioned innodb table --echo # inconsistent and crashes --echo # @@ -303,6 +313,31 @@ DROP TABLE t1; # # Bug#35765: ALTER TABLE produces wrong error when non-existent storage engine # used +SET sql_mode=no_engine_substitution; +--error ER_UNKNOWN_STORAGE_ENGINE +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine; +--error ER_UNKNOWN_STORAGE_ENGINE +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine +PARTITION BY HASH (a); +CREATE TABLE t1 (a INT) +ENGINE=Memory; +--error ER_UNKNOWN_STORAGE_ENGINE +ALTER TABLE t1 ENGINE=NonExistentEngine; +# OK to only specify one partitions engine, since it is already assigned at +# table level (after create, it is specified on all levels and all parts). +--error ER_UNKNOWN_STORAGE_ENGINE +ALTER TABLE t1 +PARTITION BY HASH (a) +(PARTITION p0 ENGINE=Memory, + PARTITION p1 ENGINE=NonExistentEngine); +--error ER_UNKNOWN_STORAGE_ENGINE +ALTER TABLE t1 ENGINE=NonExistentEngine; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +SET sql_mode=''; CREATE TABLE t1 (a INT) ENGINE=NonExistentEngine; DROP TABLE t1; @@ -322,6 +357,7 @@ PARTITION BY HASH (a) ALTER TABLE t1 ENGINE=NonExistentEngine; SHOW CREATE TABLE t1; DROP TABLE t1; +SET sql_mode=DEFAULT; # # Bug#40494: Crash MYSQL server crashes on range access with partitioning @@ -990,13 +1026,13 @@ drop table t1; # # Bug #16775: Wrong engine type stored for subpartition # -set session storage_engine= 'memory'; +set session default_storage_engine= 'memory'; create table t1 (f_int1 int(11) default null) engine = memory partition by range (f_int1) subpartition by hash (f_int1) (partition part1 values less than (1000) (subpartition subpart11 engine = memory)); drop table t1; -set session storage_engine='myisam'; +set session default_storage_engine='myisam'; # # Bug #16782: Crash using REPLACE on table with primary key @@ -1840,8 +1876,7 @@ WHERE t1.id IN ( SELECT distinct id FROM t4 WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) -ORDER BY t1.id -; +ORDER BY t1.id; drop table t1, t2, t4; diff --git a/mysql-test/t/partition_binlog.test b/mysql-test/t/partition_binlog.test index 9869be75759..d6986c86ebe 100644 --- a/mysql-test/t/partition_binlog.test +++ b/mysql-test/t/partition_binlog.test @@ -20,7 +20,7 @@ INSERT INTO t1 VALUES (1), (10), (100), (1000); --let $binlog_file=query_get_value(SHOW MASTER STATUS, File, 1) --let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) ---error ER_WRONG_PARTITION_NAME +--error ER_UNKNOWN_PARTITION ALTER TABLE t1 TRUNCATE PARTITION p1; --error ER_DROP_PARTITION_NON_EXISTENT ALTER TABLE t1 DROP PARTITION p1; diff --git a/mysql-test/t/partition_debug_sync.test b/mysql-test/t/partition_debug_sync.test index bcec5503e6f..3ca21c2185a 100644 --- a/mysql-test/t/partition_debug_sync.test +++ b/mysql-test/t/partition_debug_sync.test @@ -5,6 +5,8 @@ # --source include/have_partition.inc --source include/have_debug_sync.inc +--source include/have_debug.inc +--source include/have_innodb.inc --disable_warnings DROP TABLE IF EXISTS t1, t2; @@ -16,7 +18,9 @@ SET DEBUG_SYNC= 'RESET'; --echo # Test when remove partitioning is done while drop table is waiting --echo # for the table. --echo # After MDL was introduced, there is no longer any race, so test is done ---echo # by adding a small sleep to verify that the delete waits. +--echo # by adding a small sleep to verify that the delete waits. This happens +--echo # only until ALTER tries to upgrade its MDL lock, which ends up in MDL +--echo # deadlock which is correctly reported. connect(con1, localhost, root,,); --echo # Con 1 SET DEBUG_SYNC= 'RESET'; @@ -31,16 +35,17 @@ ENGINE = MYISAM PARTITION p2 VALUES LESS THAN (100), PARTITION p3 VALUES LESS THAN MAXVALUE ) */; SET DEBUG_SYNC= 'alter_table_before_create_table_no_lock SIGNAL removing_partitioning WAIT_FOR waiting_for_alter'; -SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL partitioning_removed'; +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL waiting_for_upgrade'; --send ALTER TABLE t1 REMOVE PARTITIONING connection default; --echo # Con default SET DEBUG_SYNC= 'now WAIT_FOR removing_partitioning'; SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL waiting_for_alter'; -SET DEBUG_SYNC= 'rm_table_no_locks_before_delete_table WAIT_FOR partitioning_removed'; +SET DEBUG_SYNC= 'rm_table_no_locks_before_delete_table WAIT_FOR waiting_for_upgrade'; DROP TABLE IF EXISTS t1; --echo # Con 1 connection con1; +--error ER_LOCK_DEADLOCK --reap connection default; SET DEBUG_SYNC= 'RESET'; @@ -77,8 +82,54 @@ connection con1; --reap SET DEBUG_SYNC= 'RESET'; disconnect con1; +--source include/wait_until_disconnected.inc connection default; --echo # Con default SET DEBUG_SYNC= 'RESET'; --echo End of 5.1 tests + +--echo # +--echo # Coverage test for non pruned ha_partition::store_lock() +--echo # +CREATE TABLE t1 (a int) ENGINE = InnoDB; +CREATE TABLE t2 (a int PRIMARY KEY) +ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 3; + +HANDLER t1 OPEN; + +--echo # Con1 +connect (con1, localhost, root,,); + +LOCK TABLES t1 WRITE, t2 READ; + +--echo # Default +connection default; + +SET DEBUG_SYNC="wait_for_lock SIGNAL locking"; +send INSERT INTO t2 VALUES (1), (2), (3); + +--echo # Con1 +connection con1; +SET DEBUG_SYNC="now WAIT_FOR locking"; + +send ALTER TABLE t1 ADD COLUMN b int; + +--echo # Default +connection default; +--error ER_LOCK_ABORTED +--reap + +SELECT 1; + +--echo # Con1 +connection con1; +--reap + +UNLOCK TABLES; +--disconnect con1 + +--echo # Default +connection default; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index ea8faec0d51..7d903fa1165 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -41,6 +41,17 @@ EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; DROP TABLE t1; --echo # +--echo # Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY +--echo # and PARTITIONING +--echo # +CREATE TABLE t1 (a INT, KEY(a)) +ENGINE = InnoDB +PARTITION BY KEY (a) PARTITIONS 1; +SELECT 1 FROM t1 WHERE a > (SELECT LAST_INSERT_ID() FROM t1 LIMIT 0) +ORDER BY a; +DROP TABLE t1; + +--echo # --echo # Bug#56287: crash when using Partition datetime in sub in query --echo # @@ -71,7 +82,7 @@ DROP TABLE t1; --echo # SELECT is not detected --echo # -SET @old_innodb_thread_concurrency:= @@innodb_thread_concurrency; +SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency; SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; SET GLOBAL innodb_thread_concurrency = 1; diff --git a/mysql-test/t/partition_mgm_err.test b/mysql-test/t/partition_mgm_err.test index f921fa8ebca..0987c427fc7 100644 --- a/mysql-test/t/partition_mgm_err.test +++ b/mysql-test/t/partition_mgm_err.test @@ -147,7 +147,7 @@ PARTITION BY KEY (a) ALTER TABLE t1 ADD PARTITION PARTITIONS 0; --error ER_TOO_MANY_PARTITIONS_ERROR -ALTER TABLE t1 ADD PARTITION PARTITIONS 1024; +ALTER TABLE t1 ADD PARTITION PARTITIONS 8192; --error ER_ONLY_ON_RANGE_LIST_PARTITION ALTER TABLE t1 DROP PARTITION x0; diff --git a/mysql-test/t/partition_myisam.test b/mysql-test/t/partition_myisam.test index 49c5d793169..a33b9e19fbf 100644 --- a/mysql-test/t/partition_myisam.test +++ b/mysql-test/t/partition_myisam.test @@ -1,5 +1,4 @@ --- source include/have_partition.inc - +--source include/have_partition.inc --disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings @@ -10,53 +9,88 @@ DROP TABLE IF EXISTS t1, t2; let $MYSQLD_DATADIR= `SELECT @@datadir`; - --echo # ---echo # Bug#50036: Inconsistent errors when using TIMESTAMP ---echo # columns/expressions - ---echo # Added test with existing TIMESTAMP partitioning (when it was allowed). -CREATE TABLE t1 (a TIMESTAMP) -ENGINE = MyISAM -PARTITION BY HASH (UNIX_TIMESTAMP(a)); -INSERT INTO t1 VALUES ('2000-01-02 03:04:05'); ---sorted_result -SELECT * FROM t1; -FLUSH TABLES; ---echo # replacing t1.frm with TO_DAYS(a) which was allowed earlier. ---remove_file $MYSQLD_DATADIR/test/t1.frm ---copy_file std_data/parts/t1TIMESTAMP.frm $MYSQLD_DATADIR/test/t1.frm ---echo # Disable warnings, since the result would differ when running with ---echo # --ps-protocol (only for the 'SELECT * FROM t1' statement). ---disable_warnings ---sorted_result -SELECT * FROM t1; ---enable_warnings ---replace_result MyISAM <curr_engine> InnoDB <curr_engine> -SHOW CREATE TABLE t1; -INSERT INTO t1 VALUES ('2001-02-03 04:05:06'); ---sorted_result -SELECT * FROM t1; -ALTER TABLE t1 ADD PARTITION PARTITIONS 2; ---error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR -ALTER TABLE t1 -PARTITION BY RANGE (TO_DAYS(a)) -(PARTITION p0 VALUES LESS THAN (10000), - PARTITION p1 VALUES LESS THAN (MAXVALUE)); +--echo # BUG#11933226 - 60681: CHECKSUM TABLE RETURNS 0 FOR PARTITIONED TABLE +--echo # +CREATE TABLE t1 ( + i INT +) +ENGINE=MyISAM +PARTITION BY RANGE (i) +(PARTITION p3 VALUES LESS THAN (3), + PARTITION p5 VALUES LESS THAN (5), + PARTITION pMax VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6); +CHECKSUM TABLE t1; +ALTER TABLE t1 CHECKSUM = 1; +CHECKSUM TABLE t1 EXTENDED; +--echo # Before patch this returned 0! +CHECKSUM TABLE t1; SHOW CREATE TABLE t1; -CREATE TABLE t2 LIKE t1; -SHOW CREATE TABLE t2; -DROP TABLE t2; -CREATE TABLE t2 SELECT * FROM t1; -DROP TABLE t2; -ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a)); +DROP TABLE t1; + +--echo # Same test without partitioning +CREATE TABLE t1 ( + i INT +) ENGINE=MyISAM; SHOW CREATE TABLE t1; -ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6); +CHECKSUM TABLE t1; +ALTER TABLE t1 CHECKSUM = 1; +CHECKSUM TABLE t1 EXTENDED; +CHECKSUM TABLE t1; SHOW CREATE TABLE t1; ---sorted_result -SELECT * FROM t1; DROP TABLE t1; +# +# Disabled by WL#946: binary format for timestamp column is not compatible. +# So the trick with replacing FRM file does not work any more. +#--echo # +#--echo # Bug#50036: Inconsistent errors when using TIMESTAMP +#--echo # columns/expressions +# +#--echo # Added test with existing TIMESTAMP partitioning (when it was allowed). +#CREATE TABLE t1 (a TIMESTAMP) +#ENGINE = MyISAM +#PARTITION BY HASH (UNIX_TIMESTAMP(a)); +#INSERT INTO t1 VALUES ('2000-01-02 03:04:05'); +#--sorted_result +#SELECT * FROM t1; +#FLUSH TABLES; +#--echo # replacing t1.frm with TO_DAYS(a) which was allowed earlier. +#--remove_file $MYSQLD_DATADIR/test/t1.frm +#--copy_file std_data/parts/t1TIMESTAMP.frm $MYSQLD_DATADIR/test/t1.frm +#--echo # Disable warnings, since the result would differ when running with +#--echo # --ps-protocol (only for the 'SELECT * FROM t1' statement). +#--disable_warnings +#--sorted_result +#SELECT * FROM t1; +#--enable_warnings +#--replace_result MyISAM <curr_engine> InnoDB <curr_engine> +#SHOW CREATE TABLE t1; +#INSERT INTO t1 VALUES ('2001-02-03 04:05:06'); +#--sorted_result +#SELECT * FROM t1; +#SELECT a, hex(weight_string(a)) FROM t1; +#ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +#--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR +#ALTER TABLE t1 +#PARTITION BY RANGE (TO_DAYS(a)) +#(PARTITION p0 VALUES LESS THAN (10000), +# PARTITION p1 VALUES LESS THAN (MAXVALUE)); +#SHOW CREATE TABLE t1; +#CREATE TABLE t2 LIKE t1; +#SHOW CREATE TABLE t2; +#DROP TABLE t2; +#CREATE TABLE t2 SELECT * FROM t1; +#DROP TABLE t2; +#ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a)); +#SHOW CREATE TABLE t1; +#ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +#SHOW CREATE TABLE t1; +#--sorted_result +#SELECT * FROM t1; +#DROP TABLE t1; --echo # --echo # Bug#31931: Mix of handlers error message @@ -181,3 +215,18 @@ PARTITION BY RANGE (a) PARTITION pMax VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (1, "Partition p1, first row"); DROP TABLE t1; +--echo # +--echo # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED +--echo # TABLE CORRUPTS MYISAM +--disable_warnings +DROP TABLE if exists `t1`; +--enable_warnings +CREATE TABLE `t1`(`a` INT)ENGINE=myisam; +ALTER TABLE `t1` ADD COLUMN `b` INT; +CREATE UNIQUE INDEX `i1` ON `t1`(`b`); +CREATE UNIQUE INDEX `i2` ON `t1`(`a`); +ALTER TABLE `t1` ADD PRIMARY KEY (`a`); +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +ALTER TABLE `t1` REMOVE PARTITIONING; +CHECK TABLE `t1` EXTENDED; +DROP TABLE t1; diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index 1c8a4d254a8..e7e764ce138 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -622,7 +622,6 @@ insert into t3 values (5),(15); explain partitions select * from t3 where a=11; explain partitions select * from t3 where a=10; explain partitions select * from t3 where a=20; - explain partitions select * from t3 where a=30; # LIST(expr) partitioning diff --git a/mysql-test/t/partition_truncate.test b/mysql-test/t/partition_truncate.test index 165213d204c..62ed548808f 100644 --- a/mysql-test/t/partition_truncate.test +++ b/mysql-test/t/partition_truncate.test @@ -11,7 +11,7 @@ partition by list (a) (partition p1 values in (0)); --error ER_WRONG_PARTITION_NAME alter table t1 truncate partition p1,p1; ---error ER_WRONG_PARTITION_NAME +--error ER_UNKNOWN_PARTITION alter table t1 truncate partition p0; drop table t1; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 6e68b57bb3c..a8a71dcf8b3 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -1628,6 +1628,28 @@ DROP TABLE t1; SET GLOBAL query_cache_size= @qc; --echo # +--echo End of 5.5 tests + +--echo # +--echo # MDEV-617 LP:671189 - Query cache is not used for tables or +--echo # databases with dots in their names +--echo # +CREATE DATABASE `foo.bar`; +use `foo.bar`; +flush status; +CREATE TABLE moocow (a int); +INSERT INTO moocow VALUES (1), (2), (3); +SHOW STATUS LIKE 'Qcache_inserts'; +SELECT * FROM moocow; +SHOW STATUS LIKE 'Qcache_inserts'; +SHOW STATUS LIKE 'Qcache_hits'; +SELECT * FROM moocow; +SHOW STATUS LIKE 'Qcache_hits'; +use test; +drop database `foo.bar`; + +--echo End of 10.0 tests + --echo restore defaults SET GLOBAL query_cache_type= default; SET GLOBAL query_cache_size= default; diff --git a/mysql-test/t/signal.test b/mysql-test/t/signal.test index 13a0db2029b..31bc7bc9633 100644 --- a/mysql-test/t/signal.test +++ b/mysql-test/t/signal.test @@ -1551,15 +1551,24 @@ drop procedure test_signal $$ --echo # Test where SIGNAL can be used --echo # +--echo +--echo # RETURN statement clears Diagnostics Area, thus +--echo # the warnings raised in a stored function are not +--echo # visible outsidef the stored function. So, we're using +--echo # @@warning_count variable to check that SIGNAL succeeded. +--echo + create function test_signal_func() returns integer begin + DECLARE v INT; DECLARE warn CONDITION FOR SQLSTATE "01XXX"; SIGNAL warn SET MESSAGE_TEXT = "This function SIGNAL a warning", MYSQL_ERRNO = 1012; - return 5; + SELECT @@warning_count INTO v; + return v; end $$ select test_signal_func() $$ diff --git a/mysql-test/t/sp-bugs.test b/mysql-test/t/sp-bugs.test index 3ab1689e8b2..1ec154f1c69 100644 --- a/mysql-test/t/sp-bugs.test +++ b/mysql-test/t/sp-bugs.test @@ -167,6 +167,15 @@ USE test; --echo End of 5.1 tests --echo # +--echo # BUG#13489996 valgrind:conditional jump or move depends on +--echo # uninitialised values-field_blob +--echo # + +CREATE FUNCTION sf() RETURNS BLOB RETURN ""; +SELECT sf(); +DROP FUNCTION sf; + +--echo # --echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE --echo # SET @@SQL_MODE = ''; @@ -228,3 +237,4 @@ DROP PROCEDURE testp_bug11763507; DROP FUNCTION testf_bug11763507; --echo #END OF BUG#11763507 test. + diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 063b30c01cb..711e639191e 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -2881,3 +2881,973 @@ SHOW WARNINGS; DROP TABLE t1; DROP TABLE t2; DROP PROCEDURE p1; + +--echo +--echo ################################################################### +--echo # Tests for the following bugs: +--echo # - Bug#11763171: 55852 - Possibly inappropriate handler activation. +--echo # - Bug#11749343: 38806 - Wrong scope for SQL HANDLERS in SP. +--echo ################################################################### +--echo + +# +# Structure of SQL-block: +# BEGIN +# <Handler declaration block> +# <Statement block> +# END +# +# Scope of Handler-decl-block is Statement-block. +# I.e. SQL-conditions thrown in the Handler-decl-block can not be handled by +# the same block, only by outer SQL-blocks. +# +# This rule is recursive, i.e. if a Handler-decl-block has nested SQL-blocks, +# the SQL-conditions from those nested blocks can not be handled by the this +# Handler-decl-block, only by outer SQL-blocks. +# + +delimiter |; + +--echo +--echo # -- Check that SQL-conditions thrown by Statement-blocks are +--echo # -- handled by Handler-decl blocks properly. +--echo + +CREATE PROCEDURE p1() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H2' AS HandlerId; + + SIGNAL SQLSTATE '01000'; # Should be handled by H2. +END| + +--echo +CALL p1()| + +--echo +--echo # -- Check that SQL-conditions thrown by Statement-blocks are +--echo # -- handled by Handler-decl blocks properly in case of nested +--echo # -- SQL-blocks. +--echo + +CREATE PROCEDURE p2() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H2' AS HandlerId; + + BEGIN + + SELECT 'B1' AS BlockId; + BEGIN + + SELECT 'B2' AS BlockId; + BEGIN + SELECT 'B3' AS BlockId; + SIGNAL SQLSTATE '01000'; # Should be handled by H2. + END; + + END; + + END; + +END| + +--echo +CALL p2()| + +--echo +--echo # -- Check SQL-handler resolution rules. +--echo + +CREATE PROCEDURE p3() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H3' AS HandlerId; + + SIGNAL SQLSTATE '01000'; # Should be handled by H3. +END| + +--echo +CALL p3()| +--echo + +CREATE PROCEDURE p4() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H3' AS HandlerId; + + SIGNAL SQLSTATE '01000'; # Should be handled by H2. +END| + +--echo +CALL p4()| +--echo + +CREATE PROCEDURE p5() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H2' AS HandlerId; + + BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H3' AS HandlerId; + + SIGNAL SQLSTATE '01000'; # Should be handled by H3. + END; +END| + +--echo +CALL p5()| + +--echo +--echo # -- Check that handlers don't handle its own exceptions. +--echo + +CREATE PROCEDURE p6() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + SELECT 'H1' AS HandlerId; + SIGNAL SQLSTATE 'HY000'; # Should *not* be handled by H1. + END; + + SELECT 'S1' AS SignalId; + SIGNAL SQLSTATE 'HY000'; # Should be handled by H1. +END| + +--echo +--error ER_SIGNAL_EXCEPTION +CALL p6()| + +--echo +--echo # -- Check that handlers don't handle its own warnings. +--echo + +CREATE PROCEDURE p7() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + SELECT 'H1' AS HandlerId; + SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. + END; + + SELECT 'S1' AS SignalId; + SIGNAL SQLSTATE '01000'; # Should be handled by H1. +END| + +--echo +CALL p7()| + +--echo +--echo # -- Check that conditions for handlers are not handled by the handlers +--echo # -- from the same block. +--echo + +CREATE PROCEDURE p8() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + SELECT 'H2' AS HandlerId; + SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. + END; + + SELECT 'S1' AS SignalId; + SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. +END| + +--echo +CALL p8()| + +--echo +--echo # -- Check that conditions for handlers are not handled by the handlers +--echo # -- from the same block even if they are thrown deep down the stack. +--echo + +CREATE PROCEDURE p9() +BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H1:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H1:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H2:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H2:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H3:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H3:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H4:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H4:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H5:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H5:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H6:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H6:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + SELECT 'H2' AS HandlerId; + SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. + END; + + SELECT 'S6' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + END; + + SELECT 'S5' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S4' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S3' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S2' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S1' AS SignalId; + SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. + +END| + +--echo +CALL p9()| + +--echo +--echo # -- Check that handlers are choosen properly in case of deep stack and +--echo # -- nested SQL-blocks. +--echo + +CREATE PROCEDURE p10() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H2' AS HandlerId; + + BEGIN + BEGIN + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H1:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H1:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H2:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H2:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H3:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H3:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H4:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H4:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H5:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H5:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H6:1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'Wrong:H6:2' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + SELECT 'H2' AS HandlerId; + SIGNAL SQLSTATE '01000'; # Should be handled by H1. + END; + + SELECT 'S6' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + END; + + SELECT 'S5' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S4' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S3' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S2' AS SignalId; + SIGNAL SQLSTATE 'HY000'; + + END; + + SELECT 'S1' AS SignalId; + SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. + + END; + END; + END; +END| + +--echo +CALL p10()| + +--echo +--echo # -- Test stored procedure from Peter's mail. +--echo + +CREATE PROCEDURE p11() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + SELECT 'H1' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H2' AS HandlerId; + + BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '01000', 1249 + BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + SELECT 'H3' AS HandlerId; + + DECLARE CONTINUE HANDLER FOR SQLWARNING + SELECT 'H4' AS HandlerId; + + BEGIN + SELECT 'H5' AS HandlerId; + + SELECT 'S3' AS SignalId; + SIGNAL SQLSTATE 'HY000'; # H3 + + SELECT 'S4' AS SignalId; + SIGNAL SQLSTATE '22003'; # H3 + + SELECT 'S5' AS SignalId; + SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H4 + END; + END; + + SELECT 'S6' AS SignalId; + SIGNAL SQLSTATE 'HY000'; # H1 + + SELECT 'S7' AS SignalId; + SIGNAL SQLSTATE '22003'; # H1 + + SELECT 'S8' AS SignalId; + SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H5 + END; + + SELECT 'S1' AS SignalId; + SIGNAL SQLSTATE 'HY000'; # H1 + + SELECT 'S2' AS SignalId; + SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H2 +END| + +--echo +CALL p11()| + +--echo +--echo # -- Check that runtime stack-trace can be deeper than parsing-time one. +--echo + +CREATE PROCEDURE p12() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN + SELECT 'H1:5' AS HandlerId; + SIGNAL SQLSTATE '01002'; + END; + SELECT 'H1:4' AS HandlerId; + SIGNAL SQLSTATE '01001'; + END; + SELECT 'H1:3' AS HandlerId; + SIGNAL SQLSTATE '01001'; + END; + SELECT 'H1:2' AS HandlerId; + SIGNAL SQLSTATE '01001'; + END; + SELECT 'H1:1' AS HandlerId; + SIGNAL SQLSTATE '01001'; + END; + + ######################################################### + + DECLARE CONTINUE HANDLER FOR SQLSTATE '01002' + SELECT 'OK' AS Msg; + + ######################################################### + + BEGIN + + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + SELECT 'H2:5' AS HandlerId; + SIGNAL SQLSTATE '01001'; + END; + SELECT 'H2:4' AS HandlerId; + SIGNAL SQLSTATE '01000'; + END; + SELECT 'H2:3' AS HandlerId; + SIGNAL SQLSTATE '01000'; + END; + SELECT 'H2:2' AS HandlerId; + SIGNAL SQLSTATE '01000'; + END; + SELECT 'H2:1' AS HandlerId; + SIGNAL SQLSTATE '01000'; + END; + + ####################################################### + + SELECT 'Throw 01000' AS Msg; + SIGNAL SQLSTATE '01000'; + END; + +END| + +--echo +CALL p12()| + +--echo +--echo # -- Check that handler-call-frames are removed properly for EXIT +--echo # -- handlers. +--echo + +CREATE PROCEDURE p13() +BEGIN + + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING + BEGIN + DECLARE EXIT HANDLER FOR SQLWARNING + BEGIN + SELECT 'EXIT handler 3' AS Msg; + END; + + SELECT 'CONTINUE handler 2: 1' AS Msg; + SIGNAL SQLSTATE '01000'; + SELECT 'CONTINUE handler 2: 2' AS Msg; + END; + + SELECT 'CONTINUE handler 1: 1' AS Msg; + SIGNAL SQLSTATE '01000'; + SELECT 'CONTINUE handler 1: 2' AS Msg; + END; + + SELECT 'Throw 01000' AS Msg; + SIGNAL SQLSTATE '01000'; +END| + +--echo +CALL p13()| + +delimiter ;| + +--echo +--echo # That's it. Cleanup. +--echo + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; +DROP PROCEDURE p7; +DROP PROCEDURE p8; +DROP PROCEDURE p9; +DROP PROCEDURE p10; +DROP PROCEDURE p11; +DROP PROCEDURE p12; +DROP PROCEDURE p13; + +--echo +--echo # Bug#12731619: NESTED SP HANDLERS CAN TRIGGER ASSERTION +--echo + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(msg VARCHAR(255)); + +delimiter |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 1 + BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 2 + BEGIN + INSERT INTO t1 VALUE('WRONG: Inside H2'); + RETURN 2; + END; + + INSERT INTO t1 VALUE('CORRECT: Inside H1'); + RETURN 1; + END; + + BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING # handler 3 + BEGIN + INSERT INTO t1 VALUE('WRONG: Inside H3'); + RETURN 3; + END; + + INSERT INTO t1 VALUE('CORRECT: Calling f1()'); + RETURN f1(); # -- exception here + END; + + INSERT INTO t1 VALUE('WRONG: Returning 10'); + RETURN 10; + +END| + +delimiter ;| + +--echo +SELECT f1(); +--echo +SELECT * FROM t1; +--echo + +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo +--echo # Check that handled SQL-conditions are properly cleared from DA. +--echo + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +--enable_warnings + +CREATE TABLE t1(a CHAR, b CHAR, c CHAR); +CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT); + +delimiter |; + +--echo +--echo # Check that SQL-conditions for which SQL-handler has been invoked, +--echo # are cleared from the Diagnostics Area. Note, there might be several +--echo # SQL-conditions, but SQL-handler must be invoked only once. +--echo + +CREATE PROCEDURE p1() +BEGIN + DECLARE EXIT HANDLER FOR SQLWARNING + SELECT 'Warning caught' AS msg; + + # The INSERT below raises 3 SQL-conditions (warnings). The EXIT HANDLER + # above must be invoked once (for one condition), but all three conditions + # must be cleared from the Diagnostics Area. + + INSERT INTO t1 VALUES('qqqq', 'ww', 'eee'); + + # The following INSERT will not be executed, because of the EXIT HANDLER. + + INSERT INTO t1 VALUES('zzz', 'xx', 'yyyy'); +END| + +--echo +CALL p1()| +--echo +SELECT * FROM t1| + +--echo +--echo # Check that SQL-conditions for which SQL-handler has *not* been +--echo # invoked, are *still* cleared from the Diagnostics Area. +--echo + +CREATE PROCEDURE p2() +BEGIN + DECLARE CONTINUE HANDLER FOR 1292 + SELECT 'Warning 1292 caught' AS msg; + + # The following INSERT raises 6 SQL-warnings with code 1292, + # and 3 SQL-warnings with code 1264. The CONTINUE HANDLER above must be + # invoked once, and all nine SQL-warnings must be cleared from + # the Diagnostics Area. + + INSERT INTO t2 + SELECT + CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +--echo +CALL p2()| + +--echo +--echo # Check that if there are two equally ranked SQL-handlers to handle +--echo # SQL-conditions from SQL-statement, only one of them will be invoked. +--echo + +CREATE PROCEDURE p3() +BEGIN + DECLARE CONTINUE HANDLER FOR 1292 + SELECT 'Warning 1292 caught' AS msg; + + DECLARE CONTINUE HANDLER FOR 1264 + SELECT 'Warning 1264 caught' AS msg; + + # The following INSERT raises 6 SQL-warnings with code 1292, + # and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above + # must be called, and only once. The SQL Standard does not define, which one + # should be invoked. + + INSERT INTO t2 + SELECT + CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +--echo +CALL p3()| + +--echo +--echo # The same as p3, but 1264 comes first. +--echo + +CREATE PROCEDURE p4() +BEGIN + DECLARE CONTINUE HANDLER FOR 1292 + SELECT 'Warning 1292 caught' AS msg; + + DECLARE CONTINUE HANDLER FOR 1264 + SELECT 'Warning 1264 caught' AS msg; + + # The following INSERT raises 4 SQL-warnings with code 1292, + # and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above + # must be called, and only once. The SQL Standard does not define, which one + # should be invoked. + + INSERT INTO t2 + SELECT + CAST(999999 AS SIGNED INTEGER), + CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +--echo +CALL p4()| + +--echo +--echo # Check that if a SQL-handler raised its own SQL-conditions, there are +--echo # preserved after handler exit. +--echo + +CREATE PROCEDURE p5() +BEGIN + DECLARE EXIT HANDLER FOR 1292 + BEGIN + SELECT 'Handler for 1292 (1)' AS Msg; + SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1234; + SHOW WARNINGS; + SELECT 'Handler for 1292 (2)' AS Msg; + END; + + INSERT INTO t2 + SELECT + CAST(999999 AS SIGNED INTEGER), + CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +--echo +CALL p5()| + +--echo +--echo # Check that SQL-conditions are available inside the handler, but +--echo # cleared after the handler exits. +--echo + +CREATE PROCEDURE p6() +BEGIN + DECLARE CONTINUE HANDLER FOR 1292 + BEGIN + SHOW WARNINGS; + SELECT 'Handler for 1292' Msg; + END; + + INSERT INTO t2 + SELECT + CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), + CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +--echo +CALL p6()| + +delimiter ;| + +--echo +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; +DROP TABLE t1; +DROP TABLE t2; + +--echo +--echo # Bug#13059316: ASSERTION FAILURE IN SP_RCONTEXT.CC +--echo # Check DECLARE statements that raise conditions before handlers +--echo # are declared. +--echo + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +delimiter |; + +CREATE PROCEDURE p1() +BEGIN + DECLARE var1 INTEGER DEFAULT 'string'; + DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H1'; +END| + +--echo +CALL p1()| +--echo + +CREATE PROCEDURE p2() +BEGIN + DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H2'; + CALL p1(); +END| + +--echo +CALL p2()| + +delimiter ;| + +--echo +DROP PROCEDURE p1; +DROP PROCEDURE p2; + + +--echo # +--echo # Bug#13113222 RQG_SIGNAL_RESIGNAL FAILED WITH ASSERTION. +--echo # + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'triggered p1'; + # This will trigger an error. + SIGNAL SQLSTATE 'HY000'; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'triggered p2'; + # This will trigger a warning. + SIGNAL SQLSTATE '01000'; +END| +delimiter ;| + +SET @old_max_error_count= @@session.max_error_count; +SET SESSION max_error_count= 0; +CALL p1(); +CALL p2(); +SET SESSION max_error_count= @old_max_error_count; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +--echo +--echo # Bug#12652873: 61392: Continue handler for NOT FOUND being triggered +--echo # from internal stored function. +--echo + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP TABLE IF EXISTS t1; +--enable_warnings + +--echo + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1, 2); + +delimiter |; + +--echo +--echo # f1() raises NOT_FOUND condition. +--echo # Raising NOT_FOUND can not be simulated by SIGNAL, +--echo # because SIGNAL would raise SQL-error in that case. +--echo + +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN + DECLARE v VARCHAR(5) DEFAULT -1; + SELECT b FROM t1 WHERE a = 2 INTO v; + RETURN v; +END| + +--echo +--echo # Here we check that the NOT_FOUND condition raised in f1() +--echo # is not visible in the outer function (f2), i.e. the continue +--echo # handler in f2() will not be called. +--echo + +CREATE FUNCTION f2() RETURNS INTEGER +BEGIN + DECLARE v INTEGER; + + DECLARE CONTINUE HANDLER FOR NOT FOUND + SET @msg = 'Handler activated.'; + + SELECT f1() INTO v; + + RETURN v; +END| + +delimiter ;| + +SET @msg = ''; + +--echo +SELECT f2(); +--echo +SELECT @msg; +--echo + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index c429e9cfe5b..71b625e0843 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -1350,3 +1350,23 @@ select count(*) from t1 where a is null; drop table t1; --echo End of 5.0 tests + +--echo # +--echo # Start of 5.6 tests +--echo # + +--echo # +--echo # WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds: CAST to DATETIME +--echo # + +--echo # +--echo # STR_TO_DATE with NO_ZERO_DATE did not return NULL (with warning) +--echo # in get_date(). Only did in val_str() and val_int(). +SET sql_mode='NO_ZERO_DATE'; +SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0','%Y')+1, STR_TO_DATE('0000','%Y')+1; +SET sql_mode='NO_ZERO_IN_DATE'; +SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0000','%Y')+1; + +--echo # +--echo # End of 5.6 tests +--echo # diff --git a/mysql-test/t/system_mysql_db_fix40123.test b/mysql-test/t/system_mysql_db_fix40123.test index 1b89ea2001b..18df3adb8b1 100644 --- a/mysql-test/t/system_mysql_db_fix40123.test +++ b/mysql-test/t/system_mysql_db_fix40123.test @@ -78,7 +78,7 @@ CREATE TABLE index_stats (db_name varchar(64) NOT NULL, table_name varchar(64) N -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, gtid_slave_pos, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, slave_master_info, slave_relay_log_info, innodb_index_stats, innodb_table_stats, slave_worker_info, table_stats, column_stats, index_stats; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, proxies_priv, innodb_index_stats, innodb_table_stats, table_stats, column_stats, index_stats; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix50030.test b/mysql-test/t/system_mysql_db_fix50030.test index 454711a93e6..36fa6285060 100644 --- a/mysql-test/t/system_mysql_db_fix50030.test +++ b/mysql-test/t/system_mysql_db_fix50030.test @@ -85,7 +85,7 @@ CREATE TABLE index_stats (db_name varchar(64) NOT NULL, table_name varchar(64) N -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, gtid_slave_pos, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, slave_master_info, slave_relay_log_info, innodb_index_stats, innodb_table_stats, slave_worker_info, table_stats, column_stats, index_stats; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, proxies_priv, innodb_index_stats, innodb_table_stats, table_stats, column_stats, index_stats; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix50117.test b/mysql-test/t/system_mysql_db_fix50117.test index af1bcf2a6e0..6ee7d0fe989 100644 --- a/mysql-test/t/system_mysql_db_fix50117.test +++ b/mysql-test/t/system_mysql_db_fix50117.test @@ -84,7 +84,8 @@ CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; -CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL, updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM; +# MariaDB: don't: +# CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL, updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM; CREATE TABLE table_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables'; @@ -104,7 +105,7 @@ CREATE TABLE index_stats (db_name varchar(64) NOT NULL, table_name varchar(64) N -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, gtid_slave_pos, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, slave_master_info, slave_relay_log_info, innodb_index_stats, innodb_table_stats, slave_worker_info, table_stats, column_stats, index_stats; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, proxies_priv, innodb_index_stats, innodb_table_stats, table_stats, column_stats, index_stats; -- enable_query_log diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index 92c22242cdb..dd4ee2f6676 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -1,5 +1,6 @@ # mysqltest should be fixed -- source include/not_embedded.inc + # # Test of temporary tables # @@ -9,6 +10,30 @@ drop table if exists t1,t2; drop view if exists v1; --enable_warnings +--echo # +--echo # test basic creation of temporary tables together with normal table +--echo # + +create table t1 (a int); +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 (a int); +drop temporary table t1; +drop table t1; + +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 AS SELECT 1; +--error 1050 +create temporary table t1 (a int); +drop temporary table t1; + +--echo # +--echo # Test with rename +--echo # + CREATE TABLE t1 (c int not null, d char (10) not null); insert into t1 values(1,""),(2,"a"),(3,"b"); CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null); diff --git a/mysql-test/t/truncate_coverage.test b/mysql-test/t/truncate_coverage.test index 135935b53b3..6f5c773ac6a 100644 --- a/mysql-test/t/truncate_coverage.test +++ b/mysql-test/t/truncate_coverage.test @@ -40,7 +40,7 @@ HANDLER t1 OPEN; --connection default let $ID= `SELECT @id := CONNECTION_ID()`; LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting'; +SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; send TRUNCATE TABLE t1; # # Get the default connection ID into a variable in an invisible statement. @@ -92,7 +92,7 @@ HANDLER t1 OPEN; --echo # connection default --connection default LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting'; +SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; send TRUNCATE TABLE t1; # # Remove datafile. diff --git a/mysql-test/t/upgrade.test b/mysql-test/t/upgrade.test index c6d01a16f49..6025fc31415 100644 --- a/mysql-test/t/upgrade.test +++ b/mysql-test/t/upgrade.test @@ -39,6 +39,8 @@ insert into `txu#p#p1` values (1); select * from `txu@0023p@0023p1`; create table `txu@0023p@0023p1` (s1 int); show tables; +insert into `txu@0023p@0023p1` values (2); +select * from `txu@0023p@0023p1`; select * from `txu#p#p1`; drop table `txu#p#p1`; drop table `txu@0023p@0023p1`; |