Difficulties converting mySQL to Oracle

Spradling_LeAnn · November 20, 2014, 3:46pm

Hi All.

We are on 4.0.6 and tried to convert our mySQl database to Oracle. While we seem to be able to read from the database, and update old tickets in some cases, we can’t create new tickets or users. Any tips appreciated.

We did a mysqldump and imported using Oracle SQL Loader. Our DBAs found a sequence error and corrected it, a number of MYSQL-type delimiters were removed, but we are still getting stuff like the below.

[Thu Nov 20 15:34:33 2014] [crit]: Couldn’t create a Principal on new user create. Strange things are afoot at the circle K (/public/server/apps/rt-home/sbin/…/lib/RT/Group.pm:456)
[Thu Nov 20 15:34:33 2014] [error]: Couldn’t create a ticket group of type ‘Requestor’ for ticket 49782: Could not create group (/public/server/apps/rt-home/sbin/…/lib/RT/Ticket.pm:1002)
[Thu Nov 20 15:34:33 2014] [crit]: Couldn’t create ticket groups for ticket 49782. aborting Ticket creation. (/public/server/apps/rt-home/sbin/…/lib/RT/Ticket.pm:522)
[Thu Nov 20 15:34:33 2014] [error]: WebRT: Ticket could not be created due to an internal error (/public/server/apps/rt-4.0.6_oracle_attempt_second_initialize/share/html/Elements/Error:82)

thanks

LeAnn Spradling | Manager
CAP EPA CAM | CGI Federal
2800 Meridian Pkwy, Durham, NC 27713
T: 919-294-3721 |M: 919-260-2750 | spradling.leann@epa.gov

Kevin_Falcone · November 21, 2014, 10:41pm

We did a mysqldump and imported using Oracle SQL Loader. Our DBAs found a
sequence error and corrected it, a number of MYSQL-type delimiters were
removed, but we are still getting stuff like the below.

I have to start by saying this is not something we’d considered a
‘supported migration technique’. Especially given your errors.

[Thu Nov 20 15:34:33 2014] [crit]: Couldn’t create a Principal on new user
create. Strange things are afoot at the circle K (/public/server/apps/rt-home/
sbin/…/lib/RT/Group.pm:456)

Your DBAs should compare

github.com

bestpractical/rt/blob/maint/etc/schema.Oracle


CREATE SEQUENCE ATTACHMENTS_seq;
CREATE TABLE Attachments (
        id              NUMBER(11,0) 
                        CONSTRAINT Attachments_Key PRIMARY KEY,
        TransactionId   NUMBER(11,0) NOT NULL,
        Parent          NUMBER(11,0) DEFAULT 0 NOT NULL, 
        MessageId       VARCHAR2(160),
        Subject         VARCHAR2(255),
        Filename        VARCHAR2(255),
        ContentType     VARCHAR2(80),
        ContentEncoding VARCHAR2(80),
        Content         CLOB,
        Headers         CLOB,
        Creator         NUMBER(11,0) DEFAULT 0 NOT NULL,
        Created         DATE
);
CREATE INDEX Attachments2 ON Attachments (TransactionId);
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
CREATE INDEX Attachments4 ON Attachments (Filename);

This file has been truncated. show original

to what you have, you’re missing or have incorrect sequences.

-kevin

Spradling_LeAnn · November 22, 2014, 12:26am

Thanks. We discovered the sequence problems but I will pass that to our DBAs.

We will have to repeat this process for our prod environment, so any suggestions or pointers to documentation for supported/better migration techniques would be welcomed. This document was the most detailed I found:

http://requesttracker.wikia.com/wiki/MySQLToOracle

LeAnn Spradling | Manager
CAP EPA CAM | CGI Federal
2800 Meridian Pkwy, Durham, NC 27713
T: 919-294-3721 |M: 919-260-2750 | spradling.leann@epa.govFrom: rt-users [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Kevin Falcone
Sent: Friday, November 21, 2014 5:41 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Difficulties converting mySQL to Oracle

We did a mysqldump and imported using Oracle SQL Loader. Our DBAs
found a sequence error and corrected it, a number of MYSQL-type
delimiters were removed, but we are still getting stuff like the below.

I have to start by saying this is not something we’d considered a ‘supported migration technique’. Especially given your errors.

[Thu Nov 20 15:34:33 2014] [crit]: Couldn’t create a Principal on new
user create. Strange things are afoot at the circle K
(/public/server/apps/rt-home/
sbin/…/lib/RT/Group.pm:456)