Description:
When I used the mysql shell program to check whether it met the requirements for upgrading from 5.7 to 8.0, encountered some problems.
20) Issues reported by 'check table x for upgrade' command
Notice : Table (sysbench.TTTTTTT) - No database selected
Error : Table (sysbench.TTTTTTT) - Corrupt
...
Errors: 1
Warnings: 40
Notices: 6
ERROR: 1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.
Here you can see the table sysbench.TTTTTTT report error has been Corrupted。In fact, this table is a view. And I can use it normally
MySQL [sysbench]> select * from `TTTTTTT`;
Empty set (0.01 sec)
Through the general log, it is found that the upgrade checker will use the 'CHECK TABLE `sysbench`.`TTTTTTT` FOR UPGRADE' statement to check the table。
But I tried to execute it manually and found that it was normal
MySQL [sysbench]> CHECK TABLE `sysbench`.`TTTTTTT` FOR UPGRADE;
+------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| sysbench.TTTTTTT | check | status | OK |
+------------------+-------+----------+----------+
1 row in set (0.00 sec)
How to repeat:
1.create database test;
2. use test; and import these table
CREATE TABLE `TABLE6` (
`column19` int unsigned NOT NULL AUTO_INCREMENT,
`area` char(2) COLLATE utf8mb4_unicode_ci NOT NULL,
`column24` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
`column10` datetime NOT NULL,
`column11` datetime DEFAULT NULL,
`column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'H',
`district_group_id` int DEFAULT NULL,
`district_code` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`column19`)
) ENGINE=InnoDB AUTO_INCREMENT=259 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `TABLE5` (
`column19` int unsigned NOT NULL AUTO_INCREMENT,
`column10` datetime NOT NULL,
`column11` datetime DEFAULT NULL,
`column31` int unsigned NOT NULL,
`column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A',
PRIMARY KEY (`column19`)
) ENGINE=InnoDB AUTO_INCREMENT=1621 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `TABLE4` (
`column19` int NOT NULL AUTO_INCREMENT,
`column1` mediumint NOT NULL,
`column23` mediumint NOT NULL,
`column27` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A',
`column10` datetime NOT NULL,
`column11` datetime DEFAULT NULL,
`last_updated_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`column19`),
UNIQUE KEY `hhhhhh` (`column1`,`column23`,`column27`)
) ENGINE=InnoDB AUTO_INCREMENT=687 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `TABLE3` (
`column1` mediumint unsigned NOT NULL,
`column21` mediumint unsigned NOT NULL AUTO_INCREMENT,
`column31` tinyint unsigned DEFAULT NULL,
`column13` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`column26` int unsigned DEFAULT NULL,
`contact` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`column9` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A',
`column10` datetime NOT NULL,
`column11` datetime NOT NULL,
PRIMARY KEY (`column21`),
KEY `merchant_branch_n1` (`column1`)
) ENGINE=InnoDB AUTO_INCREMENT=4384 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `TABLE2` (
`column19` mediumint unsigned NOT NULL AUTO_INCREMENT,
`column1` mediumint unsigned NOT NULL,
`column14` tinyint unsigned NOT NULL,
`column15` mediumint unsigned NOT NULL,
`column16` mediumint unsigned NOT NULL,
`column10` datetime NOT NULL,
`column11` datetime NOT NULL,
`column9` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`column19`),
KEY `column1` (`column1`),
KEY `cccc` (`column9`),
KEY `dddd` (`column11`)
) ENGINE=InnoDB AUTO_INCREMENT=973 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `TABLE1` (
`column1` mediumint unsigned NOT NULL AUTO_INCREMENT,
`column2` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`column4` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'I',
`column3` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT '',
`column5` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`column6` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`column22` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N',
`column26` int unsigned DEFAULT NULL,
`column7` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`column10` datetime NOT NULL,
`column11` datetime NOT NULL,
`column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
`column9` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A',
`column20` mediumint unsigned DEFAULT NULL,
`column18` mediumint unsigned DEFAULT NULL,
`column17` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_price_technology_show` tinyint(1) DEFAULT '0',
`auto_electronic_statement` tinyint(1) DEFAULT '1',
PRIMARY KEY (`column1`),
UNIQUE KEY `bbbb` (`column2`),
KEY `column18` (`column18`),
KEY `idx_cold11` (`column11`)
) ENGINE=InnoDB AUTO_INCREMENT=9119 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3.CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `TTTTTTT` AS select `m`.`column1` AS `column19`,`m`.`column2` AS `column2`,`m`.`column3` AS `column3`,`m`.`column4` AS `column4`,`m`.`column5` AS `column5`,`m`.`column6` AS `column6`,`m`.`column7` AS `column7`,if(((`m`.`column8` = 'A') and (`m`.`column9` = 'A')),'A','D') AS `column8`,`m`.`column10` AS `column10`,`mc1`.`column27` AS `zone_name`,`mc2`.`column27` AS `category_name`,`mb2`.`column28` AS `column28`,`mb2`.`column29` AS `column29`,`mb2`.`column30` AS `column30`,`mb2`.`column25` AS `column25`,cast(greatest(`m`.`column11`,coalesce(`mu`.`column11`,'0000-00-00'),coalesce(`mc1`.`column11`,`mc1`.`column10`,'0000-00-00'),coalesce(`mc2`.`column11`,`mc2`.`column10`,'0000-00-00'),`mb2`.`column11`) as datetime) AS `column11`,`mb`.`column13` AS `column13`,`mu`.`column14` AS `column14`,`mu`.`column15` AS `column15`,`mu`.`column16` AS `column16`,replace(`m`.`column17`,',','||') AS `column17` from (((((`TABLE1` `m` left join `TABLE2` `mu` on((`m`.`column18` = `mu`.`column19`))) left join `TABLE3` `mb` on(((`m`.`column20` = `mb`.`column21`) and ((`m`.`column3` = 'N') or (`m`.`column22` = 'Y'))))) left join `TABLE4` `mc1` on(((`mc1`.`column1` = `m`.`column1`) and (`mc1`.`column23` = 10) and (`mc1`.`column8` = 'A')))) left join `TABLE4` `mc2` on(((`mc2`.`column1` = `m`.`column1`) and (`mc2`.`column23` = 11) and (`mc1`.`column8` = 'A')))) left join (select `mb`.`column1` AS `column1`,concat('[',group_concat(if((`mb`.`column9` = 'A'),json_quote(`mb`.`column13`),NULL) separator ','),']') AS `column28`,concat('[',group_concat(distinct if((`mb`.`column9` = 'A'),`d`.`area`,NULL) separator ','),']') AS `column30`,concat('[',convert(group_concat(distinct if((`mb`.`column9` = 'A'),`d`.`column19`,NULL) separator ',') using utf8mb4),']') AS `column29`,concat('[',group_concat(distinct if((`mb`.`column9` = 'A'),json_quote(`d`.`column24`),NULL) separator ','),']') AS `column25`,coalesce(`l`.`column11`,`l`.`column10`) AS `column11` from ((`TABLE3` `mb` join `TABLE5` `l` on((`l`.`column19` = `mb`.`column26`))) join `TABLE6` `d` on((`l`.`column31` = `d`.`column19`))) group by `mb`.`column1`) `mb2` on(((`m`.`column1` = `mb2`.`column1`) and ((`m`.`column3` = 'N') or (`m`.`column22` = 'Y')))))
4.Use mysql shell to perform upgrade checks
./mysqlsh -- util checkForServerUpgrade username:password@ip:port --target-version=8.0.30
Suggested fix:
I'm not sure why and can't give any advice at the moment
Description: When I used the mysql shell program to check whether it met the requirements for upgrading from 5.7 to 8.0, encountered some problems. 20) Issues reported by 'check table x for upgrade' command Notice : Table (sysbench.TTTTTTT) - No database selected Error : Table (sysbench.TTTTTTT) - Corrupt ... Errors: 1 Warnings: 40 Notices: 6 ERROR: 1 errors were found. Please correct these issues before upgrading to avoid compatibility issues. Here you can see the table sysbench.TTTTTTT report error has been Corrupted。In fact, this table is a view. And I can use it normally MySQL [sysbench]> select * from `TTTTTTT`; Empty set (0.01 sec) Through the general log, it is found that the upgrade checker will use the 'CHECK TABLE `sysbench`.`TTTTTTT` FOR UPGRADE' statement to check the table。 But I tried to execute it manually and found that it was normal MySQL [sysbench]> CHECK TABLE `sysbench`.`TTTTTTT` FOR UPGRADE; +------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+----------+ | sysbench.TTTTTTT | check | status | OK | +------------------+-------+----------+----------+ 1 row in set (0.00 sec) How to repeat: 1.create database test; 2. use test; and import these table CREATE TABLE `TABLE6` ( `column19` int unsigned NOT NULL AUTO_INCREMENT, `area` char(2) COLLATE utf8mb4_unicode_ci NOT NULL, `column24` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `column10` datetime NOT NULL, `column11` datetime DEFAULT NULL, `column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'H', `district_group_id` int DEFAULT NULL, `district_code` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`column19`) ) ENGINE=InnoDB AUTO_INCREMENT=259 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci CREATE TABLE `TABLE5` ( `column19` int unsigned NOT NULL AUTO_INCREMENT, `column10` datetime NOT NULL, `column11` datetime DEFAULT NULL, `column31` int unsigned NOT NULL, `column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A', PRIMARY KEY (`column19`) ) ENGINE=InnoDB AUTO_INCREMENT=1621 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci CREATE TABLE `TABLE4` ( `column19` int NOT NULL AUTO_INCREMENT, `column1` mediumint NOT NULL, `column23` mediumint NOT NULL, `column27` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A', `column10` datetime NOT NULL, `column11` datetime DEFAULT NULL, `last_updated_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`column19`), UNIQUE KEY `hhhhhh` (`column1`,`column23`,`column27`) ) ENGINE=InnoDB AUTO_INCREMENT=687 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `TABLE3` ( `column1` mediumint unsigned NOT NULL, `column21` mediumint unsigned NOT NULL AUTO_INCREMENT, `column31` tinyint unsigned DEFAULT NULL, `column13` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `column26` int unsigned DEFAULT NULL, `contact` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `column9` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A', `column10` datetime NOT NULL, `column11` datetime NOT NULL, PRIMARY KEY (`column21`), KEY `merchant_branch_n1` (`column1`) ) ENGINE=InnoDB AUTO_INCREMENT=4384 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `TABLE2` ( `column19` mediumint unsigned NOT NULL AUTO_INCREMENT, `column1` mediumint unsigned NOT NULL, `column14` tinyint unsigned NOT NULL, `column15` mediumint unsigned NOT NULL, `column16` mediumint unsigned NOT NULL, `column10` datetime NOT NULL, `column11` datetime NOT NULL, `column9` char(1) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`column19`), KEY `column1` (`column1`), KEY `cccc` (`column9`), KEY `dddd` (`column11`) ) ENGINE=InnoDB AUTO_INCREMENT=973 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `TABLE1` ( `column1` mediumint unsigned NOT NULL AUTO_INCREMENT, `column2` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `column4` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'I', `column3` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT '', `column5` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `column6` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `column22` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N', `column26` int unsigned DEFAULT NULL, `column7` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `column10` datetime NOT NULL, `column11` datetime NOT NULL, `column8` char(1) COLLATE utf8mb4_unicode_ci NOT NULL, `column9` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A', `column20` mediumint unsigned DEFAULT NULL, `column18` mediumint unsigned DEFAULT NULL, `column17` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_price_technology_show` tinyint(1) DEFAULT '0', `auto_electronic_statement` tinyint(1) DEFAULT '1', PRIMARY KEY (`column1`), UNIQUE KEY `bbbb` (`column2`), KEY `column18` (`column18`), KEY `idx_cold11` (`column11`) ) ENGINE=InnoDB AUTO_INCREMENT=9119 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 3.CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `TTTTTTT` AS select `m`.`column1` AS `column19`,`m`.`column2` AS `column2`,`m`.`column3` AS `column3`,`m`.`column4` AS `column4`,`m`.`column5` AS `column5`,`m`.`column6` AS `column6`,`m`.`column7` AS `column7`,if(((`m`.`column8` = 'A') and (`m`.`column9` = 'A')),'A','D') AS `column8`,`m`.`column10` AS `column10`,`mc1`.`column27` AS `zone_name`,`mc2`.`column27` AS `category_name`,`mb2`.`column28` AS `column28`,`mb2`.`column29` AS `column29`,`mb2`.`column30` AS `column30`,`mb2`.`column25` AS `column25`,cast(greatest(`m`.`column11`,coalesce(`mu`.`column11`,'0000-00-00'),coalesce(`mc1`.`column11`,`mc1`.`column10`,'0000-00-00'),coalesce(`mc2`.`column11`,`mc2`.`column10`,'0000-00-00'),`mb2`.`column11`) as datetime) AS `column11`,`mb`.`column13` AS `column13`,`mu`.`column14` AS `column14`,`mu`.`column15` AS `column15`,`mu`.`column16` AS `column16`,replace(`m`.`column17`,',','||') AS `column17` from (((((`TABLE1` `m` left join `TABLE2` `mu` on((`m`.`column18` = `mu`.`column19`))) left join `TABLE3` `mb` on(((`m`.`column20` = `mb`.`column21`) and ((`m`.`column3` = 'N') or (`m`.`column22` = 'Y'))))) left join `TABLE4` `mc1` on(((`mc1`.`column1` = `m`.`column1`) and (`mc1`.`column23` = 10) and (`mc1`.`column8` = 'A')))) left join `TABLE4` `mc2` on(((`mc2`.`column1` = `m`.`column1`) and (`mc2`.`column23` = 11) and (`mc1`.`column8` = 'A')))) left join (select `mb`.`column1` AS `column1`,concat('[',group_concat(if((`mb`.`column9` = 'A'),json_quote(`mb`.`column13`),NULL) separator ','),']') AS `column28`,concat('[',group_concat(distinct if((`mb`.`column9` = 'A'),`d`.`area`,NULL) separator ','),']') AS `column30`,concat('[',convert(group_concat(distinct if((`mb`.`column9` = 'A'),`d`.`column19`,NULL) separator ',') using utf8mb4),']') AS `column29`,concat('[',group_concat(distinct if((`mb`.`column9` = 'A'),json_quote(`d`.`column24`),NULL) separator ','),']') AS `column25`,coalesce(`l`.`column11`,`l`.`column10`) AS `column11` from ((`TABLE3` `mb` join `TABLE5` `l` on((`l`.`column19` = `mb`.`column26`))) join `TABLE6` `d` on((`l`.`column31` = `d`.`column19`))) group by `mb`.`column1`) `mb2` on(((`m`.`column1` = `mb2`.`column1`) and ((`m`.`column3` = 'N') or (`m`.`column22` = 'Y'))))) 4.Use mysql shell to perform upgrade checks ./mysqlsh -- util checkForServerUpgrade username:password@ip:port --target-version=8.0.30 Suggested fix: I'm not sure why and can't give any advice at the moment