MySQL Bugs: #9648: Error storing user information

Bug #9648	Error storing user information
Submitted:	5 Apr 2005 14:05	Modified:	9 May 2005 6:33
Reporter:	Stefan Hinz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.0.19 / server 5.0.3	OS:	Windows (Windows 2000/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Connect to a 5.0.3 server. Edit user information. Click Apply changes.
Either an error message will pop up ("Error while storing the user information."), or additionally, a DOS window will open, with the following content:
** Message: Error executing query  :
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,IMPL
ICIT) for operation '='

How to repeat:
See Description. Errors as described don't occur with 4.0.x servers.

Suggested fix:
No idea.

Could you please state if you have upgraded from a 4.0 server to 5.0.3 or is this a fresh installation?

It seems that the tables in the mysql schema are in latin1, but they should be in utf8.

The 5.0.3 server was upgraded from a freshly installed 5.0.2 server.
Charset for the tables in the mysql schema is utf8, and collation is utf8_bin (see in Internal info for mysql.user, mysql.db, and mysql.proc).
However, the mysql.proc table has bugs (see Bug#9620).
Hm, thinking of it it appears like the same is true for the mysql.user_info table (in the Internals info now, too). I leave it to you to file a bug report. ;-)

I am receiving the same error using a clean install of MySql 4.1.11 and MySqlAdministrator 1.0.19 on a new Windows2003 Server. I actually re-installed from scratch and changed the default character set from latin1 to utf8.

I am also receiving the same error on a clean install of MySQL 4.1.11 and MySQL Administrator 1.0.19.  Character set is Latin1.

Forgot to mention running on Windows 2000 Professional SP4.

Same problem I ran into with v.1.0.19 MySQL Administrator.

Upgraded from 4.0.20a to 4.1.11 w/o using an installer; unzip, copy and paste.
Performed mysql_fix_privilege_tables.sql.

Using older version of MySQL Administrator 1.0.13, received the following error:
    MySQL Error Number 1267
    Illegal mix of collations (utf8_bin, IMPLICIT) and (latin1_swedish_ci, IMPLICIT) for operation '='
in a dialog box when clicked on Apply changes.

XP + SP2 box with mySQL running as a service.

Hope this information is useful.

I just used mysqlcc.exe 0.9.4 beta to modify user privileges on all databases (including mysql). Interestingly, it works just fine.

Here is the log that used to modify the user's privileges.

	REVOKE ALL PRIVILEGES ON *.* FROM 'userName'@'%'
	REVOKE GRANT OPTION ON *.* FROM 'userName'@'%'
	DELETE FROM mysql.db WHERE User='userName' AND Host = '%'
	DELETE FROM mysql.tables_priv WHERE User='userName' AND Host = '%'
	DELETE FROM mysql.columns_priv WHERE User='userName' AND Host = '%'
	FLUSH PRIVILEGES
	GRANT USAGE ON *.* TO 'userName'@'%'
	GRANT select,insert,update,delete,references,index,create temporary tables ON `database_1`.* TO 'userName'@'%'
	GRANT select,insert,update,delete,references,index,create temporary tables ON `book_sc`.* TO 'userName'@'%'
	GRANT select,insert,update,delete,references,index,create temporary tables ON `database_2`.* TO 'userName'@'%'
	GRANT select,insert,update,delete,references,index,create temporary tables ON `dotprj-innodb`.* TO 'userName'@'%'
	GRANT select,insert,update,delete,references,index,create temporary tables ON `dotprj-working`.* TO 'userName'@'%'
	GRANT select,insert,update,delete,references,index,create temporary tables ON `dotproject`.* TO 'userName'@'%'
	GRANT select,insert,update,delete,references,index,create temporary tables ON `netoffice`.* TO 'userName'@'%'
	GRANT select,insert,update,delete,index,references,create temporary tables ON `test`.* TO 'userName'@'%'
	SHOW DATABASES
	SHOW TABLES
	SHOW TABLES
	SHOW TABLES
	SHOW TABLES
	SHOW TABLES
	SHOW TABLES
	SHOW TABLES
	SHOW TABLES
	SHOW TABLES
	SHOW GRANTS FOR 'userName'@'%'

I have replaced the actual user name with `userName` and some database name with `database_#` where # is a number.

I have uploaded a fixed version of the MA to 

ftp://ftp.mysql.com/pub/mysql/download/mysql-administrator-1.0.20-win.msi

Please test if this version works well now. 

Before you test, please drop the tabel mysql.user_info. It will be recreated by the MA. 

Instead of dropping the table you can execute the following SQL statement in QB or the command line tool.

ALTER TABLE `mysql`.`user_info` CHARACTER SET utf8 COLLATE utf8_bin;

MySQL Administrator 1.0.20 seems to fix that problem. I deleted the mysql.user_info table as instructed, and then specified user information for an existing user (full name: Özgürlük Mäß). Could be saved without problems, and shows up in Query Browser exactly as entered.

I droped the table you mentioned, then downloaded and installed. But I've still got the same problem. I am able to create a user, but it only saves the username/password/description. It won't save any of the privilege settings. It also gives me the error box then, but I just click ok, then refresh, and it saved the user. So I can "create" a user, but I can't give the user any priveleges. I get the error box no matter what.

Cannot confirm the error reported by Shawn (using MySQL Administrator 1.0.20 / server 5.0.3). I tried again, this time with a newly created user, and didn't encounter any problems.

One thing I just noticed, you are using Server 5.0.3. I am using 4.1.11-nt, Client Version 5.0.0, Administrator 1.0.20. I don't know if that makes a difference. Also, FYI: Win XP Pro with all current updates from MS. Dell Inspiron 8500 (P4 Mobile, 2.5 Ghz, 512 MB)

I just downloaded everything a few days ago, and got the Admin 1.0.20 yesterday. I'm a new user of MySQL so I'm not very fluent with it. So far, it hasn't been a pleasent experience. I spent a whole day searching the net trying to figure out what this "Error while storing the user information." was about. At the end of the day I finally came across this thread.

I'm using XP pro SP2:
Server 4.1.11 with installer
Administrator 1.0.20
Query 1.1.6

I have had the same problems as reported here, have tried reinstalling the Server and have tried Administrator 1.0.18->20. This version is updating some of the new user data, i.e password, but not all, i.e max_questions, max_updates and max_connections.

The error message issued has also changed from latin1 to:
** Message: [Invalid UTF-8] Error executing query :
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_bin,IMPLICIT) for operation '='

Many thanks for working on this, hopefully you can get it fully resolved shortly - it is certainly better than it was!

Cheers, Neil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have done everything as stated above and am still getting this error.  Can anyone provide any sort of help?