MySQL 8 with foreign keys: diff/push deadlock errors

[derekperkins]

Running skeema diff against an active master results in a deadlock error about 80% of the time for me. I'm not sure what I can do about it.
Error executing DDL in workspace: Error 1213: Deadlock found when trying to get lock; try restarting transaction

[evanelias]

That's surprising, I've never seen deadlocks in that specific spot. Is Skeema connecting to MySQL directly, or is it going through middleware / proxy (e.g. vtgate, proxysql, etc)? If so, I'd wonder if the deadlocks are actually coming from the middleware instead of mysql. One way to check would be to run SHOW ENGINE INNODB STATUS on the relevant mysqld right after one of these deadlocks, and see if the latest deadlock section shows it.

In any case, for background on where this error is occurring:

• In order to introspect the filesystem versions of your tables (the desired state), Skeema first executes the CREATE statements from the filesystem in a temporary location called a workspace. By default the workspace will be a temporary schema on the target mysql server, which Skeema creates, populates with tables, introspects, and then cleans up.
• To speed up that process for users with high table counts, Skeema runs those CREATEs concurrently using up to 15 connections. Historically this has never been problematic -- I'm unaware of any situation in which concurrent CREATE TABLEs encounter deadlocks.

If Skeema is already connecting to mysqld directly, a few follow-up questions:

• What version/flavor of MySQL are you using?
• Is your table count per schema very high? (say, 300+ tables in one schema)
• Do you use foreign keys?
• Do you use partitioned tables?
• Do you have Docker on the machine you're running Skeema from? If so, you could try Skeema's workspace=docker setting to move the workspace to an ephemeral container. I suspect this will side-step your problem, but I'm still very curious about the original root cause :)

[derekperkins]

It is connecting directly to MySQL. Ironically, the schema name we are using is workspaces. I got past it both on skeema diff and skeema push by trying a bunch of times.

What version/flavor of MySQL are you using?

Percona 8.0.16

Is your table count per schema very high? (say, 300+ tables in one schema)

~20 tables

Do you use foreign keys?

Yes, about 1-3 per table

Do you use partitioned tables?

Not in this schema

Do you have Docker on the machine you're running Skeema from? If so, you could try Skeema's workspace=docker setting to move the workspace to an ephemeral container. I suspect this will side-step your problem, but I'm still very curious about the original root cause :)

This is running inside Kubernetes in production, so I'm not keen to test that. :)

[evanelias]

Thanks, I'll look more into this. It's entirely possible that MySQL 8.0's new innodb data dictionary introduced new possible deadlocks when concurrently creating tables with FKs. I've previously seen that in 8.0 when concurrently dropping tables with FKs; Skeema already has logic to handle that. That condition was trivially easy to repro though (e.g. 2 tables with FKs + 2 concurrent threads), so perhaps the CREATE one is sneakier due to CREATE being lighter-weight than DROP.

[evanelias]

Also wanted to mention this potentially relates to #93, which I hope to focus on soon.

[evanelias]

I'm able to reproduce this now, and have confirmed it's specific to MySQL 8 with foreign keys. So far it does indeed appear to be related to concurrently creating tables with FKs in a workspace -- similar to the already-handled situation with concurrently dropping tables with FKs in a workspace, but less frequent unless the percentage of tables with FKs is high.

I'm aiming to have a fix in master (plus integration test coverage) by tomorrow. Thank you again for reporting this!

[evanelias]

Now fixed in master branch, and will be included in the next patch release (probably in 1 to 2 weeks). This fix has been deployed to skeema.io CI today as well. Thanks again for reporting this bug!

When cleaning up the related concurrency logic here I used your awesome package https://github.com/nozzle/throttler, so thank you for that too btw! It's much cleaner than the approach I had been using (fiddling with DB.SetMaxOpenConns) and makes the error tracking easier to manage as well :)