How to store MySQL warnings in database?

Question

I'd like to store warnings caused by some SQL statements in the database. E.g. after

mysql> select 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------+
| Level | Code | Message       |
+-------+------+---------------+
| Error | 1365 | Division by 0 |
+-------+------+---------------+
1 row in set (0.00 sec)

I'd like to save what SHOW WARNINGS says to some persistent table, to be able to analyze it in future.

Let's assume I know when there is something to log - I can check if @@warning_count > 0.

Answer 1

You can accomplish this within a stored procedure by analyzing the diagnostics. For example, the following handler for warnings could be declared by the following

  DECLARE CONTINUE HANDLER FOR SQLWARNING 
  BEGIN 
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
         @errno = MYSQL_ERRNO, @message = MESSAGE_TEXT
    ;

    /* select the following into your debugging table */
    SELECT @errno, @sqlstate, @message;
  END;

More information about DIAGNOSTICS can be found here: https://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html