暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 排查处理Mysql8版本修改lower_case_table_names参数导致重启失败【测试成功】

排查处理Mysql8版本修改lower_case_table_names参数导致重启失败【测试成功】

巴韭特锁螺丝 2024-04-12
485

事件起因：在测试一个数据迁移工具时，源端oracle19c数据迁移到目标端mysql8.0，提示迁移目标端 Unknown database ‘SBTEST’，报错如下：

于是查看了目标端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是支持修改的，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.

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

            版权声明：本文内容来自CSDN:GreatSQL社区，遵循CC 4.0 BY-SA版权协议上原文接及本声明。
            本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行可。
            原文链接：https://blog.csdn.net/GreatSQL2021/article/details/126577836
            如有涉及到侵权，请联系，将立即予以删除处理。
            在此特别鸣谢原作者的创作。
            此篇文章的所有版权归原作者所有，与本公众号无关，商业转载建议请联系原作者，非商业转载请注明出处。

            mysqlinnodbtablemysql数据库
            文章转载自巴韭特锁螺丝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

            评论