Bug #88100 Schema Transfer Wizard fails on Views of Views
Submitted: 15 Oct 2017 8:44 Modified: 18 Jun 2018 3:11
Reporter: David Webb Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench: Migration Severity:S1 (Critical)
Version:6.3.9 OS:Any
Assigned to: CPU Architecture:Any

[15 Oct 2017 8:44] David Webb
I am migrating from MySQL 5.6.37 to 5.7.19. I've got both instances running. The Schema Transfer Wizard (STW) works fine on a simple schema, but it throws an error if the schema involves Views which select from Views.

The logged error is: "Table '<view name>' doesn't exist."

That's because it assumes that the First view is selecting from a table rather than a view. The views are installed in some sequence, so if you are lucky then the first view might reference a view which has been transferred, but otherwise you get the error.

How to repeat:
Create a schema with a table.
Create 2 views, with one selecting from the other.
Attempt to transfer it.
If it fails, you have reproduced the error.
If it works, then swap the definitions of the views (because they may have been transferred in the other sequence). Then try the transfer again.

Suggested fix:
MySQL Administrator (from the old GUI Tools) can restore backup files with views which reference views. It appears to do so by creating each view as a table (with empty data) first, then creating the views, then dropping the fake tables.

So I suggest that the Schema Transfer Wizard should adopt the same approach.

Meanwhile, I have to resort to restoring the data from a dump.
[16 Oct 2017 11:44] Chiranjeevi Battula
Hello David,

Thank you for the bug report.

[18 Jun 2018 3:11] David Webb
Is there any progress on this?