In this case a failing foreign key constraint was apparently caused by an encoding mismatch. Almost all of the tables in my database had collation utf8_unicode_ci. A few had utf8mb4_unicode_ci; I have converted those to utf8_unicode_ci and will test later if that made any difference…
What else could I check? Is there a way to safely “reset” the tables that cause these problems? Or does the ‘integrity constraint violation’ point to another problem - what problem does it point to?
Edit:
I am now converting all tables to utf8mb4_unicode_ci, following this recipe to fix the general foreign key mess.
Using alter table lead_event_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;, I get the following unusual error:
#1025 - Error on rename of ‘./mydatabase/#sql-a26_721a’ to ‘./mydatabase/lead_event_log’ (errno: 150 “Foreign key constraint is incorrectly formed”)
Same error on tables oauth2_accesstokens, oauth2_clients, oauth2_refreshtokens, users, …
Several other tables, like plugin_citrix_events, have this error:
Specified key was too long; max key length is 3072 bytes
Does anyone know what this means?