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

    XMLWordPrintable

    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

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            jeffprater Jeff Prater
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: