Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17198

Wrong order when selecting in INFORMATION_SCHEMA.COLUMNS for fields with large ENUMs

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 5.5.56
    • Fix Version/s: N/A
    • Component/s: N/A
    • Labels:
      None
    • Environment:
      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

        1. CAF07_RBCTB.log
          39 kB
          Sébastien Zinsius
        2. CAF07.log
          39 kB
          Sébastien Zinsius
        3. diff.png
          109 kB
          Sébastien Zinsius
        4. picture327-2.png
          19 kB
          Sébastien Zinsius

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              szinsius Sébastien Zinsius
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration