WP database error Unknown column ‘wp_’ in ‘field list’ for query SELECT wp_

Question

Please do not close this question, it's not a duplicate and the suggested link explaining how to use quotes on MySQL does nothing for me since this problem is inside a WP script. Thanks

I’m trying to install WP 5.4.1 on a clean Windows 2019 Server virtual machine.

I didn’t use Microsoft Web Platform Installer since it download old versions of WP / MySQL and PHP, so I’m trying to install from scratch.

Here are the steps I’ve taken:

• Downloaded and Installed latest PHP 7.4.5, non thread safe version and installed it
• renamed php.ini-production to php.ini

• Edited php.ini to:

  • upload_max_filesize = 64M
  • post_max_size = 64M
  • max_execution_time = 300
  • extesion=mysqli (removed the semicolon here since PHP needs this driver to connect to MySQL8)

• In IIS I created the *.php mapping and added default.php and index.php

• Created the dummy phpinfo.php file and tested on IIS, all working correctly and phpinfo() shows data

• Downloaded MySQL 8.0.20 and installed with the following options:

  • Server only
  • Standalone
  • Server computer
  • For the authentication method I choose ‘Use Stron password encryption’ as suggested by the PHP installer

• added MySQL BIN folder to path environment variable

• opened %PROGRAMDATA%\MySQL\MySQL Server 8.0\my.ini and disabled sha2 and enable native_password:

  • ;default_authentication_plugin=caching_sha2_password
  • default_authentication_plugin=mysql_native_password

• created a new database for WP, called wp1 (in MySQL 8, the ‘one liner’ to create the user and grant access at the same time doesn’t work, so we need to do it in 2 lines)

  mysql -u root -p
  create database wp1;
  CREATE USER ‘wp1’@’%’ IDENTIFIED BY ‘password’;
  GRANT ALL PRIVILEGES ON wp1.* TO ‘wp1’@’%’;
  FLUSH PRIVILEGES;
  EXIT

• created the folder C:\intepub\wwroot\wp1 and give full access to IUSR and Users windows groups so WP can write the config files

• In IIS, right click on the Default Web Site and click on Add Application pointing to the new wp1 folder

• navigate to localhost/wp1 to start WP installation

So after selecting the language and entering the DB info, I get this error

WordPress database error Unknown column ‘wp_’ in ‘field list’ for query SELECT wp_

After hours fighting with this, here’s what I found:

• the error came from the DB, not WP.
• tried with both wp1 and root users during the installation
• tried with both localhost and 127.0.0.1 during the installation
• the error generates in setup-config.php file, line 315 $wpdb->query("SELECT $prefix")
• seems the problem is that what arrives at MySQL is the string select wp_ insted of select 'wp_' (note the missing quotes)
• If I go to MySQL and execute select wp_ I get the exact same error

So the issue seems to be related to how MySQL 8.0 is handling quotes in the query it receives from WP installer…

I restored a snapshot just before installing MySQL 8.0.20 and this time, instead of Use Strong Password Encryption for Authentication I selected Use Legacy Authentication Method (Retain MySQL 5.x Compatibility) but the error is still the same

Before answering, please consider:

• I’m looking to solve the issue, not to start a discussion whatever I should use MySQL 5.x instead of MySQL 8.x
• Yes, the connection to the database is Ok, that’s not the problem, please read all the my post
• Yes, wp-config is being written with the correct values

Even tough I saw many messages with this same error on the WP forum, they normally have no answer, or the answer don’t make any sense (like asking to OP to check the DB credentials and write access to the WP folder), still I posted on the WP forum, but I no answer yet.

Sorry for my poor formatting!

Can anyone please help?

Answer 1

Finally found the issue...

TLDR; edit your php.ini and make sure you have both:

display_errors = On

error_log = php_errors.log

Setting error_log solves the issue... I guess that when error_log has no value (default configuration), PHP decides to send the error back to the calling program, resulting in the error message column 'wp_' in 'field list' during the WP installation.

More details here