Updating otrs from 5 to 6 / DBUpdate-to-6.pl error

Moderator: crythias

Post Reply
felixotrs
Znuny newbie
Posts: 3
Joined: 10 Apr 2021, 11:53
Znuny Version: 6.0.30
Real Name: Felix

Updating otrs from 5 to 6 / DBUpdate-to-6.pl error

Post by felixotrs »

Hi, i'm updating otrs from 5 to 6 on Debian with mysql /mariadb, following the instructions from this link:

https://doc.otrs.com/doc/manual/admin/6 ... ating.html

Running scripts/DBUpdate-to-6.pl i get the following error:

Code: Select all

$ scripts/DBUpdate-to-6.pl

Code: Select all

Executing tasks ... 

    Step 1 of 37: Check framework version ...
    Step 2 of 37: Check required Perl version ...
    Step 3 of 37: Check required database version ...
    Step 4 of 37: Check database charset ...
    Step 5 of 37: Check required Perl modules ...
    Step 6 of 37: Check if database has been backed up ...
    Step 7 of 37: Upgrade database structure ...
    Step 8 of 37: Migrate configuration ...
    Step 9 of 37: Refresh configuration cache after migration of OTRS 5 settings ...
    Step 10 of 37: Migrating ticket storage configuration ...
    Step 11 of 37: Migrating article search index configuration ...
    Step 12 of 37: Migrating ticket zoom customer information widget configuration ...
    Step 13 of 37: Drop deprecated table gi_object_lock_state ...
    Step 14 of 37: Migrate PossibleNextActions setting ...
    Step 15 of 37: Migrating time zone configuration ...
    Step 16 of 37: Create appointment calendar tables ...
    Step 17 of 37: Create ticket number counter tables ...
    Step 18 of 37: Update calendar appointment future tasks ...
    Step 19 of 37: Add basic appointment notification for reminders ...
    Step 20 of 37: Create Form Draft tables ...
    Step 21 of 37: Clean and drop group_user permission_value column ...
    Step 22 of 37: Migrate GenericAgent jobs configuration ...
    Step 23 of 37: Migrate TicketAppointment rules configuration ...
    Step 24 of 37: Migrate Merged Ticket history name values ...
    Step 25 of 37: Migrate ticket statistics ...
    Step 26 of 37: Migrate ticket notifications ...
    Step 27 of 37: Create entries in new article table ...
[Sat Apr 10 11:31:39 2021] DBUpdate-to-6.pl: Use of uninitialized value within %ArticleTypes in hash element at /opt/otrs/scripts/DBUpdateTo6/MigrateArticleData.pm line 114.
[Sat Apr 10 11:31:39 2021] DBUpdate-to-6.pl: Use of uninitialized value within %ArticleTypes in hash element at /opt/otrs/scripts/DBUpdateTo6/MigrateArticleData.pm line 117.
[Sat Apr 10 11:31:39 2021] DBUpdate-to-6.pl: Use of uninitialized value $CommunicationChannel in hash element at /opt/otrs/scripts/DBUpdateTo6/MigrateArticleData.pm line 118.
[Sat Apr 10 11:31:39 2021] DBUpdate-to-6.pl: DBD::mysql::db do failed: Column 'communication_channel_id' cannot be null at /opt/otrs/Kernel/System/DB.pm line 470.
ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-10 Perl: 5.28.1 OS: linux Time: Sat Apr 10 11:31:39 2021

 Message: Column 'communication_channel_id' cannot be null, 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 (20523): 
   Module: scripts::DBUpdateTo6::MigrateArticleData::_MigrateData Line: 302
   Module: scripts::DBUpdateTo6::MigrateArticleData::Run Line: 139
   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-10 Perl: 5.28.1 OS: linux Time: Sat Apr 10 11:31:39 2021

 Message: An error occurs during article data migration!

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


    An error occurs during article data migration!



 Not possible to complete migration, check previous messages for more information. 
Checking the Database for NULL values for article_sender_type_id in articles results in zero results:

Code: Select all

MariaDB [otrs]> select * from article where article_sender_type_id = '';
Empty set (0.000 sec)
Any idea what could be the problem or how to solve this?

Articles with article_sender_type_id not NULL:

Code: Select all

MariaDB [otrs]> select count(id) from article where article_sender_type_id  != '';
+-----------+
| count(id) |
+-----------+
|     14925 |
+-----------+
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Updating otrs from 5 to 6 / DBUpdate-to-6.pl error

Post by root »

Hi,

I don't know why you checking for article_sender_type_id when the problem is about communication_channel_id

Anyway, the problem occurs on an INSERT because of a NULL value, so checking for NULL values is not needed. ('' <> NULL).

What is the content of the table article_type of the version 5 database?

Code: Select all

select * from article_type;
- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
felixotrs
Znuny newbie
Posts: 3
Joined: 10 Apr 2021, 11:53
Znuny Version: 6.0.30
Real Name: Felix

Re: Updating otrs from 5 to 6 / DBUpdate-to-6.pl error

Post by felixotrs »

Hi Roy,
I don't know why you checking for article_sender_type_id when the problem is about communication_channel_id
sorry, my fault. When i created the topic i actually mixed it up.

Code: Select all

MariaDB [otrs]> select * from article where communication_channel_id = '';
Empty set, 1 warning (0.001 sec)
What is the content of the table article_type of the version 5 database?

Code: Select all

MariaDB [otrs]> select * from article_type;
+----+------------------------+----------+----------+---------------------+-----------+---------------------+-----------+
| id | name                   | comments | valid_id | create_time         | create_by | change_time         | change_by |
+----+------------------------+----------+----------+---------------------+-----------+---------------------+-----------+
|  1 | email-external         | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  2 | email-internal         | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  3 | email-notification-ext | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  4 | email-notification-int | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  5 | phone                  | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  6 | fax                    | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  7 | sms                    | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  8 | webrequest             | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
|  9 | note-internal          | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
| 10 | note-external          | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
| 11 | note-report            | NULL     |        1 | 2006-05-23 14:16:21 |         1 | 2006-05-23 14:16:21 |         1 |
+----+------------------------+----------+----------+---------------------+-----------+---------------------+-----------+
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Updating otrs from 5 to 6 / DBUpdate-to-6.pl error

Post by root »

Hi,

Please check the database of version 5 with this query:

Code: Select all

SELECT COUNT(*) FROM article WHERE article_type_id IS NULL;
- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
felixotrs
Znuny newbie
Posts: 3
Joined: 10 Apr 2021, 11:53
Znuny Version: 6.0.30
Real Name: Felix

Re: Updating otrs from 5 to 6 / DBUpdate-to-6.pl error

Post by felixotrs »

You mean article_sender_type_id instead of article_type_id ?

Code: Select all

MariaDB [otrs]> describe article;
+----------------------------+-------------+------+-----+---------+----------------+
| Field                      | Type        | Null | Key | Default | Extra          |
+----------------------------+-------------+------+-----+---------+----------------+
| id                         | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| ticket_id                  | bigint(20)  | NO   | MUL | NULL    |                |
| article_sender_type_id     | smallint(6) | NO   | MUL | NULL    |                |
| communication_channel_id   | bigint(20)  | NO   | MUL | NULL    |                |
| is_visible_for_customer    | smallint(6) | NO   |     | NULL    |                |
| search_index_needs_rebuild | smallint(6) | NO   | MUL | 1       |                |
| insert_fingerprint         | varchar(64) | YES  |     | NULL    |                |
| create_time                | datetime    | NO   |     | NULL    |                |
| create_by                  | int(11)     | NO   | MUL | NULL    |                |
| change_time                | datetime    | NO   |     | NULL    |                |
| change_by                  | int(11)     | NO   | MUL | NULL    |                |
+----------------------------+-------------+------+-----+---------+----------------+
11 rows in set (0.001 sec)

Code: Select all

MariaDB [otrs]> SELECT COUNT(*) FROM article WHERE article_sender_type_id IS NULL;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.000 sec)
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Updating otrs from 5 to 6 / DBUpdate-to-6.pl error

Post by root »

Hi,

I'm interested in the content of your database before you started the migration to version 6. Your 'desc article' is not OTRS 5.
The error "Column 'communication_channel_id' cannot be null, SQL: '" indicates that you have a NULL value in the article table of version 5.
You should start the migration again from the beginning. And before running DBUpdate-to-6.pl check the article table for NULL values.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
Post Reply