Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.56
-
None
-
Red Hat Entreprise Linux Workstation 7.5
Description
For each of my databases the following query is used to get and store a MD5 information identifying the consistency of the database format (more than 150 tables):
SELECT MD5(GROUP_CONCAT(TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,IS_NULLABLE)) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'mydatabase' ORDER BY TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION
|
This worked for years on RHEL5/6 with MySQL.
Today by migrating on RHEL7/MariaDB I encounter problems with this query.
The result of the query is different following the name of the database.
As soon as it contains an underscore ("_") character the result is systematically false.
| Database name | MD5 |
|---|---|
| CAF07 | 6915dc76f78d285f493aaeaacbc3aefb |
| A | 6915dc76f78d285f493aaeaacbc3aefb |
| CAF07_RBCTB_TST | 460c779c0643d557fe40c24ab203630b |
| CAF07_RBCTB | e51c6dd63770733d1931c70c44b44782 |
When displaying the result of the inner concatenation we can notice that the order to the concatenated strings is not correct when the name of the database contains an underscore:
(see also attached log files)
A flavor of this can already be noticed by performing a simple SELECT query on the COLUMNS table:
The type of the field which causes problem is an ENUM with a high number of elements:
`Type` enum('EBDefault','EBnoRegenerativeBrake','EBnoEddyCurrentBrake','EBnoMagneticShoeBrake','EBnoEPBrake','EBnoRegenerativeBrakeEddyCurrentBrake','EBnoRegenerativeBrakeEddyCurrentBrakeMagneticShoeBrake','EBnoRegenerativeBrakeEddyCurrentBrakeMagneticShoeBrakeEPBrake','EBnoRegenerativeBrakeEddyCurrentBrakeEPBrake','EBnoRegenerativeBrakeMagneticShoeBrake','EBnoRegenerativeBrakeMagneticShoeBrakeEPBrake','EBnoRegenerativeBrakeEPBrake','EBnoEddyCurrentBrakeMagneticShoeBrake','EBnoEddyCurrentBrakeMagneticShoeBrakeEPBrake','EBnoEddyCurrentBrakeEPBrake','EBnoMagneticShoeBrakeEPBrake','SBDefault','SBnoRegenerativeBrake','SBnoEddyCurrentBrake','SBnoEPBrake','SBnoRegenerativeBrakeEddyCurrentBrake','SBnoRegenerativeBrakeEddyCurrentBrakeEPBrake','SBnoRegenerativeBrakeEPBrake','SBnoEddyCurrentBrakeEPBrake','NormalSBFreightG_Default','NormalSBFreightG_A_SB01','NormalSBFreightG_A_SB12','NormalSB_Default','NormalSB_A_SB01','NormalSB_A_SB12') NOT NULL default 'SBDefault',
|
When reducing the number of elements of the ENUM (to 2) the problem does no more appear.
It can also be noticed that the results are different following the user connecting to the database server:
- correct with the root user
- incorrect with the standard user defined for the application.
The problem seems similar to MDEV-9226.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
Dear Alice,
Thanks for your answer.
I'm quite embarrassed: you're completely right!
When using your query having the ORDER BY within the GROUP_CONCAT the result is stable and not depending on the name of the table.
However, it is really strange that the existing query never caused any problem through the years.
Thanks again!
Best regards,
Sébastien
Please explain why you consider it is a bug.
I can reproduce unordered results inside GROUP_CONCAT with large enum inside the table, but they are not promised to be ordered,
as ORDER BY belongs to the whole query.
Maybe the query should be like
SELECT MD5(GROUP_CONCAT(TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,IS_NULLABLE ORDER BY TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION)) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'mydatabase' ;