[rt-users] 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/upgra
Re: [rt-users] DBD::Pg::st execute failed: ERROR: duplicate key value violates unique constraint "articles_pkey" at [...]/DBIx/SearchBuilder/Handle.pm
On 9/30/14, 00:09 , Joop wrote: > 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 -- RT Training November 4 & 5 Los Angeles http://bestpractical.com/training
Re: [rt-users] DBD::Pg::st execute failed: ERROR: duplicate key value violates unique constraint "articles_pkey" at [...]/DBIx/SearchBuilder/Handle.pm
On 10/1/14, 07:43 , Joop van de Wege wrote: >> 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 -- RT Training November 4 & 5 Los Angeles http://bestpractical.com/training
[rt-users] ...lib/RT/I18N/cs.pm is tainted. not loading...
tl;dr: I'm attempting to upgrade from RT 3.6.4 to RT 4.2.9. After running make upgrade-database I get the following errors to pretty much every command: [6313] [Tue Feb 17 17:00:00 2015] [warning]: /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/cs.pm is tainted. not loading at /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line 105. (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105) [6313] [Tue Feb 17 17:00:00 2015] [warning]: /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/de.pm is tainted. not loading at /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line 105. (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105) [6313] [Tue Feb 17 17:00:00 2015] [warning]: /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/fr.pm is tainted. not loading at /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line 105. (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105) [6313] [Tue Feb 17 17:00:00 2015] [warning]: /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/i_default.pm is tainted. not loading at /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line 105. (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105) [6313] [Tue Feb 17 17:00:00 2015] [warning]: /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/ru.pm is tainted. not loading at /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line 105. (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105) After that error message is presented I am unable to run "rt-fulltext-indexer --all" because it claims an indexer is already running. Long form: I'm using the following command to copy the existing database from the production server to the test server: pg_dump -U apache rt3 > rt3.sql I then copy rt3.sql to the test server and do the following steps to upgrade: # delete old database on chum: sudo -u postgres psql drop database rt4; \q # create empty database sudo rm -rf /var/pkgs/rt/var/*/* sudo /usr/nikola/pkgs/rt/sbin/rt-setup-database --action create,acl (I note that when I run rt-setup-database I get the following warnings: [3943] [Tue Feb 17 16:44:34 2015] [warning]: DBD::Pg::st execute failed: ERROR: relation "attachments_id_seq" does not exist at /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/Handle.pm line 452, line 1. (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/Handle.pm:452) [3943] [Tue Feb 17 16:44:34 2015] [critical]: DBD::Pg::st execute failed: ERROR: relation "attachments_id_seq" does not exist at /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/Handle.pm line 452, line 1. (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT.pm:388) but as they are warnings I hope they're not fatal.) Proceeding: #import database on chum: sudo -u postgres psql rt4 < /s0/nomad/rt3.sql # verify that the database is rt4 psql --list --username=apache # upgrade from 3.x to 4.x sudo /etc/init.d/httpd stop cd /s0/nomad/as60-amd64-rt-4.2.9 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 perl -I /usr/nikola/pkgs/rt/local/lib -I /usr/nikola/pkgs/rt/lib sbin/rt-setup-fulltext-index *** This is the point where I start seeing the tainted messages in the log. Note that this isn't the only time I see the taint errors, I get them regularly from now on. sudo perl -I /usr/nikola/pkgs/rt/local/lib -I /usr/nikola/pkgs/rt/lib sbin/rt-fulltext-indexer --all *** At this point I am informed an indexer is running (it isn't) and nothing happens. I have a bunch more steps I use to finish upgrading the database but they're after the error message so I'm including them only for completeness. Many of these steps also kick out the above taint errors. 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 (replace user 0 with 25) sudo etc/upgrade/switch-templates-to html Does anyone have any hints on where I can poke to figure out what is causing this problem? thanks, nomad
Re: [rt-users] ...lib/RT/I18N/cs.pm is tainted. not loading...
I found the problem with the "tainted" I18N/*.pm errors. It seems
I18N.pm does a path element check but doesn't know about AFS's @sys path
elements so doesn't allow for @ in names. This is the very minor change
I made to enable that. I don't know if this is going to cause problems
elsewhere, can anyone verify this for me?
: || nomad@silverfox as60-amd64-4.2.9 [77] ; diff -c lib/RT/I18N.pm
lib/RT/I18N.pm.orig
*** lib/RT/I18N.pm 2015-02-18 09:27:14.395298000 -0800
--- lib/RT/I18N.pm.orig 2015-02-18 09:26:23.718504000 -0800
***
*** 101,107
# Load language-specific functions
foreach my $file ( File::Glob::bsd_glob(substr(__FILE__, 0, -3) .
"/*.pm") ) {
! unless ( $file =~ /^([-\w\s\.\/\\\@~:]+)$/ ) {
warn("$file is tainted. not loading");
next;
}
--- 101,107
# Load language-specific functions
foreach my $file ( File::Glob::bsd_glob(substr(__FILE__, 0, -3) .
"/*.pm") ) {
! unless ( $file =~ /^([-\w\s\.\/\\~:]+)$/ ) {
warn("$file is tainted. not loading");
next;
}
Sadly, this doesn't solve the problem with "rt-fulltext-indexer --all"
thinking an indexer is already running (nor does it solve my previously
posted problems with the bodies of articles not being filled in when the
article is first created).
nomad
On 2/17/15 09:29 , Lee Damon wrote:
> tl;dr: I'm attempting to upgrade from RT 3.6.4 to RT 4.2.9. After
> running make upgrade-database I get the following errors to pretty much
> every command:
>
> [6313] [Tue Feb 17 17:00:00 2015] [warning]:
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/cs.pm
> is tainted. not loading at
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line
> 105.
> (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105)
> [6313] [Tue Feb 17 17:00:00 2015] [warning]:
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/de.pm
> is tainted. not loading at
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line
> 105.
> (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105)
> [6313] [Tue Feb 17 17:00:00 2015] [warning]:
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/fr.pm
> is tainted. not loading at
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line
> 105.
> (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105)
> [6313] [Tue Feb 17 17:00:00 2015] [warning]:
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/i_default.pm
> is tainted. not loading at
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line
> 105.
> (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105)
> [6313] [Tue Feb 17 17:00:00 2015] [warning]:
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N/ru.pm
> is tainted. not loading at
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm line
> 105.
> (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/I18N.pm:105)
>
> After that error message is presented I am unable to run
> "rt-fulltext-indexer --all" because it claims an indexer is already running.
>
>
> Long form:
>
> I'm using the following command to copy the existing database from the
> production server to the test server:
>
> pg_dump -U apache rt3 > rt3.sql
>
> I then copy rt3.sql to the test server and do the following steps to
> upgrade:
>
> # delete old database on chum:
> sudo -u postgres psql
> drop database rt4;
> \q
>
> # create empty database
> sudo rm -rf /var/pkgs/rt/var/*/*
> sudo /usr/nikola/pkgs/rt/sbin/rt-setup-database --action create,acl
>
>
> (I note that when I run rt-setup-database I get the following warnings:
>
> [3943] [Tue Feb 17 16:44:34 2015] [warning]: DBD::Pg::st execute failed:
> ERROR: relation "attachments_id_seq" does not exist at
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/Handle.pm
> line 452, line 1.
> (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/Handle.pm:452)
> [3943] [Tue Feb 17 16:44:34 2015] [critical]: DBD::Pg::st execute
> failed: ERROR: relation "attachments_id_seq" does not exist at
> /afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT/Handle.pm
> line 452, line 1.
> (/afs/ee.washington.edu/nikola/.@sys/pkgs/rt/.4.2.9/sbin/../lib/RT.pm:388)
>
> but as they are warnings I hope they're not fatal.)
>
> Proceeding:
>
> #import database on chum:
[rt-users] flock in sbin/rt-fulltext-indexer fails when RT installed on RO filesystem
I'm trying to install RT 4.2.9 into a volume in OpenAFS. I have the vast
majority of it working fine (with the patch I posted last week adding @
to the acceptable characters in a path in lib/RT/I18N.pm). However,
there is one problem I'm still seeing.
This chunk of code in sbin/rt-fulltext-indexer works fine when the
script is on a RW filesystem but fails when the file is in a RO
filesystem like AFS:
use Fcntl ':flock';
if ( !flock main::DATA, LOCK_EX | LOCK_NB ) {
if ( $OPT{quiet} ) {
RT::Logger->info("$0 is already running; aborting silently, as
requested");
exit;
}
else {
print STDERR "$0 is already running\n";
exit 1;
}
}
I'm not a Perl writer (or even reader, really) so I don't know if it
would be an easy change to add an optional command line argument to let
the admin specify a lock file (e.g. /var/lock/rt-fulltext-indexer) for
it to use instead of flocking itself.
nomad
