List:Commits« Previous MessageNext Message »
From:Hema Sridharan Date:June 24 2008 12:09am
Subject:bzr commit into mysql-6.0 branch (hema:2635) WL#4222
View as plain text  
#At file:///data0/bzr6.0mysql/mysql-6.0-backup/

 2635 Hema Sridharan	2008-06-24
      Test of Metadata consistency(WL#4222). Test modified according to Chucks review comments and test passes in Windows also.
added:
  mysql-test/suite/backup/
  mysql-test/suite/backup/combinations
  mysql-test/suite/backup/include/
  mysql-test/suite/backup/include/backup_engine.inc
  mysql-test/suite/backup/r/
  mysql-test/suite/backup/r/backup_functions.result
  mysql-test/suite/backup/r/backup_procedures.result
  mysql-test/suite/backup/r/backup_triggers.result
  mysql-test/suite/backup/t/
  mysql-test/suite/backup/t/backup_functions.test
  mysql-test/suite/backup/t/backup_procedures.test
  mysql-test/suite/backup/t/backup_triggers.test

=== added directory 'mysql-test/suite/backup'
=== added file 'mysql-test/suite/backup/combinations'
--- a/mysql-test/suite/backup/combinations	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/combinations	2008-06-24 00:07:26 +0000
@@ -0,0 +1,12 @@
+[innodb]
+--default-storage-engine=innodb
+
+[falcon]
+--default-storage-engine=falcon
+
+[myisam]
+--default-storage-engine=myisam
+
+[memory]
+--default-storage-engine=memory
+

=== added directory 'mysql-test/suite/backup/include'
=== added file 'mysql-test/suite/backup/include/backup_engine.inc'
--- a/mysql-test/suite/backup/include/backup_engine.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/include/backup_engine.inc	2008-06-24 00:07:26 +0000
@@ -0,0 +1,17 @@
+###############################################################################
+# 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);
+#echo $ENGINE;
+
+#--replace_result $ENGINE ENGINE
+#eval SELECT '$ENGINE'='memory';
+#if(`SELECT '$ENGINE'='memory'`)
+#{
+# skip Does not support MEMORY engine;
+#}
+

=== added directory 'mysql-test/suite/backup/r'
=== added file 'mysql-test/suite/backup/r/backup_functions.result'
--- a/mysql-test/suite/backup/r/backup_functions.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_functions.result	2008-06-24 00:07:26 +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/r/backup_procedures.result'
--- a/mysql-test/suite/backup/r/backup_procedures.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_procedures.result	2008-06-24 00:07:26 +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/r/backup_triggers.result'
--- a/mysql-test/suite/backup/r/backup_triggers.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_triggers.result	2008-06-24 00:07:26 +0000
@@ -0,0 +1,778 @@
+SHOW VARIABLES LIKE 'storage_engine';
+Variable_name	Value
+storage_engine	#
+
+starting the test for backup
+
+SELECT 'ENGINE'='memory';
+'ENGINE'='memory'
+0
+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 population;
+id	country	city	population	capital
+2	US	Austin	0	Washington DC
+4	IND	bang	0	Delhi
+5	US	sacramento	0	Washington DC
+7	Russia	xx	0	moscow
+8	IND	hyd	647000	Delhi
+6	IND	jammu	3848000	Delhi
+1	IND	chn	87874646000	Delhi
+3	Russia	moscow	76487623235000	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 population;
+id	country	city	population	capital
+2	US	Austin	0	Washington DC
+4	IND	bang	0	Delhi
+5	US	sacramento	0	Washington DC
+7	Russia	xx	0	moscow
+9	US	houston	0	texas
+8	IND	hyd	647000	Delhi
+6	IND	jammu	3848000	Delhi
+1	IND	chn	87874646000	Delhi
+3	Russia	moscow	76487623235000	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 population;
+id	country	city	population	capital
+2	US	Austin	0	Washington DC
+4	IND	bang	0	Delhi
+5	US	sacramento	0	Washington DC
+7	Russia	xx	0	moscow
+8	IND	hyd	647000	Delhi
+6	IND	jammu	3848000	Delhi
+10	India	Daund	36637782000	maharashtra
+1	IND	chn	87874646000	Delhi
+3	Russia	moscow	76487623235000	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 population;
+id	country	city	population	capital
+2	US	Austin	0	Washington DC
+4	IND	bang	0	Delhi
+5	US	sacramento	0	Washington DC
+7	Russia	xx	0	moscow
+11	UK	Edenburg	0	london
+8	IND	hyd	647000	Delhi
+6	IND	jammu	3848000	Delhi
+1	IND	chn	87874646000	Delhi
+3	Russia	moscow	76487623235000	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 population;
+id	country	city	population	capital
+2	US	Austin	0	Washington DC
+4	IND	bang	0	Delhi
+5	US	sacramento	0	Washington DC
+7	Russia	xx	0	moscow
+12	India	vooty	0	Tamilnadu
+8	IND	hyd	647000	Delhi
+6	IND	jammu	3848000	Delhi
+1	IND	chn	87874646000	Delhi
+3	Russia	moscow	76487623235000	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/t'
=== added file 'mysql-test/suite/backup/t/backup_functions.test'
--- a/mysql-test/suite/backup/t/backup_functions.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_functions.test	2008-06-24 00:07:26 +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/have_innodb.inc
+--source include/not_embedded.inc
+--source include/have_falcon.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
+

=== added file 'mysql-test/suite/backup/t/backup_procedures.test'
--- a/mysql-test/suite/backup/t/backup_procedures.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_procedures.test	2008-06-24 00:07:26 +0000
@@ -0,0 +1,125 @@
+###########################################################################
+# Author: Hema
+# Date: 2008-03-11
+# Purpose: To test the metadata consistency object stored procedures  for all   #  storage engines.
+###############################################################################
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+--source include/have_falcon.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
+

=== added file 'mysql-test/suite/backup/t/backup_triggers.test'
--- a/mysql-test/suite/backup/t/backup_triggers.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_triggers.test	2008-06-24 00:07:26 +0000
@@ -0,0 +1,309 @@
+##################################################################
+# Author: Hema
+# Date: 2008-05-28
+# Purpose: To test the metadata consistency of object trigger for all     
+#  storage engines
+###############################################################################
+
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+--source include/have_falcon.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
+
+--replace_result $ENGINE ENGINE
+eval SELECT '$ENGINE'='memory';
+if(`SELECT '$ENGINE'='memory'`)
+{
+ skip Temporarily Disabled Bug#35249 ;
+}
+--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 population;
+
+--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 population;
+
+--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 population;
+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 population;
+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 population;
+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
+                                            
+

Thread
bzr commit into mysql-6.0 branch (hema:2635) WL#4222Hema Sridharan24 Jun