WordPress MySQL database import errors (and fixes)

Writing this guide for the many DB import issues out there.

I recently had to sit through a brutal 70-site migration and ran into every DB import problem in the past week. Like all the most annoying ones, all coming together to make me wanna jump off a balcony.

Let’s cover them here in this guide so you don’t wanna kill yourself like I did.

Background

Trust me, I’ve been there before. You’re not a beginner. You do everything right. Heck, you’ve done this a thousand times. Export and re-import databases. It’s not new to you!

But every once in a while, you got a son-of-a-gun issue where shit won’t import properly. It’s always the gosh-darn import! And the guides out there don’t help much or are not totally accurate to your issue.

My tactics below might work for you.

• Try one or another. Or a mix. And by some random chance, your import might work magically on the 14th try.
• PLEASE MAKE BACKUPS OF YOUR DATABASE (before doing crazy stuff).

Good luck to all my fellow DB-importers out there!

1. Size & timeout issues

Most of you will get this problem at one point in your life or another. The database is so big (or server so slow or internet so slow) that the server times out during the import. Even though, phpMyAdmin might let you continue where you left off…it’s still better to have a clean import that doesn’t have any issues.

NOTE: sometimes your database may not appear big. Maybe it’s only a 200MB…make sure you go to phpMyAdmin, select all tables and pick “OPTIMIZE” option. Don’t be surprised when it now becomes 2GB (which is its true size).

Ways to fix:

• Go to PHP settings (in your server settings or php.ini) and increase max_upload_size, max_execution_time, max_input_time.
• Another thing you can do is to export the database in compressed format. When exporting, choose “Custom” then scroll a little and pick “gzipped” in the compression dropdown. And when importing, you simply import that compressed GZIP archive.
• If your database is soooo huge, then export only several tables at a time, or even individually. More often than not…it’s just one (or a few) giant table/s that’s holding up everything. Just select them off as needed.
• You can also get around it by importing your database through SSH (using mysql shell or WP-CLI) but I’m guessing you’re not technically there if you’re reading my guide.
• Sometimes the import didn’t go clean and you have to fix the issue of phpMyAdmin stuck when dropping tables.

2. Auto-increment, primary key, duplicate table, etc

These could all fall under “corrupted database table” issues. For whatever reason, you’re presented with a scary red box and some random message like 1062 - Duplicate entry '0' for key 'PRIMARY' import as separate tables. Then you read online and find a billion guides that say different things and they all don’t help.

Try my simpler fixes first:

• Export in GZIP compressed.
• Select all tables and run “repair” before exporting. (Only works for MyISAM tables.)
• Select all tables and run “optimize” before exporting.
• Select all tables and run “analyze”, or “optimize” and then “analyze” before exporting.
• If the error says something about “Duplicate entry” for any number other than “0”, you could try going directly to that entry in the table and change that number to something else (that’s not being used). And if it’s a transient, you’re probably fine to just delete it.
• Ok sure…if you’re getting the PRIMARY KEY issue on your options table, you can visit it and then click “Structure” tab and click “Change” on the primary key row and then check the tiny “A.I.” box and save. But that has never resolved my issue. If you got serious table corruption, you’ll need to run some manual SQL commands. Many have written scripts out there but I suggest you hire a dev to fix for you. As every issue is slightly different in what you need to do.
• Another tactic that works is to pick “Custom” and deselect any tables that had issues during export. Then export those troublesome tables individually (by themselves). Separating the tables also helped magically many times.
• Export use WP Migrate plugin. Sometimes the issue is migrating databases between different DB versions like MariaDB vs MySQL.

3. WordPress shows new installation screen

So you’ve imported just fine but now WordPress acts like a blank install (when you visit the site) even though you can see the database in phpMyAdmin.

This is an easy fix. It’s like that you just need to edit the prefix in your wp-config.php file:

• Check your prefix in phpMyAdmin. The default table prefix is wp_ which gives table names like wp_options, wp_posts, etc.
• If your table prefix is something else (say ddb12_), then you need to go to your wp-config.php file and edit the line that says $table_prefix = 'wp_';.

4. Database already exists

A classic issue where your database doesn’t show up anywhere in your control panel, or phpMyAdmin…yet when you try to create it, it says it already exists. So you have to delete the databases in order to create new ones, but you can’t delete if you don’t see them in there!

So the answer is to delete them from the system and mysql registry. (NOTE: I had this issue when transferring entire accounts from server to another, but the transfer never completed.)

• First step is to get into mysql shell in terminal entering this command mysql -u root -p (mysql root pass available using cat /root/.my.cnf), then show databases using SHOW DATABASES;
• If you see the databases here, you can try to drop them using SHOW DATABASES; but I’m quite sure it won’t work, for whatever error.
• You need to look up the database directory path using show variables where Variable_name ='datadir';, now exit the mysql shell using exit and go to that directory. From that directory, make sure all the databases have their own directories in there (with their exact name) and that all the database directories are empty inside.
• Then you can go back to the mysql shell and delete them. (Or also using a server-level control panel like WHM phpMyAdmin to do so.

Just doesn’t work

Sometimes, nothing works or shit is so corrupted that you’re all out of ideas.

In moments like this, I’ll try these random options:

• All-in-One Migration – export and reimport.
• WP Migrate DB – export and reimport. Or get the PRO version and push directly.
• SSH – use the command line (and MySQL shell) to import. Navigate to the directory with the SQL file and use a command like mysqldump -u dbuser -p dbname > dbfile.sql
• WP-CLI – use this to import your database.
• Hire another dev to run manual SQL fixes for me. Cuz who got time for that?!!

Any other issues I missed? Leave it in the comments and I’ll add them to my list!