How to Reinitialize MySQL after changing lower_case_table_names = 2
Posted by: DarrylB (---.abhsia.telus.net)
Date: February 22, 2023 07:28PM

On my local site running on WampServer 3.3.0 on fully updated Windows 11 Pro (see all specs in signature), I get the following two errors in my PHP_Error.log file with every click of the mouse whether I’m working in wp-admin or navigating the site while not logged in:

[19-Feb-2023 16:15:21 UTC] WordPress database error Table ‘_prefix_e_events’ already exists for query CREATE TABLE _PREFIX_e_events (
id bigint(20) unsigned auto_increment primary key,
event_data text null,
created_at datetime not null
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; made by require(‘wp-blog-header.php’), require_once(‘wp-includes/template-loader.php’), do_action(‘template_redirect’), WP_Hook->do_action, WP_Hook->apply_filters, Elementor\Frontend->init, Elementor\Plugin->init_common, Elementor\Core\Common\App->init_components, Elementor\Core\Common\Modules\EventTracker\Module->__construct, Elementor\Core\Common\Modules\EventTracker\DB->__construct, Elementor\Core\Common\Modules\EventTracker\DB->create_table

[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)
made by require(‘wp-blog-header.php’), require_once(‘wp-includes/template-loader.php’), do_action(‘template_redirect’), WP_Hook->do_action, WP_Hook->apply_filters, Elementor\Frontend->init, Elementor\Plugin->init_common, Elementor\Core\Common\App->init_components, Elementor\Core\Common\Modules\EventTracker\Module->__construct, Elementor\Core\Common\Modules\EventTracker\DB->__construct, Elementor\Core\Common\Modules\EventTracker\DB->add_indexes

This site was created using an export/import using Duplicator Pro from my live site hosted at Bluehost. I don’t not get these errors on my live site. On my local site, I quickly get thousands of errors in my PHP_Error.log file. So many that it makes it hard to find if there are any other errors. To be clear, the site works fine, is quick and responsive, but there are too many errors to allow for effective troubleshooting.

I beleive the errors are caused by my table prefix being uppercase from my live (Linux) site, while my local site (Windows) is case insensitive. The case of the table prefix in the commands does not match the case of the table name, so an error is generated. Note, for security reasons, I have changed the actual prefix to the word "prefix" and have displayed in upper or lower case as it is shown in the log file.

To remedy this, I've inserted the line: "lower_case_table_names = 2" into MySQL my.ini file.

When I restart the services I get an orange icon, the service 'wampmysqld64' will not start, and the following errors appears in mysql.log:

2023-02-22T15:59:25.510378Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('2') and data dictionary ('1').
2023-02-22T15:59:25.511467Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-02-22T15:59:25.511938Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-02-22T15:59:25.699727Z 0 [System] [MY-010910] [Server] c:\wamp64\bin\mysql\mysql8.0.31\bin\mysqld.exe: Shutdown complete (mysqld 8.0.31)

Removing that line allows the services to start and the site works fine.

After some additional research, it appears that I need to re-initialize MySQL. Is this correct? If so, how do I do that?

Thanks.

Windows 11 Pro OS Build: 22621.1265
WampServer version: 3.3.0 64-bit
Apache version: 2.4.54.2
PHP version: 8.2.0
MySQL version: 8.0.31
MariaDB version: 10.10.2
Installation: C:\Wamp64

Options: ReplyQuote
Re: How to Reinitialize MySQL after changing lower_case_table_names = 2
Posted by: DarrylB (---.abhsia.telus.net)
Date: February 22, 2023 10:25PM

EDIT: Problem is NOT solved. The error messages disappeared but once I logged out, I could not log back in until I reversed the changes below.

So, back to my original question: Do I need to re-initialize MySQL. Is this correct? If so, how do I do that?


----------


Problem solved.

Rather than changing my.ini to include lower_case_table_names = 2, I left it untouched.

Instead, I shutdown WampServer, edited my wp-config.php file, and changed the $table_prefix entry from uppercase to lower case. From this:

$table_prefix = '_PREFIX_';

to this:

$table_prefix = '_prefix_';

I restarted WampServer and all is well. No more error messages. I just need to manage this setting when I do an export back to my live site.

Windows 11 Pro OS Build: 22621.1265
WampServer version: 3.3.0 64-bit
Apache version: 2.4.54.2
PHP version: 8.2.0
MySQL version: 8.0.31
MariaDB version: 10.10.2
Installation: C:\Wamp64



Edited 1 time(s). Last edit at 02/22/2023 11:52PM by DarrylB.

Options: ReplyQuote


Sorry, only registered users may post in this forum.