Checking for foreign key issues on upgrade

Moderator: crythias

Post Reply
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Checking for foreign key issues on upgrade

Post by crythias »

So if you're updating the DB and you get something like this...

Code: Select all

DBUpdate-to-6.pl: DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`otrs`.`#sql-378_15`, CONSTRAINT `FK_ticket_history_article_id_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`)) at /opt/otrs/Kernel/System/DB.pm line 470.
ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-13 Perl: 5.30.0 OS: linux Time: Fri Dec 4 20:12:56 2020

 Message: Cannot add or update a child row: a foreign key constraint fails (`otrs`.`#sql-378_15`, CONSTRAINT `FK_ticket_history_article_id_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`)), SQL: 'EXECUTE FKStatement'
You can run this:

Code: Select all

select th.id, th.ticket_id, th.article_id from ticket_history th where not exists(select 1 from article a where a.id=th.article_id) and th.article_id is not null;
where it shows you ticket_history article_ids that don't exist.

What can you do with this? well ... delete them from ticket history? change them to a more valid article_id? It really depends on what your tolerance is for the entries. The gist, though, is that the ticket history has invalid article_ids, and there is no provision to create a foreign key constraint between the article_id that ticket_history thinks it knows and an article.id that doesn't exist. No, I don't know why it doesn't exist. In my case, the data are so old (years) that any change that resolves the problem of having an invalid article_id in ticket_history probably would be reasonable to get past this impasse.

I'm not telling you what to do with your data. It's up to you to determine the most reasonable option to pursue. Since the ticket_history has a ticket_id as well, one might consider changing the ticket_history article_id to another real article_id in the same ticket.

Backup first, your data is your responsibility, etc.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Post Reply