Bug #17138 Partitions: prepared statements become unknown
Submitted: 5 Feb 2006 17:56 Modified: 4 Jul 2006 5:40
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.7-beta-debug OS:Linux (SUSE 10.0)
Assigned to: Mikael Ronström CPU Architecture:Any

[5 Feb 2006 17:56] Peter Gulutzan
Description:
I prepare and execute random prepared statements
containing "ALTER TABLE ... PARTITION .." inside a
stored procedure, in a loop. After a random number
of iterations of the loop, I get a message that the
prepared statement handler is 'unknown'.

How to repeat:
mysql> delimiter //
mysql> CREATE TABLE `txo` (
    ->   `s1` int(11) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (s1) (PARTITION p0 VALUES IN (0) ENGINE = MyISAM, PARTITION p1 VALUES IN (1) ENGINE = MyISAM, PARTITION p2 VALUES IN (2) ENGINE = MyISAM, PARTITION p3 VALUES IN (3) ENGINE = MyISAM, PARTITION p4 VALUES IN (4) ENGINE = MyISAM, PARTITION p5 VALUES IN (5) ENGINE = MyISAM, PARTITION p6 VALUES IN (6) ENGINE = MyISAM, PARTITION p7 VALUES IN (7) ENGINE = MyISAM, PARTITION p8 VALUES IN (8) ENGINE = MyISAM, PARTITION p9 VALUES IN (9) ENGINE = MyISAM, PARTITION p10 VALUES IN (10) ENGINE = MyISAM)//
Query OK, 0 rows affected (0.02 sec)

mysql> create procedure po () begin declare v int default 0; while v < 1000 do set @v1 = round(rand()*10); insert into txo values (@v1); set v = v + 1; set @v2 = concat('alter table txo analyze partition p',@v1); prepare stmt1 from @v2; execute stmt1; begin declare continue handler for sqlexception begin end; update txo set s1 = s1 + 1 where s1 = round(rand()*10); end; set @v3 = concat('alter table txo rebuild partition p',round(rand()*10)); prepare stmt2 from @v3; execute stmt2; end while; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call po()//
ERROR 1243 (HY000): Unknown prepared statement handler (stmt2) given to EXECUTE
[5 Feb 2006 18:05] MySQL Verification Team
mysql> call po()//
ERROR 1243 (HY000): Unknown prepared statement handler (stmt2) given to EXECUTE
[11 Apr 2006 16:31] Mikael Ronström
The problem is obviously related to the update that reads a record and tries to
update it and by so doing changes to a value not covered by any partition.
This causes for some reason the prepared statement within the stored procedure
to fail.

create table t1 (a int)
partition by list (a)
(partition p0 values in (0));

insert into t1 values (0);
delimiter //;

create procedure po ()
begin
begin
  declare continue handler for sqlexception begin end;
  update t1 set a = 1 where a = 0;
end;
prepare stmt1 from 'alter table t1';
execute stmt1;
end//

call po()//

delimiter ;//
drop table t1;
[12 Apr 2006 13:03] Mikael Ronström
The reason for the behaviour is that the updates causes a fatal error. However due to
bug #15192 the fatal error doesn't lead to abortion of the stored procedure. It does
however cause the execution of prepare statement to fail and thus there is no
prepared statement to execute. So essentially this bug is a duplicate of bug #15192.

However there is an issue also in that UPDATE IGNORE should actually not treat this
type of failure as fatal error. This is currently not handled properly. So as part of this
bug I will fix this erroneus behaviour
[21 Jun 2006 16:09] Michael Widenius
Review done. Still some small cleanups to be done
[28 Jun 2006 15:10] Michael Widenius
Patch approved, after fixing small issues found in last review
[1 Jul 2006 12:51] Mikael Ronström
Patch is now pushed to the kt team tree in 5.1
[3 Jul 2006 22:01] Mikael Ronström
Patch will appear in 5.1.12
[4 Jul 2006 5:40] Jon Stephens
Documented bugfix in 5.1.12 changelog; closed.