What exactly does log_warnings=2 log?

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”. In 5.7, there is some new info added to the log_warnings definition, mostly because of the new 5.7 variable log_error_verbosity. Here is what is says about “log_warnings=2 (or higher)”: “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.