Previously, when diff'ing tables, Go La Tengo did not consider secondary index order to be significant; usually it has no functional impact in MySQL/InnoDB. However, this caused two rare problems:
Although applying the generated set of ALTERs to the "from" side of a diff would indeed yield a table that is functionally equivalent to the "to" side of the diff, the canonical table definition (e.g. output of SHOW CREATE TABLE) would still differ, due to the order of index clauses being different.
Index order has a functional impact in one rare edge case: InnoDB tables with no primary key, but multiple unique keys with all non-nullable columns. In this case, InnoDB will use the first such unique index for the clustered index.
This PR makes Table.Diff() able to maintain index ordering. By default, however, the generated TableDiff.Statement() will automatically strip out index operations that simply reorder indexes -- preserving the previous behavior, as this generates a faster-to-execute set of ALTERs by avoiding needlessly recreated indexes whose definitions have not changed. The index reordering operations are only used in two cases: whenever index order is actually relevant (2nd bullet above), or whenever the new option StatementModifiers.StrictIndexOrder is true.
Previously, when diff'ing tables, Go La Tengo did not consider secondary index order to be significant; usually it has no functional impact in MySQL/InnoDB. However, this caused two rare problems:
SHOW CREATE TABLE
) would still differ, due to the order of index clauses being different.This PR makes Table.Diff() able to maintain index ordering. By default, however, the generated TableDiff.Statement() will automatically strip out index operations that simply reorder indexes -- preserving the previous behavior, as this generates a faster-to-execute set of ALTERs by avoiding needlessly recreated indexes whose definitions have not changed. The index reordering operations are only used in two cases: whenever index order is actually relevant (2nd bullet above), or whenever the new option StatementModifiers.StrictIndexOrder is true.