[MXS-3158] Failover/switchover modifies event character set and collation Created: 2020-09-04  Updated: 2020-12-16  Resolved: 2020-12-14

Status: Closed
Project: MariaDB MaxScale
Component/s: mariadbmon
Affects Version/s: 2.4.11
Fix Version/s: 2.4.15, 2.5.7

Type: Bug Priority: Critical
Reporter: Ján Regeš Assignee: Esa Korhonen
Resolution: Fixed Votes: 0
Labels: None

Sprint: MXS-SPRINT-121

 Description   

Hi,

our database is completely in UTF8MB4. Commit below force "SET NAMES latin1" after connection to backend servers.

https://github.com/mariadb-corporation/MaxScale/commit/6c9249131ffe9feeea2874d0baff36d85d40c2f4

Due to this change, in case of failover/switchover are executed ALTER EVENT queries and MariaDB change charset of these events from utf8mb4 to latin1. After that, our events doesn't work due to Illegal mix of charset/collation errors.

CREATE/ALTER EVENT, unfortunately take charset & collation from client connection.

Possible proper way to handle this would be execute "SET NAMES %charset%; SET connection_collation %collation%".

Variable %charset% can be loaded from @@global.character_set_database or @@global.character_set_server.

Variable %collation% can be loaded from @@global.collation_database or select @@global.collation_server.

Or, this charset and collation would be changeable by some optional variables in maxscale.cnf?

We tried to add "SET NAMES utf8mb4; SET collation_connection = utf8mb_czech_ci;" into our promotion/demotion SQL files, but these commands are executed after ALTER EVENTS, so it's too late to handle that case.

 Comments   
Comment by markus makela [ 2020-10-12 ]

The code that executes the SET NAMES latin1 should store the charset in a session variable and switch back to it for the duration of the ALTER and CREATE events.

The following SQL can be used to store the original values:

set @old_character_set_connection=@@character_set_connection,
 
    @old_character_set_client=@@character_set_client,
 
    @old_character_set_results=@@character_set_results,
 
    @old_collation_connection=@@collation_connection;

The restoration is just the inverse of the same operation:

set session
 
    character_set_connection=@old_character_set_connection,
 
    character_set_client=@old_character_set_client,
 
    character_set_results=@old_character_set_results,
 
    collation_connection=@old_collation_connection;

Comment by Esa Korhonen [ 2020-12-09 ]

It seems the most correct thing to do is to look at "SHOW EVENTS" and change charset/collation for every event. In the meantime, you may want to disable the event handling for MaxScale (monitor setting "handle_events") and add the event handling manually to the promotion/demotion sql files.

Generated at Thu Feb 08 04:19:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.