Folks,
I’ve had no replies to my previous two posts to -users… I guess it’s
either
a) three strikes and I’m out, or
b) third time lucky 
Please let it be b) !!!
So the export completed, and I have a sexy new RT3 instance. Only, I’m not
able to create tickets by sending mail to it. Syslog logs only:
Mar 1 17:58:22 polaris RT: Couldn’t create a ticket
(/usr/local/rt3/lib/RT/Ticket_Overlay.pm:519)
Mar 1 17:58:22 polaris RT: Ticket could not be created due to an internal
error (/usr/local/rt3/lib/RT/Interface/Email.pm:678)
Mar 1 17:58:23 polaris RT: Create failed: 0 / 0 / Ticket could not be
created due to an internal error
(/usr/local/rt3/lib/RT/Interface/Email.pm:684)
Digging more deeply, I see the following in the HTTP logs:
[Mon Mar 1 17:58:22 2004] [error] [client 216.152.199.8] FastCGI: server
“/usr/local/rt3/bin/mason_handler.fcgi” stderr: DBD::Oracle::st execute
failed: ORA-00001: unique constraint (RT3_IFAX.TICKETS_KEY) violated (DBD
ERROR: OCIStmtExecute) [for Statement “INSERT INTO Tickets (Status, Queue,
InitialPriority, Type, Started, Starts, id, LastUpdated, Subject,
FinalPriority, Creator, Owner, LastUpdatedBy, Resolved, Created, Priority,
Due) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)” with
ParamValues: :p5=‘1970-01-01 00:00:00’, :p12=‘10’, :p8=‘2004-03-02
01:58:22’, :p14=‘1970-01-01 00:00:00’, :p10=‘50’, :p13=‘1182’, :p16=‘50’,
:p2=‘6’, :p3=‘50’, :p6=‘1970-01-01 00:00:00’, :p15=‘2004-03-02 01:58:22’,
:p1=‘new’, :p7=‘4’, :p17=‘2004-03-07 01:58:22’, :p4=‘ticket’, :p9=‘tyest’,
:p11=‘1182’] at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm
line 410.
From the archives, I see that this might have something to do with sequences
not being setup properly. If I read the above query and its bind values
properly, it’s trying to create ticket with ‘id = 4’, which is clearly not
going to work on this several thousand ticket database. Looking at all of my
sequences, I see they’re presently at:
Sequence Value
ACL_SEQ 121
ATTACHMENTS_SEQ 1
CACHEDGROUPMEMBERS_SEQ 45701
CUSTOMFIELDS_SEQ 1
CUSTOMFIELDVALUES_SEQ 1
GROUPMEMBERS_SEQ 15921
GROUPS_SEQ 1
LINKS_SEQ 61
PRINCIPALS_SEQ 19701
QUEUES_SEQ 21
SCRIPACTIONS_SEQ 21
SCRIPCONDITIONS_SEQ 21
SCRIPS_SEQ 41
TEMPLATES_SEQ 21
TICKETCUSTOMFIELDVALUES_SEQ 1
TICKETS_SEQ 5 (note that this used to be 4 … I tried another test and it
incremented)
TRANSACTIONS_SEQ 1
USERS_SEQ 1
(Some of the values might be a little off, since Oracle assigns sequences in
blocks of 20 and lets them sit around in memory)
I’m not certain how many of these look suspicious, but clearly the
TICKETS_SEQ is wrong. So I dropped that sequence, and created a new one with
an initial of 4000, well clear of my last ticket. I mailed RT, and this time
a ticket was at least partly created! happy dance I got three emails from
RT - an autoreply as the creator, a notification of the new ticket as the
watcher, and finally a message from RT saying the ticket creation had
failed, even though the previous messages clearly indicated ticket 4000 was
partly born. One odd thing I did notice … both the autoreply and the
message to the watcher said ‘this ticket has no content’.
Here’s the first part of the apache log now (a messy error cascade followed
this one, but is not relevant here):
[Mon Mar 1 18:35:38 2004] [error] [client 216.152.199.8] FastCGI: server
“/usr/local/rt3/bin/mason_handler.fcgi” stderr: DBD::Oracle::st execute
failed: ORA-00001: unique constraint (RT3_IFAX.TRANSACTIONS_KEY) violated
(DBD ERROR: OCIStmtExecute) [for Statement “INSERT INTO Transactions (Type,
Field, id, OldValue, Ticket, NewValue, Creator, Created, Data) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?)” with ParamValues: :p5=‘4002’, :p6=undef, :p3=‘3’,
:p7=‘1182’, :p1=‘Create’, :p8=‘2004-03-02 02:35:38’, :p4=undef, :p9=undef,
:p2=undef] at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm
line 410.
Arg, another messed up sequence - the MySQL from RT3 has over 24,000
transactions, and we’re starting at ‘1’ here.
I guess I can go through and manually set up the sequences to be more sane,
but if this is intended behaviour I think there should be something in the
import tool’s README. Jesse can you comment?
Also, if I can try your patience for one more second, I know I have seen it
mentioned here but there’s never been any official response. Is it normal
for the GROUPMEMBERS_SEQ to be so large? RT2 claims to only have 19 rows in
GroupMembers.
I look forward to hearing from anyone … please?
-Darren
Darren Nickerson
Senior Sales & Support Engineer
iFAX Solutions, Inc. www.ifax.com
darren.nickerson@ifax.com
+1.215.438.4638 ext 8106 office
+1.215.243.8335 fax