丽水企业网站开发企业在线制作网站页面

---

一、场景

1、对接民航电子数据库

2、将mysql数据库迁移到cae数据库

3、使用CAEMigrator迁移工具进行数据库迁移时，该工具会卡死（不清楚是否是部署cae服务的服务器资源导致）

---

二、异常情况

在生成任务的时候就会卡死

---

三、排查

将以下sql分别在mysql5和mysql8数据库执行（随便哪个库），会出现不同的结果

以下SQL由民航电子数据库对接人员提供

-- sql1
SELECT* 
FROMinformation_schema.PARAMETERS;-- sql2
SELECTa.table_schema,a.TABLE_NAME,a.CONSTRAINT_name,b.constraint_type,a.column_name,a.REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,c.UPDATE_RULE,c.DELETE_RULE,
CASECONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN0 WHEN 'FOREIGN KEY' THEN1 WHEN 'UNIQUE' THEN2 WHEN 'CHECK' THEN3 ELSE 4 END,a.ORDINAL_POSITION 
FROM(SELECTtable_schema,TABLE_NAME,CONSTRAINT_name,upper(GROUP_CONCAT( DISTINCT column_name )) AS column_name,REFERENCED_TABLE_NAME,upper(GROUP_CONCAT( DISTINCT REFERENCED_COLUMN_NAME )) AS REFERENCED_COLUMN_NAME,ORDINAL_POSITION FROMinformation_schema.KEY_COLUMN_USAGE WHEREtable_schema = 'business0' AND table_name = 't_aias_busiflowcfg' GROUP BYtable_schema,TABLE_NAME,REFERENCED_TABLE_NAME,CONSTRAINT_name,ORDINAL_POSITION ) aINNER JOIN ( SELECT TABLE_NAME, CONSTRAINT_NAME, constraint_type FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'business0' AND TABLE_NAME = 't_aias_busiflowcfg' ) b ON a.constraint_name = b.constraint_name AND a.table_name = b.table_nameLEFT JOIN ( SELECT CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME = 't_aias_busiflowcfg' ) c ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME 
ORDER BYa.CONSTRAINT_name-- sql3
SELECT DISTINCTt.table_schema,t.table_name,
CASEWHEN p.partition_method = 'RANGE COLUMNS' THEN'RANGE' WHEN p.partition_method = 'LIST COLUMNS' THEN'LIST' ELSE p.partition_method END,NULL,p.partition_expression,NULL,count( p.partition_name ),0,
NULL 
FROMINFORMATION_SCHEMA.TABLES t,INFORMATION_SCHEMA.PARTITIONS p 
WHEREt.table_schema = p.table_schema AND t.table_name = p.table_name AND t.table_schema = 'business0' AND t.table_name = 't_aias_busiflownodes' AND t.create_options = 'partitioned' 
GROUP BYt.TABLE_SCHEMA,t.table_name,p.partition_method,NULL,p.PARTITION_EXPRESSION,NULL,p.partition_name

1、发现在迁移时，如果源数据库的版本是mysql5则会卡死

2、发现在迁移时，如果源数据库版本是mysql8则迁移正常

---

四、应急方案

如果需要迁移mysql5版本的数据库，可以先把数据库复制一份到mysql8，再从mysql8迁移到cae