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

[27 Feb 2008 0:57] Peter Gulutzan
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)
[27 Feb 2008 11:47] MySQL Verification Team
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();
[15 Apr 2008 20:10] Hema Sridharan
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.
[13 May 2008 11:07] Rafal Somla
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.
[15 May 2008 17:27] Peter Gulutzan
I can repeat the bug with mysql-6.0-backup, pulled today.
Returning to status = 'Verified'.
[23 Jun 2008 18:27] Peter Gulutzan
I can no longer repeat the bug with mysql-6.0-backup, pulled today.
[24 Jun 2008 13:58] 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/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.
[25 Jun 2008 11:15] Rafal Somla
Ok to push the regression test.
[25 Jun 2008 12:26] Chuck Bell
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.
[25 Jun 2008 12:37] 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/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.
[25 Jun 2008 12:40] 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/48475

2641 Jorgen Loland	2008-06-25 [merge]
      bug#34868 - merge commit
[25 Jun 2008 12:41] 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/48476

2641 Jorgen Loland	2008-06-25 [merge]
      bug#34868 - merge commit
[13 Sep 2008 20:40] Bugs System
Pushed into 6.0.7-alpha  (revid:jorgen.loland@sun.com-20080624140059-uuis7l1xtg8iq0st) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)