MySQL 8.0 Atomic DDL – behavior change

In MySQL 8.0, we have now a new way of storing tables definitions: the Data Dictionary  !

Thanks to the Data Dictionary, DDLs in MySQL 8.0 are now Atomic. This means tat we have some changes in the behavior of the following DDLs for engines supporting  atomic DDL:

  1. DROP TABLES <tables> – All tables will be dropped, or none are dropped
  2. DROP SCHEMA – All entities in the schema will be dropped, or none are dropped
  3. DROP VIEW – All views are dropped, or none are dropped
  4. CREATE USER – All users are created, or none are created
  5. DROP USER – All users are dropped, or none are dropped
  6. GRANT – All users/roles in list are granted privileges, or none

This was detailed in Ståle’s recent post on the mysqlserverteam blog.

But what does that really mean ? Let’s test the following scenario on MySQL 5.7.20 and 8.0.3:

  1. we create a schema
  2. we create 2 tables (t1 and t2)
  3. we drop 2 tables in one statement but one of the table is not existing (t1 and t3)
  4. we list the tables present in the schema

We can see that as t3 is missing, the statement returns and error ERROR 1051 (42S02): Unknown table 'test_fred.t3' but in 5.7.20, the first table (t1) is deleted anyway. In MySQL 8.0 or the full DDL runs correctly or nothing is applied.

This behavior change can affect some weird workload that I’ve already encountered where multiple tables were dropped if they existed or not. You have been warned, this is way better and safer. Atomic DDLs is the first step of maybe full transactional ones 😉

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

2 Comments

  1. But it’s worth noting that IF EXISTS changes this behaviour (as expected). So applications should start to use (or remove) IF EXISTS for a reason that is smarter than “I want to show that I know MySQL syntaxes” 🙂

Leave a Reply to MySQL 8.0 Atomic DDL – behavior change - SSWUG.ORGCancel Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.