Bug #48663 STRICT_TRANS_TABLE causing an error when 0 rows found in an UPDATE trigger
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.