Bug #87650 View with explicit column name > 64 is marked invalid when upgraded to 8.0

• Submitted: 4 Sep 2017 7:03
• Modified: 14 Nov 2017 18:04
• Reporter: Nisha Padmini Gopalakrishnan
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.1
• OS: Any
• CPU Architecture: Any

[4 Sep 2017 7:03] Nisha Padmini Gopalakrishnan

Description:
In MySQL-5.7:
============
Create a view with the view column name greater than
64 chars:

create table t1(i int);
CREATE OR REPLACE VIEW v1 (`x12345678901234567890123456789012345678901234567890123456789012322`) AS SELECT * from t1;

Start the 8.0 server using the 5.7 data dir where the view is created.
The upgrade succeeds but marks the view as invalid. A warning is reported
in the error log.
2017-08-31T06:48:38.341721Z 2 [Warning] [003823] Resolving
dependency for the view 'test.v1' failed. View is no more valid to use

In 5.7, views with column name greater than 64 chars could be created.
In trunk, the column name length in DD::COLUMNS is restricted to the
standard of 64 chars, hence the upgrade of the view does not succeed
and the view is marked as invalid. 

How to repeat:
In MySQL-5.7:
============
Create a view with the view column name greater than
64 chars:

create table t1(i int);
CREATE OR REPLACE VIEW v1 (`x12345678901234567890123456789012345678901234567890123456789012322`) AS SELECT * from t1;

Start the 8.0 server using the 5.7 data dir where the view is created.
The upgrade succeeds but marks the view as invalid. A warning is reported
in the error log.
2017-08-31T06:48:38.341721Z 2 [Warning] [003823] Resolving
dependency for the view 'test.v1' failed. View is no more valid to use

Suggested fix:
The 8.0 server should abort the upgrade when such views are detected, so
that it can be fixed by the user. Also there should be a way to perform
pre-check in 5.7, so that such views can be identified and fixed before
upgrade.

[14 Nov 2017 18:04] Paul DuBois

Posted by developer:
 
Fixed in 8.0.4, 9.0.0.

In MySQL 8.0, view column names are restricted to 64 characters. A
MySQL 5.7 view with longer column names was marked as invalid during
an upgrade to 8.0 was marked invalid. Now such views produce an error
during the upgrade and must be altered to have legal column names
before the upgrade will succeed.