| Bug #12705 | Syntax error in mysql_fix_privilege_tables.sql | ||
|---|---|---|---|
| Submitted: | 21 Aug 2005 18:45 | Modified: | 1 Sep 2005 0:45 |
| Reporter: | Peter Brawley (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.11 | OS: | Windows (winXP) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[22 Aug 2005 7:46]
Vasily Kishkin
Thanks for bug report. I was able to reproduce the bug. In my case I got follow message: mysql -u root mysql <mysql_fix_privilege_tables.sql ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
[22 Aug 2005 18:05]
Peter Brawley
The mysql_fix_privilege_tables.sql script does not bring mysql table structures up to date for 5.0.11. After correcting the script (missing comma above) and running it, our mysql db had many differences from what the fix script tries to enforce. In case our situation is not unique, here is the script we needed to run to correct those differences:
SET STORAGE_ENGINE=MyISAM;
ALTER TABLE db
MODIFY Create_view_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Show_view_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Create_routine_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Alter_routine_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Execute_priv enum('N','Y') character set utf8 NOT NULL default 'N',
CHARACTER SET utf8 collate utf8_bin;
ALTER TABLE func
MODIFY name char(64) collate utf8_bin NOT NULL default '',
CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE help_category
MODIFY help_category_id smallint(5) unsigned NOT NULL,
MODIFY `name` char(64) NOT NULL,
MODIFY parent_category_id smallint(5) unsigned default NULL,
MODIFY url char(128) NOT NULL,
CHARACTER SET utf8;
ALTER TABLE help_keyword
MODIFY help_keyword_id int(10) unsigned NOT NULL,
MODIFY`name` char(64) NOT NULL,
CHARACTER SET utf8;
ALTER TABLE help_relation
MODIFY help_topic_id int(10) unsigned NOT NULL,
MODIFY help_keyword_id int(10) unsigned NOT NULL,
CHARACTER SET utf8;
ALTER TABLE help_topic
MODIFY help_topic_id int(10) unsigned NOT NULL,
MODIFY description text NOT NULL,
MODIFY example text NOT NULL,
MODIFY `name` char(64) NOT NULL,
MODIFY help_category_id smallint(5) unsigned NOT NULL,
MODIFY url char(128) NOT NULL,
CHARACTER SET utf8;
ALTER TABLE host
MODIFY Create_view_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Show_view_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Create_routine_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Alter_routine_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Execute_priv enum('N','Y') character set utf8 NOT NULL default 'N';
ALTER TABLE proc
MODIFY `type` enum('FUNCTION','PROCEDURE') NOT NULL,
MODIFY `returns` char(64) NOT NULL default '';
ALTER TABLE procs_priv
DROP PRIMARY KEY,
ADD PRIMARY KEY (Host,Db,User,Routine_name,Routine_type);
ALTER TABLE time_zone
MODIFY Use_leap_seconds enum('Y','N') NOT NULL default 'N',
CHARACTER SET utf8;
ALTER TABLE time_zone_leap_second
MODIFY Transition_time bigint(20) NOT NULL,
MODIFY Correction int(11) NOT NULL,
CHARACTER SET utf8;
ALTER TABLE time_zone_name
MODIFY `Name` char(64) NOT NULL,
MODIFY Time_zone_id int(10) unsigned NOT NULL,
CHARACTER SET utf8;
ALTER TABLE time_zone_transition
MODIFY Time_zone_id int(10) unsigned NOT NULL,
MODIFY Transition_time bigint(20) NOT NULL,
MODIFY Transition_type_id int(10) unsigned NOT NULL,
CHARACTER SET utf8;
ALTER TABLE time_zone_transition_type
MODIFY Time_zone_id int(10) unsigned NOT NULL,
MODIFY Transition_type_id int(10) unsigned NOT NULL,
MODIFY Abbreviation char(8) NOT NULL default '',
CHARACTER SET utf8;
ALTER TABLE user
MODIFY Create_view_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Show_view_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Create_routine_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Alter_routine_priv enum('N','Y') character set utf8 NOT NULL default 'N',
MODIFY Create_user_priv enum('N','Y') character set utf8 NOT NULL default 'N';
[23 Aug 2005 23:37]
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/internals/28728
[29 Aug 2005 23:55]
Jim Winstead
Fixed in 5.0.12.
[1 Sep 2005 0:45]
Paul DuBois
Noted in 5.0.12 changelog.
Description: As shipped, mysql_fix_privilege_tables.sql for 5.0.11 is missing a comma at line 512, so mysql reports the error ... 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 42 # Correct the name fields to not binary, and expand sql_data_access 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 # <-- MISSING COMMA DEFAULT CHARACTER SET utf8; How to repeat: run the script Suggested fix: ) DEFAULT '' NOT NULL, # <-- ADD MISSING COMMA DEFAULT CHARACTER SET utf8;