Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1572

Better usage of ENUM fields in keys


    • New Feature
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 3.0.11
    • 3.0.12
    • None
    • None


      When a key has an ENUM field, TableNibbler needs to wrap the ENUM field in a CONCAT stamentent because of the way MySQL handle those fields.

      According to the documentation: http://dev.mysql.com/doc/refman/5.6/en/enum.html#enum-sorting

      "ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.

      To prevent unexpected results when using the ORDER BY clause on an ENUM column, use one of these techniques:

      Specify the ENUM list in alphabetic order.

      Make sure that the column is sorted lexically rather than by index number by coding ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col)."

      Since in the example the features column was defined as `feature` enum('FOO','BAR','BAT','BAZ','CAT','DOG','DERP','HERP','VANILLA','CHOCOLATE','MINT') (non-alphabetical order), pt-osc's Nibbler fails to calculate the boundaries for each chunk.

      See -PT-272- for reference.
      Adding a CONCAT, makes programs like pt-table-checksum slower (See PT-1567)

      To avoid that behavior, we need these changes:

      1.  Make TableParser to detect if the ENUM items in a table definition are already sorted or not.
      2. If they are not sorted:
        1. If --force-concat-enums was provided, continue using a CONCAT to wrap ENUM fields
        2. If -force-concat-enums was NOT provided, die with a message explaining that there are ENUM fields with unsorted items and that will cause a noticeable slowdown, and tell the user he should specify -force-concat-enums
      3. If they are sorted, continue as usual NOT using CONCAT to wrap the ENUM fields




        Issue Links



              carlos.salguero Carlos Salguero (Inactive)
              carlos.salguero Carlos Salguero (Inactive)
              Sveta Smirnova Sveta Smirnova
              0 Vote for this issue
              3 Start watching this issue



                Smart Checklist