Bug #119760 loadDump with deferTableIndexes=all loads views before indexes

• Submitted: 23 Jan 0:23
• Modified: 22 Apr 21:43
• Reporter: Claire Cheong
• Status: Closed
• Category: Shell Dump & Load
• Severity: S3 (Non-critical)
• Version: 8.4.6
• OS: Ubuntu
• CPU Architecture: Any

[23 Jan 0:23] Claire Cheong

Description:
When using mysqlsh to loadDump with deferTableIndexes=all, views are created along with tables, before indexes are created. However, this means that views that rely on indexes fail to be created.

How to repeat:
Create a schema with a table that has a secondary index, then a view that explicitly uses that secondary index:

CREATE TABLE `t1` (
    `a1` int NOT NULL AUTO_INCREMENT,
    `a2` int,
    PRIMARY KEY (`a1`),
    KEY `a2` (`a2`)
);

CREATE VIEW v1 AS
SELECT a1, a2
FROM t1 USE INDEX (a2);

Dump the schema with mysqlsh dumpSchemas.

Loading the schema with mysqlsh loadDump without deferTableIndexes=all succeeds.

Loading the schema with mysqlsh loadDump with deferTableIndexes=all fails with:

ERROR: Key 'a2' doesn't exist in table 't1'

Suggested fix:
Even with deferTableIndexes=all, indexes should be created before views.

[9 Feb 19:16] Alfredo Kojima

Thank you for the bug report.

[22 Apr 21:43] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Shell 9.7.0 release notes:
 
Fixed a dump loading utility ordering issue with deferTableIndexes=all where views
        were created before deferred secondary indexes, causing view creation to
        fail for views that explicitly depend on those indexes.