diff options
| -rw-r--r-- | mysql-test/r/create.result | 55 | ||||
| -rw-r--r-- | mysql-test/r/merge.result | 19 | ||||
| -rw-r--r-- | mysql-test/t/create.test | 67 | ||||
| -rw-r--r-- | mysql-test/t/merge.test | 15 |
4 files changed, 156 insertions, 0 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index e37f9d580ba..fce775c5952 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -99,6 +99,14 @@ create table t1 (`` int); ERROR 42000: Incorrect column name '' create table t1 (i int, index `` (i)); ERROR 42000: Incorrect index name '' +create table t1 (i int); +lock tables t1 read; +create table t2 (j int); +ERROR HY000: Table 't2' was not locked with LOCK TABLES +create temporary table t2 (j int); +drop temporary table t2; +unlock tables; +drop table t1; create table t1 (a int auto_increment not null primary key, B CHAR(20)); insert into t1 (b) values ("hello"),("my"),("world"); create table t2 (key (b)) select * from t1; @@ -377,6 +385,17 @@ ERROR 42S01: Table 't3' already exists drop table t1, t2, t3; drop table t3; drop database mysqltest; +create table t1 (i int); +create table t2 (j int); +lock tables t1 read; +create table t3 like t1; +ERROR HY000: Table 't3' was not locked with LOCK TABLES +create temporary table t3 like t1; +drop temporary table t3; +create temporary table t3 like t2; +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables; +drop tables t1, t2; SET SESSION storage_engine="heap"; SELECT @@storage_engine; @@storage_engine @@ -2033,3 +2052,39 @@ ID 3 DROP TABLE t1; DROP TEMPORARY TABLE t2; +# +# Bug #22909 "Using CREATE ... LIKE is possible to create field +# with invalid default value" +# +# Altough original bug report suggests to use older version of MySQL +# for producing .FRM with invalid defaults we use sql_mode to achieve +# the same effect. +drop tables if exists t1, t2; +# Attempt to create table with invalid default should fail in normal mode +create table t1 (dt datetime default '2008-02-31 00:00:00'); +ERROR 42000: Invalid default value for 'dt' +set @old_mode= @@sql_mode; +set @@sql_mode='ALLOW_INVALID_DATES'; +# The same should be possible in relaxed mode +create table t1 (dt datetime default '2008-02-31 00:00:00'); +set @@sql_mode= @old_mode; +# In normal mode attempt to create copy of table with invalid +# default should fail +create table t2 like t1; +ERROR 42000: Invalid default value for 'dt' +set @@sql_mode='ALLOW_INVALID_DATES'; +# But should work in relaxed mode +create table t2 like t1; +# Check that table definitions match +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `dt` datetime DEFAULT '2008-02-31 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `dt` datetime DEFAULT '2008-02-31 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +set @@sql_mode= @old_mode; +drop tables t1, t2; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index e46d8e75ab1..8f7ebb06c06 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -2699,4 +2699,23 @@ LOCK TABLE m1 WRITE; ALTER TABLE m1 ADD INDEX (c1); UNLOCK TABLES; DROP TABLE m1, t1; +# +# Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter" +# +drop tables if exists t1, m1, m2; +create table t1 (i int) engine=myisam; +create table m1 (i int) engine=mrg_myisam union=(t1) insert_method=first; +create table m2 like m1; +# Table definitions should match +show create table m1; +Table Create Table +m1 CREATE TABLE `m1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`) +show create table m2; +Table Create Table +m2 CREATE TABLE `m2` ( + `i` int(11) DEFAULT NULL +) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`) +drop tables m1, m2, t1; End of 6.0 tests diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 383ba98ae6d..887ae4da404 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -102,6 +102,22 @@ create table t1 (`` int); create table t1 (i int, index `` (i)); # +# CREATE TABLE under LOCK TABLES +# +# We don't allow creation of non-temporary tables under LOCK TABLES +# as following meta-data locking protocol in this case can lead to +# deadlock. +create table t1 (i int); +lock tables t1 read; +--error ER_TABLE_NOT_LOCKED +create table t2 (j int); +# OTOH creating of temporary table should be OK +create temporary table t2 (j int); +drop temporary table t2; +unlock tables; +drop table t1; + +# # Test of CREATE ... SELECT with indexes # @@ -315,6 +331,26 @@ drop table t3; drop database mysqltest; # +# CREATE TABLE LIKE under LOCK TABLES +# +# Similarly to ordinary CREATE TABLE we don't allow creation of +# non-temporary tables under LOCK TABLES. Also we require source +# table to be locked. +create table t1 (i int); +create table t2 (j int); +lock tables t1 read; +--error ER_TABLE_NOT_LOCKED +create table t3 like t1; +# OTOH creating of temporary table should be OK +create temporary table t3 like t1; +drop temporary table t3; +# Source table should be locked +--error ER_TABLE_NOT_LOCKED +create temporary table t3 like t2; +unlock tables; +drop tables t1, t2; + +# # Test default table type # SET SESSION storage_engine="heap"; @@ -1731,3 +1767,34 @@ DROP TABLE t1; DROP TEMPORARY TABLE t2; + +--echo # +--echo # Bug #22909 "Using CREATE ... LIKE is possible to create field +--echo # with invalid default value" +--echo # +--echo # Altough original bug report suggests to use older version of MySQL +--echo # for producing .FRM with invalid defaults we use sql_mode to achieve +--echo # the same effect. +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +--echo # Attempt to create table with invalid default should fail in normal mode +--error ER_INVALID_DEFAULT +create table t1 (dt datetime default '2008-02-31 00:00:00'); +set @old_mode= @@sql_mode; +set @@sql_mode='ALLOW_INVALID_DATES'; +--echo # The same should be possible in relaxed mode +create table t1 (dt datetime default '2008-02-31 00:00:00'); +set @@sql_mode= @old_mode; +--echo # In normal mode attempt to create copy of table with invalid +--echo # default should fail +--error ER_INVALID_DEFAULT +create table t2 like t1; +set @@sql_mode='ALLOW_INVALID_DATES'; +--echo # But should work in relaxed mode +create table t2 like t1; +--echo # Check that table definitions match +show create table t1; +show create table t2; +set @@sql_mode= @old_mode; +drop tables t1, t2; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index a9d98da0403..29c0eae1df6 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -2187,5 +2187,20 @@ UNLOCK TABLES; DROP TABLE m1, t1; +--echo # +--echo # Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter" +--echo # +--disable_warnings +drop tables if exists t1, m1, m2; +--enable_warnings +create table t1 (i int) engine=myisam; +create table m1 (i int) engine=mrg_myisam union=(t1) insert_method=first; +create table m2 like m1; +--echo # Table definitions should match +show create table m1; +show create table m2; +drop tables m1, m2, t1; + + --echo End of 6.0 tests |