diff options
Diffstat (limited to 'storage/innobase/mysql-test')
42 files changed, 1464 insertions, 304 deletions
diff --git a/storage/innobase/mysql-test/innodb-autoinc-44030.result b/storage/innobase/mysql-test/innodb-autoinc-44030.result new file mode 100644 index 00000000000..c0695bf0be0 --- /dev/null +++ b/storage/innobase/mysql-test/innodb-autoinc-44030.result @@ -0,0 +1,30 @@ +drop table if exists t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (null); +INSERT INTO t1 VALUES (null); +ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; +SELECT * FROM t1; +d1 +1 +2 +SELECT * FROM t1; +d1 +1 +2 +INSERT INTO t1 VALUES(null); +Got one of the listed errors +ALTER TABLE t1 AUTO_INCREMENT = 3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`d1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +d1 +1 +2 +3 +DROP TABLE t1; diff --git a/storage/innobase/mysql-test/innodb-autoinc-44030.test b/storage/innobase/mysql-test/innodb-autoinc-44030.test new file mode 100644 index 00000000000..af2e3015280 --- /dev/null +++ b/storage/innobase/mysql-test/innodb-autoinc-44030.test @@ -0,0 +1,34 @@ +-- source include/have_innodb.inc +# embedded server ignores 'delayed', so skip this +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# 44030: Error: (1500) Couldn't read the MAX(ID) autoinc value from +# the index (PRIMARY) +# This test requires a restart of the server +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (null); +INSERT INTO t1 VALUES (null); +ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; +SELECT * FROM t1; +# Restart the server +-- source include/restart_mysqld.inc +# The MySQL and InnoDB data dictionaries should now be out of sync. +# The select should print message to the error log +SELECT * FROM t1; +# MySQL have made a change (http://lists.mysql.com/commits/75268) that no +# longer results in the two data dictionaries being out of sync. If they +# revert their changes then this check for ER_AUTOINC_READ_FAILED will need +# to be enabled. Also, see http://bugs.mysql.com/bug.php?id=47621. +-- error ER_AUTOINC_READ_FAILED,1467 +INSERT INTO t1 VALUES(null); +ALTER TABLE t1 AUTO_INCREMENT = 3; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES(null); +SELECT * FROM t1; +DROP TABLE t1; diff --git a/storage/innobase/mysql-test/innodb-autoinc.result b/storage/innobase/mysql-test/innodb-autoinc.result index d2e8eb19e0c..a36b3a1a865 100644 --- a/storage/innobase/mysql-test/innodb-autoinc.result +++ b/storage/innobase/mysql-test/innodb-autoinc.result @@ -867,25 +867,380 @@ INSERT INTO t2 SELECT NULL FROM t1; Got one of the listed errors DROP TABLE t1; DROP TABLE t2; -CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; -INSERT INTO t1 VALUES (null); -INSERT INTO t1 VALUES (null); -ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-127, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyint(4) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-127 innodb +-1 innodb +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-127, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-32767, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` smallint(6) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-32767 innodb +-1 innodb +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-32757, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-8388607, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` mediumint(9) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-8388607 innodb +-1 innodb +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-8388607, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; -d1 +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-2147483647 innodb +-1 innodb +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-9223372036854775807 innodb +-1 innodb +1 NULL +2 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; +CREATE INDEX i1 on t1(c2); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `i1` (`c2`) +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 +INSERT INTO t1 (c2) values (0); +SELECT * FROM t1; +c1 c2 +10 0 +DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +INSERT INTO t1(C2) VALUES ('innodb'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `C1` double NOT NULL AUTO_INCREMENT, + `C2` char(10) DEFAULT NULL, + PRIMARY KEY (`C1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +INSERT INTO t1(C2) VALUES ('innodb'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `C1` float NOT NULL AUTO_INCREMENT, + `C2` char(10) DEFAULT NULL, + PRIMARY KEY (`C1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 SET c1 = 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 +INSERT INTO t1 SET c1 = 2; +INSERT INTO t1 SET c1 = -1; +SELECT * FROM t1; +c1 +-1 1 -3 +2 +INSERT INTO t1 SET c1 = -1; +Got one of the listed errors +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +REPLACE INTO t1 VALUES (-1); +SELECT * FROM t1; +c1 +-1 +1 +2 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL); SELECT * FROM t1; -d1 +c1 +-685113344 1 +2 3 -INSERT INTO t1 VALUES(null); -Got one of the listed errors -ALTER TABLE t1 AUTO_INCREMENT = 3; -INSERT INTO t1 VALUES(null); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL); +SELECT * FROM t1; +c1 +-685113344 +2 +3 +4 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL); +INSERT INTO t1 VALUES (4), (5), (6), (NULL); SELECT * FROM t1; -d1 +c1 +-685113344 1 +2 3 4 +5 +6 +7 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5); +SELECT * FROM t1; +c1 +-685113344 +1 +2 +5 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); +SELECT * FROM t1; +c1 +-685113344 +1 +2 +3 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DROP TABLE t1; diff --git a/storage/innobase/mysql-test/innodb-autoinc.test b/storage/innobase/mysql-test/innodb-autoinc.test index 61c42f45733..ef0359b78b0 100644 --- a/storage/innobase/mysql-test/innodb-autoinc.test +++ b/storage/innobase/mysql-test/innodb-autoinc.test @@ -478,23 +478,187 @@ INSERT INTO t2 SELECT c1 FROM t1; INSERT INTO t2 SELECT NULL FROM t1; DROP TABLE t1; DROP TABLE t2; + +# If the user has specified negative values for an AUTOINC column then +# InnoDB should ignore those values when setting the table's max value. +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SHOW VARIABLES LIKE "%auto_inc%"; +# TINYINT +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-127, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-127, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# SMALLINT +# +CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-32767, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-32757, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# MEDIUMINT +# +CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-8388607, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-8388607, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# INT +# +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# BIGINT +# +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# End negative number check + +## +# 47125: auto_increment start value is ignored if an index is created +# and engine=innodb # -# 44030: Error: (1500) Couldn't read the MAX(ID) autoinc value from -# the index (PRIMARY) -# This test requires a restart of the server -CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; -INSERT INTO t1 VALUES (null); -INSERT INTO t1 VALUES (null); -ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; +CREATE INDEX i1 on t1(c2); +SHOW CREATE TABLE t1; +INSERT INTO t1 (c2) values (0); SELECT * FROM t1; +DROP TABLE t1; + +## +# 49032: Use the correct function to read the AUTOINC column value +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); # Restart the server -- source include/restart_mysqld.inc -# The MySQL and InnoDB data dictionaries should now be out of sync. -# The select should print message to the error log +INSERT INTO t1(C2) VALUES ('innodb'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +# Restart the server +-- source include/restart_mysqld.inc +INSERT INTO t1(C2) VALUES ('innodb'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +## +# 47720: REPLACE INTO Autoincrement column with negative values +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 SET c1 = 1; +SHOW CREATE TABLE t1; +INSERT INTO t1 SET c1 = 2; +INSERT INTO t1 SET c1 = -1; SELECT * FROM t1; --- error ER_AUTOINC_READ_FAILED,1467 -INSERT INTO t1 VALUES(null); -ALTER TABLE t1 AUTO_INCREMENT = 3; -INSERT INTO t1 VALUES(null); +-- error ER_DUP_ENTRY,1062 +INSERT INTO t1 SET c1 = -1; +SHOW CREATE TABLE t1; +REPLACE INTO t1 VALUES (-1); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +## +# 49497: Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL); SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL); +INSERT INTO t1 VALUES (4), (5), (6), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; DROP TABLE t1; diff --git a/storage/innobase/mysql-test/innodb-consistent-master.opt b/storage/innobase/mysql-test/innodb-consistent-master.opt index 8cca44767da..cb48f1aaf60 100644 --- a/storage/innobase/mysql-test/innodb-consistent-master.opt +++ b/storage/innobase/mysql-test/innodb-consistent-master.opt @@ -1 +1 @@ ---innodb_lock_wait_timeout=2
+--loose-innodb_lock_wait_timeout=2 diff --git a/storage/innobase/mysql-test/innodb-consistent.test b/storage/innobase/mysql-test/innodb-consistent.test index 791600fc8a7..bf829a74ea2 100644 --- a/storage/innobase/mysql-test/innodb-consistent.test +++ b/storage/innobase/mysql-test/innodb-consistent.test @@ -1,58 +1,58 @@ --- source include/not_embedded.inc
--- source include/have_innodb.inc
-
---disable_warnings
-drop table if exists t1;
---enable_warnings
-
-# REPLACE INTO ... SELECT and INSERT INTO ... SELECT should do
-# a consistent read of the source table.
-
-connect (a,localhost,root,,);
-connect (b,localhost,root,,);
-connection a;
-set session transaction isolation level read committed;
-create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
-create table t2 like t1;
-insert into t2 values (1),(2),(3),(4),(5),(6),(7);
-set autocommit=0;
-
-# REPLACE INTO ... SELECT case
-begin;
-# this should not result in any locks on t2.
-replace into t1 select * from t2;
-
-connection b;
-set session transaction isolation level read committed;
-set autocommit=0;
-# should not cuase a lock wait.
-delete from t2 where a=5;
-commit;
-delete from t2;
-commit;
-connection a;
-commit;
-
-# INSERT INTO ... SELECT case
-begin;
-# this should not result in any locks on t2.
-insert into t1 select * from t2;
-
-connection b;
-set session transaction isolation level read committed;
-set autocommit=0;
-# should not cuase a lock wait.
-delete from t2 where a=5;
-commit;
-delete from t2;
-commit;
-connection a;
-commit;
-
-select * from t1;
-drop table t1;
-drop table t2;
-
-connection default;
-disconnect a;
-disconnect b;
+-- source include/not_embedded.inc +-- source include/have_innodb.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# REPLACE INTO ... SELECT and INSERT INTO ... SELECT should do +# a consistent read of the source table. + +connect (a,localhost,root,,); +connect (b,localhost,root,,); +connection a; +set session transaction isolation level read committed; +create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; +create table t2 like t1; +insert into t2 values (1),(2),(3),(4),(5),(6),(7); +set autocommit=0; + +# REPLACE INTO ... SELECT case +begin; +# this should not result in any locks on t2. +replace into t1 select * from t2; + +connection b; +set session transaction isolation level read committed; +set autocommit=0; +# should not cause a lock wait. +delete from t2 where a=5; +commit; +delete from t2; +commit; +connection a; +commit; + +# INSERT INTO ... SELECT case +begin; +# this should not result in any locks on t2. +insert into t1 select * from t2; + +connection b; +set session transaction isolation level read committed; +set autocommit=0; +# should not cause a lock wait. +delete from t2 where a=5; +commit; +delete from t2; +commit; +connection a; +commit; + +select * from t1; +drop table t1; +drop table t2; + +connection default; +disconnect a; +disconnect b; diff --git a/storage/innobase/mysql-test/innodb-index.result b/storage/innobase/mysql-test/innodb-index.result index a7d66b15300..f384b825a2c 100644 --- a/storage/innobase/mysql-test/innodb-index.result +++ b/storage/innobase/mysql-test/innodb-index.result @@ -46,13 +46,6 @@ t1 CREATE TABLE `t1` ( KEY `d2` (`d`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -CREATE TABLE `t1#1`(a INT PRIMARY KEY) ENGINE=InnoDB; -alter table t1 add unique index (c), add index (d); -ERROR HY000: Table 'test.t1#1' already exists -rename table `t1#1` to `t1#2`; -alter table t1 add unique index (c), add index (d); -ERROR HY000: Table 'test.t1#2' already exists -drop table `t1#2`; alter table t1 add unique index (c), add index (d); show create table t1; Table Create Table @@ -441,6 +434,7 @@ t3 CREATE TABLE `t3` ( KEY `c` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table t2 drop index b, add index (b); +ERROR 42000: Incorrect index name 'b' show create table t2; Table Create Table t2 CREATE TABLE `t2` ( @@ -451,8 +445,8 @@ t2 CREATE TABLE `t2` ( `e` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `dc` (`d`,`c`), - KEY `c` (`c`), KEY `b` (`b`), + KEY `c` (`c`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE CASCADE, CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`c`), CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`d`) REFERENCES `t4` (`d`) @@ -968,6 +962,7 @@ create index t1u on t1 (u(1)); drop table t1; set global innodb_file_per_table=0; set global innodb_file_format=Antelope; +set global innodb_file_format_check=Antelope; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; CREATE TABLE t1( diff --git a/storage/innobase/mysql-test/innodb-index.test b/storage/innobase/mysql-test/innodb-index.test index 42888ff3686..da1bc543ae9 100644 --- a/storage/innobase/mysql-test/innodb-index.test +++ b/storage/innobase/mysql-test/innodb-index.test @@ -1,5 +1,9 @@ -- source include/have_innodb.inc +let $MYSQLD_DATADIR= `select @@datadir`; + +let $innodb_file_format_check_orig=`select @@innodb_file_format_check`; + create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb; insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak'); commit; @@ -17,16 +21,6 @@ show create table t1; alter table t1 add index (b); show create table t1; -# Check how existing tables interfere with temporary tables. -CREATE TABLE `t1#1`(a INT PRIMARY KEY) ENGINE=InnoDB; - ---error 156 -alter table t1 add unique index (c), add index (d); -rename table `t1#1` to `t1#2`; ---error 156 -alter table t1 add unique index (c), add index (d); -drop table `t1#2`; - alter table t1 add unique index (c), add index (d); show create table t1; explain select * from t1 force index(c) order by c; @@ -137,6 +131,8 @@ show create table t4; --error ER_CANT_CREATE_TABLE alter table t3 add constraint dc foreign key (a) references t1(a); show create table t3; +# this should be fixed by MySQL (see Bug #51451) +--error ER_WRONG_NAME_FOR_INDEX alter table t2 drop index b, add index (b); show create table t2; --error ER_ROW_IS_REFERENCED_2 @@ -144,7 +140,9 @@ delete from t1; --error ER_CANT_DROP_FIELD_OR_KEY drop index dc on t4; # there is no foreign key dc on t3 ---replace_regex /'\.\/test\/#sql2-[0-9a-f-]*'/'#sql2-temporary'/ +--replace_regex /'[^']*test\/#sql2-[0-9a-f-]*'/'#sql2-temporary'/ +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLD_DATADIR ./ master-data/ '' --error ER_ERROR_ON_RENAME alter table t3 drop foreign key dc; alter table t4 drop foreign key dc; @@ -398,6 +396,7 @@ create index t1u on t1 (u(1)); drop table t1; eval set global innodb_file_per_table=$per_table; eval set global innodb_file_format=$format; +eval set global innodb_file_format_check=$format; # # Test to check whether CREATE INDEX handles implicit foreign key @@ -532,3 +531,10 @@ disconnect a; disconnect b; DROP TABLE t1; + +# +# restore environment to the state it was before this test execution +# + +-- disable_query_log +eval SET GLOBAL innodb_file_format_check=$innodb_file_format_check_orig; diff --git a/storage/innobase/mysql-test/innodb-master.opt b/storage/innobase/mysql-test/innodb-master.opt index 4901efb416c..72c88068345 100644 --- a/storage/innobase/mysql-test/innodb-master.opt +++ b/storage/innobase/mysql-test/innodb-master.opt @@ -1 +1 @@ ---binlog_cache_size=32768 --innodb_lock_wait_timeout=1 +--binlog_cache_size=32768 --loose_innodb_lock_wait_timeout=1 diff --git a/storage/innobase/mysql-test/innodb-semi-consistent-master.opt b/storage/innobase/mysql-test/innodb-semi-consistent-master.opt index e76299453d3..cb48f1aaf60 100644 --- a/storage/innobase/mysql-test/innodb-semi-consistent-master.opt +++ b/storage/innobase/mysql-test/innodb-semi-consistent-master.opt @@ -1 +1 @@ ---innodb_lock_wait_timeout=2 +--loose-innodb_lock_wait_timeout=2 diff --git a/storage/innobase/mysql-test/innodb-use-sys-malloc-master.opt b/storage/innobase/mysql-test/innodb-use-sys-malloc-master.opt index 889834add01..fc8582b5887 100644 --- a/storage/innobase/mysql-test/innodb-use-sys-malloc-master.opt +++ b/storage/innobase/mysql-test/innodb-use-sys-malloc-master.opt @@ -1,2 +1 @@ ---innodb-use-sys-malloc=true ---innodb-use-sys-malloc=true +--loose-innodb-use-sys-malloc=true diff --git a/storage/innobase/mysql-test/innodb-zip.result b/storage/innobase/mysql-test/innodb-zip.result index b26c4112826..21396d81ba8 100644 --- a/storage/innobase/mysql-test/innodb-zip.result +++ b/storage/innobase/mysql-test/innodb-zip.result @@ -196,15 +196,15 @@ drop table t1; set innodb_strict_mode = on; create table t1 (id int primary key) engine = innodb key_block_size = 0; ERROR HY000: Can't create table 'test.t1' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 0. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 0. Valid values are [1, 2, 4, 8, 16] Error 1005 Can't create table 'test.t1' (errno: 1478) create table t2 (id int primary key) engine = innodb key_block_size = 9; ERROR HY000: Can't create table 'test.t2' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] Error 1005 Can't create table 'test.t2' (errno: 1478) create table t3 (id int primary key) engine = innodb key_block_size = 1; create table t4 (id int primary key) engine = innodb key_block_size = 2; @@ -233,30 +233,30 @@ key_block_size = 8 row_format = compressed; create table t2 (id int primary key) engine = innodb key_block_size = 8 row_format = redundant; ERROR HY000: Can't create table 'test.t2' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t2' (errno: 1478) create table t3 (id int primary key) engine = innodb key_block_size = 8 row_format = compact; ERROR HY000: Can't create table 'test.t3' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t3' (errno: 1478) create table t4 (id int primary key) engine = innodb key_block_size = 8 row_format = dynamic; ERROR HY000: Can't create table 'test.t4' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t4' (errno: 1478) create table t5 (id int primary key) engine = innodb key_block_size = 8 row_format = default; ERROR HY000: Can't create table 'test.t5' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t5' (errno: 1478) SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; @@ -266,26 +266,26 @@ drop table t1; create table t1 (id int primary key) engine = innodb key_block_size = 9 row_format = redundant; ERROR HY000: Can't create table 'test.t1' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] -Error 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: cannot specify ROW_FORMAT = REDUNDANT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t1' (errno: 1478) create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = compact; ERROR HY000: Can't create table 'test.t2' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] -Error 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t2' (errno: 1478) create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = dynamic; ERROR HY000: Can't create table 'test.t2' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] -Error 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. +Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16] +Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE. Error 1005 Can't create table 'test.t2' (errno: 1478) SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; @@ -293,45 +293,45 @@ table_schema table_name row_format set global innodb_file_per_table = off; create table t1 (id int primary key) engine = innodb key_block_size = 1; ERROR HY000: Can't create table 'test.t1' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t1' (errno: 1478) create table t2 (id int primary key) engine = innodb key_block_size = 2; ERROR HY000: Can't create table 'test.t2' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t2' (errno: 1478) create table t3 (id int primary key) engine = innodb key_block_size = 4; ERROR HY000: Can't create table 'test.t3' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t3' (errno: 1478) create table t4 (id int primary key) engine = innodb key_block_size = 8; ERROR HY000: Can't create table 'test.t4' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t4' (errno: 1478) create table t5 (id int primary key) engine = innodb key_block_size = 16; ERROR HY000: Can't create table 'test.t5' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table 'test.t5' (errno: 1478) create table t6 (id int primary key) engine = innodb row_format = compressed; ERROR HY000: Can't create table 'test.t6' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. Error 1005 Can't create table 'test.t6' (errno: 1478) create table t7 (id int primary key) engine = innodb row_format = dynamic; ERROR HY000: Can't create table 'test.t7' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. Error 1005 Can't create table 'test.t7' (errno: 1478) create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; @@ -345,45 +345,45 @@ set global innodb_file_per_table = on; set global innodb_file_format = `0`; create table t1 (id int primary key) engine = innodb key_block_size = 1; ERROR HY000: Can't create table 'test.t1' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t1' (errno: 1478) create table t2 (id int primary key) engine = innodb key_block_size = 2; ERROR HY000: Can't create table 'test.t2' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t2' (errno: 1478) create table t3 (id int primary key) engine = innodb key_block_size = 4; ERROR HY000: Can't create table 'test.t3' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t3' (errno: 1478) create table t4 (id int primary key) engine = innodb key_block_size = 8; ERROR HY000: Can't create table 'test.t4' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t4' (errno: 1478) create table t5 (id int primary key) engine = innodb key_block_size = 16; ERROR HY000: Can't create table 'test.t5' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t5' (errno: 1478) create table t6 (id int primary key) engine = innodb row_format = compressed; ERROR HY000: Can't create table 'test.t6' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t6' (errno: 1478) create table t7 (id int primary key) engine = innodb row_format = dynamic; ERROR HY000: Can't create table 'test.t7' (errno: 1478) -show errors; +show warnings; Level Code Message -Error 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. Error 1005 Can't create table 'test.t7' (errno: 1478) create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; diff --git a/storage/innobase/mysql-test/innodb-zip.test b/storage/innobase/mysql-test/innodb-zip.test index 5bcd0e3c824..fdb9b89e37a 100644 --- a/storage/innobase/mysql-test/innodb-zip.test +++ b/storage/innobase/mysql-test/innodb-zip.test @@ -174,11 +174,11 @@ set innodb_strict_mode = on; --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 0; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9; -show errors; +show warnings; create table t3 (id int primary key) engine = innodb key_block_size = 1; @@ -204,22 +204,22 @@ key_block_size = 8 row_format = compressed; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 8 row_format = redundant; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 8 row_format = compact; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t4 (id int primary key) engine = innodb key_block_size = 8 row_format = dynamic; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t5 (id int primary key) engine = innodb key_block_size = 8 row_format = default; -show errors; +show warnings; SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; @@ -229,17 +229,17 @@ drop table t1; --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 9 row_format = redundant; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = compact; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = dynamic; -show errors; +show warnings; SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb'; @@ -249,25 +249,25 @@ set global innodb_file_per_table = off; --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 1; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 2; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 4; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t4 (id int primary key) engine = innodb key_block_size = 8; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t5 (id int primary key) engine = innodb key_block_size = 16; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t6 (id int primary key) engine = innodb row_format = compressed; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t7 (id int primary key) engine = innodb row_format = dynamic; -show errors; +show warnings; create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; @@ -281,25 +281,25 @@ set global innodb_file_format = `0`; --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 1; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 2; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 4; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t4 (id int primary key) engine = innodb key_block_size = 8; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t5 (id int primary key) engine = innodb key_block_size = 16; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t6 (id int primary key) engine = innodb row_format = compressed; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table t7 (id int primary key) engine = innodb row_format = dynamic; -show errors; +show warnings; create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; diff --git a/storage/innobase/mysql-test/innodb.result b/storage/innobase/mysql-test/innodb.result index bdae7633fd1..d7f4731436b 100644 --- a/storage/innobase/mysql-test/innodb.result +++ b/storage/innobase/mysql-test/innodb.result @@ -692,6 +692,9 @@ select count(*) from t1 where sca_pic is null; count(*) 2 alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); +ERROR 42000: Incorrect index name 'sca_pic' +alter table t1 drop index sca_pic; +alter table t1 add index sca_pic (cat_code, sca_pic); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 @@ -699,6 +702,9 @@ select count(*) from t1 where cat_code='E'; count(*) 0 alter table t1 drop index sca_pic, add index (sca_pic, cat_code); +ERROR 42000: Incorrect index name 'sca_pic' +alter table t1 drop index sca_pic; +alter table t1 add index (sca_pic, cat_code); select count(*) from t1 where sca_code='PD' and sca_pic is null; count(*) 1 @@ -1833,6 +1839,7 @@ show variables like "innodb_thread_sleep_delay"; Variable_name Value innodb_thread_sleep_delay 10000 set storage_engine=INNODB; +set session old_alter_table=1; drop table if exists t1,t2,t3; --- Testing varchar --- --- Testing varchar --- @@ -1970,7 +1977,7 @@ explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 13 const # Using where; Using index alter table t1 add unique(v); -ERROR 23000: Duplicate entry 'v' for key 'v_2' +ERROR 23000: Duplicate entry '{ ' for key 'v_2' alter table t1 add key(v); select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; qq @@ -2406,6 +2413,7 @@ select * from t1 where a=20 and b is null; a b 20 NULL drop table t1; +set session old_alter_table=0; create table t1 (v varchar(65530), key(v)); Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes @@ -3088,7 +3096,7 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction commit; drop table t1, t2, t3, t5, t6, t8, t9; CREATE TABLE t1 (DB_ROW_ID int) engine=innodb; -ERROR HY000: Can't create table 'test.t1' (errno: -1) +ERROR 42000: Incorrect column name 'DB_ROW_ID' CREATE TABLE t1 ( a BIGINT(20) NOT NULL, PRIMARY KEY (a) diff --git a/storage/innobase/mysql-test/innodb.test b/storage/innobase/mysql-test/innodb.test index f46a3a70b56..9f9766acd82 100644 --- a/storage/innobase/mysql-test/innodb.test +++ b/storage/innobase/mysql-test/innodb.test @@ -15,6 +15,8 @@ -- source include/have_innodb.inc +let $MYSQLD_DATADIR= `select @@datadir`; + # Save the original values of some variables in order to be able to # estimate how much they have changed during the tests. Previously this # test assumed that e.g. rows_deleted is 0 here and after deleting 23 @@ -425,11 +427,19 @@ INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca select count(*) from t1 where sca_code = 'PD'; select count(*) from t1 where sca_code <= 'PD'; select count(*) from t1 where sca_pic is null; +# this should be fixed by MySQL (see Bug #51451) +--error ER_WRONG_NAME_FOR_INDEX alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic); +alter table t1 drop index sca_pic; +alter table t1 add index sca_pic (cat_code, sca_pic); select count(*) from t1 where sca_code='PD' and sca_pic is null; select count(*) from t1 where cat_code='E'; +# this should be fixed by MySQL (see Bug #51451) +--error ER_WRONG_NAME_FOR_INDEX alter table t1 drop index sca_pic, add index (sca_pic, cat_code); +alter table t1 drop index sca_pic; +alter table t1 add index (sca_pic, cat_code); select count(*) from t1 where sca_code='PD' and sca_pic is null; select count(*) from t1 where sca_pic >= 'n'; select sca_pic from t1 where sca_pic is null; @@ -1375,7 +1385,10 @@ show variables like "innodb_thread_sleep_delay"; let $default=`select @@storage_engine`; set storage_engine=INNODB; +# this should be fixed by MySQL (see Bug #51451) +set session old_alter_table=1; source include/varchar.inc; +set session old_alter_table=0; # # Some errors/warnings on create @@ -1700,7 +1713,7 @@ set foreign_key_checks=0; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; # Embedded server doesn't chdir to data directory ---replace_result $MYSQLTEST_VARDIR . master-data/ '' +--replace_result $MYSQLD_DATADIR ./ master-data/ '' -- error 1025 rename table t3 to t1; set foreign_key_checks=1; @@ -2264,7 +2277,7 @@ disconnect j; drop table t1, t2, t3, t5, t6, t8, t9; # bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID" ---error 1005 +--error ER_WRONG_COLUMN_NAME CREATE TABLE t1 (DB_ROW_ID int) engine=innodb; # @@ -2340,7 +2353,7 @@ ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; # mysqltest first does replace_regex, then replace_result --replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/ # Embedded server doesn't chdir to data directory ---replace_result $MYSQLTEST_VARDIR . master-data/ '' +--replace_result $MYSQLD_DATADIR ./ master-data/ '' --error 1025 ALTER TABLE t2 MODIFY a INT NOT NULL; DELETE FROM t1; diff --git a/storage/innobase/mysql-test/innodb_bug21704.result b/storage/innobase/mysql-test/innodb_bug21704.result index b8e0b15d50d..ffbfa8a337e 100644 --- a/storage/innobase/mysql-test/innodb_bug21704.result +++ b/storage/innobase/mysql-test/innodb_bug21704.result @@ -25,8 +25,8 @@ ALTER TABLE t1 CHANGE a c INT; ERROR HY000: Error on rename of '#sql-temporary' to './test/t1' (errno: 150) # Ensure that online column rename works. ALTER TABLE t1 CHANGE b c INT; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 # Test renaming the column in the referencing table @@ -34,8 +34,8 @@ ALTER TABLE t2 CHANGE a c INT; ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150) # Ensure that online column rename works. ALTER TABLE t2 CHANGE b c INT; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 # Test with self-referential constraints @@ -45,8 +45,8 @@ ALTER TABLE t3 CHANGE b d INT; ERROR HY000: Error on rename of '#sql-temporary' to './test/t3' (errno: 150) # Ensure that online column rename works. ALTER TABLE t3 CHANGE c d INT; -affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 # Cleanup. diff --git a/storage/innobase/mysql-test/innodb_bug38231.result b/storage/innobase/mysql-test/innodb_bug38231.result new file mode 100644 index 00000000000..2f909779755 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug38231.result @@ -0,0 +1,11 @@ +SET storage_engine=InnoDB; +INSERT INTO bug38231 VALUES (1), (10), (300); +SET autocommit=0; +SELECT * FROM bug38231 FOR UPDATE; +a +1 +10 +300 +TRUNCATE TABLE bug38231; +COMMIT; +DROP TABLE bug38231; diff --git a/storage/innobase/mysql-test/innodb_bug38231.test b/storage/innobase/mysql-test/innodb_bug38231.test new file mode 100644 index 00000000000..1611cb56203 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug38231.test @@ -0,0 +1,112 @@ +# +# Bug#38231 Innodb crash in lock_reset_all_on_table() on TRUNCATE + LOCK / UNLOCK +# http://bugs.mysql.com/38231 +# + +-- source include/have_innodb.inc + +SET storage_engine=InnoDB; + +# we care only that the following SQL commands do not crash the server +-- disable_query_log +-- disable_result_log + +DROP TABLE IF EXISTS bug38231; +CREATE TABLE bug38231 (a INT); + +-- connect (con1,localhost,root,,) +-- connect (con2,localhost,root,,) +-- connect (con3,localhost,root,,) + +-- connection con1 +SET autocommit=0; +LOCK TABLE bug38231 WRITE; + +-- connection con2 +SET autocommit=0; +-- send +LOCK TABLE bug38231 WRITE; + +# When con1 does UNLOCK below this will release either con2 or con3 which are +# both waiting on LOCK. At the end we must first --reap and UNLOCK the +# connection that has been released, otherwise it will wait forever. We assume +# that the released connection will be the first one that has gained the LOCK, +# thus we force the order here - con2 does LOCK first, then con3. In other +# words we wait for LOCK from con2 above to be exected before doing LOCK in +# con3. +-- connection con1 +let $wait_condition = + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE info = 'LOCK TABLE bug38231 WRITE'; +-- source include/wait_condition.inc +# the above enables query log, re-disable it +-- disable_query_log + +-- connection con3 +SET autocommit=0; +-- send +LOCK TABLE bug38231 WRITE; + +-- connection default +-- send +TRUNCATE TABLE bug38231; + +-- connection con1 +# Wait for TRUNCATE and the other two LOCKs to be executed; without this, +# sometimes UNLOCK executes before them. We assume there are no other +# sessions executing at the same time with the same SQL commands. +let $wait_condition = + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE info = 'TRUNCATE TABLE bug38231'; +-- source include/wait_condition.inc +let $wait_condition = + SELECT COUNT(*) = 2 FROM information_schema.processlist + WHERE info = 'LOCK TABLE bug38231 WRITE'; +-- source include/wait_condition.inc +# the above enables query log, re-disable it +-- disable_query_log + +# this crashes the server if the bug is present +UNLOCK TABLES; + +# clean up + +-- connection con2 +-- reap +UNLOCK TABLES; + +-- connection con3 +-- reap +UNLOCK TABLES; + +-- connection default +-- reap + +-- disconnect con1 +-- disconnect con2 +-- disconnect con3 + +# test that TRUNCATE works with with row-level locks + +-- enable_query_log +-- enable_result_log + +INSERT INTO bug38231 VALUES (1), (10), (300); + +-- connect (con4,localhost,root,,) + +-- connection con4 +SET autocommit=0; +SELECT * FROM bug38231 FOR UPDATE; + +-- connection default +TRUNCATE TABLE bug38231; + +-- connection con4 +COMMIT; + +-- connection default + +-- disconnect con4 + +DROP TABLE bug38231; diff --git a/storage/innobase/mysql-test/innodb_bug39438-master.opt b/storage/innobase/mysql-test/innodb_bug39438-master.opt new file mode 100644 index 00000000000..43fac202fd4 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug39438-master.opt @@ -0,0 +1 @@ +--innodb-file-per-table=1 diff --git a/storage/innobase/mysql-test/innodb_bug39438.result b/storage/innobase/mysql-test/innodb_bug39438.result new file mode 100644 index 00000000000..195775f74c8 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug39438.result @@ -0,0 +1 @@ +SET storage_engine=InnoDB; diff --git a/storage/innobase/mysql-test/innodb_bug39438.test b/storage/innobase/mysql-test/innodb_bug39438.test new file mode 100644 index 00000000000..52302871beb --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug39438.test @@ -0,0 +1,51 @@ +# +# Bug#39438 Testcase for Bug#39436 crashes on 5.1 in fil_space_get_latch +# http://bugs.mysql.com/39438 +# +# This test must be run with innodb_file_per_table=1 because the crash +# only occurs if that option is turned on and DISCARD TABLESPACE only +# works with innodb_file_per_table. +# + +-- source include/have_innodb.inc + +SET storage_engine=InnoDB; + +# we care only that the following SQL commands do not crash the server +-- disable_query_log +-- disable_result_log + +DROP TABLE IF EXISTS bug39438; + +CREATE TABLE bug39438 (id INT) ENGINE=INNODB; + +# remove: XXX Uncomment the following ALTER and remove those lines after +# remove: applying the patch. +# remove: Obviously this test is useless without this ALTER command, +# remove: but it causes warnings to be printed by mysqld and the whole +# remove: mysql-test suite fails at the end (returns non-zero). Please +# remove: apply this patch to the mysql source tree, remove those lines +# remove: and uncomment the following ALTER. We do not care about the +# remove: warnings, this test is to ensure mysqld does not crash. +# remove: === modified file 'mysql-test/lib/mtr_report.pl' +# remove: --- mysql-test/lib/mtr_report.pl 2008-08-12 10:26:23 +0000 +# remove: +++ mysql-test/lib/mtr_report.pl 2008-10-01 11:57:41 +0000 +# remove: @@ -412,7 +412,10 @@ +# remove: +# remove: # When trying to set lower_case_table_names = 2 +# remove: # on a case sensitive file system. Bug#37402. +# remove: - /lower_case_table_names was set to 2, even though your the file system '.*' is case sensitive. Now setting lower_case_table_names to 0 to avoid future problems./ +# remove: + /lower_case_table_names was set to 2, even though your the file system '.*' is case sensitive. Now setting lower_case_table_names to 0 to avoid future problems./ or +# remove: + +# remove: + # this test is expected to print warnings +# remove: + ($testname eq 'main.innodb_bug39438') +# remove: ) +# remove: { +# remove: next; # Skip these lines +# remove: +#ALTER TABLE bug39438 DISCARD TABLESPACE; + +# this crashes the server if the bug is present +SHOW TABLE STATUS; + +DROP TABLE bug39438; diff --git a/storage/innobase/mysql-test/innodb_bug42101-nonzero-master.opt b/storage/innobase/mysql-test/innodb_bug42101-nonzero-master.opt index d71dbe17d5b..455d66a06b8 100644 --- a/storage/innobase/mysql-test/innodb_bug42101-nonzero-master.opt +++ b/storage/innobase/mysql-test/innodb_bug42101-nonzero-master.opt @@ -1 +1 @@ ---innodb_commit_concurrency=1 +--loose_innodb_commit_concurrency=1 diff --git a/storage/innobase/mysql-test/innodb_bug44369.result b/storage/innobase/mysql-test/innodb_bug44369.result index e4b84ecac19..ff25c774aa2 100644 --- a/storage/innobase/mysql-test/innodb_bug44369.result +++ b/storage/innobase/mysql-test/innodb_bug44369.result @@ -1,14 +1,6 @@ create table bug44369 (DB_ROW_ID int) engine=innodb; -ERROR HY000: Can't create table 'test.bug44369' (errno: -1) +ERROR 42000: Incorrect column name 'DB_ROW_ID' create table bug44369 (db_row_id int) engine=innodb; -ERROR HY000: Can't create table 'test.bug44369' (errno: -1) -show errors; -Level Code Message -Error 1005 Error creating table 'test/bug44369' with column name 'db_row_id'. 'db_row_id' is a reserved name. Please try to re-create the table with a different column name. -Error 1005 Can't create table 'test.bug44369' (errno: -1) +ERROR 42000: Incorrect column name 'db_row_id' create table bug44369 (db_TRX_Id int) engine=innodb; -ERROR HY000: Can't create table 'test.bug44369' (errno: -1) -show errors; -Level Code Message -Error 1005 Error creating table 'test/bug44369' with column name 'db_TRX_Id'. 'db_TRX_Id' is a reserved name. Please try to re-create the table with a different column name. -Error 1005 Can't create table 'test.bug44369' (errno: -1) +ERROR 42000: Incorrect column name 'db_TRX_Id' diff --git a/storage/innobase/mysql-test/innodb_bug44369.test b/storage/innobase/mysql-test/innodb_bug44369.test index 495059eb5e6..f5d85cd5815 100644 --- a/storage/innobase/mysql-test/innodb_bug44369.test +++ b/storage/innobase/mysql-test/innodb_bug44369.test @@ -6,16 +6,12 @@ --source include/have_innodb.inc # This create table operation should fail. ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_COLUMN_NAME create table bug44369 (DB_ROW_ID int) engine=innodb; # This create should fail as well ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_COLUMN_NAME create table bug44369 (db_row_id int) engine=innodb; -show errors; - ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_COLUMN_NAME create table bug44369 (db_TRX_Id int) engine=innodb; - -show errors; diff --git a/storage/innobase/mysql-test/innodb_bug44571.result b/storage/innobase/mysql-test/innodb_bug44571.result index 36374edcb3e..7ee7820a02d 100644 --- a/storage/innobase/mysql-test/innodb_bug44571.result +++ b/storage/innobase/mysql-test/innodb_bug44571.result @@ -2,8 +2,7 @@ CREATE TABLE bug44571 (foo INT) ENGINE=InnoDB; ALTER TABLE bug44571 CHANGE foo bar INT; ALTER TABLE bug44571 ADD INDEX bug44571b (foo); ERROR 42000: Key column 'foo' doesn't exist in table -ALTER TABLE bug44571 ADD INDEX bug44571b (bar); -ERROR HY000: Incorrect key file for table 'bug44571'; try to repair it -CREATE INDEX bug44571b ON bug44571 (bar); -ERROR HY000: Incorrect key file for table 'bug44571'; try to repair it +ALTER TABLE bug44571 ADD INDEX bug44571c (bar); +DROP INDEX bug44571c ON bug44571; +CREATE INDEX bug44571c ON bug44571 (bar); DROP TABLE bug44571; diff --git a/storage/innobase/mysql-test/innodb_bug44571.test b/storage/innobase/mysql-test/innodb_bug44571.test index 685463ceff9..91b6722d8af 100644 --- a/storage/innobase/mysql-test/innodb_bug44571.test +++ b/storage/innobase/mysql-test/innodb_bug44571.test @@ -1,17 +1,22 @@ # # Bug#44571 InnoDB Plugin crashes on ADD INDEX # http://bugs.mysql.com/44571 +# Please also refer to related fix in +# http://bugs.mysql.com/47621 # -- source include/have_innodb.inc CREATE TABLE bug44571 (foo INT) ENGINE=InnoDB; ALTER TABLE bug44571 CHANGE foo bar INT; +# Create index with the old column name will fail, +# because the CHANGE foo bar is successful. And +# the column name change would communicate to +# InnoDB with the fix from bug #47621 -- error ER_KEY_COLUMN_DOES_NOT_EXITS ALTER TABLE bug44571 ADD INDEX bug44571b (foo); -# The following will fail, because the CHANGE foo bar was -# not communicated to InnoDB. ---error ER_NOT_KEYFILE -ALTER TABLE bug44571 ADD INDEX bug44571b (bar); ---error ER_NOT_KEYFILE -CREATE INDEX bug44571b ON bug44571 (bar); +# The following create indexes should succeed, +# indirectly confirm the CHANGE foo bar is successful. +ALTER TABLE bug44571 ADD INDEX bug44571c (bar); +DROP INDEX bug44571c ON bug44571; +CREATE INDEX bug44571c ON bug44571 (bar); DROP TABLE bug44571; diff --git a/storage/innobase/mysql-test/innodb_bug46000.result b/storage/innobase/mysql-test/innodb_bug46000.result index ccff888a48d..c8e3db8d641 100644 --- a/storage/innobase/mysql-test/innodb_bug46000.result +++ b/storage/innobase/mysql-test/innodb_bug46000.result @@ -1,17 +1,19 @@ create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; -ERROR HY000: Can't create table 'test.bug46000' (errno: -1) +ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX' create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; -ERROR HY000: Can't create table 'test.bug46000' (errno: -1) -show errors; +ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX' +show warnings; Level Code Message -Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Warning 1280 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Error 1280 Incorrect index name 'GEN_CLUST_INDEX' Error 1005 Can't create table 'test.bug46000' (errno: -1) create table bug46000(id int) engine=innodb; create index GEN_CLUST_INDEX on bug46000(id); -ERROR HY000: Can't create table '#sql-temporary' (errno: -1) -show errors; +ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX' +show warnings; Level Code Message -Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. -Error 1005 Can't create table '#sql-temporary' (errno: -1) +Warning 1280 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Error 1280 Incorrect index name 'GEN_CLUST_INDEX' +Error 1030 Got error -1 from storage engine create index idx on bug46000(id); drop table bug46000; diff --git a/storage/innobase/mysql-test/innodb_bug46000.test b/storage/innobase/mysql-test/innodb_bug46000.test index 80c18c58ef0..5a3c666326e 100644 --- a/storage/innobase/mysql-test/innodb_bug46000.test +++ b/storage/innobase/mysql-test/innodb_bug46000.test @@ -7,24 +7,22 @@ # This 'create table' operation should fail because of # using the reserve name as its index name. ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_NAME_FOR_INDEX create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; # Mixed upper/lower case of the reserved key words ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_NAME_FOR_INDEX create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; -show errors; +show warnings; create table bug46000(id int) engine=innodb; # This 'create index' operation should fail. ---replace_regex /'[^']*test.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_NAME_FOR_INDEX create index GEN_CLUST_INDEX on bug46000(id); ---replace_regex /'[^']*test.#sql-[0-9a-f_]*'/'#sql-temporary'/ -show errors; +show warnings; # This 'create index' operation should succeed, no # temp table left from last failed create index diff --git a/storage/innobase/mysql-test/innodb_bug47621.result b/storage/innobase/mysql-test/innodb_bug47621.result new file mode 100644 index 00000000000..c5f56c09788 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug47621.result @@ -0,0 +1,21 @@ +CREATE TABLE bug47621 (salesperson INT) ENGINE=InnoDB; +ALTER TABLE bug47621 CHANGE salesperson sales_acct_id INT; +create index orgs on bug47621(sales_acct_id); +ALTER TABLE bug47621 CHANGE sales_acct_id salesperson INT; +drop table bug47621; +CREATE TABLE bug47621_sale ( +salesperson INT, +PRIMARY KEY(salesperson)) engine = innodb; +CREATE TABLE bug47621_shirt( +id SMALLINT, +owner INT, +FOREIGN KEY(owner) +references bug47621_sale(salesperson) ON DELETE RESTRICT) +engine = innodb; +insert into bug47621_sale values(9); +insert into bug47621_shirt values(1, 9); +ALTER TABLE bug47621_shirt CHANGE id new_id INT; +drop table bug47621_shirt; +ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; +ALTER TABLE bug47621_sale ADD INDEX idx (sales_acct_id); +drop table bug47621_sale; diff --git a/storage/innobase/mysql-test/innodb_bug47621.test b/storage/innobase/mysql-test/innodb_bug47621.test new file mode 100644 index 00000000000..4863cc6bba1 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug47621.test @@ -0,0 +1,57 @@ +# This is the test for bug #47621, column rename operation should +# not result in column definition inconsistency between MySQL and +# InnoDB + +--source include/have_innodb.inc + +CREATE TABLE bug47621 (salesperson INT) ENGINE=InnoDB; + +# Change the column name +ALTER TABLE bug47621 CHANGE salesperson sales_acct_id INT; + +# If there is inconsistency of column name definition +# in MySQL or InnoDB, following create index would fail +create index orgs on bug47621(sales_acct_id); + +# Change the column name back with the index defined on it. +ALTER TABLE bug47621 CHANGE sales_acct_id salesperson INT; + +drop table bug47621; + +CREATE TABLE bug47621_sale ( + salesperson INT, + PRIMARY KEY(salesperson)) engine = innodb; + +CREATE TABLE bug47621_shirt( + id SMALLINT, + owner INT, + FOREIGN KEY(owner) + references bug47621_sale(salesperson) ON DELETE RESTRICT) + engine = innodb; + +insert into bug47621_sale values(9); + +insert into bug47621_shirt values(1, 9); + +# Any rename operation on columns involved in a reference constraint will +# fail, as it will be rejected by InnoDB row_rename_table_for_mysql(). +# In above example, any rename on column "salesperson" for table +# "bug47621_sale", or on column "owner" for table "bug47621_shirt will +# be blocked. We do not put such rename in the test since InnoDB error +# message will be printed in the error log, and result in test failure. +# +# ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; + +# Any rename on columns not involved in the foreign key constraint +# could still proceed +ALTER TABLE bug47621_shirt CHANGE id new_id INT; + +# Referencing table dropped, the rename operation on related columns +# could proceed +drop table bug47621_shirt; + +ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; + +ALTER TABLE bug47621_sale ADD INDEX idx (sales_acct_id); + +drop table bug47621_sale; diff --git a/storage/innobase/mysql-test/innodb_bug47622.result b/storage/innobase/mysql-test/innodb_bug47622.result new file mode 100644 index 00000000000..f5d13711c52 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug47622.result @@ -0,0 +1,23 @@ +CREATE TABLE bug47622( +`rule_key` int(11) NOT NULL DEFAULT '0', +`seq` smallint(6) NOT NULL DEFAULT '0', +`action` smallint(6) NOT NULL DEFAULT '0', +`arg_id` smallint(6) DEFAULT NULL, +`else_ind` TINYINT NOT NULL, +KEY IDX_A (`arg_id`) +) ENGINE=InnoDB; +ALTER TABLE bug47622 ADD UNIQUE IDX_B (rule_key,else_ind,seq,action,arg_id); +drop index IDX_B on bug47622; +create index idx on bug47622(seq, arg_id); +ALTER TABLE bug47622 ADD UNIQUE IDX_X (rule_key,else_ind,seq,action); +drop table bug47622; +CREATE TABLE bug47622 ( +`a` int(11) NOT NULL, +`b` int(11) DEFAULT NULL, +`c` char(10) DEFAULT NULL, +`d` varchar(20) DEFAULT NULL, +PRIMARY KEY (`a`), +KEY `b` (`b`) +) ENGINE=InnoDB; +alter table bug47622 add unique index (c), add index (d); +drop table bug47622; diff --git a/storage/innobase/mysql-test/innodb_bug47622.test b/storage/innobase/mysql-test/innodb_bug47622.test new file mode 100644 index 00000000000..9cf9d0e531b --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug47622.test @@ -0,0 +1,55 @@ +# This is the test for bug 47622. There could be index +# metadata sequence mismatch between MySQL and Innodb +# after creating index through FIC interfaces. +# We resolve the problem by sync the index sequence +# up when opening the table. + +--source include/have_innodb.inc + +connect (a,localhost,root,,); +connect (b,localhost,root,,); + +# Create a table with a non-unique index +CREATE TABLE bug47622( + `rule_key` int(11) NOT NULL DEFAULT '0', + `seq` smallint(6) NOT NULL DEFAULT '0', + `action` smallint(6) NOT NULL DEFAULT '0', + `arg_id` smallint(6) DEFAULT NULL, + `else_ind` TINYINT NOT NULL, + KEY IDX_A (`arg_id`) +) ENGINE=InnoDB; + +connection a; + +# A subsequent creating unique index should not trigger +# any error message. Unique index would be ranked ahead +# of regular index. +ALTER TABLE bug47622 ADD UNIQUE IDX_B (rule_key,else_ind,seq,action,arg_id); + +drop index IDX_B on bug47622; + +# In another connection, create additional set of normal +# index and unique index. Again, unique index would be ranked +# ahead of regular index. +connection b; +create index idx on bug47622(seq, arg_id); + +ALTER TABLE bug47622 ADD UNIQUE IDX_X (rule_key,else_ind,seq,action); + +drop table bug47622; + +# Create a table with one Primary key and a non-unique key +CREATE TABLE bug47622 ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `d` varchar(20) DEFAULT NULL, + PRIMARY KEY (`a`), + KEY `b` (`b`) +) ENGINE=InnoDB; + +# Add two index with one unique and one non-unique. +# Index sequence is "PRIMARY", "c", "b" and "d" +alter table bug47622 add unique index (c), add index (d); + +drop table bug47622; diff --git a/storage/innobase/mysql-test/innodb_bug47777.result b/storage/innobase/mysql-test/innodb_bug47777.result new file mode 100644 index 00000000000..fbba47edcfc --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug47777.result @@ -0,0 +1,13 @@ +create table bug47777(c2 linestring not null, primary key (c2(1))) engine=innodb; +insert into bug47777 values (geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)')); +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +count(*) +1 +update bug47777 set c2=GeomFromText('POINT(1 1)'); +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +count(*) +0 +select count(*) from bug47777 where c2 = GeomFromText('POINT(1 1)'); +count(*) +1 +drop table bug47777; diff --git a/storage/innobase/mysql-test/innodb_bug47777.test b/storage/innobase/mysql-test/innodb_bug47777.test new file mode 100644 index 00000000000..8f2985b2cf0 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug47777.test @@ -0,0 +1,24 @@ +# This is the test for bug 47777. GEOMETRY +# data is treated as BLOB data in innodb. +# Consequently, its key value generation/storing +# should follow the process for the BLOB +# datatype as well. + +--source include/have_innodb.inc + +create table bug47777(c2 linestring not null, primary key (c2(1))) engine=innodb; + +insert into bug47777 values (geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)')); + +# Verify correct row get inserted. +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); + +# Update table bug47777 should be successful. +update bug47777 set c2=GeomFromText('POINT(1 1)'); + +# Verify the row get updated successfully. The original +# c2 value should be changed to GeomFromText('POINT(1 1)'). +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +select count(*) from bug47777 where c2 = GeomFromText('POINT(1 1)'); + +drop table bug47777; diff --git a/storage/innobase/mysql-test/innodb_bug51378.result b/storage/innobase/mysql-test/innodb_bug51378.result new file mode 100644 index 00000000000..a3ca73c16a9 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug51378.result @@ -0,0 +1,66 @@ +create table bug51378 ( +col1 int not null, +col2 blob not null, +col3 time not null) engine = innodb; +create unique index idx on bug51378(col1, col2(31)); +alter table bug51378 add unique index idx2(col1, col2(31)); +create unique index idx3 on bug51378(col1, col3); +SHOW CREATE TABLE bug51378; +Table Create Table +bug51378 CREATE TABLE `bug51378` ( + `col1` int(11) NOT NULL, + `col2` blob NOT NULL, + `col3` time NOT NULL, + UNIQUE KEY `idx3` (`col1`,`col3`), + UNIQUE KEY `idx` (`col1`,`col2`(31)), + UNIQUE KEY `idx2` (`col1`,`col2`(31)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop index idx3 on bug51378; +SHOW CREATE TABLE bug51378; +Table Create Table +bug51378 CREATE TABLE `bug51378` ( + `col1` int(11) NOT NULL, + `col2` blob NOT NULL, + `col3` time NOT NULL, + UNIQUE KEY `idx` (`col1`,`col2`(31)), + UNIQUE KEY `idx2` (`col1`,`col2`(31)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +alter table bug51378 add primary key idx3(col1, col2(31)); +SHOW CREATE TABLE bug51378; +Table Create Table +bug51378 CREATE TABLE `bug51378` ( + `col1` int(11) NOT NULL, + `col2` blob NOT NULL, + `col3` time NOT NULL, + PRIMARY KEY (`col1`,`col2`(31)), + UNIQUE KEY `idx` (`col1`,`col2`(31)), + UNIQUE KEY `idx2` (`col1`,`col2`(31)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table bug51378; +create table bug51378 ( +col1 int not null, +col2 blob not null, +col3 time not null, primary key(col1, col2(31))) engine = innodb; +create unique index idx on bug51378(col1, col2(31)); +SHOW CREATE TABLE bug51378; +Table Create Table +bug51378 CREATE TABLE `bug51378` ( + `col1` int(11) NOT NULL, + `col2` blob NOT NULL, + `col3` time NOT NULL, + PRIMARY KEY (`col1`,`col2`(31)), + UNIQUE KEY `idx` (`col1`,`col2`(31)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table bug51378; +create table bug51378 ( +col1 int not null, +col2 int ) engine = innodb; +create unique index idx on bug51378(col1, col2); +SHOW CREATE TABLE bug51378; +Table Create Table +bug51378 CREATE TABLE `bug51378` ( + `col1` int(11) NOT NULL, + `col2` int(11) DEFAULT NULL, + UNIQUE KEY `idx` (`col1`,`col2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table bug51378; diff --git a/storage/innobase/mysql-test/innodb_bug51378.test b/storage/innobase/mysql-test/innodb_bug51378.test new file mode 100644 index 00000000000..8f7b0b9605a --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug51378.test @@ -0,0 +1,77 @@ +# This is the test for bug 51378. Unique index created +# through "create index" and "alter table add unique index" +# interfaces should not be treated as primary index if indexed +# columns contain one or more column prefix(es) (only prefix/part of +# the column is indexed) +# On the other hand, if there is a unique index covers all +# columns of a table, and they are non-null columns, and +# full length of the column are indexed, then this index +# will be created as primary index +# Following queries test various scenario, no mismatch +# error message should be printed. +--source include/have_innodb.inc + +# Create a table contains a BLOB column +create table bug51378 ( + col1 int not null, + col2 blob not null, + col3 time not null) engine = innodb; + +# Create following unique indexes on 'col1' and 'col2(31)' +# of the table, the index should not be treated as primary +# key because it indexes only first 31 bytes of col2. +# Thus it contains "column prefix", and will not be +# upgraded to primary index. +# There should not be mismatch message printed in the +# errorlog +create unique index idx on bug51378(col1, col2(31)); + +alter table bug51378 add unique index idx2(col1, col2(31)); + +# Unique index on 'col1' and 'col3' will be created as primary index, +# since the index does not contain column prefix +create unique index idx3 on bug51378(col1, col3); + +# Show create table would show idx3 created as unique index, internally, +# idx3 is treated as primary index both by MySQL and Innodb +SHOW CREATE TABLE bug51378; + +# "GEN_CLUST_INDEX" will be re-created as default primary index +# after idx3 is dropped +drop index idx3 on bug51378; + +SHOW CREATE TABLE bug51378; + +# Or we can add the primary key through alter table interfaces +alter table bug51378 add primary key idx3(col1, col2(31)); + +SHOW CREATE TABLE bug51378; + +drop table bug51378; + +# Or we can create such primary key through create table interfaces +create table bug51378 ( + col1 int not null, + col2 blob not null, + col3 time not null, primary key(col1, col2(31))) engine = innodb; + +# Unique index on one or more column prefix(es) will be created +# as non-cluster index +create unique index idx on bug51378(col1, col2(31)); + +SHOW CREATE TABLE bug51378; + +drop table bug51378; + +# If a table has a NULLABLE column, unique index on it will not +# be treated as primary index. +create table bug51378 ( + col1 int not null, + col2 int ) engine = innodb; + +# This will be created as non-cluster index since col2 is nullable +create unique index idx on bug51378(col1, col2); + +SHOW CREATE TABLE bug51378; + +drop table bug51378; diff --git a/storage/innobase/mysql-test/innodb_bug51920.result b/storage/innobase/mysql-test/innodb_bug51920.result new file mode 100644 index 00000000000..4c2ec3e01e5 --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug51920.result @@ -0,0 +1,13 @@ +CREATE TABLE bug51920 (i INT) ENGINE=InnoDB; +INSERT INTO bug51920 VALUES (1); +BEGIN; +SELECT * FROM bug51920 FOR UPDATE; +i +1 +UPDATE bug51920 SET i=2; +SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE INFO="UPDATE bug51920 SET i=2" +INTO @thread_id; +KILL @thread_id; +ERROR 70100: Query execution was interrupted +DROP TABLE bug51920; diff --git a/storage/innobase/mysql-test/innodb_bug51920.test b/storage/innobase/mysql-test/innodb_bug51920.test new file mode 100644 index 00000000000..05c884134be --- /dev/null +++ b/storage/innobase/mysql-test/innodb_bug51920.test @@ -0,0 +1,39 @@ +# +# Bug #51920: InnoDB connections in lock wait ignore KILL until timeout +# +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +CREATE TABLE bug51920 (i INT) ENGINE=InnoDB; +INSERT INTO bug51920 VALUES (1); + +BEGIN; +SELECT * FROM bug51920 FOR UPDATE; + +connect (con1,localhost,root,,); + +connection con1; +--send +UPDATE bug51920 SET i=2; + +connection default; +let $wait_condition = + SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE INFO="UPDATE bug51920 SET i=2"; +-- source include/wait_condition.inc + +SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE INFO="UPDATE bug51920 SET i=2" +INTO @thread_id; + +KILL @thread_id; +let $wait_condition = + SELECT COUNT(*)=0 FROM information_schema.processlist WHERE ID=@thread_id; +-- source include/wait_condition.inc + +connection con1; +-- error ER_QUERY_INTERRUPTED +reap; +connection default; +DROP TABLE bug51920; +-- disconnect con1 diff --git a/storage/innobase/mysql-test/innodb_file_format.result b/storage/innobase/mysql-test/innodb_file_format.result index 8e9a317308b..86d60706084 100644 --- a/storage/innobase/mysql-test/innodb_file_format.result +++ b/storage/innobase/mysql-test/innodb_file_format.result @@ -30,8 +30,6 @@ select @@innodb_file_format_check; @@innodb_file_format_check Barracuda set global innodb_file_format_check=default; -Warnings: -Warning 1210 Ignoring SET innodb_file_format=on select @@innodb_file_format_check; @@innodb_file_format_check Barracuda diff --git a/storage/innobase/mysql-test/innodb_information_schema.test b/storage/innobase/mysql-test/innodb_information_schema.test index eaed653854a..fc1d38d8d14 100644 --- a/storage/innobase/mysql-test/innodb_information_schema.test +++ b/storage/innobase/mysql-test/innodb_information_schema.test @@ -109,14 +109,18 @@ SELECT * FROM ```t'\"_str` WHERE c1 = '3' FOR UPDATE; -- send SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE; -# Give time to the above 2 queries to execute before continuing. -# Without this sleep it sometimes happens that the SELECT from innodb_locks -# executes before some of them, resulting in less than expected number -# of rows being selected from innodb_locks. --- sleep 0.1 - -- enable_result_log -- connection con_verify_innodb_locks +# Wait for the above queries to execute before continuing. +# Without this, it sometimes happens that the SELECT from innodb_locks +# executes before some of them, resulting in less than expected number +# of rows being selected from innodb_locks. If there is a bug and there +# are no 14 rows in innodb_locks then this test will fail with timeout. +let $count = 14; +let $table = INFORMATION_SCHEMA.INNODB_LOCKS; +-- source include/wait_until_rows_count.inc +# the above enables the query log, re-disable it +-- disable_query_log SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data; diff --git a/storage/innobase/mysql-test/patches/innodb-index.diff b/storage/innobase/mysql-test/patches/innodb-index.diff deleted file mode 100644 index 0b008c96f25..00000000000 --- a/storage/innobase/mysql-test/patches/innodb-index.diff +++ /dev/null @@ -1,62 +0,0 @@ -This part of the innodb-index test causes mysqld to print some warnings -and subsequently the whole mysql-test suite to fail. - -A permanent solution is probably to remove the printouts from the source -code or to somehow tell the mysql-test suite that warnings are expected. -Currently we simply do not execute the problematic tests. Please -coordinate a permanent solution with Marko, who added those tests. - -This cannot be proposed to MySQL because it touches files that are not -in the MySQL source repository. - -Index: storage/innobase/mysql-test/innodb-index.result -=================================================================== ---- storage/innobase/mysql-test/innodb-index.result (revision 2870) -+++ storage/innobase/mysql-test/innodb-index.result (working copy) -@@ -43,19 +43,12 @@ t1 CREATE TABLE `t1` ( - `b` int(11) DEFAULT NULL, - `c` char(10) NOT NULL, - `d` varchar(20) DEFAULT NULL, - KEY `d2` (`d`), - KEY `b` (`b`) - ) ENGINE=InnoDB DEFAULT CHARSET=latin1 --CREATE TABLE `t1#1`(a INT PRIMARY KEY) ENGINE=InnoDB; --alter table t1 add unique index (c), add index (d); --ERROR HY000: Table 'test.t1#1' already exists --rename table `t1#1` to `t1#2`; --alter table t1 add unique index (c), add index (d); --ERROR HY000: Table 'test.t1#2' already exists --drop table `t1#2`; - alter table t1 add unique index (c), add index (d); - show create table t1; - Table Create Table - t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, -Index: storage/innobase/mysql-test/innodb-index.test -=================================================================== ---- storage/innobase/mysql-test/innodb-index.test (revision 2870) -+++ storage/innobase/mysql-test/innodb-index.test (working copy) -@@ -14,22 +14,12 @@ select * from t1 force index (d2) order - --error ER_DUP_ENTRY - alter table t1 add unique index (b); - show create table t1; - alter table t1 add index (b); - show create table t1; - --# Check how existing tables interfere with temporary tables. --CREATE TABLE `t1#1`(a INT PRIMARY KEY) ENGINE=InnoDB; -- ----error 156 --alter table t1 add unique index (c), add index (d); --rename table `t1#1` to `t1#2`; ----error 156 --alter table t1 add unique index (c), add index (d); --drop table `t1#2`; -- - alter table t1 add unique index (c), add index (d); - show create table t1; - explain select * from t1 force index(c) order by c; - alter table t1 add primary key (a), drop index c; - show create table t1; - --error ER_MULTIPLE_PRI_KEY diff --git a/storage/innobase/mysql-test/patches/innodb_change_buffering_basic.diff b/storage/innobase/mysql-test/patches/innodb_change_buffering_basic.diff new file mode 100644 index 00000000000..bfa1609a97c --- /dev/null +++ b/storage/innobase/mysql-test/patches/innodb_change_buffering_basic.diff @@ -0,0 +1,60 @@ +--- mysql-test/suite/sys_vars/t/innodb_change_buffering_basic.test.orig Mon Mar 15 16:15:22 2010 ++++ mysql-test/suite/sys_vars/t/innodb_change_buffering_basic.test Fri Mar 19 01:19:09 2010 +@@ -11,8 +11,8 @@ + # + # exists as global only + # +---echo Valid values are 'inserts' and 'none' +-select @@global.innodb_change_buffering in ('inserts', 'none'); ++--echo Valid values are 'inserts', 'deletes', 'changes', 'purges', 'all', and 'none' ++select @@global.innodb_change_buffering in ('inserts', 'deletes', 'changes', 'purges', 'all', 'none'); + select @@global.innodb_change_buffering; + --error ER_INCORRECT_GLOBAL_LOCAL_VAR + select @@session.innodb_change_buffering; + +--- mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result.orig Mon Mar 15 16:15:22 2010 ++++ mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result Fri Mar 19 01:23:58 2010 +@@ -1,28 +1,28 @@ + SET @start_global_value = @@global.innodb_change_buffering; + SELECT @start_global_value; + @start_global_value +-inserts +-Valid values are 'inserts' and 'none' +-select @@global.innodb_change_buffering in ('inserts', 'none'); +-@@global.innodb_change_buffering in ('inserts', 'none') ++all ++Valid values are 'inserts', 'deletes', 'changes', 'purges', 'all', and 'none' ++select @@global.innodb_change_buffering in ('inserts', 'deletes', 'changes', 'purges', 'all', 'none'); ++@@global.innodb_change_buffering in ('inserts', 'deletes', 'changes', 'purges', 'all', 'none') + 1 + select @@global.innodb_change_buffering; + @@global.innodb_change_buffering +-inserts ++all + select @@session.innodb_change_buffering; + ERROR HY000: Variable 'innodb_change_buffering' is a GLOBAL variable + show global variables like 'innodb_change_buffering'; + Variable_name Value +-innodb_change_buffering inserts ++innodb_change_buffering all + show session variables like 'innodb_change_buffering'; + Variable_name Value +-innodb_change_buffering inserts ++innodb_change_buffering all + select * from information_schema.global_variables where variable_name='innodb_change_buffering'; + VARIABLE_NAME VARIABLE_VALUE +-INNODB_CHANGE_BUFFERING inserts ++INNODB_CHANGE_BUFFERING all + select * from information_schema.session_variables where variable_name='innodb_change_buffering'; + VARIABLE_NAME VARIABLE_VALUE +-INNODB_CHANGE_BUFFERING inserts ++INNODB_CHANGE_BUFFERING all + set global innodb_change_buffering='none'; + select @@global.innodb_change_buffering; + @@global.innodb_change_buffering +@@ -60,4 +60,4 @@ + SET @@global.innodb_change_buffering = @start_global_value; + SELECT @@global.innodb_change_buffering; + @@global.innodb_change_buffering +-inserts ++all |