[MDEV-28263] mariadb-tzinfo-to-sql binlog, embedded, and performance fixes Created: 2022-04-08  Updated: 2023-04-18  Resolved: 2022-04-23

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.4.24
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1

Type: Bug Priority: Major
Reporter: Daniel Black 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
is caused by MDEV-23326 aria TRANSACTIONAL=1 significantly sl... Closed
Relates
relates to MDBF-389 provide timezone data files Closed
relates to MDEV-28782 mariadb-tzinfo-to-sql to work in boot... Closed
relates to MDEV-30854 mariadb-tzinfo-to-sql creates sql_mod... Closed

 Description   

MDEV-18778 (10.2) broke using the output in embedded server mode (and no-one noticed) because the SQL generated required the delimiter parsing in the client.

A mtr test mysql_tzinfo_to_sql_symlink.test to work with embedded mode

 
--exec $MYSQL_TZINFO_TO_SQL $MYSQLTEST_VARDIR/zoneinfo > $MYSQL_TMP_DIR/tz.sql
 
--source $MYSQL_TMP_DIR/tz.sql

MDEV-23326 (10.4) didn't do a LOCK TABLES in the non-skip_write_binlog branch of the code and should have.

The --skip-write-binlog option from MDEV-18778 that doesn't always skip-write-binlog is just horribly confusing. As bnestere said, what breaks? "explicitly providing --skip-write-binlog, but actually wants mysql-tzinfo-to-sql to be binlogged".

Lastly, we could make the SQL generated preserve the session SQL_LOG_BIN state.

There's also a 10.6 case with wsrep_mode=REPLICA_ARIA where there isn't a need to change to InnoDB for replicating, but its a fairly minor case.

Implementation

Using user variables and EXECUTE IMMEDIATE prevents the need to use delimiter or a stored procedure.

draft implementation (against 10.6) (wsrep_mode=REPLICA_ARIA could be removed and the rest is 10.4 friendly (where MDEV-23326 was added).

Some test cases from a previous MDEV-23326 draft may be useful.

A 10.3 fix by moving SQL_LOG_BIN=? out of the WSREP_ON=ON condition.

 Comments   
Comment by Daniel Black [ 2022-04-19 ]

https://github.com/MariaDB/server/pull/2099 for the 10.6 side because that's when EXECUTE IMMEDIATE became available to LOCK TABLES.

I'm still to prepare an earlier similar fix.

Comment by Brandon Nesterenko [ 2022-04-19 ]

Reviewed the 10.6 PR

Comment by Daniel Black [ 2022-04-20 ]

Updated PR according to review.

One more still to come for the earlier MariaDB versions.

Comment by Daniel Black [ 2022-04-22 ]

10.2 version on bb-10.2-danielblack-MDEV-28263mysql-tzinfo-to-sql as a very minimal making SQL_LOG_BIN=0 unconditional under -skip-write-binlog.

Comment by Brandon Nesterenko [ 2022-04-23 ]

The 10.2 patch looks good - I left a question on GH.

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