Bug #19068 wrong order of saved items
Submitted: 13 Apr 2006 0:13 Modified: 28 Aug 2006 9:28
Reporter: Sylvain Ferey Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Administrator Severity:S3 (Non-critical)
Version:1.1.9 OS:Windows (Windows)
Assigned to: Mike Lischke CPU Architecture:Any
Tags: Backup

[13 Apr 2006 0:13] Sylvain Ferey
Description:
The files generated by the 'Backup' panel in "SQL Files" format contain, in this order, the tables, the views, the stored procedures.
If a view uses a procedure stored in the same schema, the 'restore' fails to create the views since the needed procedures are missing.

How to repeat:
Save a schema containing a view that uses a procedure stored in same schema and restore the full schema.

Suggested fix:
Invert order of procedures and views in the generated SQL file.
[13 Apr 2006 11:50] Valeriy Kravchuk
Thank you for a problem report. You meant stored functions (as there is no direct way to use SP in view definition), I believe. Anyway, please, send the smallest possible backup that demonstrates the problem.
[13 Apr 2006 23:57] Sylvain Ferey
problem demonstration:

 % mysql -u root
mysql> create database toSave;
mysql> use toSave;
mysql> create table t_float (flt float);
mysql> delimiter $
mysql> create function flt4(f float) returns varchar(16) begin return format(f, 4); end $
mysql> delimiter ;
mysql> create view v_float as select flt4(t.flt) as flt from t_float t;
mysql> insert into t_float (3.1415926535897932384626433832795);
quit mysql

start MySQL Administrator ("MySQLAdministrator.exe")

select "Backup"
Tab "Bakcup Project"
click "New Project"
select "tosave" under "Schemata"
add "tosave" (btn ">")
click "Execute Backup Now"
save file

saved file contains:

.	-- MySQL Administrator dump 1.4
.	-- Server version	5.0.19-nt
.	-- Create schema tosave
.
.	CREATE DATABASE /*!32312 IF NOT EXISTS*/ tosave;
.	USE tosave;
.
.	DROP TABLE IF EXISTS `t_float`;
.	CREATE TABLE `t_float` (
.		`flt` float default NULL
.	) ENGINE=InnoDB DEFAULT CHARSET=latin1;
.
.	INSERT INTO `t_float` (`flt`) VALUES (3.14159);
.
.	DROP VIEW IF EXISTS `v_float`;
.	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
.		VIEW `tosave`.`v_float` AS select sql_no_cache `tosave`.`flt4`(`t`.`flt`) AS `flt` from `tosave`.`t_float` `t`;
.
.	DROP FUNCTION IF EXISTS `flt4`;
.	DELIMITER $$
.	CREATE FUNCTION `flt4`(f float) RETURNS varchar(16)
.	begin
.		return format(f, 4);
.	end $$
.	DELIMITER ;

select "Catalogs"
right-click "tosave"
select "Drop schema", confirm

select "Restore"
tab "General"
click "Open Backup File"
select previously saved file
click "Start Restore"

in "Problems found" the following error is reported:
!!!! The MySQL Server returned this Error:                   !!!!
!!!! MySQL Error Nr.1305-FUNCTION tosave.flt4 does not exist !!!!

Suggested fix:
invert views & stored functions, so that saved file becomes:

.	-- MySQL Administrator dump 1.4
.	-- Server version	5.0.19-nt
.	-- Create schema tosave
.
.	CREATE DATABASE /*!32312 IF NOT EXISTS*/ tosave;
.	USE tosave;
.
.	DROP TABLE IF EXISTS `t_float`;
.	CREATE TABLE `t_float` (
.		`flt` float default NULL
.	) ENGINE=InnoDB DEFAULT CHARSET=latin1;
.
.	INSERT INTO `t_float` (`flt`) VALUES (3.14159);
.
.	DROP FUNCTION IF EXISTS `flt4`;
.	DELIMITER $$
.	CREATE FUNCTION `flt4`(f float) RETURNS varchar(16)
.	begin
.		return format(f, 4);
.	end $$
.	DELIMITER ;
.
.	DROP VIEW IF EXISTS `v_float`;
.	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
.		VIEW `tosave`.`v_float` AS select sql_no_cache `tosave`.`flt4`(`t`.`flt`) AS `flt` from `tosave`.`t_float` `t`;

re-run "Restore"
tab "General"
click "Open Backup File"
select previously saved file
click "Start Restore"
the operation executes with "The restore operation was finished successfully".
[24 Apr 2006 13:28] Valeriy Kravchuk
Verified just as described in the last comment (with VALUES clause added to INSERT statement) with 1.1.9 and MySQL server 5.0.20a on XP. It is a bug (function definition is dumped AFTER view definition).
[22 Aug 2006 14:38] Mike Lischke
I'm afraid this problem cannot be fixed in a general way. Unfortunately, there is no way to switch of dependency checking of referenced elements (except for foreign keys), which means each referenced object must exist before it can be used in a declaration of another object. However, dependencies can be very complex (function uses a view, which uses a table and function, which uses a procedure that uses the same view as the first function etc.). Hence this problem is currently unsolvable.  mysqldump uses a quite dirty hack to make it work by defining used objects first  (not complete just a dummy) and remove it afterwards. Once a definition is done reference objects can be deleted without problems.

Since the GUI tools don't have all the info the server has (particularly a full blown SQL parser) we are left with that situation. Please use mysqldump if you cannot create valid dumps with MA.

Sorry for the inconvenience.
[23 Aug 2006 11:52] Mike Lischke
I decided to reopen this bug entry and fix it by using the same approach as mysqldump does, by creating temporary dummy tables for all views to be created and reorder the items in the dump file. While this is not an elegant solution (but rather a hack, because of the missing possibility to switch off reference checking) it should solve the reporter's problem and at the same time (almost) brings MA on the same dump ability level as mysqldump is.
[23 Aug 2006 23:03] Sylvain Ferey
Thanks to consider reopening of this entry. I agree with your previous point and I share your analysis that there is no obvious way to simply reorder items so that problem always disappear; meantime if basic (not circular) cases can be solved it will be really helpfull.
[28 Aug 2006 9:28] Mike Lischke
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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html