Error with postgresql 12

[abretaud]

I tried to run a new apollo with a postgres 12.1 container, but got this error at startup:

apollo_1     | 2019-12-09 11:07:59,154 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - HHH000388: Unsuccessful: alter table allele add constraint FK_ssx0gv6xbtu3tbwt6ekkmn4iw foreign key (variant_id) references feature
apollo_1     | 2019-12-09 11:07:59,155 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - ERROR: constraint "fk_ssx0gv6xbtu3tbwt6ekkmn4iw" for relation "allele" already exists
apollo_1     | Starting Quartz Scheduler in QuartzFactoryBean
apollo_1     | 2019-12-09 11:08:23,223 [localhost-startStop-1] ERROR liquibase  - Change Set changelog-2_0_2.groovy::1454711582784-1::cmdcolin (generated) failed.  Error: Migration failed for change set changelog-2_0_2.groovy::1454711582784-1::cmdcolin (generated):
apollo_1     |      Reason: 
apollo_1     |           changelog.groovy : ERROR: column "adsrc" does not exist
apollo_1     |   Position: 205
apollo_1     | :
apollo_1     |           Caused By: Precondition Error
apollo_1     | liquibase.exception.MigrationFailedException: Migration failed for change set changelog-2_0_2.groovy::1454711582784-1::cmdcolin (generated):
apollo_1     |      Reason: 
apollo_1     |           changelog.groovy : ERROR: column "adsrc" does not exist
apollo_1     |   Position: 205
apollo_1     | :
apollo_1     |           Caused By: Precondition Error

It seems like adsrc was removed in postgres 12.
I'll just use postgresql 11.6 for now :)

[nathandunn]

Do you have a diff for the new container? It’s possible there is a jdbc or liquibase update that will fix this. Nathan

…

On Dec 9, 2019, at 3:14 AM, Anthony Bretaudeau ***@***.***> wrote:  I tried to run a new apollo with a postgres 12.1 container, but got this error at startup: apollo_1 | 2019-12-09 11:07:59,154 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate - HHH000388: Unsuccessful: alter table allele add constraint FK_ssx0gv6xbtu3tbwt6ekkmn4iw foreign key (variant_id) references feature apollo_1 | 2019-12-09 11:07:59,155 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate - ERROR: constraint "fk_ssx0gv6xbtu3tbwt6ekkmn4iw" for relation "allele" already exists apollo_1 | Starting Quartz Scheduler in QuartzFactoryBean apollo_1 | 2019-12-09 11:08:23,223 [localhost-startStop-1] ERROR liquibase - Change Set changelog-2_0_2.groovy::1454711582784-1::cmdcolin (generated) failed. Error: Migration failed for change set changelog-2_0_2.groovy::1454711582784-1::cmdcolin (generated): apollo_1 | Reason: apollo_1 | changelog.groovy : ERROR: column "adsrc" does not exist apollo_1 | Position: 205 apollo_1 | : apollo_1 | Caused By: Precondition Error apollo_1 | liquibase.exception.MigrationFailedException: Migration failed for change set changelog-2_0_2.groovy::1454711582784-1::cmdcolin (generated): apollo_1 | Reason: apollo_1 | changelog.groovy : ERROR: column "adsrc" does not exist apollo_1 | Position: 205 apollo_1 | : apollo_1 | Caused By: Precondition Error It seems like adsrc was removed in postgres 12. I'll just use postgresql 11.6 for now :) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[abretaud]

I don't have a diff, but it's in the postgresql 12 changelog: https://www.postgresql.org/docs/12/release-12.html

Remove obsolete pg_attrdef.adsrc column (Peter Eisentraut)

[nathandunn]

ah okay thanks. I'll add it to the 2.6 list

[childers]

@nathandunn @abretaud is this still open for v2.6.1?

[nathandunn]

Are you able to @childers it wasn't explicitly handled, but I don't think you'll necessarily run into it either. If so, please post the error here. It should be. a simple fix if you do see a problem.

[childers]

We're working on standing this up on an updated server. I'll keep you posted.

[childers]

Quick update. This is throwing an error on our dev server running pgsql 12, so we also are going with 11 as well.

[CarstenK]

@nathandunn

Hi,

is there a solution for this problem? I tried to install webapollo 2.6.4 on a new Ubuntu 20 server which uses pgsql 12 and got the same error.

Best,
Carsten

[hexylena]

xref #2660 I looked into why we weren't having this issue with pg13 but I don't see the adsrc column anywhere

# pg_dump postgresql://apollo:...@...:5432/apollo -s | grep adsrc -A5 -B5
#

It looks like it's down to this issue, liquibase had issues with pg12 specifically https://forum.liquibase.org/t/postgresql-12-support/4468 liquibase/liquibase#1482

Judging by a quick grep we're on 2.0.5 in our deployment /var/lib/tomcat/webapps/apollo/WEB-INF/lib/liquibase-core-2.0.5.jar

[hexylena]

In the linked issue, the person responding is on a version of liquibase much higher than ours, so, I'm also not sure why I'm not experiencing that issue.

Opening the jar, and grepping through it, I see adsrc referenced.

root@apollo:/tmp/tmp.GtaQFdHhUn$ grep adsrc -Ri  .
Binary file ./liquibase/sqlgenerator/core/SelectSequencesGeneratorPostgres.class matches

I'm forced to conclude that by virtue of migrating the database in place, we 'escaped' this bug. And that were we to ever have to re-deploy this with pg>=11, we'd probably experience it as well.

[cmdcolin]

@hexylena thanks so much for tracing this down

Is it still true that starting a blank instance on newer postgres causes issues? I can give it a try locally if I set up my dev environment but that is odd

[aturling]

After seeing this thread we tested upgrading postgres from 11 to 13 on an existing Apollo 1 instance and it seems to be working. Even running "./apollo deploy" works with no errors. I do remember also seeing the error with the "adsrc" column on a fresh installation running postgres 12+ so I had to use postgres 11 at the time. So far it seems that as long as you first install Apollo and set up the databases with postgres 11, you can then migrate to a newer postgres version.

[ChrisChilders-USDA]

Thank you so much for confirming the system as @hexylena described it.

If I understand this correctly then, this is NOT an issue with Apollo needing PGSLQ<12, but an issue with the Apollo db creation code/process needing to be updated to accommodate the deprecated functions. Does that sound right?