一條詭異的create table造成主從中斷且寫進(jìn)了主庫binlog

一、問題描述

主從中斷報(bào)錯(cuò)

*************************** 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 '版本號(hào)', `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 里面找到對(duì)應(yīng)報(bào)錯(cuò)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 '版本號(hào)', `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)

二、分析診斷

報(bào)錯(cuò)Error 'Specified key was too long; max key length is 767 bytes'
的原因是，uk_component_name索引長(zhǎng)度 4*255>767。（表字符集CHARSET = utf8mb4表示一個(gè)字符占用4個(gè)字節(jié)，字段長(zhǎng)度component_name varchar(255)）。

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

詭異的是，將binlog里的這條SQL在主庫上直接執(zhí)行是直接報(bào)錯(cuò)的，報(bào)錯(cuò)的原因是索引長(zhǎng)度過長(zhǎng)：

mysql> CREATE TABLE IF NOT EXISTS `zlz_test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', `version` varchar(63) DEFAULT NULL COMMENT '版本號(hào)',
    -> `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'

詭異的點(diǎn)：

  1. 竟然主庫直接執(zhí)行不了這條SQL，那為什么SQL腳本能寫進(jìn)主庫binlog呢？
  2. 主從上已經(jīng)有這張表了，為什么是用的前綴索引
CREATE TABLE `project_version_control` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `version` varchar(63) DEFAULT NULL COMMENT '版本號(hào)',
  `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
  1. 和索引長(zhǎng)度有關(guān)的參數(shù) innodb_large_prefix ，猜測(cè)是不是主庫上改了參數(shù)后創(chuàng)建的表？難道這個(gè)參數(shù)沒有在從庫生效嗎？！

如果想讓超過767長(zhǎng)度的索引創(chuàng)建成功，需要打開三個(gè)相關(guān)參數(shù),并設(shè)置表行格式為ROW_FORMAT=DYNAMIC。

三、場(chǎng)景模擬

  1. 設(shè)置如下3個(gè)參數(shù)
set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
set global innodb_file_per_table=on;
  1. 創(chuàng)建表的行格式ROW_FORMAT=DYNAMIC的測(cè)試表：
mysql> CREATE TABLE zlz20230109 (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->     `version` varchar(63) DEFAULT NULL COMMENT '版本號(hào)',
    ->     `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;
  1. 在主庫上建表成功了。主從中斷，重現(xiàn)了報(bào)錯(cuò)！

從庫中斷報(bào)錯(cuò)如下：

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 '版本號(hào)',
    `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
  1. 主庫上sql是執(zhí)行成功的，并且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 '版本號(hào)',
    `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
/*!*/;

故障重現(xiàn)步驟

終于重現(xiàn)出來了主從中斷~

通過上面測(cè)試基本確認(rèn)，當(dāng)時(shí)應(yīng)該是這么操作導(dǎo)致的主從中斷：

  1. 運(yùn)維人員幫開發(fā)建表，因表中索引長(zhǎng)度超過了767的限制報(bào)錯(cuò)了，建表失??；
  2. 為了建表成功，運(yùn)維臨時(shí)打開了主庫innodb_large_prefix=on，先建表成功。然后又關(guān)閉了主庫innodb_large_prefix=off；
  3. 當(dāng)時(shí)應(yīng)該未發(fā)現(xiàn)測(cè)試環(huán)境主從中斷。操作員又覺得這樣建表索引長(zhǎng)度太長(zhǎng)長(zhǎng)，于是刪了剛建的表，又重新創(chuàng)建了含前綴索引的表；
  4. 于是，從現(xiàn)象上來看發(fā)生了：主從中斷，而造成中斷的SQL在主庫直接執(zhí)行是失敗的，且主庫上已有含前綴索引的表。

四、報(bào)錯(cuò)解決方案

從庫中斷原因

報(bào)錯(cuò)的MySQL版本是 v5.6.24,這個(gè)版本的innodb_large_prefix默認(rèn)是 off的,所以創(chuàng)建超長(zhǎng)索引報(bào)錯(cuò)Specified key was too long; max key length is 767 bytes ErrorCode。

在主庫上調(diào)整了參數(shù)，理論上主從都應(yīng)該執(zhí)行成功的。模擬現(xiàn)場(chǎng)的時(shí)候，同樣的SQL，為什么主庫執(zhí)行OK，從庫卻執(zhí)行報(bào)錯(cuò)了呢？

上一步場(chǎng)景模擬也驗(yàn)證了這個(gè)推斷：這個(gè)調(diào)參只在主庫生效了，從庫上卻沒有生效。

官方文檔查看參數(shù)的限制：

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.

當(dāng)時(shí)的調(diào)參方式是：動(dòng)態(tài)修改 主庫set global innodb_large_prefix=on,即時(shí)生效。然后從庫的沒有調(diào)整。所以，從庫中斷的原因確認(rèn)了: 調(diào)參方式錯(cuò)誤導(dǎo)致的。

正確的調(diào)參順序應(yīng)該是：
動(dòng)態(tài)修改從庫→主庫，即時(shí)生效。然后再修改主從配置文件。

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

從庫中斷解決

已經(jīng)知道了從庫中斷原因，那怎么解決就簡(jiǎn)單啦~
只需要像主庫一樣，調(diào)整參數(shù)，然后 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)

索引過長(zhǎng)建表失敗的臨時(shí)解決方案

  1. 將表的行格式設(shè)置為ROW_FORMAT=DYNAMIC
  2. 設(shè)置3個(gè)參數(shù)：動(dòng)態(tài)修改線上從庫→主庫，然后修改主從配置文件。修改命令：
set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
set global innodb_file_per_table=on;

五、運(yùn)維建議

針對(duì)這個(gè)案例有以下幾點(diǎn)建議：

  • 1、優(yōu)先建議縮小name varchar(255) 這個(gè)字段的長(zhǎng)度，夠用就行；
  • 2、若第2步后還是報(bào)錯(cuò)索引超長(zhǎng)，建議優(yōu)先創(chuàng)建前綴索引；
  • 3、若前2步都無法解決，再調(diào)整參數(shù)建表（注意參數(shù)的正確使用）；

其他: innodb_file_format

當(dāng)前MySQL Innodb擁有兩種文件格式：

  1. 舊版本的antelope，對(duì)應(yīng)到兩種行格式：redundant和compact
  2. 新版本的barracuda，對(duì)應(yīng)到兩種行格式：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 該參數(shù)在MySQL5.6 中默認(rèn)是 antelope，MySQL5.7 中默認(rèn)值改成Barracuda。MySQL 8.0 中該參數(shù)已經(jīng)廢棄。

我們平時(shí)是以記錄為單位來向表中插入數(shù)據(jù)的，這些記錄在磁盤上的存放方式也被稱為行格式或者記錄格式。設(shè)計(jì)InnoDB存儲(chǔ)引擎的大叔們到現(xiàn)在為止設(shè)計(jì)了4種不同類型的行格式，分別是Compact、Redundant、Dynamic和Compressed行格式。

其中，Dynamic和Compressed行格式，我現(xiàn)在使用的MySQL版本是5.7，它的默認(rèn)行格式就是Dynamic，這倆行格式和Compact行格式挺像，只不過在處理行溢出數(shù)據(jù)時(shí)有點(diǎn)兒分歧，它們不會(huì)在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)字段真實(shí)數(shù)據(jù)的前768個(gè)字節(jié)，而是把所有的字節(jié)都存儲(chǔ)到其他頁面中，只在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)其他頁面的地址。

Compressed行格式和Dynamic不同的一點(diǎn)是，Compressed行格式會(huì)采用壓縮算法對(duì)頁面進(jìn)行壓縮，以節(jié)省空間。

  • 文件格式和行格式可以通過參數(shù)設(shè)置：
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_default_row_format=dynamic;
  • 可以通過show table status like 'tbname'來查看表的行格式，知道行格式之后也就可以對(duì)應(yīng)到文件格式。
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)

參考鏈接：

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容