OTRS Upgrade v5 to v6

Moderator: crythias

Post Reply
manoj99
Znuny newbie
Posts: 41
Joined: 22 Jan 2020, 00:00
Znuny Version: 5.0.42
Real Name: mk99

OTRS Upgrade v5 to v6

Post by manoj99 »

Our current stack is as follows - CentOS 7.8 with Oracle 19c version.

We had issues migrating from v5 to v6 and we fixed them for the migration to complete:

1. LOB extension
2. uncomment Timzone in /opt/otrs/scripts/DBUpdateTo6/TaskConfig/MigrateTimeZoneConfiguration.yml
3. rename triggers and sequences
and yes, followed OTRS manual for migration steps "https://doc.otrs.com/doc/manual/admin/6 ... ating.html"

We manually renamed triggers and sequences before running the upgrade script as the upgrade script complained to rename them i.e DBUpdate-to-6.pl. and that helped to complete the upgrade process.

After the post upgrade, we did some tests in creating tickets, replying to tickets, forwarding etc. as when replying to a ticket or forwarding we encountered an error on otrs logs
[Mon Dec 9 15:02:12 2019][Error][Kernel::System::Ticket::HistoryAdd][5611] ORA-02291: integrity constraint (OTRS.FK_TICKET_HISTORY_ARTICLE_ID41) violated - parent key not found (DBD ERROR: OCIStmtExecute), SQL: 'INSERT INTO ticket_history (name, history_type_id, ticket_id, article_id, queue_id, owner_id, priority_id, state_id, type_id, create_time, create_by, change_time, change_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, '2019-12-09 20:02:12', ?, '2019-12-09 20:02:12', ?)'
[Mon Dec 9 15:02:13 2019][Error][Kernel::System::Ticket::Article::Backend::MIMEBase::ArticleStorageDB::ArticleWritePlain][172] ORA-04098: trigger 'OTRS.SE_ARTICLE_PLAIN_T' is invalid and failed re-validation (DBD ERROR: error possibly near <*> indicator at char 12 in 'INSERT INTO <*>article_data_mime_plain (article_id, body, create_time, create_by, change_time, change_by) VALUES (:p1, :p2, '2019-12-09 20:02:13', :p3, '2019-12-09 20:02:13', :p4)'), SQL: 'INSERT INTO article_data_mime_plain (article_id, body, create_time, create_by, change_time, change_by) VALUES (?, ?, '2019-12-09 20:02:13', ?, '2019-12-09 20:02:13', ?)'
[Mon Dec 9 15:02:13 2019][Error][Kernel::System::Ticket::Article::Backend::MIMEBase::ArticleStorageDB::ArticleWriteAttachment][252] ORA-04098: trigger 'OTRS.SE_ARTICLE_ATTACHMENT_T' is invalid and failed re-validation (DBD ERROR: error possibly near <*> indicator at char 25 in '
INSERT INTO <*>article_data_mime_attachment (article_id, filename, content_type, content_size,
content, content_id, content_alternative, disposition, create_time, create_by,
change_time, change_by)
VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, '2019-12-09 20:02:13', :p9, '2019-12-09 20:02:13', :p10)'), SQL: '
INSERT INTO article_data_mime_attachment (article_id, filename, content_type, content_size,
content, content_id, content_alternative, disposition, create_time, create_by,
change_time, change_by)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, '2019-12-09 20:02:13', ?, '2019-12-09 20:02:13', ?)'
[Mon Dec 9 15:02:13 2019][Error][Kernel::System::Ticket::Article::Backend::MIMEBase::ArticleStorageDB::ArticleWriteAttachment][252] ORA-04098: trigger 'OTRS.SE_ARTICLE_ATTACHMENT_T' is invalid and failed re-validation (DBD ERROR: error possibly near <*> indicator at char 25 in '
INSERT INTO <*>article_data_mime_attachment (article_id, filename, content_type, content_size,
content, content_id, content_alternative, disposition, create_time, create_by,
change_time, change_by)
VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, '2019-12-09 20:02:13', :p9, '2019-12-09 20:02:13', :p10)'), SQL: '
INSERT INTO article_data_mime_attachment (article_id, filename, content_type, content_size,
content, content_id, content_alternative, disposition, create_time, create_by,
change_time, change_by)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, '2019-12-09 20:02:13', ?, '2019-12-09 20:02:13', ?)'

I did some analysis with v6 vanilla schema and upgraded v6 schema and found that we are missing the below mentioned seq/triggers.

Triggers:

SE_USERS_T
SE_ARTICLE_DATA_MIME_PLAIN_T -
SE_ARTICLE_DATA_MIME_ATTAC4B_T

Sequences:
SE_USERS

So, we re-created missing sequences and triggers but found that sequence gets dropped later and triggers gets to invalid state. Now, I'm not sure what exactly would be the cause of this issue and where exactly to look into to fix it.

I highly appreciate if anyone can provide any leads or solved a similar type of instance while upgrading v5 to v6.


Thanks,
M
Post Reply