CODEWITHSUNDEEP
mysqlubuntuphpmyadmin
Ceneezer
Ceneezer

Reputation: 81

ERROR 1728 (HY000): Cannot load from mysql.user. The table is probably corrupted

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 full apt-get upgrade dist and rebooted (followed by another mysql_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 accounts

At 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

Upvotes: 4

Views: 12226

Answers (1)

Ceneezer
Ceneezer

Reputation: 81

Even after re-running mysql_upgrade, the columns of user are still in the wrong order.

My (inelegant) solution was to:

  1. Export users
  2. Rearrange the table column definitions (swap password_expired with password_last_changed)
  3. Import as user2 (just to be sure)
  4. DROP TABLE user; (oh my!)
  5. Copy user2 to user (thankfully it worked!)

Upvotes: 4

Related Questions