Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.5.2
-
None
Description
Restoring mariadb-dump produce error like follow output, lack of IF NOT EXIST. tested with or without --system parameter
--------------
|
INSTALL PLUGIN DISKS SONAME 'disks.so'
|
--------------
|
|
|
ERROR 1968 (HY000) at line 58036: Plugin 'DISKS' already installed
|
-- Retrieving rows...
|
-- Retrieving table structure for table table_stats...
|
--------------
|
INSTALL PLUGIN SQL_ERROR_LOG SONAME 'sql_errlog.so'
|
--------------
|
|
|
ERROR 1968 (HY000) at line 58038: Plugin 'SQL_ERROR_LOG' already installed
|
--------------
|
CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
|
--------------
|
|
|
ERROR 1396 (HY000) at line 58039: Operation CREATE USER failed for 'mariadb.sys'@'localhost'
|
-- Sending SELECT query...
|
-- Retrieving rows...
|
--------------
|
CREATE USER `root`@`localhost` IDENTIFIED BY PASSWORD '*0CB2A44D612CC2B6BFF24691BDE47A357CCF547A'
|
--------------
|
|
|
ERROR 1396 (HY000) at line 58040: Operation CREATE USER failed for 'root'@'localhost'
|
--------------
|
CREATE USER `root`@`%` IDENTIFIED BY PASSWORD '*0CB2A44D612CC2B6BFF24691BDE47A357CCF547A'
|
--------------
|
|
|
ERROR 1396 (HY000) at line 58041: Operation CREATE USER failed for 'root'@'%'
|
-- Retrieving table structure for table innodb_index_stats...
|
--------------
|
CREATE USER `healthcheck`@`127.0.0.1` IDENTIFIED BY PASSWORD '*DC3949278F3CEB2C569EB2BDF2B87AF43891D187'
|
--------------
|
|
|
ERROR 1396 (HY000) at line 58042: Operation CREATE USER failed for 'healthcheck'@'127.0.0.1'
|
--------------
|
CREATE USER `healthcheck`@`::1` IDENTIFIED BY PASSWORD '*DC3949278F3CEB2C569EB2BDF2B87AF43891D187'
|
--------------
|
|
|
ERROR 1396 (HY000) at line 58043: Operation CREATE USER failed for 'healthcheck'@'::1'
|
-- Sending SELECT query...
|
--------------
|
CREATE USER `healthcheck`@`localhost` IDENTIFIED BY PASSWORD '*DC3949278F3CEB2C569EB2BDF2B87AF43891D187'
|
--------------
|
|
|
ERROR 1396 (HY000) at line 58044: Operation CREATE USER failed for 'healthcheck'@'localhost'
|
|
Attachments
Activity
Regarding full restore --all-databases, proposed workaround can have a performance impact but more for a tooling like ours breaking on on real user data restore issue instead of hiding it is important , those system object error are of a different nature may be can just trigger a warnings for attention
As danblack wrote, you need to use --insert-ignore if you want to restore the dump where some of the objects might already exists. With --insert-ignore the dump will have
INSTALL PLUGIN /*M!100401 IF NOT EXISTS */ |
Just noting that candidate slaves instances are provision empty , such users are from debian install. --insert-ignore fixed it, but was it always like that using mysql.user data dump , I can't remenber beeing forced to use insert-ignore for global dump restore on empty node, may be getting to old now 
But i sill remember a couple of time breaking that debian user password
I still think it would be a good idea that any local system object could be dump with /*M!100401 without forcing --insert-ignore on DML on an empty server , may be with a option --system-restore=preserve|replace,
Users can be made unique per instance like in debian , configuration can diverge before provision because you may wan't to affect some role to a slave like testing an engine or more or less login capabilities
Note the options --insert-ignore and --replace will generate code that preserves existing or replaces the --system objects.