Got error 197 from se while migrating tablespaces

Author: Fu Xiang

Now living in Zhuhai, mainly responsible for the maintenance of Oracle, MySQL, mongoDB and Redis.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

1. Background

The current zabbix system mysql version 5.6, the data volume is about 1.5T, there are some large tables partitioned by day, the operation of adding fields will be very time-consuming, I have encountered adding a field to a history table of several hundred GB. Using the pt-osc tool, ran
It was not finished in 2 days; in order to use the immediate addition function of mysql 8.0, I decided to upgrade to mysql 8.0.

2. Upgrade process

Because the amount of data is relatively large, the data logic export and import method will be very slow to upgrade. It is not recommended, so the In-Place method is used to upgrade. According to the upgrade path provided by the official document, you need to upgrade from 5.6 to 5.7, and then upgrade to 8.0. .

The upgrade from 5.6 to 5.7.35 is very smooth. When upgrading from 5.7.35 to 8.0.25, the upgrade fails with an error message such as
Down:

2021-07-20T07:33:18.138368Z 1 [ERROR] [MY-011006] [Server] Got error 197 from SE
while migrating tablespaces.
2021-07-20T07:33:18.145105Z 0 [ERROR] [MY-010020] [Server] Data Dictionary
initialization failed.
2021-07-20T07:33:18.145502Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-07-20T07:33:40.435143Z 0 [System] [MY-010910] [Server]
/usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.25) MySQL Community
Server - GPL.

Judging from the error message alone, it seems that the tablespace migration failed, and the data dictionary could not be initialized, causing mysql to fail to start. This raises a question: Why is it okay to upgrade to 5.7?

3. Resolution process

In the case that the error message is not obvious, I dropped a few mysql 8.0 minor version upgrade tests, and finally got a valuable error message in 8.0.15:

2021-07-20T12:25:06.672826Z 1 [ERROR] [MY-011014] [Server] Found partially
upgraded DD. Aborting upgrade and deleting all DD tables. Start the upgrade
process again.
2021-07-20T12:25:06.773766Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 7314,
name 'zabbix/#sql-ib104-715696445', file './zabbix/#sql-ib104-715696445.ibd' is
missing!
2021-07-20T12:25:06.834751Z 0 [ERROR] [MY-010020] [Server] Data Dictionary
initialization failed.

Before 8.0, the data dictionary information was distributed in three places: the server layer, the system tables under the mysql library, and the InnoDB internal system tables. The data dictionary was stored separately, and DDL was not atomic.

After 8.0, all metadata information is stored in InnoDB dictionary table and stored in a separate table space mysql.ibd. DDL is atomic.

Because the data dictionary management and storage methods have changed, upgrading to 8.0 data dictionary requires migration and conversion, so 5.6->5.7 is no problem, 5.7->8.0 is caused by the missing ./zabbix/#sql-ib104-715696445.ibd file Upgrade failed.

The #sql-ib104-715696445.ibd file does not exist in the zabbix database directory of the current environment, there is only one frm file starting with #sql-

[root@GZ-DB-6CU552YR4V zabbix]# ls -l ./#sql*
-rw-rw---- 1 mysql mysql 8808 5 9 2020 ./#sql-8427_2008.frm
[root@GZ-DB-6CU552YR4V zabbix]#

The frm file can be parsed through mysqlfrm and dbsake. This article is parsed through dbsake:

./dbsake frmdump ./#sql-8427_2008.frm

-- Table structure for table `#sql-8427_2008`

-- Created with MySQL Version xxxxxx

CREATE TABLE `#sql-8427_2008` (
`eventid` bigint(20) unsigned NOT NULL,
`source` int(11) NOT NULL DEFAULT '0',
`object` int(11) NOT NULL DEFAULT '0',
`objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
`clock` int(11) NOT NULL DEFAULT '0',
`value` int(11) NOT NULL DEFAULT '0',
`acknowledged` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`eventid`),
KEY `events_1` (`source`,`object`,`objectid`,`clock`),
KEY `events_2` (`source`,`object`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED;

How did this seemingly strange file come about? There are two ways to perform ddl operations on the table:

ALTER TABLE (ALGORITHM=COPY)
ALTER TABLE (ALGORITHM=INPLACE)

The ALGORITHM=INPLACE method is online ddl. If it exits abnormally during the operation, an orphan intermediate table prefixed by #sql-ib will be generated, accompanied by a different name frm file prefixed by #sql-.

For ALTER TABLE (ALGORITHM=COPY) mode ddl, if it exits abnormally during the operation, an orphan temporary table prefixed with #sql- will be generated, accompanied by a frm file with the same name prefixed by #sql-.

To query whether there are orphan tables in the database, you can query the data dictionary INFORMATION_SCHEMA.INNODB_SYS_TABLES.

For the current upgrade failure environment, query the data dictionary INFORMATION_SCHEMA.INNODB_SYS_TABLES according to Tablespace 7314 in the error message, and found that there is no table starting with #sql, but there are related records in information_schema.INNODB_SYS_DATAFILES, INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES, that is to say, the data dictionary Inconsistency between the recorded metadata information:

root@3306 (none)> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
Empty set (0.01 sec)

root@3306 (none)> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES whereSPACE=7314;
Empty set (0.00 sec)

[root@3306][(none)]> select * from information_schema.INNODB_SYS_DATAFILES where space=7314;
+-------+-----------------------------------+
| SPACE | PATH |
+-------+-----------------------------------+
| 7314 | ./zabbix/#sql-ib104-715696445.ibd |
+-------+-----------------------------------+
1 row in set (0.01 sec)

[root@3306][(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE =7314;
+-------+-----------------------------+------+-------------+------------+--------
---+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT |
PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE |
ALLOCATED_SIZE |
+-------+-----------------------------+------+-------------+------------+--------
---+---------------+------------+---------------+-----------+----------------+
| 7314 | zabbix/#sql-ib104-715696445 | 41 | Barracuda | Compressed |
16384 | 8192 | Single | 0 | 0 | 0
|
+-------+-----------------------------+------+-------------+------------+--------
---+---------------+------------+---------------+-----------+----------------+
1 row in set (0.01 sec)

How to clean up the orphan table? Refer to official documents:
https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

The premise of the solution given is that INFORMATION_SCHEMA.INNODB_SYS_TABLES has relevant metadata information, which is obviously different from the current environment, so the orphan intermediate table is cleaned up by DROP TABLE #mysql50##sql-ib104-715696445
defeat. It is not possible to delete the table, is it feasible to delete the database? The general steps are as follows:

1. create database zabbix_new;
2. alter table zabbix.xxxxxx rename to zabbix_new.xxxxxx;
3. drop database zabbix;
4. SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE =7314;

After testing, even if the zabbix library is deleted, the residual metadata #sql-ib104-715696445.ibd still exists.

The data dictionary is stored in the shared tablespace ibdata1. To solve this problem, the only way to solve this problem is to bypass the ibdata1 file. The following two solutions are thought of:

3.1. Logical export and import upgrade to mysql 8.0

Commonly used tools are mysqldump and mydumper, among which mydumper supports concurrency by table, which can greatly improve efficiency

3.2. Transfer table space + In-Place upgrade to mysql 8.0

Because there is no other machine to provide and the disk space is not sufficient, a single machine and multiple instances are used for table space transmission. The general steps are as follows:

1. Export user information and zabbix metadata

mysqldump -uroot -p -B mysql -E -R --triggers --hex-blob --set-gtid-purged=off -- single-transaction --master-data=2 >zabbix-metadata-01.sql
mysqldump -uroot -p -B zabbix -E -R --triggers --hex-blob --no-data --set-gtid- purged=off --single-transaction --master-data=2 >zabbix-metadata-02.sql

1. Initialize a 5.7 version 3307 port instance and start
2. Load user information and zabbix metadata

mysql -h127.0.0.1 -P3307 -uroot -p < zabbix-metadata-01.sql
mysql> flush privileges;
mysql -h127.0.0.1 -P3307 -uroot -p < zabbix-metadata-02.sql

1. New instance zabbix library discards table space

mysql -uroot -p -NBe "select concat('alter table ',TABLE_NAME,' discard tablespace;') from information_schema.TABLES where TABLE_SCHEMA='zabbix'";
use zabbix;
set foreign_key_checks=0;
alter table xxxxxx discard tablespace;

1. Close the original mysql instance cleanly, and move the ibd file under the zabbix library to the corresponding path of the new instance. If the space is sufficient, it is best to keep the original file through the cp method

mv /data/3306/zabbix/*.ibd /data/3307/zabbix/

1. Importing the table space, this step is also time-consuming, because you need to modify the space id of the page in the ibd file to be consistent with the data dictionary space id, etc.

alter table xxxxxx import tablespace;

When importing the table space, the row format defined by the data dictionary needs to be consistent with the row format recorded in the ibd file, otherwise an error will be reported:

ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

Modify the row format through alter table xxxxxx row_format=compact, the ibd file from mv in step 5 will be deleted, resulting in data loss, so mv must first go to the ibd file and then modify the row format

mv /data/3307/zabbix/acknowledges.ibd /data/3307/zabbix/acknowledges.ibd.bak alter table acknowledges row_format=compact;
mv /data/3307/zabbix/acknowledges.ibd.bak /data/3307/zabbix/acknowledges.ibd alter table acknowledges import tablespace;

1. In-Place way to upgrade the new instance to 8.0