#At file:///export/home/tmp/wl4229new/mysql-6.0-backup/
2737 Hema Sridharan 2008-12-05
WL#4229(Test recording of backup and restore operations). Made some changes
as per Oystein's review comments.
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-05 18:35:49 +0000
@@ -2,6 +2,21 @@ 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;
@@ -10,6 +25,21 @@ 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;
@@ -25,13 +55,28 @@ ERROR HY000: Can't write to backup locat
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'
+Error # Unknown database 'foo'
BACKUP DATABASE test,foo,bdb,bar TO 'test.bak';
ERROR 42000: Unknown database 'foo,bar'
SHOW WARNINGS;
@@ -39,6 +84,21 @@ 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';
@@ -69,24 +129,84 @@ ERROR HY000: Database 'mysql' cannot be
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
@@ -99,6 +219,21 @@ ERROR HY000: Database 'mysql' cannot be
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;
@@ -117,6 +252,10 @@ SHOW WARNINGS;
Level Code Message
Error # Table 'mysql.backup_history' doesn't exist
Error # Cannot create backup/restore execution context
+SHOW ERRORS;
+Level Code Message
+Error # Table 'mysql.backup_history' doesn't exist
+Error # Cannot create backup/restore execution context
Restoring the table
CREATE TABLE mysql.backup_history LIKE test.ob_copy;
DROP TABLE test.ob_copy;
@@ -166,6 +305,21 @@ 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;
=== 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-05 18:35:49 +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-05 18:35:49 +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;
@@ -28,6 +36,16 @@ 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";
@@ -45,6 +63,16 @@ 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
# non-existent database
@@ -55,7 +83,9 @@ DROP DATABASE IF EXISTS bar;
-- error ER_BAD_DB_ERROR
BACKUP DATABASE foo TO 'test.bak';
+--replace_column 2 #
SHOW WARNINGS;
+
-- error ER_BAD_DB_ERROR
BACKUP DATABASE test,foo,bdb,bar TO 'test.bak';
--replace_column 2 #
@@ -65,6 +95,16 @@ SHOW WARNINGS;
-- 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.
@@ -131,6 +171,16 @@ 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
@@ -141,6 +191,16 @@ BACKUP DATABASE information_schema TO 't
--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
@@ -151,6 +211,16 @@ BACKUP DATABASE mysql, information_schem
--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
@@ -161,6 +231,16 @@ 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
@@ -181,6 +261,16 @@ BACKUP DATABASE mysql, information_schem
--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 +300,13 @@ DROP TABLE mysql.backup_history;
--echo Backup the database;
--error ER_BACKUP_PROGRESS_TABLES
BACKUP DATABASE test_ob_error TO 'ob_err.bak';
+--replace_column 2 #
+SHOW WARNINGS;
+
--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,6 +320,7 @@ 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 #
@@ -239,7 +333,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,14 +405,23 @@ 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
--echo Testing RESTORE ... OVERWRITE functionality
--echo See bug#34579
--echo
@@ -332,39 +434,39 @@ USE db1;
CREATE TABLE table1 (text VARCHAR(20));
INSERT INTO table1 VALUES ('Inserted before');
---echo
+--echo
--echo Backup database
--replace_column 1 #
BACKUP DATABASE db1 TO 'overwrite.bak';
---echo
+--echo
--echo Insert more data and display
INSERT INTO table1 VALUES ('Inserted after');
SELECT * FROM table1;
---echo
+--echo
--echo Restore without OVERWRITE flag; will fail
--error ER_RESTORE_DB_EXISTS
RESTORE FROM 'overwrite.bak';
---echo
+--echo
--echo Restore with OVERWRITE flag; will succeed
--replace_column 1 #
RESTORE FROM 'overwrite.bak' OVERWRITE;
---echo
+--echo
--echo Show that inserted value 2 is not there
SELECT * FROM table1;
DROP DATABASE db1;
---echo
+--echo
--echo Restore after deleting db; will succeed
--replace_column 1 #
RESTORE FROM 'overwrite.bak';
-
---echo
+--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-05 18:35:49 +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';
#
