CREATE TABLE events in wrong order in a binary log

General

Escalation

General

Escalation

Description

When an application is concurrently creating tables, the tables can be
logged into a binary log in the wrong order. This causes replication to
break if foreign keys are used.

How to repeat:
1.
Add extra {color:#FF0000}DBUG_EXECUTE_IF{color} condition into function:

2. Start two sessions.

3. MySQL will write events in the following order to the binary log:

Environment

None

AFFECTED CS IDs

262499

Smart Checklist

Activity

Show:

George Lorch September 9, 2019 at 6:02 PM

Upon further investigation, a backport of the fix to 5.7 is not at all trivial. The new 8.0 Data Dictionary provides a fairly comprehensive API for locking and accessing table and constraint definitions that is easily accessible from just about any layer of the server code. 5.7 does not have any of this and so it is not possible to apply the proper locks and obtain the foreign key information at this point during the table creation. Considering that this is a highly unusual and arguably very poor use case and that the amount of effort required to 'fix' this in 5.7 is quite high, I do not believe that we will be able fox this for 5.7.

Zsolt Parragi September 2, 2019 at 3:18 PM

This is fixed in 8.0, by WL#6049 ( https://dev.mysql.com/worklog/task/?id=6049 ), as also mentioned in the upstream bug report now.

The fix is trivial, but it needs to know about the foreign key dependencies of the table currently being created, which is fetched from the data dictionary in 8.0. We do have some foreign key helper functions in 5.7 already, but I'm not sure if they work this early during a table's creation.