MySQL 5.5.8
I have just downloaded and setup MySQL 5.5.8 community server for Windows (32bit Installer) from MySQL official downloads. After downloading while I tried connecting it with HeidiSQL 5.x I got following error "Problem while Creating new User (SQL Error (1364): Field 'authentication_string' doesn't have a default value)".
It seems like it's a problem with HeidiSQL-5, probably due to the fact that table schema for mysql.users table might be changed in MySQL 5.5.8. You might see something like following if you're using HeidiSQL-5.
MySQL5.5 > Create User error while using HeidiSQL 5.0
A) Download HeidiSQL 6.0 and try creating user again.
B) Go to "users" table in "mysql" db and change the definition of table, alter table structure and allow null value for "authentication_string" field.
ALTER TABLE `user` CHANGE COLUMN `authentication_string` `authentication_string` TEXT NULL COLLATE 'utf8_bin' AFTER `plugin`;
Ideally one should change the query if they are writing SQL statement to create user manually, but when we are using such tools it's not possible to do that and not logical either.
If you have encountered any bumps on the road while upgrading to MySQL 5.5, please do share with us.
#1 by Melvin Bradley on January 20, 2011 - 3:48 am
Quote
Works great thanks for the write
#2 by Travis Torgerson on February 23, 2011 - 1:43 am
Quote
Agree with Melvin but will also add that the fix above also works to resolve errors adding users when using the MySQL Admin GUI Tools. When adding a user via the MySQL Admin tool, it would throw an error stating that the user may have been deleted from the database and to refresh the user pane. This fixes that.
#3 by Prabhat Singh on February 23, 2011 - 8:13 pm
Quote
Thank you for your help. I used the second approach to get past configuring MySQL to have root access from remote machine. Here is what I did:
From command line, log in mysql (mysql -h localhost -u root -p<password>)>)
mysql> use mysql
mysql> ALTER TABLE `user` CHANGE COLUMN `authentication_string` `authentication_string` TEXT NULL COLLATE 'utf8_bin' AFTER `plugin`;
This operation should succeed.
Configure MySQL again (with root access allowed for remote machine checked) and the operation should succeed this time.
#4 by pipi772 on March 4, 2011 - 6:22 pm
Quote
again, works great!