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.