[MDEV-21991] mysqldump will create a dump with indexes ordered by "type and creation time" instead of "type and name" Created: 2020-03-20  Updated: 2020-03-27  Resolved: 2020-03-26

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Massimiliano Cuttini Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: Compatibility
Environment:

CentOS 7



 Description   

We use:

mysqldump --compact --no-data

to extract all the tables definitions.
We then strip the AUTO_INCREMENTS with SED:

sed \'s/ AUTO_INCREMENT=[0-9]*//g\'

The output is then hashed in order to check table version across different servers.
We realize that few time the comparison doesn't match because INDEX are dumped by: "indextype ASC, creation ASC" instead of "indextype ASC, indexname ASC".
So 2 identical table structure will be different just because a developer added an index before another. This is weird.

Is it possible to force the dump of the indexes by type and then by alphabetical order?



 Comments   
Comment by Sergei Golubchik [ 2020-03-26 ]

No, mysqldump dumps the table definition as it was created by the user, it does not reorder anything.

If you want to compare table structure while ignoring certain details, you'd better query information_schema tables.
For example

select * from information_schema.statistics order by table_schema,table_name,index_name,seq_in_index

will give you all indexes in all tables sorted alphabetically. You can hash that afterwards as you like.

Comment by Massimiliano Cuttini [ 2020-03-26 ]

It's completly different to parse the result of the information_schema and having a dump of the table definitions. I can use the dump also to create new applications instead of I cannot reuse information schema for this directly (i should parse the result).

I know this is not a bug.
But I think this could be at least a trivial enhancement for future releases.

I think that there is not any kind of advantage having INDEX ordered by creation time.
While there is some kind of advantage having ordered for type and alphabeticaly.

Table are dumped in alphabetical order, not in creation order and there is a reason for this.
TABLES are subjected to maintanaince, you can add, remove, restore, modifiy.
If they were reordered by creation time you'll get a mess. Instead having them ordered alphabetically allow administrator to take a quick look if everything is as it should be.
For the INDEXES is the same. They are deleted, restored, modified. However for the index they are sortered depending on creation time. This is ugly and unseemly.

Generated at Thu Feb 08 09:11:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.