Bug #39109 Mysql Online Backup table doesn't show correct num_object count
Submitted: 29 Aug 2008 6:26 Modified: 3 Apr 2009 3:19
Reporter: Hema Sridharan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:mysql-6.0-backup, mysql-6.0 OS:Linux
Assigned to: Jørgen Løland
Triage: Triaged: D3 (Medium)

[29 Aug 2008 6:26] Hema Sridharan
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.
[29 Aug 2008 6:35] Hema Sridharan
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).
[29 Aug 2008 16:13] Valerii Kravchuk
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)
[9 Feb 2009 13:08] Jørgen Løland
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.
[11 Feb 2009 9:45] Bugs System
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)
[11 Feb 2009 15:35] Chuck Bell
Patch reviewed. Comments sent. Returning to in-progress while awaiting answers to questions and a new patch.
[12 Feb 2009 9:08] Bugs System
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)
[13 Feb 2009 15:25] Chuck Bell
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.
[16 Feb 2009 12:19] Bugs System
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)
[26 Mar 2009 12:34] Bugs System
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)
[3 Apr 2009 3:19] Paul Dubois
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).