| Bug #11372 | Invalid default value for 'sql_mode' when running mysql_fix_privilege_tables | ||
|---|---|---|---|
| Submitted: | 16 Jun 2005 9:19 | Modified: | 25 Jul 2006 0:17 |
| Reporter: | ugenn | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S3 (Non-critical) |
| Version: | 5.0.7 beta | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[16 Jun 2005 11:11]
ugenn
It appears to be a charset problem. It works when default-character-set = utf8 or character-set-server = utf8 is set my.cnf. I commented those out and now it works.
[25 Jun 2005 15:57]
Jorge del Conde
Hi I was unable to reproduce this bug using 5.0.8 bk. Can you please give me step by step instructions on how to repeat it ? Thanks
[31 Jul 2005 19:15]
Jorge del Conde
Thanks for your bug report. I was able to reproduce this using a recent 5.0bk pull: root-mysql/data# mysql_fix_privilege_tables -uroot --verbose This script updates all the mysql privilege tables to be usable by MySQL 4.0 and above. This is needed if you want to use the new GRANT functions, CREATE AGGREGATE FUNCTION, stored procedures, or more secure passwords in 4.1 You can safely ignore all 'Duplicate column' and 'Unknown column' errors because these just mean that your tables are already up to date. This script is safe to run even if your tables are already up to date! ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type' ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv' ERROR 1060 (42S21) at line 100: Duplicate column name 'type' ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv' ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions' ERROR 1060 (42S21) at line 137: Duplicate column name 'Create_tmp_table_priv' ERROR 1060 (42S21) at line 140: Duplicate column name 'Create_tmp_table_priv' ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor' ERROR 1060 (42S21) at line 252: Duplicate column name 'Create_view_priv' ERROR 1060 (42S21) at line 253: Duplicate column name 'Create_view_priv' ERROR 1060 (42S21) at line 254: Duplicate column name 'Create_view_priv' ERROR 1060 (42S21) at line 259: Duplicate column name 'Show_view_priv' ERROR 1060 (42S21) at line 260: Duplicate column name 'Show_view_priv' ERROR 1060 (42S21) at line 261: Duplicate column name 'Show_view_priv' ERROR 1060 (42S21) at line 282: Duplicate column name 'Create_routine_priv' ERROR 1060 (42S21) at line 284: Duplicate column name 'Create_routine_priv' ERROR 1060 (42S21) at line 289: Duplicate column name 'Alter_routine_priv' ERROR 1060 (42S21) at line 291: Duplicate column name 'Alter_routine_priv' ERROR 1060 (42S21) at line 293: Duplicate column name 'Execute_priv' ERROR 1060 (42S21) at line 306: Duplicate column name 'max_user_connections' ERROR 1060 (42S21) at line 315: Duplicate column name 'Create_user_priv' ERROR 1064 (42000) at line 472: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8' at line 41 done root-mysql/data#
[25 Jul 2006 0:17]
Jim Winstead
This is no longer a bug (if it ever was) -- the original report only shows the warnings that are to be expected from mysql_fix_privilege_tables. Jorge's later verification was probably related to Bug #12705. The default was also changed from 0 to '' for these SET fields in the fix for Bug #11365.
Description: I get the following when trying to created the system tables for stored procs/funcs by running 'mysql_fix_privilege_tables --verbose' ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type' ERROR 1060 (42S21) at line 70: Duplicate column name 'Routine_type' ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv' ERROR 1060 (42S21) at line 100: Duplicate column name 'type' ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv' ERROR 1054 (42S22) at line 121: Unknown column '' in 'where clause' ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions' ERROR 1060 (42S21) at line 137: Duplicate column name 'Create_tmp_table_priv' ERROR 1060 (42S21) at line 140: Duplicate column name 'Create_tmp_table_priv' ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor' ERROR 1060 (42S21) at line 252: Duplicate column name 'Create_view_priv' ERROR 1060 (42S21) at line 253: Duplicate column name 'Create_view_priv' ERROR 1060 (42S21) at line 254: Duplicate column name 'Create_view_priv' ERROR 1060 (42S21) at line 259: Duplicate column name 'Show_view_priv' ERROR 1060 (42S21) at line 260: Duplicate column name 'Show_view_priv' ERROR 1060 (42S21) at line 261: Duplicate column name 'Show_view_priv' ERROR 1054 (42S22) at line 266: Unknown column '' in 'where clause' ERROR 1060 (42S21) at line 277: Duplicate column name 'Create_routine_priv' ERROR 1060 (42S21) at line 278: Duplicate column name 'Create_routine_priv' ERROR 1060 (42S21) at line 279: Duplicate column name 'Create_routine_priv' ERROR 1060 (42S21) at line 284: Duplicate column name 'Alter_routine_priv' ERROR 1060 (42S21) at line 285: Duplicate column name 'Alter_routine_priv' ERROR 1060 (42S21) at line 286: Duplicate column name 'Alter_routine_priv' ERROR 1060 (42S21) at line 288: Duplicate column name 'Execute_priv' ERROR 1060 (42S21) at line 289: Duplicate column name 'Execute_priv' ERROR 1054 (42S22) at line 294: Unknown column '' in 'where clause' ERROR 1054 (42S22) at line 295: Unknown column '' in 'where clause' ERROR 1060 (42S21) at line 301: Duplicate column name 'max_user_connections' ERROR 1060 (42S21) at line 310: Duplicate column name 'Create_user_priv' ERROR 1067 (42000) at line 409: Invalid default value for 'sql_mode' ERROR 1067 (42000) at line 465: Invalid default value for 'sql_mode' I'm running innodb if it matters. How to repeat: Run mysql_fix_privilege_tables on a system w/o proc tables. Suggested fix: I tried replacing the 0 literals with '' in mysql_fix_privilege_tables.sql and it works, although I'm not sure what the side effects/repercussions are for doing so. --- mysql_fix_privilege_tables.old 2005-06-16 17:11:10.000000000 +0800 +++ mysql_fix_privilege_tables.sql 2005-06-16 17:13:16.000000000 +0800 @@ -456,7 +456,7 @@ 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' - ) DEFAULT 0 NOT NULL, + ) DEFAULT '' NOT NULL, comment char(64) binary DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) comment='Stored Procedures'; @@ -501,4 +501,4 @@ 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' - ) DEFAULT 0 NOT NULL; + ) DEFAULT '' NOT NULL;