Description:
CREATE PROCEDURE seems to fail when the value of sql_mode exceeds a certain length.
Here's the schema of some internal tables, in case they would be relevant. I recently updated from 5.1.22 and ran mysql_upgrade.exe
mysql> SHOW CREATE TABLE mysql.proc\G
*************************** 1. row ***************************
Table: proc
Create Table: CREATE TABLE "proc" (
"db" char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
"name" char(64) NOT NULL DEFAULT '',
"type" enum('FUNCTION','PROCEDURE') NOT NULL,
"specific_name" char(64) NOT NULL DEFAULT '',
"language" enum('SQL') NOT NULL DEFAULT 'SQL',
"sql_data_access" enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
"is_deterministic" enum('YES','NO') NOT NULL DEFAULT 'NO',
"security_type" enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
"param_list" blob NOT NULL,
"returns" longblob NOT NULL,
"body" longblob NOT NULL,
"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 '',
"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,
"db_collation" char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
"body_utf8" longblob,
PRIMARY KEY ("db","name","type")
)
mysql> SHOW CREATE TABLE information_schema.ROUTINES\G
*************************** 1. row ***************************
Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE "ROUTINES" (
"SPECIFIC_NAME" varchar(64) NOT NULL DEFAULT '',
"ROUTINE_CATALOG" varchar(512) DEFAULT NULL,
"ROUTINE_SCHEMA" varchar(64) NOT NULL DEFAULT '',
"ROUTINE_NAME" varchar(64) NOT NULL DEFAULT '',
"ROUTINE_TYPE" varchar(9) NOT NULL DEFAULT '',
"DTD_IDENTIFIER" varchar(64) DEFAULT NULL,
"ROUTINE_BODY" varchar(8) NOT NULL DEFAULT '',
"ROUTINE_DEFINITION" longtext,
"EXTERNAL_NAME" varchar(64) DEFAULT NULL,
"EXTERNAL_LANGUAGE" varchar(64) DEFAULT NULL,
"PARAMETER_STYLE" varchar(8) NOT NULL DEFAULT '',
"IS_DETERMINISTIC" varchar(3) NOT NULL DEFAULT '',
"SQL_DATA_ACCESS" varchar(64) NOT NULL DEFAULT '',
"SQL_PATH" varchar(64) DEFAULT NULL,
"SECURITY_TYPE" varchar(7) NOT NULL DEFAULT '',
"CREATED" datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
"LAST_ALTERED" datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
"SQL_MODE" longtext NOT NULL,
"ROUTINE_COMMENT" varchar(64) NOT NULL DEFAULT '',
"DEFINER" varchar(77) NOT NULL DEFAULT '',
"CHARACTER_SET_CLIENT" varchar(32) NOT NULL DEFAULT '',
"COLLATION_CONNECTION" varchar(32) NOT NULL DEFAULT '',
"DATABASE_COLLATION" varchar(32) NOT NULL DEFAULT ''
)
How to repeat:
mysql> SET sql_mode='ANSI,TRADITIONAL,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE PROCEDURE myproc() BEGIN END;
ERROR 1607 (HY000): Cannot create stored routine `myproc`. Check warnings
Warning (Code 1265): Data truncated for column 'sql_mode' at row 1
Error (Code 1607): Cannot create stored routine `myproc`. Check warnings
mysql> SET sql_mode='ANSI,TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE myproc() BEGIN END;
Query OK, 0 rows affected (0.00 sec)
Description: CREATE PROCEDURE seems to fail when the value of sql_mode exceeds a certain length. Here's the schema of some internal tables, in case they would be relevant. I recently updated from 5.1.22 and ran mysql_upgrade.exe mysql> SHOW CREATE TABLE mysql.proc\G *************************** 1. row *************************** Table: proc Create Table: CREATE TABLE "proc" ( "db" char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', "name" char(64) NOT NULL DEFAULT '', "type" enum('FUNCTION','PROCEDURE') NOT NULL, "specific_name" char(64) NOT NULL DEFAULT '', "language" enum('SQL') NOT NULL DEFAULT 'SQL', "sql_data_access" enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL', "is_deterministic" enum('YES','NO') NOT NULL DEFAULT 'NO', "security_type" enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER', "param_list" blob NOT NULL, "returns" longblob NOT NULL, "body" longblob NOT NULL, "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 '', "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, "db_collation" char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, "body_utf8" longblob, PRIMARY KEY ("db","name","type") ) mysql> SHOW CREATE TABLE information_schema.ROUTINES\G *************************** 1. row *************************** Table: ROUTINES Create Table: CREATE TEMPORARY TABLE "ROUTINES" ( "SPECIFIC_NAME" varchar(64) NOT NULL DEFAULT '', "ROUTINE_CATALOG" varchar(512) DEFAULT NULL, "ROUTINE_SCHEMA" varchar(64) NOT NULL DEFAULT '', "ROUTINE_NAME" varchar(64) NOT NULL DEFAULT '', "ROUTINE_TYPE" varchar(9) NOT NULL DEFAULT '', "DTD_IDENTIFIER" varchar(64) DEFAULT NULL, "ROUTINE_BODY" varchar(8) NOT NULL DEFAULT '', "ROUTINE_DEFINITION" longtext, "EXTERNAL_NAME" varchar(64) DEFAULT NULL, "EXTERNAL_LANGUAGE" varchar(64) DEFAULT NULL, "PARAMETER_STYLE" varchar(8) NOT NULL DEFAULT '', "IS_DETERMINISTIC" varchar(3) NOT NULL DEFAULT '', "SQL_DATA_ACCESS" varchar(64) NOT NULL DEFAULT '', "SQL_PATH" varchar(64) DEFAULT NULL, "SECURITY_TYPE" varchar(7) NOT NULL DEFAULT '', "CREATED" datetime NOT NULL DEFAULT '0000-00-00 00:00:00', "LAST_ALTERED" datetime NOT NULL DEFAULT '0000-00-00 00:00:00', "SQL_MODE" longtext NOT NULL, "ROUTINE_COMMENT" varchar(64) NOT NULL DEFAULT '', "DEFINER" varchar(77) NOT NULL DEFAULT '', "CHARACTER_SET_CLIENT" varchar(32) NOT NULL DEFAULT '', "COLLATION_CONNECTION" varchar(32) NOT NULL DEFAULT '', "DATABASE_COLLATION" varchar(32) NOT NULL DEFAULT '' ) How to repeat: mysql> SET sql_mode='ANSI,TRADITIONAL,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.01 sec) mysql> CREATE PROCEDURE myproc() BEGIN END; ERROR 1607 (HY000): Cannot create stored routine `myproc`. Check warnings Warning (Code 1265): Data truncated for column 'sql_mode' at row 1 Error (Code 1607): Cannot create stored routine `myproc`. Check warnings mysql> SET sql_mode='ANSI,TRADITIONAL'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE PROCEDURE myproc() BEGIN END; Query OK, 0 rows affected (0.00 sec)