Can’t change user options in MySQL Administrator

Posted by on 2012/05/31 Leave a comment (9) Go to comments

May be you installed MySQL 5 and you are not able to change user information, schema privileges and resources for your MySQL users using the MySQL Administrator. You select “User Administration” then a “User Account” but it keeps saying “No user selected” like in this capture:

It may be caused because your mysql database doesn’t have the user_info table. Run this in MySQL Query Browser or in the MySQL terminal to create the user_info table and the problem should be fixed.

DROP TABLE IF EXISTS `mysql`.`user_info`;
CREATE TABLE  `mysql`.`user_info` (
  `User` varchar(16) COLLATE utf8_bin NOT NULL,
  `Full_name` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `Description` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `Email` varchar(80) COLLATE utf8_bin DEFAULT NULL,
  `Contact_information` text COLLATE utf8_bin,
  `Icon` blob,
  PRIMARY KEY (`User`),
  KEY `user_info_Full_name` (`Full_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Stores additional user information';

People upgrading to MySQL 5 from other versions have solved this problem by executing the following command inside bin folder of MySQL installation directory:

mysqlcheck --check-upgrade --all-databases --auto-repair

And then running the mysql_fix_privilege_tables.sql script. You have to login to MySQL as root on the mysql database running this command from the bin folder of MySQL installation directory:

mysql -u root -p mysql

Type root’s password when asked and then run this in the MySQL terminal:

SOURCE ../scripts/mysql_fix_privilege_tables.sql;

If you don’t have the mysql_fix_privilege_tables.sql file in the scripts folder (or you don’t have that folder at all), download a zip archive noinstall version of MySQL, extract it and search that file. Or you can just download the file from here: mysql_fix_privilege_tables.sql.zip. Don’t forget unzipping it and copying it to your scripts folder inside the MySQL installation directory.

Once the script is completed restart your MySQL server.

Source: http://bugs.mysql.com/bug.php?id=25515

No related content found.

MySQLAdministrator, Resources, Schema Privileges, User Administration, User Information
Leave a comment ?

9 Comments.

  1. Juanjo Friday January 25th, 2013 at 05:52 PM

    I can’t believe no body say thank you to such a helpful piece of information. Thanks, you save me time today.

    Reply
  2. Nancy Monday March 4th, 2013 at 09:19 AM

    Thank you for sharing this info!

    Reply
  3. Tomás Thursday May 16th, 2013 at 01:06 PM

    Gracias, me funcionó la primera solución!

    Reply
  4. Pau Thursday May 23rd, 2013 at 02:13 PM

    WOW. Thanks for the post. Very Helpful. :)

    Reply
  5. Tinel Barb Wednesday May 29th, 2013 at 10:37 AM

    I didn’t find “mysql_fix_privilege_tables.sql” in the mysql installation package. However, your script did the job perfectly.
    i had the same problem with a fresh install of MySQL 5.5… very strange…
    The problem stays on MySQL bug reports from nearly 1 year.
    Thanks a lot!

    Reply
  6. Rafay Wednesday June 12th, 2013 at 01:29 PM

    Merci
    very helpful

    Reply
  7. Christopher David Elison Sunday June 30th, 2013 at 05:43 AM

    Thank you, the user_info SQL statement also worked for me! I have never had this problem before with MySQL Administrator until now. I first thought there was a problem with Windows file permissions/UAC or something.

    Reply
  8. Kevin Wednesday July 17th, 2013 at 11:52 PM

    I LOVE YOU! I’ve been trying to figure this out for 3 hours now.

    Reply
  9. Lucio Friday September 20th, 2013 at 08:47 PM

    Thank you!!!!

    Reply

Leave a Comment