Bug #42693 Backup: report object numbers more fine-grained
Submitted: 9 Feb 2009 13:05 Modified: 1 May 2009 13:04
Reporter: Jørgen Løland Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Backup Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[9 Feb 2009 13:05] Jørgen Løland
The backup history table has a field for displaying number of objects backed up. However, there are many kinds of objects, and it would make more sense for the user if these where split up. For example, backup_history currently shows a backup containing

1 tablespace
1 databases
2 tables
1 view
10 stored procedures

as 15 objects. Furthermore, the number of privileges backed up are not shown at all.

How to repeat:
create database db;
create table db.t (i int);
insert into db.t values (1);

SELECT num_objects FROM mysql.backup_history ORDER BY backup_id DESC LIMIT 1;; 

Suggested fix:
Change the backup_history table to have more fine-grained object info, and add column for privileges. Then use the already existing internal count variables when logging the information.
[19 Feb 2009 20:05] Chuck Bell
Design Proposals
There are several ways to implement this feature. 

Option 1: Extend the backup_history table with more columns
Option 2: Extend the backup_progress table with a count column
Option 3: Create a new backup log named backup_summary

Option 1: I am not in favor of option 1 because it pollutes the original goal of that table -- to contain a historical record of the backup or restore event. It should not contain this level of detail. Besides, we would have to add 8-12 more columns to the table.

Option 2: This option is feasible but it would require users to select from the table in a peculiar way in order to find the information. For example, if a new column named count is added and the summary rows are encoded (more on this later) with 'summary' in the object field and what is being counted in the notes field, a user would have to issue a query like the following to get the summary information for his backup:

SELECT count, notes FROM mysql.backup_progress WHERE object = 'summary' AND backup_id = X;

The output would be something like:

count    notes
-----    -----------------
   4     Databases
  10     Tables
   3     Stored procedure

Of course, we could also create a new SQL command that does this for her. Something like SHOW BACKUP_SUMMARY FOR <backup_id>;

Note: The progress log was meant to show detailed progress information not summary information. By loading the object column we run the risk of savvy database administrators pointing the relational model bible at us and shouting SHAME! because we would violate several tenets (and they'd be justified).

However, this option is viable and we can overcome the relation model issue by creating a new column for summary information but there again we have two types of information shoehorned into the same table (that's bad BTW).

Option 3: This makes the most sense. It would take more work to create a new log that captures all forms of summary information, but it would solve the problem with options 1 and 2. The SQL command to see the summary information would be as simple as:

SELECT * FROM mysql.backup_summary WHERE backup_id = <id>;