OMERO upgrade script fails 5.2.6 to 5.3.1

[bernie]

I'm in the process of upgrading OMERO from 5.2.6 to 5.3.1 and the upgrade script fails with:

ERROR: duplicate key value violates unique constraint "_lock_ids_pkey"
DETAIL: Key (id)=(117) already exists.

The pecheck SQL script ran successfully,

Bernie

[mtbc]

Dear Bernie,

That's most unfortunate, I am sorry to hear that. Thank you for the report, and also for running the precheck!

Could you try running for upgrade the psql command again but with the "-a" option also included (this outputs more) and send us the complete output? There will be many lines so if it is easiest you could just zip it up and upload it to http://qa.openmicroscopy.org.uk/qa/upload/ and we'll look out for it.

For what it's worth: an upgrade script failure should leave the database in exactly the state it was before the script was started.

Making the appropriate substitutions in the psql arguments, it could also be helpful if you would paste us the output of,

Code: Select all

echo "select * from dbpatch" | psql -h localhost -U db_user omero_database

Cheers,

Mark

[bernie]

Dear Mark,

thanks for your quick response. I've uploaded the files:

output.txt.gz
dbpatch.txt

Bernie

[mtbc]

Dear Bernie,

I'm sorry, can you try that upgrade script again, but also capture the standard error output? For example, if you had previously done,

Code: Select all

psql -a -h localhost -U db_user omero_database < sql/psql/OMERO5.3__0/OMERO5.2__0.sql | gzip > output.txt.gz


then add a 2>&1 as the last part of the psql to make,

Code: Select all

psql -a -h localhost -U db_user omero_database < sql/psql/OMERO5.3__0/OMERO5.2__0.sql 2>&1 | gzip > output.txt.gz


Cheers,
Mark

[bernie]

Of course, sorry. Uploaded:

output-2.txt.gz

Bernie

[mtbc]

Dear Bernie,

Goodness, that is a surprise, I shall consult with colleagues and we'll get back to you. Thank you indeed for uploading QAs 17707, 17708.

Cheers,
Mark

[mtbc]

We seem to be busy in lots of meetings today, perhaps partly preparing for our annual users' meeting coming up: readers should note that registration closes this Monday! Anyhow, while I still don't see why the upgrade could have gone wrong in this way, in the meantime let's gather some more information from your database so we can at least help with a fix.

Could you try,

Code: Select all

echo 'select * from _lock_ids; \d _lock_seq; \d seq_folder;' | psql -a ...


with your normal psql arguments as for our earlier "select * from dbpatch" (but also with the 2>&1) and provide the results from that? Perhaps there'll be a clue among all that.

Do you happen to know if your first attempt to upgrade the database to 5.3 failed in the same way that it now fails?

Cheers,
Mark

[bernie]

Dear Mark,

just uploaded:

dbpatch_2.txt.gz

Bernie

[bernie]

The run through upgrade that I did last week on our development OMERO server, if this is what you meant, was against a different database. There were no problems,

Bernie

[mtbc]

Dear Bernie,

I was wondering if the upgrade failure on your current troublesome server looked about the same the first time, before any retries. If it's not clear, no matter.

I would guess that if you simply do a,

Code: Select all

echo "select nextval('_lock_seq')" | psql ...


then afterward the upgrade script might then run fine. I'd expect the above command to output,

Code: Select all

nextval
---------
     123
(1 row)


Cheers,
Mark