Bug #28401 mysql_upgrade Failed with STRICT_ALL_TABLES, ANSI_QUOTES and NO_ZERO_DATE
Submitted: 13 May 2007 15:11 Modified: 9 Jun 2007 18:14
Reporter: imacat . (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.41 OS:Linux (2.6.18)
Assigned to: Magnus BlÄudd CPU Architecture:Any

[13 May 2007 15:11] imacat .
Description:
    Hi.  This is imacat from Taiwan.  I found that several SQL commands issued by mysql_upgrade fail due to sql-modes.  I attached a list below.  The reason should be obvious.

    Please tell me if you need any information, or if I could be of any help.  Thank you.

=============
* STRICT_ALL_TABLES:
--------------
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
                 MODIFY specific_name char(64) DEFAULT '' NOT NULL,
                 MODIFY sql_data_access
                        enum('CONTAINS_SQL',
                             'NO_SQL',
                             'READS_SQL_DATA',
                             'MODIFIES_SQL_DATA'
                            ) DEFAULT 'CONTAINS_SQL' NOT NULL,
                 MODIFY body longblob DEFAULT '' NOT NULL,
                 MODIFY sql_mode
                        set('REAL_AS_FLOAT',
                            'PIPES_AS_CONCAT',
                            'ANSI_QUOTES',
                            'IGNORE_SPACE',
                            'NOT_USED',
                            'ONLY_FULL_GROUP_BY',
                            'NO_UNSIGNED_SUBTRACTION',
                            'NO_DIR_IN_CREATE',
                            'POSTGRESQL',
                            'ORACLE',
                            'MSSQL',
                            'DB2',
                            'MAXDB',
                            'NO_KEY_OPTIONS',
                            'NO_TABLE_OPTIONS',
                            'NO_FIELD_OPTIONS',
                            'MYSQL323',
                            'MYSQL40',
                            'ANSI',
                            'NO_AUTO_VALUE_ON_ZERO',
                            'NO_BACKSLASH_ESCAPES',
                            'STRICT_TRANS_TABLES',
                            'STRICT_ALL_TABLES',
                            'NO_ZERO_IN_DATE',
                            'NO_ZERO_DATE',
                            'INVALID_DATES',
                            'ERROR_FOR_DIVISION_BY_ZERO',
                            'TRADITIONAL',
                            'NO_AUTO_CREATE_USER',
                            'HIGH_NOT_PRECEDENCE'
                            ) DEFAULT '' NOT NULL,
                 DEFAULT CHARACTER SET utf8
--------------

ERROR 1101 (42000) at line 397: BLOB/TEXT column 'body' can't have a default value
--------------
=============
* ANSI_QUOTES
--------------
UPDATE user SET Show_db_priv= Select_priv, Super_priv=Process_priv, Execute_priv=Process_priv, Create_tmp_table_priv='Y', Lock_tables_priv='Y', Repl_slave_priv=file_priv, Repl_client_priv=File_priv where user<>"" AND @hadShowDbPriv = 0
--------------

ERROR 1054 (42S22) at line 173: Unknown column '' in 'where clause'
--------------
UPDATE user SET Create_view_priv=Create_priv, Show_view_priv=Create_priv where user<>"" AND @hadCreateViewPriv = 0
--------------

ERROR 1054 (42S22) at line 310: Unknown column '' in 'where clause'
--------------
UPDATE user SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv where user<>"" AND @hadCreateRoutinePriv = 0
--------------

ERROR 1054 (42S22) at line 351: Unknown column '' in 'where clause'
--------------
UPDATE db SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute_priv=Select_priv where user<>"" AND @hadCreateRoutinePriv = 0
--------------

ERROR 1054 (42S22) at line 352: Unknown column '' in 'where clause'
--------------
=============
* NO_ZERO_DATE
--------------
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
                 MODIFY specific_name char(64) DEFAULT '' NOT NULL,
                 MODIFY sql_data_access
                        enum('CONTAINS_SQL',
                             'NO_SQL',
                             'READS_SQL_DATA',
                             'MODIFIES_SQL_DATA'
                            ) DEFAULT 'CONTAINS_SQL' NOT NULL,
                 MODIFY body longblob DEFAULT '' NOT NULL,
                 MODIFY sql_mode
                        set('REAL_AS_FLOAT',
                            'PIPES_AS_CONCAT',
                            'ANSI_QUOTES',
                            'IGNORE_SPACE',
                            'NOT_USED',
                            'ONLY_FULL_GROUP_BY',
                            'NO_UNSIGNED_SUBTRACTION',
                            'NO_DIR_IN_CREATE',
                            'POSTGRESQL',
                            'ORACLE',
                            'MSSQL',
                            'DB2',
                            'MAXDB',
                            'NO_KEY_OPTIONS',
                            'NO_TABLE_OPTIONS',
                            'NO_FIELD_OPTIONS',
                            'MYSQL323',
                            'MYSQL40',
                            'ANSI',
                            'NO_AUTO_VALUE_ON_ZERO',
                            'NO_BACKSLASH_ESCAPES',
                            'STRICT_TRANS_TABLES',
                            'STRICT_ALL_TABLES',
                            'NO_ZERO_IN_DATE',
                            'NO_ZERO_DATE',
                            'INVALID_DATES',
                            'ERROR_FOR_DIVISION_BY_ZERO',
                            'TRADITIONAL',
                            'NO_AUTO_CREATE_USER',
                            'HIGH_NOT_PRECEDENCE'
                            ) DEFAULT '' NOT NULL,
                 DEFAULT CHARACTER SET utf8
--------------

ERROR 1067 (42000) at line 397: Invalid default value for 'modified'
--------------
ALTER TABLE proc CONVERT TO CHARACTER SET utf8
--------------

ERROR 1067 (42000) at line 441: Invalid default value for 'modified'
--------------
ALTER TABLE proc  MODIFY db
                         char(64) collate utf8_bin DEFAULT '' NOT NULL,
                  MODIFY definer
                         char(77) collate utf8_bin DEFAULT '' NOT NULL,
                  MODIFY comment
                         char(64) collate utf8_bin DEFAULT '' NOT NULL
--------------

ERROR 1067 (42000) at line 443: Invalid default value for 'modified'
--------------
=============

How to repeat:
Set sql-mode=STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE, and run mysql_upgrade.

Suggested fix:
For problems with ANSI_QUOTES, replacing "" with '' should work.

For problems with STRICT_ALL_TABLES and NO_ZERO_DATE, I have no idea.
[14 May 2007 9:53] Sveta Smirnova
Thank you for the report.

Verified as described with SQL MODE set to STRICT_ALL_TABLES or NO_ZERO_DATE
[19 May 2007 16:15] 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/27029

ChangeSet@1.2486, 2007-05-19 18:15:08+02:00, msvensson@pilot.blaudden +3 -0
  Bug #28401 mysql_upgrade Failed with STRICT_ALL_TABLES, ANSI_QUOTES and NO_ZERO_DATE
   - The SQL commands used by mysql_upgrade are written to be run
      with sql_mode set to '' - thus the scripts should change sql_mode
      for the session to make sure the SQL is legal.
[19 May 2007 16:23] 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/27031

ChangeSet@1.2499, 2007-05-19 18:22:55+02:00, msvensson@pilot.blaudden +1 -0
  Bug#28401 mysql_upgrade Failed with STRICT_ALL_TABLES, ANSI_QUOTES and NO_ZERO_DATE
   - Upgrade result file for 5.1
[22 May 2007 17:00] Bugs System
Pushed into 5.1.19-beta
[22 May 2007 17:03] Bugs System
Pushed into 5.0.44
[9 Jun 2007 18:14] Paul Dubois
Noted in 5.0.44, 5.1.19 changelogs.

mysql_upgrade failed if certain SQL modes were set. Now it sets the
mode itself to avoid this problem.
[27 Aug 2007 11:39] 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/33146

ChangeSet@1.2575, 2007-08-27 13:39:34+02:00, msvensson@pilot.(none) +1 -0
  Bug#28401 mysql_upgrade Failed with STRICT_ALL_TABLES, ANSI_QUOTES and NO_ZERO_DATE
   - Set sql_mode to default when creating system tables
[4 Sep 2007 17:11] Bugs System
Pushed into 5.1.23-beta