一条诡异的create table造成主从中断且写进了主库bin

作者: Faye小峰 | 来源:发表于2023-01-09 17:20 被阅读0次

一、问题描述

主从中断报错

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.1.1.1
                  Master_User: slave
                  Master_Port: 3312
                Connect_Retry: 60
              Master_Log_File: mysql-bin.043413
          Read_Master_Log_Pos: 499900905
               Relay_Log_File: relaylog.129996
                Relay_Log_Pos: 45100600
        Relay_Master_Log_File: mysql-bin.043413
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1071
                   Last_Error: Error 'Specified key was too long; max key length is 767 bytes' on query. Default database: 'iast_test'. Query: 'CREATE TABLE IF NOT EXISTS `project_version_control` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `version` varchar(63) DEFAULT NULL COMMENT '版本号', `component_name` varchar(255) DEFAULT NULL COMMENT 'sql名', `component_version_hash` varchar(255) DEFAULT NULL COMMENT 'sql哈希值', `additional` text COMMENT '额外注释', `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_component_name` (`component_name`) ) ENGINE = InnoDB CHARSET = utf8mb4'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 471043276

然后在主库 binlog 里面找到对应报错SQL语句如下：

# at 471043276
#220308 11:04:09 server id 4113312  end_log_pos 471043876       Query   thread_id=2094628792    exec_time=0     error_code=0
use `iast_test`/*!*/;
SET TIMESTAMP=1646708649/*!*/;
CREATE TABLE IF NOT EXISTS `project_version_control` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `version` varchar(63) DEFAULT NULL COMMENT '版本号', `component_name` varchar(255) DEFAULT NULL COMMENT 'sql名', `component_version_hash` varchar(255) DEFAULT NULL COMMENT 'sql哈希值', `additional` text COMMENT '额外注释', `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_component_name` (`component_name`) ) ENGINE = InnoDB CHARSET = utf8mb4
/*!*/;

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.6.24-72.2-log |
+-----------------+
1 row in set (0.00 sec)

二、分析诊断

报错Error 'Specified key was too long; max key length is 767 bytes'
的原因是，uk_component_name索引长度 4*255>767。（表字符集CHARSET = utf8mb4表示一个字符占用4个字节，字段长度component_name varchar(255)）。

CHARSET = utf8mb4字符集下，MySQL v5.6.24 可以支持的字符串类型最长是 varchar(190),4*190<767;

诡异的是，将binlog里的这条SQL在主库上直接执行是直接报错的，报错的原因是索引长度过长：

mysql> CREATE TABLE IF NOT EXISTS `zlz_test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `version` varchar(63) DEFAULT NULL COMMENT '版本号',
    -> `component_name` varchar(255) DEFAULT NULL COMMENT 'sql名', `component_version_hash` varchar(255) DEFAULT NULL COMMENT 'sql哈希值', `additional` text COMMENT '额外注释',
    -> `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `project_version_control_UN` (`component_name`) ) ENGINE = InnoDB CHARSET = utf8mb4
    -> ;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytesytes'

诡异的点：

竟然主库直接执行不了这条SQL，那为什么SQL脚本能写进主库binlog呢？
主从上已经有这张表了，为什么是用的前缀索引

CREATE TABLE `project_version_control` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `version` varchar(63) DEFAULT NULL COMMENT '版本号',
  `component_name` varchar(255) DEFAULT NULL COMMENT 'sql名',
  `component_version_hash` varchar(255) DEFAULT NULL COMMENT 'sql哈希值',
  `additional` text COMMENT '额外注释',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_component_name` (`component_name`(150))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

和索引长度有关的参数 innodb_large_prefix ，猜测是不是主库上改了参数后创建的表？难道这个参数没有在从库生效吗？！

如果想让超过767长度的索引创建成功，需要打开三个相关参数,并设置表行格式为ROW_FORMAT=DYNAMIC。

三、场景模拟

设置如下3个参数

set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
set global innodb_file_per_table=on;

创建表的行格式ROW_FORMAT=DYNAMIC的测试表：

mysql> CREATE TABLE zlz20230109 (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    ->     `version` varchar(63) DEFAULT NULL COMMENT '版本号',
    ->     `component_name` varchar(255) DEFAULT NULL COMMENT 'sql名',
    ->     `component_version_hash` varchar(255) DEFAULT NULL COMMENT 'sql哈希值',
    ->     `additional` text COMMENT '额外注释',
    ->     `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    ->     PRIMARY KEY (`id`),
    ->     UNIQUE KEY `uk_component_name` (`component_name`) ) ENGINE = InnoDB CHARSET = utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.09 sec)

mysql> show create table zlz20230109;

在主库上建表成功了。主从中断，重现了报错！

从库中断报错如下：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.1.1.1
                  Master_User: slave
                  Master_Port: 3312
                Connect_Retry: 60
              Master_Log_File: mysql-bin.056613
          Read_Master_Log_Pos: 31119630
               Relay_Log_File: relaylog.169593
                Relay_Log_Pos: 100334911
        Relay_Master_Log_File: mysql-bin.056612
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1071
                   Last_Error: Error 'Specified key was too long; max key length is 767 bytes' on query. Default database: 'test'. Query: 'CREATE TABLE zlz20230109 (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `version` varchar(63) DEFAULT NULL COMMENT '版本号',
    `component_name` varchar(255) DEFAULT NULL COMMENT 'sql名',
    `component_version_hash` varchar(255) DEFAULT NULL COMMENT 'sql哈希值',
    `additional` text COMMENT '额外注释',
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_component_name` (`component_name`) ) ENGINE = InnoDB CHARSET = utf8mb4 ROW_FORMAT=DYNAMIC'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 100334752
              Relay_Log_Space: 3701141628
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL

主库上sql是执行成功的，并且binlog写入成功了。

查看master binlog中的记录:

#230109 15:59:15 server id 4113312  end_log_pos 100335364       Query   thread_id=801165500     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1673251155/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
CREATE TABLE zlz20230109 (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `version` varchar(63) DEFAULT NULL COMMENT '版本号',
    `component_name` varchar(255) DEFAULT NULL COMMENT 'sql名',
    `component_version_hash` varchar(255) DEFAULT NULL COMMENT 'sql哈希值',
    `additional` text COMMENT '额外注释',
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_component_name` (`component_name`) ) ENGINE = InnoDB CHARSET = utf8mb4 ROW_FORMAT=DYNAMIC
/*!*/;

故障重现步骤

终于重现出来了主从中断~

通过上面测试基本确认，当时应该是这么操作导致的主从中断：

运维人员帮开发建表，因表中索引长度超过了767的限制报错了，建表失败；
为了建表成功，运维临时打开了主库innodb_large_prefix=on，先建表成功。然后又关闭了主库innodb_large_prefix=off；
当时应该未发现测试环境主从中断。操作员又觉得这样建表索引长度太长长，于是删了刚建的表，又重新创建了含前缀索引的表；
于是，从现象上来看发生了：主从中断，而造成中断的SQL在主库直接执行是失败的，且主库上已有含前缀索引的表。

四、报错解决方案

从库中断原因

报错的MySQL版本是 v5.6.24,这个版本的innodb_large_prefix默认是 off的,所以创建超长索引报错Specified key was too long; max key length is 767 bytes ErrorCode。

在主库上调整了参数，理论上主从都应该执行成功的。模拟现场的时候，同样的SQL，为什么主库执行OK，从库却执行报错了呢？

上一步场景模拟也验证了这个推断：这个调参只在主库生效了，从库上却没有生效。

官方文档查看参数的限制：

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the source if it cannot also be enabled on replicas.

innodb_large_prefix is enabled by default in MySQL 5.7. This change coincides with the default value change for innodb_file_format, which is set to Barracuda by default in MySQL 5.7. Together, these default value changes allow larger index key prefixes to be created when using DYNAMIC or COMPRESSED row format. If either option is set to a non-default value, index key prefixes larger than 767 bytes are silently truncated.

当时的调参方式是：动态修改主库set global innodb_large_prefix=on,即时生效。然后从库的没有调整。所以，从库中断的原因确认了: 调参方式错误导致的。

正确的调参顺序应该是：
动态修改从库→主库，即时生效。然后再修改主从配置文件。

innodb_large_prefix: Enables longer keys for column prefix indexes. Removed in MySQL 8.0.0.

从库中断解决

已经知道了从库中断原因，那怎么解决就简单啦~
只需要像主库一样，调整参数，然后 start slave; 就行啦~

mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.1.1.1
                  Master_User: slave
                  Master_Port: 3312
                Connect_Retry: 60
              Master_Log_File: mysql-bin.056613
          Read_Master_Log_Pos: 62025692
               Relay_Log_File: relaylog.169593
                Relay_Log_Pos: 307516915
        Relay_Master_Log_File: mysql-bin.056612
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 307516756
              Relay_Log_Space: 3732047690
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 1523
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 4113312
                  Master_UUID: a9dadad5-12a4-11e6-8c6d-141877482bd5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: init
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

索引过长建表失败的临时解决方案

将表的行格式设置为ROW_FORMAT=DYNAMIC
设置3个参数：动态修改线上从库→主库，然后修改主从配置文件。修改命令：

set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
set global innodb_file_per_table=on;

五、运维建议

针对这个案例有以下几点建议：

1、优先建议缩小name varchar(255) 这个字段的长度，够用就行；
2、若第2步后还是报错索引超长，建议优先创建前缀索引；
3、若前2步都无法解决，再调整参数建表（注意参数的正确使用）；

其他: innodb_file_format

当前MySQL Innodb拥有两种文件格式：

旧版本的antelope，对应到两种行格式：redundant和compact
新版本的barracuda，对应到两种行格式：dynamic和compressed

The innodb_file_format default value was changed to Barracuda in MySQL 5.7.
The innodb_file_format option is deprecated; expect it to be removed in a future release. The purpose of the innodb_file_format option was to allow users to downgrade to the built-in version of InnoDB in earlier versions of MySQL. Now that those versions of MySQL have reached the end of their product lifecycles, downgrade support provided by this option is no longer necessary.

innodb_file_format 该参数在MySQL5.6 中默认是 antelope，MySQL5.7 中默认值改成Barracuda。MySQL 8.0 中该参数已经废弃。

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。设计InnoDB存储引擎的大叔们到现在为止设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。

其中，Dynamic和Compressed行格式，我现在使用的MySQL版本是5.7，它的默认行格式就是Dynamic，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

文件格式和行格式可以通过参数设置：

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_default_row_format=dynamic;

可以通过show table status like 'tbname'来查看表的行格式，知道行格式之后也就可以对应到文件格式。

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.6.24-72.2-log |
+-----------------+
1 row in set (0.00 sec)


mysql> show table status like 'book_member'\G
*************************** 1. row ***************************
           Name: book_member
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-04-16 11:56:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment: 群组记账成员表, book_id分库
1 row in set (0.00 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.7.26-29-log |
+---------------+
1 row in set (0.01 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-12-23 18:25:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_bin
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

参考链接：

一条诡异的create table造成主从中断且写进了主库bin

一、问题描述

二、分析诊断

三、场景模拟

故障重现步骤

四、报错解决方案

从库中断原因

从库中断解决

索引过长建表失败的临时解决方案

五、运维建议

其他: innodb_file_format

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读