MySQL Bugs: #13620: Restoring Database with Stored Procedure Fails

Bug #13620	Restoring Database with Stored Procedure Fails
Submitted:	29 Sep 2005 20:05	Modified:	19 Jan 2006 11:31
Reporter:	Sebastien Lehoux	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.1.5	OS:	Windows (Windows XP Professional)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
After i do a backup of my database that includes stored procedures, i get the following error for just about all of my stored procedures:

--------------------------------------------------------------------------

Warning: Do not know how to handle this statement at line 6504:
CREATE PROCEDURE "PRC_azGetMulRefTblValByTblCode"(P_RefTableCodes VARCHAR(8000), P_LanguageCode char(2))
BEGIN

	SELECT
		AZREFTABLESVALUES.ID,
		AZREFTABLESVALUES.ValueCode,
		AZREFTABLESVALUES.RefTableCode,
		AZREFTABLESVALUESDESCRIPTION.ShortDescription,
		AZREFTABLESVALUESDESCRIPTION.Description,
		AZREFTABLESVALUES.CreatedBy,
		AZREFTABLESVALUES.CreatedDate,
		AZREFTABLESVALUES.UpdatedBy,
		AZREFTABLESVALUES.UpdatedDate
	FROM
		AZREFTABLESVALUES
	INNER JOIN
		AZREFTABLESVALUESDESCRIPTION ON AZREFTABLESVALUES.ID = AZREFTABLESVALUESDESCRIPTION.ID
	WHERE
		INSTR(P_RefTableCodes, AZREFTABLESVALUES.RefTableCode) <> 0 AND
		AZREFTABLESVALUESDESCRIPTION.LanguageCode = P_LanguageCode
	ORDER BY
		AZREFTABLESVALUES.RefTableCode,
		AZREFTABLESVALUES.Sequence,
		AZREFTABLESVALUES.ValueCode;

END%%
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Warning: Do not know how to handle this statement at line 6538:
CREATE PROCEDURE "PRC_azGetRefTblValByTblCode"(P_RefTableCodes VARCHAR(50), P_LanguageCode CHAR(2), P_ParentValueCode VARCHAR(50))
BEGIN

	SELECT
		azRefTablesValues.ID,
		azRefTablesValues.ValueCode,
		azRefTablesValuesDescription.ShortDescription,
		azRefTablesValuesDescription.Description,
		azRefTablesValues.CreatedBy,
		azRefTablesValues.CreatedDate,
		azRefTablesValues.UpdatedBy,
		azRefTablesValues.UpdatedDate
	FROM
		azRefTablesValues
	INNER JOIN
		azRefTablesValuesDescription ON azRefTablesValues.ID = azRefTablesValuesDescription.ID
	WHERE
		azRefTablesValues.RefTableCode = P_RefTableCodes AND
		azRefTablesValuesDescription.LanguageCode = P_LanguageCode AND
		(P_ParentValueCode IS NULL OR azRefTablesValues.ParentValueCode = P_ParentValueCode)
	ORDER BY
		azRefTablesValues.Sequence;

END%%
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

------------------------------------------------------------------------

How to repeat:
Not quite sure. I guess you make a database with stored procedures, make a backup and try to restore it afterwards.

Suggested fix:
I'm really at a loss to why this is happening. Maybe something with the character encoding or the fact that the stored procedure is not preceeded with the name of the database?

Could you provide the script backup for to test on our side ?

Thanks in advance.

Backup file

Attachment: P4Backup 20050929 1421.zip (application/x-zip-compressed, text), 90.59 KiB.

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".

If you go to the file section, you'll see I included a ZIP file with the backup script.

Thank you for the feedback.
I tried your script and indeed I got the issue that you had reported
then I tried also with the mysql client and got several error message
like:

DROP PROCEDURE IF EXISTS `PRC_UpdateRevuePresse`;
DELIMI' at line 19
ERROR 1064 (42000): You have an error in your SQL s...
DELIMITER ;%%

so I noticed that your script has lines as:

DROP PROCEDURE IF EXISTS `PRC_InsertInstanceJudiciaire`;
DELIMITER %%;
CREATE PROCEDURE 

so I created a simple procedure and created a backup with 
Adminstrator 1.1.5 and now it handles them as below:

DROP PROCEDURE IF EXISTS `sp1`;
DELIMITER $$

Could you please try to create the backup with 1.1.5 and to
try to recover again. If you continue with the same issue can
you provide also that script?

Thanks in advance.

Here is the detail of my installation:
MySQL Version: MySQL 5.0.15-nt via TCP/IP
Client version: MySQL Client Version 5.0.11
MySQL Administrator version: 1.1.5
 
Here is the steps to reproduce the restore problem on the version 1.1.5 of MySQL Administrator.  The new backup file is joined post.
 
Click on New Project Backup
 
Backup Advanced Options:
Backup Execution Method = InnoDB Online Backup
Output File Options: Backup type = SQL File; Add DROP Statements checked; Complete INSERTs checked; Comment checked; Disable keys checked
 
Click on Execute Backup Now
 
Restore Options:
Target Schema: <New Schema>
'Create database(s) if they don't exist' checked
File Charset = utf8
 
Click on Start Restore
 
Problem founds:
Warning: Do not know how to handle this statement at line 52:
CREATE PROCEDURE `PRC_ReadTest`()
BEGIN
  select * from mytesttable;
END $$
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Test DB for Dec 1st comment

Attachment: MyTestDatabaseBackup 20051201 0949.zip (application/x-zip-compressed, text), 875 bytes.

I am able to create the error with a very simple test case.
1. Create a new schema, lets call it myTest.
2. Create a new table, tblTest with nothing but an integer field.
3. Create a new stored procedure, spTest.
4. Back up the schema.
5. Restore the schema and I get the error.

I noticed that if I modify the backup file from:
CREATE PROCEDURE `spTest`()
BEGIN

  Select * FROm tblTest;

END $$

To
CREATE PROCEDURE 'myTest`.`spTest`()
BEGIN

  Select * FROm tblTest;

END $$

MySQL Administrator is able to restore, but this is quite tedious since I have to do it for every stored procedure.

A workaround I am currentlly using is to copy all the text of the backup file and then run it in the MySQL Command Line Client.  This seems to restore the contents of my backup file including my stored procedures.  Which causes me to believe the backup file is correct and that the problem lies in the MySQL Administrator.

My guess is that MySQL Administrator is somehow loosing which Schema the store procedure suppose to belong to which is why it is throwing the error.

I am on Windows XP. using MySQL 5.0.16, MySQL Administrator 1.1.5

Using test case from Mr. Meza.

It' exactly how Apolo Meza has written. The restore function in MySQL Administrator can only restore routines, if the create statement contains fully qualified names but the but the backup function doesn't produce this kind of statements. Maybe anyone could add an option to the "Advanced Options" tab to enable/toggle fully qualified names in the backup file. This would be a workaround I (maybe many others) could live with...

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Please, notice that this bug occurs not only with SP but also restoring Views

Example:
----------------------------
Warning: Do not know how to handle this statement at line 18426:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `astbill`.`asv_friend` AS select `astbill`.`astaccount`.`uid` AS `uid`,`astbill`.`astaccount`.`serverid` AS `serverid`,`astbill`.`astaccount`.`accountcode` AS `accountcode`,`astbill`.`astaccount`.`username` AS `username`,`astbill`.`astaccount`.`accountname` AS `publicnumber`,`astbill`.`astaccount`.`tech` AS `tech`,`astbill`.`astaccount`.`type` AS `type`,`astbill`.`astaccount`.`secret` AS `secret`,`astbill`.`astaccount`.`forwardto` AS `forwardto`,`astbill`.`astaccount`.`fromuser` AS `fromuser`,`astbill`.`astaccount`.`authuser` AS `authuser`,`astbill`.`astaccount`.`fromdomain` AS `fromdomain`,`astbill`.`astaccount`.`nat` AS `nat`,`astbill`.`astaccount`.`qualify` AS `qualify`,`astbill`.`astaccount`.`host` AS `host`,`astbill`.`astaccount`.`port` AS `port`,`astbill`.`astaccount`.`callerid` AS `callerid`,`astbill`.`astaccount`.`context` AS `context`,`astbill`.`astaccount`.`dtmfmode` AS `dtmfmode`,`astbill`.`astaccount`.`insecure` AS `insecure`,`astbill`.`astaccount`.`canreinvite` AS `canreinvite`,`astbill`.`astaccount`.`disallow` AS `disallow`,`astbill`.`astaccount`.`allow` AS `allow`,`astbill`.`astaccount`.`restrictid` AS `restrictid`,`astbill`.`astaccount`.`comment` AS `comment`,`astbill`.`astaccount`.`active` AS `active`,`astbill`.`astaccount`.`date_created` AS `date_created`,`astbill`.`astaccount`.`timestamp` AS `timestamp` from `astbill`.`astaccount` where (`astbill`.`astaccount`.`tech` like _latin1'IN%');
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.
---------------------------------

Yes, I know. This is fixed.

Uhm..I just downloadd MySQL Administrator 1.1.7 and tried to restore the Astbill database (Astbill is a billing system for VoIP software Asterisk) but the problem remains... Now I try to ad the Astbill restore file, maybe it can help to make tries

You can find it here: http://www.ivanhalen.com/astbill_restore.zip

Ok, I saw that our parser did not yet handle the enhanced "create view" syntax and hence failed to understand the SQL. I have fixed this too but could still not recreate the objects in your sample dump, because your stored procedures reference other schemas, which I obviously not have. This does not mean, though, that restoration does still not work. I think I have now covered all cases so this should finally do what it is expected to do. Let me know if you still see a flaw in this regard.

Fix is available in next release or from our source repository.

Thanks, Mike
Wait for the new release (can you tell me when will it be out?) to check if it works and if I can import that database

Thanks still

Ehr... more than 10 day have gone but still no update from still-not-working 1.1.7 version: any news, please?

i got an error while restoring a backup file ,this is the error :
"
Warning: Do not know how to handle this statement at line 884:
CREATE DEFINER = `root` `root`@`localhost` TRIGGER `t_aufwandplan_insert` AFTER INSERT ON `t_data_aufwandplan` FOR EACH ROW begin

DECLARE OldID integer;

"
the tables have been restored correctly but triiger , views, fuction, sored procedure have not.

do any one have a solution for this problem and can help me ...
 thanks and best regards

I have also encountered similar issues:

Warning: Do not know how to handle this statement at line 2511:
update t_users
set languageID = 'en-US'
where user_name = 'gm';

I have tried different format of the SQL statements, such as using the upper/lower case of key words, using or not using “ ` " to surround table’s name and field, etc; they all cannot eliminated the warnings.