View Issue Details

IDProjectCategoryView StatusLast Update
0013388Tine 2.0Tinebasepublic2017-08-14 15:19
ReporterTwizzyDizzyAssigned Topmehrer 
PriorityhighSeverityblockReproducibilityN/A
Status resolvedResolutionfixed 
Product Version2017.08.1 Community Edition 
Target Version2017.08.3 Community EditionFixed in Version2017.08.3 Community Edition 
Summary0013388: Upgrade from 2017.02.5 to 2017.08.1 fails on TineBase database migration
DescriptionSQLSTATE[HY000]: General error: 1025 Error on rename of './tine20/#sql-4923_a778' to './tine20/tine20_container' (errno: 150), query was: ALTER TABLE `tine20_container` CHANGE COLUMN `id` `id` varchar(40) NOT NULL
.../vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute()
.../vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute()
.../vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query()
.../Setup/Backend/Abstract.php(207): Zend_Db_Adapter_Pdo_Abstract->query()
.../Setup/Backend/Mysql.php(258): Setup_Backend_Abstract->execQueryVoid()
.../Tinebase/Setup/Update/Release10.php(1333): Setup_Backend_Mysql->alterCol()
.../Setup/Controller.php(568): Tinebase_Setup_Update_Release10->update_31()
.../Setup/Controller.php(454): Setup_Controller->updateApplication()
.../Setup/Frontend/Json.php(120): Setup_Controller->updateApplications()
[internal function]: Setup_Frontend_Json->updateApplications()
.../vendor/zendframework/zendframework1/library/Zend/Server/Abstract.php(232): call_user_func_array()
.../vendor/zendframework/zendframework1/library/Zend/Json/Server.php(590): Zend_Server_Abstract->_dispatch()
.../vendor/zendframework/zendframework1/library/Zend/Json/Server.php(197): Zend_Json_Server->_handle()
.../Setup/Server/Json.php(90): Zend_Json_Server->handle()
.../Setup/Core.php(115): Setup_Server_Json->handle()
.../setup.php(14): Setup_Core::dispatchRequest()
Additional InformationOS: Debian 8
TagsNo tags attached.
mwticket

Activities

TwizzyDizzy

TwizzyDizzy

2017-08-11 15:23

reporter   ~0020566

If you need a "--no-data" mysql dump, just say the word :)

Cheers
Thomas
pmehrer

pmehrer

2017-08-11 15:30

developer   ~0020568

Hello Thomas,

thank you for your report, I am going to look at this today.

yes, a --no-data dump would be helpfull, ideally if available, one dump before the update ran and one after it ran. But just the one after the update ran is still better than nothing.

best regards
Paul
TwizzyDizzy

TwizzyDizzy

2017-08-11 15:33

reporter   ~0020570

Here's the dump after the update, I'll get you the dump from before the update later, as I have to restore backups for that.

Cheers
Thomas

tine20.schema-dump-before-update.sql (126,780 bytes)
TwizzyDizzy

TwizzyDizzy

2017-08-11 15:34

reporter   ~0020572

Argh... the last one has to be named "tine20.schema-dump-after-update.sql" :-S
pmehrer

pmehrer

2017-08-11 15:44

developer   ~0020574

thank you for the dump, this is good enough. No need to create a second dump. Give me a few minutes to validate, you can resolve this with an easy sql query. I will send it in a few min, no need to restore backups
TwizzyDizzy

TwizzyDizzy

2017-08-11 15:47

reporter   ~0020576

Ah.. it was done fast, so let's have the dump from before the update just for documentation purposes :)

tine20.schema-dump-before-update-2.sql (127,887 bytes)
pmehrer

pmehrer

2017-08-11 15:53

developer   ~0020578

please execute this sql statement before you run the update. There is no need to undo the failed update. The update is reentrant! If the update failed, just execute this sql statement:

alter table `tine20_humanresources_contract` DROP FOREIGN KEY `tine20_contract::feast_calendar_id--container::id`

and then rerun the update. The update will pickup work where it stopped.

best regards
Paul
pmehrer

pmehrer

2017-08-11 16:01

developer   ~0020580

commited a patch to fix this:

Tinebase_Setup_Update10 - add failsafety: drop hr foreign key

in human resources update7 a foreign key to container was dropped. If for
some reason this foreign key is still present, the update fails. To be safe
we drop it now again here in tinebase update10
pmehrer

pmehrer

2017-08-11 16:02

developer   ~0020582

http://gerrit.tine20.com/customers/#/c/5457/
TwizzyDizzy

TwizzyDizzy

2017-08-11 16:07

reporter   ~0020584

Different error, still a failing migration after executing your statement:

SQLSTATE[HY000]: General error: 1005 Can't create table 'tine20.#sql-4923_a94c' (errno: 150), query was: ALTER TABLE `tine20_sales_contracts` ADD CONSTRAINT `tine20_sales_contracts::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`)
.../vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute()
.../vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute()
.../vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query()
.../Setup/Backend/Abstract.php(207): Zend_Db_Adapter_Pdo_Abstract->query()
.../Setup/Backend/Abstract.php(408): Setup_Backend_Abstract->execQueryVoid()
.../Tinebase/Setup/Update/Release10.php(1567): Setup_Backend_Abstract->addForeignKey()
.../Setup/Controller.php(568): Tinebase_Setup_Update_Release10->update_31()
.../Setup/Controller.php(454): Setup_Controller->updateApplication()
.../Setup/Frontend/Json.php(120): Setup_Controller->updateApplications()
[internal function]: Setup_Frontend_Json->updateApplications()
.../vendor/zendframework/zendframework1/library/Zend/Server/Abstract.php(232): call_user_func_array()
.../vendor/zendframework/zendframework1/library/Zend/Json/Server.php(590): Zend_Server_Abstract->_dispatch()
.../vendor/zendframework/zendframework1/library/Zend/Json/Server.php(197): Zend_Json_Server->_handle()
.../Setup/Server/Json.php(90): Zend_Json_Server->handle()
.../Setup/Core.php(115): Setup_Server_Json->handle()
.../setup.php(14): Setup_Core::dispatchRequest()
pmehrer

pmehrer

2017-08-11 16:36

developer   ~0020586

more of the same. You have those tables in your database present, but uninstalled the applications. One solution would be to drop the tables of the applications that are not installed.

I will make the update script more robust for such cases and can send you a patch later. (or you just drop the tables if you are certain of what you are doing)
pmehrer

pmehrer

2017-08-11 16:53

developer   ~0020588

be adviced that your database seems to contain tables of uninstalled applications. You must not reinstall those applications with your current database. Before reinstalling those applications, the related tables need to be dropped manually. Just a side note.

find attached the patch.

best regards
Paul

diff (5,928 bytes)
diff --git a/tine20/Tinebase/Setup/Update/Release10.php b/tine20/Tinebase/Setup/Update/Release10.php
index 986f102..54f379c 100644
--- a/tine20/Tinebase/Setup/Update/Release10.php
+++ b/tine20/Tinebase/Setup/Update/Release10.php
@@ -1545,7 +1545,8 @@ class Tinebase_Setup_Update_Release10 extends Setup_Update_Abstract
                     </reference>
                 </index>'));
 
-        if ($this->_backend->tableExists('timeaccount')) {
+        if (Tinebase_Application::getInstance()->isInstalled('Timetracker') &&
+                $this->_backend->tableExists('timeaccount')) {
             $this->_backend->addForeignKey('timeaccount', new Setup_Backend_Schema_Index_Xml('<index>
                     <name>timeaccount::container_id--container::id</name>
                     <field>
@@ -1559,7 +1560,8 @@ class Tinebase_Setup_Update_Release10 extends Setup_Update_Abstract
                 </index>'));
         }
 
-        if ($this->_backend->tableExists('sales_contracts')) {
+        if (Tinebase_Application::getInstance()->isInstalled('Sales') &&
+                $this->_backend->tableExists('sales_contracts')) {
             $this->_backend->addForeignKey('sales_contracts', new Setup_Backend_Schema_Index_Xml('<index>
                     <name>sales_contracts::container_id--container::id</name>
                     <field>
@@ -1573,7 +1575,8 @@ class Tinebase_Setup_Update_Release10 extends Setup_Update_Abstract
                 </index>'));
         }
 
-        if ($this->_backend->tableExists('metacrm_lead')) {
+        if (Tinebase_Application::getInstance()->isInstalled('Crm') &&
+                $this->_backend->tableExists('metacrm_lead')) {
             $this->_backend->addForeignKey('metacrm_lead', new Setup_Backend_Schema_Index_Xml('<index>
                     <name>metacrm_lead::container_id--container::id</name>
                     <field>
@@ -1587,46 +1590,48 @@ class Tinebase_Setup_Update_Release10 extends Setup_Update_Abstract
                 </index>'));
         }
 
-        if ($this->_backend->tableExists('cal_resources')) {
-            $this->_backend->addForeignKey('cal_resources', new Setup_Backend_Schema_Index_Xml('<index>
-                    <name>cal_resources::container_id--container::id</name>
-                    <field>
-                        <name>container_id</name>
-                    </field>
-                    <foreign>true</foreign>
-                    <reference>
-                        <table>container</table>
-                        <field>id</field>
-                    </reference>
-                </index>'));
-        }
+        if (Tinebase_Application::getInstance()->isInstalled('Calendar')) {
+            if ($this->_backend->tableExists('cal_resources')) {
+                $this->_backend->addForeignKey('cal_resources', new Setup_Backend_Schema_Index_Xml('<index>
+                        <name>cal_resources::container_id--container::id</name>
+                        <field>
+                            <name>container_id</name>
+                        </field>
+                        <foreign>true</foreign>
+                        <reference>
+                            <table>container</table>
+                            <field>id</field>
+                        </reference>
+                    </index>'));
+            }
 
-        if ($this->_backend->tableExists('cal_events')) {
-            $this->_backend->addForeignKey('cal_events', new Setup_Backend_Schema_Index_Xml('<index>
-                    <name>cal_events::container_id--container::id</name>
-                    <field>
-                        <name>container_id</name>
-                    </field>
-                    <foreign>true</foreign>
-                    <reference>
-                        <table>container</table>
-                        <field>id</field>
-                    </reference>
-                </index>'));
-        }
+            if ($this->_backend->tableExists('cal_events')) {
+                $this->_backend->addForeignKey('cal_events', new Setup_Backend_Schema_Index_Xml('<index>
+                        <name>cal_events::container_id--container::id</name>
+                        <field>
+                            <name>container_id</name>
+                        </field>
+                        <foreign>true</foreign>
+                        <reference>
+                            <table>container</table>
+                            <field>id</field>
+                        </reference>
+                    </index>'));
+            }
 
-        if ($this->_backend->tableExists('cal_attendee')) {
-            $this->_backend->addForeignKey('cal_attendee', new Setup_Backend_Schema_Index_Xml('<index>
-                    <name>cal_attendee::displaycontainer_id--container::id</name>
-                    <field>
-                        <name>displaycontainer_id</name>
-                    </field>
-                    <foreign>true</foreign>
-                    <reference>
-                        <table>container</table>
-                        <field>id</field>
-                    </reference>
-                </index>'));
+            if ($this->_backend->tableExists('cal_attendee')) {
+                $this->_backend->addForeignKey('cal_attendee', new Setup_Backend_Schema_Index_Xml('<index>
+                        <name>cal_attendee::displaycontainer_id--container::id</name>
+                        <field>
+                            <name>displaycontainer_id</name>
+                        </field>
+                        <foreign>true</foreign>
+                        <reference>
+                            <table>container</table>
+                            <field>id</field>
+                        </reference>
+                    </index>'));
+            }
         }
 
         if ($this->_backend->tableExists('addressbook')) {
diff (5,928 bytes)
TwizzyDizzy

TwizzyDizzy

2017-08-11 17:38

reporter   ~0020590

Indeed I have tried them some years ago, but didn't use them in the long term. I would've expected that those tables get deleted on uninstalling the application. This doesn't seem to be the case though.

I just tried the upgrade with my initial state after running your first SQL statement above. I then applied your patch. The upgrade went smoothly.

So this actual issue is fixed and was due to the state of my database. Anyway: is there any way I could clean up my database? Is there documentation on which table is needed for what? I would like to clean up the database in order to not run into those issues again. Just as a background information: I've been running this instance of tine20 for at least 5 years ... if not even more... so yeah, this might explain the one or the other...

Cheers
Thomas

Issue History

Date Modified Username Field Change
2017-08-11 15:19 TwizzyDizzy New Issue
2017-08-11 15:23 TwizzyDizzy Note Added: 0020566
2017-08-11 15:30 pmehrer Note Added: 0020568
2017-08-11 15:30 pmehrer Assigned To => pmehrer
2017-08-11 15:33 TwizzyDizzy File Added: tine20.schema-dump-before-update.sql
2017-08-11 15:33 TwizzyDizzy Note Added: 0020570
2017-08-11 15:34 TwizzyDizzy Note Added: 0020572
2017-08-11 15:44 pmehrer Note Added: 0020574
2017-08-11 15:47 TwizzyDizzy File Added: tine20.schema-dump-before-update-2.sql
2017-08-11 15:47 TwizzyDizzy Note Added: 0020576
2017-08-11 15:53 pmehrer Note Added: 0020578
2017-08-11 16:01 pmehrer Note Added: 0020580
2017-08-11 16:02 pmehrer Status new => gerrit
2017-08-11 16:02 pmehrer Note Added: 0020582
2017-08-11 16:07 TwizzyDizzy Note Added: 0020584
2017-08-11 16:36 pmehrer Note Added: 0020586
2017-08-11 16:36 pmehrer Status gerrit => new
2017-08-11 16:53 pmehrer File Added: diff
2017-08-11 16:53 pmehrer Note Added: 0020588
2017-08-11 16:54 pmehrer Status new => gerrit
2017-08-11 17:23 pmehrer Target Version => 2017.08.4 Community Edition
2017-08-11 17:38 TwizzyDizzy Note Added: 0020590
2017-08-14 13:17 pschuele Status gerrit => resolved
2017-08-14 13:17 pschuele Resolution open => fixed
2017-08-14 13:17 pschuele Fixed in Version => 2017.08.4 Community Edition
2017-08-14 14:51 mspahn Target Version 2017.08.4 Community Edition => 2017.08.3 Community Edition
2017-08-14 15:19 mspahn Fixed in Version 2017.08.4 Community Edition => 2017.08.3 Community Edition