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

Updating with invalid ENUM value seems to pick the last valid one

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • None
    • N/A
    • Server
    • None

    Description

      If I have a column like this:

      `col` ENUM('0','1','2','3','4','5','6') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0'
      

      And then I do:

      update table set col='7' WHERE id=1234;
      

      It appears the column gets updated with the value '6'.

      I'm very surprised (and only noticed this bug in my application now!!), because I thought the point of ENUMs was also to validate that the input is correct.

      I have these in my.cnf:

      sql_mode                        = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY"
      #old_mode                        = ""  # default: "UTF8_IS_UTF8MB3"
      innodb_strict_mode              = ON
      

      Is this the expected behavior?

      I thought ENUMs used to give an error if we try to update with an invalid value, in previous MariaDB versions! (like 10.5 or so)

      Thank you very much.


      UPDATE

      Actually, it does seem that if I put some completely incorrect value, like:

      update table set col='asdasd' WHERE id=1234;
      

      It does give me the expected error:

      #1265 - Data truncated for column 'ghosted' at row 1

      What I suspect is that by providing the value "7", and MariaDB realizes that "7" (as a string) isn't a valid value, it somehow converts it to (int), and then picks up the string value "6" because it's the 7th element in the ENUM list of valid values.

      Is this what is happening?

      And if so, is there a way I can force MariaDB to be 100% strict and not accept numeric ENUM updates like this, and therefore fail in these scenarios?

      Attachments

        Activity

          People

            serg Sergei Golubchik
            nunop Nuno
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.