[MDEV-21208] mysql_tzinfo_to_sql does not work in strict mode Created: 2019-12-03  Updated: 2022-04-21  Resolved: 2022-04-21

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.1.43
Fix Version/s: 10.6.8

Type: Bug Priority: Major
Reporter: Kevin Stiehl Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-18778 mysql_tzinfo_to_sql does not work cor... Closed
Relates
relates to MDEV-21209 mysql_tzinfo_to_sql's Galera checks d... Closed

 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)

 Comments   
Comment by Elena Stepanova [ 2020-01-13 ]

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?

Comment by Kevin Stiehl [ 2020-01-13 ]

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.

Comment by Elena Stepanova [ 2020-01-13 ]

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.

Comment by Kevin Stiehl [ 2020-01-14 ]

Thnx for your quick reply.
This explanation was quite helpful

I will just update MariaDB then.

Comment by Daniel Black [ 2022-04-19 ]

10.1 eol

Comment by Daniel Black [ 2022-04-21 ]

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.

Generated at Thu Feb 08 09:05:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.