Have you ever wondered exactly does log_warnings=2 log? Well, I have, and finally decided to check the code. 🙂 (The manual used to mention setting this to 2 for diagnosing some connection-related problems, but I didn’t run into that comment in my most recent search.)
Basically, in recent 5.6 source code, we find “log_warnings > 1” in 7 files. In 5.5 source, it is only in 5 files. Here are the 7 files in 5.6:
filesort.cc (line 460) log_event.cc (lines 4873, 10020, 11209) rpl_master.cc (line 912) rpl_rli_pdb.cc (lines 1538, 1596, 1735, 2066) rpl_slave.cc (lines 3585, 4684, 5405, 5436) sql_acl.cc (lines 9591, 9613, 11351) sql_connect.cc (line 791)
Long story short, the main (most common) ones are when a filesort fails (filesort.cc) or a failed login occurs (sql_acl.cc). Then there are some replication-specific instances where it logs extra info, such as master/slave/binlog info, “ignored” errors, and some summary stats for multi-threaded slave worker threads (rpl_master.cc, rpl_rli_pdb.cc, rpl_slave.cc) (All of the extra replication logging was new to 5.6, fwiw.). If it fails to close a connection that it should close, it logs some info (sql_connect.cc). And lastly, another new option in 5.6 is password expiration support. So if that is enabled and expired for the current user (and log_warnings > 1), then a note is logged to the error log as well (sql_acl.cc line 11351).
Long story long, … I debated posting the relevant code snippets, but after seeing how boring that was going to be, I’ve omitted them. 😉
Fwiw, the max log_warnings in 5.6 on 32-bit is 4294967295 and for 64-bit is 18446744073709547520. However, at least for now anyway, it seems there is no difference between setting this to 2 or 18446744073709547520, as the only current references/actions are for “log_warnings > 1”.
“Setting log_warnings=2 (or higher) is equivalent to log_error_verbosity=3 (errors, warnings, notes), and the server sets log_warnings to 2 if a larger value is specified.”
Hope this helps.
With the Version 10.1.17 the range was expanded.
“Log some not critical warnings to the general log file.” “Value can be between 0 and 11. Higher values mean more verbosity”
14 occurrences with “log_warnings > 1”
7 occurences with “log_warnings > 2”
3 occurences with “log_warnings >= 9”
1 occurences with “log_warnings >= 10”
1 occurences with “log_warnings >= 11 ”
>= 9 (“Initializing built-in plugins”,”Initializing plugins specified on the command line”,”Initializing installed plugins”)
>= 10 / 11 (Log all errors not handled by thd->handle_error() to my_message_sql())
Comment was added to following post by MariaDB Support Engineer, Richard Stracke, 10/26/2016: