Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL 8: Column count of mysql.user is wrong, table probably corrupted

ErrorColumn count of mysql.user is wrong. Expected 42, found 44. The table is probably corrupted

Short answer

The mysql.user table is rarely damaged. The server binary and the data directory are on different versions. Either the data directory came from a newer server than the one now running (a downgrade), or the upgrade of system tables was skipped. Start the matching newer version, or restore from a dump into a fresh directory.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Compare the version of the binary that starts with the version the data directory was last used by. The error log from the last start shows the upgrade steps that ran.

    mysqld --version
    sudo grep -iE "upgrade|version" /var/log/mysql/error.log | tail -n 30
  2. 2Match what you see, then jump to its fix

    1. 1Expected is a smaller number than found, for example Expected 42, found 44The data directory was used by a newer server (in-place downgrade)Fix
    2. 2The server config or Docker command contains --upgrade=MINIMAL, and the error followed a version upgradeThe server was started with the system-table upgrade turned offFix

What does the error look like?

  • Column count of mysql.user is wrong. Expected 42, found 44. The table is probably corrupted

Cause 1 of 2

The data directory was used by a newer server (in-place downgrade)

1Check

The server expects fewer columns than the table has, so the table was written by a newer version. Package rollbacks and a changed Docker image tag cause this.

mysqld --version
sudo grep -i 'starting' /var/log/mysql/error.log | tail -n 5

2Fix

Go back to the newer version that wrote the data. In-place downgrade across series is not supported. To move to an older series, make a logical dump on the newer server and load it into a fresh data directory of the older one.

# on the newer server
mysqldump --all-databases --routines --events --triggers > backup.sql
# on the older server, with an empty data directory
mysql < backup.sql

3Verify

Start the server and read the user table.

mysql -e 'SELECT user, host FROM mysql.user LIMIT 5'

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

The server was started with the system-table upgrade turned off

1Check

The upgrade of the system tables in the mysql schema is step 2 of the server upgrade. MINIMAL runs only step 1 (the data dictionary), so the tables keep their old columns.

grep -rn 'upgrade' /etc/mysql/ 2>/dev/null

2Fix

Remove the option so the default AUTO value is used, or start once with --upgrade=FORCE to run step 2 even if the server thinks it is not needed. Expect a slower start.

# my.cnf, remove this line:
# upgrade=MINIMAL
# or, for one start:
sudo mysqld --upgrade=FORCE

3Verify

After the start, the log shows the upgrade finishing, and the table can be read.

mysql -e 'SELECT COUNT(*) FROM mysql.user'

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Pin the exact MySQL image tag or package version, and never roll the server back over a data directory that a newer one has used.
  • Take a logical dump before a major upgrade so a rollback is a restore, not an in-place downgrade.
  • Let the server run its own upgrade steps: do not start it with the upgrade option set to NONE or MINIMAL on a version change.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.