3

I am having trouble getting mysql backups to run properly when their are views in the database. I think this might have something to do with needing a placeholder object for it. In any event I run this command:

mysqldump -u myuser -pmypassword mydatabase | mysql -u myuser -pmypassword -C mydatabase_Beta

The user has full privileges and I get this:

View mydatabase_beta.yadayada references invalid tables or columns or functions or definer/invoker or view lack rights to use them.

How can I back it up so that it restores all of my database properly?

In the example I am restoring it to a different name but I do need to be able to restore a working copy. I think it is probably an additional mysqldump parameter or maybe hot copy would work better. Thoughts?

3
  • Does the config you reference cause mysql to load the data to a separate instance? Does it help to save the dumpfile and then attempt to pipe it to mysql as a second step? Is there any difference in versions or supported storage engines between the two instances if there are two? Dec 18, 2009 at 21:37
  • I removed the -C, I do not need it, same issue though. Restoring to the exact same host/instance
    – Codezy
    Dec 19, 2009 at 6:27
  • Are you getting the error on the dump or the import? Dec 16, 2010 at 3:31

3 Answers 3

2

Try doing it in two steps first:

mysqldump -uroot -ppassword --skip-extended-insert database.sql > database.sql

So it backs up to a file and then pipe that into the second command:

mysql -u myuser -pmypassword -C mydatabase_Beta < database.sql

If the reference error is in the mysqldump step either fix the views or drop it from the database. I can't imaging they're still used if they reference a non-existing table.

1

I think the answer might be to add -f:

--force, -f

Continue even if an SQL error occurs during a table dump.

One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the definition refers to a table that has been dropped. Without --force, mysqldump exits with an error message. With --force, mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.

0

Shot in the dark here, but I don't think you need the -C in the second half of that statement. According to my copy of mysql -C defines the config file path to use.

1
  • Removed -C as I did not need it, still has issue with creating one of my views.
    – Codezy
    Dec 19, 2009 at 6:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .