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)  
        )