MySQL restore from dump working on OSX but not Windows

Question

I have created a dump from a CloudSQL database for local development. I can successfully run the dump file to restore the database on a computer running OSX but when trying on a colleagues computer running Windows, the execution fails part way with the following error:

ERROR 1822 (HY000) at line 479: Failed to add the foreign key constraint. Missing index for constraint 'fk_name' in the referenced table 'table_name'

The fk in reference points to the composite primary key of the table so I'm not sure how there could be a missing index?

Here is the failing code from the dump file:

KEY fk_name (user_id, org_id),
CONSTRAINT fk_name FOREIGN KEY (user_id, org_id) REFERENCES table_name (user_id, org_id)

The OSX computer is using MySQL Ver 14.14 Distrib 5.7.19. The windows computer is using MySQL Ver 8.0.15 (MySQL Community Server - GPL)

Those look way too generic to be the actual names. But this is probably something to do with the actual names. — Michael Hampton, Feb 25 '19 at 04:43
Please provide `SHOW CREATE TABLE`. Move to dba.stackexchange.com where it can get more traction. — Rick James, Mar 07 '19 at 13:55

score 0 · Accepted Answer · answered Feb 25 '19 at 04:57

0

So, looks like it was a compatibility issue with MySQL 8.

Downgraded the Windows machine to 5.7 and all working properly now.

answered Feb 25 '19 at 04:57

Matt

101
3

MySQL restore from dump working on OSX but not Windows

1 Answers1