MySQL Bugs: #34868: Backup: restore failure if two procedures

Bug #34868	Backup: restore failure if two procedures
Submitted:	27 Feb 2008 0:57	Modified:	24 Jun 2008 7:46
Reporter:	Peter Gulutzan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
I create two procedures.
I say BACKUP DATABASE.
I say RESTORE.
Only one procedure exists.

How to repeat:
mysql> create database d;
Query OK, 1 row affected (0.00 sec)

mysql> use d
Database changed
mysql> create procedure p1() set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p2() set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> backup database d to '1';
+-----------+
| backup_id |
+-----------+
| 1         |
+-----------+
1 row in set (0.13 sec)

mysql> restore from '1';
+-----------+
| backup_id |
+-----------+
| 2         |
+-----------+
1 row in set (0.09 sec)

mysql> call p1();
ERROR 1305 (42000): PROCEDURE d.p1 does not exist
mysql> call p2();
Query OK, 0 rows affected (0.00 sec)

Thank you for the bug report. Verified as described.

create database d;
use d
create procedure p1() set @a=5;
create procedure p2() set @a=5;
backup database d to '1';
restore from '1';
call p1();
call p2();

I could see the similar behavior in my system  for multiple stored procedures and functions. But to my surprise this doesn't happen for procedures in database, which has at least one table. For example,
 
#function1
 
CREATE FUNCTION circle_area(radius float) RETURNS FLOAT RETURN PI() * radius * radius;
 
#-- creating function2
 
CREATE FUNCTION rectangle_area(length float,breadth
float)
RETURNS FLOAT
RETURN length * Breadth;
 
#Create table and load with data.
 
CREATE TABLE players(playerno tinyint,name char(30),city varchar(20))engine=myisam;
 
#--echo loading data
INSERT INTO players VALUES
(1,'aa1','RR1'),(2,'aa2','RR2'),(3,'aa3','RR3'),(4,'aa4','RR4'),(5,'aa5'
,'RR5'),(6,'aa6','RR6'),(7,'aa7','RR7'),(8,'aa8','RR8');
 
In the above example ,after  Restore both the functions are present , but if we delete the table from the database then the one of the functions is lost and eventually errors out.

I can't repeat this problem in the current mysql-6.0-backup tree. The code handling different objects during backup/restore has been refactored. Apparently this issue has been fixed by these changes.

I can repeat the bug with mysql-6.0-backup, pulled today.
Returning to status = 'Verified'.

I can no longer repeat the bug with mysql-6.0-backup, pulled today.

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/48391

2639 Jorgen Loland	2008-06-24
      Test case for bug#34868 - "Backup: restore failure if two procedures"
      
      The bug has been fixed by an unknown patch. This patch only adds a regression test.

Ok to push the regression test.

Patch approved pending minor request to change procedure bodies to make them unique and to show the results of the procedures before and after the backup and restore.

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/48474

2640 Jorgen Loland	2008-06-25
      bug#34868 - "Backup: restore failure if two procedures"
      
      Modified test case to check that the procedures have expected effect.

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/48475

2641 Jorgen Loland	2008-06-25 [merge]
      bug#34868 - merge commit

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/48476

2641 Jorgen Loland	2008-06-25 [merge]
      bug#34868 - merge commit

Pushed into 6.0.7-alpha  (revid:jorgen.loland@sun.com-20080624140059-uuis7l1xtg8iq0st) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)