[icinga-checkins] icinga.org: icingaweb2-module-director/master: upgrade_126: re-order queries

git at icinga.org git at icinga.org
Thu Dec 15 16:22:47 CET 2016


Module: icingaweb2-module-director
Branch: master
Commit: e4525a09df0fd3749259fd1812c780e8a4417263
URL:    https://git.icinga.org/?p=icingaweb2-module-director.git;a=commit;h=e4525a09df0fd3749259fd1812c780e8a4417263

Author: Thomas Gelf <thomas at gelf.net>
Date:   Thu Dec 15 16:21:49 2016 +0100

upgrade_126: re-order queries

...otherwise we might delete indexes before the related constraint in case
they are different

refs #13591

---

 schema/mysql-migrations/upgrade_126.sql |   78 ++++++++++++++++---------------
 1 file changed, 41 insertions(+), 37 deletions(-)

diff --git a/schema/mysql-migrations/upgrade_126.sql b/schema/mysql-migrations/upgrade_126.sql
index 3df3144..ef14078 100644
--- a/schema/mysql-migrations/upgrade_126.sql
+++ b/schema/mysql-migrations/upgrade_126.sql
@@ -15,15 +15,16 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET @stmt = NULL;
 
+
 SET @stmt = (SELECT IF(
     (SELECT EXISTS(
-        SELECT 1
-        FROM information_schema.statistics
-        WHERE table_schema = SCHEMA()
+        SELECT * FROM information_schema.table_constraints
+        WHERE
+            table_schema   = DATABASE()
             AND table_name = 'icinga_service_set'
-            AND index_name = 'icinga_service_set_host'
+            AND constraint_name = 'icinga_service_set_ibfk_1'
     )),
-    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_set_host',
+    'ALTER TABLE icinga_service_set DROP FOREIGN KEY icinga_service_set_ibfk_1',
     'SELECT 1'
 ));
 
@@ -39,9 +40,9 @@ SET @stmt = (SELECT IF(
         WHERE
             table_schema   = DATABASE()
             AND table_name = 'icinga_service_set'
-            AND constraint_name = 'icinga_service_set_ibfk_1'
+            AND constraint_name = 'icinga_service_set_ibfk_2'
     )),
-    'ALTER TABLE icinga_service_set DROP FOREIGN KEY icinga_service_set_ibfk_1',
+    'ALTER TABLE icinga_service_set DROP FOREIGN KEY icinga_service_set_ibfk_2',
     'SELECT 1'
 ));
 
@@ -50,15 +51,16 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET @stmt = NULL;
 
+
 SET @stmt = (SELECT IF(
     (SELECT EXISTS(
-        SELECT 1
-        FROM information_schema.statistics
-        WHERE table_schema = SCHEMA()
-            AND table_name = 'icinga_service_set'
-            AND index_name = 'icinga_service_set_ibfk_1'
+        SELECT * FROM information_schema.table_constraints
+        WHERE
+            table_schema   = DATABASE()
+            AND table_name = 'icinga_service'
+            AND constraint_name = 'icinga_service_service_set'
     )),
-    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_set_ibfk_1',
+    'ALTER TABLE icinga_service DROP FOREIGN KEY icinga_service_service_set',
     'SELECT 1'
 ));
 
@@ -73,10 +75,10 @@ SET @stmt = (SELECT IF(
         SELECT * FROM information_schema.table_constraints
         WHERE
             table_schema   = DATABASE()
-            AND table_name = 'icinga_service_set'
-            AND constraint_name = 'icinga_service_set_ibfk_2'
+            AND table_name = 'icinga_service'
+            AND constraint_name = 'icinga_service_ibfk_1'
     )),
-    'ALTER TABLE icinga_service_set DROP FOREIGN KEY icinga_service_set_ibfk_2',
+    'ALTER TABLE icinga_service DROP FOREIGN KEY icinga_service_ibfk_1',
     'SELECT 1'
 ));
 
@@ -85,15 +87,16 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET @stmt = NULL;
 
+
 SET @stmt = (SELECT IF(
     (SELECT EXISTS(
         SELECT 1
         FROM information_schema.statistics
         WHERE table_schema = SCHEMA()
-            AND table_name = 'icinga_service_set'
-            AND index_name = 'icinga_service_set_ibfk_2'
+              AND table_name = 'icinga_service'
+              AND index_name = 'icinga_service_service_set'
     )),
-    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_set_ibfk_2',
+    'ALTER TABLE icinga_service DROP INDEX icinga_service_service_set',
     'SELECT 1'
 ));
 
@@ -103,16 +106,15 @@ DEALLOCATE PREPARE stmt;
 SET @stmt = NULL;
 
 
-
 SET @stmt = (SELECT IF(
     (SELECT EXISTS(
-        SELECT * FROM information_schema.table_constraints
-        WHERE
-            table_schema   = DATABASE()
-            AND table_name = 'icinga_service'
-            AND constraint_name = 'icinga_service_service_set'
+        SELECT 1
+        FROM information_schema.statistics
+        WHERE table_schema = SCHEMA()
+              AND table_name = 'icinga_service_set'
+              AND index_name = 'icinga_service_set_ibfk_1'
     )),
-    'ALTER TABLE icinga_service DROP FOREIGN KEY icinga_service_service_set',
+    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_set_ibfk_1',
     'SELECT 1'
 ));
 
@@ -121,15 +123,16 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET @stmt = NULL;
 
+
 SET @stmt = (SELECT IF(
     (SELECT EXISTS(
         SELECT 1
         FROM information_schema.statistics
         WHERE table_schema = SCHEMA()
-            AND table_name = 'icinga_service'
-            AND index_name = 'icinga_service_service_set'
+              AND table_name = 'icinga_service_set'
+              AND index_name = 'icinga_service_set_host'
     )),
-    'ALTER TABLE icinga_service DROP INDEX icinga_service_service_set',
+    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_set_host',
     'SELECT 1'
 ));
 
@@ -141,13 +144,13 @@ SET @stmt = NULL;
 
 SET @stmt = (SELECT IF(
     (SELECT EXISTS(
-        SELECT * FROM information_schema.table_constraints
-        WHERE
-            table_schema   = DATABASE()
+        SELECT 1
+        FROM information_schema.statistics
+        WHERE table_schema = SCHEMA()
             AND table_name = 'icinga_service'
-            AND constraint_name = 'icinga_service_ibfk_1'
+            AND index_name = 'icinga_service_ibfk_1'
     )),
-    'ALTER TABLE icinga_service DROP FOREIGN KEY icinga_service_ibfk_1',
+    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_ibfk_1',
     'SELECT 1'
 ));
 
@@ -156,15 +159,16 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET @stmt = NULL;
 
+
 SET @stmt = (SELECT IF(
     (SELECT EXISTS(
         SELECT 1
         FROM information_schema.statistics
         WHERE table_schema = SCHEMA()
-            AND table_name = 'icinga_service'
-            AND index_name = 'icinga_service_ibfk_1'
+              AND table_name = 'icinga_service_set'
+              AND index_name = 'icinga_service_set_ibfk_2'
     )),
-    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_ibfk_1',
+    'ALTER TABLE icinga_service_set DROP INDEX icinga_service_set_ibfk_2',
     'SELECT 1'
 ));
 



More information about the icinga-checkins mailing list