MySQL 5.5.9 Installation Problem security settings could not be applied 1364 authentication_string

 

MySQL 5.5.9 Installation Problem

<>http://www.opensourcevarsity.com/mysqlbasics/mysqlinstall

Prior Installing MySQL on Local Windows<>

1. Disconnect the machine from any network (if it is on any Local Area Network), isolate the machine
2. Stop or Disable any antivirus setup running on the local machine ( which is why the machine must be isolated)
3. Set the Windows Firewall - OFF. Stop all windows security on the local machine.

Navigate to the folder where the MySQL .msi which is the MySQL installer exists.

Run the .msi to setup MySQL Server 5.5.9 by double clicking it.

For the installation steps, refer to tutorial MySQL Installation which is covered on this website.

Right at the very end of the install / configuration process, the following will happen:

1. Preparation configuration – successful
2. Configuration file written – successfully
3. The MySQL Server Instance service started – successfully

BUT

• When applying security settings the configuration process will fail

NOTE: There is a bug in Version 5.8.8 that prevents security application to the MySQL instance.

The following window will appear during the MySQL 5.5.9 installation process as shown in Diagram 1.

The Error Message is as shown in diagram 1.

“The security settings could not be applied to the database because the connection has failed with following error. Error Nr:1364Field ‘authentication_string’ doesn’t have a default value…” message will display as shown in Diagram 1.

Follow these simple steps to successfully overcome this issue.

Click on Skip to close this window.

 

Diagram 1

The MySQL Server Instance Configuration wizard opens as shown in Diagram 2.

Security could not be applied. Error Number 1364. Field ‘authentication_string’ doesn’t have a default value message is displayed.

Red color cross in the circle indicates the failure of complete installation of MySQL as shown in Diagram 2.

Click on cancel to stop the installation procedure as shown in Diagram 2.

Diagram 2

At the desktop Click > Start > Run as shown in Diagram 3

Diagram 3

In the run text-box enter the command cmd as shown in Diagram 4.

Diagram 4

This opens a Command Window with a Command Prompt visible.

At the Command Prompt enter the following mysql –uroot to log in to MySQL as shown in Diagram 5.

Diagram 5

Before working with data held within a table it is necessary to inform the MySQL db engine which database holds the tables. The USE command specifies which database the MySQL db engine should set as active.

Use the mysql database as shown in Diagram 7.

mysql> USE mysql;
Database changed

Diagram 7

Remove the NOT NULL from the authentication_string field in table user by passing following command at the mysql prompt as shown in Diagram 8.

mysql>ALTER TABLE `mysql`.`user` CHANGE `authentication_string` `authentication_string` TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL ;

Diagram 8

Quit the command line as shown in Diagram 9.

mysql>exit;
Bye

Diagram 9

Now you rerun the Instance Configuration wizard by navigating to Start > All Programs > MySQL > MySQL Server 5.5 > MySQL Instance Configuration Wizard as shown in Diagram 10.

Diagram 10

The MySQL Instance Configuration Wizard opens as shown in Diagram 11.

Click on Next to continue with the configuration of MySQL Server 5.5 server instance

Diagram 11

Select the maintenance option as shown in Diagram12.

Select Reconfigure Instance option to create a new configuration for the instance.

Click on Next to continue with the configuration wizard process as shown in Diagram 12.

Diagram 12

From the MySql Server Instance Configuration Wizard select Standard Configuration and click on Next as shown in Diagram 13.

Diagram 13

Check the checkbox of Include the Bin Directory in Windows PATH and click on Next as shown in Diagram 14.

Diagram 14

Now Configure the MySQL Server instance with your choice of root password as shown in Diagram 11

Current root password: ????????? ( provided earlier while installing MySQL )
Enter New Root Password : ????????? ( your choice )
Enter Confirm : ???????? ( your choice )
Check the check-box of Enable root access from remote machines.
Check the check-box of Create Anonymous Account.

Click on Next as shown in Diagram 15.

Diagram 15

The security settings would now successfully be applied to MySQL as shown in Diagram 16.

Diagram 16

MySQL 5.5.9 will now successfully be installed on your system.