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:
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 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” 🙂