Ticket creation error after using migration scripts

RT Users
1

Hi all.

I’ve just spent an exciting few days moving our RT installation from Red Hat
7.0, RT 2.0.14, with a pile of CPAN installed with chewing gum and straw, to
Red Hat 8.0, RT 3.0.0 (on httpd 2.0.40, mod_perl 1.99 and postgresql 7.3.2).
Atually most of it went smoothly, I would highly recommend cpan2rpm for those
package management control freaks ( http://perl.arix.com/cpan2rpm/man.html ).

After installing the new rt3 code, and running make initialize-database, I
can create tickets from the web interface and from rt-mailgate.

Then I did a make dropdb (to clear out these dummy tickets) and make
initialize-database again.

I used rt2-to-rt3 1.7 to dump the old database from the old machine, and
reimported it without errors into the new database.

Now, I’ve logged in with the username and password from rt2 (so the import
must have worked), and checked my ACLs:

Everyone can CreateTicket, CommentOnTicket, ReplyToTicket, and SeeQueue
Requestor can ShowTicket
staff can ModifyTicket, OwnTicket, ShowTicket, ShowTicketComments, Watch,
WatchAsAdminCC
and finally, my own user has most of the other rights enabled.

I create the ticket by clicking on the New ticket in button, and pressing
Create on the next page.

RT3 responds with:

RT Error
Ticket could not be created due to an internal error

and rt.log has the following:

[Mon Apr 7 07:49:08 2003] [warning]: DBD::Pg::st execute failed: ERROR: Cannot insert a duplicate key into unique index tickets_pkey at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query ‘INSERT INTO Tickets (Owner, Type, InitialPriority, LastUpdatedBy, Resolved, Starts, Subject, Status, Created, FinalPriority, Priority, Queue, Due,
LastUpdated, Creator) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query 'UPDATE Tickets SET EffectiveId=? WHERE id=? ’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [err]: RT::Ticket=HASH(0x97796d0) ->Create couldn’t set EffectiveId: EffectiveId could not be set to 0.
(/opt/rt3/lib/RT/Ticket_Overlay.pm:490)
[Mon Apr 7 07:49:09 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query ‘INSERT INTO Principals (PrincipalType, Disabled, ObjectId) VALUES (?, ?, ?)’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query 'UPDATE Principals SET ObjectId=? WHERE id=? ’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query ‘INSERT INTO Groups (Type, Instance, Id, Domain, Name, Description)
VALUES (?, ?, ?, ?, ?, ?)’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [error]: Couldn’t create a ticket group of type ‘Requestor’ for ticket : Could not create group (/opt/rt3/lib/RT/Ticket_Overlay.pm:1282)
[Mon Apr 7 07:49:09 2003] [crit]: Couldn’t create ticket groups for ticket . aborting Ticket creation. (/opt/rt3/lib/RT/Ticket_Overlay.pm:495)
[Mon Apr 7 07:49:09 2003] [error]: WebRT: Ticket could not be created due to an internal error () (/opt/rt3/share/html/Elements/Error:53)

I am guessing this is a problem with ACLs, possibly, because the only thing
that has changed is the contents of the database.

jaq@spacepants.org http://spacepants.org/jaq.gpg

2

[Mon Apr 7 07:49:08 2003] [warning]: DBD::Pg::st execute failed: ERROR:
Cannot insert a duplicate key into unique index tickets_pkey at
/usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.

When I had this problem, it went away after re-indexing the database.

The procedure is outlined in reindex(7). Essentially, shut down httpd,
sendmail (or whatever SMTP listener you use) and postgres, then

su - postgres
postgres -D $PGDATA -O -P rt2 # Your database name here
REINDEX DATABASE rt2 FORCE;

and restarting everything did the trick for me.

Binand

3

Ack. that needs to get into the upgrade notes. See the RT1 to RT2
importer for the explanation of what postgres is losing on.
you need to manually increment the sequence counters.

-jOn Mon, Apr 07, 2003 at 05:57:27PM +1000, Jamie Wilkinson wrote:

Hi all.

I’ve just spent an exciting few days moving our RT installation from Red Hat
7.0, RT 2.0.14, with a pile of CPAN installed with chewing gum and straw, to
Red Hat 8.0, RT 3.0.0 (on httpd 2.0.40, mod_perl 1.99 and postgresql 7.3.2).
Atually most of it went smoothly, I would highly recommend cpan2rpm for those
package management control freaks ( http://perl.arix.com/cpan2rpm/man.html ).

After installing the new rt3 code, and running make initialize-database, I
can create tickets from the web interface and from rt-mailgate.

Then I did a make dropdb (to clear out these dummy tickets) and make
initialize-database again.

I used rt2-to-rt3 1.7 to dump the old database from the old machine, and
reimported it without errors into the new database.

Now, I’ve logged in with the username and password from rt2 (so the import
must have worked), and checked my ACLs:

Everyone can CreateTicket, CommentOnTicket, ReplyToTicket, and SeeQueue
Requestor can ShowTicket
staff can ModifyTicket, OwnTicket, ShowTicket, ShowTicketComments, Watch,
WatchAsAdminCC
and finally, my own user has most of the other rights enabled.

I create the ticket by clicking on the New ticket in button, and pressing
Create on the next page.

RT3 responds with:

RT Error
Ticket could not be created due to an internal error

and rt.log has the following:

[Mon Apr 7 07:49:08 2003] [warning]: DBD::Pg::st execute failed: ERROR: Cannot insert a duplicate key into unique index tickets_pkey at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query ‘INSERT INTO Tickets (Owner, Type, InitialPriority, LastUpdatedBy, Resolved, Starts, Subject, Status, Created, FinalPriority, Priority, Queue, Due,
LastUpdated, Creator) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query 'UPDATE Tickets SET EffectiveId=? WHERE id=? ’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:08 2003] [err]: RT::Ticket=HASH(0x97796d0) ->Create couldn’t set EffectiveId: EffectiveId could not be set to 0.
(/opt/rt3/lib/RT/Ticket_Overlay.pm:490)
[Mon Apr 7 07:49:09 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query ‘INSERT INTO Principals (PrincipalType, Disabled, ObjectId) VALUES (?, ?, ?)’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query 'UPDATE Principals SET ObjectId=? WHERE id=? ’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: DBD::Pg::st execute failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 376.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [warning]: RT::Handle=HASH(0x93501d0) couldn’t execute the query ‘INSERT INTO Groups (Type, Instance, Id, Domain, Name, Description)
VALUES (?, ?, ?, ?, ?, ?)’ at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 383.
(/opt/rt3/lib/RT.pm:222)
[Mon Apr 7 07:49:09 2003] [error]: Couldn’t create a ticket group of type ‘Requestor’ for ticket : Could not create group (/opt/rt3/lib/RT/Ticket_Overlay.pm:1282)
[Mon Apr 7 07:49:09 2003] [crit]: Couldn’t create ticket groups for ticket . aborting Ticket creation. (/opt/rt3/lib/RT/Ticket_Overlay.pm:495)
[Mon Apr 7 07:49:09 2003] [error]: WebRT: Ticket could not be created due to an internal error () (/opt/rt3/share/html/Elements/Error:53)

I am guessing this is a problem with ACLs, possibly, because the only thing
that has changed is the contents of the database.

–
jaq@spacepants.org http://spacepants.org/jaq.gpg

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

4

This one time, at band camp, Jesse Vincent wrote:

Ack. that needs to get into the upgrade notes. See the RT1 to RT2
importer for the explanation of what postgres is losing on.
you need to manually increment the sequence counters.

I’ve googled for “rt2 to rt3 importer” and the only relevant hit is the
announcement that says it’s in CVS. A bit of hunting and I found this:
http://www.fsck.com/pub/rt/contrib/2.0/rt-addons/import-1.0-to-2.0
but I can’t see in it an explanation.

Forgive me if this is a braindead question, but what do I increment the
sequence counters to?

I’m still not sure why you aren’t using postgres’s serial type for this
anyway :wink:

jaq@spacepants.org http://spacepants.org/jaq.gpg

5

This one time, at band camp, Jesse Vincent wrote:

Ack. that needs to get into the upgrade notes. See the RT1 to RT2
importer for the explanation of what postgres is losing on.
you need to manually increment the sequence counters.

Okay, for reference I’ve done the following (within psql)

rt3=# select max(id) from tickets;
max
365
(1 row)

rt3=# select setval(‘tickets_id_seq’, 366);
setval
366
(1 row)

rt3=# select max(id) from transactions;
max
2024
(1 row)

rt3=# select setval(‘transactions_id_seq’, 2025);
setval
2025
(1 row)

and that’s fixed the immediate problem. I guess I’ll have to go through the other sequence types and fix them up too.

I think that the migration script could do this though.

jaq@spacepants.org http://spacepants.org/jaq.gpg

6

-----BEGIN PGP SIGNED MESSAGE-----

On 08-Apr-2003, Jamie Wilkinson wrote message “Re: [rt-users] ticket creation
error after using migration scrip”

rt3=# select max(id) from tickets;
max

365
(1 row)

rt3=# select setval(‘tickets_id_seq’, 366);
setval

366

(1 row)

FYI, you can instead do:

rt3=# select setval(‘tickets_id_seq’, (select max(id) from tickets)+1);

Andy Harrison
(full headers for details)

-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8

iQCVAwUBP0JkhVPEkLgodAWVAQEqlAP/QlMMMU/+Ac2vlN2s4GGOazmYlx9efXep
9AqBT25O8VSFLVYeSDm4r1DmiGIrCOBsnSVUeSXyLBhyPbOq+IHx3PJ96h54NoBm
/t+RZ0bxLZFlo9k85SwTwnOe4K1kL90wkx95dyyvaq+qpOaC04wj8hVPr4R4P0uA
pu79cA3w9Xo=
=rvR8
-----END PGP SIGNATURE-----