Problems with strict Amavis when using strict SQL_MODE and DBD::MariaDB driver
Amavis seems to be trying to insert the wrong timestamp value when using $timestamp_fmt_mysql = 1;
with the DBD::MariaDB driver.
The error:
Jun 10 02:03:55 test15 amavis[2942874]: (2942874-01) (!)WARN save_info_preliminary: sql exec: err=1292, 22007, DBD::MariaDB::st execute failed: Incorrect datetime value: '20210610T080355Z' for column amavis.msgs.time_iso at row 1 at (eval 110) line 173.
Background:
- I'm using the DBD::MariaDB driver to work around issue #7.
- This issue only happens when MariaDB is running in strict mode (Strict mode is the default mode for any version of MariaDB after 10.2.4 like 10.3 and 10.5):
To set the default mode used on any version above 10.2.4:
/etc/mysql/my.cnf
[mysqld] sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
When STRICT_TRANS_TABLES is used MariaDB will no longer try and fix incorrect values before inserting them into the database: https://mariadb.com/kb/en/sql-mode/#strict_trans_tables