0

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)
2
  • The commandline tool? Or some other client?
    – Rick James
    Dec 12, 2017 at 16:26
  • no, not the client, but the server... something like MariaDB SQL_ERROR_LOG plugin or a builtin option if any... Dec 12, 2017 at 21:06

2 Answers 2

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.)

0

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

2
  • From what I know, log_warnings only log server administration warnings, not SQL query execution warnings... Dec 13, 2017 at 8:50
  • @NicolasPayart - then there is no log of the Warnings.
    – Rick James
    Dec 13, 2017 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.