Bug #114274 Broken Views can be considered as warnings in mysqlshell upgradechecker utility
Submitted: 8 Mar 2:09 Modified: 11 Mar 8:46
Reporter: Chelluru Vidyadhar Email Updates:
Status: Verified Impact on me:
None 
Category:Shell Upgrade Checker Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[8 Mar 2:09] Chelluru Vidyadhar
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)
[11 Mar 8:46] MySQL Verification Team
Hello Chelluru,

Thank you for the enhancement request!!

regards,
Umesh