DBD::Pg::st execute failed: ERROR: duplicate key value violates unique constraint "articles_pkey" at /DBIx/SearchBuilder/Handle.pm

Greetings,

TL;DR:
I am in the process of trying to migrate an ancient RT 3.6.4 version to
4.2.7. Most of the migration works – old tickets and articles can be
read, new tickets can be created via the web interface – but creation
of new articles fails with "[warning]: DBD::Pg::st execute failed:
ERROR: duplicate key value violates unique constraint “articles_pkey"
at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm
line 589.
(/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm:589)
[799] [Mon Sep 22 19:44:42 2014] [warning]: RT::Handle=HASH(0x8980380)
couldn’t execute the query ‘INSERT INTO Articles (Class, Name, Creator,
LastUpdatedBy, Summary, LastUpdated, Created) VALUES (?, ?, ?, ?, ?, ?,
?)’ at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm
line 602.”
(Full text of the logged error is at the end of this email)

I’m not a DBA and have very little (read: no real) experience with
postgres. I’m hoping I can get some pointers/help or even a potential DB
fix for this problem.

Full story:

Our RT3 (backed by postgresql 8.3.7) instance was installed and
configured by a long-since-departed SA. As far as I can tell he did a
little bit of customizing but I don’t think he made any schema changes.
I do see a rt/local/html/RTFM/Article/Edit.html file but I’m darned if I
can find the original to it to see what changes he made. (Diffing
against Article/Edit.html in 3.6.4 source renders something that makes
no sense at all. I suspect he started with an even earlier version but
can’t prove it.)

The RT4 instance is installed on a new (RHEL 6) host with
postgresql-8.4.20. To migrate the data I’ve tried several different steps:

Export:
pg_dump -U apache rt3 > rt3.sql
as well as
pg_dump --blobs --create --format=plain --file=/s0/nomad/rt3.sql
–verbose --column-inserts --disable-dollar-quoting --username=apache rt3

Import:
(Commented lines are previous attempts. Uncommented are the steps I’m
currently using. There are multiple layers of old here, I’m just leaving
them documented here to show I made the attempt.)
#sudo /usr/nikola/pkgs/rt/sbin/rt-setup-database --action create,acl
sudo -u postgres createdb rt4
#sudo -u postgres createuser apache
sudo -u postgres psql rt4 < /s0/nomad/rt3.sql
sudo -u postgres psql
ALTER DATABASE rt3 RENAME TO rt4;
\q
psql --list --username=apache
# verify that the database is rt4

upgrade:

set path to have …/pkgs/perl/5.20.0/bin first – this is where I’ve

installed all the deps
cd (rt src)
sudo make upgrade-database
# upgrade to 3.9.8
sudo /usr/nikola/pkgs/perl/.5.20.0/bin/perl
/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles
sudo make upgrade-database
# upgrade from 3.9.8 to latest
cd /usr/nikola/pkgs/rt
sudo /usr/nikola/pkgs/perl/.5.20.0/bin/perl
etc/upgrade/vulnerable-passwords --fix
sudo /usr/nikola/pkgs/perl/.5.20.0/bin/perl -I
/usr/nikola/pkgs/rt/local/lib -I /usr/nikola/pkgs/rt/lib
etc/upgrade/shrink_cgm_table.pl
sudo /usr/nikola/pkgs/perl/.5.20.0/bin/perl -I
/usr/nikola/pkgs/rt/local/lib -I /usr/nikola/pkgs/rt/lib
etc/upgrade/shrink_transactions_table.pl
sudo perl -I /usr/nikola/pkgs/rt/local/lib -I /usr/nikola/pkgs/rt/lib
etc/upgrade/4.0-customfield-checkbox-extension
sudo sbin/rt-validator --check --resolv
new user id should be 25
sudo etc/upgrade/switch-templates-to html

I have shell files of all of this if anyone has specific questions. I
see exactly two “error” lines from make upgrade-database telling me to
run etc/upgrade/upgrade-articles (which is the next thing I do so I’m
pretty sure that’s not the problem).

I see a lot of warnings from etc/upgrade/upgrade-articles in the forms
(each of these is an example of something that kicks out anywhere from
10 to a couple of hundred lines):
Applied Class ‘VLSI Computing’ globally at
/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles line 131.
(/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles:131)

Fixing ACL 344 to refer to RT::Class: The new value has been set. at 

/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles line 146.
(/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles:146)

Updated CF 3 Value for Article 4 at
/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles line 174.
(/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles:174)

Fixing Topic 1 to refer to RT::Class: The new value has been set. at
/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles line 187.
(/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles:187)

Fixing Topic 5 to apply to article: ObjectType changed from
"RT::FM::Article" to “RT::Article” at
/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles line 202.
(/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles:202)

Updating base to
fsck.com-article://nikola.ee.washington.edu/article/112: The new value
has been set. for link 1871 at
/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles line 217.
(/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles:217)

Updated Transaction 477 to point to RT::Article at
/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles line 240.
(/usr/nikola/pkgs/rt/etc/upgrade/upgrade-articles:240)

When I run sbin/rt-validator --check --resolv it starts off by
complaining that multiple I18N.pm files are tained. E.g. [warning]:
/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.7/sbin/…/lib/RT/I18N/cs.pm
is tainted. not loading at
/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.7/sbin/…/lib/RT/I18N.pm line
105.
(/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.7/sbin/…/lib/RT/I18N.pm:105)

Then goes on to complain about “A record in Principals refers to a
nonexistent record in Users.” I don’t remember removing users but it is
clearly the case. It also kicks out errors relating to:

Record #80 in Groups references a nonexistent record in Users
Record #20286 in Groups references a nonexistent record in Tickets
Record #76 in GroupMembers references a nonexistent record in Groups
Record #66 in GroupMembers references a nonexistent record in Principals
Record #194 in CachedGroupMembers references a nonexistent record in
GroupMembers
Record #193 in CachedGroupMembers references a nonexistent record in Groups
Record #78253 in Transactions references a nonexistent record in Tickets
Record #169 in Transactions references a nonexistent record in Principals
Record #45447 in Attachments references a nonexistent record in Transactions
Found an attachment without a transaction.
Record #98 in ObjectCustomFieldValues references a nonexistent record in
Articles
Record #65 in Attachments references a nonexistent record in Users
Column Creator should point to a user, but there is record #9 in table
Tickets
where it’s not true. It’s ok to replace these wrong references with id
of any user.
Record #188 in Transactions references a nonexistent record in Users
Record #20288 in Principals references a nonexistent record in Groups
Record #20290 in Groups references a nonexistent record in
CachedGroupMembers
Found a record in Groups that has no direct duplicate in
CachedGroupMembers table.

Nothing else shows up in the shell files.

A full http/error_log entry is:
[799] [Mon Sep 22 19:44:42 2014] [warning]: DBD::Pg::st execute failed:
ERROR: duplicate key value violates unique constraint "articles_pkey"
at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm
line 589.
(/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm:589)
[799] [Mon Sep 22 19:44:42 2014] [warning]: RT::Handle=HASH(0x8980380)
couldn’t execute the query ‘INSERT INTO Articles (Class, Name, Creator,
LastUpdatedBy, Summary, LastUpdated, Created) VALUES (?, ?, ?, ?, ?, ?,
?)’ at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm
line 602.

DBIx::SearchBuilder::Handle::SimpleQuery(RT::Handle=HASH(0x8980380),
“INSERT INTO Articles (Class, Name, Creator, LastUpdatedBy, Su”…, 5,
“sfd”, 25, 25, “asdf”, “2014-09-22 19:44:42”, “2014-09-22 19:44:42”,
…) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm
line 352
DBIx::SearchBuilder::Handle::Insert(RT::Handle=HASH(0x8980380),
“Articles”, “Class”, 5, “Name”, “sfd”, “Creator”, 25, “LastUpdatedBy”,
…) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle/Pg.pm
line 66

DBIx::SearchBuilder::Handle::Pg::Insert(RT::Handle=HASH(0x8980380),
“Articles”, “Created”, “2014-09-22 19:44:42”, “LastUpdated”, “2014-09-22
19:44:42”, “Summary”, “asdf”, “LastUpdatedBy”, …) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Record.pm
line 1320

DBIx::SearchBuilder::Record::Create(RT::Article=HASH(0x8db7050),
“Summary”, “asdf”, “LastUpdatedBy”, 25, “Name”, “sfd”, “Creator”, 25,
…) called at
/afs/ee.washington.edu/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/…/lib/RT/Record.pm
line 337
RT::Record::Create(RT::Article=HASH(0x8db7050), “Name”, “sfd”,
“Class”, 5, “Summary”, “asdf”) called at
/afs/ee.washington.edu/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/…/lib/RT/Article.pm
line 120
RT::Article::Create(RT::Article=HASH(0x8db7050), “Summary”,
“asdf”, “Name”, “sfd”, “Class”, 5, “Topics”, undef, …) called at
/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.6/share/html/Articles/Article/Edit.html
line 159
HTML::Mason::Commands::ANON(“id”, “new”,
“Object-RT::Article–CustomField-1-Values-Magic”, 1, “next”, “”,
“Object-RT::Article–CustomField-3-Values”, “as60-amd64”,
“RefersTo-new”, …) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Component.pm
line 138

HTML::Mason::Component::run(HTML::Mason::Component::FileBased=HASH(0x8d0a1f8),
“id”, “new”, “Object-RT::Article–CustomField-1-Values-Magic”, 1,
“next”, “”, “Object-RT::Article–CustomField-3-Values”, “as60-amd64”,
…) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Request.pm
line 1305
eval {…} called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Request.pm
line 1295
HTML::Mason::Request::comp(undef, undef, undef, “id”, “new”,
“Object-RT::Article–CustomField-1-Values-Magic”, 1, “next”, “”, …)
called at
/afs/ee.washington.edu/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/…/local/lib/RT/Interface/Web.pm
line 684
RT::Interface::Web::ShowRequestedPage(HASH(0x89d71f0)) called
at
/afs/ee.washington.edu/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/…/local/lib/RT/Interface/Web.pm
line 372
RT::Interface::Web::HandleRequest(HASH(0x89d71f0)) called at
/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.6/share/html/autohandler line
53
HTML::Mason::Commands::ANON(“next”, “”,
“Object-RT::Article–CustomField-3-Values”, “as60-amd64”,
“Object-RT::Article–CustomField-1-Values-Magic”, 1, “id”, “new”,
“Summary”, …) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Component.pm
line 138

HTML::Mason::Component::run(HTML::Mason::Component::FileBased=HASH(0x89d76e8),
“next”, “”, “Object-RT::Article–CustomField-3-Values”, “as60-amd64”,
“Object-RT::Article–CustomField-1-Values-Magic”, 1, “id”, “new”, …)
called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Request.pm
line 1303
eval {…} called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Request.pm
line 1295
HTML::Mason::Request::comp(undef, undef, undef, “next”, “”,
“Object-RT::Article–CustomField-3-Values”, “as60-amd64”,
“Object-RT::Article–CustomField-1-Values-Magic”, 1, …) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Request.pm
line 484
eval {…} called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Request.pm
line 484
eval {…} called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Request.pm
line 436

HTML::Mason::Request::exec(RT::Interface::Web::Request=HASH(0x8559518))
called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/PSGIHandler.pm
line 96
eval {…} called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/PSGIHandler.pm
line 96

HTML::Mason::Request::PSGI::exec(RT::Interface::Web::Request=HASH(0x8559518))
called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/Interp.pm
line 345
HTML::Mason::Interp::exec(undef, undef, “next”, “”,
“Object-RT::Article–CustomField-3-Values”, “as60-amd64”,
“Object-RT::Article–CustomField-1-Values-Magic”, 1, “id”, …) called
at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/PSGIHandler.pm
line 59
eval {…} called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/PSGIHandler.pm
line 59

HTML::Mason::PSGIHandler::invoke_mason(HTML::Mason::PSGIHandler::Streamy=HASH(0x873f178),
HASH(0x89e6128), HASH(0x892fbf8)) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/HTML/Mason/PSGIHandler/Streamy.pm
line 52
HTML::Mason::PSGIHandler::Streamy::ANON(CODE(0x8b07150))
called at
/afs/ee.washington.edu/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/…/lib/RT/Interface/Web/Handler.pm
line 312
RT::Interface::Web::Handler::ANON(CODE(0x8b07150)) called
at /usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/Plack/Util.pm line 301
Plack::Util::ANON(CODE(0x8993a18)) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/Plack/Handler/FCGI.pm
line 136
Plack::Handler::FCGI::run(Plack::Handler::FCGI=HASH(0x88aeb50),
CODE(0x88ae508)) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/Plack/Loader.pm line 84
Plack::Loader::run(Plack::Loader=HASH(0x889ae20),
Plack::Handler::FCGI=HASH(0x88aeb50)) called at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/Plack/Runner.pm line 277
Plack::runner::run(RT::PlackRunner=HASH(0x220d5f0)) called at
/afs/ee.washington.edu/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/…/lib/RT/PlackRunner.pm
line 141
eval {…} called at
/afs/ee.washington.edu/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/…/lib/RT/PlackRunner.pm
line 141
RT::PlackRunner::run(RT::PlackRunner=HASH(0x220d5f0)) called at
/usr/nikola/.as60-amd64/pkgs/rt/.4.2.6/sbin/rt-server.fcgi line 162
(/usr/nikola/pkgs/perl/.5.20.0/lib/5.20.0/Carp.pm:169)

thanks,
nomad

Greetings,

TL;DR:
I am in the process of trying to migrate an ancient RT 3.6.4 version
to 4.2.7. Most of the migration works – old tickets and articles can
be read, new tickets can be created via the web interface – but
creation of new articles fails with “[warning]: DBD::Pg::st execute
failed: ERROR: duplicate key value violates unique constraint
"articles_pkey” at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm
line 589.
(/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm:589)
[799] [Mon Sep 22 19:44:42 2014] [warning]: RT::Handle=HASH(0x8980380)
couldn’t execute the query ‘INSERT INTO Articles (Class, Name,
Creator, LastUpdatedBy, Summary, LastUpdated, Created) VALUES (?, ?,
?, ?, ?, ?, ?)’ at
/usr/nikola/pkgs/perl/.5.20.0/lib/site_perl/5.20.0/DBIx/SearchBuilder/Handle.pm
line 602."
(Full text of the logged error is at the end of this email)

I wonder if you create a new article in the old instance if you then get
the same error.
I suspect that the sequence that populates the primary key of the
articles tables is lower than the latest article. One work around is to
set the sequence to the highest id+1 of the article table
Fire up your favourite postgres client and connect with your rt_admin to
your rt instance, do ‘select max(id) from articles;’ this should give a
number and then do ‘select last_value from articles_id_seq;’ this number
should equal the previous one but I suspect that the last one will be
lower than the first.

Regards,

Joop

I wonder if you create a new article in the old instance if you then get
the same error.

I can create a new article in the old instance without error. The test
article was #242.

I suspect that the sequence that populates the primary key of the
articles tables is lower than the latest article. One work around is to
set the sequence to the highest id+1 of the article table
Fire up your favourite postgres client and connect with your rt_admin to
your rt instance, do ‘select max(id) from articles;’ this should give a
number and then do ‘select last_value from articles_id_seq;’ this number
should equal the previous one but I suspect that the last one will be
lower than the first.

Those came up respectively as 241 (which I’d expect) and 3 (hmm).

thanks,
nomad

Do you think it would be safe to just set articles_id_seq to 242?

I tried “alter sequence articles_id_seq restart with 242;” and the
result changed.

Now an article is created but the body is empty (no matter how much I
typed into it). If I go back and modify the article I can successfully
add a body.

Nothing is logged to /var/log/httpd/error_log this time.

(One note, I’ve taken the opportunity to update from 4.2.7 to 4.2.8.)

nomad