How to prevent MariaDB mysqldump from prepending database name in CREATE VIEW?

Question

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.

• danblack: 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: Are you dumping with a single, or list or database or --all-databases?
• Jeff: @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.
• danblack: no idea sorry. I you can manufacture table structures and a create view similar create a bug report jira.mariadb.org
• SleepyTonic: Link to JIRA ticket: jira.mariadb.org/browse/MDEV-22282

Answer 1

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