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

Better usage of ENUM fields in keys

Details

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

    Description

      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

       

       

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Smart Checklist