Bug #42940 Enhancements to --log-warnings=2
Submitted: 17 Feb 2009 17:52 Modified: 19 May 2013 18:59
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:6.0+ OS:Any
Assigned to: CPU Architecture:Any

[17 Feb 2009 17:52] Morgan Tocker 
Description:
The MySQL server error log requires increased levels of verbosity (similar to how other software packages have notices, warnings and fatals).

I am requesting the following features:

* When --log-warnings=2 is enabled, log all statements the server receives that cause warnings or syntax errors.  This will help DBAs identify applications that ignore warnings (many do).

* When --log-warnings=3 is enabled (or --log-warnings=2 and --some-other-setting), log all statements which return empty results[1].  This will help identify potential mistakes in queries, or redundant queries that are sent to the server.

[1] Or alternatively, logs all statements based on primary-key lookup that return empty results.

How to repeat:
See above.
[17 Feb 2009 18:18] Valeriy Kravchuk 
Thank you for a reasonable feature request. Note that in many cases you can use MySQL Proxy to find problematic queries of these kinds.
[19 May 2013 18:59] Morgan Tocker 
This can probably be made obsolete.  Performance_schema + ps_helper (http://www.markleith.co.uk/ps_helper/) offers this:

mysql> select * from statements_with_errors_or_warnings;
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+
| query                                                             | exec_count | errors | error_pct | warnings | warning_pct | digest                           |
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+
| DROP FUNCTION IF EXISTS `format_bytes`                            |          1 |      0 |    0.0000 |        1 |    100.0000 | fee09c12d226fe8442c0ea9be6bb28a3 |
| DROP FUNCTION IF EXISTS `format_time`                             |          1 |      0 |    0.0000 |        1 |    100.0000 | 1b8f10c549c3ee339d2ec4ffdf28f294 |
| DROP FUNCTION IF EXISTS `format_path`                             |          1 |      0 |    0.0000 |        1 |    100.0000 | 354fc0b75f9395524796d5619b960b54 |
| DROP FUNCTION IF EXISTS `extract_schema_from_file_name`           |          1 |      0 |    0.0000 |        1 |    100.0000 | 5339e1dc10c9a5738284260c3f391d4b |
| DROP FUNCTION IF EXISTS `extract_table_from_file_name`            |          1 |      0 |    0.0000 |        1 |    100.0000 | 12b07a6be03b7893afe205f28cc5a096 |
| DROP FUNCTION IF EXISTS `format_statement`                        |          1 |      0 |    0.0000 |        1 |    100.0000 | 1772b19ebb52a471adb3c0f5ed8463f0 |
| DROP VIEW IF EXISTS `latest_file_io`                              |          1 |      0 |    0.0000 |        1 |    100.0000 | 7666d15b649f05e77914f2c1bf57c555 |
| DROP VIEW IF EXISTS `top_global_consumers_by_avg_latency`         |          1 |      0 |    0.0000 |        1 |    100.0000 | 829672169770a34a3a4d3dd5705177e3 |
| DROP VIEW IF EXISTS `top_global_consumers_by_total_latency`       |          1 |      0 |    0.0000 |        1 |    100.0000 | 36c579b6744751b79383a56862b709b4 |
| DROP VIEW IF EXISTS `top_global_io_consumers_by_latency`          |          1 |      0 |    0.0000 |        1 |    100.0000 | 725f3eb6f61c5dd800d62f1bb20c6c25 |
| DROP VIEW IF EXISTS `top_global_io_consumers_by_bytes_usage`      |          1 |      0 |    0.0000 |        1 |    100.0000 | a66cda4a2641cd4f5845e85b98747e8d |
| DROP VIEW IF EXISTS `top_io_by_file`                              |          1 |      0 |    0.0000 |        1 |    100.0000 | 4dfe606c8977915d517ff21f3c882014 |
| DROP VIEW IF EXISTS `top_io_by_thread`                            |          1 |      0 |    0.0000 |        1 |    100.0000 | 36eea8214f670657dd46d20c41d9bfd9 |
| DROP VIEW IF EXISTS `statement_analysis`                          |          1 |      0 |    0.0000 |        1 |    100.0000 | d7e0d30bcc4417c4374ae79bb574b91d |
| DROP VIEW IF EXISTS `digest_avg_latency_by_avg_us`                |          1 |      0 |    0.0000 |        1 |    100.0000 | ef5aca9ee3f0933da6d7b2f5453e160a |
| DROP VIEW IF EXISTS `digest_95th_percentile_by_avg_us`            |          1 |      0 |    0.0000 |        1 |    100.0000 | 6494de9e7aee5443e64244b2ccdc8c0c |
| DROP VIEW IF EXISTS `statement ... _runtimes_in_95th_percentile`  |          1 |      0 |    0.0000 |        1 |    100.0000 | ca12037f4d7431cd6736c9f7b7bc2492 |
| DROP VIEW IF EXISTS `statements_with_temp_tables`                 |          1 |      0 |    0.0000 |        1 |    100.0000 | d568984bc904bf1e450629cda6389dcc |
| DROP VIEW IF EXISTS `statements_with_sorting`                     |          1 |      0 |    0.0000 |        1 |    100.0000 | 5863cd33f5f22850cd558b775c98b465 |
| DROP VIEW IF EXISTS `statements_with_full_table_scans`            |          1 |      0 |    0.0000 |        1 |    100.0000 | 3d04d7091ef5738fdb5b23e6132338da |
| DROP VIEW IF EXISTS `statements_with_errors_or_warnings`          |          1 |      0 |    0.0000 |        1 |    100.0000 | 8d88ce3a5e52afa689db8cd174784766 |
| DROP VIEW IF EXISTS `schema_table_statistics`                     |          1 |      0 |    0.0000 |        1 |    100.0000 | c0c0ce825fbcf21336ffb4d2def7c504 |
| DROP VIEW IF EXISTS `schema_index_statistics`                     |          1 |      0 |    0.0000 |        1 |    100.0000 | 9b6c5789c3210de68a5fea9b27db2f8b |
| DROP VIEW IF EXISTS `schema_unused_indexes`                       |          1 |      0 |    0.0000 |        1 |    100.0000 | bc447e0e1903bb7f5a699de72cd22612 |
| DROP VIEW IF EXISTS `schema_tables_with_full_table_scans`         |          1 |      0 |    0.0000 |        1 |    100.0000 | ced43c7308629a410b5b57fc194219c7 |
| DROP PROCEDURE IF EXISTS `only_enable`                            |          1 |      0 |    0.0000 |        1 |    100.0000 | 1365b93e85bfd30e2cc662fcb64e21d9 |
| DROP PROCEDURE IF EXISTS `disable_current_thread`                 |          1 |      0 |    0.0000 |        1 |    100.0000 | 32a101013ff499a44ec6233e57b10df0 |
| DROP PROCEDURE IF EXISTS `enable_current_thread`                  |          1 |      0 |    0.0000 |        1 |    100.0000 | 24823b35ddfb5fcfac027d0db8a45a40 |
| DROP PROCEDURE IF EXISTS `disable_background_threads`             |          1 |      0 |    0.0000 |        1 |    100.0000 | 17229baad28182e13031af4eb4860add |
| DROP PROCEDURE IF EXISTS `enable_background_threads`              |          1 |      0 |    0.0000 |        1 |    100.0000 | 7d04f3b1e6fb4cdac4f7e9dcd0ee4c9e |
| DROP PROCEDURE IF EXISTS `currently_enabled`                      |          1 |      0 |    0.0000 |        1 |    100.0000 | a9a3a927c97a4f287ed5758c111352b8 |
| DROP PROCEDURE IF EXISTS `dump_thread_stack`                      |          1 |      0 |    0.0000 |        1 |    100.0000 | bd755bdc9c1ababdd4752bbad20658b2 |
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+
32 rows in set (0.49 sec)