#1526 Make export sql more friendly to side-by-side comparison

Artem K.

I frequently need to find differences between two versions of the same db in order to create a script for migration. The way I usually proceed is to create a structure only dump in phpmyadmin and compare the results using WinMerge or a similar tool. This works great to detect new tables and columns but can be improved for changes in indices and foreign key constraints.

The main problem with the currently generated dump is that each ADD INDEX, ADD CONSTRAINT statement is generated in the order it was added to the database (I think). This makes two identical structures show up as different in a file compare tool because indices may have been created in different sequence in each database. If all indices and constraints were sorted by name, the lines would appear in the same order and not be marked as different.

I do not know if the order of indices/constraints can be important in certain situations - I can't think of such scenarios. But if that might be the case, the sorting of keys/constraints in a dump can at least be triggered using a configuration option.


  • Madhura Jayaratne

    • assigned_to: Madhura Jayaratne
  • Madhura Jayaratne

    Export mechanism use SHOW CREATE TABLE query to get the structure and perform string manipulations to come up with the export. It seems like trying to order indexes and constraints in some order would complicate the already complex code there.

    Anyway, since this is a valid and useful feature request I am keeping this feature request open hoping that it will get implemented, probably with some refactoring on export related code.

  • Marc Delisle

    Marc Delisle - 2015-02-15
    • Priority: 5 --> Normal
  • Madhura Jayaratne

    • assigned_to: Madhura Jayaratne --> nobody
  • jeff

    jeff - 2015-03-12

    Another related difference is that some sql exports included all the keys including the primary key in the table creation expression. I've just upgraded to PMA 4.3.10 and I cannot compare any of the new exported sql files with any of the older sql files (of which I have many) because all the keys are in separate sql expressions.

    I've checked the libraries/plugins/export/ExportSql.class.php file, but not sure I want to be messing with that, if I don't have to. Besides if I do edit it, I won't be able to upgrade PMA...

    It would be nice to have a little more control over how the sql files are generated.

    Last edit: jeff 2015-03-12