ERROR 1728 (HY000): Cannot load from mysql.user. The table is probably corrupted
Even after re-running mysql_upgrade
, the columns of user are still in the wrong order.
My (inelegant) solution was to:
- Export users
- Rearrange the table column definitions (swap
password_expired
withpassword_last_changed
) - Import as
user2
(just to be sure) DROP TABLE user;
(oh my!)- Copy
user2
to user (thankfully it worked!)
Ceneezer
Updated on June 04, 2022Comments
-
Ceneezer almost 2 years
symptom: I just tried to create a user, as I have done many times in the past, only to receive this error:
ERROR 1728 (HY000): Cannot load from mysql.user. The table is probably corrupted
cause: The only things I've done that I can think of since I last created a user through phpMyAdmin that may have caused this would be apt-get upgrades. As far as I can tell all user accounts work fine (testing all would be a long process) and other databases are able to read/write/etc fine
troubleshooting:
upgrade: I've already tried
mysql_upgrade
which both reports that table (and all others) as OK, and required the--force
option. I've done a fullapt-get upgrade dist
and rebooted (followed by anothermysql_upgrade
- hey why not) and the issue persists.permissions: I've verified the permissions in all files south of
/var/lib/mysql
are mysql:root rw:rw:-- (which were originally mysql group too but only visible by user) including/var/lib/mysql-files
(empty)/var/lib/mysql-keyring
(one file) and/var/lib/mysql-upgrade
(one file)users: I have two root users (different name, nearly identical privileges) and both show up with full access to mysql.user (which shows and is able to quickedit fine - though the full edit is missing the BLOB files, could this be the problem?) through phpMyAdmin.
create user methods: I get the same error when using
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
as when trying through phpMyAdmin - through both my root accountsAt this point I'm stack-overflow shy of re-installing MySQL or perhaps even Ubuntu, and the closest articles I see are for mysql.proc (which haven't worked)
update logs show:
[Warning] Incorrect definition of table mysql.user: expected column 'password_expired' at position 41, found 'password_last_changed'.
[ERROR] /usr/sbin/mysqld: Cannot load from mysql.user. The table is probably corrupted