Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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