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:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.11 OS:Microsoft Windows (winXP)
Assigned to: Jim Winstead CPU Architecture:Any

[21 Aug 2005 18:45] Peter Brawley
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;
[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.