Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL5.6 add root user error report: Field 'ssl_cipher' doesn't have a default value how to solve", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "MySQL5.6 add root user error report: how to solve Field' ssl_cipher' doesn't have a default value" bar!

2012.14.15

MySQL: 5.6.19

Just finished deploying the retail test environment, and now deal with the issues left over from last week.

Because the localhost and 127.0.0.1 entry information are indeed in the user table:

Mysql > select Host,User,Password from user where User=\ 'root\'

+-- +

| | Host | User | Password | |

+-- +

| | bidevedw\\ _ db | root | * D013A4E3A5BB01E4239D18D7E93B59B7D2B767AD |

| |:: 1 | root | * D013A4E3A5BB01E4239D18D7E93B59B7D2B767AD |

| |% | root | * D013A4E3A5BB01E4239D18D7E93B59B7D2B767AD |

+-

This causes me to parse root@'%' login when I log in locally on the server, but the root logged in in this way does not have grant permission, which makes it impossible to authorize other users.

* * 4. Row *

Host:%

User: root

Password: * D013A4E3A5BB01E4239D18D7E93B59B7D2B767AD

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

Plugin: mysql_native_password

Authentication_string:

Password_expired: N

Analysis of solution ideas:

Add the missing entries to the user table.

Because it was only in the past half a year that I really came into contact with MySQL. So many also ask du Niang, I also think that adding records to the user table is to use INSERT into. But this method will report an error:

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

This is because several columns in the user table are non-empty, and its default value is null.

| | ssl_cipher | blob | NO | | NULL |

| | x509_issuer | blob | NO | | NULL |

| | x509_subject | blob | NO | | NULL |

So the general insert into gives the first three columns. Ignoring the other columns. This approach seems to be available before 5.1. Mine is 5.6. It won't work this way.

Solution:

GRANT USAGE ON *. * TO 'root'@'localhost' IDENTIFIED BY' 123456 'WITH GRANT OPTION

GRANT USAGE ON *. * TO 'root'@'l127.0.0.1' IDENTIFIED BY' 123456 'WITH GRANT OPTION

Remember to refresh it: flush privileges

Log in to mysql-uroot-p123456 again

View the current login user and how to log in:

Mysql > select user ()

+-+

| | user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

So you don't have to go @'%'.

You can also modify permissions in the way root @'%':

Mysql > GRANT ALL PRIVILEGES ON *. * TO root@ "%" IDENTIFIED BY "123456" WITH GRANT OPTION

Query OK, 0 rows affected (0.02 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.03 sec)

When you have permission, it will display: Grant_priv: y

In this way, root users can do grant operations in @'% 'mode

Thank you for reading, the above is the "MySQL5.6 add root user error report: Field 'ssl_cipher' doesn't have a default value how to solve" the content, after the study of this article, I believe you MySQL5.6 add root user error report: Field' ssl_cipher' doesn't have a default value how to solve this problem has a deeper understanding, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.