MySQL Bugs: #17138: Partitions: prepared statements become unknown

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

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

mysql> call po()//
ERROR 1243 (HY000): Unknown prepared statement handler (stmt2) given to EXECUTE

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;

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

Review done. Still some small cleanups to be done

Patch approved, after fixing small issues found in last review

Patch is now pushed to the kt team tree in 5.1

Patch will appear in 5.1.12

Documented bugfix in 5.1.12 changelog; closed.