From 4d794c233419658cd79b416a3ce45d0b1efea688 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 1 Feb 2008 13:00:40 +0500 Subject: Fix for bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates The problem was in incompatible syntax for key definition in CREATE TABLE. 5.0 supports only the following syntax for key definition (see "CREATE TABLE syntax" in the manual): {INDEX|KEY} [index_name] [index_type] (index_col_name,...) While 5.1 parser supports the above syntax, the "preferred" syntax was changed to: {INDEX|KEY} [index_name] (index_col_name,...) [index_type] The above syntax is used in 5.1 for the SHOW CREATE TABLE output, which led to dumps generated by 5.1 being incompatible with 5.0. Fixed by changing the parser in 5.0 to support both 5.0 and 5.1 syntax for key definition. mysql-test/r/create.result: Added a test case for bug #25162. mysql-test/t/create.test: Added a test case for bug #25162. sql/sql_yacc.yy: Changed the parser to support both 5.0 and 5.1 syntax for index type specification in CREATE TABLE. --- mysql-test/r/create.result | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/r/create.result') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 3d7486b6ba2..53c2058f3ec 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1532,4 +1532,18 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 7 drop table t1,t2; +CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1)); +DROP TABLE t1; +CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE); +DROP TABLE t1; +CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 c1 1 c1 NULL 0 NULL NULL YES HASH +DROP TABLE t1; +CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 c1 1 c1 A NULL NULL NULL YES BTREE +DROP TABLE t1; End of 5.0 tests -- cgit v1.2.1 From 9f8fb5ed044e750feb82258520110cc662e45b13 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 6 Feb 2008 14:55:19 +0300 Subject: Add a test case for Bug#21380: DEFAULT definition not always transfered by CREATE TABLE/SELECT to the new table. mysql-test/r/create.result: Update result file. --- mysql-test/r/create.result | 46 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 46 insertions(+) (limited to 'mysql-test/r/create.result') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 0613c9ba488..13192dca969 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1743,4 +1743,50 @@ t1 CREATE TABLE `t1` ( `MAXLEN` bigint(3) NOT NULL DEFAULT '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 drop table t1; + +# -- +# -- Bug#21380: DEFAULT definition not always transfered by CREATE +# -- TABLE/SELECT to the new table. +# -- + +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; + +CREATE TABLE t1( +c1 INT DEFAULT 12 COMMENT 'column1', +c2 INT NULL COMMENT 'column2', +c3 INT NOT NULL COMMENT 'column3', +c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', +c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', +c6 VARCHAR(255)) +COLLATE ucs2_unicode_ci; + +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT '12' COMMENT 'column1', + `c2` int(11) DEFAULT NULL COMMENT 'column2', + `c3` int(11) NOT NULL COMMENT 'column3', + `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', + `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', + `c6` varchar(255) COLLATE ucs2_unicode_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_unicode_ci + +CREATE TABLE t2 AS SELECT * FROM t1; + +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` int(11) DEFAULT '12' COMMENT 'column1', + `c2` int(11) DEFAULT NULL COMMENT 'column2', + `c3` int(11) NOT NULL COMMENT 'column3', + `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', + `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', + `c6` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +DROP TABLE t2; + +# -- End of test case for Bug#21380. + End of 5.1 tests -- cgit v1.2.1 From d84f322a978831f015d325ea2df8207ed7e8e6d2 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 8 Feb 2008 20:06:05 +0300 Subject: Fix create.test: use latin1 instead ucs2. --- mysql-test/r/create.result | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test/r/create.result') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 13192dca969..4bdcca81b74 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1759,7 +1759,7 @@ c3 INT NOT NULL COMMENT 'column3', c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', c6 VARCHAR(255)) -COLLATE ucs2_unicode_ci; +COLLATE latin1_bin; SHOW CREATE TABLE t1; Table Create Table @@ -1769,8 +1769,8 @@ t1 CREATE TABLE `t1` ( `c3` int(11) NOT NULL COMMENT 'column3', `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', - `c6` varchar(255) COLLATE ucs2_unicode_ci DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_unicode_ci + `c6` varchar(255) COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin CREATE TABLE t2 AS SELECT * FROM t1; @@ -1782,7 +1782,7 @@ t2 CREATE TABLE `t2` ( `c3` int(11) NOT NULL COMMENT 'column3', `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', - `c6` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci DEFAULT NULL + `c6` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -- cgit v1.2.1 From 5704c7fe2cf52fb8bec0f8e8006f7a4b181e1660 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 14 Feb 2008 18:13:40 +0300 Subject: A patch for Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields. The actual problem here was that CREATE TABLE allowed zero date as a default value for a TIMESTAMP column in NO_ZERO_DATE mode. The thing is that for TIMESTAMP date type specific rule is applied: column_name TIMESTAMP == column_name TIMESTAMP DEFAULT 0 whever for any other date data type column_name TYPE == column_name TYPE DEFAULT NULL The fix is to raise an error when we're in NO_ZERO_DATE mode and there is TIMESTAMP column w/o default value. mysql-test/r/create.result: Update result file. mysql-test/t/create.test: Test case for Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields. sql/sql_table.cc: Report an error if NO_ZERO_MODE is set and we have zero date as a default. --- mysql-test/r/create.result | 44 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 44 insertions(+) (limited to 'mysql-test/r/create.result') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 4bdcca81b74..0dfc9c1e761 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1789,4 +1789,48 @@ DROP TABLE t2; # -- End of test case for Bug#21380. +# -- +# -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields +# -- + +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t3; + +CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP); + +SET sql_mode = NO_ZERO_DATE; + +CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0); +ERROR 42000: Invalid default value for 'c2' + +CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP); +ERROR 42000: Invalid default value for 'c2' + +# -- Check that NULL column still can be created. +CREATE TABLE t2(c1 TIMESTAMP NULL); + +# -- Check ALTER TABLE. +ALTER TABLE t1 ADD INDEX(c1); +ERROR 42000: Invalid default value for 'c2' + +# -- Check DATETIME. +SET sql_mode = ''; + +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES (0); + +SET sql_mode = TRADITIONAL; + +ALTER TABLE t3 ADD INDEX(c1); +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c1' at row 1 + +# -- Cleanup. +SET sql_mode = ''; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +# -- End of Bug#18834. + End of 5.1 tests -- cgit v1.2.1 From 28cd75fec42e327950056617c7dea8ae4d2f6395 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 22 Mar 2008 11:32:24 +0300 Subject: Fix for Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type. The problem was that for some unknown reason 0 was not allowed as a default value for YEAR data type. That was coded before BK. However the Manual does not say a word about such a limitation. Also, it looks inconsistent with other data types. The fix is to allow 0 as a default value. mysql-test/r/create.result: Update result file. mysql-test/t/create.test: Add a test case for Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type. sql/unireg.cc: Allow 0 as a default value for YEAR data type. --- mysql-test/r/create.result | 41 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 41 insertions(+) (limited to 'mysql-test/r/create.result') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 81463a3f0f2..85ce830025d 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1847,4 +1847,45 @@ DROP TABLE t3; # -- End of Bug#18834. +# -- +# -- Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type. +# -- + +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1(c1 YEAR DEFAULT 2008, c2 YEAR DEFAULT 0); + +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` year(4) DEFAULT '2008', + `c2` year(4) DEFAULT '0000' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +INSERT INTO t1 VALUES(); + +SELECT * FROM t1; +c1 c2 +2008 0000 + +ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0; + +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` year(4) DEFAULT '0000', + `c2` year(4) DEFAULT '0000' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +INSERT INTO t1 VALUES(); + +SELECT * FROM t1; +c1 c2 +2008 0000 +0000 0000 + +DROP TABLE t1; + +# -- End of Bug#34274 + End of 5.1 tests -- cgit v1.2.1