Wp db reset doesn't work correctly with MySQL

[slaFFik]

Bug Summary

When trying to reset a site using WP CLI I get this error:

$ wp db reset
Are you sure you want to reset the 'local' database? [y/n] y
Error: Failed to get current SQL modes. Reason: '"mysql --no-defaults --no-auto-rehash --batch --skip-column-names --execute="SELECT' is not recognized as an internal or external command,
operable program or batch file.

This is NOT related to: Wp db reset – Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) - Bugs - Local Community (localwp.com)

Steps to reproduce

Open site shell and try to reset the site.

Environment Info

Describe your environment.

• What Operating System are you using?
  Windows 10 Pro 21H2
• What versions of site software (Nginx, Apache, PHP, MySQL) is used?
  PHP 8.0, nginx, MySQL 5.7.28
• What version of Local is installed?
  6.3.1

Supporting info

Please provide your Local Log. See this Community Forum post for instructions on how to do so:

local-lightning.zip (75.8 KB)

[sternerstuff]

Also having this issue. Can’t run WP-CLI DB commands in the site shell.

Local 6.4.2+6012
Windows 11 Pro 21H2
PHP 8.0.0, MySQL 8.0.16
local-logs3.zip (18.4 KB)

$ wp db reset
Are you sure you want to reset the 'local' database? [y/n] y
Error: Failed to get current SQL modes. Reason: '"mysql --no-defaults --no-auto-rehash --batch --skip-column-names --execute="SELECT' is not recognized as an internal or external command,
operable program or batch file.

[afragen]

The command looks like it’s missing a " at the end.

[sternerstuff]

I don’t think I’m really responsible for that command, I just type wp db reset

[afragen]

Agreed. But it might point to an issue with WP-CLI. Not really sure.

[cmagill]

I’m having this issue as well. Any solution?

[cmagill]

I am on Mac OSX 12.5.1 - Local Version 6.4.3+6116

[cmagill]

This worked for me…

Unable to run some wp-cli DB commands

As per the WordPress core documentation, and since Local WP app uses MySQL sockets, consider modifying your wp-config.php to include the use of MySQL Sockets. MYSQL SOCKETS OR PIPES If your host uses Unix sockets or pipes, adjust the DB_HOST value in the wp-config.php file accordingly. define( 'DB_HOST', '127.0.0.1:/var/run/mysqld/mysqld.sock' ); // or define( 'DB_HOST', 'localhost:/var/run/mysqld/mysqld.sock' ); // or define( 'DB_HOST', 'example.tld:/var/run/mysqld/mysqld.sock' ); Replace…

[sammunoz]

Hi @cmagill -

Thank you for sharing what worked for you & welcome to the Local community!

@slaFFik - have you tried the above?

Thank you!

Sam

[sternerstuff]

@sammunoz Per the original poster, Windows is the OS in question, and Local on Windows doesn’t support/show sockets.

The original poster, and my follow up comment, clearly show a malformed MySQL CLI command - not any issues connecting to the MySQL host.

[sternerstuff]

@sammunoz Don’t know if this helps, but this is what I see when started the shell session:

Setting Local environment variables...
----
WP-CLI:
Warning: PHP Startup: Unable to load dynamic library 'php_imagick.dll' (tried: C:/Users/Ethan/AppData/Local/Programs/Local/resources/extraResources/lightning-services/php-7.4.1+18/bin/win64/ext\php_imagick.dll (The specified module could not be found.), C:/Users/Ethan/AppData/Local/Programs/Local/resources/extraResources/lightning-services/php-7.4.1+18/bin/win64/ext\php_php_imagick.dll.dll (The specified module could not be found.)) in Unknown on line 0
WP-CLI 2.7.1
Composer:
Startup: Unable

Startup: Unable
2.4.2 2022-09-14
stdout is not a tty
PHP:
MySQL:    C:\Users\Ethan\AppData\Local\Programs\Local\resources\extraResources\lightning-services\mysql-8.0.16+6\bin\win64\bin\mysql.exe  Ver 8.0.16 for Win64 on x86_64 (MySQL Community Server - GPL)
----
Launching shell: /usr/bin/bash ...

Some things I notice:

• No Composer version is output. When I run Composer commands, it uses my globally installed Composer. which php gives me /c/ProgramData/ComposerSetup/bin/composer
• No PHP version is output, but it does appear to use the PHP version set for this site. which php gives me /c/Users/Ethan/AppData/Local/Programs/Local/resources/extraResources/lightning-services/php-7.4.1+18/bin/win64/php, so that seems correct.

Don’t know if any of that’s helpful.

[isisky]

Hello,
I’m having the same issue :

wp db reset
Are you sure you want to reset the 'local' database? [y/n] y
Error: Failed to get current SQL modes. Reason: '"mysql --no-defaults --no-auto-rehash --batch --skip-column-names --execute="SELECT' is not recognized as an internal or external command,
operable program or batch file.

Environment Info

Windows 11 22H2
PHP 8.1.9, nginx, MySQL 8.0.16
Local 6.5.1+6195
local-lightning.log (1.8 KB)

[sternerstuff]

Relevant issue on GitHub w/ WP-CLI

[isisky]

Thank you, i changed to PHP 7.4 and now I’m getting this error :
Error: Failed to get current SQL modes. Reason: ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)
I tried both in local or router mode and get the same error any idea ?

[sammunoz]

Hello all,

To clarify, it sounds like a known bug with WP-CLI (thank you @sternerstuff for linking the related Issue on GitHub).

Reviewing that GitHub thread, it looks like the WP-CLI team has added a fix with their latest release, v2.7.1 - which is what we’ve included in the latest Local Beta v6.6.

Local Beta v 6.6.0 is available here: Local Beta 6.6.0 - #2

I would suggest trying out the Beta version and if that hasn’t solved the issue, perhaps follow up with the WP-CLI team.

Thank you!

Sam

[afragen]

@sammunoz It looks like 6.6.0 has been released.

[Youdaman]

I encountered the same issue today with the same error message, and I worked out that the cause was using PHP 8 – changing to PHP 7 allowed the wp db reset command to run correctly without error. Note: you also need to make sure you’re either using site shell LocalWP gives you, or that if you’re running in another terminal that you’re using the right version of PHP there (and the right port etc for the db). You can still use PHP 8 for your site if you want, just run the wp cli commands in a terminal that uses PHP 7 in that case.

Update: I was able to get wp db commands working with PHP by downloading the latest WP CLI nightly build:

curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli-nightly.phar

And then copying that to where I keep my wp:

cp wp-cli-nightly.phar ~/bin/wp

I got the hint to try the nightly build here: Use of WP_CLI\Utils\run_mysql_command() broken on Windows 10 · Issue #5521 · wp-cli/wp-cli · GitHub

[austinwendt]

Hi @youdaman - thanks for sharing! Which version of Local were you using?

The GitHub issue you linked to mentions that this was an issue pre-WP-CLI 2.7.1 and has been fixed since (hence, why the nightly build is working). If you’re on the latest version of Local, WP-CLI 2.7.1 should be the default. I just want to make sure we didn’t miss something!

Also, if you want to try that update in the future, it should be easier to open the site shell and run wp cli update --nightly and it’ll do the heavy lifting for you!

[Youdaman]

Hi @austinwendt – I think the issue I might have been seeing is that my global version of WP-CLI was a previous version, and instead of using Local’s site shell, I was opening a terminal directly in the site root.

I have wp-cli.local.php/yml config files setup to point at the right host:ip etc so db subcommands work, but I now realise that if things work already with version 2.7.1 via Local’s shell, and my global version is at WP-CLI 2.8.0-alpha-a5121e9 now, then I must have been using a previous version globally in a non-Local terminal.

Thanks for getting back to me Austin and apologies for any concern I may have caused!

[austinwendt]

No worries at all, I’m glad it is working for you!