Bug #18437 Wrong values inserted with a before update trigger
Submitted: 22 Mar 2006 20:46 Modified: 13 Jul 2006 20:22
Reporter: Horst Hunger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0, 5.1 OS:Linux (Linux 9.3)
Assigned to: Dmitry Lenev CPU Architecture:Any

[22 Mar 2006 20:46] Horst Hunger
Description:
The trigger within the attached script for mysql inserts with a myisam engine the following values in a table:

select * from db_test.t1_i order by i144
+------+-------+------------+----------------------------------+
| i120 | i136  | i144       | i163                             |
+------+-------+------------+----------------------------------+
| I    | 00222 | 0000023456 | 1.050000000000000000000000000000 |
| I    | 00333 | 0000033456 | 2.050000000000000000000000000000 |
| I    | 00444 | 0000053456 | 4.050000000000000000000000000000 |
+------+-------+------------+----------------------------------+
3 rows in set (0.01 sec)

But with a ndb engine:

+------+-------+------------+----------------------------------+
| i120 | i136  | i144       | i163                             |
+------+-------+------------+----------------------------------+
| I    | 00444 | 0000053456 | 4.050000000000000000000000000000 |
| I    | 00444 | 0000053456 | 4.050000000000000000000000000000 |
| I    | 00444 | 0000053456 | 4.050000000000000000000000000000 |
+------+-------+------------+----------------------------------+
3 rows in set (0.13 sec)

The trigger:

Create trigger trg2 BEFORE UPDATE on tb3 for each row
       BEGIN
        insert into db_test.t1_i
                values (new.f120, new.f136, new.f144, new.f163);
       END;

The table and the test for the trigger can be found in the attached file.

How to repeat:
run attached file with a cluster.
[28 Mar 2006 16:14] Horst Hunger
set to P2 due to wrong data.
[29 Mar 2006 9:53] Konstantin Osipov
Why is version 5.1, not 5.0?
We will investigate this, but I have a sneaking suspicion that we're hitting the storage engine limit here.
[4 Apr 2006 17:02] Horst Hunger
I run the attached program with 5.0 and the error occured, too.
[6 Jun 2006 13:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7325
[20 Jun 2006 20:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7966
[6 Jul 2006 9:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8816
[13 Jul 2006 16:08] Konstantin Osipov
Pushed into 5.1.12
[13 Jul 2006 16:08] Konstantin Osipov
Pushed into 5.0 tree currently tagged 5.0.25
[13 Jul 2006 20:22] Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.

For NDB and possibly InnoDB tables, a BEFORE UPDATE trigger could
insert incorrect values.