MySQL Bugs: #25515: Can't select a user in GUI Administrator

Bug #25515	Can't select a user in GUI Administrator
Submitted:	10 Jan 2007 3:07	Modified:	1 Feb 2007 15:11
Reporter:	Emanuel Falkenauer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.2.8	OS:	Windows (Win XPP-SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
Running MySQL Administrator (in the GUI bundle) on MySQL 5.0.27-community-nt, I cannot use the User Administration: although I do see the list of currently defined users, whichever I select, the page says "No user selected" and all fields are grayed.
I tried running the Administrator both as root and myroot (which was GRANTed all privileges), but it'as always the same: "No user selected". I was logged to "localhost".
Mine is a double-Xeon 3.4 GHz with 1GB of RAM. Everything (MySQL, Administrator, etc.) was installed in default directories, except for the database files that are on D:. Only the foreign language support was dropped from installation of the Administrator.

How to repeat:
No idea: I can't imagine you would have the same problem and still let the tool out!

Thank you for a problem report. Sorry, but I can not repeat the behaviour described with the same versions. Please, send the results of:

select user, host from mysql.user;

mysql> select user,host from mysql.user;
+--------------+-----------+
| user         | host      |
+--------------+-----------+
| 6slmbb       | %         |
| combinerdemo | %         |
| myroot       | %         |
| odserver     | %         |
| olserver     | %         |
| oluser       | %         |
| optilinedemo | %         |
| root         | %         |
| olserver     | localhost |
| root         | localhost |
+--------------+-----------+
10 rows in set (0.00 sec)

So, if you click User Administration, then - myroot user, for example, you have nothing in User Information tab? What username do you use when connect to MySQL server instance? Please, try with "root", and inform about the results.

Screenshot of GUI Administrator

Attachment: MYSQLGUI.jpg (image/jpeg, text), 88.36 KiB.

Hello,
The previously attached screenshot shows that (1) I am logged as root@localhost, (2) the root user is selected in the list of users, yet (3) the User Information tab is completely empty. Thanks in advance for your help!

>Only the foreign language support was dropped from installation of the Administrator.

What is the default language of your system?

How did you drop language support?

Hi,
The default language of my system is English. I dropped the foreign languages by simply selecting in the installer the option "This feature and all its subfeatures will not be installed" (or something in that vein). The idea was to keep English (which, I suppose, is always available) and not to install Polish, Greek, etc.
Maybe I should mention that the OTHER features of the administrator (Server Information, Service Control, Startup Variables, etc.) seem to be working properly - only the User Administration (which is, sadly, the ONLY thing I am really interested in!) seems to have a problem. Thanks in advance!

Thank you for the feedback. I just able to repeat when selecting the root
user but when selecting another one it works, it is your case also?
Thanks in advance.

I'm sorry, but all users have the same problem on my machine (I did try that one, of course!). Thanks for your help.

Can you see information for users with '%' in the host tab?

| 6slmbb       | %         |
| combinerdemo | %         |
| myroot       | %         |
| odserver     | %         |
| olserver     | %         |
| oluser       | %         |
| optilinedemo | %         |

I just want to let someone know that I have the exact same problem. I'm using the same version, 1.2.8 running on XPSP2 and I'm connecting to a Netware 6.5/MysQL 5.1.9. the screen shot looks the same for me.

As we have not feedback from reporter, I mark it as verified with next sequence of actions to repeat:

1. Create user foo@localhost.
2. Be sure there is not user foo@%
3. Connect to MySQL Administrator
4. Try to select user foo

Hi,
Sorry for the delay in following up. I tried what you suggested, i.e. create user 'foo'%'localhost'. Unfortunately, the User Information tab is empty for 'foo' as well. Thanks,
Emanuel

screenshot

Attachment: 25515.jpg (image/jpeg, text), 88.55 KiB.

Emanuel, can you tell us, which server version you have used?

I'm having the exact same error as other users have mentioned, everything works just fine except for the User Administration, where all usernames are grayed out, same exact sittuation, screenshot would be worthless since both screenshots the other users added are exact the same as I would add, I'm having the same problem, can someone please fix this issue? Thanks.

Hi,

As I stated right in my initial bug submission, I'm running 5.0.27-community-nt.

Now just VERY cheap shot: did anybody of you try to install the database files on a different drive (say D:) from the rest of the installation (all programs on, say, C:) and see if you'd get the same problem? Other people seem to have the exact same problem, so I doubt it's something really strange on MY machine.

Thanks to you all!

Emanuel

Hi all, it seems I've found another post somewhere in this Bug report area, where folks were experiencing the same issues. What I gathered was it seems all of them were having the issue when they upgraded to mysql 5.0 from a previous version. I looked at my install, and when I looked at my tables, there were a few of them that had been removed during the upgrade, so the mysql administrator was looking for the tables and couldn't find them, therefore the error we keep getting. I'm sorry I don't have a screen shot at the moment, I'm at work, but I'll post the name of the tables later today. You might be able to find them out on your own if you go into the mysql tables, you'll notice some of them are highlighted in red. What I'll try and test to see if this did fix my issue (although I haven't tested it yet, I ran out of time) was to re-install mysql (I've backed up my tables first and my other DBs) then re-try it. I've gotten to the point were all is re-installed, but just haven't tried the mysql administrator yet to see if that worked. I'll also post the other posts I mentioned earlier, so you guys can see if that helps you...  Best of luck.

As promissed, here are the 3 other bug submission that seem almost exactly the same and what those folks did. As I said previously, I've tried it, just haven't tested it yet, but I thought this might be helpfull to everyone ..  Here are the links:

http://bugs.mysql.com/bug.php?id=12039
http://bugs.mysql.com/bug.php?id=12111
http://bugs.mysql.com/bug.php?id=25687

Hmm, I'm not sure the OP has had an upgrade but you know that you have to run the update_priv script if you update from 4.x?

Unfortunately, I still cannot reproduce the reported problem, nor can others I asked. Please try

SHOW CREATE TABLE mysql.priv

and let me know what you get.

Hi,
Well done, Mike: this might be getting hotter! Here you go, on my machine:

mysql> show create table mysql.priv;
ERROR 1146 (42S02): Table 'mysql.priv' doesn't exist
mysql>

Could it be the problem? As for the nonexistence of mysql.priv, could it be because, according to my recollection, I never explicitly specified any privilege (that is, I usually GRANT ALL PRIVILEGES)?

Just in case it' of any help, here are all my tables in mysql:

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
| user_info                 |
+---------------------------+
18 rows in set (0.02 sec)

mysql>

As for the upgrade from 4.x, nope: I never installed 4.x on this machine.

Cheers & thanks,

Emanuel

Oh my fault, sorry, there is no table mysql.priv. It is either mysql.tables_priv or mysql.procs_priv for individual rights on certain schema elements and of course the mysql.user table with all global rights and host settings.

To bring us further let's try something. First, enable the host listing in the user list (see context menu [right click in the users list] or in the app options). This should give you a tree with the users and all defined hosts for them as sub nodes. A user generally stands for the any-host entry (% host entry). Individual hosts (including localhost) are kept as subfolders (if defined). Try to select one of them and see if you finally get the details of that user for that host.

Try also to create new host entries (also via context menu) and see if you can work with them. Play with the possibilities (add users, delete/clone, add host, remove host etc.). Please let me know if that changes anything for you.

Note: if you select the name of a user without any-host entry defined you still will get a disabled right pane, but it should show the text "User without any-host (%) entry selected" to the right of the little icon. This is different from the "No user selected" text you get, when you open up the user management first time or refresh the user list.

OK, just to keep you all posted, I've backed up my original tables (the ones I had created for my use, not the mysql tables), uninstalled mysql, re-installed mysql from scratch, re-created the users I had previously, imported the tables from back up and now all works as advertised. It seems the original mysql tables were somehow corrupt or a ghost messed with them, but after I completely uninstalled mysql and re-installed it, it all worked fine, meaning now I'm able to change user permissions and all from MySQL Administrator GUI. This sure saved some time, since we still haven't figureed out what the exact 'cause of the problem is, but at least we know a clean install (re-install) will "fix" the problem. Hope this information helps you guys out also. Thanks again everyone for the input and ideas on this subject.

Dan, thank you for letting us know. What was your original installation? Did you upgrade your server in the past (even from 5.x to 5.x)? Or was it an original installation that got messed up?

Not a problem, anything to help the community. I originally upgraded from 4.x (don't remember exactly) to 5.18 then 5.24 .... if my memory serves me right that is ...  :-)

Hi all,
It looks indeed like you finally nailed it down: I, too, have upgraded from a previous version, namely from 5.0.15. Kudos!!

That being said, it would be great if the bug was SOLVED, rather than just gone around by a complete uninstall and reinstall of MySQL. Because honestly, with all my due respect, I have no time for the backups&co just to be able to see the users in the GUI Administrator.
So, well done... but I will keep hoping that a future version of either MySQL or the Administrator will solve the problem. In the meantime, I will just uninstall the Administrator.

Thanks & cheers to you all,

Emanuel

Emanuel, how often have you run the script to fix the privileges during upgrade? You know that a database update is sometimes not as simple as installing the version over the old one? Read these pages: http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html and particularly this http://dev.mysql.com/doc/refman/5.0/en/mysql-fix-privilege-tables.html. Read also the user comments.

Another reliable way of upgrading is to install the new server in a different directory and dump the data with the old server. Then restore to the new one and you should be done. With MA this is a piece of cake usually.

Mike,

Ok, here it is: I succeeded (with some pain, see below) to execute (the equivalent of, or so I believe!) mysql_upgrade and, indeed, ALL LOOKS FINE with the GUI Administrator. Thanks!

Tha being said, mysql_upgrade is actually impossible to run, as it constantly stumbles on directory names with spaces embedded. I find that largely beyond comprehension, given that the DEFAULT MySQL directory on Win is "C:\Program Files\MySQL\MySQL Server 5.0". Of course I tried every possible way to pass the quotes to mysql_upgrade, but to no avail. Ergo mysql_upgrade is unusable on Win as it is (or there is an explanation missing in the manual on how to go around this).

So I run "mysqlcheck --check-upgrade --all-databases --auto-repair" by hand from the bin directory, and then "SOURCE scripts/mysql_fix_privilege_tables.sql" from within the MySQL console. That fixed the problems indeed.

Finally, I wonder why these scripts are not run automatically (or optionally) from within the Win installer, given that they seem necessary for a normal operation of the new version?

Anyway, thanks for all your help,

Emanuel

Please see bug: http://bugs.mysql.com/bug.php?id=25687

Wait Mike, I've been following this and have tried to solve it using the same steps that Emanuel did with no success.

I've used the both
mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

And I still can't manage users in Administrator

Please advise.

Had the same problem. MySQL 5.0.27 upgraded from a previous 5.x version.

Ran

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables (via SOURCE command in mysql console)

and it solved the problem.  These scripts really should be automatically executed on upgrade.

Bug #26693 was marked as duplicate of this one

I have the same issue but I didn't upgrade. I installed MySql 6.0.3 and MySql Administrator 1.2.12. "User Information" tab says "No User Selected". Same thing in "Backup" and "Restore".

My user has right for everything:

GRANT ALL PRIVILEGES ON *.* TO 'ruslan'@'%' IDENTIFIED BY PASSWORD '*0000000000000000000000000000000000000000' WITH GRANT OPTION

Thank you.

Ran

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables (via SOURCE command in mysql console)

and it solved the problem.  These scripts really should be automatically executed on
upgrade.

I have the same issue but I didn't upgrade. I installed MySql 6.0.3 alfa and MySql Administrator 1.2.12. "User Information" tab says "No User Selected" for all selected users into the list. Same thing in "Backup" and "Restore".

My user root has right for everything.
can be that Mysql administrator 1.2.12 isn't ready to run along with MySql 6.0.3?
You recommend me install Mysql server 5.x?

Thank you.

I encountered the same problem as some of you two days ago. When I selected a user in the MySQL Administrator (Version 1.2.12 Win XP) all fields where gray and could not be edited. It did display a message "User without any-host (%) entry selected"  exactly like in this screenshot from Sveta Smirnova: http://bugs.mysql.com/file.php?id=5487

This seems to be a userinterface problem of the Windows client. A co-worker of mine used the same version on Ubuntu Hardy (installed through apt-get) and it does show the correct behaviour by default. To get the correct behaviour in the Windows client do the following:
-> Right-click on the user list
-> Select the "Show hosts in users list" option

Now when you click on a username in that list you see a list of hosts appear (probably localhost and maybe some other hosts too). In the Linux client, this seems to be the default behaviour. When you click on one of the hostnames you can view and edit the user information.

I know this has been a couple of years but I now have the same problem.  I did not upgrade.  I am setting up a new computer.  Installed the latest MySQL server and MySQL Administrator (5.5.15 for Win64 and 1.2.17 respectively).  I did a restore of a database from my old computer running 5.0.51a for Win32.  When I fire up administrator, I can create a user.  I can click that user and do everything to it that I would expect.  However, if I close Administrator and open it back up, then that new user becomes just like the other, not clickable.

Also, I have the "Show hosts on user list" checked but do not see them.  There is no tree, just the list of users.

What is the solution to this problem?

I had the same problem, tried some tricks mentioned here and finally my problem was solved by noticing that "mysql" database does not have table "user_info"
and fixed by manually creating this table.
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';

I have just installed V5.5.14 on Windows XP SP3 using the msi method. The schema mysql was missing the table user_info and consequently I was unable to create/manage users with the MySQL Administrator tool. I created this table as described in this Bug report and my problem was resolved. I find it very hard to believe that this installation package could create a configuration that is not immediately usable. Will this issue be resolved ? A novice user would not be able to progress this sad story IMHO

Thanks Ahmed Seddiq! Your solution worked perfectly.
Rick Neubig

Likewise, thanks for your solution Ahmed.
Worked great.

[8 Sep 2011 13:04] Ahmed Seddiq 

Funciona perfecto tenes razon falta crear la tabla user_info con los datos que marcas vos.

GRACIAS!!!!!