Bug #23669 mysql.proc not created in default install
Submitted: 26 Oct 2006 15:10 Modified: 9 Mar 2007 5:02
Reporter: Kristian Koehntopp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S2 (Serious)
Version:5.0.26 OS:Linux (SLES9)
Assigned to: Magnus BlÄudd CPU Architecture:Any
Tags: install, stored procedure

[26 Oct 2006 15:10] Kristian Koehntopp
Description:
I was installing

kristian@dbsd01:~> ls -l mysql-max-5.0.26-linux-x86_64-glibc23.tar.gz
-rw-r--r--   1 kristian users  46979410 2006-10-23 13:38 mysql-max-5.0.26-linux-x86_64-glibc23.tar.gz

kristian@dbsd01:~> md5sum mysql-max-5.0.26-linux-x86_64-glibc23.tar.gz
b8e32fca6c2e2740945f1316f2d0d910  mysql-max-5.0.26-linux-x86_64-glibc23.tar.gz

and found

root@localhost:mysql.sock [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)

As you can see, proc ist missing.

Further investigation revealed that

dbsd01:/usr/local/mysql-max-5.0.26-linux-x86_64-glibc23 # ./bin/mysql_create_system_tables data/mysql/| grep "CREATE TABLE proc " | ./mysql-3307 -u root -pmysql2006 mysql
ERROR 1101 (42000) at line 1: BLOB/TEXT column 'param_list' can't have a default value

This may be related to

root@localhost:mysql.sock [(none)]> show global variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

How to repeat:
Install the 5.0.26 package and check if you have mysql.proc. Also check if creation of mysql.proc generates any warnings should you have a lesser sql_mode.

Suggested fix:
--- mysql-max-5.0.26-linux-x86_64-glibc23-patch/bin/mysql_create_system_tables     2006-10-24 15:23:00.000000000 +0200
+++ mysql-max-5.0.26-linux-x86_64-glibc23/bin/mysql_create_system_tables        2006-10-26 17:01:56.890046844 +0200
@@ -681,9 +681,9 @@
   c_p="$c_p                     ) DEFAULT 'CONTAINS_SQL' NOT NULL,"
   c_p="$c_p   is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL,"
   c_p="$c_p   security_type     enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL,"
-  c_p="$c_p   param_list        blob DEFAULT '' NOT NULL,"
+  c_p="$c_p   param_list        blob NOT NULL,"
   c_p="$c_p   returns           char(64) DEFAULT '' NOT NULL,"
-  c_p="$c_p   body              longblob DEFAULT '' NOT NULL,"
+  c_p="$c_p   body              longblob NOT NULL,"
   c_p="$c_p   definer           char(77) collate utf8_bin DEFAULT '' NOT NULL,"
   c_p="$c_p   created           timestamp,"
   c_p="$c_p   modified          timestamp,"
[26 Oct 2006 18:48] Sveta Smirnova
Thank you for the report.

I can not repeat the problem using standart mysql_install_db script.

Please could you provide output of statement SELECT @@sql_mode, @@glogal.sql_mode ?
[26 Oct 2006 18:58] Kristian Koehntopp
Sveta,

the SQL mode is shown in the original bug report. It is

REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[26 Oct 2006 19:03] Kristian Koehntopp
root@localhost [kris]> set sql_mode = 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [kris]> create table b ( b blob default '' not null );
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost [kris]> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'b' can't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [kris]> drop table b;
Query OK, 0 rows affected (0.00 sec)

root@localhost [kris]> set sql_mode = "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.03 sec)

root@localhost [kris]> create table b ( b blob default '' not null );
ERROR 1101 (42000): BLOB/TEXT column 'b' can't have a default value
[26 Oct 2006 19:21] Sveta Smirnova
Verified as described.

Easiest test:

$scripts/mysql_install_db --sql-mode=STRICT_ALL_TABLES --basedir=. --datadir=./data

Workaround: $scripts/mysql_install_db --sql-mode= --basedir=. --datadir=./data
[14 Nov 2006 0:03] Timothy Smith
Bug #23937 marked a duplicate of this one.
[1 Dec 2006 12:52] Sveta Smirnova
Bug #24276 was marked as duplicate of this one.
[4 Dec 2006 0:07] Roland Bouman
Please mark 24782 as a duplicate of this one.

I think that a little test should be added after creating the system tables to see if all succeeded, and output the status to the user. This will help this problem being spotted at an early stage in the future.

Of course, I also think the scripts should be fixed - it should set it's own sql mode.
[4 Dec 2006 8:18] Sveta Smirnova
Bug #24782 has been marked as duplicate of this one.
[7 Dec 2006 21:40] Sveta Smirnova
Bug #24189 has been marked as duplicate of this one.
[9 Jan 2007 10:26] Sveta Smirnova
Bug #25488 has been marked as duplicate of this one
[26 Jan 2007 13:23] Valeriy Kravchuk
Bug #24750 is a duplicate of this one.
[21 Feb 2007 7:45] Sveta Smirnova
Bug #26512 was marked as duplicate of this one
[27 Feb 2007 14:31] 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:

  http://lists.mysql.com/commits/20652

ChangeSet@1.2449, 2007-02-27 15:28:19+01:00, msvensson@pilot.blaudden +1 -0
  Bug#23669 mysql.proc not created in default install
   - Remove DEFAULT values for blob's in mysql_system_tables.sql
[9 Mar 2007 5:02] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.38 and 5.1.17 changelogs.
[25 Feb 2009 3:48] Ben Krug
A user has experienced this with 5.0.42.  Was this not fixed in 5.0.38?  The error was the same, the issue was with the proc table, and setting the sql_mode='' worked, so it appears to be the same issue.
[25 Feb 2009 7:20] Sveta Smirnova
Ben, I can not repeat described behavior with current bzr sources: did upgrade from 4.1, used sql_mode='strict_all_tables'. So bug looks fixed for me.