Bug #34159 mysql_install_db fails with sql_mode=TRADITIONAL
Submitted: 30 Jan 2008 6:45 Modified: 12 Aug 2008 19:20
Reporter: Cristian Gafton Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:5.0.51, 5.0.66a OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: regression

[30 Jan 2008 6:45] Cristian Gafton
Description:
When /etc/my.cnf is configured with sql_mode=TRADITIONAL, the mysql_install_db scripts fail because the user table in mysql_system_tables.sql has a few columns with columns that do not have default values:

ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL,

How to repeat:
1. Enable sql_mode = TRADITIONAL in [mysqld] section of /etc/my.cnf.
2. Run mysql_install_db:

/usr/bin/mysql_install_db --user=mysql
Installing MySQL system tables...
ERROR: 1364  Field 'ssl_cipher' doesn't have a default value
080130  1:44:31 [ERROR] Aborting

080130  1:44:31 [Note] /usr/sbin/mysqld: Shutdown complete

Installation of system tables failed!

Suggested fix:
mysql_system_tables.sql should be syntactically correct in TRADITIONAL mode.
[30 Jan 2008 7:07] Cristian Gafton
Patching the mysql_system_tables.sql file to add a default value for the ssl_cipher column, to address the complaint, there is a new error popping up:

ERROR: 1101  BLOB/TEXT column 'ssl_cipher' can't have a default value

So, if it doesn't have a default value, it complains because it doesn't have it. If it does' it complains because it has it.
[30 Jan 2008 7:36] Jonathan Smith
Note that this configuration is reported to work on 5.0.45, making this a regression.

rPath is tracking the issue at https://issues.rpath.com/browse/RPL-2188
[30 Jan 2008 9:08] MySQL Verification Team
Thank you for the bug report.

[miguel@mira dbs]$ cd mysql-5.0/
[miguel@mira mysql-5.0]$ scripts/mysql_install_db 
Installing MySQL system tables...
ERROR: 1364  Field 'ssl_cipher' doesn't have a default value
080130  7:05:56 [ERROR] Aborting

080130  7:05:56 [Note] /home/miguel/dbs/5.0/libexec/mysqld: Shutdown complete

Installation of system tables failed!
[5 Feb 2008 9:09] Jonathan Smith
Christian Gafton has posted a proposed fix at https://issues.rpath.com/secure/attachment/13218/mysql-5.0.51-traditional.patch
[13 Feb 2008 1:37] Omer Barnir
workaround: set the sql_mode to empty when running the script and set it to its original value after that
[11 Apr 2008 22:52] Martin Mokrejs
I have the problem with 5.0.56 on Linux i686 when running mysql_install_db. I worked around it by setting in /etc/mysql/my.cnf:

[mysqld]
#sql_mode                       = STRICT_ALL_TABLES
sql_mode                        =
[31 Jul 2008 9:05] Georgi Kodinov
The fix is here : http://lists.mysql.com/commits/50754
[31 Jul 2008 9:59] Georgi Kodinov
Pushed to 5.0.67-community. Queued to 5.0-bugteam and up.
[31 Jul 2008 17:44] Paul DuBois
Noted in 5.0.67 changelog.

mysql_install_db failed if the server was running with a SQL mode of
TRADITIONAL. This program now resets the SQL mode internally to avoid
this problem.

Setting report to Patch Queued pending push of fix into other trees.
[7 Aug 2008 18:38] 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/51138

2744 Marc Alff	2008-08-07 [merge]
      Merge mysql-6.0-bugteam -> local bugfix branch
[12 Aug 2008 14:47] Bugs System
Pushed into 6.0.7-alpha  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:davi.arnaut@sun.com-20080812141852-8e6knbqclpfd8irn) (pib:3)
[12 Aug 2008 15:13] Bugs System
Pushed into 5.1.28  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:davi.arnaut@sun.com-20080812142843-he05ncsggstbn57z) (pib:3)
[12 Aug 2008 17:22] Paul DuBois
Noted in 5.1.28, 6.0.7 changelogs.
[12 Aug 2008 19:10] Bugs System
Pushed into 5.0.68  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:davi.arnaut@sun.com-20080812185100-d47qb8mz2ye6pe6b) (pib:3)
[12 Aug 2008 19:20] Paul DuBois
Noted in 5.0.68 changelog.
[28 Aug 2008 20:15] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[13 Sep 2008 22:40] Bugs System
Pushed into 6.0.6-alpha  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:hakan@mysql.com-20080716105246-eg0utbybp122n2w9) (pib:3)
[6 May 2009 20:23] Bugs System
Pushed into 5.0.82 (revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (version source revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (merge vers: 5.0.82) (pib:6)
[28 May 2009 8:13] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:jimw@mysql.com-20090515174051-ndjvfd1e9hc9k9c3) (merge vers: 5.1.36) (pib:6)
[17 Jun 2009 19:21] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:joro@sun.com-20090515134506-5mq3a8fafgbkx6u1) (merge vers: 6.0.12-alpha) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)