Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Reading the mysql.user view fails with error 1356

ErrorQueries on the mysql.user view fail because the server considers the view invalid (error 1356)

Short answer

Since MariaDB 10.4, mysql.user is only a compatibility view over mysql.global_priv. Error 1356 means that view cannot run against the tables under it, which happens when the system tables come from another version or from MySQL. Back up, then run mariadb-upgrade so the mysql schema matches the server binary.

  • Applies to MariaDB 10.11 and 11.4 LTS, mariadb-upgrade 11.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask the server who owns the view and what it selects from. The definer and the underlying table tell you whether the mysql schema is the right version.

    SHOW CREATE VIEW mysql.user\G
    SELECT VERSION();
  2. 2Match what you see, then jump to its fix

    1. 1The error starts right after a major version upgrade or after restoring the data directory from an older serverThe mysql system tables are older than the serverFix
    2. 2mysql.global_priv does not exist, or the server was swapped in place of MySQLThe data directory came from MySQLFix

What does the error look like?

  • Queries on the mysql.user view fail because the server considers the view invalid (error 1356)

Cause 1 of 2

The mysql system tables are older than the server

1Check

The server logs a recommendation to upgrade when the system tables are older. Read the startup lines of the error log.

sudo journalctl -u mariadb.service | grep -i upgrade

2Fix

Take a full backup, then run mariadb-upgrade. It updates the system tables, recreates missing mysql tables and checks the others. The mysql_upgrade name still works.

sudo mariadb-dump --all-databases > all-databases.sql
sudo mariadb-upgrade

3Verify

The view can be read again.

SELECT user, host FROM mysql.user LIMIT 3;

Confirmed by mariadb.com

Did this fix it?

Cause 2 of 2

The data directory came from MySQL

1Check

MariaDB keeps account data in mysql.global_priv and exposes mysql.user as a view over it. A MySQL data directory has a real mysql.user table instead.

SHOW TABLES FROM mysql LIKE 'global_priv';

2Fix

After migrating from MySQL, run mariadb-upgrade. Do it on a copy first, and prefer a logical dump and restore when moving between the two products.

sudo mariadb-upgrade

3Verify

mysql.global_priv exists and the view works.

SHOW TABLES FROM mysql LIKE 'global_priv';
SELECT COUNT(*) FROM mysql.user;

Confirmed by mariadb.com

Did this fix it?

How do I stop it coming back?

  • Run mariadb-upgrade after every major upgrade on systems that do not run it for you (Debian and Ubuntu packages do).
  • Read accounts through information_schema or SHOW GRANTS, not by editing mysql.user; use GRANT and CREATE USER.
  • Back up all databases before any 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.