| 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: | |
| Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
| Version: | mysql-6.0-backup, mysql-6.0 | OS: | Linux |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
[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]
Valeriy 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).
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.