mirror of
https://github.com/MariaDB/server.git
synced 2025-07-25 04:34:58 +02:00
519 lines
14 KiB
Text
519 lines
14 KiB
Text
#
|
|
# Check CREATE OR REPLACE TABLE
|
|
#
|
|
|
|
--source include/have_innodb.inc
|
|
--source include/have_metadata_lock_info.inc
|
|
|
|
SET @save_persistent=@@GLOBAL.innodb_stats_persistent;
|
|
SET GLOBAL innodb_stats_persistent=OFF;
|
|
|
|
#
|
|
# Create help table
|
|
#
|
|
|
|
CREATE TABLE t2 (a int);
|
|
INSERT INTO t2 VALUES(1),(2),(3);
|
|
|
|
--echo #
|
|
--echo # Check first syntax and wrong usage
|
|
--echo #
|
|
|
|
--error ER_WRONG_USAGE
|
|
CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
|
|
|
|
# check that we don't try to create a log table in use
|
|
--error ER_BAD_LOG_STATEMENT
|
|
create or replace table mysql.general_log (a int);
|
|
--error ER_BAD_LOG_STATEMENT
|
|
create or replace table mysql.slow_log (a int);
|
|
|
|
--echo #
|
|
--echo # Usage when table doesn't exist
|
|
--echo #
|
|
|
|
CREATE OR REPLACE TABLE t1 (a int);
|
|
--error ER_TABLE_EXISTS_ERROR
|
|
CREATE TABLE t1 (a int);
|
|
DROP TABLE t1;
|
|
CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
|
|
--error ER_TABLE_EXISTS_ERROR
|
|
CREATE TEMPORARY TABLE t1 (a int, b int, c int);
|
|
DROP TEMPORARY TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Testing with temporary tables
|
|
--echo #
|
|
--disable_view_protocol
|
|
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;
|
|
DROP TEMPORARY TABLE t1;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
# Test also with InnoDB
|
|
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;
|
|
drop table t1;
|
|
|
|
# Using lock tables on normal tables with create or replace on temp tables
|
|
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;
|
|
DROP TEMPORARY TABLE t1;
|
|
SHOW CREATE TABLE t1;
|
|
# Verify that table is still locked
|
|
--error ER_TABLE_NOT_LOCKED
|
|
CREATE OR REPLACE TABLE t2 (a int);
|
|
DROP TABLE t1;
|
|
UNLOCK TABLES;
|
|
|
|
#
|
|
# Using CREATE SELECT
|
|
#
|
|
|
|
CREATE OR REPLACE TEMPORARY TABLE t1 (a int) SELECT * from t2;
|
|
SELECT * FROM t1;
|
|
CREATE OR REPLACE TEMPORARY TABLE t1 (b int) SELECT * from t2;
|
|
SELECT * FROM t1;
|
|
SHOW CREATE TABLE t1;
|
|
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;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (a int);
|
|
CREATE OR REPLACE TABLE t1 AS SELECT 1;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
create table t1 (a int);
|
|
--error ER_UPDATE_TABLE_USED
|
|
create or replace table t1 as select * from t1;
|
|
--error ER_UPDATE_TABLE_USED
|
|
create or replace table t1 as select a from (select a from t1) as t3;
|
|
--error ER_UPDATE_TABLE_USED
|
|
create or replace table t1 as select a from t2 where t2.a in (select a from t1);
|
|
drop table t1;
|
|
--enable_view_protocol
|
|
|
|
--echo #
|
|
--echo # Testing with normal tables
|
|
--echo #
|
|
|
|
CREATE OR REPLACE TABLE t1 (a int);
|
|
CREATE OR REPLACE TABLE t1 (a int, b int);
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (a int) SELECT * from t2;
|
|
SELECT * FROM t1;
|
|
TRUNCATE TABLE t1;
|
|
CREATE TABLE IF NOT EXISTS t1 (a int) SELECT * from t2;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (i int);
|
|
CREATE OR REPLACE TABLE t1 AS SELECT 1;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--disable_service_connection
|
|
# 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);
|
|
# Verify if table is still locked
|
|
SELECT * FROM t1;
|
|
INSERT INTO t1 values(1,1);
|
|
CREATE OR REPLACE TABLE t1 (a int, b int, c int);
|
|
INSERT INTO t1 values(1,1,1);
|
|
--error ER_TABLE_NOT_LOCKED
|
|
CREATE OR REPLACE TABLE t3 (a int);
|
|
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;
|
|
# Verify if table is still locked
|
|
SELECT * FROM t2;
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t1;
|
|
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);
|
|
--error ER_TABLE_NOT_LOCKED
|
|
CREATE OR REPLACE TABLE t3 (a int);
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
|
|
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;
|
|
SELECT * FROM t2;
|
|
--error ER_TABLE_NOT_LOCKED
|
|
SELECT * FROM t1 as t1_read;
|
|
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;
|
|
--error ER_TABLE_NOT_LOCKED
|
|
SELECT * from t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test also with InnoDB (transactional engine)
|
|
--echo #
|
|
|
|
create table t1 (i int) engine=innodb;
|
|
lock table t1 write;
|
|
create or replace table t1 (j int);
|
|
unlock tables;
|
|
show create table t1;
|
|
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;
|
|
unlock tables;
|
|
select * from t1 order by a,i;
|
|
drop table t1,t3,t4;
|
|
|
|
--echo #
|
|
--echo # Test the meta data locks are freed properly
|
|
--echo #
|
|
|
|
--disable_view_protocol
|
|
create database mysqltest2;
|
|
|
|
drop table if exists 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;
|
|
--source ../suite/innodb/include/wait_all_purged.inc
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
--error ER_TABLE_MUST_HAVE_COLUMNS
|
|
create or replace table test.t1;
|
|
show tables;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
--error ER_TABLE_MUST_HAVE_COLUMNS
|
|
create or replace table mysqltest2.t2;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
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;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
--error ER_DUP_FIELDNAME
|
|
create or replace table test.t1 (a int) select 1 as 'a', 2 as 'a';
|
|
show tables;
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
--error ER_DUP_FIELDNAME
|
|
create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a';
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
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;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
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;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
unlock tables;
|
|
drop table t1;
|
|
|
|
create table test.t1 (i int);
|
|
drop database mysqltest2;
|
|
drop table test.t1;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-23391 Server crash in close_thread_table or assertion, upon CREATE OR REPLACE TABLE under lock
|
|
--echo #
|
|
create table t1 (i int);
|
|
lock table t1 write;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
--error ER_DATA_TOO_LONG
|
|
create or replace table t1 (a char(1)) engine=Innodb select 'foo' as a;
|
|
show tables;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
create table t1 (i int);
|
|
drop table t1;
|
|
--enable_view_protocol
|
|
|
|
--echo #
|
|
--echo # Testing CREATE .. LIKE
|
|
--echo #
|
|
|
|
create or replace table t1 like t2;
|
|
create or replace table t1 like t2;
|
|
show create table t1;
|
|
drop table t1;
|
|
create table t1 (b int);
|
|
lock tables t1 write, t2 read;
|
|
create or replace table t1 like t2;
|
|
SELECT * FROM t1;
|
|
INSERT INTO t1 values(1);
|
|
CREATE OR REPLACE TABLE t1 like t2;
|
|
INSERT INTO t1 values(2);
|
|
unlock tables;
|
|
show create table t1;
|
|
drop table t1;
|
|
|
|
create or replace table t1 like t2;
|
|
--error ER_NONUNIQ_TABLE
|
|
create or replace table t1 like t1;
|
|
drop table t1;
|
|
|
|
CREATE TEMPORARY TABLE t1 like t2;
|
|
--error ER_NONUNIQ_TABLE
|
|
CREATE OR REPLACE TABLE t1 like t1;
|
|
--error ER_NONUNIQ_TABLE
|
|
CREATE OR REPLACE TABLE t1 like t1;
|
|
drop table t1;
|
|
|
|
CREATE TEMPORARY TABLE t1 like t2;
|
|
CREATE OR REPLACE TEMPORARY TABLE t3 like t1;
|
|
--error ER_NONUNIQ_TABLE
|
|
CREATE OR REPLACE TEMPORARY TABLE t3 like t3;
|
|
drop table t1,t3;
|
|
|
|
--echo #
|
|
--echo # Test with prepared statements
|
|
--echo #
|
|
|
|
prepare stmt1 from 'create or replace table t1 select * from t2';
|
|
execute stmt1;
|
|
select * from t1;
|
|
execute stmt1;
|
|
select * from t1;
|
|
drop table t1;
|
|
execute stmt1;
|
|
select * from t1;
|
|
deallocate prepare stmt1;
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # Test with views
|
|
--echo #
|
|
|
|
create view t1 as select 1;
|
|
create table if not exists t1 (a int);
|
|
--error ER_IT_IS_A_VIEW
|
|
create or replace table t1 (a int);
|
|
--error ER_IT_IS_A_VIEW
|
|
drop table t1;
|
|
drop view t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-5602 CREATE OR REPLACE obtains stricter locks than the
|
|
--echo # connection had before
|
|
--echo #
|
|
|
|
create table t1 (a int);
|
|
lock table t1 write, t2 read;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
|
|
create or replace table t1 (i int);
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
create or replace table t1 like t2;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
create or replace table t1 select 1 as f1;
|
|
--sorted_result
|
|
select LOCK_MODE,LOCK_TYPE, TABLE_SCHEMA,TABLE_NAME from information_schema.metadata_lock_info;
|
|
drop table t1;
|
|
unlock tables;
|
|
|
|
--echo #
|
|
--echo # MDEV-6560
|
|
--echo # Assertion `! is_set() ' failed in Diagnostics_area::set_ok_status
|
|
--echo #
|
|
|
|
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)
|
|
--let $con_id = `SELECT CONNECTION_ID()`
|
|
--send CREATE OR REPLACE TABLE t1 LIKE tmp
|
|
--connection default
|
|
let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist
|
|
WHERE state= 'Waiting for table metadata lock';
|
|
--source include/wait_condition.inc
|
|
--replace_result $con_id con_id
|
|
--eval KILL QUERY $con_id
|
|
|
|
--connection con1
|
|
--error ER_QUERY_INTERRUPTED
|
|
--reap
|
|
--send CREATE OR REPLACE TABLE t1 (a int)
|
|
|
|
--connection default
|
|
let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist
|
|
WHERE state= 'Waiting for table metadata lock';
|
|
--source include/wait_condition.inc
|
|
--replace_result $con_id con_id
|
|
--eval KILL QUERY $con_id
|
|
|
|
--connection con1
|
|
--error ER_QUERY_INTERRUPTED
|
|
--reap
|
|
--disconnect con1
|
|
--connection default
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# Cleanup
|
|
#
|
|
DROP TABLE t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-10824 - Crash in CREATE OR REPLACE TABLE t1 AS SELECT spfunc()
|
|
--echo #
|
|
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;
|
|
|
|
--echo #
|
|
--echo # MDEV-11129
|
|
--echo # CREATE OR REPLACE TABLE t1 AS SELECT spfunc() crashes if spfunc()
|
|
--echo # references t1
|
|
--echo #
|
|
|
|
CREATE OR REPLACE TABLE t1(a INT);
|
|
DELIMITER $$;
|
|
CREATE FUNCTION f1() RETURNS VARCHAR(16383)
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(1);
|
|
RETURN 'test';
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_UPDATE_TABLE_USED
|
|
CREATE OR REPLACE TABLE t1 AS SELECT f1();
|
|
LOCK TABLE t1 WRITE;
|
|
--error ER_TABLE_NOT_LOCKED
|
|
CREATE OR REPLACE TABLE t1 AS SELECT f1();
|
|
UNLOCK TABLES;
|
|
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-14410 - Assertion `table->pos_in_locked_tables == __null ||
|
|
--echo # table->pos_in_locked_tables->table == table' failed in
|
|
--echo # mark_used_tables_as_free_for_reuse
|
|
--echo #
|
|
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);
|
|
|
|
# Cleanup
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
--echo #
|
|
--echo # MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in
|
|
--echo # Locked_tables_list::unlock_locked_tables
|
|
--echo #
|
|
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;
|
|
# drops t2
|
|
--error ER_INVALID_DEFAULT
|
|
CREATE OR REPLACE TEMPORARY TABLE t1(c INT DEFAULT '');
|
|
# make sure we didn't leave locked tables mode
|
|
--error ER_TABLE_NOT_LOCKED
|
|
SELECT * FROM t3;
|
|
# drops t1
|
|
--error ER_INVALID_DEFAULT
|
|
CREATE OR REPLACE TEMPORARY TABLE t2(c INT DEFAULT '');
|
|
# make sure we didn't leave locked tables mode
|
|
--error ER_TABLE_NOT_LOCKED
|
|
SELECT * FROM t3;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t3;
|
|
--enable_service_connection
|
|
|
|
--echo #
|
|
--echo # MDEV-29697 Assertion failure in Diagnostics_area::set_ok_status
|
|
--echo # upon CREATE OR REPLACE causing ER_UPDATE_TABLE_USED
|
|
--echo #
|
|
CREATE TABLE t (a INT) ENGINE=MyISAM;
|
|
CREATE TABLE tm (a INT) ENGINE=MERGE UNION(t);
|
|
--error ER_UPDATE_TABLE_USED
|
|
CREATE OR REPLACE TABLE t LIKE tm;
|
|
|
|
# Cleanup
|
|
DROP TABLE IF EXISTS tm, t;
|
|
|
|
--echo #
|
|
--echo # End of 10.3 tests
|
|
--echo #
|
|
|
|
SET GLOBAL innodb_stats_persistent=@save_persistent;
|