Bug #11954 init_db.sql fails to create proc table
Submitted: 15 Jul 2005 2:36 Modified: 20 Jul 2005 0:57
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.10-pre OS:Linux (Linux SuSE 9.3)
Assigned to: Jim Winstead CPU Architecture:Any

[15 Jul 2005 2:36] Omer Barnir
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:

  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',
                    ) 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(
                    ) 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 

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
[19 Jul 2005 1:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[19 Jul 2005 5:29] Elliot Murphy
Reviewed and tested the fix. The patch is harmonious with the definition in scripts/mysql_create_system_tables. OK to push.
[20 Jul 2005 0:57] Jim Winstead
Fixed in 5.0.10. No need to document.