Description:
Currently, mysqlshell upgrade Checker fetches all tables from information_schema.tables using below query and execute CHECK TABLE .. FOR UPGRADE on all the tables.
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys');
In case it finds any issues, then it will report them as errors which need to be fixed before upgrade.
However, in case current version is 8.0 and above and DB instance contain broken VIEWS i.e., view definition refer to one or more columns/tables that no longer exists on the database, these can be reported as warnings instead of errors. Because they don't block the upgrade operation.
How to repeat:
For example, initialize 8.0.11 database and run below commands.
mysql> create database test;
Query OK, 1 row affected (0.06 sec)
mysql> use test
Database changed
mysql> create table temp(id int);
Query OK, 0 rows affected (0.12 sec)
mysql> create view test_vu as select * from temp;
Query OK, 0 rows affected (0.06 sec)
mysql> drop table temp;
Query OK, 0 rows affected (0.03 sec)
In addition, load employees database (sample data) and after loading the data drop dept_emp table which breaks the definition of two views inside the same schema.
https://dev.mysql.com/doc/employee/en/employees-installation.html
Now, run mysql upgrade utility with target version as 8.0.35 and we get errors.
[root@testbox ~]# mysqlsh8035 -- util checkForServerUpgrade root@/tmp%2Fmysql8011.sock --target-version=8.0.35 --output-format=TEXT
.
.
.
7) Issues reported by 'check table x for upgrade' command
Notice : Table (test.current_dept_emp) - Table 'test.dept_emp' doesn't exist
Notice : Table (test.current_dept_emp) - View 'test.current_dept_emp'
references invalid table(s) or column(s) or function(s) or definer/invoker of
view lack rights to use them
Error : Table (test.current_dept_emp) - Corrupt
Notice : Table (test.dept_emp_latest_date) - Table 'test.dept_emp' doesn't
exist
Notice : Table (test.dept_emp_latest_date) - View
'test.dept_emp_latest_date' references invalid table(s) or column(s) or
function(s) or definer/invoker of view lack rights to use them
Error : Table (test.dept_emp_latest_date) - Corrupt
Notice : Table (test.test_vu) - Table 'test.temp' doesn't exist
Notice : Table (test.test_vu) - View 'test.test_vu' references invalid
table(s) or column(s) or function(s) or definer/invoker of view lack rights
to use them
Error : Table (test.test_vu) - Corrupt
.
.
Errors: 3
Warnings: 0
Notices: 6
ERROR: 3 errors were found. Please correct these issues before upgrading to avoid compatibility issues.
[root@testbox ~]#
Finally, upgrade mysql database and the upgrade operation completes as expected
Suggested fix:
Consider to divide the CHECK TABLE operation into two parts. One on tables and other on views. Consider generating warning when any issues on VIEWS and error for issues on TABLES.
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys') and TABLE_TYPE IN ('BASE TABLE','SYSTEM VIEW'); -- Report ERROR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys') and TABLE_TYPE='VIEW'; -- Report Warning
NOTE: we may need to add source version check as well (8.0 and above)
Description: Currently, mysqlshell upgrade Checker fetches all tables from information_schema.tables using below query and execute CHECK TABLE .. FOR UPGRADE on all the tables. SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys'); In case it finds any issues, then it will report them as errors which need to be fixed before upgrade. However, in case current version is 8.0 and above and DB instance contain broken VIEWS i.e., view definition refer to one or more columns/tables that no longer exists on the database, these can be reported as warnings instead of errors. Because they don't block the upgrade operation. How to repeat: For example, initialize 8.0.11 database and run below commands. mysql> create database test; Query OK, 1 row affected (0.06 sec) mysql> use test Database changed mysql> create table temp(id int); Query OK, 0 rows affected (0.12 sec) mysql> create view test_vu as select * from temp; Query OK, 0 rows affected (0.06 sec) mysql> drop table temp; Query OK, 0 rows affected (0.03 sec) In addition, load employees database (sample data) and after loading the data drop dept_emp table which breaks the definition of two views inside the same schema. https://dev.mysql.com/doc/employee/en/employees-installation.html Now, run mysql upgrade utility with target version as 8.0.35 and we get errors. [root@testbox ~]# mysqlsh8035 -- util checkForServerUpgrade root@/tmp%2Fmysql8011.sock --target-version=8.0.35 --output-format=TEXT . . . 7) Issues reported by 'check table x for upgrade' command Notice : Table (test.current_dept_emp) - Table 'test.dept_emp' doesn't exist Notice : Table (test.current_dept_emp) - View 'test.current_dept_emp' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Error : Table (test.current_dept_emp) - Corrupt Notice : Table (test.dept_emp_latest_date) - Table 'test.dept_emp' doesn't exist Notice : Table (test.dept_emp_latest_date) - View 'test.dept_emp_latest_date' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Error : Table (test.dept_emp_latest_date) - Corrupt Notice : Table (test.test_vu) - Table 'test.temp' doesn't exist Notice : Table (test.test_vu) - View 'test.test_vu' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Error : Table (test.test_vu) - Corrupt . . Errors: 3 Warnings: 0 Notices: 6 ERROR: 3 errors were found. Please correct these issues before upgrading to avoid compatibility issues. [root@testbox ~]# Finally, upgrade mysql database and the upgrade operation completes as expected Suggested fix: Consider to divide the CHECK TABLE operation into two parts. One on tables and other on views. Consider generating warning when any issues on VIEWS and error for issues on TABLES. SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys') and TABLE_TYPE IN ('BASE TABLE','SYSTEM VIEW'); -- Report ERROR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys') and TABLE_TYPE='VIEW'; -- Report Warning NOTE: we may need to add source version check as well (8.0 and above)