Description:
When running with no STRICT_TRANS_TABLE. An UPDATE trigger produces WARNINGS for "Statement is not safe to log in statement format." For the rows the trigger updates plus a WARNING for the UPDATE command itself.
When running with STRICT_TRANS_TABLE, on an UPDATE trigger, if there are 0 rows found/updated then the UPDATE command produces an ERROR instead of a WARNING.
mysql> set sql_mode='';
Query OK, 0 rows affected (0.01 sec)
mysql> update arttechh set status='X';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> update arttechh set status='Y';
Query OK, 3 rows affected, 4 warnings (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. |
| Warning | 1592 | Statement is not safe to log in statement format. |
| Warning | 1592 | Statement is not safe to log in statement format. |
| Warning | 1592 | Statement is not safe to log in statement format. |
+---------+------+---------------------------------------------------+
4 rows in set (0.01 sec)
mysql> set sql_mode='ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> update arttechh set status='X';
Query OK, 3 rows affected, 4 warnings (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. |
| Warning | 1592 | Statement is not safe to log in statement format. |
| Warning | 1592 | Statement is not safe to log in statement format. |
| Warning | 1592 | Statement is not safe to log in statement format. |
+---------+------+---------------------------------------------------+
4 rows in set (0.00 sec)
mysql> update arttechh set status='X';
ERROR 1592 (HY000): Statement is not safe to log in statement format.
mysql>
How to repeat:
I'll include an attached file with the trigger and table creation statements.
Suggested fix:
0 rows updated in the trigger should produce a WARNING like it does while not in strict mode and not be escalated to an ERROR.
