| Bug #35118 | Backup:Restore fails if previous create Procedure/Function/Trigger command fails | ||
|---|---|---|---|
| Submitted: | 6 Mar 2008 15:54 | Modified: | 1 Oct 2008 9:52 |
| Reporter: | Hema Sridharan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
| Version: | mysql-6.0-backup | OS: | Linux |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
[13 Mar 2008 16:53]
MySQL Verification Team
Thank you for the bug report.
[25 Apr 2008 14:18]
Rafal Somla
See very similar BUG#34180.
[1 Oct 2008 9:52]
Jørgen Løland
I can't repeat the problem on the backup branch anymore. I have run the following mtr test script without problems on Ubuntu x86/64:
--------------
############
# Procedure
############
create database test1;
use test1;
create table country (cno int ,c_code char(10), city varchar(10));
insert into country values
(1,'ind','bang'),(2,'ind','chen'),(3,'ind','bomb'),(4,'ind','hyd'),(5,'us','sfo'),(6,'us',
'phili');
--error 1064
create procedure countryinfo (IN loc char(3)) BEGIN select * from country where c_code=loc ;
delimiter //;
create procedure countryinfo (IN loc char(3)) BEGIN select * from country where c_code=loc; end //
--replace_column 1 #
backup database test1 to 'test1.bup'; //
delimiter ;//
--echo
--echo Contents of "Country" before restore
select * from country;
--echo
--echo Result of procedure before restore
call countryinfo ('ind');
--echo
--replace_column 1 #
restore from 'test1.bup';
--echo
--echo Contents of "Country" after restore
select * from country;
--echo
--echo Result of procedure after restore
call countryinfo ('ind');
############
# Function
############
create database test2;
use test2;
create table country (cno int ,c_code char(10), city varchar(10));
insert into country values
(1,'ind','bang'),(2,'ind','chen'),(3,'ind','bomb'),(4,'ind','hyd'),(5,'us','sfo'),(6,'us',
'phili');
delimiter //;
--error 1064
create function number_of_country() returns integer begin return (select count(*) from country);//
create function number_of_country() returns integer begin return (select count(*) from country); end //
--replace_column 1 #
backup database test2 to 'test2.bup'; //
delimiter ;//
--echo
--echo Contents of "Country" before restore
select * from country;
--echo
--echo Result of function before restore
select number_of_country();
--echo
--replace_column 1 #
restore from 'test2.bup';
--echo
--echo Contents of "Country" after restore
select * from country;
--echo
--echo Result of function after restore
select number_of_country();
Description: 1) I create database and tables in the database. 2)I create procedure (with wrong syntax so that command fails). 2)Repeat step 2 with correct syntax. 3)Now I backup the database. 4)I Restore it , Restore shows me an error. NOTE: The above operations can be repeated for stored functions/triggers. How to repeat: mysql> create database test1; Query OK, 1 row affected (0.00 sec) mysql> use test1; Database changed mysql> create table country (cno int ,c_code char(10), city varchar(10)); Query OK, 0 rows affected (0.03 sec) mysql> insert into country values (1,'ind','bang'),(2,'ind','chen'),(3,'ind','bomb'),(4,'ind','hyd'),(5,'us','sfo'),(6,'us','phili'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 Now I create procedure by giving wrong syntax and then will give correct syntax and the procedure is created. mysql> create procedure countryinfo (IN loc char(2)) BEGIN select * from country where c_code=loc ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> delimiter // mysql> create procedure countryinfo (IN loc char(2)) BEGIN select * from country where c_code=loc ; end // Query OK, 0 rows affected (0.01 sec) mysql> backup database test1 to '/data2/heman/backup_dmp/test1'; -> // +-----------+ | backup_id | +-----------+ | 1 | +-----------+ 1 row in set (0.08 sec) mysql> delimiter ; mysql> restore from '/data2/heman/backup_dmp/test1'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE `country` ( `cno` int(11) DEFAULT NULL, `c_code` char(10) DEF' at line 1 ================================================================================ Creating Function ================== mysql> create database test2; Query OK, 1 row affected (0.00 sec) mysql> use test2; Database changed mysql> create table country (cno int ,c_code char(10), city varchar(10)); Query OK, 0 rows affected (0.04 sec) mysql> insert into country values (1,'ind','bang'),(2,'ind','chen'),(3,'ind','bomb'),(4,'ind','hyd'),(5,'us','sfo'),(6,'us','phili'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> delimiter // mysql> create function number_of_country() returns integer begin return (select count(*) from country); -> // ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> create function number_of_country() returns integer begin return (select count(*) from country); end // Query OK, 0 rows affected (0.00 sec) mysql> select number_of_country(); -> // +---------------------+ | number_of_country() | +---------------------+ | 6 | +---------------------+ 1 row in set (0.00 sec) mysql> backup database test2 to '/data2/heman/backup_dmp/test2'; -> // +-----------+ | backup_id | +-----------+ | 3 | +-----------+ 1 row in set (0.08 sec) mysql> delimiter ; mysql> restore from '/data2/heman/backup_dmp/test2'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE `country` ( `cno` int(11) DEFAULT NULL, `c_code` char(10) DEF' at line 1 =============================================================================== The same operations can be repeated for triggers also.Here also the restore fails if the previous create trigger fails. Suggested fix: Restore should not fail if the previous create procedure/function/trigger command fails.