[critical]: DBD::Pg::st execute failed

RT Users
1

I’m migrating from a RHEL 6, PGSQL 9, RT 4.4.1 to RHEL 8.5, PGSQL 11, RT 5.0.2. I have done the basic install and can connect to the server by running /opt/rt/sbin/rt-server, and make a test ticket. I’m not sure of the order of operations from here.

I exported my database from the old server, and imported it into the new one. However, I do not see any of the old tickets - just the new, test ticket #1 that I created. I decided to try to upgrade the database. Attempting to go from 4.4.1 → 5.0.2 results in the following error:

[1858307] [Mon Apr 4 21:14:47 2022] [critical]: DBD::Pg::st execute failed: ERROR: column “uniquevalues” of relation “customfields” already exists at /home/nikolaidisp/rt-5.0.2/sbin/…/lib/RT/Handle.pm line 551. (/home/nikolaidisp/rt-5.0.2/sbin/…/lib/RT.pm:409)
DBD::Pg::st execute failed: ERROR: column “uniquevalues” of relation “customfields” already exists at /home/nikolaidisp/rt-5.0.2/sbin/…/lib/RT/Handle.pm line 551.
make: *** [Makefile:393: upgrade-database] Error 255

Attempting to skip 4.4.1 and start at 4.4.2 results in:
Processing 4.5.0
Now populating database schema.
[1858406] [Mon Apr 4 21:17:18 2022] [critical]: DBD::Pg::st execute failed: ERROR: column “hotlist” of relation “classes” does not exist at /home/nikolaidisp/rt-5.0.2/sbin/…/lib/RT/Handle.pm line 551. (/home/nikolaidisp/rt-5.0.2/sbin/…/lib/RT.pm:409)
DBD::Pg::st execute failed: ERROR: column “hotlist” of relation “classes” does not exist at /home/nikolaidisp/rt-5.0.2/sbin/…/lib/RT/Handle.pm line 551.
make: *** [Makefile:393: upgrade-database] Error 255

I’m not sure how to proceed or if I even have the order of operations (install PGSQL, restore from backup, upgrade database?) right.

Thanks,

Peter

3

Hi, did you find a solution to your issue?
I have the same error.

I’m migrating from Ubuntu 16.04, 10.0.38-MariaDB, RT 4.4.1
to Debian 11, PGSQL 13, RT 5.0.3.

DB migration I did by the following procedure #Migrating-to-a-Different-Database

My question is do I need to run “make upgrade-database” when I finished the rt-importer script?

For the record, I see my tickets from the old RT system but when I try to run “make upgrade-database”
I got the following error:
[83183] [Mon Nov 14 01:09:09 2022] [critical]: DBD::Pg::st execute failed: ERROR: column “uniquevalues” of relation “customfields” already exists at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Handle.pm line 551. (/root/downloads/rt-5.0.3/sbin/…/lib/RT.pm:409)
DBD::Pg::st execute failed: ERROR: column “uniquevalues” of relation “customfields” already exists at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Handle.pm line 551.
make: *** [Makefile:393: upgrade-database] Error 255

4

Which upgrade step (version) is this failing on when running make upgrade?

5

Proceed [y/N]:y
Processing 4.4.2
Now populating database schema.

6

Since the relationship already exists (maybe an extension added it?) I’d say you can remove line 1 of etc/upgrade/4.4.2/schema.Pg and maybe line 2 too if that already exists and move on with the upgrade

7

I dropped the COLUMNs uniquevalues and canonicalizeclass.
I don’t use the customfields.
So now upgrade break on step 4.5.1.
Proceed [y/N]:y
Processing 4.4.2
Now populating database schema.
Now inserting database indexes.
Now inserting data.
Processing 4.4.5
Now inserting data.
Processing 4.4.6
Now populating database schema.
Now inserting database indexes.
Processing 4.5.0
Now populating database schema.
Processing 4.5.1
Now populating database schema.
[94993] [Mon Nov 14 17:26:01 2022] [critical]: DBD::Pg::st execute failed: ERROR: relation “configurations_id_seq” already exists at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Handle.pm line 551. (/root/downloads/rt-5.0.3/sbin/…/lib/RT.pm:409)
DBD::Pg::st execute failed: ERROR: relation “configurations_id_seq” already exists at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Handle.pm line 551.
make: *** [Makefile:393: upgrade-database] Error 255

8

Same thing, try removing line #1 from etc/upgrade/4.5.1/schema.Pg

9

Processing 4.5.8
Now inserting data.
[95520] [Mon Nov 14 18:21:12 2022] [warning]: Couldn’t run SQL query: ALTER TABLE AttachmentsIndex ALTER COLUMN id SET DATA TYPE BIGINT: ERROR: relation “attachmentsindex” does not exist at /root/downloads/rt-5.0.3/etc/upgrade/4.5.8/content line 19. (/root/downloads/rt-5.0.3/etc/upgrade/4.5.8/content:19)

10

Looks like this upgrade step handles fulltext search, you must have it enabled. but maybe never actually set it up?

11

Thanks a lot for your answers.

Do you mean “fulltext search” enabled, is it?
/opt/rt5/etc/RT_SiteConfig.d/FulltextIndex.pm
Set( %FullTextSearch,
Enable => 1,
Indexed => 1,
Column => ‘ContentIndex’,
Table => ‘AttachmentsIndex’,
);

Do I need to disable it before the upgrade?

12

You can disable it or run sbin/rt-setup-fulltext-index to actually setup the index

13

Thank you very much!
DB upgrade is done. :tada:

May I ask you one more question?
I got the following errors:
[96169] [Mon Nov 14 19:45:13 2022] [warning]: Lifecycle incidents is missing in %Lifecycles config at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm line 645. (/root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm:645)
[96169] [Mon Nov 14 19:45:13 2022] [warning]: Lifecycle incident_reports is missing in %Lifecycles config at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm line 645. (/root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm:645)
[96169] [Mon Nov 14 19:45:13 2022] [warning]: Lifecycle blocks is missing in %Lifecycles config at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm line 645. (/root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm:645)
[96169] [Mon Nov 14 19:45:13 2022] [warning]: Lifecycle investigations is missing in %Lifecycles config at /root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm line 645. (/root/downloads/rt-5.0.3/sbin/…/lib/RT/Lifecycle.pm:645)

What does it mean and how I can fix it? Thanks again for your help.

14

Do you have RTIR installed?

15

I am not using RTIR.
But I’m not sure if the RTIR was installed on the system.
I inherited this system.
Init version was 3.8.4 (Thu Nov 14 18:59:25 2013).
The system has gone through a lot upgrades.

16

Yeah basically that warning suggests that somewhere in the DB there are queues (likely disabled) that have a lifecycle that is no longer supported as the config has been removed

17

I found this section in RT_Config.pm
Set(%Lifecycles,
Is it?

Also, I found disabled Queues “incident_reports”, “investigations”.

How remove it clean?

18

You don’t want to edit RT_Config.pm, I don’t think it really matters if this lifecycle is missing. You could look to install RTIR if you really wanted to remove the warnings or delete the old tickets/queues.

This is the config for RTIR

19

I feel “blocks” is very old as its no longer included in the lifecycle config

20

I want to remove the warnings on the old system and migrate to the new one without warnings.
Can you recommend the best way?

21

I solved the problem with Lifecycles warnings.
By removing queues Blocks, “Incident Reports”, Incidents, and Investigations on the upgraded version of RT.
I removed it with the tool Shredder.
Thank you for your help.