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

• Submitted: 23 Jan 0:23
• Reporter: Claire Cheong
• Status: Open
• 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.