DBUpdate-to-6-pl fails.

Moderator: crythias

Post Reply
janjoh
Znuny newbie
Posts: 6
Joined: 21 Dec 2017, 14:35
Znuny Version: 5.0.14
Real Name: Jan Johansson

DBUpdate-to-6-pl fails.

Post by janjoh »

We're trying to upgrade a OTRS5 to OTRS6. The database is about 800GB in size, and we run in to the following problem,

Code: Select all

-bash-4.2$ ./DBUpdate-to-6.pl

 Migration started ...

 Checking requirements ...

    Requirement check for: Check framework version ...
    Requirement check for: Check required Perl version ...
    Requirement check for: Check required database version ...
    Requirement check for: Check database charset ...
    Requirement check for: Check required Perl modules ...
    Requirement check for: Check if database has been backed up ...

        Did you backup the database? [Y]es/[N]o: y

    Requirement check for: Upgrade database structure ...
    Requirement check for: Migrating time zone configuration ...


        The currently configured time offset is 1 hours, these are the suggestions for a corresponding OTRS time zone:

        Africa/Algiers
        Africa/Bangui
        Africa/Brazzaville
        Africa/Ceuta
        Africa/Douala
        Africa/Kinshasa
        Africa/Lagos
        Africa/Libreville
        Africa/Luanda
        Africa/Malabo
        Africa/Ndjamena
        Africa/Niamey
        Africa/Porto-Novo
        Africa/Tunis
        CET
        Europe/Amsterdam
        Europe/Andorra
        Europe/Belgrade
        Europe/Berlin
        Europe/Brussels
        Europe/Budapest
        Europe/Copenhagen
        Europe/Gibraltar
        Europe/Luxembourg
        Europe/Madrid
        Europe/Malta
        Europe/Monaco
        Europe/Oslo
        Europe/Paris
        Europe/Prague
        Europe/Rome
        Europe/Stockholm
        Europe/Tirane
        Europe/Vienna
        Europe/Warsaw
        Europe/Zurich
        MET


        It seems that Europe/Stockholm should be the correct time zone to set for your OTRS.

        Enter the time zone to use for OTRSTimeZone (leave empty to show a list of all available time zones): Europe/Stockholm

        Enter the time zone to use for UserDefaultTimeZone (leave empty to show a list of all available time zones): Europe/Stockholm

    Requirement check for: Update calendar appointment future tasks ...
    Requirement check for: Migrate GenericAgent jobs configuration ...
    Requirement check for: Migrate TicketAppointment rules configuration ...
    Requirement check for: Create entries in new article table ...
    Requirement check for: Migrate ArticleType in ProcessManagement Data ...
    Requirement check for: Migrate ArticleType in PostMaster filters ...

 Executing tasks ...

    Step 1 of 38: Check framework version ...
    Step 2 of 38: Check required Perl version ...
    Step 3 of 38: Check required database version ...
    Step 4 of 38: Check database charset ...
    Step 5 of 38: Check required Perl modules ...
    Step 6 of 38: Check if database has been backed up ...
    Step 7 of 38: Upgrade database structure ...
    Step 8 of 38: Migrate configuration ...
    Step 9 of 38: Refresh configuration cache after migration of OTRS 5 settings ...
    Step 10 of 38: Migrating ticket storage configuration ...
    Step 11 of 38: Migrating article search index configuration ...
    Step 12 of 38: Migrating ticket zoom customer information widget configuration ...
    Step 13 of 38: Drop deprecated table gi_object_lock_state ...
    Step 14 of 38: Migrate PossibleNextActions setting ...
    Step 15 of 38: Migrating time zone configuration ...
    Step 16 of 38: Create appointment calendar tables ...
    Step 17 of 38: Create ticket number counter tables ...
    Step 18 of 38: Update calendar appointment future tasks ...
    Step 19 of 38: Add basic appointment notification for reminders ...
    Step 20 of 38: Create Form Draft tables ...
    Step 21 of 38: Clean and drop group_user permission_value column ...
    Step 22 of 38: Migrate GenericAgent jobs configuration ...
    Step 23 of 38: Migrate TicketAppointment rules configuration ...
    Step 24 of 38: Migrate Merged Ticket history name values ...
    Step 25 of 38: Migrate ticket statistics ...
    Step 26 of 38: Migrate ticket notifications ...
    Step 27 of 38: Create entries in new article table ...
[Fri Feb 16 13:20:31 2018] DBUpdate-to-6.pl: DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`otrsprod`.`article`, CONSTRAINT `FK_article_article_sender_type_id_id` FOREIGN KEY (`article_sender_type_id`) REFERENCES `article_sender_type` (`id`)) at /opt/otrs/Kernel/System/DB.pm line 470.
ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-10 Perl: 5.16.3 OS: linux Time: Fri Feb 16 13:20:31 2018

 Message: Cannot add or update a child row: a foreign key constraint fails (`otrsprod`.`article`, CONSTRAINT `FK_article_article_sender_type_id_id` FOREIGN KEY (`article_sender_type_id`) REFERENCES `article_sender_type` (`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 (23999):
   Module: scripts::DBUpdateTo6::MigrateArticleData::_MigrateData Line: 302
   Module: scripts::DBUpdateTo6::MigrateArticleData::Run Line: 136
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: ./DBUpdate-to-6.pl Line: 89

ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-10 Perl: 5.16.3 OS: linux Time: Fri Feb 16 13:20:31 2018

 Message: An error occurs during article data migration!

 Traceback (23999):
   Module: scripts::DBUpdateTo6::MigrateArticleData::Run Line: 142
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: ./DBUpdate-to-6.pl Line: 89


    An error occurs during article data migration!



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

-bash-4.2$
Not really sure how to procede?

Code: Select all

mysql> select distinct article_sender_type_id from article;
+------------------------+
| article_sender_type_id |
+------------------------+
|                      1 |
|                      3 |
+------------------------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from article_sender_type;
+----+----------+----------+----------+---------------------+-----------+---------------------+-----------+
| id | name     | comments | valid_id | create_time         | create_by | change_time         | change_by |
+----+----------+----------+----------+---------------------+-----------+---------------------+-----------+
|  1 | agent    | NULL     |        1 | 2016-11-01 07:59:18 |         1 | 2016-11-01 07:59:18 |         1 |
|  2 | system   | NULL     |        1 | 2016-11-01 07:59:18 |         1 | 2016-11-01 07:59:18 |         1 |
|  3 | customer | NULL     |        1 | 2016-11-01 07:59:18 |         1 | 2016-11-01 07:59:18 |         1 |
+----+----------+----------+----------+---------------------+-----------+---------------------+-----------+
3 rows in set (0.00 sec)

mysql>
sevenbits
Znuny newbie
Posts: 3
Joined: 14 Jun 2017, 12:38
Znuny Version: 5.0.20
Real Name: Ferdinand

Re: DBUpdate-to-6-pl fails.

Post by sevenbits »

Hi,
i have the same error. How did you fix it?

Thank you!
Best Regards
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: DBUpdate-to-6-pl fails.

Post by wurzel »

Hi,

please do not hijack old threads.

you have issues with "a foreign key constraint fails"

AFAIK this is not OTRS related but something for mysql/database admins. It usually fails if someone altered tables or truncated something.
You might turn of key constraint checks before uprading (AFAIK this is not the best idea) or ask someone, who knows how to fix the mysql tables.

Cheers
Florian
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 11 (Test)
Znuny 7.x latest version testing auf Debian 11

-- Ich beantworte keine Forums-Fragen PN - No PN please

I won't answer to unfriendly users any more. A greeting and regards are just polite.
sevenbits
Znuny newbie
Posts: 3
Joined: 14 Jun 2017, 12:38
Znuny Version: 5.0.20
Real Name: Ferdinand

Re: DBUpdate-to-6-pl fails.

Post by sevenbits »

Yes you are right, the Database is inconsistent and very old, it's from 2005. But this inconsistency came from OTRS. No one opened the database and delete data or write by hand. The Data is inconistent because of OTRS Bugs.

Anyway, if anyone also face this Problem, here is the solution and yes ist's not the best and not recommend to switch consistency checks off. But i got no clue from which dataset this error came from and so there is no way for me to find and fix it.

Open scripts/DBUpdateTo6/MigrateArticleData.pm put this after line 55

# HACK TO GET THE UPDATE DONE!!!
return if !$DBObject->Prepare(
SQL => "SET FOREIGN_KEY_CHECKS=0",
);

So the foreign key checks are temporary disabled.

Regards
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: DBUpdate-to-6-pl fails.

Post by jojo »

this breaks your dataase for future use.


Fix the inconsistent parts in the database (which do not come from OTRS, I upgraded much older versions till the actual version without any issues).
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
janjoh
Znuny newbie
Posts: 6
Joined: 21 Dec 2017, 14:35
Znuny Version: 5.0.14
Real Name: Jan Johansson

Re: DBUpdate-to-6-pl fails.

Post by janjoh »

sevenbits wrote: 30 May 2020, 20:45 Yes you are right, the Database is inconsistent and very old, it's from 2005. But this inconsistency came from OTRS. No one opened the database and delete data or write by hand. The Data is inconistent because of OTRS Bugs.

Anyway, if anyone also face this Problem, here is the solution and yes ist's not the best and not recommend to switch consistency checks off. But i got no clue from which dataset this error came from and so there is no way for me to find and fix it.

Open scripts/DBUpdateTo6/MigrateArticleData.pm put this after line 55

# HACK TO GET THE UPDATE DONE!!!
return if !$DBObject->Prepare(
SQL => "SET FOREIGN_KEY_CHECKS=0",
);

So the foreign key checks are temporary disabled.

Regards
So, as I am the original poster I figured I'd post a reply

I wouldnt do that. The problem was database related. We spent many many MANY hours tracking down the corrupt entries in the database. Upon correlating the time ranges with our monitoring system we could trace one time range to correlate with a kernel panic of the database server and the other corrupt section to a major network disturbance.

We did try the disable constraints and run anyway... Let's just say that failed miserably and the upgraded database did not work as expected. (We had many many millions of tickets in our system)

What I ended up doing was "trial by halving"
I exported the first half my records with otrs dump and then the second to see in which part the error occurred.

Then I did the same with the failing part and kept doing that until I found the bad records. Then we took a look at what data was there and in the end rewrote them with clean data and adding a note to the ticket saying "this ticket may have been affected by (incident ID), please be extra diligent"

We got there in the end.
Post Reply