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.
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.
Ways to fix:
- Go to PHP settings (in your server settings or php.ini) and increase
- 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.
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
- Check your prefix in phpMyAdmin. The default table prefix is
wp_which gives table names like
- If your table prefix is something else (say
ddb12_), then you need to go to your
wp-config.phpfile and edit the line that says
$table_prefix = 'wp_';.
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!