How To Resolve the Foreign Key Error When Importing a Table in WordPress

When importing a large database file into WordPress using phpMyAdmin, you might encounter the following error:

Error:
Static analysis:
1 errors were found during analysis.
Unrecognized keyword. (near ‘ON’ at position 25)

SQL Query:

SET FOREIGN_KEY_CHECKS = ON;

MySQL said:

#2006 – MySQL server has gone away

This error occurs mainly due to improper configurations in MySQL settings or connectivity issues when handling large database imports. Here’s how you can fix this issue and successfully import your database.

Fixing the Error

Step 1: Modify PHP and MySQL Configuration Files

Before proceeding with the import, you need to adjust the PHP and MySQL settings to accommodate large database files.

1. Update php.ini File

Open XAMPP Control Panel.
Click on Config under Apache.
Select php.ini and update the following settings:
max_execution_time = 600
max_input_time = 600
memory_limit = 1024M
post_max_size = 1024M
upload_max_filesize = 1024M
Save the file and close it.

2. Update my.ini File

Click on Config under MySQL in the XAMPP Control Panel.
Select my.ini and modify the following line:
max_allowed_packet = 1024M
Save the file and restart the MySQL service.

Step 2: Importing the Large Database via Command Line

Sometimes, phpMyAdmin has limitations when handling large databases. To bypass these limitations, use the MySQL command line.

Open XAMPP Control Panel.
Click Shell to open the command prompt.
Enter the following command to import your database:
mysql -p -u root DBname < C:\xampp\DBfolder\db.sql
If prompted for a password, simply press Enter (by default, it is blank).
The database import process should now start.

Additional Troubleshooting

1. Check SQL File for Errors

Ensure that your SQL file does not have syntax errors. Open it in a text editor and verify that SET FOREIGN_KEY_CHECKS = ON; is correctly formatted.

2. Increase Timeout Limits in phpMyAdmin

If the error persists, edit config.inc.php in phpMyAdmin:

$cfg['ExecTimeLimit'] = 600;

3. Optimize Large Database Imports

For very large databases, consider splitting them into smaller chunks using tools like SQLDumpSplitter.

Conclusion

By modifying PHP and MySQL configurations and using the command line for database import, you can successfully resolve the SET FOREIGN_KEY_CHECKS = ON error and prevent MySQL server has gone away issues. Implement these changes, restart the services, and try importing your database again.

If you still encounter errors, checking server logs and database structure for inconsistencies can further help in debugging the issue.

You may also like: