From 6e1daa7e0a5de311a3dbd4923bea9fab4aa05b3e Mon Sep 17 00:00:00 2001
From: Banana <mail@bananas-playground.net>
Date: Sun, 17 Jan 2021 19:09:18 +0100
Subject: [PATCH] only one index creation per query. Implicit commit in mysql
 problem

---
 .../lib/managecollectionfields.class.php      | 57 +++++++++----------
 webclient/lib/managecollections.class.php     | 13 +++--
 2 files changed, 35 insertions(+), 35 deletions(-)

diff --git a/webclient/lib/managecollectionfields.class.php b/webclient/lib/managecollectionfields.class.php
index c4a90ce..ae091f2 100644
--- a/webclient/lib/managecollectionfields.class.php
+++ b/webclient/lib/managecollectionfields.class.php
@@ -196,52 +196,47 @@ class ManageCollectionFields {
 			if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStrInsertFields,true));
 
 			if(!empty($_newColumns)) {
-				$queryStrAlterEntry = "ALTER TABLE `".DB_PREFIX."_collection_entry_".$this->_collectionId."`";
+				$queryStrAlterEntry = array();
 				foreach($_newColumns as $k=>$v) {
-					$queryStrAlterEntry .= " ADD ".$v['createstring'].",";
+					$queryStrAlterEntry[] = "ALTER TABLE `".DB_PREFIX."_collection_entry_".$this->_collectionId."` ADD ".$v['createstring']."";
 				}
-				$queryStrAlterEntry = trim($queryStrAlterEntry, ",");
-				if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStrAlterEntry,true));
 			}
 
+			// this is not good. mysql implicit commit is triggered with alter table.
+			// needs a rewrite without alter table to fully use transactions..
 			try {
 				$this->_DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
 
-				if($this->_DB->query($queryStrDeleteFields) !== false && $this->_DB->query($queryStrDeletee2l) !== false) {
-
-					$_check = true;
-					if(!empty($queriesDeleteEntryTable)) {
-						foreach($queriesDeleteEntryTable as $q) {
-							if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($q,true));
-							if($this->_DB->query($q) == false) {
-								$_check = false;
-								break;
-							}
-						}
-					}
+				$this->_DB->query($queryStrDeleteFields);
+				$this->_DB->query($queryStrDeletee2l);
 
-					if($this->_DB->query($queryStrInsertFields) !== false && $_check === true) {
-						$alterQuery = false;
-						if(!empty($_newColumns)) {
-							$alterQuery = $this->_DB->query($queryStrAlterEntry);
-						}
-						if(!empty($_newColumns) && $alterQuery === false) {
-							throw new Exception("Failed to insert alter the table.");
-						}
-					}
-					else {
-						throw new Exception("Failed to insert the new fields.");
+				$this->_DB->commit();
+
+				// mysql implicit commit
+				if(!empty($queriesDeleteEntryTable)) {
+					foreach($queriesDeleteEntryTable as $q) {
+						if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($q,true));
+						$this->_DB->query($q);
 					}
 				}
-				else {
-					throw new Exception("Failed to delete old fields.");
-				}
+
+				$this->_DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
+				$this->_DB->query($queryStrInsertFields);
 				$this->_DB->commit();
+
+				// mysql implicit commit
+				if(!empty($_newColumns)) {
+					foreach ($queryStrAlterEntry as $q1) {
+						if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($q1,true));
+						$this->_DB->query($q1);
+					}
+				}
+
 				$ret = true;
 			}
 			catch (Exception $e) {
 				$this->_DB->rollback();
-				error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
+				error_log("[ERROR] asd ".__METHOD__." mysql catch: ".$e->getMessage());
 			}
 		}
 
diff --git a/webclient/lib/managecollections.class.php b/webclient/lib/managecollections.class.php
index fc6b86e..6abecea 100644
--- a/webclient/lib/managecollections.class.php
+++ b/webclient/lib/managecollections.class.php
@@ -218,7 +218,12 @@ class ManageCollections {
 				$this->_DB->query($queryStr);
 				$newId = $this->_DB->insert_id;
 
+				$this->_updateToolRelation($newId,$data['tool']);
+				$this->_DB->commit();
+
 
+				// mysql implicit commit with create table
+				// rollback does not really solve if there is an error
 				$queryEntry2lookup = "CREATE TABLE `".DB_PREFIX."_collection_entry2lookup_".$newId."` (
 										`fk_field` int NOT NULL,
 										`fk_entry` int NOT NULL,
@@ -252,9 +257,6 @@ class ManageCollections {
 				if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryCollectionEntry,true));
 				$this->_DB->query($queryCollectionEntry);
 
-				$this->_updateToolRelation($newId,$data['tool']);
-
-				$this->_DB->commit();
 				$ret = true;
 			}
 			catch (Exception $e) {
@@ -400,16 +402,19 @@ class ManageCollections {
 			if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStrFields,true));
 
 
+			// mysql implicit commit with drop command
+			// transaction does not really help here.
 			try {
 				$this->_DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
 
 				$this->_DB->query($queryStr);
 				$this->_DB->query($queryStrTool);
+				$this->_DB->commit();
+
 				$this->_DB->query($queryStre2l);
 				$this->_DB->query($queryStrEntry);
 				$this->_DB->query($queryStrFields);
 
-				$this->_DB->commit();
 				$ret = true;
 			}
 			catch (Exception $e) {
-- 
2.39.5