Schedule Meeting


DROP TABLE improvements in MariaDB

by | Aug 6, 2020 | MariaDB

Need Help?  Click Here for Expert Support

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 view of Loch Lomond
Loch Lomond, Scotland


Before listing the improvements, it’s worth mentioning that a couple of these improvements were contributed by Tencent, as explained in a MariaDB Foundation post.

This is very important. MariaDB embraced the spirit of open source, and over the years this brought several important contribution from other companies.

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 10.5

Keep in mind that, before version 10.6, MariaDB lacked an important MySQL 8 feature: crash-safe DDL. MariaDB 10.6 partly supports crash-safe DDL. But even with 10.6, MariaDB implementation of DROP TABLE may behave differently than MySQL in a case like this:

DROP TABLE t1, t2;

If one of these tables doesn’t exist, MySQL won’t drop any of them. MariaDB will drop tables one by one, in the specified order. If a table doesn’t exist, MariaDB will abort the operation with an error, but some tables may have already been removed. The improvement brought by 10.6 is that a table cannot be half-dropped, even in case of a crash.

See the documentation about Atomic DDL.

Missing files

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.

Missing .ibd

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.*
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)

Missing .frm

.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.*
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. Contributed by Tencent.

Thanks to this improvement, MariaDB can now remove some strange behaviour that is not necessary anymore. MyISAM doesn’t need to overwrite files on table creation, and the keep_files system variable will become useless. See MDEV-23570 – deprecate keep_files_on_create.

IF EXISTS and replication

A failed DROP TABLE is not written to the binary log, and therefore it is not replicated.

If 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.


This syntax solves a potential problem with migrations, especially on replicas.

The problem

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:

  1. A long query acquired an MDL on the table.
  2. The migration try to acquire an MDL, and it’s queued.
  3. 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 WAIT and NOWAIT clauses for several SQL statements, including SELECT.

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.

Using this feature has the same effect of temporarily changing the values of lock_wait_timeout and innodb_lock_wait_timeout (wait for a transaction lock).

MySQL has similar syntax, NOWAIT and 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:


MariaDB has the Com_drop_temporary_table status variable, that counts the DROP TEMPORARY TABLE statements. The counterpart is Com_create_temporary_table.

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. Similarly, there is no way to know how many tables have been automatically deleted because their sessions ended, and how many still exist.


With MDEV-22158 – Document Galera Strict DDL, a new setting was introduced in 10.5. With wsrep_strict_ddl=1, DDL statements (including DROP TABLE, TRUNCATE TABLE, CREATE OR REPLACE TABLE) are only allowed for InnoDB tables. By default this setting is disabled.

See also



We discussed DROP TABLE improvements implemented by MariaDB. Not all improvements are well-documented, so if you know something that I don’t know please comment.

Federico Razzoli

Did you like this article?

Pic credits

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions:

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

How to query a REST API with MariaDB CONNECT engine

How to query a REST API with MariaDB CONNECT engine

Querying a remote REST API directly from the database: does it sound like pure madness? Some people will surely answer that yes, it does. So, first of all, let me spend a paragraph to show some great benefits of doing so. Then I'll show you how to query a REST API...

Coming up next, a ColumnStore webinar

Coming up next, a ColumnStore webinar

The 27th is fast approaching but you can still signup to our live webinar where we will be exploring the benefits of time series data using MariaDB ColumnStore. We will be jumping into a live demo with some example data and queries simulating high volume time series...

The benefits of MariaDB ColumnStore

The benefits of MariaDB ColumnStore

Last week Richard announced our projects on MariaDB ColumnStore. Take a look at his great post, if you didn't already. Since then, I've got some questions from customers, colleagues and friends: why did you guys decide to robustly invest into ColumnStore, and offer...


Need Help?  Click Here for Expert Support


Submit a Comment

Your email address will not be published. Required fields are marked *