exclude-invalid-views + estimation

[Andrew Garner]

holland-mysqldump estimates the backup size by checking the DATA_LENGTH+INDEX_LENGTH columns from SHOW TABLE STATUS (MySQL 5.0) or the INFORMATION_SCHEMA (MySQL 5.1+). This breaks if there are certain kinds of invalid views, typically revolving around permission problems:

mysql> SHOW TABLE STATUS\G
ERROR 1143 (42000): SELECT command denied to user 'user'@'host' for column 'XXX' in table 'YYYY'

To avoid this, on MySQL 5.1+ any INFORMATION_SCHEMA query that would result in Open_full_table/Open_frm_only must not be run or, alternatively, the bad views must be excluded. I.e.:

SELECT TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'foo'
AND TABLE_NAME NOT IN (/* list of bad view names in the foo schema */)

This can be worked around in holland 1.0 by setting the estimate-method = const:<size> in the [mysqldump] section and skipping the metadata estimation method entirely. This disables dynamic size estimation and so does not adapt to changes in the database, however.

The problem here is that holland runs these estimation queries before mysqldump even runs - and before it even detect invalid views. The logic here is pretty messy and fundamentally working around broken deployments of MySQL (ultimately related to a buggy feature in the MySQL server).

The holland amsterdam branch improves this significantly by avoiding cases where we even do extended information_schema queries. Further, IIRC, view exclusions are done before size estimation so we either avoid this problem - or it should be trivial to avoid this problem - by injecting the invalid view names into the I_S query.

Focus should probably be on releasing a holland 2.0 and better documenting workarounds to this problem with holland 1.0's brain damage.