3.6.3 How can you find the INSERT and UPDATE queries that failed in the audit log?

Over time, the audit log can contain a lot of useful information. However, how filtering this information and searching for specific events, for instance in order to determine the possible cause of a problem, can be very tedious if done manually.

For example, suppose that someone reported that some data changes are missing (and you suspect some INSERT or UPDATE queries failed) and you want to determine what might be the cause of those transaction failures. All queries are recorded to the audit log file, so you just need to get retrieve all queries of a given type that failed (with a MySQL error) and analyze them.

This can be achieved using common 'grep' command line tools, but likely involves the use of very complex regular expression to filter the desired data. Fortunately, the mysqlauditgrep utility allows to perform this kind of task in a much easier and simple way taking advantage of the knowledge of the structure and semantics of the audit log files.

Objectives

The goal is display all INSERT and UPDATE queries that failed (independently of error) from the current audit log file.

It is assumed that the audit.log file exists and is located in the directory /MySQL/SERVER/data/. The below example show how easy it is to perform the desired search with the mysqlauditgrep utility.

Example Execution

shell> mysqlauditgrep --query-type=INSERT,UPDATE --status=1-9999 /MySQL/SERVER/data/audit.log
+--------+---------------------+-------+-------------------------------------------------------+---------------+
| STATUS | TIMESTAMP           | NAME  | SQLTEXT                                               | CONNECTION_ID |
+--------+---------------------+-------+-------------------------------------------------------+---------------+
| 1046   | 2013-08-01T18:20:46 | Query | INSERT INTO tbl_not_exist (a,b,c) VALUES(1,2,3)       | 37            |
| 1146   | 2013-08-01T18:21:03 | Query | INSERT INTO mysql.tbl_not_exist (a,b,c) VALUES(1,2,3) | 37            |
| 1054   | 2013-08-01T18:23:10 | Query | INSERT INTO test.t1 (a,b,not_col) VALUES(1,2,3)       | 37            |
| 1146   | 2013-08-01T18:26:14 | Query | UPDATE tbl_not_exist SET a = 1                        | 37            |
| 1054   | 2013-08-01T18:26:53 | Query | UPDATE test.t1 SET not_col = 1                        | 37            |
+--------+---------------------+-------+-------------------------------------------------------+---------------+

Discussion

As expected, the use of the mysqlauditgrep utility requires the specification of the target audit log file to search and a few options corresponding to the needed search criteria. In this case, the --query-type option was used to restrict the displayed results to specific types of queries (i.e., only INSERT and UPDATE), and the --status option was used to specify the considered MySQL error codes (i.e., all ranging from 1 to 9999).

The --query-type option allows the specification of a comma separated list of different SQL statements. Apart from INSERT and UPDATE the list of supported values for this option also includes: CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE, SELECT, DELETE, COMMIT, SHOW, SET, CALL, PREPARE, EXECUTE, DEALLOCATE

The --status option accepts a comma-separated list of non-negative integers (corresponding to MySQL error codes) or intervals marked with a dash. For example: 1051,1100-1199,1146. In this particular case, the range value 1-9999 was used to include all MySQL error codes and display all unsuccessful commands. To retrieve only successful command (no errors) simply use the value 0 for the --status option.

Permissions Required

The user must have permissions to read the audit log on disk.

Tips and Tricks

The value specified for the --query-type option are case insensitive, therefore you can mix lowercase and uppercase to specify the list of query types. For example, 'insert,Update' produces the same result as using 'INSERT,UPDATE'. Of course the use of non-supported values raises an appropriate error.

Many other options and search criteria are provided by the mysqlauditgrep utility, check them in order to use the more appropriate one to meet your needs. Note that the utility provides the --pattern option to search entries in the audit log file using regular expressions, like common grep tools. By default, this option uses standard SQL pattern matching (used by 'LIKE' comparison operator), unless the --regexp option is used to allow more powerful standard regular expressions (POSIX extended).