[MDEV-24555] sql/event_db_repository.cc does not check for all sql_mode values Created: 2021-01-09  Updated: 2021-03-11

Status: Confirmed
Project: MariaDB Server
Component/s: Events, Upgrades
Affects Version/s: 10.4.14, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Minor
Reporter: Andrei Lurie Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: 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.

 Comments   
Comment by Elena Stepanova [ 2021-01-09 ]

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.

Comment by Andrei Lurie [ 2021-01-12 ]

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.

Comment by Elena Stepanova [ 2021-01-12 ]

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

Comment by Daniel Black [ 2021-03-11 ]

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?

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