Migrationsproblem

Hilfe zu OTRS Problemen aller Art
Post Reply
janc
Znuny newbie
Posts: 2
Joined: 16 Aug 2021, 16:31
Znuny Version: 5.0
Real Name: Jan Czmok
Company: Internet AG

Migrationsproblem

Post by janc »

Hallo!
Ich sitze seit zwei Tagen daran, unser altes OTRS5-Free auf znuny zu heben. Ich habe einen neuen Server gebaut, dort mysqldump entsprechend eingespielt und hatte diverse Fehler, die ich aber alle Fixen konnte, jetzt habe ich das folgende Problem:

Step 30 of 44: Create entries in new article table ...
[Tue Aug 17 08:52:40 2021] DBUpdate-to-6.pl: DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`otrs`.`article`, CONSTRAINT `FK_article_create_by_id` FOREIGN KEY (`create_by`) REFERENCES `users` (`id`)) at /opt/otrs/Kernel/System/DB.pm line 471.
ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-30 Perl: 5.28.1 OS: linux Time: Tue Aug 17 08:52:40 2021

Message: Cannot add or update a child row: a foreign key constraint fails (`otrs`.`article`, CONSTRAINT `FK_article_create_by_id` FOREIGN KEY (`create_by`) REFERENCES `users` (`id`)), SQL: '
INSERT INTO article (
id,ticket_id,article_sender_type_id,communication_channel_id,
is_visible_for_customer,create_by,create_time,change_by,change_time
)VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ), ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) '

Traceback (2039):
Module: scripts::DBUpdateTo6::MigrateArticleData::_MigrateData Line: 303
Module: scripts::DBUpdateTo6::MigrateArticleData::Run Line: 140
Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 158
Module: scripts::DBUpdateTo6::Run Line: 70
Module: scripts/DBUpdate-to-6.pl Line: 88

ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-30 Perl: 5.28.1 OS: linux Time: Tue Aug 17 08:52:40 2021

Message: An error occurs during article data migration!

Traceback (2039):
Module: scripts::DBUpdateTo6::MigrateArticleData::Run Line: 143
Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 158
Module: scripts::DBUpdateTo6::Run Line: 70
Module: scripts/DBUpdate-to-6.pl Line: 88


An error occurs during article data migration!


also:

MariaDB [(none)]> SHOW ENGINE INNODB STATUS ;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-08-17 08:52:40 0x7f93ec3a3700 Transaction:
TRANSACTION 215916, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1128, 63 row lock(s), undo log entries 57
MySQL thread id 111, OS thread handle 140273300158208, query id 114476 localhost 127.0.0.1 otrs Update
INSERT INTO article (
id,ticket_id,article_sender_type_id,communication_channel_id,
is_visible_for_customer,create_by,create_time,change_by,change_time
)VALUES ( '2', '2', '3', '1', '1', '1', '2010-12-03 18:03:07', '1', '2010-12-03 18:03:07' ), ( '3', '3', '3', '1', '1', '1', '2010-12-03 18:09:59', '1', '2010-12-03 18:09:59' ) , ( '4', '4', '3', '2', '1', '3', '2010-12-03 18:39:09', '3', '2010-12-03 18:39:09' ) , ( '6', '3', '1', '3', '0', '3', '2010-12-03 18:55:15', '3', '2010-12-03 18:55:15' ) , ( '7', '5', '3', '1', '1', '1', '2010-12-
Foreign key constraint fails for table `otrs`.`article`:
,
CONSTRAINT `FK_article_create_by_id` FOREIGN KEY (`create_by`) REFERENCES `users` (`id`) in parent table, in index FK_article_create_by_id tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000002; asc ;;
1: len 8; hex 800000000000005f; asc _;;

But in parent table `otrs`.`users`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000000000; asc ;;
2: len 7; hex 80000000000000; asc ;;
3: len 6; hex 7363686f6c7a; asc XXXXXXX;;
4: len 30; hex 393034386537393435656233636331386361303138303238356361616331; asc 9048e7945eb3cc18ca0180285caac1; (total 64 bytes);
5: len 7; hex 44722e2068632e; asc XXXXXX.;;
6: len 6; hex 44722e68632e; asc XXXXXX.;;
7: len 6; hex 5363686f6c7a; asc XXXXXX;;
8: len 2; hex 8001; asc ;;
9: len 5; hex 99878509d7; asc ;;
10: len 4; hex 80000002; asc ;;
11: len 5; hex 999086acd0; asc ;;
12: len 4; hex 80000003; asc ;;


einige dinge sind mt XXX getauscht, wegen Datenschutz.

Jemand einen Ansatz ?
zzz
Znuny superhero
Posts: 888
Joined: 15 Dec 2016, 15:13
Znuny Version: All
Real Name: Emin
Company: Efflux GmbH
Contact:

Re: Migrationsproblem

Post by zzz »

Hallo,

habt Ihr Agenten manuell über die Datenbank gelöscht?
Das sieht nach inkonsistenten Daten in Bezug auf Agenten/Usern aus.

Die Referenzen müssen korrigiert werden, indem entweder die manuell gelöschten Agenten wieder mit der richtigen UserID angelegt werden oder Ihr ändert die — referenzierten aber nicht existenten — UserIDs auf einen existierenden Benutzer um.

Ein DBA kann da sicherlich mehr Informationen zu geben.

— Emin
Professional OTRS, Znuny & OTOBO services: efflux.de | efflux.de/en/

Free and premium add-ons: German | English
janc
Znuny newbie
Posts: 2
Joined: 16 Aug 2021, 16:31
Znuny Version: 5.0
Real Name: Jan Czmok
Company: Internet AG

Re: Migrationsproblem

Post by janc »

Ich habe jetzt die "Fehlenden" Agenten hinzugefügt und warte auf das Resultat. Wie ordnet man die am besten neu an bzw. findet die Inkonsistenzen ?
zzz
Znuny superhero
Posts: 888
Joined: 15 Dec 2016, 15:13
Znuny Version: All
Real Name: Emin
Company: Efflux GmbH
Contact:

Re: Migrationsproblem

Post by zzz »

Code: Select all

SELECT * FROM `otrs`.users;
gibt eine Liste aller Agenten aus. Die Spalte 'ID' sollte fortlaufend und lückenlos hochzählen.
Vor manuellen Datenbank-Änderungen immer einen Back-up erstellen. Zudem sollte man sich gut überlegen, was man da macht und was für Auswirkungen das haben könnte. Idealerweise holt man sich einen Experten/DBA hinzu.

Nach manuellen DB-Änderungen immer den OTRS-Cache leeren (bin/otrs.Console.pl).

— Emin
Professional OTRS, Znuny & OTOBO services: efflux.de | efflux.de/en/

Free and premium add-ons: German | English
Post Reply