Bug #34159 mysql_install_db fails with sql_mode=TRADITIONAL

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.

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.

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

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!

Christian Gafton has posted a proposed fix at https://issues.rpath.com/secure/attachment/13218/mysql-5.0.51-traditional.patch

workaround: set the sql_mode to empty when running the script and set it to its original value after that

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                        =

The fix is here : http://lists.mysql.com/commits/50754

Pushed to 5.0.67-community. Queued to 5.0-bugteam and up.

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.

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

Pushed into 6.0.7-alpha  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:davi.arnaut@sun.com-20080812141852-8e6knbqclpfd8irn) (pib:3)

Pushed into 5.1.28  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:davi.arnaut@sun.com-20080812142843-he05ncsggstbn57z) (pib:3)

Noted in 5.1.28, 6.0.7 changelogs.

Pushed into 5.0.68  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:davi.arnaut@sun.com-20080812185100-d47qb8mz2ye6pe6b) (pib:3)

Noted in 5.0.68 changelog.

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

Pushed into 6.0.6-alpha  (revid:kgeorge@mysql.com-20080731092804-3hqpvxtfwyue1b19) (version source revid:hakan@mysql.com-20080716105246-eg0utbybp122n2w9) (pib:3)

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)

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)

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)

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)

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)

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)

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)