[MDEV-22282] When using mysqldump to backup a view that contains derived tables, the database name is prepended to each table in the view Created: 2020-04-17  Updated: 2022-12-26  Resolved: 2022-04-09

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.4.7, 10.2, 10.3, 10.5
Fix Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4

Type: Bug Priority: Major
Reporter: Jeff Prater Assignee: Sergei Golubchik
Resolution: Fixed Votes: 2
Labels: mysqldump, upstream-fixed, view, views
Environment:

Windows Server 2016 x64, Windows 10 1809 x64


 Description   

MySQL/MariaDB made it possible to create views which contain derived tables/subqueries in the FROM clause. All of the tables being referenced in this view belong to the same database--no other databases are referenced. When the view is exported via mysqldump or SHOW CREATE VIEW, all of the tables in the FROM clause are prepended with the database name. This creates a problem when importing a database dump into a database with a different name because any views containing derived queries will have the original database name hard coded into the view. This is how I discovered this bug (or feature).

All views which do not contain derived tables/subqueries does not exhibit this behavior.

This appears to be a problem in earlier versions: https://dba.stackexchange.com/questions/161850/how-can-i-prevent-mysqldump-from-prepending-the-database-name-in-create-view

EDIT: I meant to also include that any views which reference a separate view that contains subqueries will also have the database name prepended to the table names.

 Comments   
Comment by Elena Stepanova [ 2020-05-04 ]

Thanks for the report.
I'll leave it to sanja to determine whether it's a bug or a feature.

create table t1 (a int);
 
create view v1 as select * from t1;
 
show create view v1;
 
create view v2 as select * from (select * from t1) sq;
 
show create view v2;
 
 
 
# Cleanup
 
drop view v1;
 
drop view v2;
 
drop table t1;

10.5 f544a712

 
show create view v2;
 
View	Create View	character_set_client	collation_connection
 
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `sq`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `sq`	latin1	latin1_swedish_ci

Reproducible as described on 10.2-10.5 and MySQL 5.7. Not reproducible on 8.0.

Comment by Harald Groven [ 2021-01-05 ]

This bug makes database backups containing views unimportable (unless destination environment happen to have exactly the same database names as originating environment).

Please implement behaviour as in mysql 8.0 for storing views without prepending database name, unless database names are explicitly stated.

Are there any good workarounds for this bug?

Most short time solutions are likely to involve writing custom db backup scripts with extra text parsing steps to remove db name strings from mysql dump files before sql files may be imported to a development environment.

Simplest workable solution seems to be parsing a dump of db schema through a sed script like 

mysqldump --no-data DATABASE_NAME | sed "s/\`DATABASE_NAME\`\.//g" > DATABASE_NAME.sql

Among bad workarounds that shouldn't be necessary to consider are: (1) renaming destination databases to match origin databases (2) drop all views from production environment (3) adding --ignore-table list to mysqldump script for each view in database (4) comment out views from database dump, and recreate views later (5) run db-backup from user account without SHOW VIEW privilege and ignore noisy error messages.

Comment by Jeff Prater [ 2022-03-31 ]

Is there any update on this? We are currently using MariaDB 10.6.4 and the issue persists. While we do have a workaround (doing what Harold Groven suggested)--it's tedious. Thanks.

Comment by Oleksandr Byelkin [ 2022-04-08 ]

OK to push

Comment by Ivan [ 2022-12-16 ]

should I update database to see the fix? i tried just mysqldump and no luck

Comment by Sergei Golubchik [ 2022-12-26 ]

Yes, mysqldump uses SHOW CREATE VIEW to get the view definition. That is, the SQL statement for a view is generated by the server

Generated at Thu Feb 08 09:13:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.