Last updated on 10 September 2020
MariaDB implemented several improvements to the
DROP TABLE statement. We summarise them here. This article will be kept up to date when MariaDB implements further improvements, in the future.
A foreword on terminology
In the text, I made an effort to avoid the word slave, which is traditionally used in MariaDB and MySQL.
MariaDB implemented the
REPLICA synonym in version 10.5. However I still use commands and variable names containing the slave word, because there is no other option for older MariaDB versions.
A difference between MySQL and MariaDB
Keep in mind that MariaDB still lacks an important MySQL 8 feature: crash-safe DDL. Among other things, this means that the following statement may behave differently in MariaDB and MySQL:
DROP TABLE t1, t2;
If t2 does not exist, t1 will still be deleted in MariaDB, but not in MySQL.
A file may miss from a system for several reasons, including human mistakes, aborted operations, or bugs in devops tools. If it happens, we want to be able to drop and recreate the table. Not being able to do so would be a major problem.
If an InnoDB
.idb file is missing, this is not a problem for
DROP TABLE. This is something I’d like to highlight here, but not a MariaDB-specific improvement.
MariaDB [test]> CREATE TABLE x (a int); Query OK, 0 rows affected (0.011 sec) MariaDB [test]> \! ls -1 /var/lib/mysql/test/x.* /var/lib/mysql/test/x.frm /var/lib/mysql/test/x.ibd MariaDB [test]> \! rm /var/lib/mysql/test/x.ibd MariaDB [test]> DROP TABLE x; Query OK, 0 rows affected (0.008 sec)
(In case you’re confused, the
mysql command line client allows to run commands in the shell by prefixing lines with “
!\“. A handy feature)
.frm files contain the tables definitions in MariaDB and in MySQL up to version 5.7. Up to MariaDB 10.4, a missing
.frm file caused
DROP TABLE to fail. To drop the table it was necessary to drop and recreate the database.
Starting from MariaDB 10.5, dropping a table is possible even if the
.frm file is missing:
MariaDB [test]> CREATE TABLE x (a int); Query OK, 0 rows affected (0.009 sec) MariaDB [test]> \! ls -1 /var/lib/mysql/test/x.* /var/lib/mysql/test/x.frm /var/lib/mysql/test/x.ibd MariaDB [test]> \! rm /var/lib/mysql/test/x.frm MariaDB [test]> CREATE OR REPLACE TABLE x (a int); ERROR 1813 (HY000): Tablespace for table '`test`.`x`' exists. Please DISCARD the tablespace before IMPORT MariaDB [test]> DROP TABLE x; Query OK, 0 rows affected (0.006 sec)
Unfortunately, as you can see
CREATE OR REPLACE TABLE still fails. I believe it should work, because it’s supposed to act like
DROP TABLE followed by
CREATE TABLE, so I reported MDEV-23185.
More problems and storage engines
Here I’m only discussing InnoDB, and I won’t cover all problems that
DROP TABLE may encounter. For more information see this completed JIRA task: MDEV-11412 – Ensure that table is truly dropped when using DROP TABLE.
IF EXISTS and replication
DROP TABLE is not written to the binary log, and therefore it is not replicated.
DROP TABLE succeeds on the master and the table does not exist in the replica, by default the event will just be ignored by the replica and replication will not break. To make the replicas crash in these cases, change the value of
slave_ddl_exec_mode. This variable was added in 10.0.
-- Don't let replicas crash if a table is missing (default): SET GLOBAL slave_ddl_exec_mode := IDEMPOTENT; -- Make replicas crash: SET GLOBAL slave_ddl_exec_mode :- STRICT;
DROP TABLE IF EXISTS is meant to eliminate tables that could exist or not, and therefore it is written to the binary log even if the table does not exist.
Starting with MariaDB 10.5 (commit dfb41fddf6),
DROP TABLE IF EXISTS is not written to the binary log if the table exists but cannot be dropped. This avoids undesired situations where a table disappears from the replicas but survives in the master.
WAIT, NOWAIT (10.3)
This syntax solves a potential problem with migrations, especially on replicas.
When you run a migration, be it an
ALTER TABLE or a
DROP TABLE, it tries to acquire a metadata lock (MDL). All transactions that involve the table also try to acquire a metadata lock on it. If you try to run a migration on a table while a long query or transaction is running on it, this causes a major problem:
- A long query acquired an MDL on the table.
- The migration try to acquire an MDL, and it’s queued.
- More transactions try to acquire an MDL on that table, and they are queued.
If the migration is being made by a client on a replica on which long
SELECT run, it may end up blocking replication, because replicated transactions cannot acquire an MDL on the table.
Solution: a timeout
MariaDB 10.3 added support for the
NOWAIT clauses for several SQL statements, including
WAIT allows to specify a timeout, in second, in case
DROP TABLE is being blocked by another session. If the wait exceeds the timeout,
DROP TABLE will fail with an error.
NOWAIT is similar, except the timeout is zero seconds. If there is a lock on the table,
DROP TABLE will fail.
MySQL has similar syntax,
SKIP LOCKED. But notice that its purpose is completely different: it only allows
SELECT to return an error if some rows are locked by another transaction, or just skip them. It would argue that MySQL feature is more powerful, but it has less use cases.
Hint for migrations
If you wrote a script to run migrations, I suggest to always add a timeout that can be configured globally or on per-server basis, depending on how a replica is used. You don’t need to set a short timeout, just ask yourself how much you can afford the replica to lag. Keep in mind that the migration may cause a server to lag even if it doesn’t wait for another query to complete.
Locks and performance
Tasks and commit that affect
DROP TABLE locks and performance:
- MDEV-8069 – DROP or rebuild of a large table may lock up InnoDB, fixed in 10.5.
Note however that temporary tables can also be dropped with
DROP TABLE, which is related to
Com_drop_table (also present in MySQL). There is no way to know how many
DROP TABLEs have dropped a temporary table.
DROP TABLE improvements implemented by MariaDB. Not all improvements are well-documented, so if you know something that I don’t know please comment.