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

mysql_tzinfo_to_sql does not work in strict mode

Details

    Description

      When the sql-mode contains STRICT_ALL_TABLES or STRICT_TRANS_TABLES mysql_tzinfo_to_sql is not properly working because of the following statement

      ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;

      with the following error message
      ERROR 1105 (HY000) at line 146984: ORDER BY ignored as there is a user-defined clustered index in the table 'time_zone_transition'

      This could be releated to MDEV-18778 (but I am not quite sure)

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18778 mysql_tzinfo_to_sql does not work correctly in MariaDB Galera

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21209 mysql_tzinfo_to_sql's Galera checks do not work

          • Major - Major loss of function.
          • Closed

          Activity

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

            How did you end up with InnoDB as an engine for the system tables? It isn't supposed to be created like that, did you change the engine manually?

            elenst Elena Stepanova added a comment - How did you end up with InnoDB as an engine for the system tables? It isn't supposed to be created like that, did you change the engine manually?
            kstiehl Kevin Stiehl added a comment -

            No I did not change anything about the system tables. It also does not work in the official Docker Images as soon as you add STRICT_ALL_TABLES or STRICT_TRANS_TABLES to your config.

            In the Docker entrypoint.sh it fails at this line

            https://github.com/docker-library/mariadb/blob/9ddcc2b4b0422a83dafc52e5574474dafc2fa9ca/10.1/docker-entrypoint.sh#L198

            And as far as I can tell there is nothing in this entrypoint script that changes anything about the system tables.

            kstiehl Kevin Stiehl added a comment - No I did not change anything about the system tables. It also does not work in the official Docker Images as soon as you add STRICT_ALL_TABLES or STRICT_TRANS_TABLES to your config. In the Docker entrypoint.sh it fails at this line https://github.com/docker-library/mariadb/blob/9ddcc2b4b0422a83dafc52e5574474dafc2fa9ca/10.1/docker-entrypoint.sh#L198 And as far as I can tell there is nothing in this entrypoint script that changes anything about the system tables.
            elenst Elena Stepanova added a comment -

            Sorry, my bad, you are right. As a part of the bugfix that you mentioned, the tool itself temporarily changes the table engine to InnoDB.

            It is supposed to be done only for installations with wsrep_on enabled, but due to the bug MDEV-21209 it happens on non-galera installations as well. It has been fixed, but the last released version of 10.1 doesn't have the fix yet.

            However, the failure described here still affects 10.1 with wsrep_on and with strict mode enabled.

            10.2+ are not affected, because there even in strict mode the problematic ALTER only causes a warning, not an error.

            elenst Elena Stepanova added a comment - Sorry, my bad, you are right. As a part of the bugfix that you mentioned, the tool itself temporarily changes the table engine to InnoDB. It is supposed to be done only for installations with wsrep_on enabled, but due to the bug MDEV-21209 it happens on non-galera installations as well. It has been fixed, but the last released version of 10.1 doesn't have the fix yet. However, the failure described here still affects 10.1 with wsrep_on and with strict mode enabled . 10.2+ are not affected, because there even in strict mode the problematic ALTER only causes a warning, not an error.
            kstiehl Kevin Stiehl added a comment - - edited

            Thnx for your quick reply.
            This explanation was quite helpful

            I will just update MariaDB then.

            kstiehl Kevin Stiehl added a comment - - edited Thnx for your quick reply. This explanation was quite helpful I will just update MariaDB then.
            danblack Daniel Black added a comment -

            10.1 eol

            danblack Daniel Black added a comment - 10.1 eol
            danblack Daniel Black added a comment -

            Removed warning in the 10.6 version of mariadb-tzinfo-to-sql with a bunch of other changes. As its a warning from 10.2 I haven't done a backport.

            danblack Daniel Black added a comment - Removed warning in the 10.6 version of mariadb-tzinfo-to-sql with a bunch of other changes. As its a warning from 10.2 I haven't done a backport.

            People

              danblack Daniel Black
              kstiehl Kevin Stiehl
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.