Description:
1. execute instant add column in Percona 8.0.21.
2. upgrade db to Percona 8.0.40.
3. flush this table for export.
4. recreate this table and dicard+import tablespaces.
5. the data is corruption due to instant_col offset is missing.
How to repeat:
// create table in Percona 8.0.21:
CREATE TABLE `t1` (
`id` bigint unsigned NOT NULL,
`a` tinyint(1) NOT NULL DEFAULT '0',
`b` bigint unsigned NOT NULL,
`c` varchar(128) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
// insert a few record and instant add new column:
insert into t1 values(1,1,1,'aa');
alter table t1 add column `d` bigint unsigned NOT NULL;
insert into t1 values(2,2,2,'bb',2);
// the table meta of information_schema is (instant_cols=4) :
mysql> select * from information_schema.innodb_tables where space = 2;
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
| 1063 | test/t1 | 33 | 8 | 2 | Dynamic | 0 | Single | 4 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+
// shutdown db and upgrade to Percona 8.0.40, the table meta of information_schema is (instant_cols=4, total_row_versions=0) :
mysql> select * from information_schema.innodb_tables where space = 2;
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INITIAL_COLUMN_COUNTS | CURRENT_COLUMN_COUNTS | TOTAL_COLUMN_COUNTS |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+
| 1063 | test/t1 | 33 | 8 | 2 | Dynamic | 0 | Single | 4 | 0 | 5 | 5 | 5 |
+----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+
// flush table for export and import:
flush table t1 for export;
## backup t1.ibd and t1.cfg
drop table t1;
CREATE TABLE `t1` (
`id` bigint unsigned NOT NULL,
`a` tinyint(1) NOT NULL DEFAULT '0',
`b` bigint unsigned NOT NULL,
`c` varchar(128) DEFAULT '',
`d` bigint unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
alter table t1 discard tablespace;
alter table t1 import tablespace;
// for debug version, db is crashed:
[ERROR] [MY-013183] [InnoDB] Assertion failure: rec.h:1009:!rec_get_instant_flag_new(rec) thread 139865551238912
// for release version, import is succeed but check table failed:
mysql> check table t1;
+------------------------------------------+-------+----------+---------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------------------------------+-------+----------+---------------------------------------------------+
| test.t1 | check | Warning | InnoDB: The B-tree of index PRIMARY is corrupted. |
| test.t1 | check | error | Corrupt |
+------------------------------------------+-------+----------+---------------------------------------------------+
Suggested fix:
The reason of this issue is the implication of function: set_instant_info_v2
When we flush table after upgrade to 8.0.40, the version of cfg is IB_EXPORT_CFG_VERSION_V7.
For IB_EXPORT_CFG_VERSION_V7, import table get instant_info based on row_version.
But this table executed instant_add_col when the db version is 8.0.21, so it does not have row_version. It has INSTANT_COLS to identify instant info. But After import, both of INSTANT_COLS and TOTAL_ROW_VERSIONS is missing, so the data is corrupt.
/** Set the instant ADD COLUMN information to the table.
@return DB_SUCCESS if all instant columns are trailing columns, or error code */
dberr_t row_import::set_instant_info(THD *thd,
const dd::Table *dd_table) UNIV_NOTHROW {
if (m_version >= IB_EXPORT_CFG_VERSION_V7) {
return set_instant_info_v2(thd, dd_table);
}
/** Set the instant ADD/DROP COLUMN information to the table.
@return DB_SUCCESS if successful, or error code */
dberr_t row_import::set_instant_info_v2(THD *thd, const dd::Table *dd_table)
UNIV_NOTHROW {
dberr_t err = DB_SUCCESS;
bool src_has_row_versions = has_row_versions();
bool dst_has_row_versions = m_table->has_row_versions();
/* None of the table has INSTANT columns. Return success. */
if (!src_has_row_versions && !dst_has_row_versions) {
return (DB_SUCCESS);
}
Description: 1. execute instant add column in Percona 8.0.21. 2. upgrade db to Percona 8.0.40. 3. flush this table for export. 4. recreate this table and dicard+import tablespaces. 5. the data is corruption due to instant_col offset is missing. How to repeat: // create table in Percona 8.0.21: CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL, `a` tinyint(1) NOT NULL DEFAULT '0', `b` bigint unsigned NOT NULL, `c` varchar(128) DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; // insert a few record and instant add new column: insert into t1 values(1,1,1,'aa'); alter table t1 add column `d` bigint unsigned NOT NULL; insert into t1 values(2,2,2,'bb',2); // the table meta of information_schema is (instant_cols=4) : mysql> select * from information_schema.innodb_tables where space = 2; +----------+---------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+---------+------+--------+-------+------------+---------------+------------+--------------+ | 1063 | test/t1 | 33 | 8 | 2 | Dynamic | 0 | Single | 4 | +----------+---------+------+--------+-------+------------+---------------+------------+--------------+ // shutdown db and upgrade to Percona 8.0.40, the table meta of information_schema is (instant_cols=4, total_row_versions=0) : mysql> select * from information_schema.innodb_tables where space = 2; +----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INITIAL_COLUMN_COUNTS | CURRENT_COLUMN_COUNTS | TOTAL_COLUMN_COUNTS | +----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+ | 1063 | test/t1 | 33 | 8 | 2 | Dynamic | 0 | Single | 4 | 0 | 5 | 5 | 5 | +----------+---------+------+--------+-------+------------+---------------+------------+--------------+--------------------+-----------------------+-----------------------+---------------------+ // flush table for export and import: flush table t1 for export; ## backup t1.ibd and t1.cfg drop table t1; CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL, `a` tinyint(1) NOT NULL DEFAULT '0', `b` bigint unsigned NOT NULL, `c` varchar(128) DEFAULT '', `d` bigint unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; alter table t1 discard tablespace; alter table t1 import tablespace; // for debug version, db is crashed: [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.h:1009:!rec_get_instant_flag_new(rec) thread 139865551238912 // for release version, import is succeed but check table failed: mysql> check table t1; +------------------------------------------+-------+----------+---------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------------------------+-------+----------+---------------------------------------------------+ | test.t1 | check | Warning | InnoDB: The B-tree of index PRIMARY is corrupted. | | test.t1 | check | error | Corrupt | +------------------------------------------+-------+----------+---------------------------------------------------+ Suggested fix: The reason of this issue is the implication of function: set_instant_info_v2 When we flush table after upgrade to 8.0.40, the version of cfg is IB_EXPORT_CFG_VERSION_V7. For IB_EXPORT_CFG_VERSION_V7, import table get instant_info based on row_version. But this table executed instant_add_col when the db version is 8.0.21, so it does not have row_version. It has INSTANT_COLS to identify instant info. But After import, both of INSTANT_COLS and TOTAL_ROW_VERSIONS is missing, so the data is corrupt. /** Set the instant ADD COLUMN information to the table. @return DB_SUCCESS if all instant columns are trailing columns, or error code */ dberr_t row_import::set_instant_info(THD *thd, const dd::Table *dd_table) UNIV_NOTHROW { if (m_version >= IB_EXPORT_CFG_VERSION_V7) { return set_instant_info_v2(thd, dd_table); } /** Set the instant ADD/DROP COLUMN information to the table. @return DB_SUCCESS if successful, or error code */ dberr_t row_import::set_instant_info_v2(THD *thd, const dd::Table *dd_table) UNIV_NOTHROW { dberr_t err = DB_SUCCESS; bool src_has_row_versions = has_row_versions(); bool dst_has_row_versions = m_table->has_row_versions(); /* None of the table has INSTANT columns. Return success. */ if (!src_has_row_versions && !dst_has_row_versions) { return (DB_SUCCESS); }