duplicates in thumbnail_pkey, upgrading from 4.3.3. to 4.4.3

[jacques2020]

Dear community,

while upgrading my omero server (debian squeeze, up to date, ice33) from 4.3.3 to 4.4.3, following instructions at http://www.openmicroscopy.org/site/supp ... er/upgrade , I experienced an issue running the optimization

Code: Select all

psql -h localhost -U db_user omero_database -c "REINDEX DATABASE omero_database FORCE;".

ERROR: could not create unique index "thumbnail_pkey"
DETAIL: Table contains duplicated values.

This might be related to the issue I got with the preview and mentioned in my previous post?
Anyway, does anyone have suggestions to investigate and/or fix this issue.

many thanks

Jacques

[jmoore]

Hi Jacques,

Several questions first:

• do you have the entire upgrade log?
• Were there any warnings printed?
• What version of postgresql is this?

Also, though it likely won't return anything, could you run the following for us?

Code: Select all

select id, count(id) from thumbnail group by id having count(id) > 1;


Cheers,
~Josh

[jacques2020]

Hi Josh,

Unfortunately, I did not save the entire log. But I carefully checked that neither warning nor error were reported at the time of my first post. I got rather the message of successful upgrade as reproduced in the upgrade documentation linked in my original post.
Here is my Postgresql version:

Code: Select all

# psql --version
psql (PostgreSQL) 8.4.12
contains support for command-line editing

As you expected

Code: Select all

psql -h localhost -U omero omero -c "select id, count(id) from thumbnail group by id having count(id) > 1;"

Did not returned anything.

Thank you for your help.

Best

Jacques

[jmoore]

Would you mind also trying:

Code: Select all

begin;
create table thumbdupes as select ctid as tid, id from thumbnail;
select * from thumbdupes x where exists (select 1 from thumbdupes y where x.id = y.id and x.tid != y.tid);
rollback;


Cheers,
~Josh

[jacques2020]

Hi Josh,

so I run the other code and did not get more result

Code: Select all

psql -h localhost -U omero omero -c "begin;
> create table thumbdupes as select ctid as tid, id from thumbnail;
> select * from thumbdupes x where exists (select 1 from thumbdupes y where x.id = y.id and x.tid != y.tid);
> rollback;"
Password for user omero:
ROLLBACK


Thank you for your help

Cheers

Jacques

[jmoore]

Hi Jacques,

you'll probably need to put the commands above into a separate file and run them like this:

Code: Select all

psql -h localhost -U omero omero -f thumbdupes.sql


When I do this, I get:

Code: Select all

BEGIN
SELECT 8
tid | id
-----+----
(0 rows)

ROLLBACK


Cheers,
~Josh

[jacques2020]

Hi Josh,

Thank you for your quick answer
sorry for my misunderstanding. I used a separate file and got :

BEGIN
SELECT
tid | id
-----------+--------
(2801,40) | 198152
(2801,43) | 198152
(2801,39) | 198151
(2801,42) | 198151
(4 rows)

ROLLBACK

Cheers

Jacques

[jmoore]

Morning, Jacques,

I don't know how this happened, but your index is corrupted. This may point to hardware (i.e. disk) issues. If you aren't too worried about the specific thumbnails that might be related, you can delete either of the duplicates via:

Code: Select all

# For example
delete from thumbnail where ctid='(2801,39)' and id=198151;
delete from thumbnail where ctid='(2801,40)' and id=198152;


If you'd like, you can use the following to find out more about the duplicates:

Code: Select all

begin;
create table thumbdupes as
    select t.ctid as tid, t.id as "tb_id", t.owner_id "as tb_owner", t.group_id "as tb_group",
           i.name, i.id as "img_id", i.owner_id as "img_owner", i.group_id as "img_group"
      from thumbnail t, pixels p, image i
     where t.pixels = p.id and p.image = i.id;
select * from thumbdupes x where exists (select 1 from thumbdupes y where x.tb_id = y.tb_id and x.tid != y.tid);
rollback;


Having read several forums about this issue, the only suggestion I can make is for you to dump your database and restore (perhaps on a newer version of postgresql).

Best wishes,
~Josh

[jacques2020]

Hi Josh,

Thank you for your help. I am a bit puzzled by your answer.

- Is it sufficient to delete the images with duplicates thumbnails as well as the thumbnails (and re-import images)? I have no backup from a date before importing the images related to incriminated thumbnails (4 months ago). And anyway, it would mean loosing all data imported since then (or re-import them)?

- Is there a test I can run to ensure that there is no other issue with my database ?

- About postgresql version, that's the one included with debian squeeze (the latest stable release). Do you think it is such an issue that I should (manually) upgrade to a more recent version?

- About the causes, I have no memory of any disk issue on the server within the last four months. Could it have been an issue during import with network or ...?

- The code to find details failed to return anything for some reasons, but I find my way using

Code: Select all

omero -g CeDRE hql "select i from Image i where i.id = 198152"

Cheers

Jacques

[jmoore]

Hi,

- Is it sufficient to delete the images with duplicates thumbnails as well as the thumbnails (and re-import images)? I have no backup from a date before importing the images related to incriminated thumbnails (4 months ago). And anyway, it would mean loosing all data imported since then (or re-import them)?

It shouldn't be necessary to delete the images. Just the thumbnails are causing problems (so far).

- Is there a test I can run to ensure that there is no other issue with my database ?

Not that I know of offhand. If the REINDEX command runs to completion, though, I would feel fairly confident that the DB is in working order. But it probably makes sense to make a backup at this point, so we can get back to this state if need be.

- About postgresql version, that's the one included with debian squeeze (the latest stable release). Do you think it is such an issue that I should (manually) upgrade to a more recent version?

No. If you are up-to-date with your distribution's version (all patch fixes, etc.), then that should be fine.

- About the causes, I have no memory of any disk issue on the server within the last four months. Could it have been an issue during import with network or ...?

It shouldn't have anything to do with OMERO or the network. This is quite internal to PostgreSQL.

- The code to find details failed to return anything for some reasons, but I find my way using

Code: Select all

omero -g CeDRE hql "select i from Image i where i.id = 198152"

Understood. Can you also look at the thumbnail rows and see if there are any important differences? If not, then deleting one should be fine.

Cheers,
~Josh