Logging SQL warnings in MariaDB

Question

Is there a way to log SQL warnings on a MariaDB server (10.2)?

For example, I would like to be able to record (in a log file) this type of warning (strict mode is disabled in this example):

MariaDB [mysql]> select now() = 1;
+-----------+
| now() = 1 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

MariaDB [mysql]> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect datetime value: '1' |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

Answer 1

If you're OK with using strict mode so that warnings are converted to errors, then you can use the MariaDB SQL Error Log Plugin to log these into a special SQL error log file:

INSTALL SONAME 'sql_errlog'; 
SHOW VARIABLES LIKE 'sql_mode';

gives:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

So we have strict mode (STRICT_TRANS_TABLES), which means this will produce an error instead of a warning (title is varchar(5)):

INSERT INTO t1 (id, title) VALUES (1, 'foobar');

Yes, we get:

ERROR 1406 (22001): Data too long for column 'title' at row 1

... and then that error can be found in the SQL error log at /var/lib/mysql/sql_errors.log.

(Note that the example in the question will not produce an error even in strict mode as it doesn't affect a table. As it's not an error it won't be logged to the SQL error log.)

Answer 2

Starting with 5.7.2, use log_error_verbosity

Before that, see https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_log_warnings -- which probably does not do what you wanted.

For MariaDB, use log_warnings=2. See log_warnings in here