Description:
We are planning to upgrade mysql from 5.7.32 to 8.0.34. We have executed mysqlshell upgrader checker which are showing below
mysqlsh -- util checkForServerUpgrade zhenxing@10.186.64.136:5732 --target-version=8.0.34 --output-format=JSON --config-path=/data/mysql/5732/my.cnf.5732
--- output
...
{
"id": "checkTableOutput",
"title": "Issues reported by 'check table x for upgrade' command",
"status": "OK",
"detectedProblems": [
{
"level": "Notice",
"dbObject": "demo.audit_view_account",
"description": "No database selected"
},
{
"level": "Error",
"dbObject": "demo.audit_view_account",
"description": "Corrupt"
}
]
},
...
---
By comparing the view definitions in the database, it is found that the difference between the Corrupt view and the normal view is that the from statement of the Corrupt view has a table_schema prefix definition, while the normal view does not have a table_schema prefix
-- Corrupt view definition
root@localhost[demo]> show create view audit_view_account\G
*************************** 1. row ***************************
View: audit_view_account
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `audit_view_account` AS select `t`.`xAxis` AS `xAxis`,`t`.`total` AS `total`,`t`.`uk_type` AS `uk_type`,`t`.`operated_user_name` AS `operated_user_name`,`t`.`time` AS `time` from (select date_format(from_unixtime((`demo`.`platform_role`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'platform_role' AS `uk_type`,`demo`.`platform_role`.`operated_user_name` AS `operated_user_name`,`demo`.`platform_role`.`time` AS `time` from `demo`.`platform_role` group by `xAxis`,`uk_type`,`demo`.`platform_role`.`operated_user_name`,`demo`.`platform_role`.`time` union all select date_format(from_unixtime((`demo`.`dept`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'dept' AS `uk_type`,`demo`.`dept`.`operated_user_name` AS `operated_user_name`,`demo`.`dept`.`time` AS `time` from `demo`.`dept` group by `xAxis`,`uk_type`,`demo`.`dept`.`operated_user_name`,`demo`.`dept`.`time` union all select date_format(from_unixtime((`demo`.`audit_post`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `count(1)`,'post' AS `uk_type`,`demo`.`audit_post`.`operated_user_name` AS `operated_user_name`,`demo`.`audit_post`.`time` AS `time` from `demo`.`audit_post` group by `xAxis`,`uk_type`,`demo`.`audit_post`.`operated_user_name`,`demo`.`audit_post`.`time` union all select date_format(from_unixtime((`demo`.`pwd_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'pwd_change' AS `uk_type`,`demo`.`pwd_change`.`operated_user_name` AS `operated_user_name`,`demo`.`pwd_change`.`time` AS `time` from `demo`.`pwd_change` group by `xAxis`,`uk_type`,`demo`.`pwd_change`.`operated_user_name`,`demo`.`pwd_change`.`time` union all select date_format(from_unixtime((`demo`.`account_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'account' AS `uk_type`,`demo`.`account_change`.`operated_user_name` AS `operated_user_name`,`demo`.`account_change`.`time` AS `time` from `demo`.`account_change` group by `xAxis`,`uk_type`,`demo`.`account_change`.`operated_user_name`,`demo`.`account_change`.`time` union all select date_format(from_unixtime((`demo`.`user_info_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'user_info_change' AS `uk_type`,`demo`.`user_info_change`.`operated_user_name` AS `operated_user_name`,`demo`.`user_info_change`.`time` AS `time` from `demo`.`user_info_change` group by `xAxis`,`uk_type`,`demo`.`user_info_change`.`operated_user_name`,`demo`.`user_info_change`.`time` union all select date_format(from_unixtime((`demo`.`audit_app_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'app_change' AS `uk_type`,`demo`.`audit_app_change`.`operated_user_name` AS `operated_user_name`,`demo`.`audit_app_change`.`time` AS `time` from `demo`.`audit_app_change` group by `xAxis`,`uk_type`,`demo`.`audit_app_change`.`operated_user_name`,`demo`.`audit_app_change`.`time`) `t`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
-- normal view definition
root@localhost[demo]> show create view audit_view_history\G
*************************** 1. row ***************************
View: audit_view_history
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `audit_view_history` AS select `login_record_history`.`auth_type` AS `auth_type`,`login_record_history`.`login_status` AS `login_status`,`login_record_history`.`device_type` AS `device_type`,`login_record_history`.`login_ip` AS `login_ip`,`login_record_history`.`city` AS `city`,date_format(`login_record_history`.`login_time`,'%e') AS `xAxis`,count(1) AS `yAxis`,`login_record_history`.`login_time` AS `login_time`,`login_record_history`.`username` AS `username` from `login_record_history` group by `xAxis`,`login_record_history`.`auth_type`,`login_record_history`.`login_status`,`login_record_history`.`device_type`,`login_record_history`.`login_ip`,`login_record_history`.`city`,`login_record_history`.`login_time`,`login_record_history`.`username`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)
How to repeat:
1. create database demo;
2. use demo
3. source demo.sql
4. mysqlsh -- util checkForServerUpgrade zhenxing@10.186.64.136:5732 --target-version=8.0.34 --output-format=JSON --config-path=/data/mysql/5732/my.cnf.5732
Suggested fix:
Fixed the logic of view detection
Description: We are planning to upgrade mysql from 5.7.32 to 8.0.34. We have executed mysqlshell upgrader checker which are showing below mysqlsh -- util checkForServerUpgrade zhenxing@10.186.64.136:5732 --target-version=8.0.34 --output-format=JSON --config-path=/data/mysql/5732/my.cnf.5732 --- output ... { "id": "checkTableOutput", "title": "Issues reported by 'check table x for upgrade' command", "status": "OK", "detectedProblems": [ { "level": "Notice", "dbObject": "demo.audit_view_account", "description": "No database selected" }, { "level": "Error", "dbObject": "demo.audit_view_account", "description": "Corrupt" } ] }, ... --- By comparing the view definitions in the database, it is found that the difference between the Corrupt view and the normal view is that the from statement of the Corrupt view has a table_schema prefix definition, while the normal view does not have a table_schema prefix -- Corrupt view definition root@localhost[demo]> show create view audit_view_account\G *************************** 1. row *************************** View: audit_view_account Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `audit_view_account` AS select `t`.`xAxis` AS `xAxis`,`t`.`total` AS `total`,`t`.`uk_type` AS `uk_type`,`t`.`operated_user_name` AS `operated_user_name`,`t`.`time` AS `time` from (select date_format(from_unixtime((`demo`.`platform_role`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'platform_role' AS `uk_type`,`demo`.`platform_role`.`operated_user_name` AS `operated_user_name`,`demo`.`platform_role`.`time` AS `time` from `demo`.`platform_role` group by `xAxis`,`uk_type`,`demo`.`platform_role`.`operated_user_name`,`demo`.`platform_role`.`time` union all select date_format(from_unixtime((`demo`.`dept`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'dept' AS `uk_type`,`demo`.`dept`.`operated_user_name` AS `operated_user_name`,`demo`.`dept`.`time` AS `time` from `demo`.`dept` group by `xAxis`,`uk_type`,`demo`.`dept`.`operated_user_name`,`demo`.`dept`.`time` union all select date_format(from_unixtime((`demo`.`audit_post`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `count(1)`,'post' AS `uk_type`,`demo`.`audit_post`.`operated_user_name` AS `operated_user_name`,`demo`.`audit_post`.`time` AS `time` from `demo`.`audit_post` group by `xAxis`,`uk_type`,`demo`.`audit_post`.`operated_user_name`,`demo`.`audit_post`.`time` union all select date_format(from_unixtime((`demo`.`pwd_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'pwd_change' AS `uk_type`,`demo`.`pwd_change`.`operated_user_name` AS `operated_user_name`,`demo`.`pwd_change`.`time` AS `time` from `demo`.`pwd_change` group by `xAxis`,`uk_type`,`demo`.`pwd_change`.`operated_user_name`,`demo`.`pwd_change`.`time` union all select date_format(from_unixtime((`demo`.`account_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'account' AS `uk_type`,`demo`.`account_change`.`operated_user_name` AS `operated_user_name`,`demo`.`account_change`.`time` AS `time` from `demo`.`account_change` group by `xAxis`,`uk_type`,`demo`.`account_change`.`operated_user_name`,`demo`.`account_change`.`time` union all select date_format(from_unixtime((`demo`.`user_info_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'user_info_change' AS `uk_type`,`demo`.`user_info_change`.`operated_user_name` AS `operated_user_name`,`demo`.`user_info_change`.`time` AS `time` from `demo`.`user_info_change` group by `xAxis`,`uk_type`,`demo`.`user_info_change`.`operated_user_name`,`demo`.`user_info_change`.`time` union all select date_format(from_unixtime((`demo`.`audit_app_change`.`time` / 1000)),'%k') AS `xAxis`,count(1) AS `total`,'app_change' AS `uk_type`,`demo`.`audit_app_change`.`operated_user_name` AS `operated_user_name`,`demo`.`audit_app_change`.`time` AS `time` from `demo`.`audit_app_change` group by `xAxis`,`uk_type`,`demo`.`audit_app_change`.`operated_user_name`,`demo`.`audit_app_change`.`time`) `t` character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci -- normal view definition root@localhost[demo]> show create view audit_view_history\G *************************** 1. row *************************** View: audit_view_history Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `audit_view_history` AS select `login_record_history`.`auth_type` AS `auth_type`,`login_record_history`.`login_status` AS `login_status`,`login_record_history`.`device_type` AS `device_type`,`login_record_history`.`login_ip` AS `login_ip`,`login_record_history`.`city` AS `city`,date_format(`login_record_history`.`login_time`,'%e') AS `xAxis`,count(1) AS `yAxis`,`login_record_history`.`login_time` AS `login_time`,`login_record_history`.`username` AS `username` from `login_record_history` group by `xAxis`,`login_record_history`.`auth_type`,`login_record_history`.`login_status`,`login_record_history`.`device_type`,`login_record_history`.`login_ip`,`login_record_history`.`city`,`login_record_history`.`login_time`,`login_record_history`.`username` character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci 1 row in set (0.00 sec) How to repeat: 1. create database demo; 2. use demo 3. source demo.sql 4. mysqlsh -- util checkForServerUpgrade zhenxing@10.186.64.136:5732 --target-version=8.0.34 --output-format=JSON --config-path=/data/mysql/5732/my.cnf.5732 Suggested fix: Fixed the logic of view detection