Solving Interconnectit Search Replace DB Script Error While Running AJAX Request

I have used Interconnectit Search Replace DB script since 2013 to change urls as part of the process of migrating live WordPress site to localhost and vice versa. Only once I ran into this error. Here are what happen, how to investigate the root of the problem, and how to solve it.

This issue happened in XAMPP for Windows OS. If you are not using XAMPP or Windows, you can still read on and adapt it to your case.

Failed the Safe Test

After setting up everything from the texts to replace, database details, and tables, I clicked the 'Do a safe test run' button. The test was running for few milliseconds, then a message popped up on the screen:

The script encountered an error while running an AJAX request.
If you are using your hosts file to map a domain try browsing via the IP address directly.
If you are still running into problems we recommend trying the CLI script bundled with this package.
See the README for details.

The result of the test showed only 3 tables. It means the test stopped midway.

Diagnose the Problem

The live site I was moving to localhost have a lot of security and cache plugins. I suspected those plugins caused the error. However, I have deactivated the plugins manually. I have also excluded the tables those plugins created from the exported database. So what caused the error?

Using The Bundled CLI Script

To know the exact table that have caused the error, I use the CLI script that is available in the Search Replace DB script's package.

Here are the steps to do so:

1. Open the command prompt with administrative privileges by right-clicking the icon and select 'Run as administrator'.
2. Navigate to the Search Replace DB script's folder.
   • When you just open the command prompt, usually you will be in system32 folder. Assuming your XAMPP folder is in C: drive, you can follow these steps to navigate to the script's folder:
     • Type cd \ then press enter.
     • Type cd xampp\htdocs\YOUR_SITE_FOLDER_NAME\THE_SCRIPT_FOLDER_NAME then press enter.
3. Run the CLI script by typing:
   • php C:\xampp\htdocs\YOUR_SITE_FOLDER_NAME\THE_SCRIPT_FOLDER_NAME\srdb.cli.php -h localhost -u root -n YOUR_DATABASE_USERNAME -s "TEXT_TO_BE_REPLACED" -r "TEXT_THAT_WILL_REPLACE" then press enter.

The process of searching and replacing ran for awhile, then it displayed the error message:

wp_options: replacing TEXT_TO_BE_REPLACED with TEXT_THAT_WILL_REPLACE
syntax error, unexpected '\' <T_NS_SEPARATOR>, expecting '{'

With that error message, we can know that there is syntax error in wp_options table.

Solve the Problem

Now we know which table the error is in, but we don't know yet which row in the table it is in.

The wp_options table is WordPress' default table, so we can't just delete the whole table. We need to locate the offending row and delete that specific row.

In my case, the offending row is _transient_amp_remote_request_101623f47561580a914e5d56e153cf6c.

Notice that it is a transient record. Transients are temporary information that are stored in the WordPress database. It is safe to delete all transients. You can delete it manually from phpMyAdmin or by using a plugin.

Tips: try to delete all transients records in wp_options to see if it solves the issue or not. If not, do the steps below.

This is how I did it:

1. Export only the wp_options table.
2. Open the .sql file in a code editor. I use Notepad++.
3. Search for the offending character. Tips:
   • In my case, the offending character is \ which will return a lot of results. I minimized it by replacing \n, \', and \" with anything else so to isolate the standalone \ sign.
   • To speed up the process, if you use Notepad++, you can click 'Find All in Current Document' in the 'Find' dialog window. It will instantly show all the rows that have that character.
4. Now the process will be a trial and error. Make sure you make a backup for the wp_options table.
5. Try to remove the row(s) that show up in the search result in step 3 one at a time.
6. Run the CLI script to see if the removed row is the one that caused the error. If you still get the error, repeat step 5 and 6 until the error gone.
7. Once you find the offending row, note the name.
8. Restore the wp_options table from backup and then just remove the offending row that you have noted.

Other Possible Causes

The cause of the error can be different in different setup. If my solution above doesn't work for you, check this forum page for other possible causes.