Issue Restoring a site breaks VIEWS defined in MySQL

[carini]

Server operating system version

Plesk Obsidian v18.0.59_build1800240229.10

Plesk version and microupdate number

Ubuntu 20.04.6 LTS

After restoring a site through function Tools & Settings -> Backup Manager -> and then restore i find that the content of the original view is destroyed and also the permission is now set to "

DEFINER=`backup_AOjmJQR`@`%`

" thus preventing the right user to accessing or modifying the view.

MySQL [scibile-db3]> SHOW CREATE VIEW `dom_list`;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View     | Create View                                                                                                                                                                                                                                                         | character_set_client | collation_connection |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| dom_list | CREATE ALGORITHM=UNDEFINED DEFINER=`backup_AOjmJQR`@`%` SQL SECURITY DEFINER VIEW `dom_list` AS select 1 AS `changetype`,1 AS `changedate`, ...  | utf8                 | utf8mb4_general_ci   |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.001 sec)

It's just me or something similar happens also to other. Is using MySQL views legit under Plesk?

 

[carini]

Addendum:

Problem does not belong to restore, as find in backup dumps the view went corrupted during backup phase:

DROP TABLE IF EXISTS `dom_list`;
/*!50001 DROP VIEW IF EXISTS `dom_list`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `dom_list` AS SELECT
 1 AS `changetype`,
  1 AS `changedate`,
  1 AS `cname`,
[...]

I think that is a mayor problem because it means that 1+ year of backup can't be restore safely

Note: BTW the field

1 AS `changetype`,

IS NOT the original/correct statement.

 

[Kaspar]

Would you care to open a bug report for it (or open a support ticket for investigation on your server)?

 

[carini]

Plesk support replies in a way that, sincerely, leave me perplexed:

Support request Id #95245922:

Mysqldump does not dump the INFORMATION_SCHEMA database by default. As of MySQL 5.1.38, mysqldump dumps INFORMATION_SCHEMA if you name it explicitly on the command line, although currently, you must also use the --skip-lock-tables option. Before 5.1.38, mysqldump silently ignores INFORMATION_SCHEMA even if you name it explicitly on the command line

Although the reply is technically correct I'm quite dissatisfied. It means that any site using VIEW in his database can't be safely restored. The query used to create a view can be retrieved and saved by using "SHOW CREATE VIEW". backing up views should be possible without too much effort.

 

[carini]

Opened a Plesk suggestion on that topic