Bugzilla – Bug 1166786
mariadb in 15.2 cannot restore a dump made with 15.1
Last modified: 2022-09-20 10:01:47 UTC
Created attachment 832905 [details] dump file which cannot be restored How to reproduce: * install 15.1, "minimal server" * zypper in mariadb * rcmysql start * mysql_secure_installation # default answers to everyhting, set root password * create .my.cnf with root password * mysqldump --all-databases -pmysql > * zypper --releasever 15.2 ref * zypper --releasever 15.2 -v dup --no-r * reboot * rcmysql stop * rm -r /var/lib/mysql * rcmysql start * mysql_secure_installation * mysql -u root < /root/opensuse-15.1.sql Result: ERROR 1050 (42S01) at line 804: Table 'user' already exists
You are trying to load a dump that already contains the user table. This is automatically created when you start the server on an empty repository. You need to exclude it from the dump or the load. This would happen for any other migration done, including from 15.1 to 15.1 (same version) while deleting the data directory.
(In reply to Andreas Stieger from comment #1) > You are trying to load a dump that already contains the user table. This is > automatically created when you start the server on an empty repository. You > need to exclude it from the dump or the load. Presumably the database dump contains a DROP TABLE IF EXISTS `user` ? Looking at one of mine, it does.
15.1 can restore the exact same dump, 15.2 cannot This is the offending line 804 797 -- 798 -- Table structure for table `user` 799 -- 800 801 DROP TABLE IF EXISTS `user`; 802 /*!40101 SET @saved_cs_client = @@character_set_client */; 803 /*!40101 SET character_set_client = utf8 */; 804 CREATE TABLE `user` ( 805 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 806 `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', If this bug is indeed "invalid" then let's hope for SUSE support that SLES users don't get this update ;-)
(In reply to Per Jessen from comment #2) > (In reply to Andreas Stieger from comment #1) > > You are trying to load a dump that already contains the user table. This is > > automatically created when you start the server on an empty repository. You > > need to exclude it from the dump or the load. > > Presumably the database dump contains a DROP TABLE IF EXISTS `user` ? > Looking at one of mine, it does. I deliberately added the dump file so that interested people could test. I'm investigating the effort of switching to postgres now.
(In reply to Stefan Seyfried from comment #3) > If this bug is indeed "invalid" then let's hope for SUSE support that SLES > users don't get this update ;-) Oh well...
This seems to be a bug and I reported it upstream as https://jira.mariadb.org/browse/MDEV-22127 The problem is that in MariaDB 10.4 `user` table is a view that needs to be dropped as well before the table `user` is created. A quick workaround is to add "/*!50001 DROP VIEW IF EXISTS `user`*/;" before the CREATE TABLE `user` statement. Then the dump can be restored without any problems.
By adding `--add-drop-database` when calling mysqldump, the dump can be imported in MariaDB 10.4 and later versions. The optimal solution would be to automatically make the dump work without adding `-add-drop-database` and this is currenctly tracket by the upstream bug.However,, it has not received any work since it has been reported 1 year and a half ago and it has been marked as "Stalled".
I added '--add-drop-database' to the MySQL/MariaDB instructions in the SLE upgrade guide, see https://github.com/SUSE/doc-sle/commit/3641f69b60544d112e87fc59c3d26b75be76016c Any reasons to not do this?
Danilo, I've closed https://jira.suse.com/browse/DOCTEAM-681 now. If there is anything you want me to update the documentation – either remove '--add-drop-database' again or change something else – please set NEEDINFO for me.
(In reply to Christoph Wickert from comment #9) > Danilo, I've closed https://jira.suse.com/browse/DOCTEAM-681 now. If there > is anything you want me to update the documentation – either remove > '--add-drop-database' again or change something else – please set NEEDINFO > for me. Thank you Christoph for adding it and for the update.
The upstream bug hasn't seen any update in the last year and we already have a documented workaround. I am closing this bug as fixed.
*** Bug 1168493 has been marked as a duplicate of this bug. ***