MySQL unknown column 'password_last_changed'

Question

When creating a user using this command:

create user 'foo'@localhost';

this error is shown:

ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user'

Using MySQL server version: 5.7.6

I tried to add it but I don't know its data-type How can I fix this?

Pother

Seems very .. odd if/since it is triggered by CREATE USER. Maybe try over on the DBA stack. (Could the default database schema itself failed a migration/update?) – Tollgate

dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html – Highball

Answer 1

Apparently you upgraded your MySQL to 5.7 from an earlier version.

Column 'password_last_changed' used to exist in MySQL <5.7, but was removed since 5.7

If this is the case, you have to run 'mysql_upgrade' script to migrate some tables from the old version to the new one.

run

mysql_upgrade -u root -p

and enter your root password.

then you should have your problem solved

Bathometer

Loved your answer, but I wasnt needed to restart the server, I guess I have not encounter any problem yet.. – Charged

For Windows systems, follow serverfault.com/a/538573/127106 to locate mysql_upgrade.exe. – Kemerovo

I removed the restart server step, found out it's not really necessary – Bathometer

Nice. I would only add you to restart your MySql server, so you are sure the changes are included, with sudo service mysql restart. Take a look at Ask Ubuntu Discussion – Depravity

Answer 2

I think there is a typo error in this statement - "Column 'password_last_changed' used to exist in MySQL <5.7, but was removed since 5.7"

When I checked, I noticed that the password_last_changed column is present in 5.7 and not in 5.6. In 5.6 the following error is thrown when we try to include this column in select query -

In 5.6 - Query: SELECT host, user, password_last_changed FROM mysql.user;

Error Code: 1054 Unknown column 'password_last_changed' in 'field list'

[ref-image][1]: https://i.sstatic.net/QwXcm.png

Everett