MySQL Bugs: #42693: Backup: report object numbers more fine-grained

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:	None
Category:	MySQL Server: Backup	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
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.

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

Analysis
--------
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>;