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

Lee Damon nomad at ee.washington.edu
Mon Sep 29 16:41:14 EDT 2014


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/. at sys/pkgs/rt/.4.2.7/sbin/../lib/RT/I18N/cs.pm 
is tainted. not loading at 
/afs/ee.washington.edu/nikola/. at sys/pkgs/rt/.4.2.7/sbin/../lib/RT/I18N.pm line 
105. 
(/afs/ee.washington.edu/nikola/. at 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/. at 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/. at 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



More information about the rt-users mailing list