holland-coredev team mailing list archive

[Andrew Garner <1250169@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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.

** Affects: holland-backup
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Holland
Build, which is subscribed to holland-backup.
https://bugs.launchpad.net/bugs/1250169

Title:
  exclude-invalid-views + estimation

Status in Holland Backup Framework:
  New

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 manage notifications about this bug go to:
https://bugs.launchpad.net/holland-backup/+bug/1250169/+subscriptions