SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '11' for key 'PRIMARY'

[cherwinjayo]

Hello! I want to ask for help on the data migration tool having an issue with the catalog_product_entity_media_gallery Map Step part. This site is already working on M1 and we plan to migrate to M2 to escape depreciation.

Preconditions

1. Magento 1.9.2.0 to Magento 2.4.6-p3
2. Using data-migration-tool 2.4.6.1
3. Using Ubuntu 22.04 on a local PC
4. same localhost for MySQL
5. using MySQL 5.7, PHP 8.1 for M2 and PHP 8.2 for PHPMyAdmin
6. first value_id data is 11 not 1

Steps to reproduce

1. running command "php /var/www/magento-sites/m2/bin/magento migrate:data --reset --auto --verbose /var/www/magento-sites/m2/vendor/magento/data-migration-tool/etc/opensource-to-opensource/1.9.2.0/config.xml"
2. removed all unique parameters on all eav_, catalog_, customer_* tables
3. replaced first value_id on catalog_product_entity_media_gallery and it sets error to the next value_id
4. set max_allowed_packet to maximum value
5. set foreign_key_checks to 0
6. set bulk_size in config.xml to 100
7. set direct_document_copy to 1

Expected result

1. proceed with the migration
2. go ahead and to delta migration (if needed)

Actual result

1. [2023-10-16T18:58:27.869137+00:00][INFO][mode: data][stage: data migration][step: Map Step]: DEBUG: catalog_product_entity_media_gallery
2. In Mysql.php line 182:
   [PDOException (23000)]
   SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '11' for key 'PRIMARY'
3. Exception trace:
   at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:182
   PDOStatement->execute() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:182
   Migration\ResourceModel\Adapter\Mysql->insertMultiple() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:148
   Migration\ResourceModel\Adapter\Mysql->insertRecords() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Destination.php:52
   Migration\ResourceModel\Destination->saveRecords() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/Handler/Gallery/InsertValueToEntity.php:71
   Migration\Handler\Gallery\InsertValueToEntity->handle() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/RecordTransformer.php:126
   Migration\RecordTransformer->applyHandlers() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/RecordTransformer.php:74
   Migration\RecordTransformer->transform() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/Step/Map/Data.php:167
   Migration\Step\Map\Data->perform() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/Mode/AbstractMode.php:82
   Migration\Mode\AbstractMode->runStage() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:124
   Migration\Mode\Data->runData() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:69
   Migration\Mode\Data->run() at /var/www/magento-sites/m2/vendor/magento/data-migration-tool/src/Migration/Console/MigrateDataCommand.php:60
   Migration\Console\MigrateDataCommand->execute() at /var/www/magento-sites/m2/vendor/symfony/console/Command/Command.php:298
   Symfony\Component\Console\Command\Command->run() at /var/www/magento-sites/m2/vendor/magento/framework/Interception/Interceptor.php:58
   Migration\Console\MigrateDataCommand\Interceptor->___callParent() at /var/www/magento-sites/m2/vendor/magento/framework/Interception/Interceptor.php:138
   Migration\Console\MigrateDataCommand\Interceptor->Magento\Framework\Interception{closure}() at /var/www/magento-sites/m2/vendor/magento/framework/Interception/Interceptor.php:153
   Migration\Console\MigrateDataCommand\Interceptor->___callPlugins() at /var/www/magento-sites/m2/generated/code/Migration/Console/MigrateDataCommand/Interceptor.php:77
   Migration\Console\MigrateDataCommand\Interceptor->run() at /var/www/magento-sites/m2/vendor/symfony/console/Application.php:1040
   Symfony\Component\Console\Application->doRunCommand() at /var/www/magento-sites/m2/vendor/symfony/console/Application.php:301
   Symfony\Component\Console\Application->doRun() at /var/www/magento-sites/m2/vendor/magento/framework/Console/Cli.php:116
   Magento\Framework\Console\Cli->doRun() at /var/www/magento-sites/m2/vendor/symfony/console/Application.php:171
   Symfony\Component\Console\Application->run() at /var/www/magento-sites/m2/bin/magento:23

Additional notes

• there are a lot of custom stuff built around the M1 site, a custom module incorporated directly with its own tables connecting to catalog_products
• custom customer_groups
• media and catalog directories are not in the correct places, custom mapped

[m2-assistant]

Hi @cherwinotadoy. Thank you for your report.
To speed up processing of this issue, make sure that you provided sufficient information.
Add a comment to assign the issue: @magento I am working on this

---

Join Magento Community Engineering Slack and ask your questions in #github channel.

[OliverRC]

Related to #869