mirror of
https://github.com/MariaDB/server.git
synced 2025-02-09 23:24:11 +01:00
1bd681c8b3
This is a complete rewrite of DROP TABLE, also as part of other DDL, such as ALTER TABLE, CREATE TABLE...SELECT, TRUNCATE TABLE. The background DROP TABLE queue hack is removed. If a transaction needs to drop and create a table by the same name (like TRUNCATE TABLE does), it must first rename the table to an internal #sql-ib name. No committed version of the data dictionary will include any #sql-ib tables, because whenever a transaction renames a table to a #sql-ib name, it will also drop that table. Either the rename will be rolled back, or the drop will be committed. Data files will be unlinked after the transaction has been committed and a FILE_RENAME record has been durably written. The file will actually be deleted when the detached file handle returned by fil_delete_tablespace() will be closed, after the latches have been released. It is possible that a purge of the delete of the SYS_INDEXES record for the clustered index will execute fil_delete_tablespace() concurrently with the DDL transaction. In that case, the thread that arrives later will wait for the other thread to finish. HTON_TRUNCATE_REQUIRES_EXCLUSIVE_USE: A new handler flag. ha_innobase::truncate() now requires that all other references to the table be released in advance. This was implemented by Monty. ha_innobase::delete_table(): If CREATE TABLE..SELECT is detected, we will "hijack" the current transaction, drop the table in the current transaction and commit the current transaction. This essentially fixes MDEV-21602. There is a FIXME comment about making the check less failure-prone. ha_innobase::truncate(), ha_innobase::delete_table(): Implement a fast path for temporary tables. We will no longer allow temporary tables to use the adaptive hash index. dict_table_t::mdl_name: The original table name for the purpose of acquiring MDL in purge, to prevent a race condition between a DDL transaction that is dropping a table, and purge processing undo log records of DML that had executed before the DDL operation. For #sql-backup- tables during ALTER TABLE...ALGORITHM=COPY, the dict_table_t::mdl_name will differ from dict_table_t::name. dict_table_t::parse_name(): Use mdl_name instead of name. dict_table_rename_in_cache(): Update mdl_name. For the internal FTS_ tables of FULLTEXT INDEX, purge would acquire MDL on the FTS_ table name, but not on the main table, and therefore it would be able to run concurrently with a DDL transaction that is dropping the table. Previously, the DROP TABLE queue hack prevented a race between purge and DDL. For now, we introduce purge_sys.stop_FTS() to prevent purge from opening any table, while a DDL transaction that may drop FTS_ tables is in progress. The function fts_lock_table(), which will be invoked before the dictionary is locked, will wait for purge to release any table handles. trx_t::drop_table_statistics(): Drop statistics for the table. This replaces dict_stats_drop_index(). We will drop or rename persistent statistics atomically as part of DDL transactions. On lock conflict for dropping statistics, we will fail instantly with DB_LOCK_WAIT_TIMEOUT, because we will be holding the exclusive data dictionary latch. trx_t::commit_cleanup(): Separated from trx_t::commit_in_memory(). Relax an assertion around fts_commit() and allow DB_LOCK_WAIT_TIMEOUT in addition to DB_DUPLICATE_KEY. The call to fts_commit() is entirely misplaced here and may obviously break the consistency of transactions that affect FULLTEXT INDEX. It needs to be fixed separately. dict_table_t::n_foreign_key_checks_running: Remove (MDEV-21175). The counter was a work-around for missing meta-data locking (MDL) on the SQL layer, and not really needed in MariaDB. ER_TABLE_IN_FK_CHECK: Replaced with ER_UNUSED_28. HA_ERR_TABLE_IN_FK_CHECK: Remove. row_ins_check_foreign_constraints(): Do not acquire dict_sys.latch either. The SQL-layer MDL will protect us. This was reviewed by Thirunarayanan Balathandayuthapani and tested by Matthias Leich.
293 lines
11 KiB
Text
293 lines
11 KiB
Text
################################################################################
|
|
# t/partition_basic_innodb.test #
|
|
# #
|
|
# Purpose: #
|
|
# Tests around Create Partitioned table using DATA/INDEX DIR #
|
|
# InnoDB branch #
|
|
# #
|
|
#------------------------------------------------------------------------------#
|
|
# Original Author: mleich #
|
|
# Original Date: 2006-03-05 #
|
|
# Change Author: mattiasj #
|
|
# Change Date: 2008-02-05 #
|
|
# Change: copied it from partition_basic_innodb.test and kept DATA DIR #
|
|
# Change Author: mattiasj #
|
|
# Change Date: 2008-03-16 #
|
|
# Change: Replaced all test with alter -> myisam, since innodb does not support#
|
|
# Change Author: Kevin lewis #
|
|
# Change Date: 2012-03-02 #
|
|
# Change: WL5980 activates DATA DIRECTORY for InnoDB #
|
|
################################################################################
|
|
|
|
# NOTE: As of WL5980, InnoDB supports DATA DIRECTORY, but not INDEX DIRECTORY.
|
|
# See innodb.innodb-tablespace for tests using partition engine, innodb
|
|
# and DATADIRECTORY. The purpose of this test is to show that a
|
|
# partitioned table remembers the DATA/INDEX DIR and it is used if
|
|
# altered to MyISAM
|
|
#
|
|
--echo #
|
|
--echo # Verify that the DATA/INDEX DIR is stored and used if ALTER to MyISAM.
|
|
--echo #
|
|
--source include/have_innodb.inc
|
|
# The server must support partitioning.
|
|
--source include/have_partition.inc
|
|
# The server must support symlink for DATA/INDEX DIRECTORY.
|
|
--source include/have_symlink.inc
|
|
# windows does not support symlink for DATA/INDEX DIRECTORY.
|
|
--source include/not_windows.inc
|
|
|
|
# Does not work with --embedded
|
|
--source include/not_embedded.inc
|
|
|
|
let $MYSQLD_DATADIR= `select @@datadir`;
|
|
|
|
# These values can change during the test
|
|
SET @file_per_table= @@GLOBAL.innodb_file_per_table;
|
|
SET @strict_mode= @@SESSION.innodb_strict_mode;
|
|
|
|
--mkdir $MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--mkdir $MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
|
|
SET SESSION innodb_strict_mode = ON;
|
|
|
|
--echo #
|
|
--echo # InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON
|
|
--echo #
|
|
SET GLOBAL innodb_file_per_table = OFF;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
--error ER_CANT_CREATE_TABLE
|
|
eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
|
|
PARTITION BY HASH (c1) (
|
|
PARTITION p0
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
|
|
PARTITION p1
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
|
|
);
|
|
--replace_result ./ MYSQLD_DATADIR/ $MYSQLD_DATADIR MYSQLD_DATADIR
|
|
SHOW WARNINGS;
|
|
|
|
--echo #
|
|
--echo # InnoDB is different from MyISAM in that it uses a text file
|
|
--echo # with an '.isl' extension instead of a symbolic link so that
|
|
--echo # the tablespace can be re-located on any OS. Also, instead of
|
|
--echo # putting the file directly into the DATA DIRECTORY,
|
|
--echo # it adds a folder under it with the name of the database.
|
|
--echo # Since strict mode is off, InnoDB ignores the INDEX DIRECTORY
|
|
--echo # and it is no longer part of the definition.
|
|
--echo #
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
SET GLOBAL innodb_file_per_table = ON;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
|
|
PARTITION BY HASH (c1)
|
|
(PARTITION p0
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
|
|
PARTITION p1
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
|
|
);
|
|
SHOW WARNINGS;
|
|
|
|
--echo # Verifying .frm, .par, .isl & .ibd files
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir/test
|
|
--echo # The ibd tablespaces should not be directly under the DATA DIRECTORY
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
|
|
FLUSH TABLES;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Verify that the DATA/INDEX DIRECTORY is stored and used if we
|
|
--echo # ALTER TABLE to MyISAM.
|
|
--echo #
|
|
ALTER TABLE t1 engine=MyISAM;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t1;
|
|
--echo # Verifying .frm, .par and MyISAM files (.MYD, MYI)
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
|
|
--echo #
|
|
--echo # Now verify that the DATA DIRECTORY is used again if we
|
|
--echo # ALTER TABLE back to InnoDB.
|
|
--echo #
|
|
SET SESSION innodb_strict_mode = ON;
|
|
ALTER TABLE t1 engine=InnoDB;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t1;
|
|
--echo # Verifying .frm, .par, .isl and InnoDB .ibd files
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
--echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
|
|
--list_files $MYSQLTEST_VARDIR/mysql-test-data-dir/test
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work
|
|
--echo # properly when used with DATA DIRECTORY
|
|
--echo #
|
|
let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir;
|
|
let $alt_data_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir;
|
|
SET GLOBAL innodb_file_per_table = ON;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY KEY (myid)
|
|
(
|
|
PARTITION p0001 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0002 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0003 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0004 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
|
|
);
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB DATA DIRECTORY = '$data_dir_path';
|
|
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1, t2;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY RANGE (myid)
|
|
(
|
|
PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
|
|
);
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path';
|
|
|
|
insert into t1 values (1, 'one');
|
|
insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
|
|
|
|
select * from t1;
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t1;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t2;
|
|
select * from t1;
|
|
select * from t2;
|
|
DROP TABLE t1, t2;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY RANGE (myid)
|
|
(
|
|
PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
|
|
PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
|
|
);
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB;
|
|
|
|
insert into t1 values (1, 'one');
|
|
insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
|
|
|
|
select * from t1;
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t1;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t2;
|
|
select * from t1;
|
|
select * from t2;
|
|
DROP TABLE t1, t2;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY RANGE (myid)
|
|
(
|
|
PARTITION p0001 VALUES LESS THAN (50) ENGINE = INNODB,
|
|
PARTITION p0002 VALUES LESS THAN (150) ENGINE = INNODB,
|
|
PARTITION p0003 VALUES LESS THAN (1050) ENGINE = INNODB,
|
|
PARTITION p0004 VALUES LESS THAN (10050) ENGINE = INNODB
|
|
);
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path';
|
|
|
|
insert into t1 values (1, 'one');
|
|
insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
|
|
|
|
select * from t1;
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t1;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
SHOW CREATE TABLE t2;
|
|
select * from t1;
|
|
select * from t2;
|
|
DROP TABLE t1, t2;
|
|
--echo #
|
|
--echo # Cleanup
|
|
--echo #
|
|
|
|
--rmdir $MYSQLTEST_VARDIR/mysql-test-data-dir/test
|
|
--rmdir $MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
--rmdir $MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
|
|
SET GLOBAL innodb_file_per_table=@file_per_table;
|
|
SET SESSION innodb_strict_mode=@strict_mode;
|