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
• Status: Closed
• 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:[e-mail]-20080731092804-3hqpvxtfwyue1b19) (version source revid:[e-mail]-20080812141852-8e6knbqclpfd8irn) (pib:3)

[12 Aug 2008 15:13] Bugs System

Pushed into 5.1.28  (revid:[e-mail]-20080731092804-3hqpvxtfwyue1b19) (version source revid:[e-mail]-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:[e-mail]-20080731092804-3hqpvxtfwyue1b19) (version source revid:[e-mail]-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:[e-mail]-20080822132131-uveo6wiuecy6m2b8) (version source revid:[e-mail]-20080822132131-uveo6wiuecy6m2b8) (pib:3)

[13 Sep 2008 22:40] Bugs System

Pushed into 6.0.6-alpha  (revid:[e-mail]-20080731092804-3hqpvxtfwyue1b19) (version source revid:[e-mail]-20080716105246-eg0utbybp122n2w9) (pib:3)

[6 May 2009 20:23] Bugs System

Pushed into 5.0.82 (revid:[e-mail]-20090506130632-s1cl4ygdj9rt2rrz) (version source revid:[e-mail]-20090506130632-s1cl4ygdj9rt2rrz) (merge vers: 5.0.82) (pib:6)

[28 May 2009 8:13] Bugs System

Pushed into 5.1.36 (revid:[e-mail]-20090528073639-yohsb4q1jzg7ycws) (version source revid:[e-mail]-20090515174051-ndjvfd1e9hc9k9c3) (merge vers: 5.1.36) (pib:6)

[17 Jun 2009 19:21] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090616183122-chjzbaa30qopdra9) (version source revid:[e-mail]-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:[e-mail]-20090826132541-yablppc59e3yb54l) (version source revid:[e-mail]-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:[e-mail]-20090826105955-bkj027t47gfbamnc) (version source revid:[e-mail]-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:[e-mail]-20090825194404-37rtosk049t9koc4) (version source revid:[e-mail]-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:[e-mail]-20090827163030-6o3kk6r2oua159hr) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)