MySQL Bugs: #20055: Trigger: Resetting one changed column resets all in a before-update trigger

Bug #20055	Trigger: Resetting one changed column resets all in a before-update trigger
Submitted:	24 May 2006 20:11	Modified:	7 May 2007 16:44
Reporter:	Tom Donaldson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.23-BK, 5.0.21-standard-log	OS:	Linux (Linux, Mac OS X 10.4.6)
Assigned to:	Damien Katz	CPU Architecture:	Any

Description:
In a before-update trigger I check changed values. If they are out of the desired range I set the new value to the old value (i.e., SET new.latitude=old.latitude). 

The problem I am running into is that resetting any changed column to its old value resets all columns. That is, resetting one column seems to prevent the row from being committed. 

Oh, and it is a quiet failure. There is no indication of error; no warnings.

The "How to repeat" field, below, contains a short script that illustrates my problem. It creates a very simple, stripped down table, creates a before-update trigger on the table, inserts a record, and attempts to update the record several times. It displays the contents of the table on each attempt. The trigger always modifies the comment field to indicate whether the changed latitude column (or attempted change to it) was good or bad. If the input latitude was bad, it resets the bad value to the previous value. When I run this script I never see any of the 'bad' comments; they are trashed when the latitude column is reset. 

How to repeat:
-- MySQL version: 5.0.21-standard-log 
-- Running on Mac OS X 10.4.6, dual 1.8MHz G5 
-- 
-- Problem: Resetting one changed column in an updated record from a 
-- before-update trigger causes all changes for the row to be lost. 

CREATE TABLE `location` ( 
`location_id` int(10) unsigned NOT NULL auto_increment, 
`latitude` double default NULL, 
`comments` varchar(256) default NULL, 
PRIMARY KEY (`location_id`) 
); 

DELIMITER // 
CREATE TRIGGER location_update_before BEFORE UPDATE on location 
FOR EACH ROW 
BEGIN 
-- If less than -180.0 degrees or greater than 180.0 degrees 
-- comment on badness and reset to old value. Else, comment 
-- on goodness and let the new value get stored. 
CASE 
WHEN new.latitude < -180.0 THEN 
SET new.comments = 'Bad, too low'; 
SET new.latitude = old.latitude; 

WHEN new.latitude > 180.0 THEN 
SET new.comments = 'Bad, too high'; 
SET new.latitude = old.latitude; 

WHEN new.latitude < 0.0 THEN 
SET new.comments = 'Good, negative'; 

WHEN new.latitude = 0.0 THEN 
SET new.comments = 'Good, zero'; 

ELSE 
SET new.comments = 'Good, positive'; 
END CASE; 
END 
// 
DELIMITER ; 

-- Note that none of the 'Bad ...' comments will ever show up in 
-- the table because whenever the new.latitude is set to the old.latitude 
-- by the trigger, MySQL trashes ALL changes to the record. 

INSERT INTO location SET latitude=-999.999, comments='Bogus starting record'; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=-888.888 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=-180.0 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=-777.777 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=0.0 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=777.777 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=180.0 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=888.888 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=117.119 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1; 

UPDATE location SET latitude=999.999 WHERE location_id=1; 
SELECT * FROM location WHERE location_id=1;

Thank you for a problem report. Verified just as described with 5.0.23-BK on Linux:

mysql> CREATE TABLE `location` (
    -> `location_id` int(10) unsigned NOT NULL auto_increment,
    -> `latitude` double default NULL,
    -> `comments` varchar(256) default NULL,
    -> PRIMARY KEY (`location_id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter //
mysql> CREATE TRIGGER location_update_before BEFORE UPDATE on location
    -> FOR EACH ROW
    -> BEGIN
    -> -- If less than -180.0 degrees or greater than 180.0 degrees
    -> -- comment on badness and reset to old value. Else, comment
    -> -- on goodness and let the new value get stored.
    -> CASE
    -> WHEN new.latitude < -180.0 THEN
    -> SET new.comments = 'Bad, too low';
    -> SET new.latitude = old.latitude;
    ->
    -> WHEN new.latitude > 180.0 THEN
    -> SET new.comments = 'Bad, too high';
    -> SET new.latitude = old.latitude;
    ->
    -> WHEN new.latitude < 0.0 THEN
    -> SET new.comments = 'Good, negative';
    ->
    -> WHEN new.latitude = 0.0 THEN
    -> SET new.comments = 'Good, zero';
    ->
    -> ELSE
    -> SET new.comments = 'Good, positive';
    -> END CASE;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> INSERT INTO location SET latitude=-999.999, comments='Bogus starting rec
ord';
SEQuery OK, 1 row affected (0.01 sec)

Lmysql> SELECT * FROM location WHERE location_id=1;
+-------------+----------+-----------------------+
| location_id | latitude | comments              |
+-------------+----------+-----------------------+
|           1 | -999.999 | Bogus starting record |
+-------------+----------+-----------------------+
1 row in set (0.00 sec)

mysql> UPDATE location SET latitude=-888.888 WHERE location_id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM location WHERE location_id=1;
+-------------+----------+-----------------------+
| location_id | latitude | comments              |
+-------------+----------+-----------------------+
|           1 | -999.999 | Bogus starting record |
+-------------+----------+-----------------------+
1 row in set (0.00 sec)

mysql> UPDATE location SET latitude=-180.0 WHERE location_id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM location WHERE location_id=1;
+-------------+----------+----------------+
| location_id | latitude | comments       |
+-------------+----------+----------------+
|           1 |     -180 | Good, negative |
+-------------+----------+----------------+
1 row in set (0.00 sec)

mysql> UPDATE location SET latitude=-777.777 WHERE location_id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM location WHERE location_id=1;
+-------------+----------+----------------+
| location_id | latitude | comments       |
+-------------+----------+----------------+
|           1 |     -180 | Good, negative |
+-------------+----------+----------------+
1 row in set (0.00 sec)

mysql> UPDATE location SET latitude=999.999 WHERE location_id=1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM location WHERE location_id=1;
+-------------+----------+----------------+
| location_id | latitude | comments       |
+-------------+----------+----------------+
|           1 |     -180 | Good, negative |
+-------------+----------+----------------+
1 row in set (0.01 sec)

mysql> UPDATE location SET latitude=10.10 WHERE location_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM location WHERE location_id=1;
+-------------+----------+----------------+
| location_id | latitude | comments       |
+-------------+----------+----------------+
|           1 |     10.1 | Good, positive |
+-------------+----------+----------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23    |
+-----------+
1 row in set (0.01 sec)

I've been unable to reproduce this bug. This may be a different symptom for a bug that's already been fixed.