How can I get MySQL 5.5 to log warnings to one of the log files?

Question

I have found various things that say that you can log warnings to the MySQL error log, but I have not been able to actually make it happen.

I do have the error log working, and MySQL prints stuff to it on startup and shutdown and occasionally at other times, but if I e.g. SELECT CAST('123' AS DATE); and then SHOW WARNINGS; I can see the warning, but it does not show up in any logs.

I've also tried enabling the general log and the slow query log, but these don't show the warnings either.

I've tried with log_warnings = 1 and log_warnings = 2, but still no warnings are logged.

What am I doing wrong?

mysql> show variables like '%error%';
+--------------------+--------------------------+
| Variable_name      | Value                    |
+--------------------+--------------------------+
| error_count        | 0                        |
| log_error          | /var/log/mysql/mysql.err |
| max_connect_errors | 10                       |
| max_error_count    | 1024                     |
| slave_skip_errors  | OFF                      |
+--------------------+--------------------------+

mysql> show variables like '%warn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 1     |
| sql_warnings  | OFF   |
| warning_count | 0     |
+---------------+-------+
3 rows in set (0.06 sec)

mysql> show variables like '%log%';
+-----------------------------------------+-------------------------------+
| Variable_name                           | Value                         |
+-----------------------------------------+-------------------------------+
...
| general_log                             | ON                            |
| general_log_file                        | /var/log/mysql/general.log    |
...
| log                                     | ON                            |
...
| log_error                               | /var/log/mysql/mysql.err      |
| log_output                              | FILE                          |
| log_queries_not_using_indexes           | ON                            |
...
| log_warnings                            | 1                             |
...
| slow_query_log                          | ON                            |
| slow_query_log_file                     | /var/log/mysql/mysql-slow.log |
...
+-----------------------------------------+-------------------------------+

Edit:

mysql> show global status like 'Aborted%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 24    |
| Aborted_connects | 15    |
+------------------+-------+
2 rows in set (0.08 sec)

Edit:

Clarification: I do get [Warning] Aborted connection 1 to db... and [Warning] Access denied for user... messages logged, but not the warnings that you can see via SHOW WARNINGS after e.g. inserting something or running LOAD DATA INFILE... which is what I'm looking for.

Answer 1

Check your count of Aborted Clients and Aborted Connects

SHOW GLOBAL STATUS LIKE 'Aborted%';

If these numbers surpassed 1024, this is probably why warnings past that number won't post in any log.

Try adding this to /etc/my.cnf

warning_count=65535
max_error_count=65535
sql_warnings=1

Restart mysql (warning_count is a readonly variable)

Click here for the description of the following variables:
warning_count
max_error_count
sql_warnings

Answer 2

I've come to the conclusion that this is not possible. MySQL does not log these warnings to the error log. Only things like e.g. authentication failures, aborted connections, etc.