| 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
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.