Azure MariaDB - DROP TABLE IF EXISTS ``; /*!50001 DROP VIEW IF EXISTS `` */;

user_18431665 0
Viewed 169

I'm running into an error when trying to restore a backup of MariaDB

ERROR 1005 (HY000) at line 1488: Can't create table `pactbroker`.`latest_tagged_pact_consumer_version_orders` (errno: 1 "Operation not permitted")

when I'm looking at the 1488 line I see

1484 DROP TABLE IF EXISTS `latest_tagged_pact_consumer_version_orders`;
1485 /*!50001 DROP VIEW IF EXISTS `latest_tagged_pact_consumer_version_orders`*/;
1486 SET @saved_cs_client     = @@character_set_client;
1487 SET character_set_client = utf8;
1488 /*!50001 CREATE TABLE `latest_tagged_pact_consumer_version_orders` (
1489   `provider_id` tinyint NOT NULL,
1490   `consumer_id` tinyint NOT NULL,
1491   `tag_name` tinyint NOT NULL,
1492   `latest_consumer_version_order` tinyint NOT NULL
1493 ) ENGINE=MyISAM */;
1494 SET character_set_client = @saved_cs_client;

To be honest, didn't get what is the problem.

mariadb mysql
1 Answers

First of all the /*!50001 ... */ syntax is called EXECUTABLE COMMENT. the 5 digits number is the MariaDB version. In your case, it means that if MariaDB version is 5.0.0.0.1 then the code between /* and */ will be executed otherwise not.

I'm surprised to see the DROP VIEW statement after the related DROP TABLE statement, when it should be before the DROP TABLE (you cannot DROP a VIEW on TABLE which doesn't exists anymore), or absent as the DROP VIEW is not needed anymore because the DROP TABLE drops the associated VIEWS.

Assuming, that before version 5.0.0.0.1 the DROP TABLE was not dropping the associated views (otherwise, why having an EXECUTABLE COMMENT, dropping the view if MariaDB is 5.0.0.0.1), then the DROP VIEW statement should be executed before the DROP TABLE.

Assuming you are running MariaDB 5.0.0.0.1 (not supported anymore), it would mean the view is not dropped, because the DROP VIEW statement is misplaced.

Despite, my surprise, the error code you're getting is related to a foreign key constraint issue, which may come from a bug in 5.0.0.0.1 version when you drop a table with existing view, and then recreate the table when the view has not been dropped.

I'm doing a lot of assumptions because 5.0.0.0.1 has been deployed before 2009, and even I'm "young" enough to have worked with this version, my memory has not correctly indexed the detailed and exact behavior of the DROP TABLE statement applied to views.

I will be curious to know what happens in the lines before 1484 and after 1494 and get the detailed errorlog content, allowing me to move forward from assumptions to certitudes.

Many thanks

user_140710250
Related