Bug #34159 mysql_install_db fails with sql_mode=TRADITIONAL
Submitted: 30 Jan 2008 7:45 Modified: 12 Aug 2008 21:20
Reporter: Cristian Gafton
Status: Closed
Category:Server: Installing Severity:S3 (Non-critical)
Version:5.0.51, 5.0.66a OS:Any
Assigned to: Davi Arnaut Target Version:5.0+
Tags: regression
Triage: D3 (Medium) / R2 (Low) / E2 (Low)

[30 Jan 2008 7: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 8: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 8: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 10:08] Miguel Solorzano 
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 10: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 2:37] Omer BarNir 
workaround: set the sql_mode to empty when running the script and set it to its original
value after that
[12 Apr 2008 0: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 11:05] Georgi Kodinov 
The fix is here : http://lists.mysql.com/commits/50754
[31 Jul 2008 11:59] Georgi Kodinov 
Pushed to 5.0.67-community. Queued to 5.0-bugteam and up.
[31 Jul 2008 19: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 20: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 16: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 17: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 19:22] Paul DuBois 
Noted in 5.1.28, 6.0.7 changelogs.
[12 Aug 2008 21: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 21:20] Paul DuBois 
Noted in 5.0.68 changelog.
[28 Aug 2008 22: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)
[14 Sep 2008 0: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 22: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 10: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 21: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 15: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 15: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 15: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 18: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)