Bug #17764 trigger crashes myisam table
Submitted: 28 Feb 2006 10:43 Modified: 31 Mar 2006 7:22
Reporter: Nizamettin OZPOLAT Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18 OS:Linux (SuSE Linux 9.0 (i586) - Kernel 2)
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: corruption, myisam

[28 Feb 2006 10:43] Nizamettin OZPOLAT
Description:
As can be examined at the example when myisam tables name is used in stored procedure mysqlserver give error "Table 't2' is marked as crashed and should be repaired".  sp1 called by the trigger after insert on t2. When I change table type to innodb every thing is OK. sp1 sums b grouped by a on table t2 and assigns it to table t1 if t1.a=t2.a 

How to repeat:
CREATE TABLE `t1` (
  `a` int(11) default NULL,
  `b` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

insert into t1 (a,b) values(1,2),(2,3),(1,2),(2,3),(1,2);

CREATE TABLE `t2` (
  `a` int(11) default NULL,
  `c` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

drop procedure if exists sp1;
create procedure sp1(in v2 INTEGER)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  DECLARE total INTEGER;
  select sum(c) into total from t2 where a = v2 group by a;
  update t1 set t1.b = total where t1.a = v2;
END;

--drop trigger tr1;
CREATE TRIGGER tr1 AFTER INSERT
ON t2 FOR EACH ROW
  call sp1(NEW.a);

--ALTER TABLE t2 TYPE=inndb, COMMENT='';
insert into t2 (a,c) values
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),
(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3);
[28 Feb 2006 15:38] Jorge del Conde
Thanks for your bug report.

I was able to reproduce this under WinXP w/5.0.18

    -> (1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3),(1,2),(2,3
ERROR 1194 (HY000): Table 't2' is marked as crashed and should be repaired
mysql>
[18 Mar 2006 18:36] 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/3959
[28 Mar 2006 10:50] Konstantin Osipov
Thoroughly discussed with Monty and Dmitri and decided to take another approach to the problem: disable bulk insert completely in prelocked mode.
[29 Mar 2006 7:14] 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/4262
[29 Mar 2006 10:49] 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/4266
[29 Mar 2006 18:11] Konstantin Osipov
Pushed into 5.0.20, merged into 5.1.8
[31 Mar 2006 7:22] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.20 and 5.1.8 changelogs. Closed.
[7 Apr 2006 15:35] Konstantin Osipov
Bug#16074 has been marked as a duplicate of this bug.
[17 Apr 2006 21:21] Dmitry Lenev
Bug #17704 has been marked as yet another duplicate of this bug.
[19 Apr 2006 13:21] Dmitry Lenev
Bug #16021 is another duplicate of this bug.
[19 Apr 2006 15:14] 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/5151