exclude-invalid-views + estimation

Bug #1250169 reported by Andrew Garner
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
holland-backup
New
Undecided
Unassigned

Bug Description

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.

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.