MySQL 扩展字段长度报错 Specified key was too long

ALTER TABLE sign_bill_return_image_audit_result 
MODIFY COLUMN image_name VARCHAR(250) COMMENT '图片名称';

EXECUTE START AT 2023-09-08 21:31:02
Error altering new table `station_manager`.`_sign_bill_return_image_audit_result_new`: DBD::mysql::db do failed: Specified key was too long; max key length is 767 bytes [for Statement "ALTER TABLE `station_manager`.`_sign_bill_return_image_audit_result_new` MODIFY COLUMN image_name VARCHAR(250) COMMENT '图片名称';"] at /usr/bin/pt-online-schema-change line 9194.

EXECUTE FAIL AT 2023-09-08 21:31:03

mysql> show create table station_manager.sign_bill_return_image_audit_result \G
*************************** 1. row ***************************
       Table: sign_bill_return_image_audit_result
Create Table: CREATE TABLE `sign_bill_return_image_audit_result` (
  `image_name` varchar(50) NOT NULL DEFAULT '' COMMENT '图片名称',
  UNIQUE KEY `idx_img_name` (`image_name`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=27756774 DEFAULT CHARSET=utf8mb4 COMMENT='签单返还图片审核结果表'
1 row in set (0.00 sec)

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

alter table worker_board_quota_counting 
modify column  `business_id` varchar(1456) NOT NULL COMMENT '业务id';

Error altering new table `dms_offline`.`_worker_board_quota_counting_new`: DBD::mysql::db do failed: Specified key was too long; max key length is 3072 bytes [for Statement "ALTER TABLE `dms_offline`.`_worker_board_quota_counting_new` modify column `business_id` varchar(1456) NOT NULL COMMENT '业务id';"] at /usr/bin/pt-online-schema-change line 9194.

mysql> show create table dms_offline.worker_board_quota_counting \G
*************************** 1. row ***************************
       Table: worker_board_quota_counting
Create Table: CREATE TABLE `worker_board_quota_counting` (
  `business_id` varchar(456) NOT NULL COMMENT '业务id',
  UNIQUE KEY `idx_source_businessid` (`source`,`business_id`),
) ENGINE=InnoDB AUTO_INCREMENT=19747573 DEFAULT CHARSET=utf8mb4 COMMENT='人员看板计提表'
1 row in set (0.00 sec)

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

ALTER TABLE mst_sku
modify `upc_code` varchar(1000) DEFAULT NULL COMMENT '69码';

Error altering new table `wms3`.`__mst_sku_new`: DBD::mysql::db do failed: Index column size too large. The maximum column size is 767 bytes. [for Statement "ALTER TABLE `wms3`.`__mst_sku_new` MODIFY COLUMN `upc_code` varchar(1000) DEFAULT NULL COMMENT '69码';"] at /usr/bin/pt-online-schema-change line 9194.

mysql> show create table `wms3`.`mst_sku` \G
*************************** 1. row ***************************
       Table: mst_sku
Create Table: CREATE TABLE `mst_sku` (
  `upc_code` varchar(64) DEFAULT NULL COMMENT '69码',
  KEY `idx_sku_upccode` (`upc_code`),
) ENGINE=InnoDB AUTO_INCREMENT=12952734 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='商品信息表表'
1 row in set (0.00 sec)

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

ysql> select a.instance_version, t.execute_status from inception_job as t 
inner join assets_instance as a on t.mysql_ip=a.instance_ip 
where t.xbp_id =9334073;
+------------------+----------------+
| instance_version | execute_status |
+------------------+----------------+
| MySQL5.7.24      |              4 |
| MySQL5.7.24      |              4 |
| MySQL5.7.24      |              4 |
| MySQL5.7.24      |              4 |
| MySQL5.7.24      |              4 |
| MySQL5.7.24      |              4 |
| MySQL5.7.24      |              4 |
| MySQL5.7.24      |              4 |
| MySQL5.7.21      |              3 |
+------------------+----------------+
9 rows in set (0.00 sec)

mysql> create table _mst_sku_new (
`upc_code` varchar(64) DEFAULT NULL COMMENT '69码',
KEY `idx_sku_upccode` (`upc_code`)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table `_mst_sku_new` MODIFY COLUMN `upc_code` varchar(1000) DEFAULT NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
 
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter table `_mst_sku_new` MODIFY COLUMN `upc_code` varchar(1000) DEFAULT NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table `_mst_sku_new` MODIFY COLUMN `upc_code` varchar(1000) DEFAULT NULL;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> create table _mst_sku_new (
`upc_code` varchar(64) DEFAULT NULL COMMENT '69码',
KEY `idx_sku_upccode` (`upc_code`)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

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

mysql> alter table `_mst_sku_new` MODIFY COLUMN `upc_code` varchar(1000) DEFAULT NULL;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> alter table _mst_sku_new ROW_FORMAT=dynamic;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table `_mst_sku_new` MODIFY COLUMN `upc_code` varchar(1000) DEFAULT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

// 如果不是唯一索引，也就是二级非唯一索引，根据 sql_mode 判断是否返回报错
 if (key->type == KEYTYPE_MULTIPLE)
 {
   /* not a critical problem */
    // 警告
   push_warning_printf(thd, Sql_condition::SL_WARNING,
                              ER_TOO_LONG_KEY, ER(ER_TOO_LONG_KEY),
                              key_part_length);
          /* Align key length to multibyte char boundary */
          // 索引长度自动截断，比如 767 // 3 = 255
          // 将 key_part_length 减少到最接近的整数倍数，使得它不超过当前字符集中最多的多字节字符长度
          key_part_length-= key_part_length % sql_field->charset->mbmaxlen;
          /*
            If SQL_MODE is STRICT, then report error, else report warning
            and continue execution.
          */
          // 对于严格模式，将警告升级为错误
          if (thd->is_error())
            DBUG_RETURN(true);
 }

enum keytype {
  KEYTYPE_PRIMARY,
  KEYTYPE_UNIQUE,
  KEYTYPE_MULTIPLE,
  KEYTYPE_FULLTEXT,
  KEYTYPE_SPATIAL,
  KEYTYPE_FOREIGN
};

/* Even though we've defined max_supported_key_part_length, we
 still do our own checking using field_lengths to be absolutely
 sure we don't create too long indexes. */

 error = convert_error_code_to_mysql(
  row_create_index_for_mysql(index, trx, field_lengths, handler),
  flags, NULL);

/* Column or prefix length exceeds maximum column length */
  if (len > (ulint) DICT_MAX_FIELD_LEN_BY_FORMAT(table)) {
   err = DB_TOO_BIG_INDEX_COL;

   dict_mem_index_free(index);
   goto error_handling;
  }
 }

/** Find out maximum indexed column length by its table format.
For ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT, the maximum
field length is REC_ANTELOPE_MAX_INDEX_COL_LEN - 1 (767). For
Barracuda row formats COMPRESSED and DYNAMIC, the length could
be REC_VERSION_56_MAX_INDEX_COL_LEN (3072) bytes */
#define DICT_MAX_FIELD_LEN_BY_FORMAT(table)    \
  ((dict_table_get_format(table) < UNIV_FORMAT_B)  \
   ? (REC_ANTELOPE_MAX_INDEX_COL_LEN - 1)  \
   : REC_VERSION_56_MAX_INDEX_COL_LEN)

# 失败后
KEY `idx_dispatch_no` (`dispatch_no`)

# 成功后
KEY `idx_dispatch_no` (`dispatch_no`(255)),

2024-11-25T11:35:07 Copying approximately 764 rows...
Replica MSS-2hbqmzhk2m is stopped. Waiting. 
Killed

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Last_SQL_Errno: 1709
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '965c7418-175f-11ee-b6d3-fa163eae0649:12102' at master log mysql-bin.146487, end_log_pos 8137214. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
     Last_SQL_Error_Timestamp: 241125 11:35:07
            Executed_Gtid_Set: 965c7418-175f-11ee-b6d3-fa163eae0649:1-12101
                Auto_Position: 1
1 row in set (0.00 sec)

mysql> select * from performance_schema.replication_applier_status_by_worker  limit 1 \G
*************************** 1. row ***************************
         CHANNEL_NAME: 
            WORKER_ID: 1
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 965c7418-175f-11ee-b6d3-fa163eae0649:12102
    LAST_ERROR_NUMBER: 1709
   LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '965c7418-175f-11ee-b6d3-fa163eae0649:12102' at master log mysql-bin.146487, end_log_pos 8137214; Error 'Index column size too large. The maximum column size is 767 bytes.' on query. Default database: 'wms3'. Query: 'ALTER TABLE `wms3`.`_task_group_new` MODIFY COLUMN dispatch_no varchar(500) NULL COMMENT '派车单号''
 LAST_ERROR_TIMESTAMP: 2024-11-25 11:35:07
1 row in set (0.01 sec)

create table ttt(
  id int primary key auto_increment comment 'id', 
  a varchar(155355) default '' comment 'a'
) comment 'ttt';

Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.

alter table t1 add column aa varchar(155355) default '' comment 'a';

Column length too big for column 'aa' (max = 21845); use BLOB or TEXT instead.

alter table t1 modify column a varchar(155355) default '' comment 'a';

Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead.

create table ttt(
  id int primary key auto_increment comment 'id', 
  a varchar(15535) default '' comment 'a',
  b varchar(15535) default '' comment 'b'
) comment 'ttt';

{
    "id": 1, 
    "stage": "CHECKED", 
    "errlevel": 0, 
    "stagestatus": "Audit Completed", 
    "errormessage": "", 
    "sql": "USE `cctest`", 
    "affected_rows": 0, 
    "sequence": "0_0_00000000", 
    "backup_dbname": "", 
    "execute_time": "0", 
    "sqlsha1": "", 
    "backup_time": "0", 
    "actual_affected_rows": ""
}

{
    "code": 16, 
    "message": "SQLCheckMaxRowSizeError", 
    "error": "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs. 库：cctest，表：ttt，行大小为：93214，超过最大行大小65535字节，请修改字段长度或类型"
}

class SQLCheckMaxRowSizeError(BaseError):  
    def __init__(self, db_name="", table="", row_size=""):  
        BaseError.__init__(  
            self, code=SQL_Check_MAX_ROW_SIZE_Error, message="SQLCheckMaxRowSizeError",  
            error="Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. "  
                  "This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs."                  " 库：%s，表：%s，行大小为：%s，超过最大行大小65535字节，请修改字段长度或类型" % (db_name, table, row_size)  
        )