[MDEV-26305] Restore dump break mysql system tables Created: 2021-08-04  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.4.20
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Daniel Black
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Restoring dump let system tables fully broken

The dump was taken using --hex-blob --single-transaction --verbose --all-databases --add-drop-database --system=all

the dump in my case was failing restoring at
ERROR 1146 (42S02) at line 580: Table 'mysql.plugin' doesn't exist 

USE `world`;                                                                                                                                             
INSTALL PLUGIN DISKS  SONAME 'disks.so';                                                                                                                 
INSTALL PLUGIN Mroonga  SONAME 'ha_mroonga.so';
 
INSTALL PLUGIN Mroonga_stats  SONAME 'ha_mroonga.so';
 
INSTALL PLUGIN SQL_ERROR_LOG  SONAME 'sql_errlog.so';
 
CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE;

The cause is easy some tables are recreated but the ones that contains system table objects that may be recreated as SQL COMMAND are not but the dump do drop the entire mysql schema 

 
 
/*!40000 DROP DATABASE IF EXISTS `mysql`*/;
 
 
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
 
 
 
USE `mysql`;


Generated at Thu Feb 08 09:44:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.