[SOLVED] Error while migrating from 6.3.4 to 6.4

[ahauser]

The following error appears after executing the migration script, both using 6.4.1 or 6.4.3 :

Step 7 of 16: Upgrade database structure ...
[Tue Oct 25 16:28:48 2022] MigrateToZnuny6_4.pl: DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`otrs`.`#sql-alter-106df-28b01`, CONSTRAINT `FK_ticket_history_article_id_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`)) at /opt/znuny-6.4.1/Kernel/System/DB.pm line 471.
ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-10 Perl: 5.34.0 OS: linux Time: Tue Oct 25 14:28:48 2022

Message: Cannot add or update a child row: a foreign key constraint fails (`otrs`.`#sql-alter-106df-28b01`, CONSTRAINT `FK_ticket_history_article_id_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`)), SQL: 'EXECUTE FKStatement'

Traceback (270685):
Module: scripts::Migration::Base::ExecuteXMLDBString Line: 394
Module: scripts::Migration::Base::ExecuteXMLDBArray Line: 342
Module: scripts::Migration::Znuny::UpgradeDatabaseStructure::RecreateForeignKeysToArticleTable::_RecreateForeignKeysPointingToArticleTable Line: 69
Module: scripts::Migration::Znuny::UpgradeDatabaseStructure::RecreateForeignKeysToArticleTable::Run Line: 31
Module: scripts::Migration::Znuny::UpgradeDatabaseStructure::Run Line: 81
Module: scripts::Migration::_ExecuteComponent Line: 155
Module: scripts::Migration::Run Line: 67
Module: scripts/MigrateToZnuny6_4.pl Line: 82


ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-10 Perl: 5.34.0 OS: linux Time: Tue Oct 25 14:28:48 2022

Message: Error during execution of 'EXECUTE FKStatement'!

Traceback (270685):
Module: scripts::Migration::Base::ExecuteXMLDBString Line: 397
Module: scripts::Migration::Base::ExecuteXMLDBArray Line: 342
Module: scripts::Migration::Znuny::UpgradeDatabaseStructure::RecreateForeignKeysToArticleTable::_RecreateForeignKeysPointingToArticleTable Line: 69
Module: scripts::Migration::Znuny::UpgradeDatabaseStructure::RecreateForeignKeysToArticleTable::Run Line: 31
Module: scripts::Migration::Znuny::UpgradeDatabaseStructure::Run Line: 81
Module: scripts::Migration::_ExecuteComponent Line: 155
Module: scripts::Migration::Run Line: 67
Module: scripts/MigrateToZnuny6_4.pl Line: 82




Not possible to complete migration. Check previous messages for more information.

[root]

Hi,

You probably have references to tickets and/or articles that are still deleted.

A check for this can be done with these queries:

1.) SELECT COUNT(*) FROM ticket_history WHERE article_id NOT IN (SELECT id FROM article)
2.) SELECT COUNT(*) FROM article_flag WHERE article_id NOT IN (SELECT id FROM article)
3.) SELECT COUNT(*) FROM time_accounting WHERE article_id NOT IN (SELECT id FROM article)

Let us know if any of the queries returns sth different that 0

- Roy

[ahauser]

MariaDB [otrs]> SELECT COUNT(*) FROM ticket_history WHERE article_id NOT IN (SELECT id FROM article) ;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (1.619 sec)

MariaDB [otrs]> SELECT COUNT(*) FROM article_flag WHERE article_id NOT IN (SELECT id FROM article) ;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (1.106 sec)

MariaDB [otrs]> SELECT COUNT(*) FROM time_accounting WHERE article_id NOT IN (SELECT id FROM article) ;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.010 sec)

[root]

HI,

I would create a backup of the database and then execute these queries

DELETE FROM ticket_history WHERE article_id NOT IN (SELECT id FROM article);
DELETE FROM article_flag WHERE article_id NOT IN (SELECT id FROM article) ;


Every query should only delete one record.

- Roy

[ahauser]

Thanks for your help, the migration process worked correctly after this.
If I understand correctly, only 2 articles have been deleted ? (one from the history Window, and the other one from "flags" which mean unread articles, priority etc ?)

[root]

Thanks for your help, the migration process worked correctly after this.
If I understand correctly, only 2 articles have been deleted ? (one from the history Window, and the other one from "flags" which mean unread articles, priority etc ?)

No, you deleted 2 references to non existing articles.

- Roy