Description:
when running mysql-test-run.pl the mysql database is created without the 'proc' table. as a result any attempt to create a stored procedure fails as shown below:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.10-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> create procedure sp1() set@x=1;
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
16 rows in set (0.00 sec)
The problem is a result of the fact that the init_db.sql is failing to create the proc table.
When running the init_db.sql (modified to run against a different databes) from the mysql client the following error
ERROR 1067 (42000): Invalid default value for 'sql_mode'
is returned for creating the proc table:
CREATE TABLE proc (
db char(64) binary 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 DEFAULT '' NOT NULL,
returns char(64) DEFAULT '' NOT NULL,
body blob DEFAULT '' NOT NULL,
definer char(77) binary 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_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 0 NOT NULL,
comment char(64) binary DEFAULT '' NOT NULL,
PRIMARY KEY (db,name,type)
) comment='Stored Procedures';
How to repeat:
To see the problem:
1) run mysql-test-run.pl --start-and-exit
2) log in using the mysql client
3) execute the following:
use test;
create procedure sp1() set@x=1;
To see the error in init_db.sql
1) run mysql-test-run.pl --start-and-exit
2) copy mysql-test/lib/init_db.sql to init_bug.sql
3) modify the init_buf.sql to use database 'foo' (instead of 'mysql' - first line)
4) from the mysql clienet (above) execute the following:
create database foo;
source init_bug.sql
The last linesof the output will be:
Query OK, 22 rows affected (0.00 sec)
Records: 22 Duplicates: 0 Warnings: 0 <-- insertof rows to
timesone_leap_second
Query OK, 0 rows affected, 1 warning (0.02 sec) <-- create of proc_privs
(warning related to
time format used)
ERROR 1067 (42000): Invalid default value for 'sql_mode'
>>> fail to create the proc table
Description: when running mysql-test-run.pl the mysql database is created without the 'proc' table. as a result any attempt to create a stored procedure fails as shown below: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 5.0.10-beta-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> create procedure sp1() set@x=1; ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 16 rows in set (0.00 sec) The problem is a result of the fact that the init_db.sql is failing to create the proc table. When running the init_db.sql (modified to run against a different databes) from the mysql client the following error ERROR 1067 (42000): Invalid default value for 'sql_mode' is returned for creating the proc table: CREATE TABLE proc ( db char(64) binary 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 DEFAULT '' NOT NULL, returns char(64) DEFAULT '' NOT NULL, body blob DEFAULT '' NOT NULL, definer char(77) binary 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_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 0 NOT NULL, comment char(64) binary DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) comment='Stored Procedures'; How to repeat: To see the problem: 1) run mysql-test-run.pl --start-and-exit 2) log in using the mysql client 3) execute the following: use test; create procedure sp1() set@x=1; To see the error in init_db.sql 1) run mysql-test-run.pl --start-and-exit 2) copy mysql-test/lib/init_db.sql to init_bug.sql 3) modify the init_buf.sql to use database 'foo' (instead of 'mysql' - first line) 4) from the mysql clienet (above) execute the following: create database foo; source init_bug.sql The last linesof the output will be: Query OK, 22 rows affected (0.00 sec) Records: 22 Duplicates: 0 Warnings: 0 <-- insertof rows to timesone_leap_second Query OK, 0 rows affected, 1 warning (0.02 sec) <-- create of proc_privs (warning related to time format used) ERROR 1067 (42000): Invalid default value for 'sql_mode' >>> fail to create the proc table