mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-04-06 23:35:33 +02:00
Code Issues Projects Releases Packages Wiki Activity Actions 1
mariadb/mysql-test/main/create_or_replace.result
Monty e62dc52420 MDEV-25292 Atomic CREATE OR REPLACE TABLE 
Atomic CREATE OR REPLACE allows to keep an old table intact if the
command fails or during the crash. That is done by renaming the
original table to temporary name, as a backup and restoring it if the
CREATE fails. When the command is complete and logged the backup
table is deleted.

Atomic replace algorithm

  Two DDL chains are used for CREATE OR REPLACE:
  ddl_log_state_create (C) and ddl_log_state_rm (D).

  1. (C) Log rename of ORIG to TMP table (Rename TMP to original).
  2. Rename orignal to TMP.
  3. (C) Log CREATE_TABLE_ACTION of ORIG (drops ORIG);
  4. Do everything with ORIG (like insert data)
  5. (D) Log drop of TMP
  6. Write query to binlog (this marks (C) to be closed in
     case of failure)
  7. Execute drop of TMP through (D)
  8. Close (C) and (D)

  If there is a failure before 6) we revert the changes in (C)
  Chain (D) is only executed if 6) succeded (C is closed on
  crash recovery).

Foreign key errors will be found at the 1) stage.

Additional notes

  - CREATE TABLE without REPLACE and temporary tables is not affected
    by this commit.
    set @@drop_before_create_or_replace=1 can be used to
    get old behaviour where existing tables are dropped
    in CREATE OR REPLACE.

  - CREATE TABLE is reverted if binlogging the query fails.

  - Engines having HTON_EXPENSIVE_RENAME flag set are not affected by
    this commit. Conflicting tables marked with this flag will be
    deleted with CREATE OR REPLACE.

  - Replication execution is not affected by this commit.
    - Replication will first drop the conflicting table and then
      creating the new one.

  - CREATE TABLE .. SELECT XID usage is fixed and now there is no need
    to log DROP TABLE via DDL_CREATE_TABLE_PHASE_LOG (see comments in
    do_postlock()). XID is now correctly updated so it disables
    DDL_LOG_DROP_TABLE_ACTION. Note that binary log is flushed at the
    final stage when the table is ready. So if we have XID in the
    binary log we don't need to drop the table.

  - Three variations of CREATE OR REPLACE handled:

    1. CREATE OR REPLACE TABLE t1 (..);
    2. CREATE OR REPLACE TABLE t1 LIKE t2;
    3. CREATE OR REPLACE TABLE t1 SELECT ..;

  - Test case uses 6 combinations for engines (aria, aria_notrans,
    myisam, ib, lock_tables, expensive_rename) and 2 combinations for
    binlog types (row, stmt). Combinations help to check differences
    between the results. Error failures are tested for the above three
    variations.

  - expensive_rename tests CREATE OR REPLACE without atomic
    replace. The effect should be the same as with the old behaviour
    before this commit.

  - Triggers mechanism is unaffected by this change. This is tested in
    create_replace.test.

  - LOCK TABLES is affected. Lock restoration must be done after new
    table is created or TMP is renamed back to ORIG

  - Moved ddl_log_complete() from send_eof() to finalize_ddl(). This
    checkpoint was not executed before for normal CREATE TABLE but is
    executed now.

  - CREATE TABLE will now rollback also if writing to the binary
    logging failed. See rpl_gtid_strict.test

backup ddl log changes

- In case of a successfull CREATE OR REPLACE we only log
  the CREATE event, not the DROP TABLE event of the old table.

ddl_log.cc changes

  ddl_log_execute_action() now properly return error conditions.
  ddl_log_disable_entry() added to allow one to disable one entry.
  The entry on disk is still reserved until ddl_log_complete() is
  executed.

On XID usage

  Like with all other atomic DDL operations XID is used to avoid
  inconsistency between master and slave in the case of a crash after
  binary log is written and before ddl_log_state_create is closed. On
  recovery XIDs are taken from binary log and corresponding DDL log
  events get disabled.  That is done by
  ddl_log_close_binlogged_events().

On linking two chains together

  Chains are executed in the ascending order of entry_pos of execute
  entries. But entry_pos assignment order is undefined: it may assign
  bigger number for the first chain and then smaller number for the
  second chain. So the execution order in that case will be reverse:
  second chain will be executed first.

  To avoid that we link one chain to another. While the base chain
  (ddl_log_state_create) is active the secondary chain
  (ddl_log_state_rm) is not executed. That is: only one chain can be
  executed in two linked chains.

  The interface ddl_log_link_chains() was defined in "MDEV-22166
  ddl_log_write_execute_entry() extension".

Atomic info parameters in HA_CREATE_INFO

  Many functions in CREATE TABLE pass the same parameters. These
  parameters are part of table creation info and should be in
  HA_CREATE_INFO (or whatever). Passing parameters via single
  structure is much easier for adding new data and
  refactoring.

InnoDB changes
  Added ha_innobase::can_be_renamed_to_backup() to check if
  a table with foreign keys can be renamed.

Aria changes:
- Fixed issue in Aria engine with CREATE + locked tables
  that data was not properly commited in some cases in
  case of crashes.

Known issues:
- InnoDB tables with foreign key definitions are not fully supported
  with atomic create and replace:
  - ha_innobase::can_be_renamed_to_backup() can detect some cases
    where InnoDB does not support renaming table with foreign key
    constraints.  In this case MariaDB will drop the old table before
    creating the new one.
    The detected cases are:
    - The new and old table is using the same foreign key constraint
      name.
    - The old table has self referencing constraints.
  - If the old and new table uses the same name for a constraint the
    create of the new table will fail. The orignal table will be
    restored in this case.
  - The above issues will be fixed in a future commit.
- CREATE OR REPLACE TEMPORARY table is not full atomic. Any conflicting
  table will always be dropped before creating a new one. (Old behaviour).
2025-03-18 18:28:16 +01:00

1044 lines
31 KiB
Text
Raw Permalink Blame History

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);
#
# Check first syntax and wrong usage
#
CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
create or replace table mysql.general_log (a int);
ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled
create or replace table mysql.slow_log (a int);
ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled
#
# Usage when table doesn't exist
#
CREATE OR REPLACE TABLE t1 (a int);
CREATE TABLE t1 (a int);
ERROR 42S01: Table 't1' already exists
DROP TABLE t1;
CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
CREATE TEMPORARY TABLE t1 (a int, b int, c int);
ERROR 42S01: Table 't1' already exists
DROP TEMPORARY TABLE t1;
#
# Testing with temporary tables
#
CREATE OR REPLACE TABLE t1 (a int);
CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TEMPORARY TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TEMPORARY TABLE t1;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
create temporary table t1 (i int) engine=InnoDB;
create or replace temporary table t1 (a int, b int) engine=InnoDB;
create or replace temporary table t1 (j int);
show create table t1;
Table Create Table
t1 CREATE TEMPORARY TABLE `t1` (
`j` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
CREATE OR REPLACE TABLE t1 (a int);
LOCK TABLES t1 write;
CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine= innodb;
CREATE OR REPLACE TEMPORARY TABLE t1 (a int) engine= innodb;
CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine=myisam;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TEMPORARY TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TEMPORARY TABLE t1;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE OR REPLACE TABLE t2 (a int);
ERROR HY000: Table 't2' was not locked with LOCK TABLES
DROP TABLE t1;
UNLOCK TABLES;
CREATE OR REPLACE TEMPORARY TABLE t1 (a int) SELECT * from t2;
SELECT * FROM t1;
a
1
2
3
CREATE OR REPLACE TEMPORARY TABLE t1 (b int) SELECT * from t2;
SELECT * FROM t1;
b a
NULL 1
NULL 2
NULL 3
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TEMPORARY TABLE `t1` (
`b` int(11) DEFAULT NULL,
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 AS SELECT a FROM t2;
CREATE TEMPORARY TABLE IF NOT EXISTS t1(a int, b int) SELECT 1,2 FROM t2;
Warnings:
Note 1050 Table 't1' already exists
DROP TABLE t1;
CREATE TABLE t1 (a int);
CREATE OR REPLACE TABLE t1 AS SELECT 1;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`1` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
create table t1 (a int);
create or replace table t1 as select * from t1;
ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
create or replace table t1 as select a from (select a from t1) as t3;
ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
create or replace table t1 as select a from t2 where t2.a in (select a from t1);
ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
drop table t1;
#
# Testing with normal tables
#
CREATE OR REPLACE TABLE t1 (a int);
CREATE OR REPLACE TABLE t1 (a int, b int);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a int) SELECT * from t2;
SELECT * FROM t1;
a
1
2
3
TRUNCATE TABLE t1;
CREATE TABLE IF NOT EXISTS t1 (a int) SELECT * from t2;
Warnings:
Note 1050 Table 't1' already exists
SELECT * FROM t1;
a
DROP TABLE t1;
CREATE TABLE t1 (i int);
CREATE OR REPLACE TABLE t1 AS SELECT 1;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`1` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
# Using lock tables with CREATE OR REPLACE
CREATE OR REPLACE TABLE t1 (a int);
LOCK TABLES t1 write,t2 write;
CREATE OR REPLACE TABLE t1 (a int, b int);
SELECT * FROM t1;
a b
INSERT INTO t1 values(1,1);
CREATE OR REPLACE TABLE t1 (a int, b int, c int);
INSERT INTO t1 values(1,1,1);
SELECT count(*) from t2;
count(*)
3
CREATE OR REPLACE TABLE t3 (a int);
ERROR HY000: Table 't3' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP TABLE t1;
# Using lock tables with CREATE OR REPLACE ... SELECT
CREATE OR REPLACE TABLE t1 (a int);
LOCK TABLES t1 write,t2 write;
CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2;
SELECT * FROM t2;
a
1
2
3
SELECT * FROM t1;
b a 1
NULL 1 1
NULL 2 1
NULL 3 1
SELECT * FROM t1;
b a 1
NULL 1 1
NULL 2 1
NULL 3 1
INSERT INTO t1 values(1,1,1);
CREATE OR REPLACE TABLE t1 (a int, b int, c int, d int);
INSERT INTO t1 values(1,1,1,1);
CREATE OR REPLACE TABLE t1 SELECT * from t2;
SELECT * from t1;
a
1
2
3
INSERT INTO t1 values(4);
set @@drop_before_create_or_replace=0;
CREATE OR REPLACE TABLE t1 (a int primary key) SELECT 1 as a from t2;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t1;
a
1
2
3
4
set @@drop_before_create_or_replace=1;
CREATE OR REPLACE TABLE t1 (a int primary key) SELECT 1 as a from t2;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t1;
ERROR HY000: Table 't1' was not locked with LOCK TABLES
set @@drop_before_create_or_replace=default;
SELECT count(*) from t2;
count(*)
3
CREATE OR REPLACE TABLE t3 (a int);
ERROR HY000: Table 't3' was not locked with LOCK TABLES
UNLOCK TABLES;
SELECT * from t1;
ERROR 42S02: Table 'test.t1' doesn't exist
# Testing write and read locks with CREATE OR REPLACE
CREATE OR REPLACE TABLE t1 (a int);
LOCK TABLES t1 write,t2 write, t1 as t1_read read;
CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2;
SELECT * FROM t1;
b a 1
NULL 1 1
NULL 2 1
NULL 3 1
SELECT * FROM t2;
a
1
2
3
SELECT * FROM t1 as t1_read;
ERROR HY000: Table 't1_read' was not locked with LOCK TABLES
DROP TABLE t1;
UNLOCK TABLES;
CREATE OR REPLACE TABLE t1 (a int);
LOCK TABLE t1 WRITE;
CREATE OR REPLACE TABLE t1 AS SELECT 1;
SELECT * from t1;
1
1
SELECT * from t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
DROP TABLE t1;
#
# Test also with InnoDB (transactional engine)
#
create table t1 (i int) engine=innodb;
lock table t1 write;
create or replace table t1 (j int);
unlock tables;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`j` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
create table t1 (i int) engine=InnoDB;
lock table t1 write, t2 write;
create or replace table t1 (j int) engine=innodb;
unlock tables;
drop table t1;
create table t1 (i int) engine=InnoDB;
create table t3 (i int) engine=InnoDB;
insert into t3 values(1),(2),(3);
create table t4 (i int) engine=InnoDB;
insert into t4 values(1);
lock table t1 write, t2 write, t3 write, t4 write;
create or replace table t1 (a int, i int) engine=innodb select t2.a,t3.i from t2,t3;
select * from t4;
i
1
unlock tables;
select * from t1 order by a,i;
a i
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
drop table t1,t3,t4;
#
# Test the meta data locks are freed properly
#
create database mysqltest2;
drop table if exists test.t1,mysqltest2.t2;
Warnings:
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;
InnoDB 0 transactions not purged
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
create or replace table test.t1;
ERROR 42000: A table must have at least 1 column
show tables;
Tables_in_test
t1
t2
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
create or replace table mysqltest2.t2;
ERROR 42000: A table must have at least 1 column
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1,mysqltest2.t2;
unlock tables;
create table test.t1 (i int);
create table mysqltest2.t2 like test.t1;
lock table test.t1 write, mysqltest2.t2 write;
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
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 LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE 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 LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1,mysqltest2.t2;
unlock tables;
create table test.t1 (i int) engine=innodb;
create table mysqltest2.t2 like test.t1;
lock table test.t1 write, mysqltest2.t2 write;
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
unlock tables;
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 LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysqltest2
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock mysqltest2 t2
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
unlock tables;
drop table t1;
create table test.t1 (i int);
drop database mysqltest2;
drop table test.t1;
#
# MDEV-23391 Server crash in close_thread_table or assertion, upon CREATE OR REPLACE TABLE under lock
#
create table t1 (i int);
lock table t1 write;
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE 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
show tables;
Tables_in_test
t1
t2
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
unlock tables;
#
# Testing CREATE .. LIKE
#
create or replace table t1 like t2;
create or replace table t1 like t2;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
create table t1 (b int);
lock tables t1 write, t2 read;
create or replace table t1 like t2;
SELECT * FROM t1;
a
INSERT INTO t1 values(1);
CREATE OR REPLACE TABLE t1 like t2;
INSERT INTO t1 values(2);
unlock tables;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
create or replace table t1 like t2;
create or replace table t1 like t1;
ERROR 42000: Not unique table/alias: 't1'
drop table t1;
CREATE TEMPORARY TABLE t1 like t2;
CREATE OR REPLACE TABLE t1 like t1;
ERROR 42000: Not unique table/alias: 't1'
CREATE OR REPLACE TABLE t1 like t1;
ERROR 42000: Not unique table/alias: 't1'
drop table t1;
CREATE TEMPORARY TABLE t1 like t2;
CREATE OR REPLACE TEMPORARY TABLE t3 like t1;
CREATE OR REPLACE TEMPORARY TABLE t3 like t3;
ERROR 42000: Not unique table/alias: 't3'
drop table t1,t3;
#
# Test with prepared statements
#
prepare stmt1 from 'create or replace table t1 select * from t2';
execute stmt1;
select * from t1;
a
1
2
3
execute stmt1;
select * from t1;
a
1
2
3
drop table t1;
execute stmt1;
select * from t1;
a
1
2
3
deallocate prepare stmt1;
drop table t1;
#
# Test with views
#
create view t1 as select 1;
create table if not exists t1 (a int);
Warnings:
Note 1050 Table 't1' already exists
create or replace table t1 (a int);
ERROR 42S02: 'test.t1' is a view
drop table t1;
ERROR 42S02: 'test.t1' is a view
drop view t1;
#
# MDEV-5602 CREATE OR REPLACE obtains stricter locks than the
# connection had before
#
create table t1 (a int);
lock table t1 write, t2 read;
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
MDL_SHARED_READ Table metadata lock test t2
create or replace table t1 (i int);
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
MDL_SHARED_READ Table metadata lock test t2
create or replace table t1 like t2;
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
MDL_SHARED_READ Table metadata lock test t2
create or replace table t1 select 1 as f1;
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
MDL_SHARED_READ Table metadata lock test t2
drop table t1;
unlock tables;
#
# MDEV-6560
# Assertion `! is_set() ' failed in Diagnostics_area::set_ok_status
#
CREATE TABLE t1 (col_int_nokey INT) ENGINE=InnoDB;
CREATE OR REPLACE TEMPORARY TABLE tmp LIKE t1;
LOCK TABLE t1 WRITE;
connect con1,localhost,root,,test;
CREATE OR REPLACE TABLE t1 LIKE tmp;
connection default;
KILL QUERY con_id;
connection con1;
ERROR 70100: Query execution was interrupted
CREATE OR REPLACE TABLE t1 (a int);
connection default;
KILL QUERY con_id;
connection con1;
ERROR 70100: Query execution was interrupted
disconnect con1;
connection default;
drop table t1;
DROP TABLE t2;
#
# MDEV-10824 - Crash in CREATE OR REPLACE TABLE t1 AS SELECT spfunc()
#
CREATE TABLE t1(a INT);
CREATE FUNCTION f1() RETURNS VARCHAR(16383) RETURN 'test';
CREATE OR REPLACE TABLE t1 AS SELECT f1();
LOCK TABLE t1 WRITE;
CREATE OR REPLACE TABLE t1 AS SELECT f1();
UNLOCK TABLES;
DROP FUNCTION f1;
DROP TABLE t1;
#
# MDEV-11129
# CREATE OR REPLACE TABLE t1 AS SELECT spfunc() crashes if spfunc()
# references t1
#
CREATE OR REPLACE TABLE t1(a INT);
CREATE FUNCTION f1() RETURNS VARCHAR(16383)
BEGIN
INSERT INTO t1 VALUES(1);
RETURN 'test';
END;
$$
CREATE OR REPLACE TABLE t1 AS SELECT f1();
ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
LOCK TABLE t1 WRITE;
CREATE OR REPLACE TABLE t1 AS SELECT f1();
ERROR HY000: Table 't1' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP FUNCTION f1;
DROP TABLE t1;
#
# MDEV-14410 - Assertion `table->pos_in_locked_tables == __null ||
# table->pos_in_locked_tables->table == table' failed in
# mark_used_tables_as_free_for_reuse
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (b INT);
CREATE TABLE t3 (c INT);
CREATE TRIGGER tr1 BEFORE INSERT ON t3 FOR EACH ROW INSERT INTO t1 VALUES ();
CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t3 SELECT * FROM t1;
LOCK TABLE t1 WRITE, t2 WRITE;
CREATE OR REPLACE TABLE t1 (i INT);
UNLOCK TABLES;
INSERT INTO t2 VALUES (1);
DROP TABLE t1, t2, t3;
#
# MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in
# Locked_tables_list::unlock_locked_tables
#
CREATE TEMPORARY TABLE t1(a INT) ENGINE=InnoDB;
CREATE TEMPORARY TABLE t2(a INT);
CREATE TABLE t3(a INT);
LOCK TABLE t2 WRITE;
SELECT * FROM t2;
a
CREATE OR REPLACE TEMPORARY TABLE t1(c INT DEFAULT '');
ERROR 42000: Invalid default value for 'c'
SELECT * FROM t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
CREATE OR REPLACE TEMPORARY TABLE t2(c INT DEFAULT '');
ERROR 42000: Invalid default value for 'c'
SELECT * FROM t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP TABLE t3;
#
# MDEV-29697 Assertion failure in Diagnostics_area::set_ok_status
# upon CREATE OR REPLACE causing ER_UPDATE_TABLE_USED
#
CREATE TABLE t (a INT) ENGINE=MyISAM;
CREATE TABLE tm (a INT) ENGINE=MERGE UNION(t);
CREATE OR REPLACE TABLE t LIKE tm;
ERROR HY000: Table 'tm' is specified twice, both as a target for 'CREATE' and as a separate source for data
DROP TABLE IF EXISTS tm, t;
#
# End of 10.3 tests
#
#
# MDEV-25292 Atomic CREATE OR REPLACE TABLE
#
# Test triggers
create table t1 (a int);
insert into t1 values (1);
create trigger trg1 before insert on t1 for each row set @a:=1;
create trigger trg2 before update on t1 for each row set @a:=1;
select trigger_name from information_schema.triggers where trigger_schema="test" and event_object_table='t1';
trigger_name
trg1
trg2
create or replace table t1 (b int);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
select trigger_name from information_schema.triggers where trigger_schema="test" and event_object_table='t1';
trigger_name
drop table t1;
create table t1 (a int);
insert into t1 values (1);
create trigger trg1 before insert on t1 for each row set @a:=1;
create trigger trg2 before update on t1 for each row set @a:=1;
create or replace table t1;
ERROR 42000: A table must have at least 1 column
select * from t1;
a
1
select trigger_name from information_schema.triggers where trigger_schema="test" and event_object_table='t1';
trigger_name
trg1
trg2
drop table t1;
# Test table locks
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1);
lock tables t1 write, t2 write;
create or replace table t1 (b int);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
create or replace table t1 (c int);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
select * from t1,t2;
c a
select * from t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
insert into t1 values (1);
create or replace table t1;
ERROR 42000: A table must have at least 1 column
select * from t1;
c
1
lock tables t1 write, t2 write;
create or replace table t1 (d int);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`d` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
unlock tables;
drop table t1,t2;
#
# End of 11.8 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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
test t2 0 0
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
test t2 0 0
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);
create table t3 (x int);
create or replace table t1 like t3;
create or replace table t1 select * from t3;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`x` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop tables t3, t2, t1;
# UNIQUE
create table t1 (pk int auto_increment primary key, a varchar(2300), unique (a)) engine aria character set latin1;
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) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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;
create or replace table `t3#` (a int primary key references t1 (pk)) engine=innodb;
lock tables t1 write, t2 write, `t3#` write;
create or replace table t2 (c1 int not null, c1 varchar(255) ) engine=innodb;
ERROR 42S21: Duplicate column name 'c1'
create or replace table `t3#` (c1 int not null, c1 varchar(255) ) engine=innodb;
ERROR 42S21: Duplicate column name 'c1'
select * from t1;
pk
select * from t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
select * from `t3#`;
ERROR HY000: Table 't3#' was not locked with LOCK TABLES
unlock tables;
select * from t2;
ERROR 42S02: Table 'test.t2' doesn't exist
select * from `t3#`;
ERROR 42S02: Table 'test.t3#' doesn't exist
drop tables if exists t2, `t3#`, t1;
Warnings:
Note 1051 Unknown table 'test.t2,test.t3#'
#
# 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;
ERROR HY000: Can't create table `test`.`u` (errno: 121 "Duplicate key on write or update")
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);
lock table t write;
create or replace table t (y int);
ERROR HY000: Error on rename of './test/t' to './test/#sql-create...' (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;
#
# End of 12.0 tests
#
SET GLOBAL innodb_stats_persistent=@save_persistent;
Reference in a new issue View git blame Copy permalink