diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/alter_table.result | 487 | ||||
| -rw-r--r-- | mysql-test/r/ctype_utf8mb4.result | 15 | ||||
| -rw-r--r-- | mysql-test/r/events_restart.result | 23 | ||||
| -rw-r--r-- | mysql-test/r/partition.result | 47 | ||||
| -rw-r--r-- | mysql-test/r/partition_debug_sync.result | 28 | ||||
| -rw-r--r-- | mysql-test/r/partition_innodb.result | 13 | ||||
| -rw-r--r-- | mysql-test/r/partition_myisam.result | 74 |
7 files changed, 681 insertions, 6 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 9fbd585e25a..94c7d60c512 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1327,6 +1327,16 @@ CREATE DATABASE db1 CHARACTER SET utf8; CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100))); ALTER TABLE db1.t1 ADD baz INT; DROP DATABASE db1; +# Additional coverage for refactoring which is made as part +# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +# to allow temp table operations". +# +# At some point the below test case failed on assertion. +DROP TABLE IF EXISTS t1; +CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM; +ALTER TABLE t1 DISCARD TABLESPACE; +ERROR HY000: Storage engine MyISAM of the table `test`.`t1` doesn't have this option +DROP TABLE t1; # # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME # CLAUSE FAILS OR ABORTS SERVER. @@ -1340,3 +1350,480 @@ rename table t2 to t1; execute stmt1; deallocate prepare stmt1; drop table t2; +# +# Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a INT) engine=innodb; +INSERT INTO t1 VALUES (1), (2); +# This should not do anything +ALTER TABLE t1; +affected rows: 0 +# Check that we rebuild the table +ALTER TABLE t1 engine=innodb; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# This should also rebuild the table +ALTER TABLE t1 FORCE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1; +# Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't +# identify correct column name. +# +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(10) unsigned DEFAULT NULL, + `c2` char(100) NOT NULL DEFAULT '', + `c3` char(16) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# WL#5534 Online ALTER, Phase 1 +# +# Single thread tests. +# See innodb_mysql_sync.test for multi thread tests. +DROP TABLE IF EXISTS t1; +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); +# +# 1: Test ALGORITHM keyword +# +# --enable_info allows us to see how many rows were updated +# by ALTER TABLE. in-place will show 0 rows, while copy > 0. +ALTER TABLE t1 ADD INDEX i1(b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID; +ERROR HY000: Unknown ALGORITHM 'INVALID' +ALTER TABLE m1 ENABLE KEYS; +affected rows: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT; +affected rows: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE; +affected rows: 0 +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; +# +# 2: Test ALGORITHM + old_alter_table +# +SET SESSION old_alter_table= 1; +affected rows: 0 +ALTER TABLE t1 ADD INDEX i1(b); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SET SESSION old_alter_table= 0; +affected rows: 0 +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; +# +# 3: Test unsupported in-place operation +# +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; +# +# 4: Test LOCK keyword +# +ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID; +ERROR HY000: Unknown LOCK type 'INVALID' +ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT; +ALTER TABLE m1 ENABLE KEYS, LOCK= NONE; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE; +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; +# +# 5: Test ALGORITHM + LOCK +# +ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; +ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +affected rows: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; +ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1, m1; +# +# 6: Possible deadlock involving thr_lock.c +# +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); +# Connection con1 +# Sending: +ALTER TABLE t1 DISABLE KEYS; +# Connection default +# Waiting until ALTER TABLE is blocked. +UPDATE t1 SET b = 4; +COMMIT; +# Connection con1 +# Reaping: ALTER TABLE t1 DISABLE KEYS +# Connection default +DROP TABLE t1; +# +# 7: Which operations require copy and which can be done in-place? +# +# Test which ALTER TABLE operations are done in-place and +# which operations are done using temporary table copy. +# +# --enable_info allows us to see how many rows were updated +# by ALTER TABLE. in-place will show 0 rows, while copy > 0. +# +DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3; +# 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); +ALTER TABLE ti1; +affected rows: 0 +ALTER TABLE tm1; +affected rows: 0 +ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN d VARCHAR(200); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD COLUMN f INT AFTER a; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN f INT AFTER a; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD INDEX ii1(b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD INDEX im1(b); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. +ALTER TABLE ti1 ADD PRIMARY KEY(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD PRIMARY KEY(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP INDEX ii3; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP INDEX im3; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP COLUMN d2; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP COLUMN d2; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CHANGE COLUMN f g INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CHANGE COLUMN f g INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e INT; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e INT; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e INT FIRST; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e INT FIRST; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +SET @orig_sql_mode = @@sql_mode; +SET @@sql_mode = 'STRICT_TRANS_TABLES'; +ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SET @@sql_mode = @orig_sql_mode; +ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN c INT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN c INT NULL; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP COLUMN h; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP COLUMN h; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP INDEX ii2; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP INDEX im2; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP PRIMARY KEY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP PRIMARY KEY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP FOREIGN KEY fi1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP FOREIGN KEY fm1; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 RENAME TO ti3; +affected rows: 0 +ALTER TABLE tm1 RENAME TO tm3; +affected rows: 0 +ALTER TABLE ti3 RENAME TO ti1; +affected rows: 0 +ALTER TABLE tm3 RENAME TO tm1; +affected rows: 0 +ALTER TABLE ti1 ORDER BY b; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ORDER BY b; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DEFAULT CHARACTER SET utf8; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 FORCE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 FORCE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 AUTO_INCREMENT 3; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 AUTO_INCREMENT 3; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 AVG_ROW_LENGTH 10; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 AVG_ROW_LENGTH 10; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CHECKSUM 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CHECKSUM 1; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 COMMENT 'test'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 COMMENT 'test'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MAX_ROWS 100; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MAX_ROWS 100; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MIN_ROWS 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MIN_ROWS 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 PACK_KEYS 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 PACK_KEYS 1; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE ti1, ti2, tm1, tm2; +# 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); +ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +INSERT INTO ti3(b) VALUES (5); +ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +INSERT INTO ti3(b) VALUES (7); +SELECT * FROM ti3; +a b +1 1 +2 2 +5 5 +7 7 +DROP TABLE ti3; +# +# 8: Scenario in which ALTER TABLE was returning an unwarranted +# ER_ILLEGAL_HA error at some point during work on this WL. +# +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/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index c87ec5ce44a..407763bd09a 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -2538,6 +2538,21 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, t2; # +# Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH LONGTEXT, UNION, USER VARIABLE +# +CREATE TABLE t1(f1 LONGTEXT CHARACTER SET utf8mb4); +INSERT INTO t1 VALUES ('a'); +SELECT @a:= CAST(f1 AS SIGNED) FROM t1 +UNION ALL +SELECT CAST(f1 AS SIGNED) FROM t1; +@a:= CAST(f1 AS SIGNED) +0 +0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a' +Warning 1292 Truncated incorrect INTEGER value: 'a' +DROP TABLE t1; +# # End of 5.5 tests # # diff --git a/mysql-test/r/events_restart.result b/mysql-test/r/events_restart.result index 6a751fa29f8..ba3aa503b63 100644 --- a/mysql-test/r/events_restart.result +++ b/mysql-test/r/events_restart.result @@ -65,3 +65,26 @@ select @@event_scheduler; ON drop table execution_log; drop database events_test; +# +# Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION +# NOT PRESERVE IS DELETED AT SERVER +# +SELECT @@event_scheduler; +@@event_scheduler +ON +USE test; +DROP EVENT IF EXISTS e1; +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test e1 root@localhost SYSTEM RECURRING # 1 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +"Now we restart the server" +USE test; +SELECT @@event_scheduler; +@@event_scheduler +ON +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test e1 root@localhost SYSTEM RECURRING # 1 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT e1; +# end test for bug#11748899 diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 354e578da37..d8053f4cb89 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -67,6 +67,19 @@ AND A.b = '06' AND A.c = 343; DROP TABLE t1; # +# Bug#59503: explain extended crash in get_mm_leaf +# +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 1) +DROP TABLE t1; +# # Bug#57778: failed primary key add to partitioned innodb table # inconsistent and crashes # @@ -303,6 +316,32 @@ INSERT INTO t1 VALUES (NULL); SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; pk DROP TABLE t1; +SET sql_mode=no_engine_substitution; +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine; +ERROR 42000: Unknown storage engine 'NonExistentEngine' +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine +PARTITION BY HASH (a); +ERROR 42000: Unknown storage engine 'NonExistentEngine' +CREATE TABLE t1 (a INT) +ENGINE=Memory; +ALTER TABLE t1 ENGINE=NonExistentEngine; +ERROR 42000: Unknown storage engine 'NonExistentEngine' +ALTER TABLE t1 +PARTITION BY HASH (a) +(PARTITION p0 ENGINE=Memory, +PARTITION p1 ENGINE=NonExistentEngine); +ERROR 42000: Unknown storage engine 'NonExistentEngine' +ALTER TABLE t1 ENGINE=NonExistentEngine; +ERROR 42000: Unknown storage engine 'NonExistentEngine' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET sql_mode=''; CREATE TABLE t1 (a INT) ENGINE=NonExistentEngine; Warnings: @@ -339,6 +378,7 @@ t1 CREATE TABLE `t1` ( (PARTITION p0 ENGINE = MEMORY, PARTITION p1 ENGINE = MEMORY) */ DROP TABLE t1; +SET sql_mode=DEFAULT; CREATE TABLE t1 (a INT NOT NULL, KEY(a)) PARTITION BY RANGE(a) (PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE); @@ -1056,13 +1096,13 @@ select * from t1 where f1 = 10; f1 f2 10 1 drop table t1; -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'; create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1)) partition by hash(f_int1) partitions 2; insert into t1 values (1,1),(2,2); @@ -1885,8 +1925,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; MyISAM_part 16421 19092 diff --git a/mysql-test/r/partition_debug_sync.result b/mysql-test/r/partition_debug_sync.result index 42628aca35c..c5b1d252100 100644 --- a/mysql-test/r/partition_debug_sync.result +++ b/mysql-test/r/partition_debug_sync.result @@ -5,7 +5,9 @@ SET DEBUG_SYNC= 'RESET'; # Test when remove partitioning is done while drop table is waiting # for the table. # After MDL was introduced, there is no longer any race, so test is done -# by adding a small sleep to verify that the delete waits. +# by adding a small sleep to verify that the delete waits. This happens +# only until ALTER tries to upgrade its MDL lock, which ends up in MDL +# deadlock which is correctly reported. # Con 1 SET DEBUG_SYNC= 'RESET'; CREATE TABLE t1 @@ -64,3 +66,27 @@ SET DEBUG_SYNC= 'RESET'; # Con default SET DEBUG_SYNC= 'RESET'; End of 5.1 tests +# +# Coverage test for non pruned ha_partition::store_lock() +# +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; +# Con1 +LOCK TABLES t1 WRITE, t2 READ; +# Default +SET DEBUG_SYNC="wait_for_lock SIGNAL locking"; +INSERT INTO t2 VALUES (1), (2), (3); +# Con1 +SET DEBUG_SYNC="now WAIT_FOR locking"; +ALTER TABLE t1 ADD COLUMN b int; +# Default +ERROR HY000: Wait on a lock was aborted due to a pending exclusive lock +SELECT 1; +1 +1 +# Con1 +UNLOCK TABLES; +# Default +DROP TABLE t1, t2; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index adfe3287459..6f046c2b42c 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -31,6 +31,17 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,b b 67 NULL 18 Using where; Using index DROP TABLE t1; # +# Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY +# and PARTITIONING +# +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; +1 +DROP TABLE t1; +# # Bug#56287: crash when using Partition datetime in sub in query # CREATE TABLE t1 @@ -60,7 +71,7 @@ DROP TABLE t1; # Bug#54747: Deadlock between REORGANIZE PARTITION and # SELECT is not detected # -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; CREATE TABLE t1 diff --git a/mysql-test/r/partition_myisam.result b/mysql-test/r/partition_myisam.result index 6e0642f9cee..8140aa4b7b3 100644 --- a/mysql-test/r/partition_myisam.result +++ b/mysql-test/r/partition_myisam.result @@ -1,5 +1,64 @@ DROP TABLE IF EXISTS t1, t2; # +# BUG#11933226 - 60681: CHECKSUM TABLE RETURNS 0 FOR PARTITIONED TABLE +# +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; +Table Checksum +test.t1 2653438147 +ALTER TABLE t1 CHECKSUM = 1; +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2653438147 +# Before patch this returned 0! +CHECKSUM TABLE t1; +Table Checksum +test.t1 2653438147 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 +/*!50100 PARTITION BY RANGE (i) +(PARTITION p3 VALUES LESS THAN (3) ENGINE = MyISAM, + PARTITION p5 VALUES LESS THAN (5) ENGINE = MyISAM, + PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +DROP TABLE t1; +# Same test without partitioning +CREATE TABLE t1 ( +i INT +) ENGINE=MyISAM; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6); +CHECKSUM TABLE t1; +Table Checksum +test.t1 2653438147 +ALTER TABLE t1 CHECKSUM = 1; +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2653438147 +CHECKSUM TABLE t1; +Table Checksum +test.t1 2653438147 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 +DROP TABLE t1; +# # Bug#31931: Mix of handlers error message # CREATE TABLE t1 (a INT) @@ -171,3 +230,18 @@ PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=100, PARTITION pMax VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (1, "Partition p1, first row"); DROP TABLE t1; +# +# bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED +# TABLE CORRUPTS MYISAM +DROP TABLE if exists `t1`; +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`); +ALTER TABLE `t1` REMOVE PARTITIONING; +ERROR HY000: Partition management on a not partitioned table is not possible +CHECK TABLE `t1` EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; |