Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.12
Description
MariaDB 10.4 can't import a database dump that was created by MariaDB < 10.4 and reports message: "ERROR 1050 (42S01) at line 804: Table 'user' already exists"
The problem seems to be caused by changes in MariaDB 10.4 where mysql.global_priv table has replaced the mysql.user table, and mysql.user is now a view. So when you create a dump of the DB in 10.4, it drops both table and view before "CREATE TABLE `user`":
MariaDB 10.4 dump
--
|
-- Temporary table structure for view `user`
|
--
|
|
|
DROP TABLE IF EXISTS `user`;
|
/*!50001 DROP VIEW IF EXISTS `user`*/;
|
SET @saved_cs_client = @@character_set_client;
|
SET character_set_client = utf8;
|
/*!50001 CREATE TABLE `user` (
|
...
|
But when you create a dump e.g. on MariaDB 10.2 (tested with 10.2.31), only a table is dropped:
MariaDB 10.2 dump:
--
|
-- Table structure for table `user`
|
--
|
|
|
DROP TABLE IF EXISTS `user`;
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
/*!40101 SET character_set_client = utf8 */;
|
CREATE TABLE `user` (
|
When a user wants to restore 10.2 dump with MariaDB 10.4, the view is not dropped and MariaDB reports "Table 'user' already exists" error. If the "DROP VIEW IF EXISTS" statement is added to the dump, then it's restored without any problems.
Attachments
Issue Links
- relates to
-
MDEV-23630 mysqldump to logically dump system tables
-
- Closed
-
Activity
MDEV-23630 has added a new option, --system=all/users/.... In this mode mysqldump will use CREATE USER and GRANT statements instead of INSERT, so it'll work for any MariaDB version.
mysqldump --system=all does not work on an already initialized database so I think that's not really usable. A dump created with this option also create the user root@localhost that has already been added by mysql_install_db.
While migrating from a server with:
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
to another server with:
mysql Ver 15.1 Distrib 10.5.22-MariaDB, for Linux (x86_64) using EditLine wrapper
At issue was import followed by `myssql_upgrade` does not import users. As the OP's solution wasn't clear (to me), in case someone else runs across this thead, the edit that worked (for me) looked like:
--- 2024-06-27_backup.sql 2024-06-27 12:14:41.000000000 -0500
|
+++ 2024-06-27_edited.sql 2024-06-28 18:58:56.130912396 -0500
|
@@ -10670,7 +10670,8 @@
|
-- Table structure for table `user`
|
--
|
|
|
-DROP TABLE IF EXISTS `user`;
|
+DROP TABLE IF EXISTS `global_priv`;
|
+DROP VIEW IF EXISTS `user`;
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
/*!40101 SET character_set_client = utf8 */;
|
CREATE TABLE `user` (
|
It also is essential to run mysql_upgrade before opening the imported data as it corrupts otherwise.
--add-drop-database is affected by https://bugs.mysql.com/bug.php?id=69970