Unable to Import Wordpress Site to Local 5.10.1 - ERROR 1146 (42S02) at line 1: Table 'local.wp_options' doesn't exist

[americanninja]

Issue Summary

Unable to successfully import backup of Wordpress site to Local. I created a zip archive with 1.) a mysqldump of my website’s database (backup.sql) and 2.) a copy of the wp-contents folder. It proceeds successfully with “adding Wordpress” step, but then fails at the “Importing Database: backup.sql” step.

Here is the exact error message:

Uh-oh! Unable to import site.

Error: Command failed: /Applications/Local.app/Contents/Resources/extraResources/lightning-services/mysql-8.0.16+5/bin/darwin/bin/mysql local -B --disable-column-names -e SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’;
ERROR 1146 (42S02) at line 1: Table ‘local.wp_options’ doesn’t exist

I found two separate forum threads with this exact issue, but no resolution. Honestly, for an app that specializes in Wordpress, I have no idea why I’m failing to even get past this step. I assume this should be the easy part.

The table wp_options does exist in the database. I can see it with SequelPro on my server and in the SQL dump file.

I exported the database directly from my server using
mysqldump -uUSER -pPASSWORD databasename > backup.sql

I use VESTACP, and it formats databases with the database name as SERVERUSERNAME_DATABASENAME. All tables have been prefixed with “wp_”, as is common with most WP sites. Is the issue the database name itself? I’m wondering why the error message states “local.wp_options” for the table name. Why it’s not just wp_options.

Anyone have any idea? I’m just a bit shocked this is even an issue. I followed the process exactly, which is fairly simple. Just zip a folder with the mysqldump file and the wp-contents folder. I was expecting this process to be the easy part.

I have heard great things about this app, I would love to actually use it.

I’m using the latest version of Local on Mac (Catalina OS).

[americanninja]

Okay, so I figured it out. I’m really surprised to find out what the issue was, given how silly/stupid it is and that this tool didn’t have a way to manage it automatically. Especially since this tool is focused on Wordpress sites and making it easy. You would assume the import process would be the easy part.

Also, I saw another person post this same issue and the solution provided by someone else, although it works, it’s A LOT more effort and just a ridiculous approach as well.

So, here is the issue and the solution. In my case, I typically use a bash script to automate the backup job. In my bash script I use the following command:

mysqldump --force --opt --user=$USER -p$PASSWORD --databases $DB > DBbackup.sql

The issue is using the --databases parameter. Now, the reason I use this, is because that parameter will output two lines at the top of the backup.sql file.

1. CREATE DATABASE (if does not exist) DATABASE_NAME
2. USE DATABASE_NAME

Now, this is very useful, as I can easily just reimport that backup.sql and it will simply drop all tables and overwrite them into that database. I don’t need to do any prep, dropping tables or deleting and recreating the database, it doesn’t load into the wrong database, etc.

Very very handy.

So, after searching this forum and using google and coming up empty, I thought there must be something to do with that error code using “local.wp_options”. So I figured, maybe it’s because my database name is included in this backup.sql file. Sure enough, that was it. So I simplified my script to use only this:

mysqldump --user=$USER -p$PASSWORD $DB > DBbackup.sql

This will produce a database dump without the use and create database lines. Dropped my zip archive into local and bam, it worked.

So, anyway, there is the solution. If you found this and you were pulling out your hair, saying what the ***** is going on. You now have your solution. I hope you found this before you wasted too much time.

Now, my question to the developers of this fantastic tool. Why is there not an option to just ignore this by the import tool OR simply alert the user of this so that they can fix it. The error message is completely useless and doesn’t really help to identify the issue. Also, a simple how-to guide for new users covering this topic would also be great. I assume anyone that finds this tool, the first thing they will want to do is to import their live site. So that process should be painless and an enjoyable one. Like, wow! All I needed to do was drop a zip file with my wp-contents and mysqldump file. And I have a live working local website. This is amazing. Instead of a few hours thinking what the heck is going on!

Anyway, just giving my two cents worth and hoping this post gets indexed in google for those of you that search for this error in the future.