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

sql/event_db_repository.cc does not check for all sql_mode values

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.4.14, 10.4(EOL), 10.5
    • 10.5
    • Events, Upgrades
    • None

    Description

      mysql.event table check fails during mysqld start after upgrading 10.2 to 10.4.
      The error message is:

      [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_V...
      2021-01-09 9:28:26 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

      I believe the reason for the error is that MDEV-16991 has added new enum value TIME_ROUND_FRACTIONAL to the sql_mode column and mysql_system_tables_fix.sql script alters the table during mysql_update. But the corresponding value has not been added to the event_table_fields struct (in sql/event_db_repository.cc), and thus the Event_db_repository::check_system_tables returns back the error.

      The version we use is 10.4.13 but I checked the latest 10.6 branch and the issue seems to still be still there.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment - - edited

          The check doesn't fail when there are extra elements in the set, only when there are missing ones.
          Please note that the error is issued upon server startup, before mysql_upgrade is executed.
          If it re-occurs persistently, probably something has gone wrong with mysql_upgrade.

          That said, the omission in the code should be fixed anyway. Thanks for the report.

          I'll keep it in "need_feedback" until we figure out what's happened to mysql_upgrade. After that we'll need to change the summary appropriately.

          elenst Elena Stepanova added a comment - - edited The check doesn't fail when there are extra elements in the set, only when there are missing ones. Please note that the error is issued upon server startup, before mysql_upgrade is executed. If it re-occurs persistently, probably something has gone wrong with mysql_upgrade . That said, the omission in the code should be fixed anyway. Thanks for the report. I'll keep it in "need_feedback" until we figure out what's happened to mysql_upgrade. After that we'll need to change the summary appropriately.
          alurie Andrei Lurie added a comment - - edited

          Thank you for your reply, Elena.
          Your explanation clarifies it.
          The column elements at the time of error were:
          set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
          which explains why the check failed (the event_table_fields struct has more elements).
          This error does not occur on subsequent restarts - only on first start (so it makes sense that mysql_upgrade fixes it for subsequent starts).
          Sorry for the false alarm - I did not realize mysql_upgrade was not yet ran when I was looking at the mysqld server log.
          We can close this as "user error" (or whatever the appropriate disposition is).
          Thank you.

          alurie Andrei Lurie added a comment - - edited Thank you for your reply, Elena. Your explanation clarifies it. The column elements at the time of error were: set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') which explains why the check failed (the event_table_fields struct has more elements). This error does not occur on subsequent restarts - only on first start (so it makes sense that mysql_upgrade fixes it for subsequent starts). Sorry for the false alarm - I did not realize mysql_upgrade was not yet ran when I was looking at the mysqld server log. We can close this as "user error" (or whatever the appropriate disposition is). Thank you.
          elenst Elena Stepanova added a comment -

          Thanks.
          I've adjusted the summary to focus on the missing expectation in the code.

          elenst Elena Stepanova added a comment - Thanks. I've adjusted the summary to focus on the missing expectation in the code.
          danblack Daniel Black added a comment -

          The error in this field is also common in 5.7 -> MariaDB upgrades (https://www.tusacentral.net/joomla/index.php/mysql-blogs/232-who-is-drop-in-replacement-of).

          Given the set (almost?) always is a superset, is it possible to keep the Event scheduler active on errors in the sql_mode so they keep running for whenever the user gets to noticing that mysql_upgrade is needed?

          danblack Daniel Black added a comment - The error in this field is also common in 5.7 -> MariaDB upgrades ( https://www.tusacentral.net/joomla/index.php/mysql-blogs/232-who-is-drop-in-replacement-of ). Given the set (almost?) always is a superset, is it possible to keep the Event scheduler active on errors in the sql_mode so they keep running for whenever the user gets to noticing that mysql_upgrade is needed?

          People

            sanja Oleksandr Byelkin
            alurie Andrei Lurie
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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