2

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?

  • Can you give two examples of queries which do work and another one that fails? – Vérace Jun 23 '18 at 15:51
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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