Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22282

When using mysqldump to backup a view that contains derived tables, the database name is prepended to each table in the view

Details

    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.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          kongharald Harald Groven added a comment - - edited

          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.

          kongharald Harald Groven added a comment - - edited 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.
          jeffprater Jeff Prater added a comment -

          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.

          jeffprater Jeff Prater added a comment - 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.
          sanja Oleksandr Byelkin added a comment -

          OK to push

          sanja Oleksandr Byelkin added a comment - OK to push
          idubynets Ivan added a comment -

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

          idubynets Ivan added a comment - should I update database to see the fix? i tried just mysqldump and no luck
          serg Sergei Golubchik added a comment - - edited

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

          serg Sergei Golubchik added a comment - - edited Yes, mysqldump uses SHOW CREATE VIEW to get the view definition. That is, the SQL statement for a view is generated by the server

          People

            serg Sergei Golubchik
            jeffprater Jeff Prater
            Votes:
            2 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.