mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-07-28 14:10:46 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/create_or_replace.test
Sergey Vojtovich 18985d8471 MDEV-19749 - MDL scalability regression after backup locks 
Statements that intend to modify data have to acquire protection
against ongoing backup. Prior to backup locks, protection against
FTWRL was acquired in form of 2 shared metadata locks of GLOBAL
(global read lock) and COMMIT namespaces. These two namespaces
were separate entities, they didn't share data structures and
locking primitives. And thus they were separate contention
points.

With backup locks, introduced by 7a9dfdd, these namespaces were
combined into a single BACKUP namespace. It became a single
contention point, which doubled load on BACKUP namespace data
structures and locking primitives compared to GLOBAL and COMMIT
namespaces. In other words system throughput has halved.

MDL fast lanes solve this problem by allowing multiple contention
points for single MDL_lock. Fast lane is scalable multi-instance
registry for leightweight locks. Internally it is just a list of
granted tickets, close counter and a mutex.

Number of fast lanes (or contention points) is defined by the
metadata_locks_instances system variable. Value of 1 disables fast
lanes and lock requests are served by conventional MDL_lock data
structures.

Since fast lanes allow arbitrary number of contention points, they
outperform pre-backup locks GLOBAL and COMMIT.

Fast lanes are enabled only for BACKUP namespace. Support for other
namespaces is to be implemented separately.

Lock types are divided in 2 categories: lightweight and heavyweight.

Lightweight lock types represent DML: MDL_BACKUP_DML,
MDL_BACKUP_TRANS_DML, MDL_BACKUP_SYS_DML, MDL_BACKUP_DDL,
MDL_BACKUP_ALTER_COPY, MDL_BACKUP_COMMIT. They are fully compatible
with each other. Normally served by corresponding fast lane, which is
determined by thread_id % metadata_locks_instances.

Heavyweight lock types represent ongoing backup: MDL_BACKUP_START,
MDL_BACKUP_FLUSH, MDL_BACKUP_WAIT_FLUSH, MDL_BACKUP_WAIT_DDL,
MDL_BACKUP_WAIT_COMMIT, MDL_BACKUP_FTWRL1, MDL_BACKUP_FTWRL2,
MDL_BACKUP_BLOCK_DDL. These locks are always served by conventional
MDL_lock data structures. Whenever such lock is requested, fast
lanes are closed and all tickets registered in fast lanes are
moved to conventional MDL_lock data structures. Until such locks
are released or aborted, lightweight lock requests are served by
conventional MDL_lock data structures.

Strictly speaking moving tickets from fast lanes to conventional
MDL_lock data structures is not required. But it allows to reduce
complexity and keep intact methods like: MDL_lock::visit_subgraph(),
MDL_lock::notify_conflicting_locks(), MDL_lock::reschedule_waiters(),
MDL_lock::can_grant_lock().

It is not even required to register tickets in fast lanes. They
can be implemented basing on an atomic variable that holds two
counters: granted lightweight locks and granted/waiting heavyweight
locks. Similarly to MySQL solution, which roughly speaking has
"single atomic fast lane". However it appears to be it won't bring
any better performance, while code complexity is going to be much
higher.
2025-07-15 23:19:06 +04:00

520 lines
14 KiB
Text
Raw Permalink Blame History

#
# 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;
--sorted_result
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;
Reference in a new issue View git blame Copy permalink