Bug #16021 Wrong index given to function in trigger

• Submitted: 27 Dec 2005 15:55
• Modified: 19 Apr 2006 13:20
• Reporter: Gleb Paharenko
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.17
• OS: Linux (Linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[27 Dec 2005 15:55] Gleb Paharenko

Description:
First reported at:
  http://lists.mysql.com/mysql/193224

Bulk insert on MyISAM tables produces 124 error, while with InnoDB everything is Ok.

How to repeat:
create table t(i int not null, j int not null, n numeric(15,2), primary key(i,j));
create table s(i int not null, n numeric(15,2), primary key(i));

delimiter //
create trigger t_ai after insert on t for each row begin
    declare sn numeric(15,2);
    select sum(n) into sn from t where i=new.i;
    replace into s values(new.i, sn);
end//
delimiter ;//

insert into t values 
   (1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00),
   (1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00),
   (1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00);
ERROR 1030 (HY000): Got error 124 from storage engine

mysql> truncate table t; truncate table s; alter table t engine=InnoDB; alter table s engine=InnoDb;
Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values     (1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00),    (1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00),    (1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00);
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

InnoDB works fine!

Suggested fix:
Fix this

[27 Dec 2005 16:38] Valeriy Kravchuk

Thank you for a problem report. Verified just as described on 5.0.19-BK (ChangeSet@1.2003, 2005-12-24 14:32:50+01:00).

[19 Apr 2006 13:20] Dmitry Lenev

Hi, Gleb!

I can't repeat this problem using the latest version of MySQL (5.0.21-bk). Investigation showed that that your problem is yet another manifestation of bug #17764 "trigger crashes myisam table" which was fixed in MySQL 5.0.20. So I am marking this bug as duplicate.

Since behavior in your case slightly differs from the behavior described in bug #17764 I will add test case based on your report to our test-suite.