Error migration DB from 5.0.29 to 6.0.x

Moderator: crythias

Post Reply
LearningToFly
Znuny newbie
Posts: 4
Joined: 27 Aug 2018, 09:09
Znuny Version: 5.0.29
Real Name: Dmitrii

Error migration DB from 5.0.29 to 6.0.x

Post by LearningToFly »

Hello everyone.

Centos 7 with Mariadb

I try to update OTRS from version 5.0.29 to 6.0.x and everytime I get one database migration error.

Code: Select all

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 ...
    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 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 ...df -h
    
    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 ...
    Step 28 of 37: Post changes on article related tables ...
    Step 29 of 37: Migrate ArticleType in ProcessManagement Data ...
    Step 30 of 37: Migrate ArticleType in PostMaster filters ...
    Step 31 of 37: Migrate chat articles ...
	[Thu Aug 16 16:36:50 2018] DBUpdate-to-6.pl: DBD::mysql::st execute failed: Unknown column 'at.communication_channel_id' in 'where clause' at /opt/otrs/Kernel/System/DB.pm 	line 694.
	ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-00 Perl: 5.16.3 OS: linux Time: Thu Aug 16 16:36:50 2018

 Message: Unknown column 'at.communication_channel_id' in 'where clause', SQL: '
            SELECT COUNT(at.id)
            FROM article_data_mime adm
                JOIN article at ON adm.article_id = at.id
            WHERE at.communication_channel_id = ?
        '

 Traceback (6266): 
   Module: scripts::DBUpdateTo6::MigrateChatData::_CheckChatArticles Line: 434
   Module: scripts::DBUpdateTo6::MigrateChatData::Run Line: 56
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: scripts/DBUpdate-to-6.pl Line: 89

    Step 32 of 37: Initialize default cron jobs ...
    Step 33 of 37: Migrate web service configuration ...
    Step 34 of 37: Uninstall Merged Feature Add-Ons ...
    Step 35 of 37: Clean up the cache ...
    Step 36 of 37: Refresh configuration cache another time ...
    Step 37 of 37: Check SysConfig consistency ...
... migration completed with successfull, daemon started normaly, but some pages of OTRS become to unavaible with "Internal Server Error". In system journal much looped messages

Code: Select all

	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'a.is_visible_for_customer' in 'where clause', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'a.is_visible_for_customer' in 'where clause', SQL	: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'a.is_visible_for_customer' in 'where clause', SQL	: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:22 2018 	error 	OTRS-CGI-00 	Unknown column 'communication_channel_id' in 'field list', SQL: '	
	Thu Aug 16 17:32:03 2018 	error 	OTRS-otrs.Console.pl-Maint::Ticket::FulltextIndexRebuildWorker-00 	There was an error executing Execute() in 	Kernel::System::Console::Command::Maint::Ticket::FulltextIndexRebuildWorker: [Thu Aug 16 17:32:03 2018] otrs.Daemon.pl: DBD::mysql::st execute failed: Unknown column 	'search_index_needs_rebuild' in 'where clause' at /opt/otrs/Kernel/System/DB.pm line 694.
	Thu Aug 16 17:32:03 2018 	error 	OTRS-otrs.Console.pl-Maint::Ticket::FulltextIndexRebuildWorker-00 	Unknown column 'search_index_needs_rebuild' in 'where clause', SQL: '
Please advise something to resolve this problem, because I already broke my brain.

Thanks
fcasal
Znuny wizard
Posts: 336
Joined: 21 Apr 2014, 16:14
Znuny Version: 6.0.18

Re: Error migration DB from 5.0.29 to 6.0.x

Post by fcasal »

Please, tell us the step by step your are using during the migration.

Greetings.
LearningToFly
Znuny newbie
Posts: 4
Joined: 27 Aug 2018, 09:09
Znuny Version: 5.0.29
Real Name: Dmitrii

Re: Error migration DB from 5.0.29 to 6.0.x

Post by LearningToFly »

fcasal wrote: 28 Aug 2018, 13:54 Please, tell us the step by step your are using during the migration.

Greetings.

Thnks for answer

I strongly followed the official manual page of upgrade to version 6.0.x (http://doc.otrs.com/doc/manual/admin/st ... ating.html) . All installed modules is official supported (ITSM, FAQ) and upgraded successfully too.

In past I upgraded from version 3.3.4 without any problem and stuck only at this major upgrade.
fcasal
Znuny wizard
Posts: 336
Joined: 21 Apr 2014, 16:14
Znuny Version: 6.0.18

Re: Error migration DB from 5.0.29 to 6.0.x

Post by fcasal »

Are you trying with the last version 6.0.10?
LearningToFly
Znuny newbie
Posts: 4
Joined: 27 Aug 2018, 09:09
Znuny Version: 5.0.29
Real Name: Dmitrii

Re: Error migration DB from 5.0.29 to 6.0.x

Post by LearningToFly »

fcasal wrote: 29 Aug 2018, 09:03 Are you trying with the last version 6.0.10?
I tried and result is same
fcasal
Znuny wizard
Posts: 336
Joined: 21 Apr 2014, 16:14
Znuny Version: 6.0.18

Re: Error migration DB from 5.0.29 to 6.0.x

Post by fcasal »

After some updates it is convenient to restart the server, did you try that?
LearningToFly
Znuny newbie
Posts: 4
Joined: 27 Aug 2018, 09:09
Znuny Version: 5.0.29
Real Name: Dmitrii

Re: Error migration DB from 5.0.29 to 6.0.x

Post by LearningToFly »

fcasal wrote: 01 Sep 2018, 10:04 After some updates it is convenient to restart the server, did you try that?
Yes. And nothing to changed...

What is column "communication_channel_id"? As I know, this column apear just in 6.0.x version of OTRS, because in early versions this column does not exist.

I found the branch in this forum with same problem https://forums.otterhub.org/viewtopic.php?t=38826, but in German language and I'm not understand how they resolved this problem
fcasal
Znuny wizard
Posts: 336
Joined: 21 Apr 2014, 16:14
Znuny Version: 6.0.18

Re: Error migration DB from 5.0.29 to 6.0.x

Post by fcasal »

In the other place didn´t could resolv the problem, but they think could be a problem with the update script, so perhaps could be a problem with database permission?
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by crythias »

I know this is old but I also encountered this. For those who might be seeing this, the issue is the tables that are created article_data_mime*

Code: Select all

        '<TableAlter NameOld="article" NameNew="article_data_mime"/>',
        '<TableAlter NameOld="article_plain" NameNew="article_data_mime_plain"/>',
        '<TableAlter NameOld="article_attachment" NameNew="article_data_mime_attachment"/>',
And without drop/rename, they will fail migration if the DBUpgrade attempts again, even if you reload your v5 sql data.

Your data is your responsibility. Understand the implications of commands that delete data. Make backups and Verify your backups.
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
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by root »

Hi,

I always recommend creating a dump of the complete database OTRS is using. It's easier to drop and recreat the database after sth. goes wrong and just import the dump into an empty one.

- 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 ?
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by crythias »

Yeah, but when it's 55GB, it's a bit cumbersome to retry.
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
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by root »

crythias wrote: 09 Dec 2020, 20:01 Yeah, but when it's 55GB, it's a bit cumbersome to retry.
Sure, but the alternative is not really reliable. I've done this too often to try another way.

Which ArticleStorage Backend is configured?

- 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 ?
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by crythias »

Database currently.
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
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by root »

crythias wrote: 09 Dec 2020, 20:26 Database currently.
Gerald,

I would switch, at least for the migration, to FS. This will save you a lot of time and probably pain. And I delete the full-text index prior to a migration.
This allows me to reimport the dump as often as needed in the shortest time possible.

- 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 ?
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: Error migration DB from 5.0.29 to 6.0.x

Post by wurzel »

Hi,

you can try to use article storage FS
and move the data to filesystem to shrink your data size in the db.

Then you might have less troubles backuping/restoring ;-)

Flo
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.
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by crythias »

I'm getting the following:

Code: Select all

 - Add new table for chat data
[Wed Dec  9 23:51:32 2020] DBUpdate-to-6.pl: DBD::mysql::db do failed: Can't cre                                                                                                                                                             ate table `otrs`.`article_data_otrs_chat` (errno: 150 "Foreign key constraint is                                                                                                                                                              incorrectly formed") 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:                                                                                                                                                              Wed Dec 9 23:51:32 2020

 Message: Can't create table `otrs`.`article_data_otrs_chat` (errno: 150 "Foreig                                                                                                                                                             n key constraint is incorrectly formed"), SQL: 'EXECUTE FKStatement'

 Traceback (65524):
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 394
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBArray Line: 342
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::NewChatDataTable::Run                                                                                                                                                              Line: 53
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::Run Line: 142
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: scripts/DBUpdate-to-6.pl Line: 87


ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-13 Perl: 5.30.0 OS: linux Time:                                                                                                                                                              Wed Dec 9 23:51:32 2020

 Message: Error during execution of 'EXECUTE FKStatement'!

 Traceback (65524):
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 397
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBArray Line: 342
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::NewChatDataTable::Run                                                                                                                                                              Line: 53
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::Run Line: 142
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: scripts/DBUpdate-to-6.pl Line: 87

    Error.
And what I see is article is missing

Code: Select all

| Tables_in_otrs               |
+------------------------------+
| acl                          |
| acl_sync                     |
| article2                     |
| article_data_mime            |
| article_data_mime_attachment |
| article_data_mime_plain      |
| article_data_otrs_chat       |
| article_flag                 |
| article_search               |
| article_sender_type          |
| article_type      
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
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by root »

Hi,

Where does the table article2 come from?

- 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 ?
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Error migration DB from 5.0.29 to 6.0.x

Post by crythias »

That's mine. legacy and not relevant.
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