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

Better usage of ENUM fields in keys

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Pending Release
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.0.11
    • Fix Version/s: 3.0.12
    • Component/s: None
    • Labels:
      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

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                carlos.salguero Carlos Salguero
                Reviewer:
                Sveta Smirnova
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day
                  1d