Unable to Upgrade from OTRS 5.0.19 to 6.0.4

[Rici]

Hi Everyone,

We are running into issues when performing an upgrade on our test system, when we launch the db update script ( scripts/DBUpdate-to-6.pl ) we get an error at step 28 (Post changes on article related tables ...):

Code: Select all

[Thu Jan 25 13:58:56 2018] DBUpdate-to-6.pl: DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`otrsxxx`.`#sql-48ac_19b4`, CONSTRAIT `FK_time_accounting_article_id_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`)) at /opt/otrs/Kernel/System/DB.pm line 470.
ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-51 Perl: 5.22.1 OS: linux Time: Thu Jan 25 13:58:56 2018

 Message: Cannot add or update a child row: a foreign key constraint fails (`otrsxxx`.`#sql-48ac_19b4`, CONSTRAINT `FK_time_accounting_article_id_id` FOREIGN KEY (`article_i`) REFERENCES `article` (`id`)), SQL: 'EXECUTE FKStatement'

 Traceback (42918):
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 363
   Module: scripts::DBUpdateTo6::PostArticleTableStructureChanges::_RecreateForeignKeysPointingToArticleTable Line: 432
   Module: scripts::DBUpdateTo6::PostArticleTableStructureChanges::Run Line: 66
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: scripts/DBUpdate-to-6.pl Line: 89


ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-51 Perl: 5.22.1 OS: linux Time: Thu Jan 25 13:58:56 2018

 Message: Error during execution of 'EXECUTE FKStatement'!

 Traceback (42918):
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 366
   Module: scripts::DBUpdateTo6::PostArticleTableStructureChanges::_RecreateForeignKeysPointingToArticleTable Line: 432
   Module: scripts::DBUpdateTo6::PostArticleTableStructureChanges::Run Line: 66
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: scripts/DBUpdate-to-6.pl Line: 89


 Not possible to complete migration, check previous messages for more information.

Any ideas on what may be causing this? We were previously able to perform the upgrade from OTRS 5.0.19 to an earlier 6 beta on our test system a couple of months back without any errors.

[fcasal]

That can be for several things, I would do:

- Download again the software, sometimes get corrupted when descompresing.
- Check the mysql database because it shows a foreign key error.

Greetings.

[jojo]

Solve the database issue (no need to download anything)

[Rici]

Hi Guys,

The database is a restore nightly backup of our production instance, which is currently working without issues in 5.0.19. Do you have any guidance on how to go about solving the foreign key issue?

[reneeb]

Which addons are installed?

[Rici]

Only the official FAQ, no other Addons have been installed.

[Rici]

Additional info, from a quick join between the article and time_accounting tables we can see that some records in time_account point to a non existing article_id:

Code: Select all

SELECT a.id,t.article_id FROM  `otrsdb`.`time_accounting` as t
LEFT JOIN `otrsdb`.`article` as a ON t.article_id = a.id
WHERE a.id IS NULL

otrs-upgrade-20180126.PNG

[Rici]

Should we just try to delete these records from time_accounting table?
Any ideas on how these records could have ended up with a Null reference to begin with?

[reneeb]

Maybe you added some time accountings when the tickets/articles existed. And then you removed tickets without using the API functions...

[Rici]

Hello Reneeb,

Thanks for the response. I don't believe we ever removed tickets in any way.

Would you recommend us to just delete the orphan time accounting records?