mysqldump generates incorrect create view queries

Question

I use mysqlpump to backup database:

mysqlpump --single-transaction --add-drop-database --skip-definer \ --databases mydatabase --result-file /opt/myservice/backup/export.sql

My DB contains several views, and some of them are exported with totally incorrect SELECTs in the CREATE VIEW queries like this one:

CREATE VIEW `careerpulse-staging`.`user_view` AS SELECT
 1 AS `id`,
 1 AS `name`,
 1 AS `department`,
 1 AS `manager_id`
;

Why is it happening and how can I fix that?

• Vérace: Can you give two examples of queries which do work and another one that fails?

Answer 1

When dumping / pumping MySQL databases, the output script generates this as a temporary structure first, and later below at the end of the script, it drops this and create the actual view itself, something like this:

...DROP VIEW IF EXISTS myview ...

If you end up with the final view still selecting 1s, it would be probably because database name mismatch, when you use --databases option in the mysqldump , the script will output

USE database_name

in multiple places (not only at the top of the script), and specifically there is one before the DROP and Recreating the actual view.

This happened to me when exporting and importing databases with different name.