diff options
Diffstat (limited to 'mysql-test/main/create_or_replace.result')
| -rw-r--r-- | mysql-test/main/create_or_replace.result | 383 |
1 files changed, 17 insertions, 366 deletions
diff --git a/mysql-test/main/create_or_replace.result b/mysql-test/main/create_or_replace.result index be863986f78..4e96af97326 100644 --- a/mysql-test/main/create_or_replace.result +++ b/mysql-test/main/create_or_replace.result @@ -1,3 +1,5 @@ +SET @save_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=OFF; CREATE TABLE t2 (a int); INSERT INTO t2 VALUES(1),(2),(3); # @@ -258,8 +260,7 @@ Note 1051 Unknown table 'test.t1,mysqltest2.t2' create table test.t1 (i int) engine=myisam; create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -271,37 +272,24 @@ create or replace table test.t1; ERROR 42000: A table must have at least 1 column show tables; Tables_in_test -t1 t2 -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 -# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 create or replace table mysqltest2.t2; ERROR 42000: A table must have at least 1 column -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_BACKUP_DDL NULL Backup lock -# MDL_BACKUP_DML NULL Backup lock -# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 -# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test -# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 -# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 -drop table mysqltest2.t2; -drop table t1; create table t1 (i int); drop table t1; create table test.t1 (i int); create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -313,37 +301,24 @@ create or replace table test.t1 (a int) select 1 as 'a', 2 as 'a'; ERROR 42S21: Duplicate column name 'a' show tables; Tables_in_test -t1 t2 -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 -# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a'; ERROR 42S21: Duplicate column name 'a' -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_BACKUP_DDL NULL Backup lock -# MDL_BACKUP_DML NULL Backup lock -# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 -# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test -# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 -# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 -drop table mysqltest2.t2; -drop table t1; create table t1 (i int); drop table t1; create table test.t1 (i int) engine=innodb; create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 @@ -355,8 +330,7 @@ drop table test.t1,mysqltest2.t2; create table test.t1 (i int) engine=aria transactional=1 checksum=1; create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 @@ -373,8 +347,7 @@ drop table test.t1; # create table t1 (i int); lock table t1 write; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -382,12 +355,10 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 create or replace table t1 (a char(1)) engine=Innodb select 'foo' as a; ERROR 22001: Data too long for column 'a' at row 1 -drop table t1; show tables; Tables_in_test t2 -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME create table t1 (i int); drop table t1; @@ -475,8 +446,7 @@ drop view t1; # create table t1 (a int); lock table t1 write, t2 read; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -484,8 +454,7 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 # MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 (i int); -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -493,8 +462,7 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 # MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 like t2; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -502,8 +470,7 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 # MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 select 1 as f1; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -612,320 +579,4 @@ DROP TABLE IF EXISTS tm, t; # # End of 10.3 tests # -# End of 10.4 tests -# -# MDEV-25292 Atomic CREATE OR REPLACE TABLE -# -create table t1 (a int); -insert t1 values (1), (1); -create table t2 (c int); -create or replace table t2 (a int, b int, key k (a), key k (b)); -ERROR 42000: Duplicate key name 'k' -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `c` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -create or replace table t2 (a int, b int, key k (a), key k (b)) as select a, a as b from t1; -ERROR 42000: Duplicate key name 'k' -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `c` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -create or replace table t2 (a int primary key) as select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `c` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -set @old_mode= @@sql_mode; -set @@sql_mode='ALLOW_INVALID_DATES'; -create table t3 (dt datetime default '2008-02-31 00:00:00'); -set @@sql_mode= @old_mode; -create or replace table t2 like t3; -ERROR 42000: Invalid default value for 'dt' -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `c` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -# LOCK TABLES -lock tables t2 write, t1 write; -flush tables; -show open tables like 't2'; -Database Table In_use Name_locked -test t2 1 0 -create or replace table t2 (y int); -flush tables; -show open tables like 't2'; -Database Table In_use Name_locked -test t2 1 0 -create or replace table t2 like t1; -flush tables; -show open tables like 't2'; -Database Table In_use Name_locked -test t2 1 0 -create or replace table t2 (y int) as select * from t1; -flush tables; -show open tables like 't2'; -Database Table In_use Name_locked -test t2 1 0 -unlock tables; -# SP -create or replace procedure sp(n int) -begin -select concat('sp call ', n, ':') as ''; -show open tables like 't2'; -create or replace table t2 (y int); -select 'create or replace table t2 (y int);' as ''; -show open tables like 't2'; -insert into t2 values (2); -select 'insert into t2 values (2);' as ''; -show open tables like 't2'; -create or replace table t2 like t1; -select 'create or replace table t2 like t1;' as ''; -show open tables like 't2'; -create or replace table t2 (y int) as select * from t1; -select 'create or replace table t2 (y int) as select * from t1;' as ''; -show open tables like 't2'; -select 'select * from t2;' as ''; -select * from t2; -show open tables like 't2'; -end $ -flush tables; -call sp(1); - -sp call 1: -Database Table In_use Name_locked - -create or replace table t2 (y int); -Database Table In_use Name_locked - -insert into t2 values (2); -Database Table In_use Name_locked -test t2 0 0 - -create or replace table t2 like t1; -Database Table In_use Name_locked - -create or replace table t2 (y int) as select * from t1; -Database Table In_use Name_locked - -select * from t2; -y a -NULL 1 -NULL 1 -Database Table In_use Name_locked -test t2 0 0 -call sp(2); - -sp call 2: -Database Table In_use Name_locked -test t2 0 0 - -create or replace table t2 (y int); -Database Table In_use Name_locked - -insert into t2 values (2); -Database Table In_use Name_locked -test t2 0 0 - -create or replace table t2 like t1; -Database Table In_use Name_locked - -create or replace table t2 (y int) as select * from t1; -Database Table In_use Name_locked - -select * from t2; -y a -NULL 1 -NULL 1 -Database Table In_use Name_locked -test t2 0 0 -# SP under LOCK TABLES -lock tables t2 write, t1 write; -call sp(3); - -sp call 3: -Database Table In_use Name_locked -test t2 1 0 - -create or replace table t2 (y int); -Database Table In_use Name_locked -test t2 1 0 - -insert into t2 values (2); -Database Table In_use Name_locked -test t2 1 0 - -create or replace table t2 like t1; -Database Table In_use Name_locked -test t2 1 0 - -create or replace table t2 (y int) as select * from t1; -Database Table In_use Name_locked -test t2 1 0 - -select * from t2; -y a -NULL 1 -NULL 1 -Database Table In_use Name_locked -test t2 1 0 -call sp(4); - -sp call 4: -Database Table In_use Name_locked -test t2 1 0 - -create or replace table t2 (y int); -Database Table In_use Name_locked -test t2 1 0 - -insert into t2 values (2); -Database Table In_use Name_locked -test t2 1 0 - -create or replace table t2 like t1; -Database Table In_use Name_locked -test t2 1 0 - -create or replace table t2 (y int) as select * from t1; -Database Table In_use Name_locked -test t2 1 0 - -select * from t2; -y a -NULL 1 -NULL 1 -Database Table In_use Name_locked -test t2 1 0 -unlock tables; -drop procedure sp; -drop tables t1, t2, t3; -# Trigger -create table t1 (a int); -create trigger a before insert on t1 for each row set @s= 1; -create or replace table t1 (old int); -show create trigger a; -ERROR HY000: Trigger does not exist -drop table t1; -# PS: check thd->change_list sanity -create table t1 (a int not null, b char(10) as (concat('', dayname('2020-02-02')))) collate utf8_bin; -prepare stmt from 'insert into t1 (b) values (2)'; -create or replace table t1 (x int); -drop table t1; -drop prepare stmt; -# Foreign keys -create table t1 (x int primary key, y int) engine innodb; -create table t2 (x int references t1(x)) engine innodb; -create or replace table t1 (x int primary key); -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2) -create or replace table t1 (x int primary key); -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2) -create table t3 (x int); -create or replace table t1 like t3; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2) -create or replace table t1 like t3; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2) -create or replace table t1 select * from t3; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2) -create or replace table t1 select * from t3; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2) -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `x` int(11) NOT NULL, - `y` int(11) DEFAULT NULL, - PRIMARY KEY (`x`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -drop tables t3, t2, t1; -# UNIQUE -create table t1 (pk int auto_increment primary key, a varchar(2300), unique (a)) engine aria; -insert into t1 (a) values ('a'), ('b'), ('c'); -create table t2 (x int); -create or replace table t2 engine aria select * from t1; -select * from t2; -pk a -1 a -2 b -3 c -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `pk` int(11) NOT NULL DEFAULT 0, - `a` varchar(2300) DEFAULT NULL -) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 -drop tables t2, t1; -# -# MDEV-28956 Locking is broken if CREATE OR REPLACE fails under LOCK TABLES -# -# Atomic CREATE OR REPLACE part: -# -create table t1 (pk int primary key) engine=innodb; -create or replace table t2 (a int primary key references t1 (pk)) engine=innodb; -lock tables t1 write, t2 write; -create or replace table t2 (c1 int not null, c1 varchar(255) ) engine=innodb; -ERROR 42S21: Duplicate column name 'c1' -select * from t1; -pk -select * from t2; -a -unlock tables; -drop tables t2, t1; -# -# MDEV-28933 CREATE OR REPLACE fails to recreate same constraint name -# -use test; -create table t (a int primary key) engine=innodb; -create or replace table u ( -a int primary key, -constraint c foreign key d (a) references t (a)) engine=innodb; -select * from information_schema.innodb_sys_foreign; -ID FOR_NAME REF_NAME N_COLS TYPE -test/c test/u test/t 1 0 -select * from information_schema.innodb_sys_foreign_cols; -ID FOR_COL_NAME REF_COL_NAME POS -test/c a a 0 -create or replace table u ( -a int primary key, -constraint c foreign key d (a) references t (a)) engine=innodb; -select * from information_schema.innodb_sys_foreign; -ID FOR_NAME REF_NAME N_COLS TYPE -test/c test/u test/t 1 0 -select * from information_schema.innodb_sys_foreign_cols; -ID FOR_COL_NAME REF_COL_NAME POS -test/c a a 0 -drop tables u, t; -select * from information_schema.innodb_sys_foreign; -ID FOR_NAME REF_NAME N_COLS TYPE -select * from information_schema.innodb_sys_foreign_cols; -ID FOR_COL_NAME REF_COL_NAME POS -# -# MDEV-29544 SIGSEGV in HA_CREATE_INFO::finalize_locked_tables -# -call mtr.add_suppression("mysql.innodb_index_stats"); -set sql_mode= ''; -create table t (x int) engine innodb; -insert into t values (77); -alter table mysql.innodb_index_stats modify stat_description char(10); -Warnings: -Warning 1265 Data truncated for column 'stat_description' at row 2 -Warning 1265 Data truncated for column 'stat_description' at row 3 -lock table t write; -create or replace table t (y int); -ERROR HY000: Error on rename of './test/t' to './test/#sql-backup-t' (errno: 168 "Unknown (generic) error from engine") -unlock tables; -alter table mysql.innodb_index_stats modify stat_description varchar(1024) not null; -select * from t; -x -77 -drop table t; -set sql_mode= default; -# -# MDEV-29620 Assertion `next_insert_id == 0' failed in handler::ha_external_lock -# -create or replace table t1 (i serial) as select * from (values(1), (2)) dt; -drop table t1; +SET GLOBAL innodb_stats_persistent=@save_persistent; |