Merge sanja.is.com.ua:/home/bell/mysql/bk/mysql-5.0

into  sanja.is.com.ua:/home/bell/mysql/bk/work-trigger-5.0

20 files changed, 427 insertions, 125 deletions

diff --git a/client/client_priv.h b/client/client_priv.h
index c8aa7385276..27e0f838995 100644
--- a/client/client_priv.h
+++ b/client/client_priv.h
@@ -49,5 +49,6 @@ enum options_client
 #ifdef HAVE_NDBCLUSTER_DB
   OPT_NDBCLUSTER, OPT_NDB_CONNECTSTRING,
 #endif
-  OPT_IGNORE_TABLE,OPT_INSERT_IGNORE,OPT_SHOW_WARNINGS,OPT_DROP_DATABASE
+  OPT_IGNORE_TABLE, OPT_INSERT_IGNORE, OPT_SHOW_WARNINGS, OPT_DROP_DATABASE,
+  OPT_TRIGGER
 };
diff --git a/client/mysqldump.c b/client/mysqldump.c
index be9eb5ef58b..48e5d8c52dd 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -372,7 +372,7 @@ static struct my_option my_long_options[] =
    (gptr*) &path, (gptr*) &path, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
   {"tables", OPT_TABLES, "Overrides option --databases (-B).",
    0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
-   {"triggers", '/0', "Dump triggers for each dumped table",
+   {"triggers", OPT_TRIGGER, "Dump triggers for each dumped table",
      (gptr*) &opt_dump_triggers, (gptr*) &opt_dump_triggers, 0, GET_BOOL,
      NO_ARG, 1, 0, 0, 0, 0, 0},
 #ifndef DONT_ALLOW_USER_CHANGE
@@ -1296,10 +1296,11 @@ static uint get_table_structure(char *table, char *db)
         row= mysql_fetch_row(tableRes);
 
         if (opt_drop)
-          fprintf(sql_file, "DROP VIEW IF EXISTS %s;\n",opt_quoted_table);
+          fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n",
+                  opt_quoted_table);
 
         /* Print CREATE statement but remove TEMPORARY */
-        fprintf(sql_file, "CREATE %s;\n", row[1]+17);
+        fprintf(sql_file, "/*!50001 CREATE %s*/;\n", row[1]+17);
         check_io(sql_file);
 
         mysql_free_result(tableRes);
@@ -1335,19 +1336,23 @@ static uint get_table_structure(char *table, char *db)
           DBUG_RETURN(0);
         }
         if (mysql_num_rows(tableRes))
-          fprintf(sql_file, "\nDELIMITER //;\n");
+          fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n\
+DELIMITER //;\n");
         while ((row=mysql_fetch_row(tableRes)))
         {
-          fprintf(sql_file, "CREATE TRIGGER %s %s %s ON %s\n"
-                  "FOR EACH ROW%s//\n\n",
-                  quote_name(row[0], name_buff, 0),
-                  row[4],
-                  row[1],
+          fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\"*/ //\n\
+/*!50003 CREATE TRIGGER %s %s %s ON %s FOR EACH ROW%s*/ //\n\n",
+                  row[6], /* sql_mode */
+                  quote_name(row[0], name_buff, 0), /* Trigger */
+                  row[4], /* Timing */
+                  row[1], /* Event */
                   result_table,
-                  row[3]);
+                  row[3] /* Statement */);
         }
         if (mysql_num_rows(tableRes))
-          fprintf(sql_file, "DELIMITER ;//");
+          fprintf(sql_file,
+                  "DELIMITER ;//\n\
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;");
         mysql_free_result(tableRes);
       }
     }
@@ -2957,13 +2962,15 @@ static my_bool get_view_structure(char *table, char* db)
   }
   if (opt_drop)
   {
-    fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n", opt_quoted_table);
-    fprintf(sql_file, "DROP VIEW IF EXISTS %s;\n", opt_quoted_table);
+    fprintf(sql_file, "/*!50001 DROP TABLE IF EXISTS %s*/;\n",
+            opt_quoted_table);
+    fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n",
+            opt_quoted_table);
     check_io(sql_file);
   }
 
   row= mysql_fetch_row(table_res);
-  fprintf(sql_file, "%s;\n", row[1]);
+  fprintf(sql_file, "/*!50001 %s*/;\n", row[1]);
   check_io(sql_file);
   mysql_free_result(table_res);
 
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 5c95d7b39d8..1781492af8b 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -713,6 +713,7 @@ information_schema	ROUTINES	SQL_MODE
 information_schema	VIEWS	VIEW_DEFINITION
 information_schema	TRIGGERS	ACTION_CONDITION
 information_schema	TRIGGERS	ACTION_STATEMENT
+information_schema	TRIGGERS	SQL_MODE
 select table_name, column_name, data_type from information_schema.columns
 where data_type = 'datetime';
 table_name	column_name	data_type
@@ -790,45 +791,45 @@ set @fired:= "Yes";
 end if;
 end|
 show triggers;
-Trigger	Event	Table	Statement	Timing	Created
+Trigger	Event	Table	Statement	Timing	Created	sql_mode
 trg1	INSERT	t1	
 begin
 if new.j > 10 then
 set new.j := 10;
 end if;
-end	BEFORE	NULL
+end	BEFORE	NULL	
 trg2	UPDATE	t1	
 begin
 if old.i % 2 = 0 then
 set new.j := -1;
 end if;
-end	BEFORE	NULL
+end	BEFORE	NULL	
 trg3	UPDATE	t1	
 begin
 if new.j = -1 then
 set @fired:= "Yes";
 end if;
-end	AFTER	NULL
+end	AFTER	NULL	
 select * from information_schema.triggers;
-TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED
+TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE
 NULL	test	trg1	INSERT	NULL	test	t1	0	NULL	
 begin
 if new.j > 10 then
 set new.j := 10;
 end if;
-end	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL
+end	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL	
 NULL	test	trg2	UPDATE	NULL	test	t1	0	NULL	
 begin
 if old.i % 2 = 0 then
 set new.j := -1;
 end if;
-end	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL
+end	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL	
 NULL	test	trg3	UPDATE	NULL	test	t1	0	NULL	
 begin
 if new.j = -1 then
 set @fired:= "Yes";
 end if;
-end	ROW	AFTER	NULL	NULL	OLD	NEW	NULL
+end	ROW	AFTER	NULL	NULL	OLD	NEW	NULL	
 drop trigger trg1;
 drop trigger trg2;
 drop trigger trg3;
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 77b67dc5ba7..917724580cf 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -1543,13 +1543,13 @@ LOCK TABLES `t1` WRITE;
 UNLOCK TABLES;
 /*!40000 ALTER TABLE `t1` ENABLE KEYS */;
 DROP TABLE IF EXISTS `v1`;
-DROP VIEW IF EXISTS `v1`;
-CREATE TABLE `v1` (
+/*!50001 DROP VIEW IF EXISTS `v1`*/;
+/*!50001 CREATE TABLE `v1` (
   `a` int(11) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-DROP TABLE IF EXISTS `v1`;
-DROP VIEW IF EXISTS `v1`;
-CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1`;
+) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+/*!50001 DROP TABLE IF EXISTS `v1`*/;
+/*!50001 DROP VIEW IF EXISTS `v1`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1`*/;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
@@ -1595,13 +1595,13 @@ INSERT INTO `t2` VALUES ('alfred'),('angie'),('bingo'),('waffle'),('lemon');
 UNLOCK TABLES;
 /*!40000 ALTER TABLE `t2` ENABLE KEYS */;
 DROP TABLE IF EXISTS `v2`;
-DROP VIEW IF EXISTS `v2`;
-CREATE TABLE `v2` (
+/*!50001 DROP VIEW IF EXISTS `v2`*/;
+/*!50001 CREATE TABLE `v2` (
   `a` varchar(30) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-DROP TABLE IF EXISTS `v2`;
-DROP VIEW IF EXISTS `v2`;
-CREATE ALGORITHM=UNDEFINED VIEW `mysqldump_test_db`.`v2` AS select `mysqldump_test_db`.`t2`.`a` AS `a` from `mysqldump_test_db`.`t2` where (`mysqldump_test_db`.`t2`.`a` like _latin1'a%') WITH CASCADED CHECK OPTION;
+) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+/*!50001 DROP TABLE IF EXISTS `v2`*/;
+/*!50001 DROP VIEW IF EXISTS `v2`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED VIEW `mysqldump_test_db`.`v2` AS select `mysqldump_test_db`.`t2`.`a` AS `a` from `mysqldump_test_db`.`t2` where (`mysqldump_test_db`.`t2`.`a` like _latin1'a%') WITH CASCADED CHECK OPTION*/;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
@@ -1685,6 +1685,7 @@ end|
 create trigger trg2 before update on t1 for each row begin
 if old.a % 2 = 0 then set new.b := 12; end if;
 end|
+set sql_mode="traditional"|
 create trigger trg3 after update on t1 for each row
 begin
 if new.a = -1 then
@@ -1697,24 +1698,25 @@ if new.a > 10 then
 set @fired:= "No";
 end if;
 end|
+set sql_mode=default|
 show triggers like "t1";
-Trigger	Event	Table	Statement	Timing	Created
+Trigger	Event	Table	Statement	Timing	Created	sql_mode
 trg1	INSERT	t1	
 begin
 if new.a > 10 then
 set new.a := 10;
 set new.a := 11;
 end if;
-end	BEFORE	0000-00-00 00:00:00
+end	BEFORE	0000-00-00 00:00:00	
 trg2	UPDATE	t1	 begin
 if old.a % 2 = 0 then set new.b := 12; end if;
-end	BEFORE	0000-00-00 00:00:00
+end	BEFORE	0000-00-00 00:00:00	
 trg3	UPDATE	t1	
 begin
 if new.a = -1 then
 set @fired:= "Yes";
 end if;
-end	AFTER	0000-00-00 00:00:00
+end	AFTER	0000-00-00 00:00:00	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
 INSERT INTO t1 (a) VALUES (1),(2),(3),(22);
 update t1 set a = 4 where a=3;
 
@@ -1736,30 +1738,32 @@ CREATE TABLE `t1` (
   `b` bigint(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
+/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
 DELIMITER //;
-CREATE TRIGGER `trg1` BEFORE INSERT ON `t1`
-FOR EACH ROW
+/*!50003 SET SESSION SQL_MODE=""*/ //
+/*!50003 CREATE TRIGGER `trg1` BEFORE INSERT ON `t1` FOR EACH ROW
 begin
 if new.a > 10 then
 set new.a := 10;
 set new.a := 11;
 end if;
-end//
+end*/ //
 
-CREATE TRIGGER `trg2` BEFORE UPDATE ON `t1`
-FOR EACH ROW begin
+/*!50003 SET SESSION SQL_MODE=""*/ //
+/*!50003 CREATE TRIGGER `trg2` BEFORE UPDATE ON `t1` FOR EACH ROW begin
 if old.a % 2 = 0 then set new.b := 12; end if;
-end//
+end*/ //
 
-CREATE TRIGGER `trg3` AFTER UPDATE ON `t1`
-FOR EACH ROW
+/*!50003 SET SESSION 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"*/ //
+/*!50003 CREATE TRIGGER `trg3` AFTER UPDATE ON `t1` FOR EACH ROW
 begin
 if new.a = -1 then
 set @fired:= "Yes";
 end if;
-end//
+end*/ //
 
 DELIMITER ;//
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;
 
 /*!40000 ALTER TABLE `t1` DISABLE KEYS */;
 LOCK TABLES `t1` WRITE;
@@ -1771,16 +1775,18 @@ CREATE TABLE `t2` (
   `a` int(11) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
+/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
 DELIMITER //;
-CREATE TRIGGER `trg4` BEFORE INSERT ON `t2`
-FOR EACH ROW
+/*!50003 SET SESSION 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"*/ //
+/*!50003 CREATE TRIGGER `trg4` BEFORE INSERT ON `t2` FOR EACH ROW
 begin
 if new.a > 10 then
 set @fired:= "No";
 end if;
-end//
+end*/ //
 
 DELIMITER ;//
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;
 
 /*!40000 ALTER TABLE `t2` DISABLE KEYS */;
 LOCK TABLES `t2` WRITE;
@@ -1844,4 +1850,28 @@ show tables;
 Tables_in_test
 t1
 t2
+show triggers;
+Trigger	Event	Table	Statement	Timing	Created	sql_mode
+trg1	INSERT	t1	
+begin
+if new.a > 10 then
+set new.a := 10;
+set new.a := 11;
+end if;
+end	BEFORE	#	
+trg2	UPDATE	t1	 begin
+if old.a % 2 = 0 then set new.b := 12; end if;
+end	BEFORE	#	
+trg3	UPDATE	t1	
+begin
+if new.a = -1 then
+set @fired:= "Yes";
+end if;
+end	AFTER	#	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
+trg4	INSERT	t2	
+begin
+if new.a > 10 then
+set @fired:= "No";
+end if;
+end	BEFORE	#	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
 DROP TABLE t1, t2;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 69900edf3d9..04d49bf4b3b 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -2658,20 +2658,20 @@ call avg ()|
 drop procedure avg|
 drop procedure if exists bug6129|
 set @old_mode= @@sql_mode;
-set @@sql_mode= "";
+set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO";
 create procedure bug6129()
 select @@sql_mode|
 call bug6129()|
 @@sql_mode
-
+ERROR_FOR_DIVISION_BY_ZERO
 set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"|
 call bug6129()|
 @@sql_mode
-NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
+ERROR_FOR_DIVISION_BY_ZERO
 set @@sql_mode= "NO_ZERO_IN_DATE"|
 call bug6129()|
 @@sql_mode
-NO_ZERO_IN_DATE
+ERROR_FOR_DIVISION_BY_ZERO
 set @@sql_mode=@old_mode;
 drop procedure bug6129|
 drop procedure if exists bug9856|
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 7e3a6fa65d4..52bf307a686 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -595,3 +595,43 @@ update t1 set col2 = 4;
 ERROR 42000: FUNCTION test.bug5893 does not exist
 drop trigger t1_bu;
 drop table t1;
+set sql_mode='ansi';
+create table t1 ("t1 column" int);
+create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5;
+set sql_mode="";
+insert into t1 values (0);
+create trigger t1_af after insert on t1 for each row set @a=10;
+insert into t1 values (0);
+select * from t1;
+t1 column
+5
+5
+select @a;
+@a
+10
+show triggers;
+Trigger	Event	Table	Statement	Timing	Created	sql_mode
+t1_bi	INSERT	t1	set new."t1 column" = 5	BEFORE	#	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+t1_af	INSERT	t1	 set @a=10	AFTER	#	
+drop table t1;
+set sql_mode="traditional";
+create table t1 (a date);
+insert into t1 values ('2004-01-00');
+ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1
+set sql_mode="";
+create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00';
+set sql_mode="traditional";
+insert into t1 values ('2004-01-01');
+select * from t1;
+a
+2004-01-00
+set sql_mode=default;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` date default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show triggers;
+Trigger	Event	Table	Statement	Timing	Created	sql_mode
+t1_bi	INSERT	t1	 set new.a = '2004-01-00'	BEFORE	#	
+drop table t1;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 5cb86788aa2..27bea937dcf 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -729,6 +729,7 @@ end|
 create trigger trg2 before update on t1 for each row begin
   if old.a % 2 = 0 then set new.b := 12; end if;
 end|
+set sql_mode="traditional"|
 create trigger trg3 after update on t1 for each row
 begin
   if new.a = -1 then
@@ -741,6 +742,7 @@ begin
     set @fired:= "No";
   end if;
 end|
+set sql_mode=default|
 delimiter ;|
 --replace_column 6 '0000-00-00 00:00:00'
 show triggers like "t1";
@@ -756,4 +758,6 @@ drop table t1;
 --exec $MYSQL test < var/tmp/mysqldump.sql
 # Check that tables have been reloaded
 show tables;
+--replace_column 6 #
+show triggers;
 DROP TABLE t1, t2;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 1a35df40a17..ecc9c3c12a2 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -3378,7 +3378,7 @@ drop procedure avg|
 drop procedure if exists bug6129|
 --enable_warnings
 set @old_mode= @@sql_mode;
-set @@sql_mode= "";
+set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO";
 create procedure bug6129()
   select @@sql_mode|
 call bug6129()|
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index f6b3c714d28..7bf8b1a4e2b 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -610,3 +610,35 @@ update t1 set col2 = 4;
 # This should not crash server too.
 drop trigger t1_bu;
 drop table t1;
+
+#
+# storing and restoring parsing modes for triggers (BUG#5891)
+#
+set sql_mode='ansi';
+create table t1 ("t1 column" int);
+create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5;
+set sql_mode="";
+insert into t1 values (0);
+# create trigger with different sql_mode
+create trigger t1_af after insert on t1 for each row set @a=10;
+insert into t1 values (0);
+select * from t1;
+select @a;
+--replace_column 6 #
+show triggers;
+drop table t1;
+# check that rigger preserve sql_mode during execution
+set sql_mode="traditional";
+create table t1 (a date);
+-- error 1292
+insert into t1 values ('2004-01-00');
+set sql_mode="";
+create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00';
+set sql_mode="traditional";
+insert into t1 values ('2004-01-01');
+select * from t1;
+set sql_mode=default;
+show create table t1;
+--replace_column 6 #
+show triggers;
+drop table t1;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 74b0e024905..93688689cd7 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -220,21 +220,58 @@ extern "C" int gethostname(char *name, int namelen);
 /* Constants */
 
 const char *show_comp_option_name[]= {"YES", "NO", "DISABLED"};
-static const char *sql_mode_names[] =
+static const char *sql_mode_names[]=
 {
   "REAL_AS_FLOAT", "PIPES_AS_CONCAT", "ANSI_QUOTES", "IGNORE_SPACE",
   "?", "ONLY_FULL_GROUP_BY", "NO_UNSIGNED_SUBTRACTION",
   "NO_DIR_IN_CREATE",
   "POSTGRESQL", "ORACLE", "MSSQL", "DB2", "MAXDB", "NO_KEY_OPTIONS",
   "NO_TABLE_OPTIONS", "NO_FIELD_OPTIONS", "MYSQL323", "MYSQL40", "ANSI",
-  "NO_AUTO_VALUE_ON_ZERO", "NO_BACKSLASH_ESCAPES", "STRICT_TRANS_TABLES", "STRICT_ALL_TABLES",
-  "NO_ZERO_IN_DATE", "NO_ZERO_DATE", "ALLOW_INVALID_DATES", "ERROR_FOR_DIVISION_BY_ZERO",
+  "NO_AUTO_VALUE_ON_ZERO", "NO_BACKSLASH_ESCAPES", "STRICT_TRANS_TABLES",
+  "STRICT_ALL_TABLES",
+  "NO_ZERO_IN_DATE", "NO_ZERO_DATE", "ALLOW_INVALID_DATES",
+  "ERROR_FOR_DIVISION_BY_ZERO",
   "TRADITIONAL", "NO_AUTO_CREATE_USER", "HIGH_NOT_PRECEDENCE",
   "NO_ENGINE_SUBSTITUTION",
   NullS
 };
+static const unsigned int sql_mode_names_len[]=
+{
+  /*REAL_AS_FLOAT*/               13,
+  /*PIPES_AS_CONCAT*/             15,
+  /*ANSI_QUOTES*/                 11,
+  /*IGNORE_SPACE*/                12,
+  /*?*/                           1,
+  /*ONLY_FULL_GROUP_BY*/          18,
+  /*NO_UNSIGNED_SUBTRACTION*/     23,
+  /*NO_DIR_IN_CREATE*/            16,
+  /*POSTGRESQL*/                  10,
+  /*ORACLE*/                      6,
+  /*MSSQL*/                       5,
+  /*DB2*/                         3,
+  /*MAXDB*/                       5,
+  /*NO_KEY_OPTIONS*/              14,
+  /*NO_TABLE_OPTIONS*/            16,
+  /*NO_FIELD_OPTIONS*/            16,
+  /*MYSQL323*/                    8,
+  /*MYSQL40*/                     7,
+  /*ANSI*/                        4,
+  /*NO_AUTO_VALUE_ON_ZERO*/       21,
+  /*NO_BACKSLASH_ESCAPES*/        20,
+  /*STRICT_TRANS_TABLES*/         19,
+  /*STRICT_ALL_TABLES*/           17,
+  /*NO_ZERO_IN_DATE*/             15,
+  /*NO_ZERO_DATE*/                12,
+  /*ALLOW_INVALID_DATES*/         19,
+  /*ERROR_FOR_DIVISION_BY_ZERO*/  26,
+  /*TRADITIONAL*/                 11,
+  /*NO_AUTO_CREATE_USER*/         19,
+  /*HIGH_NOT_PRECEDENCE*/         19,
+  /*NO_ENGINE_SUBSTITUTION*/      22
+};
 TYPELIB sql_mode_typelib= { array_elements(sql_mode_names)-1,"",
-			    sql_mode_names, NULL };
+			    sql_mode_names,
+                            (unsigned int *)sql_mode_names_len };
 static const char *tc_heuristic_recover_names[]=
 {
   "COMMIT", "ROLLBACK", NullS
diff --git a/sql/parse_file.cc b/sql/parse_file.cc
index abca8736916..82ce2f2d7b5 100644
--- a/sql/parse_file.cc
+++ b/sql/parse_file.cc
@@ -166,6 +166,25 @@ write_parameter(IO_CACHE *file, gptr base, File_option *parameter,
     }
     break;
   }
+  case FILE_OPTIONS_ULLLIST:
+  {
+    List_iterator_fast<ulonglong> it(*((List<ulonglong>*)
+                                       (base + parameter->offset)));
+    bool first= 1;
+    ulonglong *val;
+    while ((val= it++))
+    {
+      num.set(*val, &my_charset_bin);
+      // We need ' ' after string to detect list continuation
+      if ((!first && my_b_append(file, (const byte *)" ", 1)) ||
+          my_b_append(file, (const byte *)num.ptr(), num.length()))
+      {
+        DBUG_RETURN(TRUE);
+      }
+      first= 0;
+    }
+    break;
+  }
   default:
     DBUG_ASSERT(0); // never should happened
   }
@@ -615,6 +634,8 @@ File_parser::parse(gptr base, MEM_ROOT *mem_root,
   char *eol;
   LEX_STRING *str;
   List<LEX_STRING> *list;
+  ulonglong *num;
+  List<ulonglong> *nlist;
   DBUG_ENTER("File_parser::parse");
 
   while (ptr < end && found < required)
@@ -719,7 +740,7 @@ File_parser::parse(gptr base, MEM_ROOT *mem_root,
 	case FILE_OPTIONS_STRLIST:
 	{
           list= (List<LEX_STRING>*)(base + parameter->offset);
-	    
+
 	  list->empty();
 	  // list parsing
 	  while (ptr < end)
@@ -741,17 +762,56 @@ File_parser::parse(gptr base, MEM_ROOT *mem_root,
 	      goto list_err_w_message;
 	    }
 	  }
-      end_of_list:
+
+end_of_list:
 	  if (*(ptr++) != '\n')
 	    goto list_err;
 	  break;
 
-      list_err_w_message:
+list_err_w_message:
 	  my_error(ER_FPARSER_ERROR_IN_PARAMETER, MYF(0),
                    parameter->name.str, line);
-      list_err:
+list_err:
 	  DBUG_RETURN(TRUE);
 	}
+        case FILE_OPTIONS_ULLLIST:
+        {
+          nlist= (List<ulonglong>*)(base + parameter->offset);
+          nlist->empty();
+          // list parsing
+          while (ptr < end)
+          {
+            int not_used;
+            char *num_end= end;
+            if (!(num= (ulonglong*)alloc_root(mem_root, sizeof(ulonglong))) ||
+                nlist->push_back(num, mem_root))
+              goto nlist_err;
+            *num= my_strtoll10(ptr, &num_end, &not_used);
+            ptr= num_end;
+            switch (*ptr) {
+            case '\n':
+              goto end_of_nlist;
+            case ' ':
+              // we cant go over buffer bounds, because we have \0 at the end
+              ptr++;
+              break;
+            default:
+              goto nlist_err_w_message;
+            }
+          }
+
+end_of_nlist:
+          if (*(ptr++) != '\n')
+            goto nlist_err;
+          break;
+
+nlist_err_w_message:
+          my_error(ER_FPARSER_ERROR_IN_PARAMETER, MYF(0),
+                   parameter->name.str, line);
+nlist_err:
+          DBUG_RETURN(TRUE);
+
+        }
 	default:
 	  DBUG_ASSERT(0); // never should happened
 	}
diff --git a/sql/parse_file.h b/sql/parse_file.h
index 82a89dffd18..cc0aa6556f6 100644
--- a/sql/parse_file.h
+++ b/sql/parse_file.h
@@ -27,8 +27,10 @@ enum file_opt_type {
   FILE_OPTIONS_REV,		/* Revision version number (ulonglong) */
   FILE_OPTIONS_TIMESTAMP,	/* timestamp (LEX_STRING have to be
 				   allocated with length 20 (19+1) */
-  FILE_OPTIONS_STRLIST          /* list of escaped strings
+  FILE_OPTIONS_STRLIST,         /* list of escaped strings
                                    (List<LEX_STRING>) */
+  FILE_OPTIONS_ULLLIST          /* list of ulonglong values
+                                   (List<ulonglong>) */
 };
 
 struct File_option
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 9526cc79c56..35f1070c4eb 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -3202,29 +3202,49 @@ bool sys_var_thd_table_type::update(THD *thd, set_var *var)
  Functions to handle sql_mode
 ****************************************************************************/
 
-byte *sys_var_thd_sql_mode::value_ptr(THD *thd, enum_var_type type,
-				      LEX_STRING *base)
+/*
+  Make string representation of mode
+
+  SYNOPSIS
+    thd   in  thread handler
+    val   in  sql_mode value
+    len   out pointer on length of string
+
+  RETURN
+    pointer to string with sql_mode representation
+*/
+
+byte *sys_var_thd_sql_mode::symbolic_mode_representation(THD *thd, ulong val,
+                                                         ulong *len)
 {
-  ulong val;
   char buff[256];
   String tmp(buff, sizeof(buff), &my_charset_latin1);
 
   tmp.length(0);
-  val= ((type == OPT_GLOBAL) ? global_system_variables.*offset :
-        thd->variables.*offset);
   for (uint i= 0; val; val>>= 1, i++)
   {
     if (val & 1)
     {
-      tmp.append(enum_names->type_names[i]);
+      tmp.append(sql_mode_typelib.type_names[i],
+                 sql_mode_typelib.type_lengths[i]);
       tmp.append(',');
     }
   }
   if (tmp.length())
     tmp.length(tmp.length() - 1);
+  *len= tmp.length();
   return (byte*) thd->strmake(tmp.ptr(), tmp.length());
 }
 
+byte *sys_var_thd_sql_mode::value_ptr(THD *thd, enum_var_type type,
+				      LEX_STRING *base)
+{
+  ulong val= ((type == OPT_GLOBAL) ? global_system_variables.*offset :
+              thd->variables.*offset);
+  ulong length_unused;
+  return symbolic_mode_representation(thd, val, &length_unused);
+}
+
 
 void sys_var_thd_sql_mode::set_default(THD *thd, enum_var_type type)
 {
diff --git a/sql/set_var.h b/sql/set_var.h
index a7e680cc7fa..c8b075ddd35 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -361,6 +361,8 @@ public:
   }
   void set_default(THD *thd, enum_var_type type);
   byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+  static byte *symbolic_mode_representation(THD *thd, ulong sql_mode,
+                                            ulong *length);
 };
 
 
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 02c006d01ee..0c3a6d04598 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -574,6 +574,7 @@ sp_head::execute(THD *thd)
   sp_rcontext *ctx;
   int ret= 0;
   uint ip= 0;
+  ulong save_sql_mode;
   Query_arena *old_arena;
   query_id_t old_query_id;
   TABLE *old_derived_tables;
@@ -626,6 +627,8 @@ sp_head::execute(THD *thd)
   old_query_id= thd->query_id;
   old_derived_tables= thd->derived_tables;
   thd->derived_tables= 0;
+  save_sql_mode= thd->variables.sql_mode;
+  thd->variables.sql_mode= m_sql_mode;
   /*
     It is also more efficient to save/restore current thd->lex once when
     do it in each instruction
@@ -715,6 +718,7 @@ sp_head::execute(THD *thd)
   thd->query_id= old_query_id;
   DBUG_ASSERT(!thd->derived_tables);
   thd->derived_tables= old_derived_tables;
+  thd->variables.sql_mode= save_sql_mode;
 
   thd->current_arena= old_arena;
   state= EXECUTED;
@@ -1245,8 +1249,6 @@ sp_head::show_create_procedure(THD *thd)
   String buffer(buff, sizeof(buff), system_charset_info);
   int res;
   List<Item> field_list;
-  ulong old_sql_mode;
-  sys_var *sql_mode_var;
   byte *sql_mode_str;
   ulong sql_mode_len;
   bool full_access;
@@ -1258,19 +1260,13 @@ sp_head::show_create_procedure(THD *thd)
 
   if (check_show_routine_access(thd, this, &full_access))
     return 1;
-  
-  old_sql_mode= thd->variables.sql_mode;
-  thd->variables.sql_mode= m_sql_mode;
-  sql_mode_var= find_sys_var("SQL_MODE", 8);
-  if (sql_mode_var)
-  {
-    sql_mode_str= sql_mode_var->value_ptr(thd, OPT_SESSION, 0);
-    sql_mode_len= strlen((char*) sql_mode_str);
-  }
 
+  sql_mode_str=
+    sys_var_thd_sql_mode::symbolic_mode_representation(thd,
+                                                       m_sql_mode,
+                                                       &sql_mode_len);
   field_list.push_back(new Item_empty_string("Procedure", NAME_LEN));
-  if (sql_mode_var)
-    field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len));
+  field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len));
   // 1024 is for not to confuse old clients
   field_list.push_back(new Item_empty_string("Create Procedure",
 					     max(buffer.length(), 1024)));
@@ -1282,15 +1278,13 @@ sp_head::show_create_procedure(THD *thd)
   }
   protocol->prepare_for_resend();
   protocol->store(m_name.str, m_name.length, system_charset_info);
-  if (sql_mode_var)
-    protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info);
+  protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info);
   if (full_access)
     protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
   res= protocol->write();
   send_eof(thd);
 
  done:
-  thd->variables.sql_mode= old_sql_mode;
   DBUG_RETURN(res);
 }
 
@@ -1326,7 +1320,6 @@ sp_head::show_create_function(THD *thd)
   String buffer(buff, sizeof(buff), system_charset_info);
   int res;
   List<Item> field_list;
-  ulong old_sql_mode;
   sys_var *sql_mode_var;
   byte *sql_mode_str;
   ulong sql_mode_len;
@@ -1339,15 +1332,10 @@ sp_head::show_create_function(THD *thd)
   if (check_show_routine_access(thd, this, &full_access))
     return 1;
 
-  old_sql_mode= thd->variables.sql_mode;
-  thd->variables.sql_mode= m_sql_mode;
-  sql_mode_var= find_sys_var("SQL_MODE", 8);
-  if (sql_mode_var)
-  {
-    sql_mode_str= sql_mode_var->value_ptr(thd, OPT_SESSION, 0);
-    sql_mode_len= strlen((char*) sql_mode_str);
-  }
-
+  sql_mode_str=
+    sys_var_thd_sql_mode::symbolic_mode_representation(thd,
+                                                       m_sql_mode,
+                                                       &sql_mode_len);
   field_list.push_back(new Item_empty_string("Function",NAME_LEN));
   if (sql_mode_var)
     field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len));
@@ -1361,15 +1349,13 @@ sp_head::show_create_function(THD *thd)
   }
   protocol->prepare_for_resend();
   protocol->store(m_name.str, m_name.length, system_charset_info);
-  if (sql_mode_var)
-    protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info);
+  protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info);
   if (full_access)
     protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
   res= protocol->write();
   send_eof(thd);
 
  done:
-  thd->variables.sql_mode= old_sql_mode;
   DBUG_RETURN(res);
 }
 
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 32dc4449174..09b9c8f47a1 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -121,7 +121,7 @@ public:
   uchar *m_tmp_query;		// Temporary pointer to sub query string
   uint m_old_cmq;		// Old CLIENT_MULTI_QUERIES value
   st_sp_chistics *m_chistics;
-  ulong m_sql_mode;		// For SHOW CREATE
+  ulong m_sql_mode;		// For SHOW CREATE and execution
   LEX_STRING m_qname;		// db.name
   LEX_STRING m_db;
   LEX_STRING m_name;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index b9ab93cd66f..7ccf475b65e 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2983,9 +2983,13 @@ static bool store_trigger(THD *thd, TABLE *table, const char *db,
                           const char *tname, LEX_STRING *trigger_name,
                           enum trg_event_type event,
                           enum trg_action_time_type timing,
-                          LEX_STRING *trigger_stmt)
+                          LEX_STRING *trigger_stmt,
+                          ulong sql_mode)
 {
   CHARSET_INFO *cs= system_charset_info;
+  byte *sql_mode_str;
+  ulong sql_mode_len;
+
   restore_record(table, s->default_values);
   table->field[1]->store(db, strlen(db), cs);
   table->field[2]->store(trigger_name->str, trigger_name->length, cs);
@@ -2999,6 +3003,12 @@ static bool store_trigger(THD *thd, TABLE *table, const char *db,
                           trg_action_time_type_names[timing].length, cs);
   table->field[14]->store("OLD", 3, cs);
   table->field[15]->store("NEW", 3, cs);
+
+  sql_mode_str=
+    sys_var_thd_sql_mode::symbolic_mode_representation(thd,
+                                                       sql_mode,
+                                                       &sql_mode_len);
+  table->field[17]->store(sql_mode_str, sql_mode_len, cs);
   return schema_table_store_record(thd, table);
 }
 
@@ -3031,13 +3041,16 @@ static int get_schema_triggers_record(THD *thd, struct st_table_list *tables,
       {
         LEX_STRING trigger_name;
         LEX_STRING trigger_stmt;
+        ulong sql_mode;
         if (triggers->get_trigger_info(thd, (enum trg_event_type) event,
                                        (enum trg_action_time_type)timing,
-                                       &trigger_name, &trigger_stmt))
+                                       &trigger_name, &trigger_stmt,
+                                       &sql_mode))
           continue;
         if (store_trigger(thd, table, base_name, file_name, &trigger_name,
                          (enum trg_event_type) event,
-                         (enum trg_action_time_type) timing, &trigger_stmt))
+                         (enum trg_action_time_type) timing, &trigger_stmt,
+                         sql_mode))
           DBUG_RETURN(1);
       }
     }
@@ -3949,6 +3962,7 @@ ST_FIELD_INFO triggers_fields_info[]=
   {"ACTION_REFERENCE_OLD_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0},
   {"ACTION_REFERENCE_NEW_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0},
   {"CREATED", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Created"},
+  {"SQL_MODE", 65535, MYSQL_TYPE_STRING, 0, 0, "sql_mode"},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
 };
 
@@ -4003,7 +4017,7 @@ ST_SCHEMA_TABLE schema_tables[]=
    fill_open_tables, make_old_format, 0, -1, -1, 1},
   {"STATUS", variables_fields_info, create_schema_table, fill_status, 
    make_old_format, 0, -1, -1, 1},
-  {"TRIGGERS", triggers_fields_info, create_schema_table, 
+  {"TRIGGERS", triggers_fields_info, create_schema_table,
    get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0},
   {"VARIABLES", variables_fields_info, create_schema_table, fill_variables,
    make_old_format, 0, -1, -1, 1},
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 976fac3d9c9..87608eb4c83 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -32,8 +32,12 @@ const char * const triggers_file_ext= ".TRG";
 */
 static File_option triggers_file_parameters[]=
 {
-  {{(char*)"triggers", 8}, offsetof(class Table_triggers_list, definitions_list),
-   FILE_OPTIONS_STRLIST},
+  {{(char*)"triggers", 8},
+    offsetof(class Table_triggers_list, definitions_list),
+    FILE_OPTIONS_STRLIST},
+  {{(char*)"sql_modes", 13},
+    offsetof(class Table_triggers_list, definition_modes_list),
+    FILE_OPTIONS_ULLLIST},
   {{0, 0}, 0, FILE_OPTIONS_STRING}
 };
 
@@ -127,12 +131,13 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
     DBUG_RETURN(TRUE);
 
   /*
-    We do not allow creation of triggers on views or temporary tables.
-    We have to do this check here and not in
-    Table_triggers_list::create_trigger() because we want to avoid messing
-    with table cash for views and temporary tables.
+    We do not allow creation of triggers on temporary tables. We also don't
+    allow creation of triggers on views but fulfilment of this restriction
+    is guaranteed by open_ltable(). It is better to have this check here
+    than do it in Table_triggers_list::create_trigger() and mess with table
+    cache.
   */
-  if (tables->view || table->s->tmp_table != NO_TMP_TABLE)
+  if (table->s->tmp_table != NO_TMP_TABLE)
   {
     my_error(ER_TRG_ON_VIEW_OR_TEMP_TABLE, MYF(0), tables->alias);
     DBUG_RETURN(TRUE);
@@ -221,6 +226,7 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables)
        trigname_path[FN_REFLEN];
   LEX_STRING dir, file, trigname_file;
   LEX_STRING *trg_def, *name;
+  ulonglong *trg_sql_mode;
   Item_trigger_field *trg_field;
   struct st_trigname trigname;
 
@@ -307,11 +313,15 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables)
   */
   if (!(trg_def= (LEX_STRING *)alloc_root(&table->mem_root,
                                           sizeof(LEX_STRING))) ||
-      definitions_list.push_back(trg_def, &table->mem_root))
+      definitions_list.push_back(trg_def, &table->mem_root) ||
+      !(trg_sql_mode= (ulonglong*)alloc_root(&table->mem_root,
+                                             sizeof(ulonglong))) ||
+      definition_modes_list.push_back(trg_sql_mode, &table->mem_root))
     goto err_with_cleanup;
 
   trg_def->str= thd->query;
   trg_def->length= thd->query_length;
+  *trg_sql_mode= thd->variables.sql_mode;
 
   if (!sql_create_definition_file(&dir, &file, &triggers_file_type,
                                   (gptr)this, triggers_file_parameters, 3))
@@ -390,11 +400,13 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables)
   LEX_STRING *name;
   List_iterator_fast<LEX_STRING> it_name(names_list);
   List_iterator<LEX_STRING>      it_def(definitions_list);
+  List_iterator<ulonglong>       it_mod(definition_modes_list);
   char path[FN_REFLEN];
 
   while ((name= it_name++))
   {
     it_def++;
+    it_mod++;
 
     if (my_strcasecmp(system_charset_info, lex->spname->m_name.str,
                       name->str) == 0)
@@ -404,6 +416,7 @@ bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables)
         clean trigger removing since table will be reopened anyway.
       */
       it_def.remove();
+      it_mod.remove();
 
       if (definitions_list.is_empty())
       {
@@ -550,10 +563,48 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
       if (!triggers)
         DBUG_RETURN(1);
 
+      /*
+        We don't have sql_modes in old versions of .TRG file, so we should
+        initialize list for safety.
+      */
+      triggers->definition_modes_list.empty();
+
       if (parser->parse((gptr)triggers, &table->mem_root,
-                        triggers_file_parameters, 1))
+                        triggers_file_parameters, 2))
         DBUG_RETURN(1);
 
+      List_iterator_fast<LEX_STRING> it(triggers->definitions_list);
+      LEX_STRING *trg_create_str, *trg_name_str;
+      ulonglong *trg_sql_mode;
+
+      if (triggers->definition_modes_list.is_empty() &&
+          !triggers->definitions_list.is_empty())
+      {
+        /*
+          It is old file format => we should fill list of sql_modes.
+
+          We use one mode (current) for all triggers, because we have not
+          information about mode in old format.
+        */
+        if (!(trg_sql_mode= (ulonglong*)alloc_root(&table->mem_root,
+                                                   sizeof(ulonglong))))
+        {
+          DBUG_RETURN(1); // EOM
+        }
+        *trg_sql_mode= global_system_variables.sql_mode;
+        while ((trg_create_str= it++))
+        {
+          if (triggers->definition_modes_list.push_back(trg_sql_mode,
+                                                        &table->mem_root))
+          {
+            DBUG_RETURN(1); // EOM
+          }
+        }
+        it.rewind();
+      }
+
+      DBUG_ASSERT(triggers->definition_modes_list.elements ==
+                  triggers->definitions_list.elements);
       table->triggers= triggers;
 
       /*
@@ -574,10 +625,10 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
       if (!names_only && triggers->prepare_record1_accessors(table))
         DBUG_RETURN(1);
 
-      List_iterator_fast<LEX_STRING> it(triggers->definitions_list);
-      LEX_STRING *trg_create_str, *trg_name_str;
       char *trg_name_buff;
+      List_iterator_fast<ulonglong> itm(triggers->definition_modes_list);
       LEX *old_lex= thd->lex, lex;
+      ulong save_sql_mode= thd->variables.sql_mode;
 
       thd->lex= &lex;
 
@@ -587,6 +638,8 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
       thd->db= (char *) db;
       while ((trg_create_str= it++))
       {
+        trg_sql_mode= itm++;
+        thd->variables.sql_mode= (ulong)*trg_sql_mode;
         lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length);
 
         if (yyparse((void *)thd) || thd->is_fatal_error)
@@ -599,9 +652,11 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
           goto err_with_lex_cleanup;
         }
 
+        lex.sphead->m_sql_mode= *trg_sql_mode;
         triggers->bodies[lex.trg_chistics.event]
                              [lex.trg_chistics.action_time]= lex.sphead;
-        if (triggers->names_list.push_back(&lex.sphead->m_name, &table->mem_root))
+        if (triggers->names_list.push_back(&lex.sphead->m_name,
+                                           &table->mem_root))
             goto err_with_lex_cleanup;
 
         if (names_only)
@@ -615,8 +670,9 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
           in old/new versions of row in trigger to Field objects in table being
           opened.
 
-          We ignore errors here, because if even something is wrong we still will
-          be willing to open table to perform some operations (e.g. SELECT)...
+          We ignore errors here, because if even something is wrong we still
+          will be willing to open table to perform some operations (e.g.
+          SELECT)...
           Anyway some things can be checked only during trigger execution.
         */
         for (Item_trigger_field *trg_field=
@@ -630,6 +686,7 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
       thd->db= save_db.str;
       thd->db_length= save_db.length;
       thd->lex= old_lex;
+      thd->variables.sql_mode= save_sql_mode;
 
       DBUG_RETURN(0);
 
@@ -637,6 +694,7 @@ err_with_lex_cleanup:
       // QQ: anything else ?
       lex_end(&lex);
       thd->lex= old_lex;
+      thd->variables.sql_mode= save_sql_mode;
       thd->db= save_db.str;
       thd->db_length= save_db.length;
       DBUG_RETURN(1);
@@ -665,6 +723,7 @@ err_with_lex_cleanup:
       time_type - trigger action time
       name      - returns name of trigger
       stmt      - returns statement of trigger
+      sql_mode  - returns sql_mode of trigger
 
   RETURN VALUE
     False - success
@@ -674,7 +733,8 @@ err_with_lex_cleanup:
 bool Table_triggers_list::get_trigger_info(THD *thd, trg_event_type event,
                                            trg_action_time_type time_type,
                                            LEX_STRING *trigger_name,
-                                           LEX_STRING *trigger_stmt)
+                                           LEX_STRING *trigger_stmt,
+                                           ulong *sql_mode)
 {
   sp_head *body;
   DBUG_ENTER("get_trigger_info");
@@ -682,6 +742,7 @@ bool Table_triggers_list::get_trigger_info(THD *thd, trg_event_type event,
   {
     *trigger_name= body->m_name;
     *trigger_stmt= body->m_body;
+    *sql_mode= body->m_sql_mode;
     DBUG_RETURN(0);
   }
   DBUG_RETURN(1);
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index e751741fa34..ede2cb39f0e 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -60,6 +60,10 @@ public:
     It have to be public because we are using it directly from parser.
   */
   List<LEX_STRING>  definitions_list;
+  /*
+    List of sql modes for triggers
+  */
+  List<ulonglong> definition_modes_list;
 
   Table_triggers_list(TABLE *table_arg):
     record1_field(0), table(table_arg)
@@ -123,7 +127,8 @@ public:
   }
   bool get_trigger_info(THD *thd, trg_event_type event,
                         trg_action_time_type time_type,
-                        LEX_STRING *trigger_name, LEX_STRING *trigger_stmt);
+                        LEX_STRING *trigger_name, LEX_STRING *trigger_stmt,
+                        ulong *sql_mode);
 
   static bool check_n_load(THD *thd, const char *db, const char *table_name,
                            TABLE *table, bool names_only);
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index a60bf80a6d8..8b9c86a0f08 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -691,7 +691,7 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
   view_select= &lex->select_lex;
   view_select->select_number= ++thd->select_number;
   {
-    ulong options= thd->options;
+    ulong save_mode= thd->variables.sql_mode;
     /* switch off modes which can prevent normal parsing of VIEW
       - MODE_REAL_AS_FLOAT            affect only CREATE TABLE parsing
       + MODE_PIPES_AS_CONCAT          affect expression parsing
@@ -716,13 +716,13 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
       ? MODE_NO_AUTO_VALUE_ON_ZERO    affect UPDATEs
       + MODE_NO_BACKSLASH_ESCAPES     affect expression parsing
     */
-    thd->options&= ~(MODE_PIPES_AS_CONCAT | MODE_ANSI_QUOTES |
-                     MODE_IGNORE_SPACE | MODE_NO_BACKSLASH_ESCAPES);
+    thd->variables.sql_mode&= ~(MODE_PIPES_AS_CONCAT | MODE_ANSI_QUOTES |
+                                MODE_IGNORE_SPACE | MODE_NO_BACKSLASH_ESCAPES);
     CHARSET_INFO *save_cs= thd->variables.character_set_client;
     thd->variables.character_set_client= system_charset_info;
     res= yyparse((void *)thd);
     thd->variables.character_set_client= save_cs;
-    thd->options= options;
+    thd->variables.sql_mode= save_mode;
   }
   if (!res && !thd->is_fatal_error)
   {