Unique Constraint Violation Exception: An exception occurred while executing 'INSERT INTO media

Omeka S
21

I tested your patch and the import does now complete without error. All the images are loaded.

Can you confirm that I applied the patch correctly ?

        // Get correct position for each media
        $mediaRanks = [];
        $currentItemId = null;
        $position = null;
        foreach ($mediaInOrder as $mediaData) {
            if ($mediaData['item_id'] !== $currentItemId) {
                $position = 1;
                $currentItemId = $mediaData['item_id'];
            } else {
                $position++;
            }

            $mediaRanks[$mediaData['id']] = $position;
        }

        // Update positions of the updated media.
        $conn->beginTransaction();
        try {
            $updateQuery = 'UPDATE media SET position = ? WHERE id = ?';
            foreach ($mediaRanks as $id => $rank) {
                $conn->executeQuery($updateQuery, [$rank, $id], ['integer', 'integer']);
            }
            $conn->commit();
        } catch (\Exception $e) {
            $conn->rollBack();
        }
    }

I think you’ve spotted the error and fixed it in the patch!

Can you explain a little bit what was the error (to feed my curiosity)?

22

OK, that’s great, thanks for reporting your experience with it. You do have the patch applied right.

The issue is a little complex to explain, as it involves the inner workings of Doctrine, the database library we use in Omeka S. CSV Import, to deal with potentially very large imports, has to work in batches and clear away the completed imported records periodically.

The problem here is related to a specific piece of code that only runs when you’re importing media specifically, which is used to set the “position” of the new media to be at the end of the list for their parent item. This code was changed in 2.5.0, which is the reason behind the problem only appearing recently. Basically, the 2.5.0 code could “confuse” Doctrine into thinking it should insert a new copy of a media instead of just updating its position, but only after the first batch. This is why it was a little tricky to run down: you had to do a media import specifically, and have it be large enough (or set the batch size small enough) to do multiple batches. Both you and @nxm gave valuable feedback for narrowing down the conditions here, thanks to both of you.

I should be able to apply this quickly to a bugfix release for CSV Import, and you’ll be able to upgrade to that rather than have to apply the patch.

23

Great thanks for your explanation that was clear enough for me to understand what was going on.

Thanks also for the patch and for the upcoming release of CVS Import. Could please ping us here once the new version is released?

24

The release with the fix is available now, version 2.5.1. It’s available on the module’s page as usual.

25

Thanks @jflatnes. I updated the plugin, I loaded my images, and… no problem, no warning, no error.

However, I got one item that has no image linked to it, the item 1746. Can there still be a problem on how images are processed so that an image could be forgotten?

The strange thing is that - in fact - two images were ignored:

  • photos/1746.jpg
  • originals/1746.jpg

Two images from the same item.

26

I made a new test, with a csv file containing only two files:

"dcterms:identifier","Filename-originals","public/private"
1746,"originals/1746.jpg","private"
1746,"photos/1746.jpg","public"

While the import went well: Status: Completed and no log. I can’t see any image linked to the item, while the image from the import file do not exist any more. It’s like the images where not processed correctly.

How can I debug that?

27

Is it possible that you have more than one item with the same identifier “1746”? You could be looking at one item but the importer is adding the media to a different one if you have multiple items that share this identifier. Since it’s reliably and only the rows using this specific identifier, that’s probably the most likely source of your issue.

28

I thought the first thing I would check was the unicity of the item whose identifier was 1746, but I double checked it and it appears that you were right: there was two item with the identifier 1746.

Is there a way to tell Omeka that the field identifier is an identifier and hence can be managed by Omeka as an identifier: each item must have a unique identifier, and any new item should have an identifier automatically filled with a value that is max(identifier)+1. This would be great…

29

By the way, is there a way to find out if an identifier is used more than once?

This would be great if I can find all duplicate identifiers…

30

@jflatnes I think I’ve found another ORM error, similar to the one previously spotted in this thread. You can have a look at this thread to find out: Import media and replace files .