Bug #11287 Errors in stored procedure (ERROR 1418, ERROR 2013)
Submitted: 13 Jun 2005 12:58 Modified: 21 Jul 2005 14:30
Reporter: Eugene Reuter Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.6 OS:Linux (CentOS4)
Assigned to: CPU Architecture:Any

[13 Jun 2005 12:58] Eugene Reuter 
Description:
Table Layout:

create table 
mysum( 
id int not null auto_increment primary key,
acc int,
amount decimal(10,2),
index (acc)
)
engine=INNODB;

create table 
mytr( 
id int not null auto_increment primary key,
acc int,
amount decimal(10,2),
status enum('I','P') not null,
index (acc),
FOREIGN KEY (acc) REFERENCES mysum (acc)
ON DELETE RESTRICT ON UPDATE CASCADE
)
engine=INNODB;

When I first tried to create procedure update_mytr like this:
_________________________________________________
delimiter &
CREATE PROCEDURE update_mytr()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE am decimal(10,2);
  DECLARE ac,myid int;
  DECLARE cur1 CURSOR FOR SELECT id,acc,amount FROM mytest.mytr where status='I';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  

  OPEN cur1;
  

  REPEAT
    FETCH cur1 INTO myid, ac, am;
    
    IF NOT done THEN
       update mytest.mysum set amount=amount+am where acc=ac;
       
    ELSE
    update mytest.mytr set status='P' where  status='I'; 
       
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
 
END 
&
_____________________________________________________
ERROR 1418 (HY000): This routine is declared to be non-deterministic and to 
modify data and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)

change to deterministic helped:
_______________________________________________
delimiter &
CREATE PROCEDURE update_mytr() deterministic
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE am decimal(10,2);
  DECLARE ac,myid int;
  DECLARE cur1 CURSOR FOR SELECT id,acc,amount FROM mytest.mytr where status='I';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  

  OPEN cur1;
  

  REPEAT
    FETCH cur1 INTO myid, ac, am;
    
    IF NOT done THEN
       update mytest.mysum set amount=amount+am where acc=ac;
       
    ELSE
    update mytest.mytr set status='P' where  status='I'; 
       
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
 
END 
&
delimiter ;
________________________________________________________________
but if i run this procedure I get ERROR 2013:
tables befor call update_mytr()
mysql> select * from mytr;
+----+------+--------+--------+
| id | acc  | amount | status |
+----+------+--------+--------+
|  1 |  300 | 10.00  | I      |
|  2 |  400 | 10.00  | I      |
|  3 |  400 | 10.00  | I      |
|  4 |  400 | 14.00  | I      |
+----+------+--------+--------+
4 rows in set (0.00 sec)

mysql> select * from mysum;
+----+------+--------+
| id | acc  | amount |
+----+------+--------+
|  1 |  300 | 0.00   |
|  2 |  400 | 0.00   |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> call update_mytr();
ERROR 2013 (HY000): Lost connection to MySQL server during query

tables after call update_mytr()
mysql> select * from mysum;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mytest

+----+------+--------+
| id | acc  | amount |
+----+------+--------+
|  1 |  300 | 10.00  |
|  2 |  400 | 0.00   |
+----+------+--------+
2 rows in set (0.00 sec)
mysql> select * from mytr;
+----+------+--------+--------+
| id | acc  | amount | status |
+----+------+--------+--------+
|  1 |  300 | 10.00  | I      |
|  2 |  400 | 10.00  | I      |
|  3 |  400 | 10.00  | I      |
|  4 |  400 | 14.00  | I      |
+----+------+--------+--------+
4 rows in set (0.00 sec)

How to repeat:
create table 
mysum( 
id int not null auto_increment primary key,
acc int,
amount decimal(10,2),
index (acc)
)
engine=INNODB;

create table 
mytr( 
id int not null auto_increment primary key,
acc int,
amount decimal(10,2),
status enum('I','P') not null,
index (acc),
FOREIGN KEY (acc) REFERENCES mysum (acc)
ON DELETE RESTRICT ON UPDATE CASCADE
)
engine=INNODB;

delimiter &
CREATE PROCEDURE update_mytr() deterministic # without ERROR 1418
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE am decimal(10,2);
  DECLARE ac,myid int;
  DECLARE cur1 CURSOR FOR SELECT id,acc,amount FROM mytest.mytr where status='I';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  

  OPEN cur1;
  

  REPEAT
    FETCH cur1 INTO myid, ac, am;
    
    IF NOT done THEN
       update mytest.mysum set amount=amount+am where acc=ac;
       
    ELSE
    update mytest.mytr set status='P' where  status='I'; 
       
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
 
END 
&
delimiter ;

mysql> select * from mysum;
+----+------+--------+
| id | acc  | amount |
+----+------+--------+
|  1 |  300 | 0.00   |
|  2 |  400 | 0.00   |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> select * from mytr;
+----+------+--------+--------+
| id | acc  | amount | status |
+----+------+--------+--------+
|  1 |  300 | 10.00  | I      |
|  2 |  400 | 10.00  | I      |
|  3 |  400 | 10.00  | I      |
|  4 |  400 | 14.00  | I      |
+----+------+--------+--------+
4 rows in set (0.00 sec)

call update_mytr();
[13 Jun 2005 13:03] Eugene Reuter 
--
[15 Jun 2005 7:46] Aleksey Kishkin 
I tested it on linux slackware and it seems to be working without problem..

mysql> select * from mysum;
+----+------+--------+
| id | acc  | amount |
+----+------+--------+
|  1 |  300 | 0.00   |
|  2 |  400 | 0.00   |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> select * from mytr;
+----+------+--------+--------+
| id | acc  | amount | status |
+----+------+--------+--------+
|  1 |  300 | 10.00  | I      |
|  2 |  400 | 10.00  | I      |
|  3 |  400 | 10.00  | I      |
|  4 |  400 | 14.00  | I      |
+----+------+--------+--------+
4 rows in set (0.00 sec)

mysql> call update_mytr();
Query OK, 4 rows affected (0.02 sec)

What architecture do you use? i386? IA64? x86_64? ppc32? 

Do you use binaries, built by MySQL or compile mysql from source?
[15 Jun 2005 15:57] Eugene Reuter 
Aleksey thank you for your response!!!
I installed mysql compiled binaries for RHEL4 (MySQL-server-standard-5.0.6-0.rhel4.i386.rpm) on CentOS4 which is clone of RHEL4.
[21 Jun 2005 14:30] Hartmut Holzgraefe 
I assume you have binary loging enabled (for replication or point-in-time recovery)?

In that case either all stored procedures have to be deterministic or 
log_bin_trust_routine_creators has to be used to enable binary loging
in the options file instead of just log_bin
[21 Jul 2005 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".