当前位置：当前位置：首页 >人工智能 >MySQL8.0修改lower_case_table_names参数导致重启失败正文

MySQL8.0修改lower_case_table_names参数导致重启失败

作者:应用开发　来源:IT科技　浏览:　【大 中 小】　发布时间:2025-11-05 08:51:12 评论数:

事件起因：在测试一个数据迁移工具时，参数源端oracle19c数据迁移到目标端MySQL8.0，重启提示迁移目标端 Unknown database SBTEST，失败报错如下：复制2022-07-29 10:08:19,参数155 ERROR com.greatsync.connector.jdbc.internal.ComplexJdbcOutputFormat [] - JDBC executeBatch error, retry times = 0java.sql.BatchUpdateException: Unknown database SBTEST at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_312] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_312] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_312] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_312] at com.mysql.cj.util.Util.handleNewInstance(Util.java:192) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.util.Util.getInstance(Util.java:167) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.util.Util.getInstance(Util.java:174) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:755) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:426) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.greatsync.connector.jdbc.statement.FieldNamedPreparedStatementImpl.executeBatch(FieldNamedPreparedStatementImpl.java:77) ~[blob_p-90078b57fb2c51eb5935c045d2e493ef0a7d1524-e9c0e1166eea1bd94ba3d5a23c03b9b3:?] at com.greatsync.connector.jdbc.internal.executor.TableSimpleStatementExecutor.executeBatch(TableSimpleStatementExecutor.java:63) ~[blob_p-90078b57fb2c51eb5935c045d2e493ef0a7d1524-e9c0e1166eea1bd94ba3d5a23c03b9b3:?] at com.greatsync.connector.jdbc.internal.executor.TableBufferReducedStatementExecutor.executeBatch(TableBufferReducedStatementExecutor.java:121) ~[blob_p-90078b57fb2c51eb5935c045d2e493ef0a7d1524-e9c0e1166eea1bd94ba3d5a23c03b9b3:?] at com.greatsync.connector.jdbc.internal.ComplexJdbcOutputFormat.attemptFlush(ComplexJdbcOutputFormat.java:243) ~[blob_p-90078b57fb2c51eb5935c045d2e493ef0a7d1524-e9c0e1166eea1bd94ba3d5a23c03b9b3:?] at com.greatsync.connector.jdbc.internal.ComplexJdbcOutputFormat.flush(ComplexJdbcOutputFormat.java:210) ~[blob_p-90078b57fb2c51eb5935c045d2e493ef0a7d1524-e9c0e1166eea1bd94ba3d5a23c03b9b3:?] at com.greatsync.connector.jdbc.internal.ComplexJdbcSinkFunction.lambda$open$0(ComplexJdbcSinkFunction.java:63) ~[blob_p-90078b57fb2c51eb5935c045d2e493ef0a7d1524-e9c0e1166eea1bd94ba3d5a23c03b9b3:?] at org.apache.flink.streaming.runtime.tasks.StreamTask.invokeProcessingTimeCallback(StreamTask.java:1693) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.StreamTask.lambda$null$22(StreamTask.java:1684) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.StreamTaskActionExecutor$1.runThrowing(StreamTaskActionExecutor.java:50) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.mailbox.Mail.run(Mail.java:90) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.mailbox.MailboxProcessor.processMailsNonBlocking(MailboxProcessor.java:353) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.mailbox.MailboxProcessor.processMail(MailboxProcessor.java:317) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.mailbox.MailboxProcessor.runMailboxLoop(MailboxProcessor.java:201) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.StreamTask.runMailboxLoop(StreamTask.java:809) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:761) ~[flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.runtime.taskmanager.Task.runWithSystemExitMonitoring(Task.java:958) [flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.runtime.taskmanager.Task.restoreAndInvoke(Task.java:937) [flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:766) [flink-dist_2.11-1.14.4.jar:1.14.4] at org.apache.flink.runtime.taskmanager.Task.run(Task.java:575) [flink-dist_2.11-1.14.4.jar:1.14.4] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_312]Caused by: java.sql.SQLSyntaxErrorException: Unknown database SBTEST at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1098) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1046) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1371) ~[mysql-connector-java-8.0.28.jar:8.0.28] at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:716) ~[mysql-connector-java-8.0.28.jar:8.0.28] ... 22 more1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.

于是查看了目标端MySQL的Databases：

复制mysql> select @@version,@@default_storage_engine;+-----------+--------------------------+| @@version | @@default_storage_engine |+-----------+--------------------------+| 8.0.29 | InnoDB |+-----------+--------------------------+1 row in set (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sbtest || sys |+--------------------+5 rows in set (0.01 sec)--使用大写的database name，报错mysql>

use SBTEST

ERROR 1049 (42000): Unknown database SBTEST--使用小写的重启database name，可以正常访问mysql>

use sbtest

Reading table information for completion of table and 失败

column names

You can turn off this feature to get a quicker startup with -

Database changed1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.

发现目标端是存在sbtest这个库，但是参数只能小写访问，继续检查大小写敏感配置

复制mysql> select @@lower_case_table_names;+--------------------------+| @@lower_case_table_names |+--------------------------+| 0 |+--------------------------+1 row in set (0.00 sec)1.2.3.4.5.6.7.

可见目标端的重启MySQL8.0未开启忽略大写的配置，Oracle的失败对象名称默认是大写，迁移工具迁移时未进行对象名称转小写，参数导致迁移失败，重启程序报错这时的失败想法那手动改下lower_case_table_names不就行了，于是参数就有了如下的操作：修改MySQL配置文件：

复制#my.cnf

配置中增加如下配置

lower-case-table-names=11.2.

重启我的MySQL8.0 docker容器并查看日志：

复制root@mysql:~# docker restart mysql8.0.29mysql8.0.29root@mysql:~# docker logs -f mysql8.0.292022-07-29 02:28:48+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1

debian10 started.

2022-07-29 02:28:48+00:00 [Note] [Entrypoint]: Switching to dedicated user mysql2022-07-29 02:28:48+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1

debian10 started.

2022-07-29T02:28:48.532695-00:00 0 [Warning] [MY-011068] [Server] The syntax log_slave_updates is deprecated and will be removed in

a future release. Please use log_replica_updates instead.

2022-07-29T02:28:48.532736-00:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.2022-07-29T02:28:48.532776-00:00 0 [Warning] [MY-010918] [Server] default_authentication_plugin is deprecated and will be removed in

a future release. Please use authentication_policy instead.

2022-07-29T02:28:48.532794-00:00 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29) starting as process 12022-07-29T02:28:48.541090-00:00 1 [System] [MY-013576] [InnoDB]

InnoDB initialization has started.

2022-07-29T02:28:50.875623-00:00 1 [System] [MY-013577] [InnoDB]

InnoDB initialization has ended.

2022-07-29T02:28:50.896028-00:00 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server (1) and data dictionary (0)

2022-07-29T02:28:50.896513-00:00 0 [ERROR] [MY-010020] [Server]

Data Dictionary initialization failed.

2022-07-29T02:28:50.897228-00:00 0 [ERROR] [MY-010119] [Server]

Aborting

2022-07-29T02:28:51.615910-00:00 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29) MySQL Community Server -

GPL.

2022-07-29 02:28:53+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1

debian10 started.

2022-07-29 02:28:53+00:00 [Note] [Entrypoint]: Switching to dedicated user mysql2022-07-29 02:28:53+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1

debian10 started.

2022-07-29T02:28:54.151877-00:00 0 [Warning] [MY-011068] [Server] The syntax log_slave_updates is deprecated and will be removed in

a future release. Please use log_replica_updates instead.

2022-07-29T02:28:54.151918-00:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.2022-07-29T02:28:54.151954-00:00 0 [Warning] [MY-010918] [Server] default_authentication_plugin is deprecated and will be removed in

a future release. Please use authentication_policy instead.

2022-07-29T02:28:54.151972-00:00 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29) starting as process 12022-07-29T02:28:54.401739-00:00 1 [System] [MY-013576] [InnoDB]

InnoDB initialization has started.

2022-07-29T02:28:56.161607-00:00 1 [System] [MY-013577] [InnoDB]

InnoDB initialization has ended.

2022-07-29T02:28:56.166187-00:00 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server (1) and data dictionary (0)

2022-07-29T02:28:56.166354-00:00 0 [ERROR] [MY-010020] [Server]

Data Dictionary initialization failed.

2022-07-29T02:28:56.166517-00:00 0 [ERROR] [MY-010119] [Server]

Aborting

2022-07-29T02:28:56.800121-00:00 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29) MySQL Community Server - GPL.1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.

咦，WordPress模板居然重启失败并报错，重启我记得之前MySQL5.7上是失败可以修改成功的，于是在MySQL5.7上复现了一下该修改操作：

复制mysql> select @@version,@@default_storage_engine;+------------+--------------------------+| @@version | @@default_storage_engine |+------------+--------------------------+| 5.7.37-log | InnoDB |+------------+--------------------------+1 row in set (0.00 sec)mysql> select @@lower_case_table_names;+--------------------------+| @@lower_case_table_names |+--------------------------+| 0 |+--------------------------+1 row in set (0.00 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

配置文件中添加：lower-case-table-names=1后重启MySQL5.7的Docker容器

复制root@mysql:~#docker restart mysql5.7mysql5.7-- 查看日志，重启成功root@mysql:~#docker logs -f mysql5.72022-07-29 04:36:00+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.37-1

debian10 started.

2022-07-29 04:36:01+00:00 [Note] [Entrypoint]: Switching to dedicated user mysql2022-07-29 04:36:02+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.37-1

debian10 started.

2022-07-29T04:36:02.585577-00:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2022-07-29T04:36:02.585636-00:00 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.2022-07-29T04:36:02.585657-00:00 0 [Note] mysqld (mysqld 5.7.37-log) starting as process 1

...

2022-07-29T04:36:02.589953-00:00 0 [Note] InnoDB:

PUNCH HOLE support available

2022-07-29T04:36:02.589965-00:00 0 [Note] InnoDB: Mutexes and

rw_locks use GCC atomic builtins

2022-07-29T04:36:02.589968-00:00 0 [Note] InnoDB:

Uses event mutexes

2022-07-29T04:36:02.589970-00:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is

used for memory barrier

2022-07-29T04:36:02.589972-00:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.112022-07-29T04:36:02.589974-00:00 0 [Note] InnoDB:

Using Linux native AIO

2022-07-29T04:36:02.590203-00:00 0 [Note] InnoDB: Number of pools: 12022-07-29T04:36:02.590301-00:00 0 [Note] InnoDB:

Using CPU crc32 instructions

2022-07-29T04:36:02.591501-00:00 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128

2022-07-29T04:36:02.598983-00:00 0 [Note] InnoDB:

Completed initialization of buffer pool

2022-07-29T04:36:02.600436-00:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority()

2022-07-29T04:36:02.611754-00:00 0 [Note] InnoDB: Highest supported file format is

Barracuda.

2022-07-29T04:36:02.691303-00:00 0 [Note] InnoDB:

Creating shared tablespace for temporary tables

2022-07-29T04:36:02.691434-00:00 0 [Note] InnoDB: Setting file ./ibtmp1 size to 12 MB. Physically writing the file full;

Please wait ...

2022-07-29T04:36:02.886567-00:00 0 [Note] InnoDB: File ./ibtmp1 size is now 12

MB.

2022-07-29T04:36:02.888579-00:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s)

are active.

2022-07-29T04:36:02.888614-00:00 0 [Note] InnoDB: 32 non-redo rollback segment(s)

are active.

2022-07-29T04:36:02.889822-00:00 0 [Note] InnoDB: 5.7.37 started; log sequence number 126622382022-07-29T04:36:02.890218-00:00 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/

ib_buffer_pool

2022-07-29T04:36:02.890839-00:00 0 [Note] Plugin FEDERATED is

disabled.

2022-07-29T04:36:02.897432-00:00 0 [Note] InnoDB: Buffer pool(s) load completed at 220729 4:36:022022-07-29T04:36:03.150414-00:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in

data directory. Trying to enable SSL support using them.

2022-07-29T04:36:03.150468-00:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in

data directory.

2022-07-29T04:36:03.150482-00:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or

higher.

2022-07-29T04:36:03.150491-00:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or

higher.

2022-07-29T04:36:03.151831-00:00 0 [Warning] CA certificate ca.pem is self signed

2022-07-29T04:36:03.151851-00:00 0 [Note] Skipping generation of RSA key pair as key files are present in

data directory.

2022-07-29T04:36:03.151929-00:00 0 [Note] Server hostname (bind-address): *; port: 33062022-07-29T04:36:03.151953-00:00 0 [Note] IPv6 is

available.

2022-07-29T04:36:03.151961-00:00 0 [Note] - :: resolves to ::;2022-07-29T04:36:03.151968-00:00 0 [Note] Server socket created on IP: ::

2022-07-29T04:36:03.206432-00:00 0 [Warning] Insecure configuration for --pid-file: Location /var/lib/mysql in the path is accessible to all OS users. Consider choosing a different directory.2022-07-29T04:36:03.218018-00:00 0 [Note]

Failed to start slave threads for channel

2022-07-29T04:36:03.234065-00:00 0 [Note] Event Scheduler: Loaded 0

events

2022-07-29T04:36:03.234636-00:00 0 [Note] mysqld:

ready for connections.

Version: 5.7.37-log socket: /var/run/mysqld/mysqld.sock port: 3306 MySQL Community Server (GPL)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.

查看配置生效了：

复制mysql> root@mysql:~# docker exec -it mysql5.7

bash

root@f9bc50f17027:/# mysql -uroot -p****mysql: [Warning] Using a password on

the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or

\g.

its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or

its

affiliates. Other names may be trademarks of their respective

owners.

Type help; or \h for help. Type \c

to clear the current input statement.

mysql> select @@lower_case_table_names;+--------------------------+| @@lower_case_table_names |+--------------------------+| 1 |+--------------------------+1 row in set (0.00 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.

确实MySQL5.7是支持修改的，MySQL8.0不支持修改，于是查了MySQL8.0的官网解释：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

-- linux未设置默认是0

The default value of this variable is platform-dependent (see lower_case_file_system). On Linux and other Unix-like systems, the default is 0. On Windows the default value is 1. On macOS, the default value is 2. On Linux (and other Unix-like systems), setting the value to 2 is not supported; the server forces the value to 0 instead.

-- 禁止使用与服务器初始化时使用的设置不同的lower_case_table_names启动服务器

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are determined by the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

-- 在初始化服务器之前，有必要将lower_case_table_names配置为所需的设置

It is therefore necessary to configure lower_case_table_names to the desired setting before initializing the server. In most cases, this requires configuring lower_case_table_names in a MySQL option file before starting the MySQL server for the first time.

原来需要MySQL8.0初始化的时候就需要设置好，并且后续不支持修改。于是我又重新用Docker部署了一个新的MySQL8.0数据库继续测试。

参数说明lower_case_table_names=0 表名存储为给定的大小和比较是亿华云计算区分大小写的。lower_case_table_names=1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写。lower_case_table_names=2 表名存储为给定的大小写，但是比较的时候是小写的。Unix,Linux下lower_case_table_names默认值为 0 ；Windows下默认值是 1 ；Mac OS X下默认值是 2。总结

可见我们在使用MySQL8.0前需要根据自己的使用场景评估是否要开启忽略大小写，如果需要开启忽略大小写，初始化前需要把lower-case-table-names=1写入到my.cnf配置文件中，这样才不会影响后续的使用。云服务器

电脑赛博朋克素材教程（探索赛博朋克风格的电脑设计与创作技巧）
oppo手机边充电边玩的危害与防范（揭秘oppo手机边充电边玩的危害以及如何正确使用）