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: | |
| 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 |
[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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.062 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.