Tag Archives: MySQL – ERROR 1054 (42S22)

[Solved] Mysql 5.7 Change Passwords ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’

1. Environment

Reinstalled the environment on the new server, which was 5.6, and upgraded to version 5.7.

2. Problems

A new installation of MySQL 5.7 prompted a password error when logging in. I didn't change the password when I installed it, but later changed it by logging in password-free.

Enter.

update mysql.user set password=password('root') where user='root'

Error message: ERROR 1054 (42S22): Unknown column 'password' in 'field list'

3. Solutions

There is no password field in MySQL 5.7 + database. The password field is changed to authentication_ string.

#Just change the change statement to the following

update mysql.user set authentication_ string=password(‘root’) where user=’root’ ;

#Refresh permissions

flush privileges;

[Solved] MySQL ERROR 1054 (42S22): Unknown column ‘i2goods.t_ebook_data.fbookid’ in ‘field list’

1.1.1 Phenomenon

When importing data, the following error occurs.

ERROR 1054 (42S22) at line 734: Unknown column ‘i2goods.t_ebook_data.fbookid’ in ‘field list’

1.1.2 Reason

The internet usually says that there is indeed a column not found, and after checking the imported SQL file, the error is a statement to create a view, the SQL statement is as follows.

VIEW `v_ebook_data` AS

select

`t_ebook_data`.`fbookid` AS `fbookid`,

`t_ebook_data`.`fperiodid` AS `fperiodid`,

1 AS `fdevicetype`,

`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,

`t_ebook_data`.`fformat` AS `fformat`,

`t_ebook_data`.`ffilepath` AS `ffilepath`,

`t_ebook_data`.`ffilesize` AS `ffilesize`,

`t_ebook_data`.`fdataversion` AS `fdataversion`,

`t_ebook_data`.`fdataid` AS `fdataid`

from `t_ebook_data`

where (`t_ebook_data`.`fdevicetype` = 1) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,1 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 1) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`)))))) union select `t_ebook_data`.`fbookid` AS `fbookid`,`t_ebook_data`.`fperiodid` AS `fperiodid`,2 AS `fdevicetype`,`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,`t_ebook_data`.`fformat` AS `fformat`,`t_ebook_data`.`ffilepath` AS `ffilepath`,`t_ebook_data`.`ffilesize` AS `ffilesize`,`t_ebook_data`.`fdataversion` AS `fdataversion`,`t_ebook_data`.`fdataid` AS `fdataid` from `t_ebook_data` where (`t_ebook_data`.`fdevicetype` = 2) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,2 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 2) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`)))))) union select `t_ebook_data`.`fbookid` AS `fbookid`,`t_ebook_data`.`fperiodid` AS `fperiodid`,3 AS `fdevicetype`,`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,`t_ebook_data`.`fformat` AS `fformat`,`t_ebook_data`.`ffilepath` AS `ffilepath`,`t_ebook_data`.`ffilesize` AS `ffilesize`,`t_ebook_data`.`fdataversion` AS `fdataversion`,`t_ebook_data`.`fdataid` AS `fdataid` from `t_ebook_data` where (`t_ebook_data`.`fdevicetype` = 3) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,3 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 3) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`))))))

 

When I opened the database with the tool and checked the exported table t_ebook_data, there was indeed the field fbookid, so the error message felt baffling.

Later, after careful analysis, I found that there is a prefix in front of the table name: ‘i2goods’, which is actually the name of the database, so I suspected that this is not the cause, and deleted all the ‘i2goods’ prefixes in the statement on the table, and the result was fine. The result was fine.

1.1.3 Solution

Remove all the database name prefixes from the create view statement, then perform the import operation, and it works.

In addition, to solve the following Got error: 1449 problem, after raising the privileges of the root user, you don’t need to remove the database name prefix in the above way, because when you look at the exported SQL file, all SQL statements in it have no database name prefix, so the import is fine.