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:
: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