[MDEV-15021] mysqldump --tables --routines generates non importable dump file Created: 2018-01-22  Updated: 2018-06-10  Resolved: 2018-06-10

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 5.5, 10.0, 10.1, 5.5.56, 10.2.8, 10.2
Fix Version/s: 5.5.61, 10.0.36, 10.1.34, 10.2.16, 10.3.8

Type: Bug Priority: Major
Reporter: Martin Häcker Assignee: Rutuja Surve (Inactive)
Resolution: Fixed Votes: 1
Labels: beginner-friendly, contribution, foundation, patch, upstream
Environment:

Mac OS X 10.13.2, CentOS 7.4.1708

Attachments: File problematic_dump.sql    

 Description   

When exporting some views and routines together (to separate manually and automatically managed parts of a schema) with `mysqldump ... --routines --tabels table1 table2 ...` I noticed that the generated dump-file is not importable again, because the routines are ordered after all the tables/ views and thus views that use them cannot be imported.

I have attached a test dump that I generated that shows this problem.

Workaround: Using negative logic and instead listing all the tables/views to skip seems to not trigger this bug.

 Comments   
Comment by Martin Häcker [ 2018-01-22 ]

Oh by the way: in this file I attached, the views do not use the routines, but im my production db they do. Sorry if this has created confusion.

Comment by Elena Stepanova [ 2018-01-23 ]

Thanks for the report. Reproducible on all versions of MariaDB (5.5-10.3) and MySQL (5.5-8.0).

CREATE FUNCTION f() RETURNS INT RETURN 1;
 
CREATE VIEW v1 AS SELECT f();
 
 
 
--exec $MYSQL_DUMP -uroot test --routines --tables v1 > $MYSQLTEST_VARDIR/test.dmp
 
 
 
DROP VIEW v1;
 
DROP FUNCTION f;
 
 
 
--exec $MYSQL -uroot test < $MYSQLTEST_VARDIR/test.dmp
 
 
 
# Cleanup
 
DROP VIEW v1;
 
DROP FUNCTION f;


ERROR 1305 (42000) at line 43: FUNCTION test.f does not exist
 
mysqltest: At line 9: exec of '/data/bld/10.3/bin/mysql --defaults-file=/data/bld/10.3/mysql-test/var/my.cnf -uroot test < /data/bld/10.3/mysql-test/var/test.dmp' failed, error: 256, status: 1, errno: 11

Comment by Vicențiu Ciorbaru [ 2018-06-10 ]

Fixed with 1d43f71c7b4c39a6fd196c104b5ebafb65376199

Generated at Thu Feb 08 08:18:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.