holland-mysqldump's exclude-invalid-views does not check exception properly

[Andrew Garner]

Creating a bad view with the following test case will cause an uncaught exception to propagate when exclude-invalid-views is enabled:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (id int, data varchar(40)) character set = utf8 collate utf8_general_ci;
DROP VIEW IF EXISTS v_foo;
CREATE VIEW v_foo AS SELECT CONVERT(data USING utf8) = data FROM foo;
SHOW FIELDS FROM v_foo;
ALTER TABLE foo CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
SHOW FIELDS FROM v_foo;

# grep exclude-invalid-views /etc/holland/backupsets/default.conf
exclude-invalid-views = yes

# holland backup default
...
* Finding and excluding invalid views...
* Invalid and excluded views will be saved to /var/spool/holland/default/20130802_134443/invalid_views.sql
Backup failed after 0.04 seconds
Purged failed backup: default/20130802_134443
Released lock /etc/holland/backupsets/default.conf
Uncaught exception while running command 'backup': <_mysql_exceptions.OperationalError instance at 0x2b9e0718eab8>
Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/holland/core/command/command.py", line 209, in dispatch
    return self.run(self.optparser.prog, opts, *args)
  File "/usr/lib/python2.4/site-packages/holland/commands/backup.py", line 102, in run
    runner.backup(name, config, opts.dry_run)
  File "/usr/lib/python2.4/site-packages/holland/core/backup/base.py", line 124, in backup
    plugin.backup()
  File "/usr/lib/python2.4/site-packages/holland/backup/mysqldump/plugin.py", line 177, in backup
    self._backup()
  File "/usr/lib/python2.4/site-packages/holland/backup/mysqldump/plugin.py", line 196, in _backup
    exclude_invalid_views(self.schema, self.client, definitions_path)
  File "/usr/lib/python2.4/site-packages/holland/backup/mysqldump/plugin.py", line 428, in exclude_invalid_views
    cursor.execute('SHOW FIELDS FROM `%s`.`%s`' %
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1267, "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='")

This backup would have still failed without this option set:

Executing: /usr/bin/mysqldump --defaults-file=/var/spool/holland/default/20130802_135550/my.cnf --flush-privileges --max-allowed-packet=128M --lock-all-tables --all-databases
/usr/bin/mysqldump[26470]: mysqldump: Couldn't execute 'show create table `v_foo`': Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' (1267)

Internally there are a few problems here:

* Holland's view exclusion is missing a few cases where an invalid view should be excluded. At least errorcode 1267 should be added to the exclusion list.

* holland is not trapping errors cleaning from exclude_invalid_views() so SQL errors will propagate unchecked.

[Andrew Garner (muzazzi)]

Fixed pushed to feature branch for inclusion in v1.0.12: https://github.com/holland-backup/holland/tree/feature/lp_1207852_improve_invalid_view_detection

Sample run with this fix:

* Finding and excluding invalid views...
* Invalid and excluded views will be saved to /var/spool/holland/default/20131007_174947/invalid_views.sql
* Excluding invalid view `test`.`v_foo`: [1267] Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
!!! SHOW CREATE VIEW failed for `test`.`v_foo`. The view likely references columns that no longer exist in the underlying tables.
!!! Reconstructing view definition `test`.`v_foo` from INFORMATION_SCHEMA.VIEWS. This definition will not have an explicit ALGORITHM set.
* Saving view definition for `test`.`v_foo`
Excluding table test.v_foo