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:
- DROP TABLES <tables> – All tables will be dropped, or none are dropped
- DROP SCHEMA – All entities in the schema will be dropped, or none are dropped
- DROP VIEW – All views are dropped, or none are dropped
- CREATE USER – All users are created, or none are created
- DROP USER – All users are dropped, or none are dropped
- GRANT – All users/roles in list are granted privileges, or none
This was detailed in St 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:
- we create a schema
- we create 2 tables (t1 and t2)
- we drop 2 tables in one statement but one of the table is not existing (t1 and t3)
- 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 😉
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” 🙂