MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Hema Sridharan Date:December 3 2008 7:26pm
Subject:bzr commit into mysql-6.0-backup branch (hema:2737) WL#4229
View as plain text  
#At file:///export/home/tmp/wl4229new/mysql-6.0-backup/

 2737 Hema Sridharan	2008-12-03
      WL#4229(Test recording of backup and restore operations). 
      Some modifications made in the tests according to Oystein's suggestions.
modified:
  mysql-test/suite/backup/r/backup_errors.result
  mysql-test/suite/backup/r/backup_logs.result
  mysql-test/suite/backup/t/backup_errors.test
  mysql-test/suite/backup/t/backup_logs.test

=== modified file 'mysql-test/suite/backup/r/backup_errors.result'
--- a/mysql-test/suite/backup/r/backup_errors.result	2008-11-25 17:44:19 +0000
+++ b/mysql-test/suite/backup/r/backup_errors.result	2008-12-03 19:24:42 +0000
@@ -2,43 +2,84 @@ DROP DATABASE IF EXISTS adb;
 DROP DATABASE IF EXISTS bdb;
 RESTORE FROM 'test.bak';
 ERROR HY000: Can't read backup location 'test.bak'
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "RESTORE FROM 'test%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	restore	test.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Can't read backup location 'test.bak'
+error
 CREATE DATABASE adb;
 CREATE DATABASE bdb;
 CREATE TABLE bdb.t1(a int) ENGINE=MEMORY;
 BACKUP DATABASE adb TO '';
 ERROR HY000: Malformed file path ''
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Malformed file path ''
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE adb TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Malformed file path ''
+error
 BACKUP DATABASE adb TO "bdb/t1.frm";
 ERROR HY000: Can't write to backup location 'bdb/t1.frm' (file already exists?)
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Can't write to backup location 'bdb/t1.frm' (file already exists?)
 BACKUP DATABASE adb TO "test.bak";
 backup_id
 #
-SHOW WARNINGS;
-Level	Code	Message
 BACKUP DATABASE adb TO "test.bak";
 ERROR HY000: Can't write to backup location 'test.bak' (file already exists?)
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Can't write to backup location 'test.bak' (file already exists?)
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE adb TO%";
+verify backup history and progress logs for backup_state.
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	test.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Can't write to backup location 'test.bak' (file already exists?)
+error
 DROP DATABASE IF EXISTS foo;
 DROP DATABASE IF EXISTS bar;
 BACKUP DATABASE foo TO 'test.bak';
 ERROR 42000: Unknown database 'foo'
-SHOW WARNINGS;
-Level	Code	Message
-Error	1049	Unknown database 'foo'
 BACKUP DATABASE test,foo,bdb,bar TO 'test.bak';
 ERROR 42000: Unknown database 'foo,bar'
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Unknown database 'foo,bar'
-BACKUP DATABASE foo,test,bar,foo TO 'test.bak';
-ERROR 42000: Not unique database: 'foo'
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE test,foo,bdb,bar TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation,backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	test.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Unknown database 'foo,bar'
+error
 use adb;
 create table t1 (a int);
 create procedure p1() backup database test to 'test.bak';
@@ -66,39 +107,96 @@ DROP DATABASE bdb;
 Backup of mysql, information_schema scenario 1
 BACKUP DATABASE mysql TO 't.bak';
 ERROR HY000: Database 'mysql' cannot be included in a backup
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Database 'mysql' cannot be included in a backup
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	t.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Database 'mysql' cannot be included in a backup
+error
 Backup of mysql, information_schema scenario 2
 BACKUP DATABASE information_schema TO 't.bak';
 ERROR HY000: Database 'information_schema' cannot be included in a backup
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Database 'information_schema' cannot be included in a backup
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE information_schema TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	t.bak
+SELECT  notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Database 'information_schema' cannot be included in a backup
+error
 Backup of mysql, information_schema scenario 3
 BACKUP DATABASE mysql, information_schema TO 't.bak';
 ERROR HY000: Database 'mysql' cannot be included in a backup
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Database 'mysql' cannot be included in a backup
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql, information_schema TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	t.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Database 'mysql' cannot be included in a backup
+error
 Backup of mysql, information_schema scenario 4
 BACKUP DATABASE mysql, test TO 't.bak';
 ERROR HY000: Database 'mysql' cannot be included in a backup
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Database 'mysql' cannot be included in a backup
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql, test TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	t.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Database 'mysql' cannot be included in a backup
+error
 Backup of mysql, information_schema scenario 5
 BACKUP DATABASE information_schema, test TO 't.bak';
 ERROR HY000: Database 'information_schema' cannot be included in a backup
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Database 'information_schema' cannot be included in a backup
 Backup of mysql, information_schema scenario 6
 BACKUP DATABASE mysql, information_schema, test TO 't.bak';
 ERROR HY000: Database 'mysql' cannot be included in a backup
-SHOW WARNINGS;
-Level	Code	Message
-Error	#	Database 'mysql' cannot be included in a backup
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql, information_schema, test TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	t.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Database 'mysql' cannot be included in a backup
+error
 Making copies of progress tables.
 CREATE TABLE IF NOT EXISTS test.ob_copy LIKE mysql.backup_history;
 CREATE TABLE IF NOT EXISTS test.obp_copy LIKE mysql.backup_progress;
@@ -113,7 +211,7 @@ DROP TABLE mysql.backup_history;
 Backup the database;
 BACKUP DATABASE test_ob_error TO 'ob_err.bak';
 ERROR HY000: Can't open the backup logs as tables. Check 'mysql.backup_history' and 'mysql.backup_progress' or run mysql_upgrade to repair.
-SHOW WARNINGS;
+SHOW ERRORS;
 Level	Code	Message
 Error	#	Table 'mysql.backup_history' doesn't exist
 Error	#	Cannot create backup/restore execution context
@@ -124,7 +222,7 @@ DROP TABLE mysql.backup_progress;
 Backup the database;
 BACKUP DATABASE test_ob_error TO 'ob_err.bak';
 ERROR HY000: Can't open the backup logs as tables. Check 'mysql.backup_history' and 'mysql.backup_progress' or run mysql_upgrade to repair.
-SHOW WARNINGS;
+SHOW ERRORS;
 Level	Code	Message
 Error	#	Table 'mysql.backup_progress' doesn't exist
 Error	#	Cannot create backup/restore execution context
@@ -166,54 +264,22 @@ trigger metadata
 SET SESSION DEBUG='+d,backup_fail_add_trigger';
 BACKUP DATABASE db1 TO 'bup_db1.bak';
 ERROR HY000: Failed to obtain meta-data for trigger `db1`.`trg`
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE db1 TO%";
+verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+backup_state	operation	backup_file
+error	backup	bup_db1.bak
+SELECT notes FROM mysql.backup_progress
+WHERE backup_id=@bup_id;
+notes
+starting
+running
+Failed to obtain meta-data for trigger `db1`.`trg`
+error
 SET DEBUG_SYNC= 'reset';
 DROP DATABASE db1;
 
 Done testing for Bug#38624
-
-Testing RESTORE ... OVERWRITE functionality
-See bug#34579
-
-Initialize
-CREATE DATABASE db1;
-USE db1;
-CREATE TABLE table1 (text VARCHAR(20));
-INSERT INTO table1 VALUES ('Inserted before');
-
-Backup database
-BACKUP DATABASE db1 TO 'overwrite.bak';
-backup_id
-#
-
-Insert more data and display
-INSERT INTO table1 VALUES ('Inserted after');
-SELECT * FROM table1;
-text
-Inserted before
-Inserted after
-
-Restore without OVERWRITE flag; will fail
-RESTORE FROM 'overwrite.bak';
-ERROR HY000: Database 'db1' already exists. Use OVERWRITE flag to overwrite.
-
-Restore with OVERWRITE flag; will succeed
-RESTORE FROM 'overwrite.bak' OVERWRITE;
-backup_id
-#
-
-Show that inserted value 2 is not there
-SELECT * FROM table1;
-text
-Inserted before
-DROP DATABASE db1;
-
-Restore after deleting db; will succeed
-RESTORE FROM 'overwrite.bak';
-backup_id
-#
-
-Show that inserted value 2 is not there
-SELECT * FROM table1;
-text
-Inserted before
-DROP DATABASE db1;

=== modified file 'mysql-test/suite/backup/r/backup_logs.result'
--- a/mysql-test/suite/backup/r/backup_logs.result	2008-11-17 09:57:51 +0000
+++ b/mysql-test/suite/backup/r/backup_logs.result	2008-12-03 19:24:42 +0000
@@ -1,11 +1,11 @@
 SET DEBUG_SYNC= 'RESET';
 
-Checking character set for backup tables 
+Checking character set for backup tables
 
-SELECT column_name, character_set_name 
-FROM information_schema.columns 
-WHERE table_name LIKE 'backup_history' 
-AND character_set_name LIKE 'utf8' 
+SELECT column_name, character_set_name
+FROM information_schema.columns
+WHERE table_name LIKE 'backup_history'
+  AND character_set_name LIKE 'utf8'
 ORDER BY column_name;
 column_name	character_set_name
 backup_file	utf8
@@ -19,10 +19,10 @@ operation	utf8
 username	utf8
 user_comment	utf8
 
-SELECT column_name, character_set_name 
-FROM information_schema.columns 
-WHERE table_name LIKE 'backup_progress' 
-AND character_set_name LIKE 'utf8' 
+SELECT column_name, character_set_name
+FROM information_schema.columns
+WHERE table_name LIKE 'backup_progress'
+  AND character_set_name LIKE 'utf8'
 ORDER BY column_name;
 column_name	character_set_name
 notes	utf8
@@ -31,12 +31,67 @@ object	utf8
 Now starting real tests
 
 DROP DATABASE IF EXISTS backup_logs;
-PURGE BACKUP LOGS;
+Check backup logs when log_backup_output is TABLE and FILE
+SET @@global.log_backup_output = 'TABLE,FILE';
+CREATE USER 'tom'@'localhost' IDENTIFIED BY 'abc';
+GRANT ALL ON *.* TO 'tom'@'localhost' WITH GRANT OPTION;
+SHOW GRANTS FOR 'tom'@'localhost';
+Grants for tom@localhost
+GRANT ALL PRIVILEGES ON *.* TO 'tom'@'localhost' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WITH GRANT OPTION
+SELECT user, host, password FROM mysql.user WHERE user='tom';
+user	host	password
+tom	localhost	*0D3CED9BEC10A777AEC23CCC353A8C08A633045E
+SELECT CURRENT_USER();
+CURRENT_USER()
+tom@localhost
 CREATE DATABASE backup_logs;
-con1: Create table and new users.
+con1: Activate sync points for the backup statement.
+SET DEBUG_SYNC= 'after_backup_log_init     SIGNAL started   WAIT_FOR do_run';
+SET DEBUG_SYNC= 'after_backup_start_backup SIGNAL running   WAIT_FOR finish';
+Perform backup database operation with database alone.
+BACKUP DATABASE backup_logs TO 'backup_logs1.bak';
+con default: Wait for the backup to be started.
+SET DEBUG_SYNC= 'now WAIT_FOR started';
+Let backup step to running state.
+SET DEBUG_SYNC= 'now SIGNAL do_run WAIT_FOR running';
+con default: Let backup finish.
+SET DEBUG_SYNC= 'now SIGNAL finish';
+con1: Finish backup command
+backup_id
+#
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE backup_logs TO%";
+SELECT operation,num_objects, username, command FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+operation	num_objects	username	command
+backup	0	tom	BACKUP DATABASE backup_logs TO 'backup_logs1.bak'
+SELECT CURRENT_USER();
+CURRENT_USER()
+root@localhost
+Perform Backup and verify the username as 'root' in backup history log
+BACKUP DATABASE backup_logs TO 'backup_logs1.bak';
+backup_id
+#
+Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE backup_logs TO%";
+SELECT operation,num_objects, username, command FROM mysql.backup_history
+WHERE backup_id=@bup_id;
+operation	num_objects	username	command
+backup	0	root	BACKUP DATABASE backup_logs TO 'backup_logs1.bak'
+
+From the above tables we can notice that num_objects shows '0' if only DB
+is included in backup image(BUG#39109)
+
+Include all objects in database(Databases, tables, procedures and 
+functions, views, triggers and events) and perform backup operation.
+con1: Create tables 
 CREATE TABLE backup_logs.t1 (a char(30)) ENGINE=MYISAM;
 CREATE TABLE backup_logs.t2 (a char(30)) ENGINE=INNODB;
 CREATE TABLE backup_logs.t3 (a char(30)) ENGINE=MEMORY;
+CREATE TABLE backup_logs.t4(id INT, name CHAR(20))ENGINE=BLACKHOLE;
+CREATE TABLE backup_logs.t1_res (id BIGINT UNSIGNED NOT NULL) ENGINE=MEMORY;
 INSERT INTO backup_logs.t1 VALUES ("01 Test #1 - progress");
 INSERT INTO backup_logs.t1 VALUES ("02 Test #1 - progress");
 INSERT INTO backup_logs.t1 VALUES ("03 Test #1 - progress");
@@ -54,57 +109,98 @@ INSERT INTO backup_logs.t3 VALUES ("01 T
 INSERT INTO backup_logs.t3 VALUES ("02 Test #1 - progress");
 INSERT INTO backup_logs.t3 VALUES ("03 Test #1 - progress");
 INSERT INTO backup_logs.t3 VALUES ("04 Test #1 - progress");
+INSERT INTO backup_logs.t4 VALUES(1,'aa1'),(2,'aa2'),(3,'aa3');
+SELECT * FROM backup_logs.t4;
+id	name
+create all objects like views, procedures, functions, triggers
+and events.
+** create view **
+CREATE VIEW backup_logs.v1 AS SELECT * FROM backup_logs.t1;
+CREATE VIEW backup_logs.vv AS SELECT * FROM backup_logs.v1;
+** create triggers **
+CREATE TRIGGER backup_logs.trg AFTER INSERT ON backup_logs.t1 FOR EACH ROW
+BEGIN
+INSERT INTO backup_logs.t3 VALUES('Test objects count');
+END;||
+** create procedures **
+CREATE PROCEDURE backup_logs.p1()
+BEGIN
+SELECT * FROM backup_logs.t1;
+END;
+||
+** create functions **
+CREATE FUNCTION backup_logs.f1() RETURNS INTEGER
+BEGIN
+RETURN (SELECT COUNT(*) FROM backup_logs.t1);
+END;
+||
+** create event **
+CREATE EVENT backup_logs.e1 ON SCHEDULE EVERY 1 YEAR DO
+DELETE FROM objects.t4 WHERE id=10;
+
 Do backup of database
 con2: Activate sync points for the backup statement.
 SET DEBUG_SYNC= 'after_backup_log_init     SIGNAL started   WAIT_FOR do_run';
 SET DEBUG_SYNC= 'after_backup_start_backup SIGNAL phase1    WAIT_FOR backup';
 SET DEBUG_SYNC= 'after_backup_validated    SIGNAL validated WAIT_FOR do_phase2';
 SET DEBUG_SYNC= 'after_backup_binlog       SIGNAL phase2    WAIT_FOR finish';
-Start using a known backup id for a more definitive test.
-SET SESSION debug="+d,set_backup_id";
-con2: Send backup command. 
-con2: Backup id = 500.
+con2: Send backup command.
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 con1: Wait for the backup to be started.
 SET DEBUG_SYNC= 'now WAIT_FOR started';
+
 con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
-notes
-starting
+backup progress tables will always show start_time, stop_time,total_bytes
+and progress as '0' for all phases of backup operation.
+BUG#39356 Backup progress table details aren't updated properly
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_progress;
+INSERT INTO backup_logs.t1_res (id) VALUES (@bup_id);
+SELECT total_bytes, progress, notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
+total_bytes	progress	notes
+0	0	starting
 con1: Let backup step to running state.
 SET DEBUG_SYNC= 'now SIGNAL do_run WAIT_FOR phase1';
 con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
-notes
-starting
-running
+SELECT total_bytes, progress,notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
+total_bytes	progress	notes
+0	0	starting
+0	0	running
 con1: Let backup do the backup phase1.
 SET DEBUG_SYNC= 'now SIGNAL backup WAIT_FOR validated';
 con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
-notes
-starting
-running
-validity point
+SELECT total_bytes, progress, notes FROM mysql.backup_progress
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
+total_bytes	progress	notes
+0	0	starting
+0	0	running
+0	0	validity point
 con1: Let backup do the backup phase2.
 SET DEBUG_SYNC= 'now SIGNAL do_phase2 WAIT_FOR phase2';
 con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
-notes
-starting
-running
-validity point
-vp time
-running
+SELECT total_bytes, progress, notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
+total_bytes	progress	notes
+0	0	starting
+0	0	running
+0	0	validity point
+0	0	vp time
+0	0	running
 con1: Let backup finish.
 SET DEBUG_SYNC= 'now SIGNAL finish';
 con2: Finish backup command
 backup_id
-500
-FLUSH BACKUP LOGS;
-Turn off debugging session.
-SET SESSION debug="-d";
-SELECT * FROM mysql.backup_history WHERE backup_id = 500;;
+#
+From backup_history log we will notice that "drivers" column will show 
+Myisam, snapshot, default and no-data drivers 
+"error_num" will be '0' as both backup and restore was successful
+"num_objects" count is always 5(as there are 5 tables in database).
+It does not list other objects from the backup image(BUG#39109)
+SHOW VARIABLES LIKE 'log_backup_output';
+Variable_name	Value
+log_backup_output	FILE,TABLE
+SELECT ob.* FROM mysql.backup_history AS ob JOIN backup_logs.t1_res AS t1 ON ob.backup_id = t1.id;;
 backup_id	#
 process_id	#
 binlog_pos	#
@@ -112,19 +208,20 @@ binlog_file	#
 backup_state	complete
 operation	backup
 error_num	0
-num_objects	3
-total_bytes	3971
+num_objects	5
+total_bytes	5225
 validity_point_time	#
 start_time	#
 stop_time	#
 host_or_server_name	localhost
-username	root
-backup_file	#
+username	tom
+backup_file	backup_logs_orig.bak
 backup_file_path	#
 user_comment	
 command	BACKUP DATABASE backup_logs to 'backup_logs_orig.bak'
-drivers	MyISAM, Snapshot, Default
-SELECT * FROM mysql.backup_progress WHERE backup_id = 500;
+drivers	MyISAM, Default, Snapshot, Nodata
+SELECT obp.* FROM mysql.backup_progress AS obp JOIN 
+backup_logs.t1_res AS t1 ON obp.backup_id = t1.id;
 backup_id	object	start_time	stop_time	total_bytes	progress	error_num	notes
 #	backup kernel	#	#	0	0	0	starting
 #	backup kernel	#	#	0	0	0	running
@@ -132,34 +229,43 @@ backup_id	object	start_time	stop_time	to
 #	backup kernel	#	#	0	0	0	vp time
 #	backup kernel	#	#	0	0	0	running
 #	backup kernel	#	#	0	0	0	complete
+FLUSH BACKUP LOGS;
+DELETE FROM backup_logs.t1_res;
 con2: Activate sync points for the backup statement.
 SET DEBUG_SYNC= 'after_backup_log_init      SIGNAL started WAIT_FOR do_run';
 SET DEBUG_SYNC= 'after_backup_start_restore SIGNAL running WAIT_FOR finish';
 con2: Send restore command.
-con2: Backup id = 501.
 RESTORE FROM 'backup_logs_orig.bak' OVERWRITE;
 con1: Wait for the restore to be started.
 SET DEBUG_SYNC= 'now WAIT_FOR started';
 con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 501;
-notes
-starting
+select * from backup_logs.t1_res;
+id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_progress;
+INSERT INTO backup_logs.t1_res (id) VALUES (@bup_id);
+SELECT total_bytes, progress,notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
+total_bytes	progress	notes
+0	0	starting
 con1: Let restore step to running state.
 SET DEBUG_SYNC= 'now SIGNAL do_run WAIT_FOR running';
 con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 501;
-notes
-starting
-running
+SELECT total_bytes, progress,notes FROM mysql.backup_progress
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
+total_bytes	progress	notes
+0	0	starting
+0	0	running
 con1: Let restore do its job and finish.
 SET DEBUG_SYNC= 'now SIGNAL finish';
 con2: Finish restore command
 backup_id
-501
-FLUSH BACKUP LOGS;
+#
 SET DEBUG_SYNC= 'now SIGNAL complete';
 SET DEBUG_SYNC= 'now WAIT_FOR complete';
-SELECT * FROM mysql.backup_history WHERE backup_id = 501;;
+DELETE FROM backup_logs.t1_res;
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history WHERE command LIKE "RESTORE FROM%";
+INSERT INTO backup_logs.t1_res (id) VALUES (@bup_id);
+SELECT ob.* FROM mysql.backup_history AS ob JOIN backup_logs.t1_res AS t1 ON ob.backup_id = t1.id;;
 backup_id	#
 process_id	#
 binlog_pos	#
@@ -167,38 +273,40 @@ binlog_file	#
 backup_state	complete
 operation	restore
 error_num	0
-num_objects	3
-total_bytes	1483
+num_objects	5
+total_bytes	1493
 validity_point_time	#
 start_time	#
 stop_time	#
 host_or_server_name	localhost
-username	root
-backup_file	#
+username	tom
+backup_file	backup_logs_orig.bak
 backup_file_path	#
 user_comment	
 command	RESTORE FROM 'backup_logs_orig.bak' OVERWRITE
-drivers	MyISAM, Snapshot, Default
-SELECT * FROM mysql.backup_progress WHERE backup_id = 501;
+drivers	MyISAM, Default, Snapshot, Nodata
+SELECT obp.* FROM mysql.backup_progress AS obp JOIN backup_logs.t1_res AS t1 ON obp.backup_id = t1.id;
 backup_id	object	start_time	stop_time	total_bytes	progress	error_num	notes
 #	backup kernel	#	#	0	0	0	starting
 #	backup kernel	#	#	0	0	0	running
 #	backup kernel	#	#	0	0	0	complete
+FLUSH BACKUP LOGS;
 SET DEBUG_SYNC= 'RESET';
-The backup id for this command should be 502.
+SET SESSION debug="d,set_backup_id";
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 backup_id
-502
-The backup id for this command should be 503.
+500
+SET SESSION debug="d";
+The backup id for this command should be 501.
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 backup_id
-503
-The backup id for this command should be 504.
+501
+The backup id for this command should be 502.
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 backup_id
-504
-The backup id for this command should be 505.
+502
+The backup id for this command should be 503.
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 backup_id
-505
+503
 DROP DATABASE backup_logs;

=== modified file 'mysql-test/suite/backup/t/backup_errors.test'
--- a/mysql-test/suite/backup/t/backup_errors.test	2008-11-25 17:44:19 +0000
+++ b/mysql-test/suite/backup/t/backup_errors.test	2008-12-03 19:24:42 +0000
@@ -3,9 +3,7 @@
 --source include/have_debug_sync.inc
 
 # Check that BACKUP/RESTORE commands correctly report errors 
-#
-# TODO: When we know how to do that, check that the backup progress table
-# contains appropriate rows when errors have been detected.
+# and are updated in backup history and progress logs
 
 --disable_warnings
 DROP DATABASE IF EXISTS adb;
@@ -18,6 +16,16 @@ DROP DATABASE IF EXISTS bdb;
 --error ER_BACKUP_READ_LOC
 RESTORE FROM 'test.bak';
 
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "RESTORE FROM 'test%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
+
 CREATE DATABASE adb;
 CREATE DATABASE bdb;
 CREATE TABLE bdb.t1(a int) ENGINE=MEMORY;
@@ -25,25 +33,37 @@ CREATE TABLE bdb.t1(a int) ENGINE=MEMORY
 # invalid location
 --error ER_BAD_PATH
 BACKUP DATABASE adb TO '';
---replace_column 2 #
-SHOW WARNINGS;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE adb TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 # don't overwrite existing files
 --error ER_BACKUP_WRITE_LOC
 BACKUP DATABASE adb TO "bdb/t1.frm";
---replace_column 2 #
-SHOW WARNINGS;
 
 --replace_column 1 #
 BACKUP DATABASE adb TO "test.bak";
---replace_column 2 #
-SHOW WARNINGS;
 
 # don't overwrite existing backup image
 --error ER_BACKUP_WRITE_LOC
 BACKUP DATABASE adb TO "test.bak";
---replace_column 2 #
-SHOW WARNINGS;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE adb TO%";
+
+--echo verify backup history and progress logs for backup_state.
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 --remove_file $MYSQLTEST_VARDIR/master-data/test.bak
 
@@ -55,15 +75,18 @@ DROP DATABASE IF EXISTS bar;
 
 -- error ER_BAD_DB_ERROR
 BACKUP DATABASE foo TO 'test.bak';
-SHOW WARNINGS;
 -- error ER_BAD_DB_ERROR
 BACKUP DATABASE test,foo,bdb,bar TO 'test.bak';
---replace_column 2 #
-SHOW WARNINGS;
 
-# repeated database
--- error ER_NONUNIQ_DB
-BACKUP DATABASE foo,test,bar,foo TO 'test.bak';
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE test,foo,bdb,bar TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation,backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 # Test that BACKUP/RESTORE statements are disable inside stored routines,
 # triggers and events.
@@ -128,8 +151,16 @@ DROP DATABASE bdb;
 --echo Backup of mysql, information_schema scenario 1
 --error ER_BACKUP_CANNOT_INCLUDE_DB
 BACKUP DATABASE mysql TO 't.bak';
---replace_column 2 #
-SHOW WARNINGS;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/t.bak
@@ -138,8 +169,16 @@ SHOW WARNINGS;
 --echo Backup of mysql, information_schema scenario 2
 --error ER_BACKUP_CANNOT_INCLUDE_DB
 BACKUP DATABASE information_schema TO 't.bak';
---replace_column 2 #
-SHOW WARNINGS;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE information_schema TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT  notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/t.bak
@@ -148,8 +187,16 @@ SHOW WARNINGS;
 --echo Backup of mysql, information_schema scenario 3
 --error ER_BACKUP_CANNOT_INCLUDE_DB
 BACKUP DATABASE mysql, information_schema TO 't.bak';
---replace_column 2 #
-SHOW WARNINGS;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql, information_schema TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/t.bak
@@ -158,8 +205,16 @@ SHOW WARNINGS;
 --echo Backup of mysql, information_schema scenario 4
 --error ER_BACKUP_CANNOT_INCLUDE_DB
 BACKUP DATABASE mysql, test TO 't.bak';
---replace_column 2 #
-SHOW WARNINGS;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql, test TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/t.bak
@@ -168,8 +223,6 @@ SHOW WARNINGS;
 --echo Backup of mysql, information_schema scenario 5
 --error ER_BACKUP_CANNOT_INCLUDE_DB
 BACKUP DATABASE information_schema, test TO 't.bak';
---replace_column 2 #
-SHOW WARNINGS;
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/t.bak
@@ -178,8 +231,16 @@ SHOW WARNINGS;
 --echo Backup of mysql, information_schema scenario 6
 --error ER_BACKUP_CANNOT_INCLUDE_DB
 BACKUP DATABASE mysql, information_schema, test TO 't.bak';
---replace_column 2 #
-SHOW WARNINGS;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE mysql, information_schema, test TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/t.bak
@@ -210,10 +271,11 @@ DROP TABLE mysql.backup_history;
 --echo Backup the database;
 --error ER_BACKUP_PROGRESS_TABLES
 BACKUP DATABASE test_ob_error TO 'ob_err.bak';
+
 --error 0,1
 --remove_file $MYSQLTEST_VARDIR/master-data/ob_err.bak
 --replace_column 2 #
-SHOW WARNINGS;
+SHOW ERRORS;
 
 # Restore the table
 --echo Restoring the table
@@ -227,10 +289,11 @@ DROP TABLE mysql.backup_progress;
 --echo Backup the database;
 --error ER_BACKUP_PROGRESS_TABLES
 BACKUP DATABASE test_ob_error TO 'ob_err.bak';
+
 --error 0,1
 --remove_file $MYSQLTEST_VARDIR/master-data/ob_err.bak
 --replace_column 2 #
-SHOW WARNINGS;
+SHOW ERRORS;
 
 # Restore the table
 --echo Restoring the table
@@ -239,7 +302,6 @@ DROP TABLE test.obp_copy;
 
 DROP DATABASE test_ob_error;
 
-
 --echo
 --echo Bug#38624
 --echo Test that backup fails with error if database files are removed 
@@ -312,59 +374,18 @@ SET SESSION DEBUG='+d,backup_fail_add_tr
 --error ER_BACKUP_GET_META_TRIGGER
 BACKUP DATABASE db1 TO 'bup_db1.bak';
 
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE db1 TO%";
+
+--echo verify backup history and progress logs:
+SELECT backup_state,operation, backup_file FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+SELECT notes FROM mysql.backup_progress
+     WHERE backup_id=@bup_id;
+
 SET DEBUG_SYNC= 'reset';
 DROP DATABASE db1;
 
 --echo
 --echo Done testing for Bug#38624
-
-
---echo 
---echo Testing RESTORE ... OVERWRITE functionality
---echo See bug#34579
---echo
-
---source include/not_embedded.inc
-
---echo Initialize
-CREATE DATABASE db1;
-USE db1;
-CREATE TABLE table1 (text VARCHAR(20));
-INSERT INTO table1 VALUES ('Inserted before');
-
---echo 
---echo Backup database
---replace_column 1 #
-BACKUP DATABASE db1 TO 'overwrite.bak';
-
---echo 
---echo Insert more data and display
-INSERT INTO table1 VALUES ('Inserted after');
-SELECT * FROM table1;
-
---echo 
---echo Restore without OVERWRITE flag; will fail
---error ER_RESTORE_DB_EXISTS
-RESTORE FROM 'overwrite.bak';
-
---echo 
---echo Restore with OVERWRITE flag; will succeed
---replace_column 1 #
-RESTORE FROM 'overwrite.bak' OVERWRITE;
-
---echo 
---echo Show that inserted value 2 is not there
-SELECT * FROM table1;
-
-DROP DATABASE db1;
-
---echo 
---echo Restore after deleting db; will succeed
---replace_column 1 #
-RESTORE FROM 'overwrite.bak';
-
---echo 
---echo Show that inserted value 2 is not there
-SELECT * FROM table1;
-
-DROP DATABASE db1;

=== modified file 'mysql-test/suite/backup/t/backup_logs.test'
--- a/mysql-test/suite/backup/t/backup_logs.test	2008-11-17 09:57:51 +0000
+++ b/mysql-test/suite/backup/t/backup_logs.test	2008-12-03 19:24:42 +0000
@@ -1,5 +1,5 @@
 #
-# This test includes tests for ensuring the backup progress tables
+# This test includes tests for ensuring the backup progress and history tables
 # are updated.
 #
 
@@ -13,27 +13,27 @@ SET DEBUG_SYNC= 'RESET';
 
 # bug#33836 backup tables should have charset utf8
 
---echo 
---echo Checking character set for backup tables 
---echo 
-
-SELECT column_name, character_set_name 
-FROM information_schema.columns 
-WHERE table_name LIKE 'backup_history' 
-  AND character_set_name LIKE 'utf8' 
+--echo
+--echo Checking character set for backup tables
+--echo
+
+SELECT column_name, character_set_name
+FROM information_schema.columns
+WHERE table_name LIKE 'backup_history'
+  AND character_set_name LIKE 'utf8'
 ORDER BY column_name;
 
---echo 
+--echo
 
-SELECT column_name, character_set_name 
-FROM information_schema.columns 
-WHERE table_name LIKE 'backup_progress' 
-  AND character_set_name LIKE 'utf8' 
+SELECT column_name, character_set_name
+FROM information_schema.columns
+WHERE table_name LIKE 'backup_progress'
+  AND character_set_name LIKE 'utf8'
 ORDER BY column_name;
 
---echo 
+--echo
 --echo Now starting real tests
---echo 
+--echo
 
 #
 # Preparative cleanup.
@@ -44,47 +44,157 @@ DROP DATABASE IF EXISTS backup_logs;
 --remove_file $MYSQLTEST_VARDIR/master-data/backup_logs_orig.bak;
 --enable_warnings
 
-PURGE BACKUP LOGS;
+--echo Check backup logs when log_backup_output is TABLE and FILE
+SET @@global.log_backup_output = 'TABLE,FILE';
 
-connect (con1,localhost,root,,);
-connect (con2,localhost,root,,);
+#Create users and assign privileges
+CREATE USER 'tom'@'localhost' IDENTIFIED BY 'abc';
+GRANT ALL ON *.* TO 'tom'@'localhost' WITH GRANT OPTION; 
+
+#Checking the grant priveleges
+SHOW GRANTS FOR 'tom'@'localhost';
+SELECT user, host, password FROM mysql.user WHERE user='tom';
+
+# Verify different username entry in backup_history logs.
+
+connect (con1,localhost,tom,abc);
+connect (con2,localhost,tom,abc);
+connect (con3,localhost,root,);
 
 #
-# Test 1 - Check output of backup.
+# Test 1 - Check output of backup and verify different columns in
+#          backup history and progress logs. 
 #
 
 connection con1;
-
+SELECT CURRENT_USER();
 CREATE DATABASE backup_logs;
 
---echo con1: Create table and new users.
+--echo con1: Activate sync points for the backup statement.
+SET DEBUG_SYNC= 'after_backup_log_init     SIGNAL started   WAIT_FOR do_run';
+SET DEBUG_SYNC= 'after_backup_start_backup SIGNAL running   WAIT_FOR finish';
+
+--echo Perform backup database operation with database alone.
+send BACKUP DATABASE backup_logs TO 'backup_logs1.bak';
+
+connection default;
+
+--echo con default: Wait for the backup to be started.
+SET DEBUG_SYNC= 'now WAIT_FOR started';
+--echo Let backup step to running state.
+SET DEBUG_SYNC= 'now SIGNAL do_run WAIT_FOR running';
+
+--echo con default: Let backup finish.
+SET DEBUG_SYNC= 'now SIGNAL finish';
+
+connection con1;
+--echo con1: Finish backup command
+--replace_column 1 #
+reap;
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE backup_logs TO%";
+SELECT operation,num_objects, username, command FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+--remove_file $MYSQLTEST_VARDIR/master-data/backup_logs1.bak
+
+connection con3;
+SELECT CURRENT_USER(); 
+--echo Perform Backup and verify the username as 'root' in backup history log
+--replace_column 1 #
+BACKUP DATABASE backup_logs TO 'backup_logs1.bak';
+
+--echo Get last backup_id
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history
+WHERE command LIKE "BACKUP DATABASE backup_logs TO%";
+SELECT operation,num_objects, username, command FROM mysql.backup_history
+     WHERE backup_id=@bup_id;
+--remove_file $MYSQLTEST_VARDIR/master-data/backup_logs1.bak
+
+--echo
+--echo From the above tables we can notice that num_objects shows '0' if only DB
+--echo is included in backup image(BUG#39109)
+--echo
+
+# BUG#39109: Mysql Online Backup table doesn't show correct num_object count
+# Once BUG#39109 is fixed, all the objects should be listed in the num_object
+# count of backup_history log
+
+--echo Include all objects in database(Databases, tables, procedures and 
+--echo functions, views, triggers and events) and perform backup operation.
+
+connection con1;
+--echo con1: Create tables 
+
+# Tables are created using Myisam(Native driver), Innodb(Consistent Snapshot 
+# driver), Memory(Default driver) and Blackhole(no-data driver). Ensure that 
+# drivers column indicates all types of drivers for backup database operation.
 
 CREATE TABLE backup_logs.t1 (a char(30)) ENGINE=MYISAM;
 CREATE TABLE backup_logs.t2 (a char(30)) ENGINE=INNODB;
 CREATE TABLE backup_logs.t3 (a char(30)) ENGINE=MEMORY;
+CREATE TABLE backup_logs.t4(id INT, name CHAR(20))ENGINE=BLACKHOLE;
+CREATE TABLE backup_logs.t1_res (id BIGINT UNSIGNED NOT NULL) ENGINE=MEMORY;
 
-INSERT INTO backup_logs.t1 VALUES ("01 Test #1 - progress"); 
-INSERT INTO backup_logs.t1 VALUES ("02 Test #1 - progress"); 
-INSERT INTO backup_logs.t1 VALUES ("03 Test #1 - progress"); 
-INSERT INTO backup_logs.t1 VALUES ("04 Test #1 - progress"); 
-INSERT INTO backup_logs.t1 VALUES ("05 Test #1 - progress"); 
-INSERT INTO backup_logs.t1 VALUES ("06 Test #1 - progress"); 
-INSERT INTO backup_logs.t1 VALUES ("07 Test #1 - progress"); 
-
-INSERT INTO backup_logs.t2 VALUES ("01 Test #1 - progress"); 
-INSERT INTO backup_logs.t2 VALUES ("02 Test #1 - progress"); 
-INSERT INTO backup_logs.t2 VALUES ("03 Test #1 - progress"); 
-INSERT INTO backup_logs.t2 VALUES ("04 Test #1 - progress"); 
-INSERT INTO backup_logs.t2 VALUES ("05 Test #1 - progress"); 
-INSERT INTO backup_logs.t2 VALUES ("06 Test #1 - progress"); 
-
-INSERT INTO backup_logs.t3 VALUES ("01 Test #1 - progress"); 
-INSERT INTO backup_logs.t3 VALUES ("02 Test #1 - progress"); 
-INSERT INTO backup_logs.t3 VALUES ("03 Test #1 - progress"); 
-INSERT INTO backup_logs.t3 VALUES ("04 Test #1 - progress"); 
+INSERT INTO backup_logs.t1 VALUES ("01 Test #1 - progress");
+INSERT INTO backup_logs.t1 VALUES ("02 Test #1 - progress");
+INSERT INTO backup_logs.t1 VALUES ("03 Test #1 - progress");
+INSERT INTO backup_logs.t1 VALUES ("04 Test #1 - progress");
+INSERT INTO backup_logs.t1 VALUES ("05 Test #1 - progress");
+INSERT INTO backup_logs.t1 VALUES ("06 Test #1 - progress");
+INSERT INTO backup_logs.t1 VALUES ("07 Test #1 - progress");
+
+INSERT INTO backup_logs.t2 VALUES ("01 Test #1 - progress");
+INSERT INTO backup_logs.t2 VALUES ("02 Test #1 - progress");
+INSERT INTO backup_logs.t2 VALUES ("03 Test #1 - progress");
+INSERT INTO backup_logs.t2 VALUES ("04 Test #1 - progress");
+INSERT INTO backup_logs.t2 VALUES ("05 Test #1 - progress");
+INSERT INTO backup_logs.t2 VALUES ("06 Test #1 - progress");
+
+INSERT INTO backup_logs.t3 VALUES ("01 Test #1 - progress");
+INSERT INTO backup_logs.t3 VALUES ("02 Test #1 - progress");
+INSERT INTO backup_logs.t3 VALUES ("03 Test #1 - progress");
+INSERT INTO backup_logs.t3 VALUES ("04 Test #1 - progress");
+
+INSERT INTO backup_logs.t4 VALUES(1,'aa1'),(2,'aa2'),(3,'aa3');
+SELECT * FROM backup_logs.t4;
+
+--echo create all objects like views, procedures, functions, triggers
+--echo and events.
+
+--echo ** create view **
+CREATE VIEW backup_logs.v1 AS SELECT * FROM backup_logs.t1;
+CREATE VIEW backup_logs.vv AS SELECT * FROM backup_logs.v1;
+
+--echo ** create triggers **
+delimiter ||;
+CREATE TRIGGER backup_logs.trg AFTER INSERT ON backup_logs.t1 FOR EACH ROW
+BEGIN
+ INSERT INTO backup_logs.t3 VALUES('Test objects count');
+END;||
+
+--echo ** create procedures **
+CREATE PROCEDURE backup_logs.p1()
+BEGIN
+  SELECT * FROM backup_logs.t1;
+END;
+||
+
+--echo ** create functions **
+CREATE FUNCTION backup_logs.f1() RETURNS INTEGER
+BEGIN
+RETURN (SELECT COUNT(*) FROM backup_logs.t1);
+END;
+||
+delimiter ;||
+
+--echo ** create event **
+CREATE EVENT backup_logs.e1 ON SCHEDULE EVERY 1 YEAR DO
+ DELETE FROM objects.t4 WHERE id=10;
 
+--echo
 --echo Do backup of database
-
 connection con2;
 
 --echo con2: Activate sync points for the backup statement.
@@ -93,11 +203,7 @@ SET DEBUG_SYNC= 'after_backup_start_back
 SET DEBUG_SYNC= 'after_backup_validated    SIGNAL validated WAIT_FOR do_phase2';
 SET DEBUG_SYNC= 'after_backup_binlog       SIGNAL phase2    WAIT_FOR finish';
 
---echo Start using a known backup id for a more definitive test.
-SET SESSION debug="+d,set_backup_id";
-
---echo con2: Send backup command. 
---echo con2: Backup id = 500.
+--echo con2: Send backup command.
 send BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 
 connection con1;
@@ -105,46 +211,73 @@ connection con1;
 --echo con1: Wait for the backup to be started.
 SET DEBUG_SYNC= 'now WAIT_FOR started';
 
+--echo
 --echo con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
+--echo backup progress tables will always show start_time, stop_time,total_bytes
+--echo and progress as '0' for all phases of backup operation.
+--echo BUG#39356 Backup progress table details aren't updated properly
+# Correct backup/restore times and progress should be indicated in the backup
+# logs once this bug is fixed
+
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_progress;
+INSERT INTO backup_logs.t1_res (id) VALUES (@bup_id);
+SELECT total_bytes, progress, notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
 
 --echo con1: Let backup step to running state.
 SET DEBUG_SYNC= 'now SIGNAL do_run WAIT_FOR phase1';
 
 --echo con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
+SELECT total_bytes, progress,notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
 
 --echo con1: Let backup do the backup phase1.
 SET DEBUG_SYNC= 'now SIGNAL backup WAIT_FOR validated';
 
 --echo con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
+SELECT total_bytes, progress, notes FROM mysql.backup_progress
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
 
 --echo con1: Let backup do the backup phase2.
 SET DEBUG_SYNC= 'now SIGNAL do_phase2 WAIT_FOR phase2';
 
 --echo con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 500;
+SELECT total_bytes, progress, notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
 
 --echo con1: Let backup finish.
 SET DEBUG_SYNC= 'now SIGNAL finish';
 
 connection con2;
 --echo con2: Finish backup command
+--replace_column 1 #
 reap;
 
-FLUSH BACKUP LOGS;
-
---echo Turn off debugging session.
-SET SESSION debug="-d";
-
 connection con1;
 
+--echo From backup_history log we will notice that "drivers" column will show 
+--echo Myisam, snapshot, default and no-data drivers 
+--echo "error_num" will be '0' as both backup and restore was successful
+--echo "num_objects" count is always 5(as there are 5 tables in database).
+--echo It does not list other objects from the backup image(BUG#39109)
+ 
 #Show results
---replace_column 1 # 2 # 3 # 4 # 10 # 11 # 12 # 15 # 16 #
---query_vertical SELECT * FROM mysql.backup_history WHERE backup_id = 500;
+
+SHOW VARIABLES LIKE 'log_backup_output';
+--replace_column 1 # 2 # 3 # 4 # 10 # 11 # 12 # 16 # 
+--query_vertical SELECT ob.* FROM mysql.backup_history AS ob JOIN backup_logs.t1_res AS t1 ON ob.backup_id = t1.id;
 --replace_column 1 # 3 # 4 #
-SELECT * FROM mysql.backup_progress WHERE backup_id = 500;
+SELECT obp.* FROM mysql.backup_progress AS obp JOIN 
+backup_logs.t1_res AS t1 ON obp.backup_id = t1.id;
+
+FLUSH BACKUP LOGS;
+--file_exists $MYSQLTEST_VARDIR/master-data/backup_history.log
+--file_exists $MYSQLTEST_VARDIR/master-data/backup_progress.log
+
+#cat_file $MYSQLTEST_VARDIR/master-data/backup_history.log;
+#cat_file $MYSQLTEST_VARDIR/master-data/backup_progress.log;
+
+DELETE FROM backup_logs.t1_res;
 
 connection con2;
 
@@ -153,7 +286,6 @@ SET DEBUG_SYNC= 'after_backup_log_init  
 SET DEBUG_SYNC= 'after_backup_start_restore SIGNAL running WAIT_FOR finish';
 
 --echo con2: Send restore command.
---echo con2: Backup id = 501.
 send RESTORE FROM 'backup_logs_orig.bak' OVERWRITE;
 
 connection con1;
@@ -162,23 +294,26 @@ connection con1;
 SET DEBUG_SYNC= 'now WAIT_FOR started';
 
 --echo con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 501;
+select * from backup_logs.t1_res;
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_progress;
+INSERT INTO backup_logs.t1_res (id) VALUES (@bup_id);
+SELECT total_bytes, progress,notes FROM mysql.backup_progress 
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
 
 --echo con1: Let restore step to running state.
 SET DEBUG_SYNC= 'now SIGNAL do_run WAIT_FOR running';
 
 --echo con1: Display progress
-SELECT notes FROM mysql.backup_progress WHERE backup_id = 501;
-
+SELECT total_bytes, progress,notes FROM mysql.backup_progress
+AS ob JOIN backup_logs.t1_res as t1 ON ob.backup_id = t1.id;
 --echo con1: Let restore do its job and finish.
 SET DEBUG_SYNC= 'now SIGNAL finish';
 
 connection con2;
 --echo con2: Finish restore command
+--replace_column 1 #
 reap;
 
-FLUSH BACKUP LOGS;
-
 SET DEBUG_SYNC= 'now SIGNAL complete';
 
 connection con1;
@@ -186,27 +321,51 @@ connection con1;
 SET DEBUG_SYNC= 'now WAIT_FOR complete';
 
 #Show results
---replace_column 1 # 2 # 3 # 4 # 10 # 11 # 12 # 15 # 16 #
---query_vertical SELECT * FROM mysql.backup_history WHERE backup_id = 501;
+DELETE FROM backup_logs.t1_res;
+SELECT MAX(backup_id) INTO @bup_id FROM mysql.backup_history WHERE command LIKE "RESTORE FROM%";
+INSERT INTO backup_logs.t1_res (id) VALUES (@bup_id);
+--replace_column 1 # 2 # 3 # 4 # 10 # 11 # 12 # 16 #
+--query_vertical SELECT ob.* FROM mysql.backup_history AS ob JOIN backup_logs.t1_res AS t1 ON ob.backup_id = t1.id;
+
+# Correct backup/restore times and progress should be indicated in the backup
+# logs once this bug 39356 is fixed
+
 --replace_column 1 # 3 # 4 #
-SELECT * FROM mysql.backup_progress WHERE backup_id = 501;
+SELECT obp.* FROM mysql.backup_progress AS obp JOIN backup_logs.t1_res AS t1 ON obp.backup_id = t1.id;
+
+FLUSH BACKUP LOGS;
+--file_exists $MYSQLTEST_VARDIR/master-data/backup_history.log
+--file_exists $MYSQLTEST_VARDIR/master-data/backup_progress.log
+#cat_file $MYSQLTEST_VARDIR/master-data/backup_history.log;
+#cat_file $MYSQLTEST_VARDIR/master-data/backup_progress.log;
 
 SET DEBUG_SYNC= 'RESET';
 
+#
+# Now test read of backupid with known id using debug insertion
+#
+SET SESSION debug="d,set_backup_id";
+
 --remove_file $MYSQLTEST_VARDIR/master-data/backup_logs_orig.bak
---echo The backup id for this command should be 502.
+
+#
+# The first backup will cause the value to be set to 500 and written to file.
+# The second backup will read the value (500) and increment it.
+#--replace_column 1 #
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 
+SET SESSION debug="d";
+
 --remove_file $MYSQLTEST_VARDIR/master-data/backup_logs_orig.bak
---echo The backup id for this command should be 503.
+--echo The backup id for this command should be 501.
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 
 --remove_file $MYSQLTEST_VARDIR/master-data/backup_logs_orig.bak
---echo The backup id for this command should be 504.
+--echo The backup id for this command should be 502.
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 
 --remove_file $MYSQLTEST_VARDIR/master-data/backup_logs_orig.bak
---echo The backup id for this command should be 505.
+--echo The backup id for this command should be 503.
 BACKUP DATABASE backup_logs to 'backup_logs_orig.bak';
 
 #

Thread
bzr commit into mysql-6.0-backup branch (hema:2737) WL#4229Hema Sridharan3 Dec
  • Re: bzr commit into mysql-6.0-backup branch (hema:2737) WL#4229Rafal Somla4 Dec