4

I am having the exact same problem as this person here: How can I prevent mysqldump from prepending the database name in CREATE VIEW?

I am using MariaDB 10.4.7 x64 on Windows. For just ONE of my views, whenever I run mysqldump, it always prepends the database name to each table. I have tried dropping and recreating the view to no avail. This is what the result from mysqldump looks like.

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `view_x` 
    AS
(
SELECT * FROM
databasename.table1
LEFT JOIN databasename.table2 ON databasename.table2.id = databasename.table1.id
...
);

This is causing an issue when I import the database into a test server using a different database name because the previous database name is hard coded into the view.

Has anyone ever experienced this? I'm afraid my googlefu has failed me.

EDIT: After looking at all of my views, this particular view (and the only view) uses derived tables in the FROM clause. After removing these derived queries and all traces from the SELECT statement and recreating the view, the database name was no longer being prepended to the tables. So...I don't know if this is a bug or expected behavior. I think I will file a bug report.

7
  • maybe your view was created with explicit database references. look at SHOW CREATE VIEW view_x. Technically mysqldump uses SHOW CREATE TABLE view_x whoever the results should be the same.
    – danblack
    Apr 17, 2020 at 3:15
  • Are you dumping with a single, or list or database or --all-databases?
    – danblack
    Apr 17, 2020 at 3:16
  • @danblack I'm dumping it using a single database. When I look at SHOW CREATE VIEW view_x it prepends the database name. I've tried dropping the view and recreating WITHOUT explicit database references and it still does the same thing. I've created multiple views to test and NONE of them do this but this one.
    – Jeff
    Apr 17, 2020 at 13:01
  • no idea sorry. I you can manufacture table structures and a create view similar create a bug report jira.mariadb.org
    – danblack
    Apr 18, 2020 at 0:02
  • 3
    Link to JIRA ticket: jira.mariadb.org/browse/MDEV-22282 May 20, 2020 at 13:46

1 Answer 1

2

Future visitors, follow to the jira ticket on https://jira.mariadb.org/browse/MDEV-22282

It seems to have been fixed on Versions 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4

Another workaround - also seen on that jira ticket - is to monckeypatch the SQL dump. Just to exemplify:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.