2

I have a standard mysqlbackup script that runs in cron. We recently had an issue where we removed a column from a table that a view was referencing. As such, our backup script was failing with an error similar to the following:

mysqldump: Couldn't execute 'show create table `v`': View 'foo_breakage.v' 
references invalid table(s) or column(s) or function(s) or definer/invoker of 
view lack rights to use them (1356)

If you simply drop this view, backups will work again. What I'm trying to solve is to be either a) notified in the future when this error happens, or b) (this is probably unlikely), but force the backup to go through regardless with an error like this if at all possible.

What's the best way to approach this problem? I'm assuming the only, and best approach for this is to check an exit status code and send an email notification to sysops in the event of failure. Will the above failure show up in an exit status code? Looking for creative/easy solutions.

2 Answers 2

5

I would check for errors and mail/trigger something accordingly - but you can also force the backup to continue regardless of errors:

from the mysqldump reference page:

--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.

1

Easy solution : use the "automysqlbackup' script from sourceforge.net and throw that on cron.

It even auto rotates daily / weekly backups + emails you with all the results.

You must log in to answer this question.

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