[ERROR] [MY-011087] [Server] Different lower_case_table_names ON PERCONA CLUSTER - Oracle Linux 8

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi.
Already set up a 3 nodes Percona XtraDB Cluster successfully. But developers can’t query/execute objects due to an case sensitive problem.

Trying to change the parameter to modify case sensitive from lower_case_table_names=0 to lower_case_table_names=1 but the instance can’t start and log the next error.

2023-12-22T22:09:04.281621Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2023-12-22T22:09:04.282150Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-12-22T22:09:04.282234Z 0 [ERROR] [MY-010119] [Server] Aborting

I’m asking for the procedure to reinitialize data directory of a MySQL Percona XtraDB Cluster v8 on Oracle Linux 8. I’d try to reinitialize data directory only in one node, but didn’t work. I had to rollback.

2

Hello @sariurgo15,
I would try using lower_case_table_names=2 which works like =0 but comparisons are evaluated as lowercase. This might work. If it doesn’t then you will have to erase the datadir, init a new mysql with correct parameters and then perform a logical dump/restore.

3

Thank you @matthewb. I did try the value 2 as you suggest. Effectively the instance starts, but the behavior is not what we need.

4

Use this behavior to start MySQL, then use mydumper to make a logical dump of your data. Init a brand new mysql with the value of lower case that you need and then import the dump. (ie: you can’t fix this with a simple config change; you will have to reload your dataset)

5

Thanks @matthewb. Already solved problem.

6

I post my solution for a 3 nodes Percona Cluster.
This is what worked for me.

First of all, make a full mysqldump of your databases (just in case).

NODE 1

$ systemctl stop mysql
$ cd /var/lib
$ cp -rp mysql /backupdir   # backup
$ rm -rf mysql
$ mkdir mysql
$ chown mysql:mysql mysql
$ chmod 750 mysql

OPTIONAL: Trunc logs

`$ :> /var/log/mysqld.log`

$ vim /etc/my.cnf
  --> lower_case_table_names=1

Initialize data directory with additional parameters:

$ mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --console
$ ls -l /var/lib/mysql
$ more /var/log/mysqld.log

Start mysql services in bootstrap mode

$ systemctl start mysql@bootstrap.service


$ mysql -uroot --skip-password
  --> reset root password

mysql> alter user 'root'@'localhost' identified by 'xxxxxxxxxx';
mysql> flush privileges;
mysql> exit;

NODE 2

$ systemctl stop mysql
$ cd /var/lib
$ cp -rp mysql /backupdir   # backup
$ rm -rf mysql
$ mkdir mysql
$ chown mysql:mysql mysql
$ chmod 750 mysql

OPTIONAL: Trunc logs

$ :> /var/log/mysqld.log

$ vim /etc/my.cnf
  --> lower_case_table_names=1

Startup mysql service (and initialize directory for this node):

$ systemctl start mysql
$ ls -l /var/lib/mysql
$ more /var/log/mysqld.log

NODE 3

$ systemctl stop mysql
$ cd /var/lib
$ cp -rp mysql /backupdir   # backup
$ rm -rf mysql
$ mkdir mysql
$ chown mysql:mysql mysql
$ chmod 750 mysql

OPTIONAL: Trunc logs

$ :> /var/log/mysqld.log

$ vim /etc/my.cnf
  --> lower_case_table_names=1

Startup mysql service (and initialize directory for this node):

$ systemctl start mysql
$ ls -l /var/lib/mysql
$ more /var/log/mysqld.log

NODO 1

Restart services in normal mode (from bootstrap mode)

$ systemctl stop mysql@bootstrap.service
$ systemctl start mysql

REFERENCES:

https://dev.mysql.com/doc/mysql-installation-excerpt/8.3/en/data-directory-initialization.html

https://dev.mysql.com/doc/mysql-installation-excerpt/8.3/en/data-directory-initialization.html$data-directory-initialization-procedure

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html$sysvar_lower_case_table_names