0013388: Upgrade from 2017.02.5 to 2017.08.1 fails on TineBase database migration #6623

Closed
Gloirin opened this Issue Jun 9, 2018 · 13 comments

Comments

Projects
None yet
1 participant
@Gloirin

Gloirin commented Jun 9, 2018

Reported by TwizzyDizzy on 11 Aug 2017 13:19

Version: 2017.08.1 Community Edition

SQLSTATE[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 information: OS: Debian 8

@Gloirin Gloirin added this to the 2017.08.3 Community Edition milestone Jun 9, 2018

@Gloirin Gloirin self-assigned this Jun 9, 2018

@Gloirin Gloirin closed this Jun 9, 2018

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:23

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

Cheers
Thomas

Gloirin commented Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:23

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

Cheers
Thomas

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 13:30

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 13:30

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:33

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

Gloirin commented Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:33

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:34

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

Gloirin commented Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:34

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 13:44

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 13:44

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:47

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

Gloirin commented Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 13:47

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 13:53

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 13:53

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:01

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:01

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:02

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:02

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 14:07

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()

Gloirin commented Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 14:07

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()

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:36

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)

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:36

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)

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:53

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

Gloirin commented Jun 11, 2018

Comment posted by pmehrer on 11 Aug 2017 14:53

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

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 15:38

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

Gloirin commented Jun 11, 2018

Comment posted by TwizzyDizzy on 11 Aug 2017 15:38

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment