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

Omeka S
1

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?

2

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?

3

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.

4

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.

5

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}

6

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?

7

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:

image
image2170×96 32.9 KB

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]:

8

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.

9

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.

10

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.

image

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??)

image
image2072×454 127 KB

11

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.

12

Sorry for the late response. The day you posted this I left that project (literally left the country) and will not be back to it until May 2024. I will do as you ask then as I will be again uploading many records. Thanks for the interest.

13

I am now ready to take back this issue.

The issue happens regularly, every time I try to load some media.

I’m using Omeka S 4.0.4, CSV Import version 2.5.0 and File Sideload version 1.7.1.

I try to load my file with all modules disabled except CSV Import and File Sideload, but I still get the same error:

2023-12-21T21:19:27+00:00 ERR (3): PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'e3c6680b663a92f7f4477ce8990b5fd9040ff4ce' for key 'UNIQ_6A2CA10C5CC5DB90' in /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
Stack trace:
#0 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(117): PDOStatement->execute(NULL)
#1 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(179): Doctrine\DBAL\Driver\PDOStatement->execute(NULL)
#2 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(226): Doctrine\DBAL\Statement->execute(NULL)
#3 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/JoinedSubclassPersister.php(194): Doctrine\DBAL\Statement->executeStatement()
#4 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1128): Doctrine\ORM\Persisters\Entity\JoinedSubclassPersister->executeInserts()
#5 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(425): Doctrine\ORM\UnitOfWork->executeInserts(Object(Doctrine\ORM\Mapping\ClassMetadata))
#6 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(392): Doctrine\ORM\UnitOfWork->commit(NULL)
#7 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(617): Doctrine\ORM\EntityManager->flush()
#8 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(366): CSVImport\Job\Import->reorderMedias(Array)
#9 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(260): CSVImport\Job\Import->create(Array)
#10 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(199): CSVImport\Job\Import->processBatchData(Array)
#11 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/src/Job/DispatchStrategy/Synchronous.php(34): CSVImport\Job\Import->perform()
#12 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/src/Job/Dispatcher.php(105): Omeka\Job\DispatchStrategy\Synchronous->send(Object(Omeka\Entity\Job))
#13 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/data/scripts/perform-job.php(66): Omeka\Job\Dispatcher->send(Object(Omeka\Entity\Job), Object(Omeka\Job\DispatchStrategy\Synchronous))
#14 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'e3c6680b663a92f7f4477ce8990b5fd9040ff4ce' for key 'UNIQ_6A2CA10C5CC5DB90' in /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
Stack trace:
#0 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(119): Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
#1 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(179): Doctrine\DBAL\Driver\PDOStatement->execute(NULL)
#2 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(226): Doctrine\DBAL\Statement->execute(NULL)
#3 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/JoinedSubclassPersister.php(194): Doctrine\DBAL\Statement->executeStatement()
#4 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1128): Doctrine\ORM\Persisters\Entity\JoinedSubclassPersister->executeInserts()
#5 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(425): Doctrine\ORM\UnitOfWork->executeInserts(Object(Doctrine\ORM\Mapping\ClassMetadata))
#6 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(392): Doctrine\ORM\UnitOfWork->commit(NULL)
#7 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(617): Doctrine\ORM\EntityManager->flush()
#8 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(366): CSVImport\Job\Import->reorderMedias(Array)
#9 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(260): CSVImport\Job\Import->create(Array)
#10 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(199): CSVImport\Job\Import->processBatchData(Array)
#11 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/src/Job/DispatchStrategy/Synchronous.php(34): CSVImport\Job\Import->perform()
#12 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/src/Job/Dispatcher.php(105): Omeka\Job\DispatchStrategy\Synchronous->send(Object(Omeka\Entity\Job))
#13 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/data/scripts/perform-job.php(66): Omeka\Job\Dispatcher->send(Object(Omeka\Entity\Job), Object(Omeka\Job\DispatchStrategy\Synchronous))
#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 [5791, "sideload", "file", null, "photos\/1419.jpg", "image\/jpeg", "e3c6680b663a92f7f4477ce8990b5fd9040ff4ce", "jpg", "b495429753c1f8eacf2994843773ad38f16d67537baeeac13ca45a52318634c2", 104970, 1, 1, null, null, null, 5744]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'e3c6680b663a92f7f4477ce8990b5fd9040ff4ce' for key 'UNIQ_6A2CA10C5CC5DB90' in /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:74
Stack trace:
#0 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(182): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDO\Exception))
#1 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(159): Doctrine\DBAL\DBALException::wrapException(Object(Doctrine\DBAL\Driver\PDO\MySQL\Driver), Object(Doctrine\DBAL\Driver\PDO\Exception), 'An exception oc...')
#2 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(2226): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\MySQL\Driver), Object(Doctrine\DBAL\Driver\PDO\Exception), 'INSERT INTO med...', Array)
#3 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(185): Doctrine\DBAL\Connection->handleExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'INSERT INTO med...', Array, Array)
#4 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(226): Doctrine\DBAL\Statement->execute(NULL)
#5 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/JoinedSubclassPersister.php(194): Doctrine\DBAL\Statement->executeStatement()
#6 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1128): Doctrine\ORM\Persisters\Entity\JoinedSubclassPersister->executeInserts()
#7 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(425): Doctrine\ORM\UnitOfWork->executeInserts(Object(Doctrine\ORM\Mapping\ClassMetadata))
#8 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(392): Doctrine\ORM\UnitOfWork->commit(NULL)
#9 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(617): Doctrine\ORM\EntityManager->flush()
#10 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(366): CSVImport\Job\Import->reorderMedias(Array)
#11 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(260): CSVImport\Job\Import->create(Array)
#12 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/modules/CSVImport/src/Job/Import.php(199): CSVImport\Job\Import->processBatchData(Array)
#13 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/src/Job/DispatchStrategy/Synchronous.php(34): CSVImport\Job\Import->perform()
#14 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/src/Job/Dispatcher.php(105): Omeka\Job\DispatchStrategy\Synchronous->send(Object(Omeka\Entity\Job))
#15 /home/clients/87af2e3481ab91fc1106985669dd916e/sites/dev.example.org/application/data/scripts/perform-job.php(66): Omeka\Job\Dispatcher->send(Object(Omeka\Entity\Job), Object(Omeka\Job\DispatchStrategy\Synchronous))
#16 {main}

The image is however loaded, but the CSV Import stops after this image, after this error, and following media are not loaded.

14

OK, thanks for the update.

A few questions:

  • Are you doing “media” imports, or are these “item” imports where you’re mapping a media column?
  • Do any of the media get successfully created (like, the first one, or first several)? You updated your post to note that one does get loaded; is that just always the first one?
  • Have you only ever seen this problem when using CSV Import 2.5.0? Have you tried at all with version 2.4.1, the previous release?
  • (edit) Oh, and I forgot one more: are there any other errors or messages in the import job’s log? Ones that happen before this “constraint violation” one?
15

The strand thing is that I can’t find any similar item in the database, here’s what I’ve found for the corresponding file:

+------+---------+----------+----------+------+-----------------+------------+------------------------------------------+-----------+------------------------------------------------------------------+--------+--------------+----------------+----------+------+----------+
| id   | item_id | ingester | renderer | data | source          | media_type | storage_id                               | extension | sha256                                                           | size   | has_original | has_thumbnails | position | lang | alt_text |
+------+---------+----------+----------+------+-----------------+------------+------------------------------------------+-----------+------------------------------------------------------------------+--------+--------------+----------------+----------+------+----------+
| 5732 |    5355 | sideload | file     | NULL | photos/1419.jpg | image/jpeg | 407af7321ba64bfd493e8ee144cc392378490bbd | jpg       | b4e848faf053e1eb37949e9ccb5bf4ee2055840ed9eb6529f1be3184273f9bca | 142760 |            1 |              1 |        2 | NULL | NULL     |
+------+---------+----------+----------+------+-----------------+------------+------------------------------------------+-----------+------------------------------------------------------------------+--------+--------------+----------------+----------+------+----------+
1 row in set (0,04 sec)

comparing to what was in the logfile:

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 [5791, "sideload", "file", null, "photos\/1419.jpg", "image\/jpeg", "e3c6680b663a92f7f4477ce8990b5fd9040ff4ce", "jpg", "b495429753c1f8eacf2994843773ad38f16d67537baeeac13ca45a52318634c2", 104970, 1, 1, null, null, null, 5744]:
16

To answer your questions, I am doing a media import, the same way I 've been doing this since 2020. I’m not sure when this problem appeared, but it was in the last months… I gave you all the log I had in my logfile.

Last thing, here is the csv file that I’m using for the import:

"dcterms:identifier","Filename-originals","public/private"
1419,"originals/1419.jpg","private"
1717,"originals/1717.jpg","private"
1718,"originals/1718.jpg","private"
1719,"originals/1719.jpg","private"
1720,"originals/1720.jpg","private"
1721,"originals/1721.jpg","private"
1722,"originals/1722.jpg","private"
1723,"originals/1723.jpg","private"
1724,"originals/1724.jpg","private"
1725,"originals/1725.jpg","private"
1726,"originals/1726.jpg","private"
1727,"originals/1727.jpg","private"
1728,"originals/1728.jpg","private"
1729,"originals/1729.jpg","private"
1730,"originals/1730.jpg","private"
1731,"originals/1731.jpg","private"
1732,"originals/1732.jpg","private"
1733,"originals/1733.jpg","private"
1734,"originals/1734.jpg","private"
1735,"originals/1735.jpg","private"
1736,"originals/1736.jpg","private"
1737,"originals/1737.jpg","private"
1738,"originals/1738.jpg","private"
1739,"originals/1739.jpg","private"
1740,"originals/1740.jpg","private"
1741,"originals/1741.jpg","private"
1742,"originals/1742.jpg","private"
1743,"originals/1743.jpg","private"
1744,"originals/1744.jpg","private"
1745,"originals/1745.jpg","private"
1746,"originals/1746.jpg","private"
1747,"originals/1747.jpg","private"
1748,"originals/1748.jpg","private"
1749,"originals/1749.jpg","private"
1750,"originals/1750.jpg","private"
1751,"originals/1751.jpg","private"
1752,"originals/1752.jpg","private"
1753,"originals/1753.jpg","private"
1754,"originals/1754.jpg","private"
1419,"photos/1419.jpg","public"
1717,"photos/1717.jpg","public"
1718,"photos/1718.jpg","public"
1719,"photos/1719.jpg","public"
1720,"photos/1720.jpg","public"
1721,"photos/1721.jpg","public"
1722,"photos/1722.jpg","public"
1723,"photos/1723.jpg","public"
1724,"photos/1724.jpg","public"
1725,"photos/1725.jpg","public"
1726,"photos/1726.jpg","public"
1727,"photos/1727.jpg","public"
1728,"photos/1728.jpg","public"
1729,"photos/1729.jpg","public"
1730,"photos/1730.jpg","public"
1731,"photos/1731.jpg","public"
1732,"photos/1732.jpg","public"
1733,"photos/1733.jpg","public"
1734,"photos/1734.jpg","public"
1735,"photos/1735.jpg","public"
1736,"photos/1736.jpg","public"
1737,"photos/1737.jpg","public"
1738,"photos/1738.jpg","public"
1739,"photos/1739.jpg","public"
1740,"photos/1740.jpg","public"
1741,"photos/1741.jpg","public"
1742,"photos/1742.jpg","public"
1743,"photos/1743.jpg","public"
1744,"photos/1744.jpg","public"
1745,"photos/1745.jpg","public"
1746,"photos/1746.jpg","public"
1747,"photos/1747.jpg","public"
1748,"photos/1748.jpg","public"
1749,"photos/1749.jpg","public"
1750,"photos/1750.jpg","public"
1751,"photos/1751.jpg","public"
1752,"photos/1752.jpg","public"
1753,"photos/1753.jpg","public"
1754,"photos/1754.jpg","public"

As you can the the private images get loaded without problem, while the problem appears with the first public image. Each item get 2 images: the private one is the high resolution image, while the public one is the low resolution watermarked image.

17

The example file is very helpful.

Another question: there’s a “batch size” setting in the Advanced tab: what do you have it set to? You could also just go to “Job details” for this import in the Past Imports list and post the whole “args” value, which includes all the import settings for that job.

One final thing: do things change if you change the order or number of entries in your file you’re importing? Like, if you list the “public” ones first, is it still the 40th row that’s the problem, or does the issue “stay” with the “photos/1419.jpg” media regardless?

18

I have a possible solution.

Are you comfortable applying a patch? There’s a small patch I’ve written that applies to the CSV Import module to resolve this issue. If you’re able and willing, it would be great if you could try it out.

19

Here are the json parameters of the last import. As you can see, rows_by_batch is set to 20.

{
    "filename": "import-20231221.csv",
    "filesize": "2711",
    "filepath": "\/home\/clients\/87af2e3481ab91fc1106985669dd916e\/tmp\/omekafVoAmD",
    "media_type": "text\/csv",
    "resource_type": "media",
    "comment": "",
    "automap_check_names_alone": false,
    "column-item": [
        "dcterms:identifier"
    ],
    "column-data-type": [
        "literal",
        "literal",
        "literal"
    ],
    "column-media_source": {
        "1": "sideload"
    },
    "column-is_public": {
        "2": "1"
    },
    "generic-data": "default",
    "media-source": "default",
    "o:resource_template": "",
    "o:resource_class": "",
    "o:owner": {
        "o:id": 1
    },
    "o:is_public": 1,
    "multivalue_separator": "|",
    "global_language": "",
    "action": "create",
    "identifier_column": 0,
    "identifier_property": "",
    "action_unidentified": "skip",
    "rows_by_batch": 20,
    "column-multivalue": [],
    "delimiter": ",",
    "enclosure": "\"",
    "escape": "\\"
}
20

If I change the csv import file to remove some file:

"dcterms:identifier","Filename-originals","public/private"
1720,"originals/1720.jpg","private"
1721,"originals/1721.jpg","private"
1722,"originals/1722.jpg","private"
1723,"originals/1723.jpg","private"
1724,"originals/1724.jpg","private"
1725,"originals/1725.jpg","private"
1726,"originals/1726.jpg","private"
1727,"originals/1727.jpg","private"
1728,"originals/1728.jpg","private"
1729,"originals/1729.jpg","private"
1730,"originals/1730.jpg","private"
1731,"originals/1731.jpg","private"
1732,"originals/1732.jpg","private"
1733,"originals/1733.jpg","private"
1734,"originals/1734.jpg","private"
1735,"originals/1735.jpg","private"
1736,"originals/1736.jpg","private"
1737,"originals/1737.jpg","private"
1738,"originals/1738.jpg","private"
1739,"originals/1739.jpg","private"
1740,"originals/1740.jpg","private"
1741,"originals/1741.jpg","private"
1742,"originals/1742.jpg","private"
1743,"originals/1743.jpg","private"
1744,"originals/1744.jpg","private"
1745,"originals/1745.jpg","private"
1746,"originals/1746.jpg","private"
1747,"originals/1747.jpg","private"
1748,"originals/1748.jpg","private"
1749,"originals/1749.jpg","private"
1750,"originals/1750.jpg","private"
1751,"originals/1751.jpg","private"
1752,"originals/1752.jpg","private"
1753,"originals/1753.jpg","private"
1754,"originals/1754.jpg","private"
1720,"photos/1720.jpg","public"
1721,"photos/1721.jpg","public"
1722,"photos/1722.jpg","public"
1723,"photos/1723.jpg","public"
1724,"photos/1724.jpg","public"
1725,"photos/1725.jpg","public"
1726,"photos/1726.jpg","public"
1727,"photos/1727.jpg","public"
1728,"photos/1728.jpg","public"
1729,"photos/1729.jpg","public"
1730,"photos/1730.jpg","public"
1731,"photos/1731.jpg","public"
1732,"photos/1732.jpg","public"
1733,"photos/1733.jpg","public"
1734,"photos/1734.jpg","public"
1735,"photos/1735.jpg","public"
1736,"photos/1736.jpg","public"
1737,"photos/1737.jpg","public"
1738,"photos/1738.jpg","public"
1739,"photos/1739.jpg","public"
1740,"photos/1740.jpg","public"
1741,"photos/1741.jpg","public"
1742,"photos/1742.jpg","public"
1743,"photos/1743.jpg","public"
1744,"photos/1744.jpg","public"
1745,"photos/1745.jpg","public"
1746,"photos/1746.jpg","public"
1747,"photos/1747.jpg","public"
1748,"photos/1748.jpg","public"
1749,"photos/1749.jpg","public"
1750,"photos/1750.jpg","public"
1751,"photos/1751.jpg","public"
1752,"photos/1752.jpg","public"
1753,"photos/1753.jpg","public"
1754,"photos/1754.jpg","public"

The problem still occurs at the first public image that is loaded, but this is the 36th image to load.

In my opinion, the problem occurs at the load of a second image of an item.