Why do I get ERROR 1824 (HY000) while importing MySQL dump from 5.7 to 8.0.x?

Question

I have a MySQL 5.7.x dump with about 12GB that I try to import into a newly installed MySQL 8.0.x local instance.

mysql -u username -p test < DB-210508.sql  

The import starts but fails with the following message:

ERROR 1824 (HY000) at line 518: Failed to open the referenced table 'table-name'

I am wondering why there is a problem with foreign keys and if it is not more due to the fact that I am importing into 8.0.x

The MySQL 8.0.x instance is configured like this:

[mysqld]
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 32
innodb_buffer_pool_chunk_size = 134217728
collation_server        = utf8_unicode_ci
character_set_server    = utf8

What could be the reason for the failed import?

Answer 1

This error happens when you have a classic “chicken and egg” problem. The most common situations for this would be:

• populating a self-referencing table where a lower ID requires the existence of a higher ID
  ⇢ a good example of this would be an Employee table where record 33 had a manager with an ID of 999. 33 could not be recorded until 999 existed in the table.
• populating a table that depends on another table that has not yet been created
  ⇢ this can happen when you have an Employee table that has references to User, but User isn’t created until after Employee.

The error you received was:

… Failed to open the referenced table 'table-name'

This points to the second item as being the most likely culprit.

The simplest way to resolve this would be to remove the foreign key checks when importing the data, then restore the checks after the import is complete. You can do this be editing your 12GB .sql file to have this at the very start:

SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;

Then, at the very end of the import file:

SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;

Note: With the foreign key check disabled, there is a possibility of recording bad data. If this is something you need to avoid for regulatory purposes, do not follow this “quick” merhod.