[MDEV-32209] mariadb cannot import 10.3 or older mysqldump-produced dumps anymore Created: 2023-09-20  Updated: 2023-09-22

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

Type: Bug Priority: Minor
Reporter: Rick Pizzi Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 1
Labels: regression

Issue Links:
Problem/Incident
is caused by MDEV-17658 change the structure of mysql.user table Closed

 Description   

It appears that the change in mysql.user table from table to view has broken the ability to restore older dumps produced (for example) with MariaDB 10.3 or older.

The 10.3 dump will try to drop table user, but in 10.5 and later you cannot drop a view with "drop table" anymore (not sure when this changed).

Hence in 10.5 you get:

MariaDB [mysql]> DROP TABLE IF EXISTS `user`;
 
Query OK, 0 rows affected, 1 warning (0.000 sec)
 
MariaDB [mysql]> show warnings;
 
+-------+------+------------------------+
 
| Level | Code | Message                |
 
+-------+------+------------------------+
 
| Note  | 1965 | 'mysql.user' is a view |
 
+-------+------+------------------------+
 
1 row in set (0.000 sec)

But in the 10.3 dump you have:

--
 
-- 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` (
 
  `Host` char(60) NOT NULL DEFAULT '',
 
  `User` char(80) NOT NULL DEFAULT '',
 
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
 
  `Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
 
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
 
  `Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
 
[ ... ]

I would recommend we revert the change that disabled "drop table" for view, in the sake of compatibility.

Thanks,
Rick

 Comments   
Comment by Rick Pizzi [ 2023-09-20 ]

I guess it's not just a matter of reverting the drop table thing, as of course we need a view there and not a table...
Perhaps mysql_upgrade will fix that?

Comment by Marko Mäkelä [ 2023-09-21 ]

MDEV-17658 in MariaDB Server 10.4.1 changed the system table mysql.user to a view.

Comment by Rick Pizzi [ 2023-09-22 ]

A possible solution would be to add the following to the output of mysqldump, right after the "DROP TABLE IF EXISTS `user`:

DROP VIEW IF EXISTS `user`;

Comment by Sergei Golubchik [ 2023-09-22 ]

we can hardly change mysqldump of 10.3 so that it would have been adding DROP VIEW IF EXISTS `user`; to the dumps it has been creating over the last five years.

I don't see what we can do here, a user can employ the workaround you provided.

Comment by Rick Pizzi [ 2023-09-22 ]

Right, so we should allow DROP TABLE to drop views as well (by the way, the fact it currently doesn't allow that, should raise an error and not a warning, IMHO)
Then, after the old dump is imported, mysql_upgrade -s will re-convert the schema to the current version.
Just tested and it works fine.

Thanks,
Rick

Generated at Thu Feb 08 10:29:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.