MySQL Bugs: #101944: Upgrade to MySQL Server 8.0 from 5.6 fails with 'point' columns

Bug #101944	Upgrade to MySQL Server 8.0 from 5.6 fails with 'point' columns
Submitted:	9 Dec 2020 21:50	Modified:	19 Feb 2021 16:34
Reporter:	Erik Kratzenberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Creating a basic table with a 'point' column under MySQL 5.6 will ultimately cause an upgrade from 5.7 to 8.0 to fail with data dictionary errors:

2020-12-09T21:19:27.881812Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.22) starting as process 29246
2020-12-09T21:19:27.932937Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2020-12-09T21:19:27.933086Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-09T21:19:31.029812Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-09T21:19:38.146906Z 2 [ERROR] [MY-012064] [InnoDB] Column datatype mismatch for col: testcol
2020-12-09T21:19:38.147251Z 2 [ERROR] [MY-012070] [InnoDB] Column testcol for table: `testdb`.`testtable` mismatches with InnoDB Dictionary
2020-12-09T21:19:38.147685Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for testdb.testtable
2020-12-09T21:19:40.685020Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2020-12-09T21:19:40.685819Z 0 [ERROR] [MY-010119] [Server] Aborting

How to repeat:
1. Install MySQL 5.6.50

2. Create Table with at least one 'point' column:

   CREATE TABLE testtable ( testcol point DEFAULT NULL ) ENGINE=InnoDB

3. Upgrade to MySQL 5.7.32

4. Run 'mysql_upgrade' (No issues reported)

5. Run checkForServerUpgrade() via MySQL Shell (no errors reported)

6. Upgrade to MySQL 8.0.22

7. MySQL fails to start with above errors

Suggested fix:
A work-around by dumping/importing the table in question between 5.7 and 8.0 seems to work.

Hello Erik Kratzenberg,

Thank you for the report and feedback.

regards,
Umesh

Upgrade log

Attachment: 101944_5.6.50_5.7.32_8.0.22.log (application/octet-stream, text), 10.72 KiB.

Posted by developer:
 
Added changelog entry for MySQL Shell 8.0.24:

MySQL Shell's upgrade checker utility util.checkForServerUpgrade() now checks for spatial data columns that were originally created in MySQL 5.6. The underlying data type for such columns in MySQL 5.6 does not match their underlying data type in MySQL 8.0, so upgrade of the table is prohibited, and it must be recreated.