Bug #24750 MySQL is missing mysql.proc table after database setup
Submitted: 1 Dec 2006 11:37 Modified: 26 Jan 2007 13:21
Reporter: Jacques Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.27 OS:Linux (Redhat Linux RHEL4)
Assigned to: CPU Architecture:Any

[1 Dec 2006 11:37] Jacques
Description:
The mysql database initialisation scripts is not creating the mysql.proc table.

How to repeat:
Setup the RPM's and try and create a stored procedure.  You get an error back saying that mysql.proc does not exist.

Suggested fix:
Ensure the following table schema for proc is created.

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" char(64) NOT NULL default '',
  "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 '',
  PRIMARY KEY  ("db","name","type")
);
[1 Dec 2006 12:14] Jacques
Error message is:

ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
[1 Dec 2006 12:51] Sveta Smirnova
Thank you for the report.

There is similar verified bug #24276. Please confirm or refuse if they are same.

I.e. provide output of statement:

SELECT @@sql_mode;
[2 Jan 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Jan 2007 13:00] Jacques
mysql> SELECT @@sql_mode;
+---------------------------------------------------------------------------------+
| @@sql_mode                                                                      |
+---------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[26 Jan 2007 13:21] Valeriy Kravchuk
Dupl
[26 Jan 2007 13:22] Valeriy Kravchuk
Duplicate of bug #23669.