Description:
Customer request:
They have a slightly chaotic application mix and want to find all SQL statements that cause an error or a warning, so that they may fix them.
Applications are so chaotic that they cannot rely on the applications logging or otherwise reporting such cases.
They are looking for a way to have the MySQL server collect all statements that had a syntax error or failed for other reasons like missing privilege, unknown table or column, violating the SQL mode ("only full group by") or other similar reasons.
I had hoped that such statements would not be contained in the slow query log, so that the difference of the general query log and the slow query log (configured to log each and every statement) would be the erroneous ones.
But that approach failed, because the slow query log also contains the erroneous statements (tested using version 5.7.7).
In fact, it was the other direction: The general log does not contain statements with syntax errors but the slow query log does.
Both logs, however, contain statements that refer to non-existing tables or columns.
I did not check for privilege or SQL mode issues.
So they are asking for a log generated by the server, to catch all such erroneous statements.
How to repeat:
I verified that the slow query log includes the erroneous statements by this setup:
#
# general log: log everything, to file
#
general_log = 1
general_log_file = the-gen.log
log_output = FILE
log_timestamps = SYSTEM
sql_log_off = 0
#
# slow query log: also log everything, also to file
#
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 0
log_slow_admin_statements = 1
long_query_time = 0.0
min_examined_row_limit = 0
slow_query_log = 1
slow_query_log_file = the-slow.log
Suggested fix:
In spite of its name, the error log would be the wrong place, because syntax error messages there would hide the really critical issues requiring DBA attention.
The general query log probably cannot be used, because errors and warnings will be determined too late, when that entry is already written.
The slow query log could be used, if each entry would also contain the error or warning number caused by the statement.
OTOH, this would mix syntax and performance issues, probably no good idea.
The cleanest solution would be a separate log for erroneous statements.
To make it better usable, this logging should be settable by session, so that
users/developers can attack aplications one by one.
© 2024, Oracle Corporation and/or its affiliates
Description: Customer request: They have a slightly chaotic application mix and want to find all SQL statements that cause an error or a warning, so that they may fix them. Applications are so chaotic that they cannot rely on the applications logging or otherwise reporting such cases. They are looking for a way to have the MySQL server collect all statements that had a syntax error or failed for other reasons like missing privilege, unknown table or column, violating the SQL mode ("only full group by") or other similar reasons. I had hoped that such statements would not be contained in the slow query log, so that the difference of the general query log and the slow query log (configured to log each and every statement) would be the erroneous ones. But that approach failed, because the slow query log also contains the erroneous statements (tested using version 5.7.7). In fact, it was the other direction: The general log does not contain statements with syntax errors but the slow query log does. Both logs, however, contain statements that refer to non-existing tables or columns. I did not check for privilege or SQL mode issues. So they are asking for a log generated by the server, to catch all such erroneous statements. How to repeat: I verified that the slow query log includes the erroneous statements by this setup: # # general log: log everything, to file # general_log = 1 general_log_file = the-gen.log log_output = FILE log_timestamps = SYSTEM sql_log_off = 0 # # slow query log: also log everything, also to file # log_queries_not_using_indexes = 1 log_throttle_queries_not_using_indexes = 0 log_slow_admin_statements = 1 long_query_time = 0.0 min_examined_row_limit = 0 slow_query_log = 1 slow_query_log_file = the-slow.log Suggested fix: In spite of its name, the error log would be the wrong place, because syntax error messages there would hide the really critical issues requiring DBA attention. The general query log probably cannot be used, because errors and warnings will be determined too late, when that entry is already written. The slow query log could be used, if each entry would also contain the error or warning number caused by the statement. OTOH, this would mix syntax and performance issues, probably no good idea. The cleanest solution would be a separate log for erroneous statements. To make it better usable, this logging should be settable by session, so that users/developers can attack aplications one by one.