Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 365.4Kb
HTML Download (TGZ) - 287.5Kb
HTML Download (Zip) - 298.6Kb

MySQL Utilities 1.5 Manual  /  ...  /  How Do I Show All INSERT and UPDATE Queries That Failed?

3.6.3 How Do I Show All INSERT and UPDATE Queries That Failed?

Useful information can be recorded in the audit log files and also a considerable amount of it. However, how can someone easily filter this information and search for specific events, for instance in order to determine the possible cause of a problem.

For example, suppose that someone reported that some data changes are missing (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.


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            |


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 lower and upper case to specify the list of query types. For example, 'insert,Update' will produce the same result as using 'INSERT,UPDATE'. Of course the use of non-supported values will raise 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 will use standard SQL pattern matching (used by 'LIKE' comparison operator), unless the --regexp option is used to allow more powerful standard regular expressions (POSIX extended).

Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 365.4Kb
HTML Download (TGZ) - 287.5Kb
HTML Download (Zip) - 298.6Kb
User Comments
Sign Up Login You must be logged in to post a comment.