bk commit into 5.1 tree (davi:1.2516) BUG#32633

[Davi Arnaut]

Below is the list of changes that have just been committed into a local
5.1 repository of davi.  When davi does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-02-04 12:55:34-02:00, davi@stripped +12 -0
  Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution
  
  The problem is that one can not create a stored routine if sql_mode
  contains NO_ENGINE_SUBSTITUTION or PAD_CHAR_TO_FULL_LENGTH. Also when
  a event is created, the mode is silently lost if sql_mode contains one
  of the aforementioned.  This was happening because the table definitions
  which stored sql_mode values weren't being updated to accept new values
  of sql_mode.
  
  The solution is to update, in a backwards compatible manner, the various
  table definitions (columns) that store the sql_mode value to take into
  account the new possible values.

  mysql-test/r/events_bugs.result@stripped, 2008-02-04 12:55:30-02:00, davi@stripped +9 -0
    Add test case result for Bug#32633

  mysql-test/r/information_schema.result@stripped, 2008-02-04 12:55:30-02:00, davi@stripped
+1 -1
    Update the sql_mode column definition.

  mysql-test/r/sp.result@stripped, 2008-02-04 12:55:30-02:00, davi@stripped +13 -0
    Add test case result for Bug#32633

  mysql-test/r/system_mysql_db.result@stripped, 2008-02-04 12:55:30-02:00, davi@stripped +2
-2
    Update the sql_mode column definition.

  mysql-test/t/events_bugs.test@stripped, 2008-02-04 12:55:30-02:00, davi@stripped +13 -0
    Add test case for Bug#32633

  mysql-test/t/sp.test@stripped, 2008-02-04 12:55:31-02:00, davi@stripped +18 -0
    Add test case for Bug#32633

  mysql-test/t/system_mysql_db_fix50117.test@stripped, 2008-02-04 12:55:31-02:00,
davi@stripped +1 -1
    Update the sql_mode column definition.

  scripts/mysql_system_tables.sql@stripped, 2008-02-04 12:55:31-02:00, davi@stripped +2 -2
    Update the sql_mode column definition.

  scripts/mysql_system_tables_fix.sql@stripped, 2008-02-04 12:55:31-02:00, davi@stripped +6
-2
    Update the sql_mode column definition.

  sql/event_db_repository.cc@stripped, 2008-02-04 12:55:31-02:00, davi@stripped +26 -20
    Add new sql_mode values and store the sql_mode field using
    the string representation. Throw out a error if store fails.

  sql/mysqld.cc@stripped, 2008-02-04 12:55:31-02:00, davi@stripped +5 -0
    Add warning to avoid this problem in the future.

  sql/sp.cc@stripped, 2008-02-04 12:55:31-02:00, davi@stripped +5 -1
    SQL mode of the thread must not effect data dictionary operations.

diff -Nrup a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result
--- a/mysql-test/r/events_bugs.result	2007-11-21 23:41:52 -02:00
+++ b/mysql-test/r/events_bugs.result	2008-02-04 12:55:30 -02:00
@@ -722,4 +722,13 @@ DROP USER mysqltest_u1@localhost;
 #
 #####################################################################
 
+drop event if exists e1;
+set @old_mode= @@sql_mode;
+set @@sql_mode= 'no_engine_substitution';
+create event e1 on schedule every 15 minute starts '2006-01-01 00:00:00' on completion
not preserve do begin end;
+set @@sql_mode= @old_mode;
+show create event e1;
+Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database
Collation
+e1	NO_ENGINE_SUBSTITUTION	SYSTEM	CREATE EVENT `e1` ON SCHEDULE EVERY 15 MINUTE STARTS
'2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO begin
end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop event e1;
 DROP DATABASE events_test;
diff -Nrup a/mysql-test/r/information_schema.result
b/mysql-test/r/information_schema.result
--- a/mysql-test/r/information_schema.result	2008-02-01 08:50:16 -02:00
+++ b/mysql-test/r/information_schema.result	2008-02-04 12:55:30 -02:00
@@ -588,7 +588,7 @@ proc	body	longblob
 proc	definer	char(77)
 proc	created	timestamp
 proc	modified	timestamp
-proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')
+proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
 proc	comment	char(64)
 proc	character_set_client	char(32)
 proc	collation_connection	char(32)
diff -Nrup a/mysql-test/r/sp.result b/mysql-test/r/sp.result
--- a/mysql-test/r/sp.result	2008-01-23 21:21:04 -02:00
+++ b/mysql-test/r/sp.result	2008-02-04 12:55:30 -02:00
@@ -6963,6 +6963,19 @@ END	latin1	latin1_swedish_ci	latin1_swed
 
 DROP FUNCTION f1;
 
+drop procedure if exists p;
+set @old_mode= @@sql_mode;
+set @@sql_mode= pow(2,32)-1;
+select @@sql_mode into @full_mode;
+create procedure p() begin end;
+call p();
+set @@sql_mode= @old_mode;
+select replace(@full_mode, '?', 'NOT_USED') into @full_mode;
+select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode;
+select name from mysql.proc where name = 'p' and sql_mode = @full_mode;
+name
+p
+drop procedure p;
 # ------------------------------------------------------------------
 # -- End of 5.1 tests
 # ------------------------------------------------------------------
diff -Nrup a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result
--- a/mysql-test/r/system_mysql_db.result	2007-10-19 18:03:16 -02:00
+++ b/mysql-test/r/system_mysql_db.result	2008-02-04 12:55:30 -02:00
@@ -201,7 +201,7 @@ proc	CREATE TABLE `proc` (
   `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `sql_mode`
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')
NOT NULL DEFAULT '',
+  `sql_mode`
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
NOT NULL DEFAULT '',
   `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
   `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -226,7 +226,7 @@ event	CREATE TABLE `event` (
   `ends` datetime DEFAULT NULL,
   `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
   `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
-  `sql_mode`
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')
NOT NULL DEFAULT '',
+  `sql_mode`
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
NOT NULL DEFAULT '',
   `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `originator` int(10) NOT NULL,
   `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff -Nrup a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test
--- a/mysql-test/t/events_bugs.test	2007-10-22 17:16:19 -02:00
+++ b/mysql-test/t/events_bugs.test	2008-02-04 12:55:30 -02:00
@@ -935,6 +935,19 @@ DROP USER mysqltest_u1@localhost;
 --echo #####################################################################
 --echo
 
+#
+# Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution
+#
+
+--disable_warnings
+drop event if exists e1;
+--enable_warnings
+set @old_mode= @@sql_mode;
+set @@sql_mode= 'no_engine_substitution';
+create event e1 on schedule every 15 minute starts '2006-01-01 00:00:00' on completion
not preserve do begin end;
+set @@sql_mode= @old_mode;
+show create event e1;
+drop event e1;
 
 ###########################################################################
 # 
diff -Nrup a/mysql-test/t/sp.test b/mysql-test/t/sp.test
--- a/mysql-test/t/sp.test	2008-01-23 21:21:04 -02:00
+++ b/mysql-test/t/sp.test	2008-02-04 12:55:31 -02:00
@@ -8135,6 +8135,24 @@ DROP FUNCTION f1;
 
 ###########################################################################
 
+#
+# Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution
+#
+
+--disable_warnings
+drop procedure if exists p;
+--enable_warnings
+set @old_mode= @@sql_mode;
+set @@sql_mode= pow(2,32)-1;
+select @@sql_mode into @full_mode;
+create procedure p() begin end;
+call p();
+set @@sql_mode= @old_mode;
+select replace(@full_mode, '?', 'NOT_USED') into @full_mode;
+select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode;
+select name from mysql.proc where name = 'p' and sql_mode = @full_mode;
+drop procedure p;
+
 --echo # ------------------------------------------------------------------
 --echo # -- End of 5.1 tests
 --echo # ------------------------------------------------------------------
diff -Nrup a/mysql-test/t/system_mysql_db_fix50117.test
b/mysql-test/t/system_mysql_db_fix50117.test
--- a/mysql-test/t/system_mysql_db_fix50117.test	2007-05-19 13:55:36 -03:00
+++ b/mysql-test/t/system_mysql_db_fix50117.test	2008-02-04 12:55:31 -02:00
@@ -78,7 +78,7 @@ CREATE TABLE IF NOT EXISTS proc ( db cha
 
 CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db
char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL,
Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type
enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv
set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL,
Timestamp timestamp(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY
Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin  
comment='Procedure privileges';
 
-CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT
NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT
NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field
ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND')
default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed
DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status
ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion
ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode 
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_G
 ROUP_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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')
DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default
'', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db,
name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
+CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT
NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT
NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field
ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND')
default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed
DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status
ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion
ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode 
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_G
 ROUP_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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default
'', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db,
name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
 
 CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File
VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL,
updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT
UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM;
 
diff -Nrup a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
--- a/scripts/mysql_system_tables.sql	2007-10-19 18:03:16 -02:00
+++ b/scripts/mysql_system_tables.sql	2008-02-04 12:55:31 -02:00
@@ -60,7 +60,7 @@ CREATE TABLE IF NOT EXISTS time_zone_tra
 CREATE TABLE IF NOT EXISTS time_zone_leap_second (   Transition_time bigint signed NOT
NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) )
engine=MyISAM CHARACTER SET utf8   comment='Leap seconds information for time zones';
 
 
-CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name
char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name
char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL,
sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA')
DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL,
security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT
NULL, returns longblob DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77)
collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode
set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED',
'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_VA
 LUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES',
'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO',
'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE') DEFAULT '' NOT NULL, comment
char(64) collate utf8_bin DEFAULT '' NOT NULL, character_set_client char(32) collate
utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate
utf8_bin, body_utf8 longblob, PRIMARY KEY (db,name,type)) engine=MyISAM character set
utf8 comment='Stored Procedures';
+CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name
char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name
char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL,
sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA')
DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL,
security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT
NULL, returns longblob DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77)
collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode
set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED',
'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_VA
 LUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES',
'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO',
'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION',
'PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT
'' NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32)
collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY
(db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures';
 
 CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db
char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL,
Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type
enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv
set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL,
Timestamp timestamp(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY
Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin  
comment='Procedure privileges';
 
@@ -80,7 +80,7 @@ PREPARE stmt FROM @str;
 EXECUTE stmt;
 DROP PREPARE stmt;
 
-CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT
NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT
NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field
ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND')
default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed
DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status
ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion
ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode 
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','N
 OT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE')
DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default
'', originator int(10) NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT
'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32)
collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY
(db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
+CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT
NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT
NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field
ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND')
default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed
DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status
ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion
ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode 
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','N
 OT_USED','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','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default
'', originator int(10) NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT
'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32)
collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY
(db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
 
 
 CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File
VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL,
updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT
UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM;
diff -Nrup a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
--- a/scripts/mysql_system_tables_fix.sql	2007-11-30 09:00:10 -02:00
+++ b/scripts/mysql_system_tables_fix.sql	2008-02-04 12:55:31 -02:00
@@ -375,7 +375,9 @@ ALTER TABLE proc MODIFY name char(64) DE
                             'ERROR_FOR_DIVISION_BY_ZERO',
                             'TRADITIONAL',
                             'NO_AUTO_CREATE_USER',
-                            'HIGH_NOT_PRECEDENCE'
+                            'HIGH_NOT_PRECEDENCE',
+                            'NO_ENGINE_SUBSTITUTION',
+                            'PAD_CHAR_TO_FULL_LENGTH'
                             ) DEFAULT '' NOT NULL,
                  DEFAULT CHARACTER SET utf8;
 
@@ -461,7 +463,9 @@ ALTER TABLE event ADD sql_mode
                             'ERROR_FOR_DIVISION_BY_ZERO',
                             'TRADITIONAL',
                             'NO_AUTO_CREATE_USER',
-                            'HIGH_NOT_PRECEDENCE'
+                            'HIGH_NOT_PRECEDENCE',
+                            'NO_ENGINE_SUBSTITUTION',
+                            'PAD_CHAR_TO_FULL_LENGTH'
                             ) DEFAULT '' NOT NULL AFTER on_completion;
 ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default '';
 ALTER TABLE event ADD COLUMN originator INT(10) NOT NULL AFTER comment;
diff -Nrup a/sql/event_db_repository.cc b/sql/event_db_repository.cc
--- a/sql/event_db_repository.cc	2007-11-01 18:52:50 -02:00
+++ b/sql/event_db_repository.cc	2008-02-04 12:55:31 -02:00
@@ -111,7 +111,7 @@ const TABLE_FIELD_W_TYPE event_table_fie
     "'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES',"
     "'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES',"
     "'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',"
-    "'HIGH_NOT_PRECEDENCE')") },
+    "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')") },
     {NULL, 0}
   },
   {
@@ -177,6 +177,7 @@ mysql_event_fill_row(THD *thd,
   CHARSET_INFO *scs= system_charset_info;
   enum enum_events_table_field f_num;
   Field **fields= table->field;
+  int rs= FALSE;
 
   DBUG_ENTER("mysql_event_fill_row");
 
@@ -205,12 +206,9 @@ mysql_event_fill_row(THD *thd,
     goto err_truncate;
 
   /* both ON_COMPLETION and STATUS are NOT NULL thus not calling set_notnull()*/
-  fields[ET_FIELD_ON_COMPLETION]->store((longlong)et->on_completion, TRUE);
-
-  fields[ET_FIELD_STATUS]->store((longlong)et->status, TRUE);
-
-  fields[ET_FIELD_ORIGINATOR]->store((longlong)et->originator, TRUE);
-
+  rs|= fields[ET_FIELD_ON_COMPLETION]->store((longlong)et->on_completion, TRUE);
+  rs|= fields[ET_FIELD_STATUS]->store((longlong)et->status, TRUE);
+  rs|= fields[ET_FIELD_ORIGINATOR]->store((longlong)et->originator, TRUE);
 
   /*
     Change the SQL_MODE only if body was present in an ALTER EVENT and of course
@@ -220,7 +218,7 @@ mysql_event_fill_row(THD *thd,
   {
     DBUG_ASSERT(sp->m_body.str);
 
-    fields[ET_FIELD_SQL_MODE]->store((longlong)thd->variables.sql_mode, TRUE);
+    rs|= fields[ET_FIELD_SQL_MODE]->store((longlong)thd->variables.sql_mode, TRUE);
 
     if (fields[f_num= ET_FIELD_BODY]->store(sp->m_body.str,
                                             sp->m_body.length,
@@ -236,16 +234,16 @@ mysql_event_fill_row(THD *thd,
     if (!is_update || !et->starts_null)
     {
       fields[ET_FIELD_TIME_ZONE]->set_notnull();
-      fields[ET_FIELD_TIME_ZONE]->store(tz_name->ptr(), tz_name->length(),
-                                        tz_name->charset());
+      rs|= fields[ET_FIELD_TIME_ZONE]->store(tz_name->ptr(), tz_name->length(),
+                                             tz_name->charset());
     }
 
     fields[ET_FIELD_INTERVAL_EXPR]->set_notnull();
-    fields[ET_FIELD_INTERVAL_EXPR]->store((longlong)et->expression, TRUE);
+    rs|= fields[ET_FIELD_INTERVAL_EXPR]->store((longlong)et->expression, TRUE);
 
     fields[ET_FIELD_TRANSIENT_INTERVAL]->set_notnull();
 
-    fields[ET_FIELD_TRANSIENT_INTERVAL]->
+    rs|= fields[ET_FIELD_TRANSIENT_INTERVAL]->
                             store(interval_type_to_name[et->interval].str,
                                   interval_type_to_name[et->interval].length,
                                   scs);
@@ -274,8 +272,8 @@ mysql_event_fill_row(THD *thd,
   {
     const String *tz_name= thd->variables.time_zone->get_name();
     fields[ET_FIELD_TIME_ZONE]->set_notnull();
-    fields[ET_FIELD_TIME_ZONE]->store(tz_name->ptr(), tz_name->length(),
-                                      tz_name->charset());
+    rs|= fields[ET_FIELD_TIME_ZONE]->store(tz_name->ptr(), tz_name->length(),
+                                           tz_name->charset());
 
     fields[ET_FIELD_INTERVAL_EXPR]->set_null();
     fields[ET_FIELD_TRANSIENT_INTERVAL]->set_null();
@@ -308,13 +306,13 @@ mysql_event_fill_row(THD *thd,
   }
 
   fields[ET_FIELD_CHARACTER_SET_CLIENT]->set_notnull();
-  fields[ET_FIELD_CHARACTER_SET_CLIENT]->store(
+  rs|= fields[ET_FIELD_CHARACTER_SET_CLIENT]->store(
     thd->variables.character_set_client->csname,
     strlen(thd->variables.character_set_client->csname),
     system_charset_info);
 
   fields[ET_FIELD_COLLATION_CONNECTION]->set_notnull();
-  fields[ET_FIELD_COLLATION_CONNECTION]->store(
+  rs|= fields[ET_FIELD_COLLATION_CONNECTION]->store(
     thd->variables.collation_connection->name,
     strlen(thd->variables.collation_connection->name),
     system_charset_info);
@@ -323,15 +321,23 @@ mysql_event_fill_row(THD *thd,
     CHARSET_INFO *db_cl= get_default_db_collation(thd, et->dbname.str);
 
     fields[ET_FIELD_DB_COLLATION]->set_notnull();
-    fields[ET_FIELD_DB_COLLATION]->store(
-      db_cl->name, strlen(db_cl->name), system_charset_info);
+    rs|= fields[ET_FIELD_DB_COLLATION]->store(db_cl->name,
+                                              strlen(db_cl->name),
+                                              system_charset_info);
   }
 
   if (et->body_changed)
   {
     fields[ET_FIELD_BODY_UTF8]->set_notnull();
-    fields[ET_FIELD_BODY_UTF8]->store(
-      sp->m_body_utf8.str, sp->m_body_utf8.length, system_charset_info);
+    rs|= fields[ET_FIELD_BODY_UTF8]->store(sp->m_body_utf8.str,
+                                           sp->m_body_utf8.length,
+                                           system_charset_info);
+  }
+
+  if (rs)
+  {
+    my_error(ER_EVENT_STORE_FAILED, MYF(0), fields[f_num]->field_name, rs);
+    DBUG_RETURN(TRUE);
   }
 
   DBUG_RETURN(FALSE);
diff -Nrup a/sql/mysqld.cc b/sql/mysqld.cc
--- a/sql/mysqld.cc	2008-01-25 15:14:58 -02:00
+++ b/sql/mysqld.cc	2008-02-04 12:55:31 -02:00
@@ -221,6 +221,11 @@ extern "C" int gethostname(char *name, i
 /* Constants */
 
 const char *show_comp_option_name[]= {"YES", "NO", "DISABLED"};
+/*
+  WARNING: When adding new SQL modes don't forget to update the
+           tables definitions that stores it's value.
+           (ie: mysql.event, mysql.proc)
+*/
 static const char *sql_mode_names[]=
 {
   "REAL_AS_FLOAT", "PIPES_AS_CONCAT", "ANSI_QUOTES", "IGNORE_SPACE",
diff -Nrup a/sql/sp.cc b/sql/sp.cc
--- a/sql/sp.cc	2008-01-22 20:45:42 -02:00
+++ b/sql/sp.cc	2008-02-04 12:55:31 -02:00
@@ -388,7 +388,7 @@ db_find_routine(THD *thd, int type, sp_n
   uint length;
   char buff[65];
   String str(buff, sizeof(buff), &my_charset_bin);
-  ulong sql_mode;
+  ulong sql_mode, saved_mode= thd->variables.sql_mode;
   Open_tables_state open_tables_state_backup;
   Stored_program_creation_ctx *creation_ctx;
 
@@ -400,6 +400,9 @@ db_find_routine(THD *thd, int type, sp_n
   if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
     DBUG_RETURN(SP_OPEN_TABLE_FAILED);
 
+  /* Reset sql_mode during data dictionary operations. */
+  thd->variables.sql_mode= 0;
+
   if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
     goto done;
 
@@ -503,6 +506,7 @@ db_find_routine(THD *thd, int type, sp_n
  done:
   if (table)
     close_system_tables(thd, &open_tables_state_backup);
+  thd->variables.sql_mode= saved_mode;
   DBUG_RETURN(ret);
 }