Tag Archives: DB

[Solved]Error 1054 (42s22): unknown column ‘password’ in ‘field list’ how to modify the password

1. Update list apt get

sudo apt-get update

2. Install MySQL server and client

sudo apt-get install mysql-server mysql-client

3. When installing the server, if there is no default password, there is no password. Enter the following command to enter mysql

mysql -u root -p

4. Modify the root password. Note that the password field does not exist and needs to be modified to authentication_ String to modify

use mysql;
update mysql.user set authentication_string=password('root') where user='root' and Host ='localhost';
update user set plugin="mysql_native_password"; 
flush privileges;
quit;

5. Set the encoding, modify the/etc/MySQL/my.cnf file, and add the following contents:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

6. Enter Mysql to view the status (whether the code is utf8)

status or \s

7. Start and close MySQL service

open:service mysql start
close:service mysql stop
restart:service mysql restart

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)