Can’t change user options in MySQL Administrator

Posted by on 2012/05/31 Leave a comment (11) 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

MySQLAdministrator, Resources, Schema Privileges, User Administration, User Information

You might also like

Change root password in MySQL
In GNU/Linux: $ sudo mysqld_safe --skip-grant-tables & $ mysql -u root -p #press enter to use a...
Run shell commands in Mac OS X executing a normal application
Once I installed Apache2+MySQL5+PHP5.3 using MacPorts I wanted to start and stop the services without...
Apache 2 + MySQL 5 + PHP 5.3 + phpMyAdmin 3.5.4 installation script for Mac OS X using MacPorts
Based on the instructions provided by Gilles Fabio I've created a complete installation script to install...
Useful Apache 2 and MySQL 5 service aliases for MacPorts
Those are some aliases I use to start, stop and restart Apache 2 and MySQL on my MacPorts installation...
Leave a comment ?

11 Comments.

  1. Juanjo %A %B %e%q, %Y at %I:%M %p

    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 %A %B %e%q, %Y at %I:%M %p

    Thank you for sharing this info!

    Reply
  3. Tomás %A %B %e%q, %Y at %I:%M %p

    Gracias, me funcionó la primera solución!

    Reply
  4. Pau %A %B %e%q, %Y at %I:%M %p

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

    Reply
  5. Tinel Barb %A %B %e%q, %Y at %I:%M %p

    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 %A %B %e%q, %Y at %I:%M %p

    Merci
    very helpful

    Reply
  7. Christopher David Elison %A %B %e%q, %Y at %I:%M %p

    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 %A %B %e%q, %Y at %I:%M %p

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

    Reply
  9. Lucio %A %B %e%q, %Y at %I:%M %p

    Thank you!!!!

    Reply
  10. imarh %A %B %e%q, %Y at %I:%M %p

    Very very very thank you!

    Reply
  11. venancio guedes %A %B %e%q, %Y at %I:%M %p

    thanks you, here work Perfect!

    Reply

Leave a Comment