| 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: | |
| 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 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.
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,"