How do I re-initialize MySQL on WampServer to allow for lower_case_table_names = 2

Question

I used Duplicator Pro to move my live site to a virtual host on WampServer on my Windows 11 Pro desktop. Everything works fine except for two recurring errors in the PHP_Error.log file:

[19-Feb-2023 16:15:21 UTC] WordPress database error Table ‘_prefix_e_events’ already exists for query CREATE TABLE _PREFIX_e_events ...

and

[19-Feb-2023 16:15:21 UTC] WordPress database error Duplicate key name ‘created_at_index’ for query ALTER TABLE _PREFIX_e_events ADD INDEX created_at_index (created_at) ...

Note that I changed the real prefix to "prefix".

I understand the error is caused by mixed case PREFIX/prefix in the commands. I added "lower_case_table_names = 2" to my.ini file, but then WampServer won't start with this error:

2023-02-22T15:59:25.510378Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('2') and data dictionary ('1').

I believe I need to re-initialize the server but I don't know how to do that. This post gives clear instructions how to do this in Linux.

Is re-initializing the server the correct solution? If so, does anyone know how to do this on Windows 11 with WampServer 3.3.1? If not, what are my next steps?

Other things I have tried, with no luck:

Installed the latest release of MySQL 8.0.32, edited my.ini file to add lower_case_table_names = 2, then launched WampServer and switched to the new version of MySQL. This generated the same error with mismatched server/data dictionary.

Launched phpMyAdmin and tried to change the lower_case_table_names setting from 1 to 2, but got an error that this variable is read only.

Changed the prefix to lower case in wp-config.php. This eliminated the errors but only worked until I logged out. I was unable to login had to restore the prefix to uppercase in wp-config.php and then logged back into WordPress and have the site function again.

Answer 1

The reason you were unable to login after changing the prefix is that user roles are cached in the options table.

Based on your errors above, it looks like you changed $table_prefix from _PREFIX_ to _prefix_. So, this is what I have used for the below UPDATE statements.

As well as changing $table_prefix, you need to update some values in the database:

UPDATE _prefix_usermeta
SET meta_key = REPLACE(meta_key, '_PREFIX_', '_prefix_')
WHERE BINARY meta_key LIKE '%\_PREFIX\_%';

UPDATE _prefix_options
SET option_name = REPLACE(option_name, '_PREFIX_', '_prefix_')
WHERE BINARY option_name LIKE '%\_PREFIX\_%';

It's possible that your events plugin is caching the old prefix in some other way. What plugin are you using?

---

Re-initialize MySQL on Windows

The following instructions are based on the following:

• MySQL 8.0.32
• MySQL windows service named MySQL80
• Path to mysqld.exe: C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe
• Path to my.ini: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
• Path to datadir: C:\ProgramData\MySQL\MySQL Server 8.0\Data

1. Open cmd.exe as administrator
2. Stop MySQL service

   net stop MySQL80
   

3. Find and change, or add, "lower_case_table_names=2" in the [mysqld] section of your my.ini
4. Move the current data directory

   move "C:\ProgramData\MySQL\MySQL Server 8.0\Data" "C:\ProgramData\MySQL\MySQL Server 8.0\Backup"
   

5. Create new data directory

   mkdir "C:\ProgramData\MySQL\MySQL Server 8.0\Data"
   

6. Initialize MySQL

   "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --initialize --lower_case_table_names=2 --console
   

7. Make a note of the new temporary password generated by initialize
8. Start MySQL service

   net start MySQL80
   

9. Login to mysql using the new password (from step 7)

   mysql -uroot -p
   

10. Set new password

    mysql> ALTER USER USER() IDENTIFIED BY 'your_new_root_password'
    

    or, if you want to have blank password:

    mysql> SET PASSWORD FOR root@localhost = '';