当达到行版本64次限制时,操作将被拒绝,
并给出错误信息,建议使用 COPY 或 INPLACE 算法重建表。
4092 (HY000): Maximum row versions reached for table test/t1.
No more columns can be added or dropped instantly. Please use COPY/INPLACE.
自 MySQL 9.1.0 起,次数上升为 255。
复现
-- 创建测试表
CREATE TABLE test_instant_limit (
id INT PRIMARY KEY
);
-- 使用存储过程来执行65次ALTER TABLE操作
DELIMITER //
CREATE PROCEDURE test_instant_limit_procedure()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE alter_result VARCHAR(255);
-- 循环64次,使用INSTANT算法添加列
WHILE i <= 64 DO
SET @sql = CONCAT('ALTER TABLE test_instant_limit ADD COLUMN col_', i, ' INT, ALGORITHM=INSTANT');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT('成功添加列 col_', i, ' 使用INSTANT算法') INTO alter_result;
SELECT alter_result;
SET i = i + 1;
END WHILE;
-- 尝试第65次INSTANT更改
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET alter_result = CONCAT('错误:', @errno, ' (', @sqlstate, '): ', @text);
END;
SET @sql = 'ALTER TABLE test_instant_limit ADD COLUMN col_65 INT, ALGORITHM=INSTANT';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF alter_result IS NULL THEN
SET alter_result = '成功添加列 col_65 使用INSTANT算法(超出预期)';
END IF;
SELECT alter_result;
END;
END //
DELIMITER ;
-- 执行存储过程
CALL test_instant_limit_procedure();
-- 查看次数
SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME LIKE 'test/test_instant_limit';
+-------------------------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+-------------------------+--------------------+
| test/test_instant_limit | 64 |
+-------------------------+--------------------+
1 row in set (0.00 sec)
参考手册:
When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the
TOTAL_ROW_VERSIONS
value is reset to 0. The maximum number of row versions permitted is 64 (255 as of MySQL 9.1.0), as each row version requires additional space for table metadata. When the row version limit is reached,ADD COLUMN
andDROP COLUMN
operations usingALGORITHM=INSTANT
are rejected with an error message that recommends rebuilding the table using theCOPY
orINPLACE
algorithm.
ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html