关于mysql:Mysql80修改lowercasetablenames参数导致重启失败

235次阅读

共计 16538 个字符，预计需要花费 42 分钟才能阅读完成。

  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。

  • GreatSQL 是 MySQL 的国产分支版本，应用上与 MySQL 统一。

事件起因：在测试一个数据迁徙工具时，源端 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 = 0
java.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 more

于是查看了指标端 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 -A

Database changed

发现指标端是存在 sbtest 这个库，然而只能小写访问，持续查看大小写敏感配置

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

mysql>

可见指标端的 mysql8.0 未开启疏忽大写的配置，oracle 的对象名称默认是大写，迁徙工具迁徙时未进行对象名称转小写，导致迁徙失败，程序报错
这时的想法那手动改下 lower_case_table_names 不就行了，于是就有了如下的操作：
批改 mysql 配置文件：

#my.cnf 配置中减少如下配置
lower-case-table-names=1

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

root@mysql:~# docker restart mysql8.0.29 
mysql8.0.29
root@mysql:~# docker logs -f mysql8.0.29
2022-07-29 02:28:48+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1debian10 started.
2022-07-29 02:28:48+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-07-29 02:28:48+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1debian10 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 1
2022-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-1debian10 started.
2022-07-29 02:28:53+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-07-29 02:28:53+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.29-1debian10 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 1
2022-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.

咦，竟然重启失败并报错，我记得之前 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)

配置文件中增加：lower-case-table-names= 1 后重启 mysql5.7 的 docker 容器

root@mysql:~#docker restart mysql5.7 
mysql5.7
-- 查看日志，重启胜利
root@mysql:~#docker logs -f mysql5.7 
2022-07-29 04:36:00+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.37-1debian10 started.
2022-07-29 04:36:01+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-07-29 04:36:02+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.37-1debian10 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.11
2022-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: 1
2022-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 = 128M
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 12662238
2022-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:02
2022-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: 3306
2022-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)

查看配置失效了：

mysql> root@mysql:~# docker exec -it mysql5.7  bash
root@f9bc50f17027:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or 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)

的确 mysql5.7 是反对批改的，mysql8.0 不反对批改，于是查了 mysql8.0 的官网解释：
https://dev.mysql.com/doc/ref…

— 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.

原来须要 mysql.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 配置文件中，这样才不会影响后续的应用。

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区：

欢送来 GreatSQL 社区发帖发问
https://greatsql.cn/

技术交换群：

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

正文完
mysql
发表至： mysql
2022-08-29
 0
还在划水这个SQL你能写出来吗
关于mysql:MySQL-锁的种类和概念
关于mysql:实现一个简单的Database5译文
关于mysql:Mysql基本的规则与规范