Bug #39109 Mysql Online Backup table doesn't show correct num_object count

Description:
1) Create database db1, tables, views, triggers, procedures and functions in db1.
2) Execute backup database operation.
3) Verify the online backup table to check the no.of objects(num_objects) included in the backup image.

The "num_objects" row from online backup table shows only no.of tables in the database and fails to show remaining objects.

CREATE DATABASE db1;
USE db1;
CREATE TABLE t1(id int);
CREATE TABLE t2(id int);
--echo Creating view
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t2;
BACKUP DATABASE db1 to 'db1.bak';
--query_vertical SELECT * FROM mysql.online_backup;
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1(id int);
CREATE TABLE t2(id int);
--echo Creating view
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t2;
BACKUP DATABASE db1 to 'db1.bak';
--query_vertical SELECT * FROM mysql.online_backup;

How to repeat:
mysql> create database db1;
mysql> use db1;
Database changed
mysql> create table t1(id int);
mysql> create table t2(id int);
mysql> create view v1 as select * from t1;
mysql> create view v2 as select * from t1;
mysql> backup database db1 to 'db1.bak';
+-----------+
| backup_id |
+-----------+
| 1         |
+-----------+
1 row in set (0.22 sec)

mysql> select * from mysql.online_backup\G
*************************** 1. row ***************************
          backup_id: 1
         process_id: 0
         binlog_pos: 0
        binlog_file: NULL
       backup_state: complete
          operation: backup
          error_num: 0
        num_objects: 2
        total_bytes: 2050
validity_point_time: 2008-08-29 05:41:00
         start_time: 2008-08-29 05:41:00
          stop_time: 2008-08-29 05:41:00
host_or_server_name: localhost
           username: root
        backup_file: db1.bak
       user_comment:
            command: backup database db1 to 'db1.bak'
            drivers: MyISAM
1 row in set (0.00 sec)

mysql> create table t3(id int);
mysql> ### triggers #####
mysql> CREATE trigger trg before insert on t1 for each row set @a:=1;
mysql> set @a:=0;
mysql> select @a;
+------+
| @a   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> insert into t1 values (9);
mysql> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> ##procedures ####
mysql> create procedure foo()
    ->   insert into t2 values (42);
Query OK, 0 rows affected (0.00 sec)

mysql> call foo();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|   42 |
+------+
1 row in set (0.00 sec)

mysql> ####functions ####
mysql> create function mul(x int, y int) returns int
    ->   return x*y;
Query OK, 0 rows affected (0.00 sec)
mysql> select mul(1,1), mul(3,5), mul(4711, 666);
+----------+----------+----------------+
| mul(1,1) | mul(3,5) | mul(4711, 666) |
+----------+----------+----------------+
|        1 |       15 |        3137526 |
+----------+----------+----------------+
1 row in set (0.00 sec)

mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
| v1            |
| v2            |
+---------------+

mysql> backup database db1 to 'db11.bak';
+-----------+
| backup_id |
+-----------+
| 2         |
+-----------+
1 row in set (0.23 sec)

mysql> select * from mysql.online_backup where backup_id=2\G
*************************** 1. row ***************************
          backup_id: 2
         process_id: 0
         binlog_pos: 0
        binlog_file: NULL
       backup_state: complete
          operation: backup
          error_num: 0
        num_objects: 3
        total_bytes: 2066
validity_point_time: 2008-08-29 05:43:17
         start_time: 2008-08-29 05:43:17
          stop_time: 2008-08-29 05:43:17
host_or_server_name: localhost
           username: root
        backup_file: db11.bak
       user_comment:
            command: backup database db1 to 'db11.bak'
            drivers: MyISAM
1 row in set (0.00 sec)

If you notice from the above online backup table and one before that, the value of "num_objects" is shown as 2 and 3. This shows that num_objects displays only no.of tables in the database whereas all other objects like views, sp, sf etc are ignored.

Also note if you backup only the database without any tables in it, then the num_objects from the online backup tables shows zero(0).

Thank you for a problem report. Verified with recent 6.0-bzr on Linux:

openxs@suse:/home2/openxs/dbs/6.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.7-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

mysql> USE db1;
Database changed
mysql> CREATE TABLE t1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t2(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE VIEW v2 AS SELECT * FROM t2;
Query OK, 0 rows affected (0.01 sec)

mysql> BACKUP DATABASE db1 to 'db1_1.bak';
+-----------+
| backup_id |
+-----------+
| 4         |
+-----------+
1 row in set (0.18 sec)

mysql> SELECT * FROM mysql.online_backup where backup_id=4\G
*************************** 1. row ***************************
          backup_id: 4
         process_id: 0
         binlog_pos: 0
        binlog_file: NULL
       backup_state: complete
          operation: backup
          error_num: 0
        num_objects: 2
        total_bytes: 2050
validity_point_time: 2008-08-04 08:30:17
         start_time: 2008-08-04 08:30:17
          stop_time: 2008-08-04 08:30:17
host_or_server_name: localhost
           username: root
        backup_file: db1_1.bak
       user_comment:
            command: BACKUP DATABASE db1 to 'db1_1.bak'
            engines: MyISAM
4 rows in set (0.00 sec)

mysql> create table t3(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE trigger trg before insert on t1 for each row set @a:=1;
Query OK, 0 rows affected (0.03 sec)

mysql> set @a=0;
Query OK, 0 rows affected (0.03 sec)

mysql> select @a;
+------+
| @a   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> insert into t1 values(9);
Query OK, 1 row affected (0.02 sec)

mysql> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>  create procedure foo()
    -> insert into t2 values(42);
Query OK, 0 rows affected (0.00 sec)

mysql> call foo();
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|   42 |
+------+
1 row in set (0.00 sec)

mysql> create function mul(x int, y int) returns int
    ->   return x*y;
Query OK, 0 rows affected (0.03 sec)

mysql> select mul(3,5);
+----------+
| mul(3,5) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
| v2            |
+---------------+
4 rows in set (0.00 sec)

mysql> backup database db1 to 'db11.bak';
+-----------+
| backup_id |
+-----------+
| 5         |
+-----------+
1 row in set (0.38 sec)

mysql> SELECT * FROM mysql.online_backup where backup_id=5\G
*************************** 1. row ***************************
          backup_id: 5
         process_id: 0
         binlog_pos: 0
        binlog_file: NULL
       backup_state: complete
          operation: backup
          error_num: 0
        num_objects: 3
        total_bytes: 3091
validity_point_time: 2008-08-04 08:34:32
         start_time: 2008-08-04 08:34:32
          stop_time: 2008-08-04 08:34:32
host_or_server_name: localhost
           username: root
        backup_file: db11.bak
       user_comment:
            command: backup database db1 to 'db11.bak'
            engines: MyISAM
1 row in set (0.02 sec)

Bug#42693 (feature request) created to make object count more fine-grained. For this bug, the object count will include:

tablespaces
databases
tables
views
all types of routines

but not privileges, users and character sets.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65862

2764 Jorgen Loland	2009-02-11
      Bug#39109 - Mysql Online Backup table doesn't show correct num_object count
      
      Previously, the num_objects column in the backup_history table showed the number of tables in the backup image. It now shows the number of objects with names (i.e, tablespace, database, table, view, routine)

Patch reviewed. Comments sent. Returning to in-progress while awaiting answers to questions and a new patch.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66000

2770 Jorgen Loland	2009-02-12
      Bug#39109 - Mysql Online Backup table doesn't show correct num_object count
            
      Previously, the num_objects column in the backup_history table showed the number of
      tables in the backup image. It now shows the number of objects with names (i.e,
      tablespace, database, table, view, routine)

Patch approved pending this request:

...

>  /// Returns total number of tables in the image.
>  inline
> -ulong Image_info::table_count() const
> +uint Image_info::table_count() const
>  {    return m_table_count;
>  }

[1] I think this and the other count functions should return ulong to be safe. The number of objects counted could exceed uint.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66476

2772 Jorgen Loland	2009-02-16
      Bug#39109 - Mysql Online Backup table doesn't show correct num_object count
                  
      Previously, the num_objects column in the backup_history table showed the number of
      tables in the backup image. It now shows the number of objects with names (i.e,
      tablespace, database, table, view, routine)

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090326121822-pt84kzxxayzho4mn) (version source revid:rafal.somla@sun.com-20090302164601-znhm4tadplfi2iqu) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

Previously, the num_objects column in the backup_history table showed
only the number of tables in the backup image. It now shows the
number of objects with names (tablespaces, databases, tables, views,
stored programs).