MySQL 5.5.8 – Problem while Creating new User (SQL Error (1364): Field ‘authentication_string’ doesn’t have a default value)
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.
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.