WP-CLI's `search-replace` command doesn't work for `wp_options` table

[m8ker]

I'm using Tangible as integrated into the LiveCanvas plugin. When I have the LiveCanvas plugin enabled, WP-cli's "search-replace" function does not update the URLs in the wp_options table and potentially, other tables.

We tracked the issue to Tangible. In the LiveCanvas plugin, if we disable tangible, WP search-replace works correctly.

I worked with Jeff at LiveCanvas first, but he said the issue isn't with the LC implementation, but with Tangible itself. He advised me to open up an issue with Tangible.

Edit -- more context: taking Live Canvas out of the equation by disabling the plugin and enabling the Loops and Logic plugin also caused the same issue.

[jeff-at-livecanvas]

Hello M8ker,
I'd add from our Discord discussion, that also enabling just the Loops and Logic plugin alone would cause the issue - so this is why I stated that it shouldnt be LC-dependent

[eliot-akira]

WP-cli's "search-replace" function does not update the URLs in the wp_options table

Thanks for the issue report. We're looking into solving it, and will let you know of any progress.

[eliot-akira]

OK, I'm not seeing the issue on my local test site. WP-CLI's search-replace command works as expected, with the newest version of the Template System.

It may be specific to your site or server setup. Or possibly a conflict with another plugin installed.

It might help if you could post the result of running wp --info.

---

In the LiveCanvas plugin, if we disable tangible, WP search-replace works correctly.

Could you try the following?

• Deactivate LiveCanvas

• Install & activate the Template System by itself

  The newest version can be downloaded here: https://github.com/TangibleInc/template-system/releases/download/latest/tangible-template-system-latest.zip

• Run WP-CLI's search-replace command

If the command works, it means the issue is related to the LiveCanvas' integration of the template system.

If the command doesn't work - is there any error, or just silently doesn't replace anything?

To eliminate any other factors, would you mind trying it on an empty site with only the Template System installed?

---

Edit: Oh I didn't see this part:

Edit -- more context: taking Live Canvas out of the equation by disabling the plugin and enabling the Loops and Logic plugin also caused the same issue.

Hmm, without an error message or a way to reproduce the error on my side, it's difficult to investigate what's going wrong.

Could you try installing the Template System alongside L&L to see if anything changes? Also, the output of wp --info may help.

[m8ker]

@eliot-akira I created a new instance of WP in Local by Flywheel, and installed just the Template System plugin. I tested the fresh installation, and I got the same issue: wp_options didn't update with the other tables when just Template System was installed. Here is the output of wp search-replace, first without any plugins installed (9 replacements), then with Template System installed (7 replacements):

Success: Made 9 replacements. myname@My-MacBook-Pro public % wp search-replace https://lightgray-baboon-201857.hostingersite.com http://test.local +------------------+-----------------------+--------------+------+ | Table | Column | Replacements | Type | +------------------+-----------------------+--------------+------+ | wp_commentmeta | meta_key | 0 | SQL | | wp_commentmeta | meta_value | 0 | SQL | | wp_comments | comment_author | 0 | SQL | | wp_comments | comment_author_email | 0 | SQL | | wp_comments | comment_author_url | 0 | SQL | | wp_comments | comment_author_IP | 0 | SQL | | wp_comments | comment_content | 0 | SQL | | wp_comments | comment_approved | 0 | SQL | | wp_comments | comment_agent | 0 | SQL | | wp_comments | comment_type | 0 | SQL | | wp_links | link_url | 0 | SQL | | wp_links | link_name | 0 | SQL | | wp_links | link_image | 0 | SQL | | wp_links | link_target | 0 | SQL | | wp_links | link_description | 0 | SQL | | wp_links | link_visible | 0 | SQL | | wp_links | link_rel | 0 | SQL | | wp_links | link_notes | 0 | SQL | | wp_links | link_rss | 0 | SQL | | wp_options | option_name | 0 | SQL | | wp_options | option_value | 2 | PHP | | wp_options | autoload | 0 | SQL | | wp_postmeta | meta_key | 0 | SQL | | wp_postmeta | meta_value | 0 | SQL | | wp_posts | post_content | 2 | SQL | | wp_posts | post_title | 0 | SQL | | wp_posts | post_excerpt | 0 | SQL | | wp_posts | post_status | 0 | SQL | | wp_posts | comment_status | 0 | SQL | | wp_posts | ping_status | 0 | SQL | | wp_posts | post_password | 0 | SQL | | wp_posts | post_name | 0 | SQL | | wp_posts | to_ping | 0 | SQL | | wp_posts | pinged | 0 | SQL | | wp_posts | post_content_filtered | 0 | SQL | | wp_posts | guid | 4 | SQL | | wp_posts | post_type | 0 | SQL | | wp_posts | post_mime_type | 0 | SQL | | wp_term_taxonomy | taxonomy | 0 | SQL | | wp_term_taxonomy | description | 0 | SQL | | wp_termmeta | meta_key | 0 | SQL | | wp_termmeta | meta_value | 0 | SQL | | wp_terms | name | 0 | SQL | | wp_terms | slug | 0 | SQL | | wp_usermeta | meta_key | 0 | SQL | | wp_usermeta | meta_value | 0 | PHP | | wp_users | user_login | 0 | SQL | | wp_users | user_nicename | 0 | SQL | | wp_users | user_email | 0 | SQL | | wp_users | user_url | 1 | SQL | | wp_users | user_activation_key | 0 | SQL | | wp_users | display_name | 0 | SQL | +------------------+-----------------------+--------------+------+ Success: Made 9 replacements. myname@My-MacBook-Pro public % wp search-replace http://test.local https://lightgray-baboon-201857.hostingersite.com +------------------+-----------------------+--------------+------+ | Table | Column | Replacements | Type | +------------------+-----------------------+--------------+------+ | wp_commentmeta | meta_key | 0 | SQL | | wp_commentmeta | meta_value | 0 | SQL | | wp_comments | comment_author | 0 | SQL | | wp_comments | comment_author_email | 0 | SQL | | wp_comments | comment_author_url | 0 | SQL | | wp_comments | comment_author_IP | 0 | SQL | | wp_comments | comment_content | 0 | SQL | | wp_comments | comment_approved | 0 | SQL | | wp_comments | comment_agent | 0 | SQL | | wp_comments | comment_type | 0 | SQL | | wp_links | link_url | 0 | SQL | | wp_links | link_name | 0 | SQL | | wp_links | link_image | 0 | SQL | | wp_links | link_target | 0 | SQL | | wp_links | link_description | 0 | SQL | | wp_links | link_visible | 0 | SQL | | wp_links | link_rel | 0 | SQL | | wp_links | link_notes | 0 | SQL | | wp_links | link_rss | 0 | SQL | | wp_options | option_name | 0 | SQL | | wp_options | option_value | 0 | PHP | | wp_options | autoload | 0 | SQL | | wp_postmeta | meta_key | 0 | SQL | | wp_postmeta | meta_value | 0 | SQL | | wp_posts | post_content | 2 | SQL | | wp_posts | post_title | 0 | SQL | | wp_posts | post_excerpt | 0 | SQL | | wp_posts | post_status | 0 | SQL | | wp_posts | comment_status | 0 | SQL | | wp_posts | ping_status | 0 | SQL | | wp_posts | post_password | 0 | SQL | | wp_posts | post_name | 0 | SQL | | wp_posts | to_ping | 0 | SQL | | wp_posts | pinged | 0 | SQL | | wp_posts | post_content_filtered | 0 | SQL | | wp_posts | guid | 4 | SQL | | wp_posts | post_type | 0 | SQL | | wp_posts | post_mime_type | 0 | SQL | | wp_term_taxonomy | taxonomy | 0 | SQL | | wp_term_taxonomy | description | 0 | SQL | | wp_termmeta | meta_key | 0 | SQL | | wp_termmeta | meta_value | 0 | SQL | | wp_terms | name | 0 | SQL | | wp_terms | slug | 0 | SQL | | wp_usermeta | meta_key | 0 | SQL | | wp_usermeta | meta_value | 0 | PHP | | wp_users | user_login | 0 | SQL | | wp_users | user_nicename | 0 | SQL | | wp_users | user_email | 0 | SQL | | wp_users | user_url | 1 | SQL | | wp_users | user_activation_key | 0 | SQL | | wp_users | display_name | 0 | SQL | +------------------+-----------------------+--------------+------+ Success: Made 7 replacements. myname@My-MacBook-Pro public %

There are no errors displayed. Just differing numbers of replacements, showing wp_options doesn't get url strings replaced. Here is the output of wp --info:

OS: Darwin 24.1.0 Darwin Kernel Version 24.1.0: Thu Nov 14 18:19:02 PST 2024; root:xnu-11215.41.3~13/RELEASE_ARM64_T8132 arm64 Shell: /bin/zsh PHP binary: /Users/myname/Library/Application Support/Local/lightning-services/php-8.2.23+0/bin/darwin-arm64/bin/php PHP version: 8.2.23 php.ini used: /Users/myname/Library/Application Support/Local/run/s19iPUtvq/conf/php/php.ini MySQL binary: /Users/myname/Library/Application Support/Local/lightning-services/mysql-8.0.16+6/bin/darwin/bin/mysql MySQL version: mysql Ver 8.0.16 for macos10.14 on x86_64 (MySQL Community Server - GPL) SQL modes: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION WP-CLI root dir: phar://wp-cli.phar/vendor/wp-cli/wp-cli WP-CLI vendor dir: phar://wp-cli.phar/vendor WP_CLI phar path: /Users/myname/Local Sites/soundview-health-associates/app/public WP-CLI packages dir: WP-CLI cache dir: /Users/myname/.wp-cli/cache WP-CLI global config: /Applications/Local.app/Contents/Resources/extraResources/bin/wp-cli/config.yaml WP-CLI project config: WP-CLI version: 2.10.0

One other interesting detail is that this happens on both my Mac and my PC, which seems to rule out issues with my environment.

[eliot-akira]

This information is helpful for investigating further, thank you. I reformatted the WP-CLI command output, and filtered out the non-affected tables.

Without Template System:

+------------------+-----------------------+--------------+------+
| Table            | Column                | Replacements | Type |
+------------------+-----------------------+--------------+------+
| wp_options       | option_value          | 2            | PHP  | 
| wp_posts         | post_content          | 2            | SQL  | 
| wp_posts         | guid                  | 4            | SQL  | 
| wp_users         | user_url              | 1            | SQL  | 
+------------------+-----------------------+--------------+------+ 
Success: Made 9 replacements. 

With Template System:

+------------------+-----------------------+--------------+------+
| Table            | Column                | Replacements | Type |
+------------------+-----------------------+--------------+------+
| wp_posts         | post_content          | 2            | SQL  | 
| wp_posts         | guid                  | 4            | SQL  | 
| wp_users         | user_url              | 1            | SQL  | 
+------------------+-----------------------+--------------+------+ 
Success: Made 7 replacements. 

How strange! So the search & replace is working except for wp_options table specifically.

Notably, for wp_options the Type is PHP, whereas all other tables are of type SQL. I think this is a clue, it seems WP-CLI is not getting values from the wp_options table directly with SQL, but using a PHP or WordPress function. I'm guessing the Template System is affecting it somehow, possibly a filter (though I don't recall seeing or implementing such a filter).

---

I looked into the source code of WP-CLI's search & replace command, and found where it's differentiating PHP and SQL value types.

• https://github.com/wp-cli/search-replace-command/blob/892570e343b8949e0643e6b12cb20dd92816852e/src/Search_Replace_Command.php#L399-L405

Comparing the functions sql_handle_col() and php_handle_col(), I see that the SQL type uses SQL to directly search & replace, and PHP type uses an internal class SearchReplacer. The latter uses normal PHP functions, not any defined by WordPress, and there are no filters being applied.

..OK, getting closer to the cause of the issue.

[eliot-akira]

I confirmed on my local machine as well as on a remote server, both running Linux, that WP-CLI search-replace is getting applied correctly to the wp_options table. For example:

+------------------+-----------------------+--------------+------+
| Table            | Column                | Replacements | Type |
+------------------+-----------------------+--------------+------+
| wp_comments      | comment_content       | 4            | SQL  |
| wp_options       | option_value          | 5            | PHP  |
| wp_postmeta      | meta_value            | 1            | PHP  |
| wp_posts         | post_content          | 3            | PHP  |
| wp_posts         | guid                  | 199          | SQL  |
+------------------+-----------------------+--------------+------+
Success: 212 replacements to be made.

I notice that wp_postmeta is also PHP type. I wonder, on your side, if any post meta field value is affected by the issue.

---

Another difference I see from wp --info is that I'm running MariaDB:

MySQL version:  mysql  Ver 15.1 Distrib 10.6.14-MariaDB, for Linux (x86_64) using readline 5.1
SQL modes:

Compared to your setup:

MySQL version:	mysql  Ver 8.0.16 for macos10.14 on x86_64 (MySQL Community Server - GPL)
SQL modes: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION WP-CLI

It's hard to imagine how the Template System could possibly affect MySQL and not MariaDB, as the latter is a drop-in replacement by the same lead developer - but I'll try to prepare a test environment with Docker and check.

[eliot-akira]

Maybe this would help: could you export the database from the empty site with the issue?

wp db export db.sql

It can be attached to a comment by drag & drop. Then I can import it into an empty site on my side, and see if the issue appears.

---

Another angle: use WP-CLI's --log option to create a log file.

wp search-replace --log=log.txt http://test.local http://test.local2

The resulting log.txt will include changed items, which might offer a clue.

---

I also see there's a --precise option, which will:

Force the use of PHP (instead of SQL) which is more thorough, but slower.

..But since the issue is happening with PHP type, this would probably make it worse.

---

I'm also looking through the search-replace command's issue tracker, to see if there are any related issues reported.

It does kinda feel like it's a bug on their side, but even in that case, I'd like to find out how the Template System could be triggering it.

[m8ker]

@eliot-akira confirming when I use --log option, the same thing is happening:

With Tangible
+----------+--------------+--------------+------+
| Table    | Column       | Replacements | Type |
+----------+--------------+--------------+------+
| wp_posts | post_content | 2            | SQL  |
| wp_posts | guid         | 5            | SQL  |
| wp_users | user_url     | 1            | SQL  |
+----------+--------------+--------------+------+

Without Tangible 
+------------+--------------+--------------+------+
| Table      | Column       | Replacements | Type |
+------------+--------------+--------------+------+
| wp_options | option_value | 2            | PHP  |
| wp_posts   | post_content | 2            | SQL  |
| wp_posts   | guid         | 5            | SQL  |
| wp_users   | user_url     | 1            | SQL  |
+------------+--------------+--------------+------+

I attached the sql file, but I had to change the extension to .txt to get it to upload, so you'd need to change the ext back to .sql.
local.txt

[eliot-akira]

OK, I was able to:

• Prepare a Docker Compose setup running WordPress with MySQL 8
• Install and activate the newest Template System (by the way, Tangible is the name of the company - the product is Loops & Logic, whose core module is this Template System)
• Import the attached database export
• Run WP-CLI search & replace on values in the wp_options table

It runs successfully with no issue.

$ docker compose run --rm cli wp search-replace --log=log.txt http://test.local http://test2.local

+------------+--------------+--------------+------+
| Table      | Column       | Replacements | Type |
+------------+--------------+--------------+------+
| wp_options | option_value | 2            | PHP  |
| wp_posts   | post_content | 2            | SQL  |
| wp_posts   | guid         | 5            | SQL  |
| wp_users   | user_url     | 1            | SQL  |
+------------+--------------+--------------+------+

Success: Made 10 replacements.

---

So, apparently it's not about MySQL or MariaDB. It also doesn't seem specific to the values in the database. Maybe the "SQL modes"..? The only other major difference, as far as I can tell, is the operating system - but somehow I doubt that could be influencing the result.