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

ploc · September 28, 2023, 11:20pm

I’m having an issue while trying to upload image files (media) to an Omeka S instance.

Here is the CSV Import log:

Next Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO media (id, ingester, renderer, data, source, media_type, storage_id, extension, sha256, size, has_original, has_thumbnails, position, lang, alt_text, item_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [5779, "sideload", "file", null, "photos\/1718.jpg", "image\/jpeg", "895f79d244ab32c5da1dc8dceb020a361451f71b", "jpg", "7c7e1014287adb55d614f12aeb635deaf925907230e2339c1bfbf7944ff52ec0", 188917, 1, 1, null, null, null, 5531]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '895f79d244ab32c5da1dc8dceb020a361451f71b' for key 'UNIQ_6A2CA10C5CC5DB90' in /srv/data/web/vhosts/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:74

What could cause this uniq constraint violation?

jflatnes · September 29, 2023, 9:08pm

Does this happen reliably, like every time you try to import, intermittently, or just once?

Also, what versions of Omeka S and CSV Import are you using?

nxm · October 23, 2023, 2:48pm

Hello Both,

I am having this same problem using CSV Import with File Sideload. I get 2x 20 record saves (so 40 records saved) and in the next 20 I get the key violation. So far this has happened 3 times. I am using Omeka-S 4.0.4 and CSV Import 2.5.0. I have not checked for updates to either Omeka-S or CSV Import but will do that now.

nxm · October 23, 2023, 3:22pm

Hello Again,

Appears I am current with versions. Out of curiosity I turned the batch size down from 20 to 5 to see if it was failing consistently in the same 5 record increments. Instead, it failed after 2x 5 record inserts. So I turned the batch size up to 40 and it failed after 2x 40 record inserts.

nxm · October 23, 2023, 4:16pm

A last update here … I processed all my records in a series of jobs, all failing after 2x whatever batch size. On the last job, it actually saved all the records but did give the same error, indicating that IMG_0826.JPG had a key failure … but I see that indeed it is saved to the database with the key that it was complaining about: e27631662e7e5ea79a73b214c72827f80712665c

Here is the full log in case of use:

2023-10-23T15:30:29+00:00 ERR (3): PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘e27631662e7e5ea79a73b214c72827f80712665c’ for key ‘media.UNIQ_6A2CA10C5CC5DB90’ in /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
Stack trace:
#0 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(117): PDOStatement->execute()
#1 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(179): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(226): Doctrine\DBAL\Statement->execute()
#3 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/JoinedSubclassPersister.php(194): Doctrine\DBAL\Statement->executeStatement()
#4 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1128): Doctrine\ORM\Persisters\Entity\JoinedSubclassPersister->executeInserts()
#5 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(425): Doctrine\ORM\UnitOfWork->executeInserts()
#6 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(392): Doctrine\ORM\UnitOfWork->commit()
#7 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(617): Doctrine\ORM\EntityManager->flush()
#8 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(366): CSVImport\Job\Import->reorderMedias()
#9 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(260): CSVImport\Job\Import->create()
#10 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(199): CSVImport\Job\Import->processBatchData()
#11 /var/www/caari_collection/application/src/Job/DispatchStrategy/Synchronous.php(34): CSVImport\Job\Import->perform()
#12 /var/www/caari_collection/application/src/Job/Dispatcher.php(105): Omeka\Job\DispatchStrategy\Synchronous->send()
#13 /var/www/caari_collection/application/data/scripts/perform-job.php(66): Omeka\Job\Dispatcher->send()
#14 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘e27631662e7e5ea79a73b214c72827f80712665c’ for key ‘media.UNIQ_6A2CA10C5CC5DB90’ in /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
Stack trace:
#0 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(119): Doctrine\DBAL\Driver\PDO\Exception::new()
#1 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(179): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(226): Doctrine\DBAL\Statement->execute()
#3 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/JoinedSubclassPersister.php(194): Doctrine\DBAL\Statement->executeStatement()
#4 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1128): Doctrine\ORM\Persisters\Entity\JoinedSubclassPersister->executeInserts()
#5 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(425): Doctrine\ORM\UnitOfWork->executeInserts()
#6 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(392): Doctrine\ORM\UnitOfWork->commit()
#7 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(617): Doctrine\ORM\EntityManager->flush()
#8 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(366): CSVImport\Job\Import->reorderMedias()
#9 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(260): CSVImport\Job\Import->create()
#10 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(199): CSVImport\Job\Import->processBatchData()
#11 /var/www/caari_collection/application/src/Job/DispatchStrategy/Synchronous.php(34): CSVImport\Job\Import->perform()
#12 /var/www/caari_collection/application/src/Job/Dispatcher.php(105): Omeka\Job\DispatchStrategy\Synchronous->send()
#13 /var/www/caari_collection/application/data/scripts/perform-job.php(66): Omeka\Job\Dispatcher->send()
#14 {main}

Next Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing ‘INSERT INTO media (id, ingester, renderer, data, source, media_type, storage_id, extension, sha256, size, has_original, has_thumbnails, position, lang, alt_text, item_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ with params [21795, “sideload”, “file”, null, “IMG_0826.JPG”, “image/jpeg”, “e27631662e7e5ea79a73b214c72827f80712665c”, “jpg”, “5274be58d3284a6d8676654d0ea4bf39b19523d810dfa253495989b3614f2acb”, 7350905, 1, 1, null, null, null, 11702]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘e27631662e7e5ea79a73b214c72827f80712665c’ for key ‘media.UNIQ_6A2CA10C5CC5DB90’ in /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:74
Stack trace:
#0 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(182): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException()
#1 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(159): Doctrine\DBAL\DBALException::wrapException()
#2 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(2226): Doctrine\DBAL\DBALException::driverExceptionDuringQuery()
#3 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(185): Doctrine\DBAL\Connection->handleExceptionDuringQuery()
#4 /var/www/caari_collection/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(226): Doctrine\DBAL\Statement->execute()
#5 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/JoinedSubclassPersister.php(194): Doctrine\DBAL\Statement->executeStatement()
#6 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1128): Doctrine\ORM\Persisters\Entity\JoinedSubclassPersister->executeInserts()
#7 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(425): Doctrine\ORM\UnitOfWork->executeInserts()
#8 /var/www/caari_collection/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(392): Doctrine\ORM\UnitOfWork->commit()
#9 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(617): Doctrine\ORM\EntityManager->flush()
#10 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(366): CSVImport\Job\Import->reorderMedias()
#11 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(260): CSVImport\Job\Import->create()
#12 /var/www/caari_collection/modules/CSVImport/src/Job/Import.php(199): CSVImport\Job\Import->processBatchData()
#13 /var/www/caari_collection/application/src/Job/DispatchStrategy/Synchronous.php(34): CSVImport\Job\Import->perform()
#14 /var/www/caari_collection/application/src/Job/Dispatcher.php(105): Omeka\Job\DispatchStrategy\Synchronous->send()
#15 /var/www/caari_collection/application/data/scripts/perform-job.php(66): Omeka\Job\Dispatcher->send()
#16 {main}

jflatnes · October 25, 2023, 8:25pm

You said that on the last one, it threw these errors but the files were actually created. Is that just the case for that one run, or is it generally what happens?

nxm · October 29, 2023, 1:17pm

Hello,

I am not sure I know how to answer that question or rather I do not really have the correct information from the logs (??) but I will tell you what I know.

I had to work on some other things and just back to this with a new set of images to import. I have made no changes to the server or Omeka-S install.

On the first run (with batch size set at 100) it again failed after loading two batches. It is failing on GUID “6e01c81d4f6b37c1b74af010f5aa84c7b6421bea”

That GUID is in the database assigned to IMG_1075.JPG:

From the log I see this:

Next Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing ‘INSERT INTO media (id, ingester, renderer, data, source, media_type, storage_id, extension, sha256, size, has_original, has_thumbnails, position, lang, alt_text, item_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ with params [21997, “sideload”, “file”, null, “IMG_1075.JPG”, “image/jpeg”, “6e01c81d4f6b37c1b74af010f5aa84c7b6421bea”, “jpg”, “84f961cb846b49f61402d1a2796bfe7991166c3f8fe912e8184dbb4666f28cd8”, 4157398, 1, 1, null, null, null, 11683]:

nxm · October 29, 2023, 1:36pm

Just a last note on this for now. On my last load I set the batch size (125) to larger than my remaining record count (121) and everything worked fine.

jflatnes · November 1, 2023, 9:11pm

So basically it sounds like the problem only happens when there are multiple batches, and though it stops the process, the media was actually already inserted? It looks to me like the system is trying to add the same media object twice, leading to this error the second time.

Another piece of helpful information would be what other modules you’re using. This could be an interaction that involves some module other than CSV Import.

nxm · November 9, 2023, 10:15am

Hello, sorry for the delay. Here are the other modules (as a picture). Please note that Log and Easy Admin are in the directory but not actually installed on the Omeka-S admin panel.

I did not interpret the error as trying to load the same media object but perhaps you are right. I was interpreting it as a duplicate GUID (see below image). If it helps, this IMG_1075.JPG is the first record in the 2nd batch (so it fails on the third batch when trying to insert that record again I guess??)

jflatnes · November 9, 2023, 5:56pm

Because it’s happening so regularly, it’s almost certainly not a case where the random ID is conflicting between two truly separate media objects. The “same” insert happening twice is more likely.

Can you try something else: temporarily disable all those modules and see if you still get the problem? That would rule in or out the problem coming from an interaction between different modules.