#At file:///export/home/tmp/rpl/wl4568/mysql-6.0-backup/
2699 Hema Sridharan 2008-09-27
WL#4568 : Moving all backup tests from suite to backup_engines suite
removed:
mysql-test/suite/backup/combinations
mysql-test/suite/backup/include/
mysql-test/suite/backup/include/backup_engine.inc
mysql-test/suite/backup/r/backup_functions.result
mysql-test/suite/backup/r/backup_online_testing.result
mysql-test/suite/backup/r/backup_procedures.result
mysql-test/suite/backup/r/backup_triggers.result
mysql-test/suite/backup/t/backup_functions.test
mysql-test/suite/backup/t/backup_online_testing.test
mysql-test/suite/backup/t/backup_procedures.test
mysql-test/suite/backup/t/backup_triggers.test
added:
mysql-test/suite/backup_engines/
mysql-test/suite/backup_engines/combinations
mysql-test/suite/backup_engines/include/
mysql-test/suite/backup_engines/include/backup_engine.inc
mysql-test/suite/backup_engines/r/
mysql-test/suite/backup_engines/r/backup_functions.result
mysql-test/suite/backup_engines/r/backup_online_testing.result
mysql-test/suite/backup_engines/r/backup_procedures.result
mysql-test/suite/backup_engines/r/backup_triggers.result
mysql-test/suite/backup_engines/t/
mysql-test/suite/backup_engines/t/backup_functions.test
mysql-test/suite/backup_engines/t/backup_online_testing.test
mysql-test/suite/backup_engines/t/backup_procedures.test
mysql-test/suite/backup_engines/t/backup_triggers.test
=== removed file 'mysql-test/suite/backup/combinations'
--- a/mysql-test/suite/backup/combinations 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/combinations 1970-01-01 00:00:00 +0000
@@ -1,11 +0,0 @@
-[myisam]
---default-storage-engine=myisam
-
-[falcon]
---default-storage-engine=falcon
-
-[memory]
---default-storage-engine=memory
-
-[innodb]
---default-storage-engine=innodb
=== removed directory 'mysql-test/suite/backup/include'
=== removed file 'mysql-test/suite/backup/include/backup_engine.inc'
--- a/mysql-test/suite/backup/include/backup_engine.inc 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/include/backup_engine.inc 1970-01-01 00:00:00 +0000
@@ -1,9 +0,0 @@
-###############################################################################
-# Author: Hema
-# Date: 2008-04-01
-# Purpose:To define the engine variable.
-###############################################################################
---replace_column 2 #
-SHOW VARIABLES LIKE 'storage_engine';
-let $ENGINE= query_get_value(SHOW VARIABLES LIKE 'storage_engine', Value, 1);
-
=== removed file 'mysql-test/suite/backup/r/backup_functions.result'
--- a/mysql-test/suite/backup/r/backup_functions.result 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/r/backup_functions.result 1970-01-01 00:00:00 +0000
@@ -1,601 +0,0 @@
-SHOW VARIABLES LIKE 'storage_engine';
-Variable_name Value
-storage_engine #
-
-starting the test for backup
-
-DROP DATABASE IF EXISTS bup_sfs;
-CREATE DATABASE bup_sfs;
-USE bup_sfs;
-SET SQL_MODE=TRADITIONAL;
-Creating table
-CREATE TABLE bup_sfs.players(
-playerno INT not null PRIMARY KEY,
-name CHAR(10),
-Bdate DATE,
-sex CHAR(2),
-joined YEAR
-);
-CREATE TABLE bup_sfs.teams(
-teamno INT NOT NULL PRIMARY KEY,
-playerno INT,
-division CHAR(10),
-FOREIGN KEY(playerno) REFERENCES players(playerno)
-);
-CREATE TABLE bup_sfs.matches(
-Matchno INT NOT NULL PRIMARY KEY,
-teamno INT ,
-playerno INT,
-won INT,
-lost INT,
-FOREIGN KEY(teamno) REFERENCES teams(teamno),
-FOREIGN KEY(playerno) REFERENCES players(playerno)
-);
-CREATE TABLE bup_sfs.penalties(
-paymentno INT not null PRIMARY KEY,
-playerno INT,
-paymentdate DATE,
-amount FLOAT,
-FOREIGN KEY(playerno) REFERENCES players(playerno)
-);
-Load Data in tables
-INSERT INTO bup_sfs.players VALUES
-(1,'aa1','1948-10-09','M','1960'),(2,'aa2','1950-05-09','M','1965'),
-(3,'aa3','1953-10-10','F','1970'),(4,'aa4','1970-10-19','M','1985'),
-(5,'aa5','1972-04-28','F','1994'),(6,'aa6','1945-01-26','M','1960');
-INSERT INTO bup_sfs.teams VALUES(1,3,'first'),(2,4,'division');
-INSERT INTO bup_sfs.matches VALUES
-(1,1,2,3,1),(2,2,1,4,2),(3,1,5,0,1),(4,2,4,3,0),(5,2,5,2,2),
-(6,1,3,3,1),(7,2,1,4,2),(8,1,5,0,1),(9,2,2,3,0),(10,2,2,2,2);
-INSERT INTO bup_sfs.penalties VALUES
-(1,5,'1980-10-10',100.23),(2,3,'1967-12-10',123.23),
-(3,3,'1983-05-25',25.25), (4,4,'1981-11-10',210.23) ,
-(5,5,'1980-12-12',300), (6,1,'1984-07-02',10),
-(7,1,'1985-10-10',23.90);
-CREATE FUNCTION number_of_players() RETURNS INTEGER
-BEGIN
-RETURN (SELECT COUNT(*) FROM players);
-END;
-||
-CREATE FUNCTION number_of_players1() RETURNS INTEGER
-BEGIN
-RETURN (SELECT COUNT(*) FROM players WHERE playerno > 2);
-END;
-||
-CREATE FUNCTION fcount() RETURNS INTEGER
-BEGIN
-DECLARE v_out INT;
-SELECT count(*) FROM matches INTO v_out;
-RETURN v_out;
-END;
-||
-CREATE FUNCTION dollars(amount FLOAT(7,2)) RETURNS FLOAT(7,2)
-BEGIN
-RETURN amount * (1/0.8);
-END;
-||
-SELECT number_of_players();
-number_of_players()
-6
-SELECT number_of_players1();
-number_of_players1()
-4
-SELECT fcount();
-fcount()
-10
-SELECT paymentno,amount, dollars(amount) FROM penalties;
-paymentno amount dollars(amount)
-1 100.23 125.29
-2 123.23 154.04
-3 25.25 31.56
-4 210.23 262.79
-5 300 375.00
-6 10 12.50
-7 23.9 29.87
-creating stored functions which is not dependent on table
-CREATE FUNCTION square_area(side tinyINT)
-RETURNS FLOAT
-RETURN side * side;
-SELECT square_area(11);
-square_area(11)
-121
-showing objects and create statements.
-SHOW CREATE FUNCTION number_of_players;;
-Function number_of_players
-sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
-Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
-BEGIN
-RETURN (SELECT COUNT(*) FROM players);
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TABLE players;;
-Table players
-Create Table CREATE TABLE `players` (
- `playerno` int(11) NOT NULL,
- `name` char(10) DEFAULT NULL,
- `Bdate` date DEFAULT NULL,
- `sex` char(2) DEFAULT NULL,
- `joined` year(4) DEFAULT NULL,
- PRIMARY KEY (`playerno`)
-) ENGINE=ENGINE DEFAULT CHARSET=latin1
-describe penalties;
-Field Type Null Key Default Extra
-paymentno int(11) NO PRI NULL
-playerno int(11) YES MUL NULL
-paymentdate date YES NULL
-amount float YES NULL
-SHOW FUNCTION STATUS;;
-Db bup_sfs
-Name dollars
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name fcount
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players1
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name square_area
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-backup data
-BACKUP DATABASE bup_sfs TO 'bup_sfs.bak';
-backup_id
-#
-dropping database.
-DROP DATABASE bup_sfs;
-Restoring Database
-RESTORE FROM 'bup_sfs.bak';
-backup_id
-#
-SELECT @@SQL_MODE;
-@@SQL_MODE
-STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
-showing objects and create statements
-SHOW CREATE DATABASE bup_sfs;;
-Database bup_sfs
-Create Database CREATE DATABASE `bup_sfs` /*!40100 DEFAULT CHARACTER SET latin1 */
-SHOW CREATE FUNCTION number_of_players;;
-Function number_of_players
-sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
-Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
-BEGIN
-RETURN (SELECT COUNT(*) FROM players);
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-describe penalties;
-Field Type Null Key Default Extra
-paymentno int(11) NO PRI NULL
-playerno int(11) YES MUL NULL
-paymentdate date YES NULL
-amount float YES NULL
-describe matches;
-Field Type Null Key Default Extra
-Matchno int(11) NO PRI NULL
-teamno int(11) YES MUL NULL
-playerno int(11) YES MUL NULL
-won int(11) YES NULL
-lost int(11) YES NULL
-SHOW FUNCTION STATUS;;
-Db bup_sfs
-Name dollars
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name fcount
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players1
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name square_area
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-INSERT INTO bup_sfs.players VALUES
-(57,'brown','1971-08-17','M','1985'),(95,'Miller','1963-05-14','M','1972');
-INSERT INTO bup_sfs.matches VALUES(11,2,6,0,3);
-SELECT square_area(12);
-square_area(12)
-144
-SELECT number_of_players();
-number_of_players()
-8
-SELECT number_of_players1();
-number_of_players1()
-6
-SELECT fcount();
-fcount()
-11
-SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno<=3;
-paymentno amount dollars(amount)
-1 100.23 125.29
-2 123.23 154.04
-3 25.25 31.56
-DROP DATABASE bup_sfs;
-change SQL_MODE and then perform RESTORE operation
-SET SQL_MODE=ANSI;
-RESTORE FROM 'bup_sfs.bak';
-backup_id
-#
-SELECT @@SQL_MODE;
-@@SQL_MODE
-REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
-showing objects and create statements
-SHOW CREATE DATABASE bup_sfs;;
-Database bup_sfs
-Create Database CREATE DATABASE "bup_sfs" /*!40100 DEFAULT CHARACTER SET latin1 */
-SHOW CREATE FUNCTION number_of_players;;
-Function number_of_players
-sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
-Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
-BEGIN
-RETURN (SELECT COUNT(*) FROM players);
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW FUNCTION STATUS;;
-Db bup_sfs
-Name dollars
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name fcount
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players1
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name square_area
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SELECT square_area(13);
-square_area(13)
-169
-SELECT number_of_players();
-number_of_players()
-6
-SELECT fcount();
-fcount()
-10
-DROP DATABASE bup_sfs;
-change SQL_MODE and then perform RESTORE
-SET SQL_MODE=MAXDB;
-RESTORE FROM 'bup_sfs.bak';
-backup_id
-#
-SELECT @@SQL_MODE;
-@@SQL_MODE
-PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
-showing objects and create statements
-SHOW CREATE DATABASE bup_sfs;;
-Database bup_sfs
-Create Database CREATE DATABASE "bup_sfs" /*!40100 DEFAULT CHARACTER SET latin1 */
-SHOW CREATE FUNCTION number_of_players;;
-Function number_of_players
-sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
-Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
-BEGIN
-RETURN (SELECT COUNT(*) FROM players);
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW FUNCTION STATUS;;
-Db bup_sfs
-Name dollars
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name fcount
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players1
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name square_area
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SELECT square_area(14);
-square_area(14)
-196
-SELECT number_of_players();
-number_of_players()
-6
-SELECT fcount();
-fcount()
-10
-SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno >=3;
-paymentno amount dollars(amount)
-3 25.25 31.56
-4 210.23 262.79
-5 300 375.00
-6 10 12.50
-7 23.9 29.87
-DROP DATABASE bup_sfs;
-change SQL_MODE and then perform RESTORE
-SET SQL_MODE=' ';
-RESTORE FROM 'bup_sfs.bak';
-backup_id
-#
-SELECT @@SQL_MODE;
-@@SQL_MODE
-
-showing objects and create statements
-SHOW CREATE DATABASE bup_sfs;;
-Database bup_sfs
-Create Database CREATE DATABASE `bup_sfs` /*!40100 DEFAULT CHARACTER SET latin1 */
-SHOW CREATE FUNCTION number_of_players;;
-Function number_of_players
-sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
-Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
-BEGIN
-RETURN (SELECT COUNT(*) FROM players);
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TABLE players;;
-Table players
-Create Table CREATE TABLE `players` (
- `playerno` int(11) NOT NULL,
- `name` char(10) DEFAULT NULL,
- `Bdate` date DEFAULT NULL,
- `sex` char(2) DEFAULT NULL,
- `joined` year(4) DEFAULT NULL,
- PRIMARY KEY (`playerno`)
-) ENGINE=ENGINE DEFAULT CHARSET=latin1
-describe matches;
-Field Type Null Key Default Extra
-Matchno int(11) NO PRI NULL
-teamno int(11) YES MUL NULL
-playerno int(11) YES MUL NULL
-won int(11) YES NULL
-lost int(11) YES NULL
-SHOW FUNCTION STATUS;;
-Db bup_sfs
-Name dollars
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name fcount
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name number_of_players1
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sfs
-Name square_area
-Type FUNCTION
-Definer #
-Modified #
-Created #
-Security_type DEFINER
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SELECT square_area(20);
-square_area(20)
-400
-SELECT number_of_players();
-number_of_players()
-6
-SELECT fcount();
-fcount()
-10
-SELECT paymentno,amount, dollars(amount) FROM penalties;
-paymentno amount dollars(amount)
-1 100.23 125.29
-2 123.23 154.04
-3 25.25 31.56
-4 210.23 262.79
-5 300 375.00
-6 10 12.50
-7 23.9 29.87
-
-*** DROP bup_sfs DATABASE ****
-
-DROP DATABASE bup_sfs;
=== removed file 'mysql-test/suite/backup/r/backup_online_testing.result'
--- a/mysql-test/suite/backup/r/backup_online_testing.result 2008-06-30 19:40:33 +0000
+++ b/mysql-test/suite/backup/r/backup_online_testing.result 1970-01-01 00:00:00 +0000
@@ -1,1137 +0,0 @@
-SET DEBUG_SYNC= 'RESET';
-DROP DATABASE IF EXISTS bup_online;
-
-starting the test for backup
-
-CREATE DATABASE bup_online;
-Establishing connections
-** con1 to perform BACKUP AND RESTORE**
-** con2 and con2a to do INSERTS**
-** con3 to synchronize with BACKUP
-** con4 to do UPDATES**
-** con5 to do DELETES**
-** con6 to check table contents**
-
-con1: ***Creating Tables***
-CREATE TABLE t1(id int, details char(30))ENGINE=INNODB;
-INSERT INTO t1 VALUES(1,'testing1'),(2,'testing2'),(3,'testing3'),
-(4,'testing4'),(5,'testing5'),(6,'testing6'),(7,'tesing7'),(8,'testing8'),
-(9,'testing9'),(10,'testing10'),(11,'testing11'),(12,'testing12');
-CREATE TABLE t2(id int, info char(30))ENGINE=INNODB;
-INSERT INTO t2 VALUES(201, 'aa1'),(202,'aa2');
-CREATE TABLE t3(id int, name char(20))ENGINE=INNODB;
-INSERT INTO t3 VALUES(1,'xx1'),(2,'xx2'),(3,'xx3'),(4,'xx4'),(5,'xx5');
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-12
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-2
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-5
-
-**TEST1**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_data_init SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(100,'new value added100');
-INSERT INTO t1 VALUES(101,'new value added101');
-INSERT INTO t1 VALUES(102,'new value added102');
-
-con4: Updating Data
-UPDATE t2 SET info='updated values1' WHERE id=201;
-UPDATE t2 SET info='updated values2' WHERE id=202;
-
-con5: Deleting Data
-DELETE FROM t1 WHERE id=11;
-DELETE FROM t1 WHERE id=12;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(500,'online500');
-INSERT INTO t1 VALUES(600,'online600');
-
-con4: Updating Data
-UPDATE t1 SET details='ONLINE1' WHERE id=10;
-
-con5: Deleting Data
-DELETE FROM t2 WHERE id=201;
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 testing8
-9 testing9
-10 ONLINE1
-100 new value added100
-101 new value added101
-102 new value added102
-500 online500
-600 online600
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-15
-SELECT * FROM t2;
-id info
-202 updated values2
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-1
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 testing8
-9 testing9
-10 testing10
-100 new value added100
-101 new value added101
-102 new value added102
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-13
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-2
-
-**TEST2**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_data_prepare SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online1.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(103,'new value added103');
-INSERT INTO t2 VALUES(203,'Updated Values3');
-
-con4: Updating Data
-UPDATE t1 SET details='ONLINETEST' WHERE id=10;
-
-con5: Deleting Data
-DELETE FROM t3 WHERE id=5;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(700,'online700');
-
-con4: Updating Data
-UPDATE t3 SET name='yy' WHERE id=3;
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 testing8
-9 testing9
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-700 online700
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-15
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-3
-SELECT *FROM t3;
-id name
-1 xx1
-2 xx2
-3 yy
-4 xx4
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-4
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online1.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 testing8
-9 testing9
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-14
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-3
-SELECT *FROM t3;
-id name
-1 xx1
-2 xx2
-3 xx3
-4 xx4
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-4
-
-**TEST3**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_command SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(104,'new value added103');
-INSERT INTO t2 VALUES(204,'Updated Values3');
-
-con4: Updating Data
-UPDATE t1 SET details='ONLINETEST1' WHERE id=9;
-
-con5: Deleting Data
-DELETE FROM t3 WHERE id=4;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(800,'online800');
-
-con4: Updating Data
-UPDATE t3 SET name='zz' WHERE id=2;
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 testing8
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-800 online800
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-16
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-4
-SELECT *FROM t3;
-id name
-1 xx1
-2 zz
-3 xx3
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-3
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 testing8
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-15
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-4
-SELECT *FROM t3;
-id name
-1 xx1
-2 xx2
-3 xx3
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-3
-
-**TEST4**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_meta SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(105,'new value added105');
-INSERT INTO t2 VALUES(205,'Updated Values5');
-
-con4: Updating Data
-UPDATE t1 SET details='ONLINETEST1' WHERE id=8;
-
-con5: Deleting Data
-DELETE FROM t3 WHERE id=3;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(900,'online900');
-
-con4: Updating Data
-UPDATE t3 SET name='tt' WHERE id=2;
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-900 online900
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-17
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-5
-SELECT *FROM t3;
-id name
-1 xx1
-2 tt
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-2
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 tesing7
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-16
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-5
-SELECT *FROM t3;
-id name
-1 xx1
-2 xx2
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-2
-
-**TEST5**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_data SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(106,'new value added106');
-INSERT INTO t2 VALUES(206,'Updated Values6');
-
-con4: Updating Data
-UPDATE t1 SET details='ONLINETEST1' WHERE id=7;
-
-con5: Deleting Data
-DELETE FROM t3 WHERE id=2;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(1000,'online1000');
-
-con4: Updating Data
-UPDATE t3 SET name='uu' WHERE id=1;
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-1000 online1000
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-18
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 uu
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-1
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-17
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 xx1
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-1
-
-**TEST6**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_data_lock SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Starting Insert Data
-INSERT INTO t1 VALUES(107,'new value added107');
-
-con2a: Starting Insert Data
-INSERT INTO t2 VALUES(207,'Updated Values7');
-
-con4: Starting Update data
-UPDATE t1 SET details='ONLINETEST1' WHERE id=6;
-
-con5: Starting Delete data
-DELETE FROM t3 WHERE id=1;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Fetching result of INSERT and Insert more data
-INSERT INTO t1 VALUES(1010,'online1010');
-
-con2a: Fetching result of INSERT
-
-con4: Fetching result of UPDATE and Update more data
-UPDATE t2 SET info='t2 updated1' WHERE id=205;
-
-con5: Fetching result of DELETE
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 ONLINETEST1
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-107 new value added107
-1010 online1010
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-19
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 t2 updated1
-206 Updated Values6
-207 Updated Values7
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-7
-SELECT *FROM t3;
-id name
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-0
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-17
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 xx1
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-1
-
-**TEST7**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_data_unlock SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Starting Insert Data
-INSERT INTO t1 VALUES(108,'new value added108');
-
-con2a: Starting Insert Data
-INSERT INTO t3 VALUES(20,'zz20');
-
-con4: Starting Update data
-UPDATE t1 SET details='ONLINETEST1' WHERE id=4;
-
-con5: Starting Delete data
-DELETE FROM t3 WHERE id=1;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Fetching result of INSERT and Insert more data
-INSERT INTO t1 VALUES(1020,'online1020');
-
-con2a: Fetching result of INSERT
-
-con4: Fetching result of UPDATE and Update more data
-UPDATE t2 SET info='t2 updated2' WHERE id=206;
-
-con5: Fetching result of DELETE
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 ONLINETEST1
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-108 new value added108
-1020 online1020
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-19
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 t2 updated2
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-20 zz20
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-1
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-17
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 xx1
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-1
-
-**TEST8**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_data_finish SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(109,'new value added109');
-INSERT INTO t3 VALUES(30,'zz30');
-
-con4: Updating Data
-UPDATE t1 SET details='ONLINETEST1' WHERE id=3;
-
-con5: Deleting Data
-DELETE FROM t3 WHERE id=20;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(1030,'online1030');
-
-con4: Updating Data
-UPDATE t2 SET info='t2 updated3' WHERE id=207;
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 ONLINETEST1
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-109 new value added109
-1030 online1030
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-19
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 xx1
-30 zz30
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-2
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-17
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 xx1
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-1
-
-**TEST9**
-
-con1: Starting Backup Data
-SET DEBUG_SYNC= 'before_backup_done SIGNAL bup_sync
- WAIT_FOR bup_finish';
-BACKUP DATABASE bup_online TO 'bup_online.bak';
-
-con3: Waiting for BACKUP to reach the breakpoint
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(110,'new value added110');
-INSERT INTO t3 VALUES(40,'zz40');
-
-con4: Updating Data
-UPDATE t1 SET details='ONLINETEST1' WHERE id=2;
-
-con5: Deleting Data
-DELETE FROM t3 WHERE id=30;
-
-con3: Signalling that BACKUP can finish
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
-con1: Fetching result of BACKUP
-backup_id
-#
-
-con2: Inserting Data
-INSERT INTO t1 VALUES(1040,'online1040');
-
-con4: Updating Data
-UPDATE t2 SET info='t2 updated4' WHERE id=201;
-
-con6: **Checking the Data Contents before Restore**
-SELECT * FROM t1;
-id details
-1 testing1
-2 ONLINETEST1
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-110 new value added110
-1040 online1040
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-19
-SELECT * FROM t2;
-id info
-201 t2 updated4
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 xx1
-40 zz40
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-2
-
-con1: Dropping the database
-DROP DATABASE bup_online;
-con1: Restoring the database
-RESTORE FROM 'bup_online.bak';
-backup_id
-#
-
-*** RESULT AFTER RESTORE ***
-
-SELECT * FROM t1;
-id details
-1 testing1
-2 testing2
-3 testing3
-4 testing4
-5 testing5
-6 testing6
-7 ONLINETEST1
-8 ONLINETEST1
-9 ONLINETEST1
-10 ONLINETEST
-100 new value added100
-101 new value added101
-102 new value added102
-103 new value added103
-104 new value added103
-105 new value added105
-106 new value added106
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-17
-SELECT * FROM t2;
-id info
-201 updated values1
-202 updated values2
-203 Updated Values3
-204 Updated Values3
-205 Updated Values5
-206 Updated Values6
-SELECT COUNT(*) FROM t2;
-COUNT(*)
-6
-SELECT *FROM t3;
-id name
-1 xx1
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-1
-
-*** DROP bup_online DATABASE ****
-
-DROP DATABASE bup_online;
=== removed file 'mysql-test/suite/backup/r/backup_procedures.result'
--- a/mysql-test/suite/backup/r/backup_procedures.result 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/r/backup_procedures.result 1970-01-01 00:00:00 +0000
@@ -1,175 +0,0 @@
-SHOW VARIABLES LIKE 'storage_engine';
-Variable_name Value
-storage_engine #
-
-starting the test for backup
-
-DROP DATABASE IF EXISTS bup_sp;
-CREATE DATABASE bup_sp;
-USE bup_sp;
-*******Creating table******
-CREATE TABLE bup_sp.country(
-ccode CHAR(10),
-city VARCHAR(10)
-);
-*********loading data*********
-INSERT INTO bup_sp.country VALUES
-('IND','chennai'),('US','sfo'),('US','austin'),
-('IND','Bomb'),('US','Richmond'),('IND','hyd');
-SELECT * FROM bup_sp.country ORDER BY ccode, city;
-ccode city
-IND Bomb
-IND chennai
-IND hyd
-US austin
-US Richmond
-US sfo
-CREATE PROCEDURE countryinfo (IN loc CHAR(5))
-BEGIN
-SELECT * FROM country WHERE ccode=loc;
-END;
-||
-CREATE PROCEDURE dorepeat(p1 INT)
-BEGIN
-SET @x = 0;
-REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-END ;||
-CALL countryinfo('US');
-ccode city
-US sfo
-US austin
-US Richmond
-CALL countryinfo('IND');
-ccode city
-IND chennai
-IND Bomb
-IND hyd
-CALL dorepeat(1000);
-SELECT @x;
-@x
-1001
-showing objects and create statements.
-SHOW CREATE PROCEDURE countryinfo;;
-Procedure countryinfo
-sql_mode
-Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `countryinfo`(IN loc CHAR(5))
-BEGIN
-SELECT * FROM country WHERE ccode=loc;
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TABLE country;;
-Table country
-Create Table CREATE TABLE `country` (
- `ccode` char(10) DEFAULT NULL,
- `city` varchar(10) DEFAULT NULL
-) ENGINE=ENGINE DEFAULT CHARSET=latin1
-SHOW PROCEDURE STATUS;;
-Db bup_sp
-Name countryinfo
-Type PROCEDURE
-Definer #
-Modified #
-Created #
-Security_type #
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sp
-Name dorepeat
-Type PROCEDURE
-Definer #
-Modified #
-Created #
-Security_type #
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-backup data
-
-BACKUP DATABASE bup_sp TO 'bup_sp.bak';
-backup_id
-#
-dropping database.
-DROP DATABASE bup_sp;
-
-RESTORE FROM 'bup_sp.bak';
-backup_id
-#
-showing objects and create statements
-SHOW CREATE PROCEDURE countryinfo;;
-Procedure countryinfo
-sql_mode
-Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `countryinfo`(IN loc CHAR(5))
-BEGIN
-SELECT * FROM country WHERE ccode=loc;
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TABLE country;;
-Table country
-Create Table CREATE TABLE `country` (
- `ccode` char(10) DEFAULT NULL,
- `city` varchar(10) DEFAULT NULL
-) ENGINE=ENGINE DEFAULT CHARSET=latin1
-SHOW PROCEDURE STATUS;;
-Db bup_sp
-Name countryinfo
-Type PROCEDURE
-Definer #
-Modified #
-Created #
-Security_type #
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Db bup_sp
-Name dorepeat
-Type PROCEDURE
-Definer #
-Modified #
-Created #
-Security_type #
-Comment
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-INSERT INTO bup_sp.country VALUES
-('US','boston'),('IND','jaipur'),('US','natick');
-SELECT * FROM bup_sp.country ORDER BY ccode, city;
-ccode city
-IND Bomb
-IND chennai
-IND hyd
-IND jaipur
-US austin
-US boston
-US natick
-US Richmond
-US sfo
-CALL countryinfo('IND');
-ccode city
-IND chennai
-IND Bomb
-IND hyd
-IND jaipur
-CALL countryinfo('US');
-ccode city
-US sfo
-US austin
-US Richmond
-US boston
-US natick
-CALL dorepeat(99);
-SELECT @x;
-@x
-100
-
-*** DROP bup_sp DATABASE ****
-
-DROP DATABASE bup_sp;
=== removed file 'mysql-test/suite/backup/r/backup_triggers.result'
--- a/mysql-test/suite/backup/r/backup_triggers.result 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/r/backup_triggers.result 1970-01-01 00:00:00 +0000
@@ -1,775 +0,0 @@
-SHOW VARIABLES LIKE 'storage_engine';
-Variable_name Value
-storage_engine #
-
-starting the test for backup
-
-DROP DATABASE IF EXISTS bup_ts;
-CREATE DATABASE bup_ts;
-USE bup_ts;
-SELECT @@SQL_MODE;
-@@SQL_MODE
-
-*****Creating table cap ******
-CREATE TABLE bup_ts.cap(
-id SMALLINT,
-country CHAR(20),
-city VARCHAR(20),
-population BIGINT,
-capital CHAR(20)
-);
-******* Creating table city*****.
-CREATE TABLE bup_ts.city(
-id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-name VARCHAR(20),
-ccode CHAR(10),
-population bigint
-);
-*******Creating table dropcity *****
-CREATE TABLE bup_ts.dropcity(
-id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-name VARCHAR(20)
-);
-******Create Trigger for the table*******
-*******creating trigger with event before insert**********
-CREATE TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
-SET new.population= if(new.population<0,0,truncate(new.population,-3));||
-********creating trigger with event before Update******
-CREATE TRIGGER capu_tri BEFORE UPDATE ON cap FOR EACH ROW
-BEGIN
-SET @country=old.country;
-SET @capital_old=old.capital;
-SET @capital_new=new.capital;
-END;
-||
-*****creating trigger with event after delete********
-CREATE TRIGGER cityin_d AFTER
-DELETE ON city FOR EACH ROW
-INSERT INTO dropcity(id, name) VALUES(old.id, old.name);||
-Creating Tables
-CREATE TABLE bup_ts.t2(a CHAR(4));
-CREATE TABLE bup_ts.t3(a CHAR(4));
-CREATE TABLE bup_ts.t4(a CHAR(4));
-Creating Triggers
-Creating trigger with event after insert
-CREATE TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
-BEGIN
-INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
-END;
-||
-Creating trigger with event after Update
-CREATE TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
-BEGIN
-INSERT INTO t4 VALUES('J'),('O'),('B');
-END;
-||
-Load data in to table
-********Inserting Values in tables*********
-INSERT INTO bup_ts.cap VALUES
-(1,'IND','chn',87874646468,'deli'),(2,'US','Austin',-6466547,'DC'),
-(3,'Russia','moscow',76487623235682,'moscow'),(4,'IND','bang',-83875477,'deli'),
-(5,'US','sacramento',-388348,'DC'),(6,'IND','jammu',3848488,'deli'),
-(7,'Russia','xx',-373788,'moscow'),(8,'IND','hyd',647747,'deli');
-INSERT INTO bup_ts.city VALUES
-(1,'aa','AD',263768),(2,'bb','PO',8839898),(3,'cc','KL',898778),
-(4,'dd','IK',73287328),(5,'ee','YU',89399),(6,'ff','IL',90880),
-(7,'gg','TY',345),(8,'hh','WE',9239084);
-Updating the table to fire trigger with event update
-UPDATE cap SET capital='Delhi' WHERE country='Ind';
-UPDATE cap SET capital='Washington DC' WHERE country='US';
-SELECT @country as country, @capital_old as 'old capital',
-@capital_new as 'new capital';
-country old capital new capital
-US DC Washington DC
-showing the tables to check the trigger is fired with event insert
-SELECT * FROM bup_ts.cap ORDER BY city;
-id country city population capital
-2 US Austin 0 Washington DC
-4 IND bang 0 Delhi
-1 IND chn 87874646000 Delhi
-8 IND hyd 647000 Delhi
-6 IND jammu 3848000 Delhi
-3 Russia moscow 76487623235000 moscow
-5 US sacramento 0 Washington DC
-7 Russia xx 0 moscow
-selecting data from table city
-SELECT * FROM bup_ts.city ORDER BY name;
-id name ccode population
-1 aa AD 263768
-2 bb PO 8839898
-3 cc KL 898778
-4 dd IK 73287328
-5 ee YU 89399
-6 ff IL 90880
-7 gg TY 345
-8 hh WE 9239084
-deleting few rows from table city to fire trigger with event delete.
-DELETE FROM bup_ts.city WHERE name='dd';
-DELETE FROM bup_ts.city WHERE name='bb';
-
-Checking table contents of city and dropcity to verify if trigger is
-fired for event delete.
-SELECT * FROM city ORDER BY name;
-id name ccode population
-1 aa AD 263768
-3 cc KL 898778
-5 ee YU 89399
-6 ff IL 90880
-7 gg TY 345
-8 hh WE 9239084
-SELECT * FROM dropcity ORDER BY id;
-id name
-2 bb
-4 dd
-******Firing Trigger after insert and after update *********
-INSERT INTO bup_ts.t2 values('a'),('k'),('*'),('i');
-SELECT * FROM bup_ts.t3;
-a
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-UPDATE t2 SET a='*' WHERE a='k';
-SELECT * FROM bup_ts.t4;
-a
-J
-O
-B
-Show the data and Create statements
-showing objects and create statements.
-SHOW TRIGGERS;;
-Trigger cap_tri
-Event INSERT
-Table cap
-Statement SET new.population= if(new.population<0,0,truncate(new.population,-3))
-Timing BEFORE
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger capu_tri
-Event UPDATE
-Table cap
-Statement BEGIN
-SET @country=old.country;
-SET @capital_old=old.capital;
-SET @capital_new=new.capital;
-END
-Timing BEFORE
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger cityin_d
-Event DELETE
-Table city
-Statement INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger trai
-Event INSERT
-Table t2
-Statement BEGIN
-INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
-END
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger trau
-Event UPDATE
-Table t2
-Statement BEGIN
-INSERT INTO t4 VALUES('J'),('O'),('B');
-END
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER cap_tri;;
-Trigger cap_tri
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
-SET new.population= if(new.population<0,0,truncate(new.population,-3))
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER capu_tri;;
-Trigger capu_tri
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER capu_tri BEFORE UPDATE ON cap FOR EACH ROW
-BEGIN
-SET @country=old.country;
-SET @capital_old=old.capital;
-SET @capital_new=new.capital;
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER cityin_d;;
-Trigger cityin_d
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cityin_d AFTER
-DELETE ON city FOR EACH ROW
-INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER trai;;
-Trigger trai
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
-BEGIN
-INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER trau;;
-Trigger trau
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
-BEGIN
-INSERT INTO t4 VALUES('J'),('O'),('B');
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TABLE city;;
-Table city
-Create Table CREATE TABLE `city` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(20) DEFAULT NULL,
- `ccode` char(10) DEFAULT NULL,
- `population` bigint(20) DEFAULT NULL,
- PRIMARY KEY (`id`)
-) ENGINE=ENGINE AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
-SHOW CREATE TABLE t2;;
-Table t2
-Create Table CREATE TABLE `t2` (
- `a` char(4) DEFAULT NULL
-) ENGINE=ENGINE DEFAULT CHARSET=latin1
-backup data
-BACKUP DATABASE bup_ts TO 'bup_ts.bak';
-backup_id
-#
-dropping database.
-DROP DATABASE bup_ts;
-perform restore
-RESTORE FROM 'bup_ts.bak';
-backup_id
-#
-show data and create statements after Restore
-showing objects and create statements
-SHOW CREATE DATABASE bup_ts;;
-Database bup_ts
-Create Database CREATE DATABASE `bup_ts` /*!40100 DEFAULT CHARACTER SET latin1 */
-SHOW CREATE TRIGGER cap_tri;;
-Trigger cap_tri
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
-SET new.population= if(new.population<0,0,truncate(new.population,-3))
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER cityin_d;;
-Trigger cityin_d
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cityin_d AFTER
-DELETE ON city FOR EACH ROW
-INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER trai;;
-Trigger trai
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
-BEGIN
-INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TRIGGER trau;;
-Trigger trau
-sql_mode
-SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
-BEGIN
-INSERT INTO t4 VALUES('J'),('O'),('B');
-END
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW TRIGGERS;;
-Trigger cap_tri
-Event INSERT
-Table cap
-Statement SET new.population= if(new.population<0,0,truncate(new.population,-3))
-Timing BEFORE
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger capu_tri
-Event UPDATE
-Table cap
-Statement BEGIN
-SET @country=old.country;
-SET @capital_old=old.capital;
-SET @capital_new=new.capital;
-END
-Timing BEFORE
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger cityin_d
-Event DELETE
-Table city
-Statement INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger trai
-Event INSERT
-Table t2
-Statement BEGIN
-INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
-END
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger trau
-Event UPDATE
-Table t2
-Statement BEGIN
-INSERT INTO t4 VALUES('J'),('O'),('B');
-END
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Inserting some more values in the table to check the trigger with event insert
-INSERT INTO bup_ts.cap VALUES
-(9,'US','houston',-33399587677866656555655555655,'texas');
-Warnings:
-Warning 1264 Out of range value for column 'population' at row 1
-SELECT * FROM bup_ts.cap ORDER BY city;
-id country city population capital
-2 US Austin 0 Washington DC
-4 IND bang 0 Delhi
-1 IND chn 87874646000 Delhi
-9 US houston 0 texas
-8 IND hyd 647000 Delhi
-6 IND jammu 3848000 Delhi
-3 Russia moscow 76487623235000 moscow
-5 US sacramento 0 Washington DC
-7 Russia xx 0 moscow
-Deleting some rows from city to check the trigger with event delete
-DELETE FROM city WHERE name='aa';
-Checking table city and drop city
-SELECT * FROM bup_ts.city ORDER BY name;
-id name ccode population
-3 cc KL 898778
-5 ee YU 89399
-6 ff IL 90880
-7 gg TY 345
-8 hh WE 9239084
-SELECT * FROM bup_ts.dropcity ORDER BY id;
-id name
-1 aa
-2 bb
-4 dd
-INSERT INTO bup_ts.t2 VALUES('m'),('s1'),('s'),('q'),('l');
-SELECT * FROM bup_ts.t3;
-a
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-UPDATE t2 SET a='y' WHERE a='s1';
-SELECT * FROM bup_ts.t4;
-a
-J
-O
-B
-J
-O
-B
-DROP DATABASE bup_ts;
-Change sql mode and perform Restore and check the table contents.
-SET SQL_MODE=TRADITIONAL;
-RESTORE FROM 'bup_ts.bak';
-backup_id
-#
-SHOW TRIGGERS;;
-Trigger cap_tri
-Event INSERT
-Table cap
-Statement SET new.population= if(new.population<0,0,truncate(new.population,-3))
-Timing BEFORE
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger capu_tri
-Event UPDATE
-Table cap
-Statement BEGIN
-SET @country=old.country;
-SET @capital_old=old.capital;
-SET @capital_new=new.capital;
-END
-Timing BEFORE
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger cityin_d
-Event DELETE
-Table city
-Statement INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger trai
-Event INSERT
-Table t2
-Statement BEGIN
-INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
-END
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-Trigger trau
-Event UPDATE
-Table t2
-Statement BEGIN
-INSERT INTO t4 VALUES('J'),('O'),('B');
-END
-Timing AFTER
-Created NULL
-sql_mode
-Definer root@localhost
-character_set_client latin1
-collation_connection latin1_swedish_ci
-Database Collation latin1_swedish_ci
-SHOW CREATE TABLE t2;;
-Table t2
-Create Table CREATE TABLE `t2` (
- `a` char(4) DEFAULT NULL
-) ENGINE=ENGINE DEFAULT CHARSET=latin1
-SHOW CREATE TABLE city;;
-Table city
-Create Table CREATE TABLE `city` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(20) DEFAULT NULL,
- `ccode` char(10) DEFAULT NULL,
- `population` bigint(20) DEFAULT NULL,
- PRIMARY KEY (`id`)
-) ENGINE=ENGINE AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
-SHOW CREATE TABLE cap;;
-Table cap
-Create Table CREATE TABLE `cap` (
- `id` smallint(6) DEFAULT NULL,
- `country` char(20) DEFAULT NULL,
- `city` varchar(20) DEFAULT NULL,
- `population` bigint(20) DEFAULT NULL,
- `capital` char(20) DEFAULT NULL
-) ENGINE=ENGINE DEFAULT CHARSET=latin1
-Check contents after Restore and change of SQL mode
-INSERT INTO bup_ts.cap VALUES(10,'India','Daund',36637782899,'maharashtra');
-SELECT * FROM bup_ts.cap ORDER BY city;
-id country city population capital
-2 US Austin 0 Washington DC
-4 IND bang 0 Delhi
-1 IND chn 87874646000 Delhi
-10 India Daund 36637782000 maharashtra
-8 IND hyd 647000 Delhi
-6 IND jammu 3848000 Delhi
-3 Russia moscow 76487623235000 moscow
-5 US sacramento 0 Washington DC
-7 Russia xx 0 moscow
-INSERT INTO bup_ts.t2 VALUES('i'),('w');
-SELECT * FROM t3;
-a
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-UPDATE t2 SET a='^' WHERE a='w';
-SELECT * FROM t4;
-a
-J
-O
-B
-J
-O
-B
-DROP DATABASE bup_ts;
-change of SQL mode
-SET SQL_MODE=MAXDB;
-RESTORE FROM 'bup_ts.bak';
-backup_id
-#
-Check contents after Restore and change of SQL mode
-INSERT INTO bup_ts.cap VALUES(11,'UK','Edenburg',-36637782899,'london');
-SELECT * FROM bup_ts.cap ORDER BY city;
-id country city population capital
-2 US Austin 0 Washington DC
-4 IND bang 0 Delhi
-1 IND chn 87874646000 Delhi
-11 UK Edenburg 0 london
-8 IND hyd 647000 Delhi
-6 IND jammu 3848000 Delhi
-3 Russia moscow 76487623235000 moscow
-5 US sacramento 0 Washington DC
-7 Russia xx 0 moscow
-INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h');
-SELECT * FROM bup_ts.t3;
-a
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-UPDATE t2 SET a='p' WHERE a='h';
-SELECT * FROM bup_ts.t4;
-a
-J
-O
-B
-J
-O
-B
-DROP DATABASE bup_ts;
-Chnage SQL mode and then perform Restore.
-SET SQL_MODE=ANSI;
-RESTORE FROM 'bup_ts.bak';
-backup_id
-#
-Check contents after Restore and change of SQL mode
-INSERT INTO bup_ts.cap VALUES(12,'India','vooty',789,'Tamilnadu');
-SELECT * FROM bup_ts.cap ORDER BY city;
-id country city population capital
-2 US Austin 0 Washington DC
-4 IND bang 0 Delhi
-1 IND chn 87874646000 Delhi
-8 IND hyd 647000 Delhi
-6 IND jammu 3848000 Delhi
-3 Russia moscow 76487623235000 moscow
-5 US sacramento 0 Washington DC
-12 India vooty 0 Tamilnadu
-7 Russia xx 0 moscow
-INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h'),('b');
-SELECT * FROM bup_ts.t3;
-a
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-H
-E
-L
-L
-O
-UPDATE bup_ts.t2 SET a='#' WHERE a='w';
-SELECT * FROM bup_ts.t4;
-a
-J
-O
-B
-J
-O
-B
-
-*** DROP bup_ts DATABASE ****
-
-DROP DATABASE bup_ts;
=== removed file 'mysql-test/suite/backup/t/backup_functions.test'
--- a/mysql-test/suite/backup/t/backup_functions.test 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/t/backup_functions.test 1970-01-01 00:00:00 +0000
@@ -1,273 +0,0 @@
-###########################################################################
-# Author: Hema
-# Date: 2008-03-11
-# Purpose: To test the metadata consistency of object (stored functions)
-# after backup and Restore.
-###############################################################################
---source include/not_embedded.inc
---source suite/backup/include/backup_engine.inc
---source include/have_debug.inc
-
-connect (backup,localhost,root,,);
-connect (breakpoints,localhost,root,,);
-
-##############################################################
---echo
---echo starting the test for backup
---echo
-##############################################################
-
---error 0,1
---remove_file $MYSQLTEST_VARDIR/master-data/bup_sfs.bak
-
-#Create Database and object stored function for this test.
-
---disable_warnings
-DROP DATABASE IF EXISTS bup_sfs;
---enable_warnings
-
-CREATE DATABASE bup_sfs;
-USE bup_sfs;
-
-SET SQL_MODE=TRADITIONAL;
-
-#Create table and load with data.
-
---echo Creating table
-
-CREATE TABLE bup_sfs.players(
-playerno INT not null PRIMARY KEY,
-name CHAR(10),
-Bdate DATE,
-sex CHAR(2),
-joined YEAR
-);
-
-CREATE TABLE bup_sfs.teams(
-teamno INT NOT NULL PRIMARY KEY,
-playerno INT,
-division CHAR(10),
-FOREIGN KEY(playerno) REFERENCES players(playerno)
-);
-
-CREATE TABLE bup_sfs.matches(
-Matchno INT NOT NULL PRIMARY KEY,
-teamno INT ,
-playerno INT,
-won INT,
-lost INT,
-FOREIGN KEY(teamno) REFERENCES teams(teamno),
-FOREIGN KEY(playerno) REFERENCES players(playerno)
-);
-
-CREATE TABLE bup_sfs.penalties(
-paymentno INT not null PRIMARY KEY,
-playerno INT,
-paymentdate DATE,
-amount FLOAT,
-FOREIGN KEY(playerno) REFERENCES players(playerno)
-);
-
---echo Load Data in tables
-
-INSERT INTO bup_sfs.players VALUES
-(1,'aa1','1948-10-09','M','1960'),(2,'aa2','1950-05-09','M','1965'),
-(3,'aa3','1953-10-10','F','1970'),(4,'aa4','1970-10-19','M','1985'),
-(5,'aa5','1972-04-28','F','1994'),(6,'aa6','1945-01-26','M','1960');
-
-INSERT INTO bup_sfs.teams VALUES(1,3,'first'),(2,4,'division');
-
-INSERT INTO bup_sfs.matches VALUES
-(1,1,2,3,1),(2,2,1,4,2),(3,1,5,0,1),(4,2,4,3,0),(5,2,5,2,2),
-(6,1,3,3,1),(7,2,1,4,2),(8,1,5,0,1),(9,2,2,3,0),(10,2,2,2,2);
-
-INSERT INTO bup_sfs.penalties VALUES
-(1,5,'1980-10-10',100.23),(2,3,'1967-12-10',123.23),
-(3,3,'1983-05-25',25.25), (4,4,'1981-11-10',210.23) ,
-(5,5,'1980-12-12',300), (6,1,'1984-07-02',10),
-(7,1,'1985-10-10',23.90);
-
-#Create stored functions for the table
-
-delimiter ||;
-CREATE FUNCTION number_of_players() RETURNS INTEGER
-BEGIN
-RETURN (SELECT COUNT(*) FROM players);
-END;
-||
-
-CREATE FUNCTION number_of_players1() RETURNS INTEGER
-BEGIN
-RETURN (SELECT COUNT(*) FROM players WHERE playerno > 2);
-END;
-||
-
-CREATE FUNCTION fcount() RETURNS INTEGER
-BEGIN
- DECLARE v_out INT;
- SELECT count(*) FROM matches INTO v_out;
- RETURN v_out;
-END;
-||
-
-CREATE FUNCTION dollars(amount FLOAT(7,2)) RETURNS FLOAT(7,2)
-BEGIN
- RETURN amount * (1/0.8);
-END;
-||
-
-delimiter ;||
-
-#Excercise the objects
-
-SELECT number_of_players();
-SELECT number_of_players1();
-SELECT fcount();
-SELECT paymentno,amount, dollars(amount) FROM penalties;
---echo creating stored functions which is not dependent on table
-#BUG#34868 Backup: restore failure if two procedures but this doesn't happen for
-# procedures in database, which has atleast one table
-
-
-CREATE FUNCTION square_area(side tinyINT)
-RETURNS FLOAT
-RETURN side * side;
-
-#excercise function
-
-SELECT square_area(11);
-
-#Show data and create statements.
---echo showing objects and create statements.
---query_vertical SHOW CREATE FUNCTION number_of_players;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE players;
-describe penalties;
---replace_column 4 # 5 # 6 #
---query_vertical SHOW FUNCTION STATUS;
-
-#Backup and restore data.
---echo backup data
---replace_column 1 #
-BACKUP DATABASE bup_sfs TO 'bup_sfs.bak';
-
---echo dropping database.
-DROP DATABASE bup_sfs;
-
---echo Restoring Database
-
-# BUG#35118 Backup:Restore fails if previous create Procedure/Function/Trigger
-# command fails
-
---replace_column 1 #
-RESTORE FROM 'bup_sfs.bak';
-SELECT @@SQL_MODE;
-
-#show data and create statements
---echo showing objects and create statements
---query_vertical SHOW CREATE DATABASE bup_sfs;
---query_vertical SHOW CREATE FUNCTION number_of_players;
-describe penalties;
-describe matches;
---replace_column 4 # 5 # 6 #
-
---query_vertical SHOW FUNCTION STATUS;
-
-#Again calling function to check if Sf are backed up and restored properly or
-# not.
-
-INSERT INTO bup_sfs.players VALUES
-(57,'brown','1971-08-17','M','1985'),(95,'Miller','1963-05-14','M','1972');
-INSERT INTO bup_sfs.matches VALUES(11,2,6,0,3);
-
-SELECT square_area(12);
-SELECT number_of_players();
-SELECT number_of_players1();
-SELECT fcount();
-SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno<=3;
-
-#dropping Database again.
-DROP DATABASE bup_sfs;
-
---echo change SQL_MODE and then perform RESTORE operation
-
-SET SQL_MODE=ANSI;
---replace_column 1 #
-RESTORE FROM 'bup_sfs.bak';
-
-SELECT @@SQL_MODE;
-
-#show data and create statements
---echo showing objects and create statements
---query_vertical SHOW CREATE DATABASE bup_sfs;
---query_vertical SHOW CREATE FUNCTION number_of_players;
---replace_column 4 # 5 # 6 #
-
---query_vertical SHOW FUNCTION STATUS;
-
-#Again calling function to check if Sf are backed up and restored
-#properly or not.
-
-SELECT square_area(13);
-SELECT number_of_players();
-SELECT fcount();
-DROP DATABASE bup_sfs;
-
---echo change SQL_MODE and then perform RESTORE
-
-SET SQL_MODE=MAXDB;
---replace_column 1 #
-RESTORE FROM 'bup_sfs.bak';
-
-SELECT @@SQL_MODE;
-
-#show data and create statements
---echo showing objects and create statements
---query_vertical SHOW CREATE DATABASE bup_sfs;
---query_vertical SHOW CREATE FUNCTION number_of_players;
---replace_column 4 # 5 # 6 #
---query_vertical SHOW FUNCTION STATUS;
-
-#Again calling function to check if Sf are backed up and restored properly
-#or not.
-
-SELECT square_area(14);
-SELECT number_of_players();
-SELECT fcount();
-SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno >=3;
-DROP DATABASE bup_sfs;
---echo change SQL_MODE and then perform RESTORE
-
-SET SQL_MODE=' ';
---replace_column 1 #
-RESTORE FROM 'bup_sfs.bak';
-
-SELECT @@SQL_MODE;
-#show data and create statements
---echo showing objects and create statements
---query_vertical SHOW CREATE DATABASE bup_sfs;
---query_vertical SHOW CREATE FUNCTION number_of_players;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE players;
-describe matches;
---replace_column 4 # 5 # 6 #
---query_vertical SHOW FUNCTION STATUS;
-
-#Again calling function to check if Sf are backed up and restored properly
-# ornot
-
-SELECT square_area(20);
-SELECT number_of_players();
-SELECT fcount();
-SELECT paymentno,amount, dollars(amount) FROM penalties;
-
-# Test cleanup section
-
---echo
---echo *** DROP bup_sfs DATABASE ****
---echo
-
-DROP DATABASE bup_sfs;
-
---remove_file $MYSQLTEST_VARDIR/master-data/bup_sfs.bak
-
=== removed file 'mysql-test/suite/backup/t/backup_online_testing.test'
--- a/mysql-test/suite/backup/t/backup_online_testing.test 2008-06-30 19:40:33 +0000
+++ b/mysql-test/suite/backup/t/backup_online_testing.test 1970-01-01 00:00:00 +0000
@@ -1,1154 +0,0 @@
-###############################################################################
-# Author: Hema
-# Date: 2008-04-01
-# Purpose: Verify Online capability during differnt phases of BACKUP database
-# operation.
-# We will ensure that Ongoing transactions are not affected by Backup Database
-# command.
-# To perform this test we need backup and DML operations going in parallel.
-# We can make use of sync points to pause BACKUP database at different stages
-# and execute DML statements. Further we can confirm that the BACKUP database
-# operation is completed and the DML executed after 'validity point of time'
-# aren't included in the backup.
-# We establish multiple connections and for concurrency we use Send and Reap
-# commands where required for statements that are blocked.
-# SEND issues a statement and does not wait for the result.
-# REAP waits for and returns the result of the statement issued by SEND.
-#
-# Breakpoints Usage:
-# before_backup_data_init, before_backup_data_prepare, before_backup_command,
-# before_backup_meta, before_backup_data are the Breakpoints that occur
-# before Validity point of time.
-#
-# before_backup_data_init occurs at the start of INITIALIZE phase.
-# before_backup_data_prepare occurs at the start of PREPARE phase.
-# before_backup_command occurs at the start of BACKUP Operation.
-# before_backup_meta occurs before the call to write metadata.
-# before_backup_data occurs before the call to write table data.
-#
-# before_backup_data_lock, before_backup_data_unlock,
-# before_backup_data_finish and before_backup_done are the breakpoints
-# that occurs after validity point of time. The DML executed after
-# 'validity point of time' aren't included in the backup.
-#
-# before_backup_data_lock occurs before the engines are locked.
-# before_backup_data_unlock occurs before the engines are unlocked.
-# before_backup_data_finish occurs before the final data saving.
-# before_backup_done occurs at the end of the backup statement.
-###############################################################################
---source include/have_innodb.inc
---source include/have_debug_sync.inc
---source include/not_embedded.inc
-
-#
-# Preparatory cleanup.
-#
---disable_warnings
-# Initialize the debug sync facility.
-SET DEBUG_SYNC= 'RESET';
-DROP DATABASE IF EXISTS bup_online;
---error 0,1
---remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
---enable_warnings
-
-##############################################################
---echo
---echo starting the test for backup
---echo
-##############################################################
-
-CREATE DATABASE bup_online;
-
---echo Establishing connections
---echo ** con1 to perform BACKUP AND RESTORE**
---echo ** con2 and con2a to do INSERTS**
---echo ** con3 to synchronize with BACKUP
---echo ** con4 to do UPDATES**
---echo ** con5 to do DELETES**
---echo ** con6 to check table contents**
-
-#
-# Set the startup database for all connections to bup_online.
-#
-connect (con1,localhost,root,,bup_online);
-connect (con2,localhost,root,,bup_online);
-connect (con2a,localhost,root,,bup_online);
-connect (con3,localhost,root,,bup_online);
-connect (con4,localhost,root,,bup_online);
-connect (con5,localhost,root,,bup_online);
-connect (con6,localhost,root,,bup_online);
-
-#
-# Create table and load with data.
-#
---echo
---echo con1: ***Creating Tables***
-connection con1;
-
-CREATE TABLE t1(id int, details char(30))ENGINE=INNODB;
-INSERT INTO t1 VALUES(1,'testing1'),(2,'testing2'),(3,'testing3'),
- (4,'testing4'),(5,'testing5'),(6,'testing6'),(7,'tesing7'),(8,'testing8'),
- (9,'testing9'),(10,'testing10'),(11,'testing11'),(12,'testing12');
-
-CREATE TABLE t2(id int, info char(30))ENGINE=INNODB;
-INSERT INTO t2 VALUES(201, 'aa1'),(202,'aa2');
-
-CREATE TABLE t3(id int, name char(20))ENGINE=INNODB;
-INSERT INTO t3 VALUES(1,'xx1'),(2,'xx2'),(3,'xx3'),(4,'xx4'),(5,'xx5');
-
-SELECT COUNT(*) FROM t1;
-SELECT COUNT(*) FROM t2;
-SELECT COUNT(*) FROM t3;
-
-
---echo
---echo **TEST1**
-################Test1##############################
-##use of Breakpoint before_backup_data_init #
-###################################################
-
-#################
-#BACKUP DATABASE#
-#################
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_data_init SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(100,'new value added100');
-INSERT INTO t1 VALUES(101,'new value added101');
-INSERT INTO t1 VALUES(102,'new value added102');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t2 SET info='updated values1' WHERE id=201;
-UPDATE t2 SET info='updated values2' WHERE id=202;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t1 WHERE id=11;
-DELETE FROM t1 WHERE id=12;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(500,'online500');
-INSERT INTO t1 VALUES(600,'online600');
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t1 SET details='ONLINE1' WHERE id=10;
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t2 WHERE id=201;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
-
---echo
---echo **TEST2**
-################Test2##############################
-##use of Breakpoint before_backup_data_prepare #
-###################################################
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_data_prepare SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online1.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(103,'new value added103');
-INSERT INTO t2 VALUES(203,'Updated Values3');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t1 SET details='ONLINETEST' WHERE id=10;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t3 WHERE id=5;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(700,'online700');
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t3 SET name='yy' WHERE id=3;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online1.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online1.bak;
-
-
---echo
---echo **TEST3**
-################Test3##############################
-##use of Breakpoint before_backup_command #
-###################################################
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_command SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(104,'new value added103');
-INSERT INTO t2 VALUES(204,'Updated Values3');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t1 SET details='ONLINETEST1' WHERE id=9;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t3 WHERE id=4;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(800,'online800');
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t3 SET name='zz' WHERE id=2;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
-
---echo
---echo **TEST4**
-################Test4##############################
-##use of Breakpoint before_backup_meta #
-###################################################
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_meta SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(105,'new value added105');
-INSERT INTO t2 VALUES(205,'Updated Values5');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t1 SET details='ONLINETEST1' WHERE id=8;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t3 WHERE id=3;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(900,'online900');
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t3 SET name='tt' WHERE id=2;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
-
---echo
---echo **TEST5**
-################Test5##############################
-##use of Breakpoint before_backup_data #
-###################################################
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_data SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(106,'new value added106');
-INSERT INTO t2 VALUES(206,'Updated Values6');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t1 SET details='ONLINETEST1' WHERE id=7;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t3 WHERE id=2;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(1000,'online1000');
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t3 SET name='uu' WHERE id=1;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
-
---echo
---echo **TEST6**
-################Test6##############################
-##use of Breakpoint before_backup_data_lock #
-###################################################
-
-# In this test we need to send the DML statements to the background.
-# They block in wait_if_global_read_lock.
-# BACKUP is blocked at the sync point "data_lock", which occurs after
-# block_commits(), which locks the global read lock, and before
-# unblock_commits(), which releases the global read lock.
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_data_lock SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Starting Insert Data
-connection con2;
-send INSERT INTO t1 VALUES(107,'new value added107');
-
---echo
---echo con2a: Starting Insert Data
-connection con2a;
-send INSERT INTO t2 VALUES(207,'Updated Values7');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Starting Update data
-connection con4;
-send UPDATE t1 SET details='ONLINETEST1' WHERE id=6;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Starting Delete data
-connection con5;
-send DELETE FROM t3 WHERE id=1;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Fetching result of INSERT and Insert more data
-connection con2;
-reap;
-INSERT INTO t1 VALUES(1010,'online1010');
-
---echo
---echo con2a: Fetching result of INSERT
-connection con2a;
-reap;
-
---echo
---echo con4: Fetching result of UPDATE and Update more data
-connection con4;
-reap;
-UPDATE t2 SET info='t2 updated1' WHERE id=205;
-
---echo
---echo con5: Fetching result of DELETE
-connection con5;
-reap;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
-
---echo
---echo **TEST7**
-################Test7##############################
-##use of Breakpoint before_backup_data_unlock #
-###################################################
-
-# In this test we need to send the DML statements to the background.
-# They block in wait_if_global_read_lock.
-# BACKUP is blocked at the sync point "data_unlock", which occurs before
-# unblock_commits(), which releases the global read lock and after
-# block_commits(), which locks the global read lock.
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_data_unlock SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Starting Insert Data
-connection con2;
-send INSERT INTO t1 VALUES(108,'new value added108');
-
---echo
---echo con2a: Starting Insert Data
-connection con2a;
-send INSERT INTO t3 VALUES(20,'zz20');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Starting Update data
-connection con4;
-send UPDATE t1 SET details='ONLINETEST1' WHERE id=4;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Starting Delete data
-connection con5;
-send DELETE FROM t3 WHERE id=1;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Fetching result of INSERT and Insert more data
-connection con2;
-reap;
-INSERT INTO t1 VALUES(1020,'online1020');
-
---echo
---echo con2a: Fetching result of INSERT
-connection con2a;
-reap;
-
---echo
---echo con4: Fetching result of UPDATE and Update more data
-connection con4;
-reap;
-UPDATE t2 SET info='t2 updated2' WHERE id=206;
-
---echo
---echo con5: Fetching result of DELETE
-connection con5;
-reap;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
-
---echo
---echo **TEST8**
-################Test8##############################
-##use of Breakpoint before_backup_data_finish #
-###################################################
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_data_finish SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(109,'new value added109');
-INSERT INTO t3 VALUES(30,'zz30');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t1 SET details='ONLINETEST1' WHERE id=3;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t3 WHERE id=20;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(1030,'online1030');
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t2 SET info='t2 updated3' WHERE id=207;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
-
---echo
---echo **TEST9**
-################Test9##############################
-##use of Breakpoint before_backup_done #
-###################################################
-
---echo
---echo con1: Starting Backup Data
-connection con1;
-# Activate the sync point for BACKUP.
-# When BACKUP reaches the sync point it emits the signal bup_sync
-# and waits for another thread to emit the signal bup_finish.
-SET DEBUG_SYNC= 'before_backup_done SIGNAL bup_sync
- WAIT_FOR bup_finish';
-send BACKUP DATABASE bup_online TO 'bup_online.bak';
-
---echo
---echo con3: Waiting for BACKUP to reach the breakpoint
-connection con3;
-# If BACKUP did already reach its sync point, then the signal is already
-# present. If not, we wait until BACKUP emits it.
-SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
-
-#############
-#INSERT DATA#
-#############
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(110,'new value added110');
-INSERT INTO t3 VALUES(40,'zz40');
-
-#############
-#UPDATE DATA#
-#############
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t1 SET details='ONLINETEST1' WHERE id=2;
-
-#############
-#Delete Data#
-#############
-
---echo
---echo con5: Deleting Data
-connection con5;
-DELETE FROM t3 WHERE id=30;
-
---echo
---echo con3: Signalling that BACKUP can finish
-connection con3;
-SET DEBUG_SYNC= 'now SIGNAL bup_finish';
-
---echo
---echo con1: Fetching result of BACKUP
-connection con1;
---replace_column 1 #
-reap;
-
---echo
---echo con2: Inserting Data
-connection con2;
-INSERT INTO t1 VALUES(1040,'online1040');
-
---echo
---echo con4: Updating Data
-connection con4;
-UPDATE t2 SET info='t2 updated4' WHERE id=201;
-
---echo
---echo con6: **Checking the Data Contents before Restore**
-connection con6;
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-##################
-#RESTORE DATABASE#
-##################
-
---echo
---echo con1: Dropping the database
-connection con1;
-DROP DATABASE bup_online;
-
---echo con1: Restoring the database
---replace_column 1 #
-RESTORE FROM 'bup_online.bak';
-
-#check data contents after Restore.
---echo
---echo *** RESULT AFTER RESTORE ***
---echo
-
-SELECT * FROM t1;
-SELECT COUNT(*) FROM t1;
-SELECT * FROM t2;
-SELECT COUNT(*) FROM t2;
-SELECT *FROM t3;
-SELECT COUNT(*) FROM t3;
-
-# Test cleanup section
-
---echo
---echo *** DROP bup_online DATABASE ****
---echo
-
-DROP DATABASE bup_online;
-
-remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
-
=== removed file 'mysql-test/suite/backup/t/backup_procedures.test'
--- a/mysql-test/suite/backup/t/backup_procedures.test 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/t/backup_procedures.test 1970-01-01 00:00:00 +0000
@@ -1,124 +0,0 @@
-###########################################################################
-# Author: Hema
-# Date: 2008-03-11
-# Purpose: To test the metadata consistency object stored procedures for all
-# storage engines.
-###############################################################################
---source include/not_embedded.inc
---source suite/backup/include/backup_engine.inc
---source include/have_debug.inc
-
-connect (backup,localhost,root,,);
-connect (breakpoints,localhost,root,,);
-
-##############################################################
---echo
---echo starting the test for backup
---echo
-##############################################################
-
---error 0,1
---remove_file $MYSQLTEST_VARDIR/master-data/bup_sp.bak
-
-# Multiple instances of stored routines without atleast one table in
-# database results in failure.
-# BUG#34868
-
---disable_warnings
-DROP DATABASE IF EXISTS bup_sp;
---enable_warnings
-
-CREATE DATABASE bup_sp;
-USE bup_sp;
-
-#Create table with all storage engines and load with data.
-
---echo *******Creating table******
-
-CREATE TABLE bup_sp.country(
-ccode CHAR(10),
-city VARCHAR(10)
-);
-
---echo *********loading data*********
-INSERT INTO bup_sp.country VALUES
-('IND','chennai'),('US','sfo'),('US','austin'),
-('IND','Bomb'),('US','Richmond'),('IND','hyd');
-
-SELECT * FROM bup_sp.country ORDER BY ccode, city;
-
-#Create stored procedures for the table
-
-delimiter ||;
-CREATE PROCEDURE countryinfo (IN loc CHAR(5))
-BEGIN
- SELECT * FROM country WHERE ccode=loc;
-END;
-||
-
-CREATE PROCEDURE dorepeat(p1 INT)
- BEGIN
- SET @x = 0;
- REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
- END ;||
-delimiter ;||
-
-#Excercise the objects
-
-CALL countryinfo('US');
-CALL countryinfo('IND');
-CALL dorepeat(1000);
-SELECT @x;
-#Show the data and Create statements
-
---echo showing objects and create statements.
---query_vertical SHOW CREATE PROCEDURE countryinfo;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE country;
---replace_column 4 # 5 # 6 # 7 #
---query_vertical SHOW PROCEDURE STATUS;
-
-#Backup and restore data.
-
---echo backup data
---echo
---replace_column 1 #
-BACKUP DATABASE bup_sp TO 'bup_sp.bak';
---echo dropping database.
-DROP DATABASE bup_sp;
-
-#RESTORE FROM bup_sp.bak;
---echo
---replace_column 1 #
-RESTORE FROM 'bup_sp.bak';
-
-#show data and create statements
---echo showing objects and create statements
---query_vertical SHOW CREATE PROCEDURE countryinfo;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE country;
---replace_column 4 # 5 # 6 # 7 #
---query_vertical SHOW PROCEDURE STATUS;
-
-
-#Excercise the objects after restore to make sure if objects are backedup and
-#restored properly.
-
-INSERT INTO bup_sp.country VALUES
-('US','boston'),('IND','jaipur'),('US','natick');
-SELECT * FROM bup_sp.country ORDER BY ccode, city;
-CALL countryinfo('IND');
-CALL countryinfo('US');
-CALL dorepeat(99);
-SELECT @x;
-
-# Test cleanup section
-
---echo
---echo *** DROP bup_sp DATABASE ****
---echo
-
-DROP DATABASE bup_sp;
-
---remove_file $MYSQLTEST_VARDIR/master-data/bup_sp.bak
-
=== removed file 'mysql-test/suite/backup/t/backup_triggers.test'
--- a/mysql-test/suite/backup/t/backup_triggers.test 2008-08-12 03:22:05 +0000
+++ b/mysql-test/suite/backup/t/backup_triggers.test 1970-01-01 00:00:00 +0000
@@ -1,298 +0,0 @@
-
-##################################################################
-# Author: Hema
-# Date: 2008-05-28
-# Purpose: To test the metadata consistency of object trigger for all
-# storage engines
-###################################################################
-
---source include/not_embedded.inc
---source suite/backup/include/backup_engine.inc
---source include/have_debug.inc
-
-connect (backup,localhost,root,,);
-connect (breakpoints,localhost,root,,);
-
-##############################################################
---echo
---echo starting the test for backup
---echo
-##############################################################
-
---error 0,1
---remove_file $MYSQLTEST_VARDIR/master-data/bup_ts.bak
-
---error 0,1
---disable_warnings
-#Create Database and object trigger for this test.
-
-DROP DATABASE IF EXISTS bup_ts;
---enable_warnings
-
-CREATE DATABASE bup_ts;
-USE bup_ts;
-
-#Create table and load with data.
-SELECT @@SQL_MODE;
---echo *****Creating table cap ******
-
-CREATE TABLE bup_ts.cap(
-id SMALLINT,
-country CHAR(20),
-city VARCHAR(20),
-population BIGINT,
-capital CHAR(20)
-);
-
---echo ******* Creating table city*****.
-
-CREATE TABLE bup_ts.city(
-id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-name VARCHAR(20),
-ccode CHAR(10),
-population bigint
-);
-
---echo *******Creating table dropcity *****
-
-CREATE TABLE bup_ts.dropcity(
-id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-name VARCHAR(20)
-);
-
---echo ******Create Trigger for the table*******
-# BUG#35118 Restore fails if previous create Procedure/Function/Trigger
-# command fails
-
-delimiter ||;
-
---echo *******creating trigger with event before insert**********
-#BUG#34759 Server crash when backing-up database with after insert
-# trigger with event delete.
-
-CREATE TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
-SET new.population= if(new.population<0,0,truncate(new.population,-3));||
-
---echo ********creating trigger with event before Update******
-CREATE TRIGGER capu_tri BEFORE UPDATE ON cap FOR EACH ROW
-BEGIN
- SET @country=old.country;
- SET @capital_old=old.capital;
- SET @capital_new=new.capital;
-END;
-||
-
---echo *****creating trigger with event after delete********
-
-CREATE TRIGGER cityin_d AFTER
-DELETE ON city FOR EACH ROW
-INSERT INTO dropcity(id, name) VALUES(old.id, old.name);||
-
---echo Creating Tables
-delimiter ;||
-
-CREATE TABLE bup_ts.t2(a CHAR(4));
-CREATE TABLE bup_ts.t3(a CHAR(4));
-CREATE TABLE bup_ts.t4(a CHAR(4));
-
---echo Creating Triggers
-
---echo Creating trigger with event after insert
-#BUG#34759 Server crash when backing-up database with after insert trigger with
-# event delete.
-
-delimiter ||;
-
-CREATE TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
-BEGIN
- INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
-END;
-||
-
---echo Creating trigger with event after Update
-
-CREATE TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
-BEGIN
- INSERT INTO t4 VALUES('J'),('O'),('B');
-END;
-||
-
-delimiter ;||
-
---echo Load data in to table
---echo ********Inserting Values in tables*********
-
-INSERT INTO bup_ts.cap VALUES
-(1,'IND','chn',87874646468,'deli'),(2,'US','Austin',-6466547,'DC'),
-(3,'Russia','moscow',76487623235682,'moscow'),(4,'IND','bang',-83875477,'deli'),
-(5,'US','sacramento',-388348,'DC'),(6,'IND','jammu',3848488,'deli'),
-(7,'Russia','xx',-373788,'moscow'),(8,'IND','hyd',647747,'deli');
-
-INSERT INTO bup_ts.city VALUES
-(1,'aa','AD',263768),(2,'bb','PO',8839898),(3,'cc','KL',898778),
-(4,'dd','IK',73287328),(5,'ee','YU',89399),(6,'ff','IL',90880),
-(7,'gg','TY',345),(8,'hh','WE',9239084);
-
---echo Updating the table to fire trigger with event update
-
-UPDATE cap SET capital='Delhi' WHERE country='Ind';
-UPDATE cap SET capital='Washington DC' WHERE country='US';
-SELECT @country as country, @capital_old as 'old capital',
-@capital_new as 'new capital';
-
---echo showing the tables to check the trigger is fired with event insert
-
-SELECT * FROM bup_ts.cap ORDER BY city;
-
---echo selecting data from table city
-
-SELECT * FROM bup_ts.city ORDER BY name;
-
---echo deleting few rows from table city to fire trigger with event delete.
-#*****Bug#35249******
-#Here memory storage engine crashes if trigger is created
-# with event delete and executed.
-
-DELETE FROM bup_ts.city WHERE name='dd';
-DELETE FROM bup_ts.city WHERE name='bb';
-
---echo
---echo Checking table contents of city and dropcity to verify if trigger is
---echo fired for event delete.
-SELECT * FROM city ORDER BY name;
-SELECT * FROM dropcity ORDER BY id;
-
---echo ******Firing Trigger after insert and after update *********
-
-INSERT INTO bup_ts.t2 values('a'),('k'),('*'),('i');
-SELECT * FROM bup_ts.t3;
-UPDATE t2 SET a='*' WHERE a='k';
-SELECT * FROM bup_ts.t4;
-
---echo Show the data and Create statements
-
---echo showing objects and create statements.
---query_vertical SHOW TRIGGERS;
---query_vertical SHOW CREATE TRIGGER cap_tri;
---query_vertical SHOW CREATE TRIGGER capu_tri;
---query_vertical SHOW CREATE TRIGGER cityin_d;
---query_vertical SHOW CREATE TRIGGER trai;
---query_vertical SHOW CREATE TRIGGER trau;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE city;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE t2;
-
-
-#Backup and restore data.
---echo backup data
---replace_column 1 #
-
-BACKUP DATABASE bup_ts TO 'bup_ts.bak';
-
---echo dropping database.
-DROP DATABASE bup_ts;
-
---echo perform restore
---replace_column 1 #
-
-RESTORE FROM 'bup_ts.bak';
-
---echo show data and create statements after Restore
---echo showing objects and create statements
---query_vertical SHOW CREATE DATABASE bup_ts;
---query_vertical SHOW CREATE TRIGGER cap_tri;
---query_vertical SHOW CREATE TRIGGER cityin_d;
---query_vertical SHOW CREATE TRIGGER trai;
---query_vertical SHOW CREATE TRIGGER trau;
---query_vertical SHOW TRIGGERS;
---echo Inserting some more values in the table to check the trigger with event insert
-
-INSERT INTO bup_ts.cap VALUES
-(9,'US','houston',-33399587677866656555655555655,'texas');
-
-SELECT * FROM bup_ts.cap ORDER BY city;
-
---echo Deleting some rows from city to check the trigger with event delete
-
-DELETE FROM city WHERE name='aa';
-
---echo Checking table city and drop city
-
-SELECT * FROM bup_ts.city ORDER BY name;
-SELECT * FROM bup_ts.dropcity ORDER BY id;
-INSERT INTO bup_ts.t2 VALUES('m'),('s1'),('s'),('q'),('l');
-SELECT * FROM bup_ts.t3;
-UPDATE t2 SET a='y' WHERE a='s1';
-SELECT * FROM bup_ts.t4;
-DROP DATABASE bup_ts;
-
---echo Change sql mode and perform Restore and check the table contents.
-
-SET SQL_MODE=TRADITIONAL;
---replace_column 1 #
-RESTORE FROM 'bup_ts.bak';
-
---query_vertical SHOW TRIGGERS;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE t2;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE city;
---replace_result $ENGINE ENGINE
---query_vertical SHOW CREATE TABLE cap;
---echo Check contents after Restore and change of SQL mode
-
-INSERT INTO bup_ts.cap VALUES(10,'India','Daund',36637782899,'maharashtra');
-SELECT * FROM bup_ts.cap ORDER BY city;
-INSERT INTO bup_ts.t2 VALUES('i'),('w');
-SELECT * FROM t3;
-UPDATE t2 SET a='^' WHERE a='w';
-SELECT * FROM t4;
-
-DROP DATABASE bup_ts;
-
---echo change of SQL mode
-
-SET SQL_MODE=MAXDB;
---replace_column 1 #
-RESTORE FROM 'bup_ts.bak';
-
---echo Check contents after Restore and change of SQL mode
-
-INSERT INTO bup_ts.cap VALUES(11,'UK','Edenburg',-36637782899,'london');
-SELECT * FROM bup_ts.cap ORDER BY city;
-INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h');
-SELECT * FROM bup_ts.t3;
-UPDATE t2 SET a='p' WHERE a='h';
-SELECT * FROM bup_ts.t4;
-
-DROP DATABASE bup_ts;
---echo Chnage SQL mode and then perform Restore.
-
-SET SQL_MODE=ANSI;
---replace_column 1 #
-
-RESTORE FROM 'bup_ts.bak';
-
---echo Check contents after Restore and change of SQL mode
-
-INSERT INTO bup_ts.cap VALUES(12,'India','vooty',789,'Tamilnadu');
-
-SELECT * FROM bup_ts.cap ORDER BY city;
-INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h'),('b');
-
-SELECT * FROM bup_ts.t3;
-UPDATE bup_ts.t2 SET a='#' WHERE a='w';
-
-SELECT * FROM bup_ts.t4;
-
-# Test cleanup section
---echo
---echo *** DROP bup_ts DATABASE ****
---echo
-
-DROP DATABASE bup_ts;
-
---remove_file $MYSQLTEST_VARDIR/master-data/bup_ts.bak
-
-
=== added directory 'mysql-test/suite/backup_engines'
=== added file 'mysql-test/suite/backup_engines/combinations'
--- a/mysql-test/suite/backup_engines/combinations 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/combinations 2008-09-26 22:00:09 +0000
@@ -0,0 +1,11 @@
+[myisam]
+--default-storage-engine=myisam
+
+[falcon]
+--default-storage-engine=falcon
+
+[memory]
+--default-storage-engine=memory
+
+[innodb]
+--default-storage-engine=innodb
=== added directory 'mysql-test/suite/backup_engines/include'
=== added file 'mysql-test/suite/backup_engines/include/backup_engine.inc'
--- a/mysql-test/suite/backup_engines/include/backup_engine.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/include/backup_engine.inc 2008-09-26 22:00:09 +0000
@@ -0,0 +1,9 @@
+###############################################################################
+# Author: Hema
+# Date: 2008-04-01
+# Purpose:To define the engine variable.
+###############################################################################
+--replace_column 2 #
+SHOW VARIABLES LIKE 'storage_engine';
+let $ENGINE= query_get_value(SHOW VARIABLES LIKE 'storage_engine', Value, 1);
+
=== added directory 'mysql-test/suite/backup_engines/r'
=== added file 'mysql-test/suite/backup_engines/r/backup_functions.result'
--- a/mysql-test/suite/backup_engines/r/backup_functions.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/r/backup_functions.result 2008-09-26 22:00:09 +0000
@@ -0,0 +1,601 @@
+SHOW VARIABLES LIKE 'storage_engine';
+Variable_name Value
+storage_engine #
+
+starting the test for backup
+
+DROP DATABASE IF EXISTS bup_sfs;
+CREATE DATABASE bup_sfs;
+USE bup_sfs;
+SET SQL_MODE=TRADITIONAL;
+Creating table
+CREATE TABLE bup_sfs.players(
+playerno INT not null PRIMARY KEY,
+name CHAR(10),
+Bdate DATE,
+sex CHAR(2),
+joined YEAR
+);
+CREATE TABLE bup_sfs.teams(
+teamno INT NOT NULL PRIMARY KEY,
+playerno INT,
+division CHAR(10),
+FOREIGN KEY(playerno) REFERENCES players(playerno)
+);
+CREATE TABLE bup_sfs.matches(
+Matchno INT NOT NULL PRIMARY KEY,
+teamno INT ,
+playerno INT,
+won INT,
+lost INT,
+FOREIGN KEY(teamno) REFERENCES teams(teamno),
+FOREIGN KEY(playerno) REFERENCES players(playerno)
+);
+CREATE TABLE bup_sfs.penalties(
+paymentno INT not null PRIMARY KEY,
+playerno INT,
+paymentdate DATE,
+amount FLOAT,
+FOREIGN KEY(playerno) REFERENCES players(playerno)
+);
+Load Data in tables
+INSERT INTO bup_sfs.players VALUES
+(1,'aa1','1948-10-09','M','1960'),(2,'aa2','1950-05-09','M','1965'),
+(3,'aa3','1953-10-10','F','1970'),(4,'aa4','1970-10-19','M','1985'),
+(5,'aa5','1972-04-28','F','1994'),(6,'aa6','1945-01-26','M','1960');
+INSERT INTO bup_sfs.teams VALUES(1,3,'first'),(2,4,'division');
+INSERT INTO bup_sfs.matches VALUES
+(1,1,2,3,1),(2,2,1,4,2),(3,1,5,0,1),(4,2,4,3,0),(5,2,5,2,2),
+(6,1,3,3,1),(7,2,1,4,2),(8,1,5,0,1),(9,2,2,3,0),(10,2,2,2,2);
+INSERT INTO bup_sfs.penalties VALUES
+(1,5,'1980-10-10',100.23),(2,3,'1967-12-10',123.23),
+(3,3,'1983-05-25',25.25), (4,4,'1981-11-10',210.23) ,
+(5,5,'1980-12-12',300), (6,1,'1984-07-02',10),
+(7,1,'1985-10-10',23.90);
+CREATE FUNCTION number_of_players() RETURNS INTEGER
+BEGIN
+RETURN (SELECT COUNT(*) FROM players);
+END;
+||
+CREATE FUNCTION number_of_players1() RETURNS INTEGER
+BEGIN
+RETURN (SELECT COUNT(*) FROM players WHERE playerno > 2);
+END;
+||
+CREATE FUNCTION fcount() RETURNS INTEGER
+BEGIN
+DECLARE v_out INT;
+SELECT count(*) FROM matches INTO v_out;
+RETURN v_out;
+END;
+||
+CREATE FUNCTION dollars(amount FLOAT(7,2)) RETURNS FLOAT(7,2)
+BEGIN
+RETURN amount * (1/0.8);
+END;
+||
+SELECT number_of_players();
+number_of_players()
+6
+SELECT number_of_players1();
+number_of_players1()
+4
+SELECT fcount();
+fcount()
+10
+SELECT paymentno,amount, dollars(amount) FROM penalties;
+paymentno amount dollars(amount)
+1 100.23 125.29
+2 123.23 154.04
+3 25.25 31.56
+4 210.23 262.79
+5 300 375.00
+6 10 12.50
+7 23.9 29.87
+creating stored functions which is not dependent on table
+CREATE FUNCTION square_area(side tinyINT)
+RETURNS FLOAT
+RETURN side * side;
+SELECT square_area(11);
+square_area(11)
+121
+showing objects and create statements.
+SHOW CREATE FUNCTION number_of_players;;
+Function number_of_players
+sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
+Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
+BEGIN
+RETURN (SELECT COUNT(*) FROM players);
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TABLE players;;
+Table players
+Create Table CREATE TABLE `players` (
+ `playerno` int(11) NOT NULL,
+ `name` char(10) DEFAULT NULL,
+ `Bdate` date DEFAULT NULL,
+ `sex` char(2) DEFAULT NULL,
+ `joined` year(4) DEFAULT NULL,
+ PRIMARY KEY (`playerno`)
+) ENGINE=ENGINE DEFAULT CHARSET=latin1
+describe penalties;
+Field Type Null Key Default Extra
+paymentno int(11) NO PRI NULL
+playerno int(11) YES MUL NULL
+paymentdate date YES NULL
+amount float YES NULL
+SHOW FUNCTION STATUS;;
+Db bup_sfs
+Name dollars
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name fcount
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players1
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name square_area
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+backup data
+BACKUP DATABASE bup_sfs TO 'bup_sfs.bak';
+backup_id
+#
+dropping database.
+DROP DATABASE bup_sfs;
+Restoring Database
+RESTORE FROM 'bup_sfs.bak';
+backup_id
+#
+SELECT @@SQL_MODE;
+@@SQL_MODE
+STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
+showing objects and create statements
+SHOW CREATE DATABASE bup_sfs;;
+Database bup_sfs
+Create Database CREATE DATABASE `bup_sfs` /*!40100 DEFAULT CHARACTER SET latin1 */
+SHOW CREATE FUNCTION number_of_players;;
+Function number_of_players
+sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
+Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
+BEGIN
+RETURN (SELECT COUNT(*) FROM players);
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+describe penalties;
+Field Type Null Key Default Extra
+paymentno int(11) NO PRI NULL
+playerno int(11) YES MUL NULL
+paymentdate date YES NULL
+amount float YES NULL
+describe matches;
+Field Type Null Key Default Extra
+Matchno int(11) NO PRI NULL
+teamno int(11) YES MUL NULL
+playerno int(11) YES MUL NULL
+won int(11) YES NULL
+lost int(11) YES NULL
+SHOW FUNCTION STATUS;;
+Db bup_sfs
+Name dollars
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name fcount
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players1
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name square_area
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+INSERT INTO bup_sfs.players VALUES
+(57,'brown','1971-08-17','M','1985'),(95,'Miller','1963-05-14','M','1972');
+INSERT INTO bup_sfs.matches VALUES(11,2,6,0,3);
+SELECT square_area(12);
+square_area(12)
+144
+SELECT number_of_players();
+number_of_players()
+8
+SELECT number_of_players1();
+number_of_players1()
+6
+SELECT fcount();
+fcount()
+11
+SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno<=3;
+paymentno amount dollars(amount)
+1 100.23 125.29
+2 123.23 154.04
+3 25.25 31.56
+DROP DATABASE bup_sfs;
+change SQL_MODE and then perform RESTORE operation
+SET SQL_MODE=ANSI;
+RESTORE FROM 'bup_sfs.bak';
+backup_id
+#
+SELECT @@SQL_MODE;
+@@SQL_MODE
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+showing objects and create statements
+SHOW CREATE DATABASE bup_sfs;;
+Database bup_sfs
+Create Database CREATE DATABASE "bup_sfs" /*!40100 DEFAULT CHARACTER SET latin1 */
+SHOW CREATE FUNCTION number_of_players;;
+Function number_of_players
+sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
+Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
+BEGIN
+RETURN (SELECT COUNT(*) FROM players);
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW FUNCTION STATUS;;
+Db bup_sfs
+Name dollars
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name fcount
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players1
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name square_area
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SELECT square_area(13);
+square_area(13)
+169
+SELECT number_of_players();
+number_of_players()
+6
+SELECT fcount();
+fcount()
+10
+DROP DATABASE bup_sfs;
+change SQL_MODE and then perform RESTORE
+SET SQL_MODE=MAXDB;
+RESTORE FROM 'bup_sfs.bak';
+backup_id
+#
+SELECT @@SQL_MODE;
+@@SQL_MODE
+PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
+showing objects and create statements
+SHOW CREATE DATABASE bup_sfs;;
+Database bup_sfs
+Create Database CREATE DATABASE "bup_sfs" /*!40100 DEFAULT CHARACTER SET latin1 */
+SHOW CREATE FUNCTION number_of_players;;
+Function number_of_players
+sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
+Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
+BEGIN
+RETURN (SELECT COUNT(*) FROM players);
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW FUNCTION STATUS;;
+Db bup_sfs
+Name dollars
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name fcount
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players1
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name square_area
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SELECT square_area(14);
+square_area(14)
+196
+SELECT number_of_players();
+number_of_players()
+6
+SELECT fcount();
+fcount()
+10
+SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno >=3;
+paymentno amount dollars(amount)
+3 25.25 31.56
+4 210.23 262.79
+5 300 375.00
+6 10 12.50
+7 23.9 29.87
+DROP DATABASE bup_sfs;
+change SQL_MODE and then perform RESTORE
+SET SQL_MODE=' ';
+RESTORE FROM 'bup_sfs.bak';
+backup_id
+#
+SELECT @@SQL_MODE;
+@@SQL_MODE
+
+showing objects and create statements
+SHOW CREATE DATABASE bup_sfs;;
+Database bup_sfs
+Create Database CREATE DATABASE `bup_sfs` /*!40100 DEFAULT CHARACTER SET latin1 */
+SHOW CREATE FUNCTION number_of_players;;
+Function number_of_players
+sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
+Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `number_of_players`() RETURNS int(11)
+BEGIN
+RETURN (SELECT COUNT(*) FROM players);
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TABLE players;;
+Table players
+Create Table CREATE TABLE `players` (
+ `playerno` int(11) NOT NULL,
+ `name` char(10) DEFAULT NULL,
+ `Bdate` date DEFAULT NULL,
+ `sex` char(2) DEFAULT NULL,
+ `joined` year(4) DEFAULT NULL,
+ PRIMARY KEY (`playerno`)
+) ENGINE=ENGINE DEFAULT CHARSET=latin1
+describe matches;
+Field Type Null Key Default Extra
+Matchno int(11) NO PRI NULL
+teamno int(11) YES MUL NULL
+playerno int(11) YES MUL NULL
+won int(11) YES NULL
+lost int(11) YES NULL
+SHOW FUNCTION STATUS;;
+Db bup_sfs
+Name dollars
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name fcount
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name number_of_players1
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sfs
+Name square_area
+Type FUNCTION
+Definer #
+Modified #
+Created #
+Security_type DEFINER
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SELECT square_area(20);
+square_area(20)
+400
+SELECT number_of_players();
+number_of_players()
+6
+SELECT fcount();
+fcount()
+10
+SELECT paymentno,amount, dollars(amount) FROM penalties;
+paymentno amount dollars(amount)
+1 100.23 125.29
+2 123.23 154.04
+3 25.25 31.56
+4 210.23 262.79
+5 300 375.00
+6 10 12.50
+7 23.9 29.87
+
+*** DROP bup_sfs DATABASE ****
+
+DROP DATABASE bup_sfs;
=== added file 'mysql-test/suite/backup_engines/r/backup_online_testing.result'
--- a/mysql-test/suite/backup_engines/r/backup_online_testing.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/r/backup_online_testing.result 2008-09-26 22:00:09 +0000
@@ -0,0 +1,1137 @@
+SET DEBUG_SYNC= 'RESET';
+DROP DATABASE IF EXISTS bup_online;
+
+starting the test for backup
+
+CREATE DATABASE bup_online;
+Establishing connections
+** con1 to perform BACKUP AND RESTORE**
+** con2 and con2a to do INSERTS**
+** con3 to synchronize with BACKUP
+** con4 to do UPDATES**
+** con5 to do DELETES**
+** con6 to check table contents**
+
+con1: ***Creating Tables***
+CREATE TABLE t1(id int, details char(30))ENGINE=INNODB;
+INSERT INTO t1 VALUES(1,'testing1'),(2,'testing2'),(3,'testing3'),
+(4,'testing4'),(5,'testing5'),(6,'testing6'),(7,'tesing7'),(8,'testing8'),
+(9,'testing9'),(10,'testing10'),(11,'testing11'),(12,'testing12');
+CREATE TABLE t2(id int, info char(30))ENGINE=INNODB;
+INSERT INTO t2 VALUES(201, 'aa1'),(202,'aa2');
+CREATE TABLE t3(id int, name char(20))ENGINE=INNODB;
+INSERT INTO t3 VALUES(1,'xx1'),(2,'xx2'),(3,'xx3'),(4,'xx4'),(5,'xx5');
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+12
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+5
+
+**TEST1**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_data_init SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(100,'new value added100');
+INSERT INTO t1 VALUES(101,'new value added101');
+INSERT INTO t1 VALUES(102,'new value added102');
+
+con4: Updating Data
+UPDATE t2 SET info='updated values1' WHERE id=201;
+UPDATE t2 SET info='updated values2' WHERE id=202;
+
+con5: Deleting Data
+DELETE FROM t1 WHERE id=11;
+DELETE FROM t1 WHERE id=12;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(500,'online500');
+INSERT INTO t1 VALUES(600,'online600');
+
+con4: Updating Data
+UPDATE t1 SET details='ONLINE1' WHERE id=10;
+
+con5: Deleting Data
+DELETE FROM t2 WHERE id=201;
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 testing8
+9 testing9
+10 ONLINE1
+100 new value added100
+101 new value added101
+102 new value added102
+500 online500
+600 online600
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+15
+SELECT * FROM t2;
+id info
+202 updated values2
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+1
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 testing8
+9 testing9
+10 testing10
+100 new value added100
+101 new value added101
+102 new value added102
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+13
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+2
+
+**TEST2**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_data_prepare SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online1.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(103,'new value added103');
+INSERT INTO t2 VALUES(203,'Updated Values3');
+
+con4: Updating Data
+UPDATE t1 SET details='ONLINETEST' WHERE id=10;
+
+con5: Deleting Data
+DELETE FROM t3 WHERE id=5;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(700,'online700');
+
+con4: Updating Data
+UPDATE t3 SET name='yy' WHERE id=3;
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 testing8
+9 testing9
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+700 online700
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+15
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+3
+SELECT *FROM t3;
+id name
+1 xx1
+2 xx2
+3 yy
+4 xx4
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+4
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online1.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 testing8
+9 testing9
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+14
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+3
+SELECT *FROM t3;
+id name
+1 xx1
+2 xx2
+3 xx3
+4 xx4
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+4
+
+**TEST3**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_command SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(104,'new value added103');
+INSERT INTO t2 VALUES(204,'Updated Values3');
+
+con4: Updating Data
+UPDATE t1 SET details='ONLINETEST1' WHERE id=9;
+
+con5: Deleting Data
+DELETE FROM t3 WHERE id=4;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(800,'online800');
+
+con4: Updating Data
+UPDATE t3 SET name='zz' WHERE id=2;
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 testing8
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+800 online800
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+16
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+4
+SELECT *FROM t3;
+id name
+1 xx1
+2 zz
+3 xx3
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+3
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 testing8
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+15
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+4
+SELECT *FROM t3;
+id name
+1 xx1
+2 xx2
+3 xx3
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+3
+
+**TEST4**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_meta SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(105,'new value added105');
+INSERT INTO t2 VALUES(205,'Updated Values5');
+
+con4: Updating Data
+UPDATE t1 SET details='ONLINETEST1' WHERE id=8;
+
+con5: Deleting Data
+DELETE FROM t3 WHERE id=3;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(900,'online900');
+
+con4: Updating Data
+UPDATE t3 SET name='tt' WHERE id=2;
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+900 online900
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+17
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+5
+SELECT *FROM t3;
+id name
+1 xx1
+2 tt
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+2
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 tesing7
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+16
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+5
+SELECT *FROM t3;
+id name
+1 xx1
+2 xx2
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+2
+
+**TEST5**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_data SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(106,'new value added106');
+INSERT INTO t2 VALUES(206,'Updated Values6');
+
+con4: Updating Data
+UPDATE t1 SET details='ONLINETEST1' WHERE id=7;
+
+con5: Deleting Data
+DELETE FROM t3 WHERE id=2;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(1000,'online1000');
+
+con4: Updating Data
+UPDATE t3 SET name='uu' WHERE id=1;
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+1000 online1000
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+18
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 uu
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+1
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+17
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 xx1
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+1
+
+**TEST6**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_data_lock SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Starting Insert Data
+INSERT INTO t1 VALUES(107,'new value added107');
+
+con2a: Starting Insert Data
+INSERT INTO t2 VALUES(207,'Updated Values7');
+
+con4: Starting Update data
+UPDATE t1 SET details='ONLINETEST1' WHERE id=6;
+
+con5: Starting Delete data
+DELETE FROM t3 WHERE id=1;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Fetching result of INSERT and Insert more data
+INSERT INTO t1 VALUES(1010,'online1010');
+
+con2a: Fetching result of INSERT
+
+con4: Fetching result of UPDATE and Update more data
+UPDATE t2 SET info='t2 updated1' WHERE id=205;
+
+con5: Fetching result of DELETE
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 ONLINETEST1
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+107 new value added107
+1010 online1010
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+19
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 t2 updated1
+206 Updated Values6
+207 Updated Values7
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+7
+SELECT *FROM t3;
+id name
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+0
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+17
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 xx1
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+1
+
+**TEST7**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_data_unlock SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Starting Insert Data
+INSERT INTO t1 VALUES(108,'new value added108');
+
+con2a: Starting Insert Data
+INSERT INTO t3 VALUES(20,'zz20');
+
+con4: Starting Update data
+UPDATE t1 SET details='ONLINETEST1' WHERE id=4;
+
+con5: Starting Delete data
+DELETE FROM t3 WHERE id=1;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Fetching result of INSERT and Insert more data
+INSERT INTO t1 VALUES(1020,'online1020');
+
+con2a: Fetching result of INSERT
+
+con4: Fetching result of UPDATE and Update more data
+UPDATE t2 SET info='t2 updated2' WHERE id=206;
+
+con5: Fetching result of DELETE
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 ONLINETEST1
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+108 new value added108
+1020 online1020
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+19
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 t2 updated2
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+20 zz20
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+1
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+17
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 xx1
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+1
+
+**TEST8**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_data_finish SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(109,'new value added109');
+INSERT INTO t3 VALUES(30,'zz30');
+
+con4: Updating Data
+UPDATE t1 SET details='ONLINETEST1' WHERE id=3;
+
+con5: Deleting Data
+DELETE FROM t3 WHERE id=20;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(1030,'online1030');
+
+con4: Updating Data
+UPDATE t2 SET info='t2 updated3' WHERE id=207;
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 ONLINETEST1
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+109 new value added109
+1030 online1030
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+19
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 xx1
+30 zz30
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+2
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+17
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 xx1
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+1
+
+**TEST9**
+
+con1: Starting Backup Data
+SET DEBUG_SYNC= 'before_backup_done SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+con3: Waiting for BACKUP to reach the breakpoint
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(110,'new value added110');
+INSERT INTO t3 VALUES(40,'zz40');
+
+con4: Updating Data
+UPDATE t1 SET details='ONLINETEST1' WHERE id=2;
+
+con5: Deleting Data
+DELETE FROM t3 WHERE id=30;
+
+con3: Signalling that BACKUP can finish
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+con1: Fetching result of BACKUP
+backup_id
+#
+
+con2: Inserting Data
+INSERT INTO t1 VALUES(1040,'online1040');
+
+con4: Updating Data
+UPDATE t2 SET info='t2 updated4' WHERE id=201;
+
+con6: **Checking the Data Contents before Restore**
+SELECT * FROM t1;
+id details
+1 testing1
+2 ONLINETEST1
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+110 new value added110
+1040 online1040
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+19
+SELECT * FROM t2;
+id info
+201 t2 updated4
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 xx1
+40 zz40
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+2
+
+con1: Dropping the database
+DROP DATABASE bup_online;
+con1: Restoring the database
+RESTORE FROM 'bup_online.bak';
+backup_id
+#
+
+*** RESULT AFTER RESTORE ***
+
+SELECT * FROM t1;
+id details
+1 testing1
+2 testing2
+3 testing3
+4 testing4
+5 testing5
+6 testing6
+7 ONLINETEST1
+8 ONLINETEST1
+9 ONLINETEST1
+10 ONLINETEST
+100 new value added100
+101 new value added101
+102 new value added102
+103 new value added103
+104 new value added103
+105 new value added105
+106 new value added106
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+17
+SELECT * FROM t2;
+id info
+201 updated values1
+202 updated values2
+203 Updated Values3
+204 Updated Values3
+205 Updated Values5
+206 Updated Values6
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+6
+SELECT *FROM t3;
+id name
+1 xx1
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+1
+
+*** DROP bup_online DATABASE ****
+
+DROP DATABASE bup_online;
=== added file 'mysql-test/suite/backup_engines/r/backup_procedures.result'
--- a/mysql-test/suite/backup_engines/r/backup_procedures.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/r/backup_procedures.result 2008-09-26 22:00:09 +0000
@@ -0,0 +1,175 @@
+SHOW VARIABLES LIKE 'storage_engine';
+Variable_name Value
+storage_engine #
+
+starting the test for backup
+
+DROP DATABASE IF EXISTS bup_sp;
+CREATE DATABASE bup_sp;
+USE bup_sp;
+*******Creating table******
+CREATE TABLE bup_sp.country(
+ccode CHAR(10),
+city VARCHAR(10)
+);
+*********loading data*********
+INSERT INTO bup_sp.country VALUES
+('IND','chennai'),('US','sfo'),('US','austin'),
+('IND','Bomb'),('US','Richmond'),('IND','hyd');
+SELECT * FROM bup_sp.country ORDER BY ccode, city;
+ccode city
+IND Bomb
+IND chennai
+IND hyd
+US austin
+US Richmond
+US sfo
+CREATE PROCEDURE countryinfo (IN loc CHAR(5))
+BEGIN
+SELECT * FROM country WHERE ccode=loc;
+END;
+||
+CREATE PROCEDURE dorepeat(p1 INT)
+BEGIN
+SET @x = 0;
+REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+END ;||
+CALL countryinfo('US');
+ccode city
+US sfo
+US austin
+US Richmond
+CALL countryinfo('IND');
+ccode city
+IND chennai
+IND Bomb
+IND hyd
+CALL dorepeat(1000);
+SELECT @x;
+@x
+1001
+showing objects and create statements.
+SHOW CREATE PROCEDURE countryinfo;;
+Procedure countryinfo
+sql_mode
+Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `countryinfo`(IN loc CHAR(5))
+BEGIN
+SELECT * FROM country WHERE ccode=loc;
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TABLE country;;
+Table country
+Create Table CREATE TABLE `country` (
+ `ccode` char(10) DEFAULT NULL,
+ `city` varchar(10) DEFAULT NULL
+) ENGINE=ENGINE DEFAULT CHARSET=latin1
+SHOW PROCEDURE STATUS;;
+Db bup_sp
+Name countryinfo
+Type PROCEDURE
+Definer #
+Modified #
+Created #
+Security_type #
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sp
+Name dorepeat
+Type PROCEDURE
+Definer #
+Modified #
+Created #
+Security_type #
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+backup data
+
+BACKUP DATABASE bup_sp TO 'bup_sp.bak';
+backup_id
+#
+dropping database.
+DROP DATABASE bup_sp;
+
+RESTORE FROM 'bup_sp.bak';
+backup_id
+#
+showing objects and create statements
+SHOW CREATE PROCEDURE countryinfo;;
+Procedure countryinfo
+sql_mode
+Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `countryinfo`(IN loc CHAR(5))
+BEGIN
+SELECT * FROM country WHERE ccode=loc;
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TABLE country;;
+Table country
+Create Table CREATE TABLE `country` (
+ `ccode` char(10) DEFAULT NULL,
+ `city` varchar(10) DEFAULT NULL
+) ENGINE=ENGINE DEFAULT CHARSET=latin1
+SHOW PROCEDURE STATUS;;
+Db bup_sp
+Name countryinfo
+Type PROCEDURE
+Definer #
+Modified #
+Created #
+Security_type #
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db bup_sp
+Name dorepeat
+Type PROCEDURE
+Definer #
+Modified #
+Created #
+Security_type #
+Comment
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+INSERT INTO bup_sp.country VALUES
+('US','boston'),('IND','jaipur'),('US','natick');
+SELECT * FROM bup_sp.country ORDER BY ccode, city;
+ccode city
+IND Bomb
+IND chennai
+IND hyd
+IND jaipur
+US austin
+US boston
+US natick
+US Richmond
+US sfo
+CALL countryinfo('IND');
+ccode city
+IND chennai
+IND Bomb
+IND hyd
+IND jaipur
+CALL countryinfo('US');
+ccode city
+US sfo
+US austin
+US Richmond
+US boston
+US natick
+CALL dorepeat(99);
+SELECT @x;
+@x
+100
+
+*** DROP bup_sp DATABASE ****
+
+DROP DATABASE bup_sp;
=== added file 'mysql-test/suite/backup_engines/r/backup_triggers.result'
--- a/mysql-test/suite/backup_engines/r/backup_triggers.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/r/backup_triggers.result 2008-09-26 22:00:09 +0000
@@ -0,0 +1,775 @@
+SHOW VARIABLES LIKE 'storage_engine';
+Variable_name Value
+storage_engine #
+
+starting the test for backup
+
+DROP DATABASE IF EXISTS bup_ts;
+CREATE DATABASE bup_ts;
+USE bup_ts;
+SELECT @@SQL_MODE;
+@@SQL_MODE
+
+*****Creating table cap ******
+CREATE TABLE bup_ts.cap(
+id SMALLINT,
+country CHAR(20),
+city VARCHAR(20),
+population BIGINT,
+capital CHAR(20)
+);
+******* Creating table city*****.
+CREATE TABLE bup_ts.city(
+id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+name VARCHAR(20),
+ccode CHAR(10),
+population bigint
+);
+*******Creating table dropcity *****
+CREATE TABLE bup_ts.dropcity(
+id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+name VARCHAR(20)
+);
+******Create Trigger for the table*******
+*******creating trigger with event before insert**********
+CREATE TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
+SET new.population= if(new.population<0,0,truncate(new.population,-3));||
+********creating trigger with event before Update******
+CREATE TRIGGER capu_tri BEFORE UPDATE ON cap FOR EACH ROW
+BEGIN
+SET @country=old.country;
+SET @capital_old=old.capital;
+SET @capital_new=new.capital;
+END;
+||
+*****creating trigger with event after delete********
+CREATE TRIGGER cityin_d AFTER
+DELETE ON city FOR EACH ROW
+INSERT INTO dropcity(id, name) VALUES(old.id, old.name);||
+Creating Tables
+CREATE TABLE bup_ts.t2(a CHAR(4));
+CREATE TABLE bup_ts.t3(a CHAR(4));
+CREATE TABLE bup_ts.t4(a CHAR(4));
+Creating Triggers
+Creating trigger with event after insert
+CREATE TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
+END;
+||
+Creating trigger with event after Update
+CREATE TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t4 VALUES('J'),('O'),('B');
+END;
+||
+Load data in to table
+********Inserting Values in tables*********
+INSERT INTO bup_ts.cap VALUES
+(1,'IND','chn',87874646468,'deli'),(2,'US','Austin',-6466547,'DC'),
+(3,'Russia','moscow',76487623235682,'moscow'),(4,'IND','bang',-83875477,'deli'),
+(5,'US','sacramento',-388348,'DC'),(6,'IND','jammu',3848488,'deli'),
+(7,'Russia','xx',-373788,'moscow'),(8,'IND','hyd',647747,'deli');
+INSERT INTO bup_ts.city VALUES
+(1,'aa','AD',263768),(2,'bb','PO',8839898),(3,'cc','KL',898778),
+(4,'dd','IK',73287328),(5,'ee','YU',89399),(6,'ff','IL',90880),
+(7,'gg','TY',345),(8,'hh','WE',9239084);
+Updating the table to fire trigger with event update
+UPDATE cap SET capital='Delhi' WHERE country='Ind';
+UPDATE cap SET capital='Washington DC' WHERE country='US';
+SELECT @country as country, @capital_old as 'old capital',
+@capital_new as 'new capital';
+country old capital new capital
+US DC Washington DC
+showing the tables to check the trigger is fired with event insert
+SELECT * FROM bup_ts.cap ORDER BY city;
+id country city population capital
+2 US Austin 0 Washington DC
+4 IND bang 0 Delhi
+1 IND chn 87874646000 Delhi
+8 IND hyd 647000 Delhi
+6 IND jammu 3848000 Delhi
+3 Russia moscow 76487623235000 moscow
+5 US sacramento 0 Washington DC
+7 Russia xx 0 moscow
+selecting data from table city
+SELECT * FROM bup_ts.city ORDER BY name;
+id name ccode population
+1 aa AD 263768
+2 bb PO 8839898
+3 cc KL 898778
+4 dd IK 73287328
+5 ee YU 89399
+6 ff IL 90880
+7 gg TY 345
+8 hh WE 9239084
+deleting few rows from table city to fire trigger with event delete.
+DELETE FROM bup_ts.city WHERE name='dd';
+DELETE FROM bup_ts.city WHERE name='bb';
+
+Checking table contents of city and dropcity to verify if trigger is
+fired for event delete.
+SELECT * FROM city ORDER BY name;
+id name ccode population
+1 aa AD 263768
+3 cc KL 898778
+5 ee YU 89399
+6 ff IL 90880
+7 gg TY 345
+8 hh WE 9239084
+SELECT * FROM dropcity ORDER BY id;
+id name
+2 bb
+4 dd
+******Firing Trigger after insert and after update *********
+INSERT INTO bup_ts.t2 values('a'),('k'),('*'),('i');
+SELECT * FROM bup_ts.t3;
+a
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+UPDATE t2 SET a='*' WHERE a='k';
+SELECT * FROM bup_ts.t4;
+a
+J
+O
+B
+Show the data and Create statements
+showing objects and create statements.
+SHOW TRIGGERS;;
+Trigger cap_tri
+Event INSERT
+Table cap
+Statement SET new.population= if(new.population<0,0,truncate(new.population,-3))
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger capu_tri
+Event UPDATE
+Table cap
+Statement BEGIN
+SET @country=old.country;
+SET @capital_old=old.capital;
+SET @capital_new=new.capital;
+END
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger cityin_d
+Event DELETE
+Table city
+Statement INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger trai
+Event INSERT
+Table t2
+Statement BEGIN
+INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
+END
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger trau
+Event UPDATE
+Table t2
+Statement BEGIN
+INSERT INTO t4 VALUES('J'),('O'),('B');
+END
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER cap_tri;;
+Trigger cap_tri
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
+SET new.population= if(new.population<0,0,truncate(new.population,-3))
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER capu_tri;;
+Trigger capu_tri
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER capu_tri BEFORE UPDATE ON cap FOR EACH ROW
+BEGIN
+SET @country=old.country;
+SET @capital_old=old.capital;
+SET @capital_new=new.capital;
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER cityin_d;;
+Trigger cityin_d
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cityin_d AFTER
+DELETE ON city FOR EACH ROW
+INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER trai;;
+Trigger trai
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER trau;;
+Trigger trau
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t4 VALUES('J'),('O'),('B');
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TABLE city;;
+Table city
+Create Table CREATE TABLE `city` (
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `name` varchar(20) DEFAULT NULL,
+ `ccode` char(10) DEFAULT NULL,
+ `population` bigint(20) DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=ENGINE AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;;
+Table t2
+Create Table CREATE TABLE `t2` (
+ `a` char(4) DEFAULT NULL
+) ENGINE=ENGINE DEFAULT CHARSET=latin1
+backup data
+BACKUP DATABASE bup_ts TO 'bup_ts.bak';
+backup_id
+#
+dropping database.
+DROP DATABASE bup_ts;
+perform restore
+RESTORE FROM 'bup_ts.bak';
+backup_id
+#
+show data and create statements after Restore
+showing objects and create statements
+SHOW CREATE DATABASE bup_ts;;
+Database bup_ts
+Create Database CREATE DATABASE `bup_ts` /*!40100 DEFAULT CHARACTER SET latin1 */
+SHOW CREATE TRIGGER cap_tri;;
+Trigger cap_tri
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
+SET new.population= if(new.population<0,0,truncate(new.population,-3))
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER cityin_d;;
+Trigger cityin_d
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER cityin_d AFTER
+DELETE ON city FOR EACH ROW
+INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER trai;;
+Trigger trai
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TRIGGER trau;;
+Trigger trau
+sql_mode
+SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t4 VALUES('J'),('O'),('B');
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW TRIGGERS;;
+Trigger cap_tri
+Event INSERT
+Table cap
+Statement SET new.population= if(new.population<0,0,truncate(new.population,-3))
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger capu_tri
+Event UPDATE
+Table cap
+Statement BEGIN
+SET @country=old.country;
+SET @capital_old=old.capital;
+SET @capital_new=new.capital;
+END
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger cityin_d
+Event DELETE
+Table city
+Statement INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger trai
+Event INSERT
+Table t2
+Statement BEGIN
+INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
+END
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger trau
+Event UPDATE
+Table t2
+Statement BEGIN
+INSERT INTO t4 VALUES('J'),('O'),('B');
+END
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Inserting some more values in the table to check the trigger with event insert
+INSERT INTO bup_ts.cap VALUES
+(9,'US','houston',-33399587677866656555655555655,'texas');
+Warnings:
+Warning 1264 Out of range value for column 'population' at row 1
+SELECT * FROM bup_ts.cap ORDER BY city;
+id country city population capital
+2 US Austin 0 Washington DC
+4 IND bang 0 Delhi
+1 IND chn 87874646000 Delhi
+9 US houston 0 texas
+8 IND hyd 647000 Delhi
+6 IND jammu 3848000 Delhi
+3 Russia moscow 76487623235000 moscow
+5 US sacramento 0 Washington DC
+7 Russia xx 0 moscow
+Deleting some rows from city to check the trigger with event delete
+DELETE FROM city WHERE name='aa';
+Checking table city and drop city
+SELECT * FROM bup_ts.city ORDER BY name;
+id name ccode population
+3 cc KL 898778
+5 ee YU 89399
+6 ff IL 90880
+7 gg TY 345
+8 hh WE 9239084
+SELECT * FROM bup_ts.dropcity ORDER BY id;
+id name
+1 aa
+2 bb
+4 dd
+INSERT INTO bup_ts.t2 VALUES('m'),('s1'),('s'),('q'),('l');
+SELECT * FROM bup_ts.t3;
+a
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+UPDATE t2 SET a='y' WHERE a='s1';
+SELECT * FROM bup_ts.t4;
+a
+J
+O
+B
+J
+O
+B
+DROP DATABASE bup_ts;
+Change sql mode and perform Restore and check the table contents.
+SET SQL_MODE=TRADITIONAL;
+RESTORE FROM 'bup_ts.bak';
+backup_id
+#
+SHOW TRIGGERS;;
+Trigger cap_tri
+Event INSERT
+Table cap
+Statement SET new.population= if(new.population<0,0,truncate(new.population,-3))
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger capu_tri
+Event UPDATE
+Table cap
+Statement BEGIN
+SET @country=old.country;
+SET @capital_old=old.capital;
+SET @capital_new=new.capital;
+END
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger cityin_d
+Event DELETE
+Table city
+Statement INSERT INTO dropcity(id, name) VALUES(old.id, old.name)
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger trai
+Event INSERT
+Table t2
+Statement BEGIN
+INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
+END
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger trau
+Event UPDATE
+Table t2
+Statement BEGIN
+INSERT INTO t4 VALUES('J'),('O'),('B');
+END
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE TABLE t2;;
+Table t2
+Create Table CREATE TABLE `t2` (
+ `a` char(4) DEFAULT NULL
+) ENGINE=ENGINE DEFAULT CHARSET=latin1
+SHOW CREATE TABLE city;;
+Table city
+Create Table CREATE TABLE `city` (
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `name` varchar(20) DEFAULT NULL,
+ `ccode` char(10) DEFAULT NULL,
+ `population` bigint(20) DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=ENGINE AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE cap;;
+Table cap
+Create Table CREATE TABLE `cap` (
+ `id` smallint(6) DEFAULT NULL,
+ `country` char(20) DEFAULT NULL,
+ `city` varchar(20) DEFAULT NULL,
+ `population` bigint(20) DEFAULT NULL,
+ `capital` char(20) DEFAULT NULL
+) ENGINE=ENGINE DEFAULT CHARSET=latin1
+Check contents after Restore and change of SQL mode
+INSERT INTO bup_ts.cap VALUES(10,'India','Daund',36637782899,'maharashtra');
+SELECT * FROM bup_ts.cap ORDER BY city;
+id country city population capital
+2 US Austin 0 Washington DC
+4 IND bang 0 Delhi
+1 IND chn 87874646000 Delhi
+10 India Daund 36637782000 maharashtra
+8 IND hyd 647000 Delhi
+6 IND jammu 3848000 Delhi
+3 Russia moscow 76487623235000 moscow
+5 US sacramento 0 Washington DC
+7 Russia xx 0 moscow
+INSERT INTO bup_ts.t2 VALUES('i'),('w');
+SELECT * FROM t3;
+a
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+UPDATE t2 SET a='^' WHERE a='w';
+SELECT * FROM t4;
+a
+J
+O
+B
+J
+O
+B
+DROP DATABASE bup_ts;
+change of SQL mode
+SET SQL_MODE=MAXDB;
+RESTORE FROM 'bup_ts.bak';
+backup_id
+#
+Check contents after Restore and change of SQL mode
+INSERT INTO bup_ts.cap VALUES(11,'UK','Edenburg',-36637782899,'london');
+SELECT * FROM bup_ts.cap ORDER BY city;
+id country city population capital
+2 US Austin 0 Washington DC
+4 IND bang 0 Delhi
+1 IND chn 87874646000 Delhi
+11 UK Edenburg 0 london
+8 IND hyd 647000 Delhi
+6 IND jammu 3848000 Delhi
+3 Russia moscow 76487623235000 moscow
+5 US sacramento 0 Washington DC
+7 Russia xx 0 moscow
+INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h');
+SELECT * FROM bup_ts.t3;
+a
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+UPDATE t2 SET a='p' WHERE a='h';
+SELECT * FROM bup_ts.t4;
+a
+J
+O
+B
+J
+O
+B
+DROP DATABASE bup_ts;
+Chnage SQL mode and then perform Restore.
+SET SQL_MODE=ANSI;
+RESTORE FROM 'bup_ts.bak';
+backup_id
+#
+Check contents after Restore and change of SQL mode
+INSERT INTO bup_ts.cap VALUES(12,'India','vooty',789,'Tamilnadu');
+SELECT * FROM bup_ts.cap ORDER BY city;
+id country city population capital
+2 US Austin 0 Washington DC
+4 IND bang 0 Delhi
+1 IND chn 87874646000 Delhi
+8 IND hyd 647000 Delhi
+6 IND jammu 3848000 Delhi
+3 Russia moscow 76487623235000 moscow
+5 US sacramento 0 Washington DC
+12 India vooty 0 Tamilnadu
+7 Russia xx 0 moscow
+INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h'),('b');
+SELECT * FROM bup_ts.t3;
+a
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+H
+E
+L
+L
+O
+UPDATE bup_ts.t2 SET a='#' WHERE a='w';
+SELECT * FROM bup_ts.t4;
+a
+J
+O
+B
+J
+O
+B
+
+*** DROP bup_ts DATABASE ****
+
+DROP DATABASE bup_ts;
=== added directory 'mysql-test/suite/backup_engines/t'
=== added file 'mysql-test/suite/backup_engines/t/backup_functions.test'
--- a/mysql-test/suite/backup_engines/t/backup_functions.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/t/backup_functions.test 2008-09-26 22:00:09 +0000
@@ -0,0 +1,273 @@
+###########################################################################
+# Author: Hema
+# Date: 2008-03-11
+# Purpose: To test the metadata consistency of object (stored functions)
+# after backup and Restore.
+###############################################################################
+--source include/not_embedded.inc
+--source suite/backup_engines/include/backup_engine.inc
+--source include/have_debug.inc
+
+connect (backup,localhost,root,,);
+connect (breakpoints,localhost,root,,);
+
+##############################################################
+--echo
+--echo starting the test for backup
+--echo
+##############################################################
+
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_sfs.bak
+
+#Create Database and object stored function for this test.
+
+--disable_warnings
+DROP DATABASE IF EXISTS bup_sfs;
+--enable_warnings
+
+CREATE DATABASE bup_sfs;
+USE bup_sfs;
+
+SET SQL_MODE=TRADITIONAL;
+
+#Create table and load with data.
+
+--echo Creating table
+
+CREATE TABLE bup_sfs.players(
+playerno INT not null PRIMARY KEY,
+name CHAR(10),
+Bdate DATE,
+sex CHAR(2),
+joined YEAR
+);
+
+CREATE TABLE bup_sfs.teams(
+teamno INT NOT NULL PRIMARY KEY,
+playerno INT,
+division CHAR(10),
+FOREIGN KEY(playerno) REFERENCES players(playerno)
+);
+
+CREATE TABLE bup_sfs.matches(
+Matchno INT NOT NULL PRIMARY KEY,
+teamno INT ,
+playerno INT,
+won INT,
+lost INT,
+FOREIGN KEY(teamno) REFERENCES teams(teamno),
+FOREIGN KEY(playerno) REFERENCES players(playerno)
+);
+
+CREATE TABLE bup_sfs.penalties(
+paymentno INT not null PRIMARY KEY,
+playerno INT,
+paymentdate DATE,
+amount FLOAT,
+FOREIGN KEY(playerno) REFERENCES players(playerno)
+);
+
+--echo Load Data in tables
+
+INSERT INTO bup_sfs.players VALUES
+(1,'aa1','1948-10-09','M','1960'),(2,'aa2','1950-05-09','M','1965'),
+(3,'aa3','1953-10-10','F','1970'),(4,'aa4','1970-10-19','M','1985'),
+(5,'aa5','1972-04-28','F','1994'),(6,'aa6','1945-01-26','M','1960');
+
+INSERT INTO bup_sfs.teams VALUES(1,3,'first'),(2,4,'division');
+
+INSERT INTO bup_sfs.matches VALUES
+(1,1,2,3,1),(2,2,1,4,2),(3,1,5,0,1),(4,2,4,3,0),(5,2,5,2,2),
+(6,1,3,3,1),(7,2,1,4,2),(8,1,5,0,1),(9,2,2,3,0),(10,2,2,2,2);
+
+INSERT INTO bup_sfs.penalties VALUES
+(1,5,'1980-10-10',100.23),(2,3,'1967-12-10',123.23),
+(3,3,'1983-05-25',25.25), (4,4,'1981-11-10',210.23) ,
+(5,5,'1980-12-12',300), (6,1,'1984-07-02',10),
+(7,1,'1985-10-10',23.90);
+
+#Create stored functions for the table
+
+delimiter ||;
+CREATE FUNCTION number_of_players() RETURNS INTEGER
+BEGIN
+RETURN (SELECT COUNT(*) FROM players);
+END;
+||
+
+CREATE FUNCTION number_of_players1() RETURNS INTEGER
+BEGIN
+RETURN (SELECT COUNT(*) FROM players WHERE playerno > 2);
+END;
+||
+
+CREATE FUNCTION fcount() RETURNS INTEGER
+BEGIN
+ DECLARE v_out INT;
+ SELECT count(*) FROM matches INTO v_out;
+ RETURN v_out;
+END;
+||
+
+CREATE FUNCTION dollars(amount FLOAT(7,2)) RETURNS FLOAT(7,2)
+BEGIN
+ RETURN amount * (1/0.8);
+END;
+||
+
+delimiter ;||
+
+#Excercise the objects
+
+SELECT number_of_players();
+SELECT number_of_players1();
+SELECT fcount();
+SELECT paymentno,amount, dollars(amount) FROM penalties;
+--echo creating stored functions which is not dependent on table
+#BUG#34868 Backup: restore failure if two procedures but this doesn't happen for
+# procedures in database, which has atleast one table
+
+
+CREATE FUNCTION square_area(side tinyINT)
+RETURNS FLOAT
+RETURN side * side;
+
+#excercise function
+
+SELECT square_area(11);
+
+#Show data and create statements.
+--echo showing objects and create statements.
+--query_vertical SHOW CREATE FUNCTION number_of_players;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE players;
+describe penalties;
+--replace_column 4 # 5 # 6 #
+--query_vertical SHOW FUNCTION STATUS;
+
+#Backup and restore data.
+--echo backup data
+--replace_column 1 #
+BACKUP DATABASE bup_sfs TO 'bup_sfs.bak';
+
+--echo dropping database.
+DROP DATABASE bup_sfs;
+
+--echo Restoring Database
+
+# BUG#35118 Backup:Restore fails if previous create Procedure/Function/Trigger
+# command fails
+
+--replace_column 1 #
+RESTORE FROM 'bup_sfs.bak';
+SELECT @@SQL_MODE;
+
+#show data and create statements
+--echo showing objects and create statements
+--query_vertical SHOW CREATE DATABASE bup_sfs;
+--query_vertical SHOW CREATE FUNCTION number_of_players;
+describe penalties;
+describe matches;
+--replace_column 4 # 5 # 6 #
+
+--query_vertical SHOW FUNCTION STATUS;
+
+#Again calling function to check if Sf are backed up and restored properly or
+# not.
+
+INSERT INTO bup_sfs.players VALUES
+(57,'brown','1971-08-17','M','1985'),(95,'Miller','1963-05-14','M','1972');
+INSERT INTO bup_sfs.matches VALUES(11,2,6,0,3);
+
+SELECT square_area(12);
+SELECT number_of_players();
+SELECT number_of_players1();
+SELECT fcount();
+SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno<=3;
+
+#dropping Database again.
+DROP DATABASE bup_sfs;
+
+--echo change SQL_MODE and then perform RESTORE operation
+
+SET SQL_MODE=ANSI;
+--replace_column 1 #
+RESTORE FROM 'bup_sfs.bak';
+
+SELECT @@SQL_MODE;
+
+#show data and create statements
+--echo showing objects and create statements
+--query_vertical SHOW CREATE DATABASE bup_sfs;
+--query_vertical SHOW CREATE FUNCTION number_of_players;
+--replace_column 4 # 5 # 6 #
+
+--query_vertical SHOW FUNCTION STATUS;
+
+#Again calling function to check if Sf are backed up and restored
+#properly or not.
+
+SELECT square_area(13);
+SELECT number_of_players();
+SELECT fcount();
+DROP DATABASE bup_sfs;
+
+--echo change SQL_MODE and then perform RESTORE
+
+SET SQL_MODE=MAXDB;
+--replace_column 1 #
+RESTORE FROM 'bup_sfs.bak';
+
+SELECT @@SQL_MODE;
+
+#show data and create statements
+--echo showing objects and create statements
+--query_vertical SHOW CREATE DATABASE bup_sfs;
+--query_vertical SHOW CREATE FUNCTION number_of_players;
+--replace_column 4 # 5 # 6 #
+--query_vertical SHOW FUNCTION STATUS;
+
+#Again calling function to check if Sf are backed up and restored properly
+#or not.
+
+SELECT square_area(14);
+SELECT number_of_players();
+SELECT fcount();
+SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno >=3;
+DROP DATABASE bup_sfs;
+--echo change SQL_MODE and then perform RESTORE
+
+SET SQL_MODE=' ';
+--replace_column 1 #
+RESTORE FROM 'bup_sfs.bak';
+
+SELECT @@SQL_MODE;
+#show data and create statements
+--echo showing objects and create statements
+--query_vertical SHOW CREATE DATABASE bup_sfs;
+--query_vertical SHOW CREATE FUNCTION number_of_players;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE players;
+describe matches;
+--replace_column 4 # 5 # 6 #
+--query_vertical SHOW FUNCTION STATUS;
+
+#Again calling function to check if Sf are backed up and restored properly
+# ornot
+
+SELECT square_area(20);
+SELECT number_of_players();
+SELECT fcount();
+SELECT paymentno,amount, dollars(amount) FROM penalties;
+
+# Test cleanup section
+
+--echo
+--echo *** DROP bup_sfs DATABASE ****
+--echo
+
+DROP DATABASE bup_sfs;
+
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_sfs.bak
+
=== added file 'mysql-test/suite/backup_engines/t/backup_online_testing.test'
--- a/mysql-test/suite/backup_engines/t/backup_online_testing.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/t/backup_online_testing.test 2008-09-26 22:00:09 +0000
@@ -0,0 +1,1154 @@
+###############################################################################
+# Author: Hema
+# Date: 2008-04-01
+# Purpose: Verify Online capability during differnt phases of BACKUP database
+# operation.
+# We will ensure that Ongoing transactions are not affected by Backup Database
+# command.
+# To perform this test we need backup and DML operations going in parallel.
+# We can make use of sync points to pause BACKUP database at different stages
+# and execute DML statements. Further we can confirm that the BACKUP database
+# operation is completed and the DML executed after 'validity point of time'
+# aren't included in the backup.
+# We establish multiple connections and for concurrency we use Send and Reap
+# commands where required for statements that are blocked.
+# SEND issues a statement and does not wait for the result.
+# REAP waits for and returns the result of the statement issued by SEND.
+#
+# Breakpoints Usage:
+# before_backup_data_init, before_backup_data_prepare, before_backup_command,
+# before_backup_meta, before_backup_data are the Breakpoints that occur
+# before Validity point of time.
+#
+# before_backup_data_init occurs at the start of INITIALIZE phase.
+# before_backup_data_prepare occurs at the start of PREPARE phase.
+# before_backup_command occurs at the start of BACKUP Operation.
+# before_backup_meta occurs before the call to write metadata.
+# before_backup_data occurs before the call to write table data.
+#
+# before_backup_data_lock, before_backup_data_unlock,
+# before_backup_data_finish and before_backup_done are the breakpoints
+# that occurs after validity point of time. The DML executed after
+# 'validity point of time' aren't included in the backup.
+#
+# before_backup_data_lock occurs before the engines are locked.
+# before_backup_data_unlock occurs before the engines are unlocked.
+# before_backup_data_finish occurs before the final data saving.
+# before_backup_done occurs at the end of the backup statement.
+###############################################################################
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+--source include/not_embedded.inc
+
+#
+# Preparatory cleanup.
+#
+--disable_warnings
+# Initialize the debug sync facility.
+SET DEBUG_SYNC= 'RESET';
+DROP DATABASE IF EXISTS bup_online;
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+--enable_warnings
+
+##############################################################
+--echo
+--echo starting the test for backup
+--echo
+##############################################################
+
+CREATE DATABASE bup_online;
+
+--echo Establishing connections
+--echo ** con1 to perform BACKUP AND RESTORE**
+--echo ** con2 and con2a to do INSERTS**
+--echo ** con3 to synchronize with BACKUP
+--echo ** con4 to do UPDATES**
+--echo ** con5 to do DELETES**
+--echo ** con6 to check table contents**
+
+#
+# Set the startup database for all connections to bup_online.
+#
+connect (con1,localhost,root,,bup_online);
+connect (con2,localhost,root,,bup_online);
+connect (con2a,localhost,root,,bup_online);
+connect (con3,localhost,root,,bup_online);
+connect (con4,localhost,root,,bup_online);
+connect (con5,localhost,root,,bup_online);
+connect (con6,localhost,root,,bup_online);
+
+#
+# Create table and load with data.
+#
+--echo
+--echo con1: ***Creating Tables***
+connection con1;
+
+CREATE TABLE t1(id int, details char(30))ENGINE=INNODB;
+INSERT INTO t1 VALUES(1,'testing1'),(2,'testing2'),(3,'testing3'),
+ (4,'testing4'),(5,'testing5'),(6,'testing6'),(7,'tesing7'),(8,'testing8'),
+ (9,'testing9'),(10,'testing10'),(11,'testing11'),(12,'testing12');
+
+CREATE TABLE t2(id int, info char(30))ENGINE=INNODB;
+INSERT INTO t2 VALUES(201, 'aa1'),(202,'aa2');
+
+CREATE TABLE t3(id int, name char(20))ENGINE=INNODB;
+INSERT INTO t3 VALUES(1,'xx1'),(2,'xx2'),(3,'xx3'),(4,'xx4'),(5,'xx5');
+
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(*) FROM t3;
+
+
+--echo
+--echo **TEST1**
+################Test1##############################
+##use of Breakpoint before_backup_data_init #
+###################################################
+
+#################
+#BACKUP DATABASE#
+#################
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_data_init SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(100,'new value added100');
+INSERT INTO t1 VALUES(101,'new value added101');
+INSERT INTO t1 VALUES(102,'new value added102');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t2 SET info='updated values1' WHERE id=201;
+UPDATE t2 SET info='updated values2' WHERE id=202;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t1 WHERE id=11;
+DELETE FROM t1 WHERE id=12;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(500,'online500');
+INSERT INTO t1 VALUES(600,'online600');
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t1 SET details='ONLINE1' WHERE id=10;
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t2 WHERE id=201;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
+
+--echo
+--echo **TEST2**
+################Test2##############################
+##use of Breakpoint before_backup_data_prepare #
+###################################################
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_data_prepare SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online1.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(103,'new value added103');
+INSERT INTO t2 VALUES(203,'Updated Values3');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t1 SET details='ONLINETEST' WHERE id=10;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t3 WHERE id=5;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(700,'online700');
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t3 SET name='yy' WHERE id=3;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online1.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online1.bak;
+
+
+--echo
+--echo **TEST3**
+################Test3##############################
+##use of Breakpoint before_backup_command #
+###################################################
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_command SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(104,'new value added103');
+INSERT INTO t2 VALUES(204,'Updated Values3');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t1 SET details='ONLINETEST1' WHERE id=9;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t3 WHERE id=4;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(800,'online800');
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t3 SET name='zz' WHERE id=2;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
+
+--echo
+--echo **TEST4**
+################Test4##############################
+##use of Breakpoint before_backup_meta #
+###################################################
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_meta SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(105,'new value added105');
+INSERT INTO t2 VALUES(205,'Updated Values5');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t1 SET details='ONLINETEST1' WHERE id=8;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t3 WHERE id=3;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(900,'online900');
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t3 SET name='tt' WHERE id=2;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
+
+--echo
+--echo **TEST5**
+################Test5##############################
+##use of Breakpoint before_backup_data #
+###################################################
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_data SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(106,'new value added106');
+INSERT INTO t2 VALUES(206,'Updated Values6');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t1 SET details='ONLINETEST1' WHERE id=7;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t3 WHERE id=2;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(1000,'online1000');
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t3 SET name='uu' WHERE id=1;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
+
+--echo
+--echo **TEST6**
+################Test6##############################
+##use of Breakpoint before_backup_data_lock #
+###################################################
+
+# In this test we need to send the DML statements to the background.
+# They block in wait_if_global_read_lock.
+# BACKUP is blocked at the sync point "data_lock", which occurs after
+# block_commits(), which locks the global read lock, and before
+# unblock_commits(), which releases the global read lock.
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_data_lock SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Starting Insert Data
+connection con2;
+send INSERT INTO t1 VALUES(107,'new value added107');
+
+--echo
+--echo con2a: Starting Insert Data
+connection con2a;
+send INSERT INTO t2 VALUES(207,'Updated Values7');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Starting Update data
+connection con4;
+send UPDATE t1 SET details='ONLINETEST1' WHERE id=6;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Starting Delete data
+connection con5;
+send DELETE FROM t3 WHERE id=1;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Fetching result of INSERT and Insert more data
+connection con2;
+reap;
+INSERT INTO t1 VALUES(1010,'online1010');
+
+--echo
+--echo con2a: Fetching result of INSERT
+connection con2a;
+reap;
+
+--echo
+--echo con4: Fetching result of UPDATE and Update more data
+connection con4;
+reap;
+UPDATE t2 SET info='t2 updated1' WHERE id=205;
+
+--echo
+--echo con5: Fetching result of DELETE
+connection con5;
+reap;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
+
+--echo
+--echo **TEST7**
+################Test7##############################
+##use of Breakpoint before_backup_data_unlock #
+###################################################
+
+# In this test we need to send the DML statements to the background.
+# They block in wait_if_global_read_lock.
+# BACKUP is blocked at the sync point "data_unlock", which occurs before
+# unblock_commits(), which releases the global read lock and after
+# block_commits(), which locks the global read lock.
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_data_unlock SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Starting Insert Data
+connection con2;
+send INSERT INTO t1 VALUES(108,'new value added108');
+
+--echo
+--echo con2a: Starting Insert Data
+connection con2a;
+send INSERT INTO t3 VALUES(20,'zz20');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Starting Update data
+connection con4;
+send UPDATE t1 SET details='ONLINETEST1' WHERE id=4;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Starting Delete data
+connection con5;
+send DELETE FROM t3 WHERE id=1;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Fetching result of INSERT and Insert more data
+connection con2;
+reap;
+INSERT INTO t1 VALUES(1020,'online1020');
+
+--echo
+--echo con2a: Fetching result of INSERT
+connection con2a;
+reap;
+
+--echo
+--echo con4: Fetching result of UPDATE and Update more data
+connection con4;
+reap;
+UPDATE t2 SET info='t2 updated2' WHERE id=206;
+
+--echo
+--echo con5: Fetching result of DELETE
+connection con5;
+reap;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
+
+--echo
+--echo **TEST8**
+################Test8##############################
+##use of Breakpoint before_backup_data_finish #
+###################################################
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_data_finish SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(109,'new value added109');
+INSERT INTO t3 VALUES(30,'zz30');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t1 SET details='ONLINETEST1' WHERE id=3;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t3 WHERE id=20;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(1030,'online1030');
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t2 SET info='t2 updated3' WHERE id=207;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
+
+--echo
+--echo **TEST9**
+################Test9##############################
+##use of Breakpoint before_backup_done #
+###################################################
+
+--echo
+--echo con1: Starting Backup Data
+connection con1;
+# Activate the sync point for BACKUP.
+# When BACKUP reaches the sync point it emits the signal bup_sync
+# and waits for another thread to emit the signal bup_finish.
+SET DEBUG_SYNC= 'before_backup_done SIGNAL bup_sync
+ WAIT_FOR bup_finish';
+send BACKUP DATABASE bup_online TO 'bup_online.bak';
+
+--echo
+--echo con3: Waiting for BACKUP to reach the breakpoint
+connection con3;
+# If BACKUP did already reach its sync point, then the signal is already
+# present. If not, we wait until BACKUP emits it.
+SET DEBUG_SYNC= 'now WAIT_FOR bup_sync';
+
+#############
+#INSERT DATA#
+#############
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(110,'new value added110');
+INSERT INTO t3 VALUES(40,'zz40');
+
+#############
+#UPDATE DATA#
+#############
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t1 SET details='ONLINETEST1' WHERE id=2;
+
+#############
+#Delete Data#
+#############
+
+--echo
+--echo con5: Deleting Data
+connection con5;
+DELETE FROM t3 WHERE id=30;
+
+--echo
+--echo con3: Signalling that BACKUP can finish
+connection con3;
+SET DEBUG_SYNC= 'now SIGNAL bup_finish';
+
+--echo
+--echo con1: Fetching result of BACKUP
+connection con1;
+--replace_column 1 #
+reap;
+
+--echo
+--echo con2: Inserting Data
+connection con2;
+INSERT INTO t1 VALUES(1040,'online1040');
+
+--echo
+--echo con4: Updating Data
+connection con4;
+UPDATE t2 SET info='t2 updated4' WHERE id=201;
+
+--echo
+--echo con6: **Checking the Data Contents before Restore**
+connection con6;
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+##################
+#RESTORE DATABASE#
+##################
+
+--echo
+--echo con1: Dropping the database
+connection con1;
+DROP DATABASE bup_online;
+
+--echo con1: Restoring the database
+--replace_column 1 #
+RESTORE FROM 'bup_online.bak';
+
+#check data contents after Restore.
+--echo
+--echo *** RESULT AFTER RESTORE ***
+--echo
+
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t2;
+SELECT COUNT(*) FROM t2;
+SELECT *FROM t3;
+SELECT COUNT(*) FROM t3;
+
+# Test cleanup section
+
+--echo
+--echo *** DROP bup_online DATABASE ****
+--echo
+
+DROP DATABASE bup_online;
+
+remove_file $MYSQLTEST_VARDIR/master-data/bup_online.bak;
+
=== added file 'mysql-test/suite/backup_engines/t/backup_procedures.test'
--- a/mysql-test/suite/backup_engines/t/backup_procedures.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/t/backup_procedures.test 2008-09-26 22:00:09 +0000
@@ -0,0 +1,124 @@
+###########################################################################
+# Author: Hema
+# Date: 2008-03-11
+# Purpose: To test the metadata consistency object stored procedures for all
+# storage engines.
+###############################################################################
+--source include/not_embedded.inc
+--source suite/backup_engines/include/backup_engine.inc
+--source include/have_debug.inc
+
+connect (backup,localhost,root,,);
+connect (breakpoints,localhost,root,,);
+
+##############################################################
+--echo
+--echo starting the test for backup
+--echo
+##############################################################
+
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_sp.bak
+
+# Multiple instances of stored routines without atleast one table in
+# database results in failure.
+# BUG#34868
+
+--disable_warnings
+DROP DATABASE IF EXISTS bup_sp;
+--enable_warnings
+
+CREATE DATABASE bup_sp;
+USE bup_sp;
+
+#Create table with all storage engines and load with data.
+
+--echo *******Creating table******
+
+CREATE TABLE bup_sp.country(
+ccode CHAR(10),
+city VARCHAR(10)
+);
+
+--echo *********loading data*********
+INSERT INTO bup_sp.country VALUES
+('IND','chennai'),('US','sfo'),('US','austin'),
+('IND','Bomb'),('US','Richmond'),('IND','hyd');
+
+SELECT * FROM bup_sp.country ORDER BY ccode, city;
+
+#Create stored procedures for the table
+
+delimiter ||;
+CREATE PROCEDURE countryinfo (IN loc CHAR(5))
+BEGIN
+ SELECT * FROM country WHERE ccode=loc;
+END;
+||
+
+CREATE PROCEDURE dorepeat(p1 INT)
+ BEGIN
+ SET @x = 0;
+ REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
+ END ;||
+delimiter ;||
+
+#Excercise the objects
+
+CALL countryinfo('US');
+CALL countryinfo('IND');
+CALL dorepeat(1000);
+SELECT @x;
+#Show the data and Create statements
+
+--echo showing objects and create statements.
+--query_vertical SHOW CREATE PROCEDURE countryinfo;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE country;
+--replace_column 4 # 5 # 6 # 7 #
+--query_vertical SHOW PROCEDURE STATUS;
+
+#Backup and restore data.
+
+--echo backup data
+--echo
+--replace_column 1 #
+BACKUP DATABASE bup_sp TO 'bup_sp.bak';
+--echo dropping database.
+DROP DATABASE bup_sp;
+
+#RESTORE FROM bup_sp.bak;
+--echo
+--replace_column 1 #
+RESTORE FROM 'bup_sp.bak';
+
+#show data and create statements
+--echo showing objects and create statements
+--query_vertical SHOW CREATE PROCEDURE countryinfo;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE country;
+--replace_column 4 # 5 # 6 # 7 #
+--query_vertical SHOW PROCEDURE STATUS;
+
+
+#Excercise the objects after restore to make sure if objects are backedup and
+#restored properly.
+
+INSERT INTO bup_sp.country VALUES
+('US','boston'),('IND','jaipur'),('US','natick');
+SELECT * FROM bup_sp.country ORDER BY ccode, city;
+CALL countryinfo('IND');
+CALL countryinfo('US');
+CALL dorepeat(99);
+SELECT @x;
+
+# Test cleanup section
+
+--echo
+--echo *** DROP bup_sp DATABASE ****
+--echo
+
+DROP DATABASE bup_sp;
+
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_sp.bak
+
=== added file 'mysql-test/suite/backup_engines/t/backup_triggers.test'
--- a/mysql-test/suite/backup_engines/t/backup_triggers.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/t/backup_triggers.test 2008-09-26 22:00:09 +0000
@@ -0,0 +1,298 @@
+
+##################################################################
+# Author: Hema
+# Date: 2008-05-28
+# Purpose: To test the metadata consistency of object trigger for all
+# storage engines
+###################################################################
+
+--source include/not_embedded.inc
+--source suite/backup_engines/include/backup_engine.inc
+--source include/have_debug.inc
+
+connect (backup,localhost,root,,);
+connect (breakpoints,localhost,root,,);
+
+##############################################################
+--echo
+--echo starting the test for backup
+--echo
+##############################################################
+
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_ts.bak
+
+--error 0,1
+--disable_warnings
+#Create Database and object trigger for this test.
+
+DROP DATABASE IF EXISTS bup_ts;
+--enable_warnings
+
+CREATE DATABASE bup_ts;
+USE bup_ts;
+
+#Create table and load with data.
+SELECT @@SQL_MODE;
+--echo *****Creating table cap ******
+
+CREATE TABLE bup_ts.cap(
+id SMALLINT,
+country CHAR(20),
+city VARCHAR(20),
+population BIGINT,
+capital CHAR(20)
+);
+
+--echo ******* Creating table city*****.
+
+CREATE TABLE bup_ts.city(
+id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+name VARCHAR(20),
+ccode CHAR(10),
+population bigint
+);
+
+--echo *******Creating table dropcity *****
+
+CREATE TABLE bup_ts.dropcity(
+id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
+name VARCHAR(20)
+);
+
+--echo ******Create Trigger for the table*******
+# BUG#35118 Restore fails if previous create Procedure/Function/Trigger
+# command fails
+
+delimiter ||;
+
+--echo *******creating trigger with event before insert**********
+#BUG#34759 Server crash when backing-up database with after insert
+# trigger with event delete.
+
+CREATE TRIGGER cap_tri BEFORE INSERT ON cap FOR EACH ROW
+SET new.population= if(new.population<0,0,truncate(new.population,-3));||
+
+--echo ********creating trigger with event before Update******
+CREATE TRIGGER capu_tri BEFORE UPDATE ON cap FOR EACH ROW
+BEGIN
+ SET @country=old.country;
+ SET @capital_old=old.capital;
+ SET @capital_new=new.capital;
+END;
+||
+
+--echo *****creating trigger with event after delete********
+
+CREATE TRIGGER cityin_d AFTER
+DELETE ON city FOR EACH ROW
+INSERT INTO dropcity(id, name) VALUES(old.id, old.name);||
+
+--echo Creating Tables
+delimiter ;||
+
+CREATE TABLE bup_ts.t2(a CHAR(4));
+CREATE TABLE bup_ts.t3(a CHAR(4));
+CREATE TABLE bup_ts.t4(a CHAR(4));
+
+--echo Creating Triggers
+
+--echo Creating trigger with event after insert
+#BUG#34759 Server crash when backing-up database with after insert trigger with
+# event delete.
+
+delimiter ||;
+
+CREATE TRIGGER trai AFTER INSERT ON t2 FOR EACH ROW
+BEGIN
+ INSERT INTO t3 VALUES('H'),('E'),('L'),('L'),('O');
+END;
+||
+
+--echo Creating trigger with event after Update
+
+CREATE TRIGGER trau AFTER UPDATE ON t2 FOR EACH ROW
+BEGIN
+ INSERT INTO t4 VALUES('J'),('O'),('B');
+END;
+||
+
+delimiter ;||
+
+--echo Load data in to table
+--echo ********Inserting Values in tables*********
+
+INSERT INTO bup_ts.cap VALUES
+(1,'IND','chn',87874646468,'deli'),(2,'US','Austin',-6466547,'DC'),
+(3,'Russia','moscow',76487623235682,'moscow'),(4,'IND','bang',-83875477,'deli'),
+(5,'US','sacramento',-388348,'DC'),(6,'IND','jammu',3848488,'deli'),
+(7,'Russia','xx',-373788,'moscow'),(8,'IND','hyd',647747,'deli');
+
+INSERT INTO bup_ts.city VALUES
+(1,'aa','AD',263768),(2,'bb','PO',8839898),(3,'cc','KL',898778),
+(4,'dd','IK',73287328),(5,'ee','YU',89399),(6,'ff','IL',90880),
+(7,'gg','TY',345),(8,'hh','WE',9239084);
+
+--echo Updating the table to fire trigger with event update
+
+UPDATE cap SET capital='Delhi' WHERE country='Ind';
+UPDATE cap SET capital='Washington DC' WHERE country='US';
+SELECT @country as country, @capital_old as 'old capital',
+@capital_new as 'new capital';
+
+--echo showing the tables to check the trigger is fired with event insert
+
+SELECT * FROM bup_ts.cap ORDER BY city;
+
+--echo selecting data from table city
+
+SELECT * FROM bup_ts.city ORDER BY name;
+
+--echo deleting few rows from table city to fire trigger with event delete.
+#*****Bug#35249******
+#Here memory storage engine crashes if trigger is created
+# with event delete and executed.
+
+DELETE FROM bup_ts.city WHERE name='dd';
+DELETE FROM bup_ts.city WHERE name='bb';
+
+--echo
+--echo Checking table contents of city and dropcity to verify if trigger is
+--echo fired for event delete.
+SELECT * FROM city ORDER BY name;
+SELECT * FROM dropcity ORDER BY id;
+
+--echo ******Firing Trigger after insert and after update *********
+
+INSERT INTO bup_ts.t2 values('a'),('k'),('*'),('i');
+SELECT * FROM bup_ts.t3;
+UPDATE t2 SET a='*' WHERE a='k';
+SELECT * FROM bup_ts.t4;
+
+--echo Show the data and Create statements
+
+--echo showing objects and create statements.
+--query_vertical SHOW TRIGGERS;
+--query_vertical SHOW CREATE TRIGGER cap_tri;
+--query_vertical SHOW CREATE TRIGGER capu_tri;
+--query_vertical SHOW CREATE TRIGGER cityin_d;
+--query_vertical SHOW CREATE TRIGGER trai;
+--query_vertical SHOW CREATE TRIGGER trau;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE city;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE t2;
+
+
+#Backup and restore data.
+--echo backup data
+--replace_column 1 #
+
+BACKUP DATABASE bup_ts TO 'bup_ts.bak';
+
+--echo dropping database.
+DROP DATABASE bup_ts;
+
+--echo perform restore
+--replace_column 1 #
+
+RESTORE FROM 'bup_ts.bak';
+
+--echo show data and create statements after Restore
+--echo showing objects and create statements
+--query_vertical SHOW CREATE DATABASE bup_ts;
+--query_vertical SHOW CREATE TRIGGER cap_tri;
+--query_vertical SHOW CREATE TRIGGER cityin_d;
+--query_vertical SHOW CREATE TRIGGER trai;
+--query_vertical SHOW CREATE TRIGGER trau;
+--query_vertical SHOW TRIGGERS;
+--echo Inserting some more values in the table to check the trigger with event insert
+
+INSERT INTO bup_ts.cap VALUES
+(9,'US','houston',-33399587677866656555655555655,'texas');
+
+SELECT * FROM bup_ts.cap ORDER BY city;
+
+--echo Deleting some rows from city to check the trigger with event delete
+
+DELETE FROM city WHERE name='aa';
+
+--echo Checking table city and drop city
+
+SELECT * FROM bup_ts.city ORDER BY name;
+SELECT * FROM bup_ts.dropcity ORDER BY id;
+INSERT INTO bup_ts.t2 VALUES('m'),('s1'),('s'),('q'),('l');
+SELECT * FROM bup_ts.t3;
+UPDATE t2 SET a='y' WHERE a='s1';
+SELECT * FROM bup_ts.t4;
+DROP DATABASE bup_ts;
+
+--echo Change sql mode and perform Restore and check the table contents.
+
+SET SQL_MODE=TRADITIONAL;
+--replace_column 1 #
+RESTORE FROM 'bup_ts.bak';
+
+--query_vertical SHOW TRIGGERS;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE t2;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE city;
+--replace_result $ENGINE ENGINE
+--query_vertical SHOW CREATE TABLE cap;
+--echo Check contents after Restore and change of SQL mode
+
+INSERT INTO bup_ts.cap VALUES(10,'India','Daund',36637782899,'maharashtra');
+SELECT * FROM bup_ts.cap ORDER BY city;
+INSERT INTO bup_ts.t2 VALUES('i'),('w');
+SELECT * FROM t3;
+UPDATE t2 SET a='^' WHERE a='w';
+SELECT * FROM t4;
+
+DROP DATABASE bup_ts;
+
+--echo change of SQL mode
+
+SET SQL_MODE=MAXDB;
+--replace_column 1 #
+RESTORE FROM 'bup_ts.bak';
+
+--echo Check contents after Restore and change of SQL mode
+
+INSERT INTO bup_ts.cap VALUES(11,'UK','Edenburg',-36637782899,'london');
+SELECT * FROM bup_ts.cap ORDER BY city;
+INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h');
+SELECT * FROM bup_ts.t3;
+UPDATE t2 SET a='p' WHERE a='h';
+SELECT * FROM bup_ts.t4;
+
+DROP DATABASE bup_ts;
+--echo Chnage SQL mode and then perform Restore.
+
+SET SQL_MODE=ANSI;
+--replace_column 1 #
+
+RESTORE FROM 'bup_ts.bak';
+
+--echo Check contents after Restore and change of SQL mode
+
+INSERT INTO bup_ts.cap VALUES(12,'India','vooty',789,'Tamilnadu');
+
+SELECT * FROM bup_ts.cap ORDER BY city;
+INSERT INTO bup_ts.t2 VALUES('i'),('w'),('h'),('b');
+
+SELECT * FROM bup_ts.t3;
+UPDATE bup_ts.t2 SET a='#' WHERE a='w';
+
+SELECT * FROM bup_ts.t4;
+
+# Test cleanup section
+--echo
+--echo *** DROP bup_ts DATABASE ****
+--echo
+
+DROP DATABASE bup_ts;
+
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_ts.bak
+
+
