Cant restart MySQL 8.0.35 after change lower_case_table_names = 0

rudysi

Hello,

I just realized all my table names converted to lowercase, so I change the mysql configuration (lower_case_table_names = 0).

But MySQL can't start anymore.

please help.

Regards,
Rudy

aaPanel_Kern

Hello, I suggest you check the running error log:
cat /www/server/data/*.err|grep -i -C 10 error

rudysi

DimmmCom

rudysi lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

rudysi

aaPanel_Kern

This is what I got:

2024-12-28T00:23:31.414665Z 0 [Warning] [MY-000081] [Server] option 'max_allowed 2024-12-28T00:23:31.414712Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is 2024-12-28T00:23:31.414764Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO 2024-12-28T00:23:31.414831Z 0 [Warning] [MY-010918] [Server] 'default_authentica 2024-12-28T00:23:31.414848Z 0 [System] [MY-010116] [Server] /www/server/mysql/bi 2024-12-28T00:23:31.675725Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configur 2024-12-28T00:23:31.677403Z 1 [System] [MY-013576] [InnoDB] InnoDB initializatio 2024-12-28T00:23:32.753388Z 1 [System] [MY-013577] [InnoDB] InnoDB initializatio 2024-12-28T00:23:33.409805Z 0 [Warning] [MY-010068] [Server] CA certificate ca.p 2024-12-28T00:23:33.409836Z 0 [System] [MY-013602] [Server] Channel mysql_main c 2024-12-28T00:23:33.500879Z 0 [System] [MY-011323] [Server] X Plugin ready for c 2024-12-28T00:23:33.500933Z 0 [System] [MY-010931] [Server] /www/server/mysql/bi 2024-12-28T00:23:34.312344Z 8 [Warning] [MY-013360] [Server] Plugin mysql_native 2024-12-28T00:23:34.314011Z 8 [Warning] [MY-013360] [Server] Plugin mysql_native 2024-12-28T00:23:57.913437Z 0 [System] [MY-013172] [Server] Received SHUTDOWN fr 2024-12-28T00:24:00.505327Z 0 [System] [MY-010910] [Server] /www/server/mysql/bi 2024-12-28T00:24:11.693159Z 0 [Warning] [MY-000081] [Server] option 'max_allowed 2024-12-28T00:24:11.693209Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is 2024-12-28T00:24:11.693264Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO 2024-12-28T00:24:11.693349Z 0 [Warning] [MY-010918] [Server] 'default_authentica 2024-12-28T00:24:11.693365Z 0 [System] [MY-010116] [Server] /www/server/mysql/bi 2024-12-28T00:24:11.706584Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configur 2024-12-28T00:24:11.708078Z 1 [System] [MY-013576] [InnoDB] InnoDB initializatio 2024-12-28T00:24:12.690078Z 1 [System] [MY-013577] [InnoDB] InnoDB initializatio 2024-12-28T00:24:13.623095Z 0 [Warning] [MY-010068] [Server] CA certificate ca.p 2024-12-28T00:24:13.623142Z 0 [System] [MY-013602] [Server] Channel mysql_main c 2024-12-28T00:24:13.896620Z 0 [System] [MY-011323] [Server] X Plugin ready for c 2024-12-28T00:24:13.896667Z 0 [System] [MY-010931] [Server] /www/server/mysql/bi 2024-12-28T00:24:16.004441Z 0 [System] [MY-013172] [Server] Received SHUTDOWN fr 2024-12-28T00:24:17.119275Z 0 [System] [MY-010910] [Server] /www/server/mysql/bi 2024-12-28T00:24:20.311950Z 0 [Warning] [MY-000081] [Server] option 'max_allowed 2024-12-28T00:24:20.312000Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is 2024-12-28T00:24:20.312058Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO 2024-12-28T00:24:20.312127Z 0 [Warning] [MY-010918] [Server] 'default_authentica 2024-12-28T00:24:20.312143Z 0 [System] [MY-010116] [Server] /www/server/mysql/bi 2024-12-28T00:24:20.321559Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configur 2024-12-28T00:24:20.324043Z 1 [System] [MY-013576] [InnoDB] InnoDB initializatio 2024-12-28T00:24:26.794253Z 1 [System] [MY-013577] [InnoDB] InnoDB initializatio 2024-12-28T00:24:41.898276Z 0 [Warning] [MY-000081] [Server] option 'max_allowed 2024-12-28T00:24:41.898348Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is 2024-12-28T00:24:41.898440Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO 2024-12-28T00:24:41.898523Z 0 [Warning] [MY-010918] [Server] 'default_authentica 2024-12-28T00:24:41.898543Z 0 [System] [MY-010116] [Server] /www/server/mysql/bi 2024-12-28T00:24:42.195597Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configur 2024-12-28T00:24:42.392080Z 1 [System] [MY-013576] [InnoDB] InnoDB initializatio 2024-12-28T00:24:49.298433Z 1 [System] [MY-013577] [InnoDB] InnoDB initializatio 2024-12-28T00:24:58.592299Z 0 [Warning] [MY-010068] [Server] CA certificate ca.p 2024-12-28T00:24:58.592401Z 0 [System] [MY-013602] [Server] Channel mysql_main c 2024-12-28T00:25:00.797440Z 0 [System] [MY-011323] [Server] X Plugin ready for c 2024-12-28T00:25:00.797573Z 0 [System] [MY-010931] [Server] /www/server/mysql/bi 2024-12-28T00:30:04.021732Z 0 [System] [MY-013172] [Server] Received SHUTDOWN fr 2024-12-28T00:30:04.600591Z 0 [System] [MY-010910] [Server] /www/server/mysql/bi 2024-12-28T00:30:07.729230Z 0 [Warning] [MY-000081] [Server] option 'max_allowed 2024-12-28T00:30:07.729274Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is 2024-12-28T00:30:07.729329Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO 2024-12-28T00:30:07.729420Z 0 [Warning] [MY-010918] [Server] 'default_authentica 2024-12-28T00:30:07.729436Z 0 [System] [MY-010116] [Server] /www/server/mysql/bi 2024-12-28T00:30:07.738587Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configur 2024-12-28T00:30:07.740442Z 1 [System] [MY-013576] [InnoDB] InnoDB initializatio 2024-12-28T00:30:08.706705Z 1 [System] [MY-013577] [InnoDB] InnoDB initializatio 2024-12-28T00:30:08.791057Z 1 [ERROR] [MY-011087] [Server] Different lower_case_ 2024-12-28T00:30:08.791264Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initi 2024-12-28T00:30:08.791279Z 0 [ERROR] [MY-010119] [Server] Aborting 2024-12-28T00:30:08.917790Z 0 [System] [MY-010910] [Server] /www/server/mysql/bi _packet': unsigned value 107374182400 adjusted to 1073741824. deprecated and will be removed in a future release. _ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with st rict mode. They will be merged with strict mode in a future release. tion_plugin' is deprecated and will be removed in a future release. Please use a uthentication_policy instead. n/mysqld (mysqld 8.0.35) starting as process 249701 ation parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=10485760. Please use innodb_redo_log_c apacity instead. n has started. n has ended. em is self signed. onfigured to support TLS. Encrypted connections are now supported for this chann el. onnections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock n/mysqld: ready for connections. Version: '8.0.35' socket: '/tmp/mysql.sock' p ort: 3306 Source distribution. _password reported: ''mysql_native_password' is deprecated and will be removed i n a future release. Please use caching_sha2_password instead' _password reported: ''mysql_native_password' is deprecated and will be removed i n a future release. Please use caching_sha2_password instead' om user <via user signal>. Shutting down mysqld (Version: 8.0.35). n/mysqld: Shutdown complete (mysqld 8.0.35) Source distribution. _packet': unsigned value 107374182400 adjusted to 1073741824. deprecated and will be removed in a future release. _ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with st rict mode. They will be merged with strict mode in a future release. tion_plugin' is deprecated and will be removed in a future release. Please use a uthentication_policy instead. n/mysqld (mysqld 8.0.35) starting as process 253391 ation parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=10485760. Please use innodb_redo_log_c apacity instead. n has started. n has ended. em is self signed. onfigured to support TLS. Encrypted connections are now supported for this chann el. onnections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock n/mysqld: ready for connections. Version: '8.0.35' socket: '/tmp/mysql.sock' p ort: 3306 Source distribution. om user <via user signal>. Shutting down mysqld (Version: 8.0.35). n/mysqld: Shutdown complete (mysqld 8.0.35) Source distribution. _packet': unsigned value 107374182400 adjusted to 1073741824. deprecated and will be removed in a future release. _ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with st rict mode. They will be merged with strict mode in a future release. tion_plugin' is deprecated and will be removed in a future release. Please use a uthentication_policy instead. n/mysqld (mysqld 8.0.35) starting as process 254457 ation parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=10485760. Please use innodb_redo_log_c apacity instead. n has started. n has ended. _packet': unsigned value 107374182400 adjusted to 1073741824. deprecated and will be removed in a future release. _ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with st rict mode. They will be merged with strict mode in a future release. tion_plugin' is deprecated and will be removed in a future release. Please use a uthentication_policy instead. n/mysqld (mysqld 8.0.35) starting as process 255517 ation parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=10485760. Please use innodb_redo_log_c apacity instead. n has started. n has ended. em is self signed. onfigured to support TLS. Encrypted connections are now supported for this chann el. onnections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock n/mysqld: ready for connections. Version: '8.0.35' socket: '/tmp/mysql.sock' p ort: 3306 Source distribution. om user <via user signal>. Shutting down mysqld (Version: 8.0.35). n/mysqld: Shutdown complete (mysqld 8.0.35) Source distribution. _packet': unsigned value 107374182400 adjusted to 1073741824. deprecated and will be removed in a future release. _ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with st rict mode. They will be merged with strict mode in a future release. tion_plugin' is deprecated and will be removed in a future release. Please use a uthentication_policy instead. n/mysqld (mysqld 8.0.35) starting as process 272515 ation parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=10485760. Please use innodb_redo_log_c apacity instead. n has started. n has ended. table_names settings for server ('0') and data dictionary ('1'). alization failed. n/mysqld: Shutdown complete (mysqld 8.0.35) Source distribution.

rudysi

DimmmCom

Hi, thanks for the answer.

I tried uninstall and install again MySQL 8.0.35 via aaPanel app store, it doesn't work, it's still conflict between lower_case_ table_names settings for server ('0') and data dictionary ('1').

How do I change data dictionary when installing MySQL?

DimmmCom

rudysi /www/server/panel/install/mysql.sh
Edit the Mysql_Initialize() function.
elif [ "${version}" == "5.7" ] || [ "${version}" == "8.0" ] || [ "${version}" == "8.4" ] || [ "${version}" == "9.0" ];then ${Setup_Path}/bin/mysqld --initialize-insecure --basedir=${Setup_Path} --datadir=${Data_Path} --user=mysql
replace with
elif [ "${version}" == "5.7" ] || [ "${version}" == "8.0" ] || [ "${version}" == "8.4" ] || [ "${version}" == "9.0" ];then ${Setup_Path}/bin/mysqld --initialize-insecure --basedir=${Setup_Path} --datadir=${Data_Path} --user=mysql --lower_case_table_names=0
Uninstall Mysql
Run the Mysql installation using the command bash /www/server/panel/install/mysql.sh install 8.0

rudysi

DimmmCom

Hi,

I did exactly as you instructed, after the installation process was complete, the default configuration, lower_case_table_names was still 1, I changed it to 0, saved the configuration, restarted mysql service.

It didn't work, error again, I stopped mysql service, then started it again, same error, mysql service didn't start.

Here's is the last couple lines from mysql log:

2024-12-30T14:14:23.309861Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2024-12-30T14:14:24.027856Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2024-12-30T14:14:24.042863Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('0') and data dictionary ('1'). 2024-12-30T14:14:24.043071Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2024-12-30T14:14:24.043082Z 0 [ERROR] [MY-010119] [Server] Aborting 2024-12-30T14:14:24.654267Z 0 [System] [MY-010910] [Server] /www/server/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.36) Source distribution.

If I uninstall MySQL and install mariaDB, without change anything, when I imported a database through phpMyAdmin, all my table names are case sensitive already.

Thank you.