Error 1364 (HY000): field ‘SSL_ cipher’ doesn’t have a default value

When creating a user in mysql today, you can see how to insert data directly into the User table when someone is using it, as follows:

mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));

So I also tried to create users with this method in the database, but got the following error:

mysql> insert into mysql.user(Host,User,Password) values('localhost','siu',password('siu'));       
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

The reason for this error is that mysql’s default configuration is strict mode, which prohibits adding new users by directly modifying the USER table in the mysql library by INSERT.
solution is to modify my. Ini (Windows system) or my. Conf (Linux system) configuration file, taking Linux system as an example:

sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Modified to:

sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then restart the mysql service

service mysql restart

Create the user again and it will be successful

mysql> insert into mysql.user(Host,User,Password) values("localhost","joey",password("1234"));    
Query OK, 1 row affected, 3 warnings (0.00 sec)

It is important to note, however, that since mysql by default forbids this method to create users for database security, we should also avoid creating users by inserting. The correct way to create a user is:

mysql> create user 'joey'@'localhost' identified by 'joey';
Query OK, 0 rows affected (0.00 sec)

This user is then authorized for certain databases:

grant all privileges on joey.* to 'joey'@'localhost' identified by 'joey'; 
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


Read More: