MariaDB 10.5执行DELETE删除用户后查询mysql.user视图报1356错误的解决求助

I've run into this exact issue before—directly modifying the mysql.user system view (or its underlying tables) instead of using DROP USER can break the view's references, leading to that frustrating 1356 error. Here are two reliable solutions to get your system back on track:

Solution 1: Use mysql_upgrade to Repair System Tables #

This is the simplest method, as mysql_upgrade is designed to fix corrupted system views and tables after misoperations:

• Stop the MariaDB service first:

  sudo systemctl stop mariadb
  

• Start MariaDB with grant tables skipped (so you can log in without permissions):

  sudo mysqld_safe --skip-grant-tables &
  

  Note: If your system uses mariadbd instead of mysqld, replace the command with sudo mariadbd-safe --skip-grant-tables &
• Log into MariaDB without a password:

  mysql -u root
  

• Run mysql_upgrade to repair system tables and views:

  mysql_upgrade -u root
  

• Restart MariaDB normally:

  sudo systemctl restart mariadb
  

• Log back in and verify the fix by running:

  SELECT * FROM mysql.user;
  

Solution 2: Manually Recreate the mysql.user View #

If mysql_upgrade doesn't resolve the issue, you can manually recreate the view using the correct definition for your MariaDB version (1:10.5.15-0+deb11u1):

• Follow steps 1-3 from Solution 1 to start MariaDB with skipped grant tables and log in.
• Switch to the mysql database and recreate the view:

  USE mysql;
  CREATE OR REPLACE VIEW `user` AS
  SELECT
    `global_priv`.`Host` AS `Host`,
    `global_priv`.`User` AS `User`,
    `global_priv`.`Password` AS `Password`,
    `global_priv`.`Select_priv` AS `Select_priv`,
    `global_priv`.`Insert_priv` AS `Insert_priv`,
    `global_priv`.`Update_priv` AS `Update_priv`,
    `global_priv`.`Delete_priv` AS `Delete_priv`,
    `global_priv`.`Create_priv` AS `Create_priv`,
    `global_priv`.`Drop_priv` AS `Drop_priv`,
    `global_priv`.`Reload_priv` AS `Reload_priv`,
    `global_priv`.`Shutdown_priv` AS `Shutdown_priv`,
    `global_priv`.`Process_priv` AS `Process_priv`,
    `global_priv`.`File_priv` AS `File_priv`,
    `global_priv`.`Grant_priv` AS `Grant_priv`,
    `global_priv`.`References_priv` AS `References_priv`,
    `global_priv`.`Index_priv` AS `Index_priv`,
    `global_priv`.`Alter_priv` AS `Alter_priv`,
    `global_priv`.`Show_db_priv` AS `Show_db_priv`,
    `global_priv`.`Super_priv` AS `Super_priv`,
    `global_priv`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,
    `global_priv`.`Lock_tables_priv` AS `Lock_tables_priv`,
    `global_priv`.`Execute_priv` AS `Execute_priv`,
    `global_priv`.`Repl_slave_priv` AS `Repl_slave_priv`,
    `global_priv`.`Repl_client_priv` AS `Repl_client_priv`,
    `global_priv`.`Create_view_priv` AS `Create_view_priv`,
    `global_priv`.`Show_view_priv` AS `Show_view_priv`,
    `global_priv`.`Create_routine_priv` AS `Create_routine_priv`,
    `global_priv`.`Alter_routine_priv` AS `Alter_routine_priv`,
    `global_priv`.`Create_user_priv` AS `Create_user_priv`,
    `global_priv`.`Event_priv` AS `Event_priv`,
    `global_priv`.`Trigger_priv` AS `Trigger_priv`,
    `global_priv`.`Create_tablespace_priv` AS `Create_tablespace_priv`,
    `global_priv`.`ssl_type` AS `ssl_type`,
    `global_priv`.`ssl_cipher` AS `ssl_cipher`,
    `global_priv`.`x509_issuer` AS `x509_issuer`,
    `global_priv`.`x509_subject` AS `x509_subject`,
    `global_priv`.`max_questions` AS `max_questions`,
    `global_priv`.`max_updates` AS `max_updates`,
    `global_priv`.`max_connections` AS `max_connections`,
    `global_priv`.`max_user_connections` AS `max_user_connections`,
    `global_priv`.`plugin` AS `plugin`,
    `global_priv`.`authentication_string` AS `authentication_string`,
    `global_priv`.`password_expired` AS `password_expired`,
    `global_priv`.`password_last_changed` AS `password_last_changed`,
    `global_priv`.`password_lifetime` AS `password_lifetime`,
    `global_priv`.`account_locked` AS `account_locked`,
    `global_priv`.`Create_role_priv` AS `Create_role_priv`,
    `global_priv`.`Drop_role_priv` AS `Drop_role_priv`,
    `global_priv`.`Password_reuse_history` AS `Password_reuse_history`,
    `global_priv`.`Password_reuse_time` AS `Password_reuse_time`,
    `global_priv`.`Password_require_current` AS `Password_require_current`,
    `global_priv`.`User_attributes` AS `User_attributes`
  FROM `global_priv`
  WHERE (`global_priv`.`User` <> '' OR `global_priv`.`Host` <> '');
  

• Flush privileges and exit:

  FLUSH PRIVILEGES;
  EXIT;
  

• Restart MariaDB and verify the fix as before.

Important Note for Future Operations #

Always use DROP USER 'username'@'host'; to remove users in MariaDB. Starting from version 10.4, mysql.user is a system view (not a physical table), so direct DELETE operations on it can corrupt dependencies between the view and its underlying tables like global_priv.

内容的提问来源于stack exchange，提问作者user2194805