Use of ALTER TABLE query in pimgento import

[jeevi-ziffity]

Hi team,

Currently, I am using the Magento2.4.4-P2 version. In somecase while running the Pimgento product import we are getting a Waiting for table metadata lock issue on the ALTER TABLE catalog_product_entity AUTO_INCREMENT = <increment_value> query.

Code is: https://github.com/akeneo/magento2-connector-community/blob/master/Helper/Import/Entities.php#L531

As I debugged the product import flow. The flow looks like, when we run the product import, the following steps are executed in order:

1. The pimgento temp table ("tmp_pimgento_entities_product") was created and filled with the product CSV data (The data may contain existing and new product records). At this time, the _entity_id column in the temp table has "NULL" values. The existing records and new records are segregated by the _is_new column in the temp table.

2. The values of the _entity_id column for the existing products will be updated by mapping the pimgento_entities and tmp_pimgento_entities_product table's SKU.

The values of the _entity_id column for New products will be updated by getting an auto increment of the catalog_product_entity table and adding +1 with that increment ID. At this point of time, the values for _entity_id column are filled for all the rows and there will be no NULL values.

3. Then, the

'ALTER TABLE catalog_product_entity AUTO_INCREMENT = ' . (max((int)$maxCode, (int)$maxEntity) + 1)

query was executed.

$maxCode - We get max entity_id value from pimgento_entities table. For Example:

SELECT MAX(entity_id) FROM pimgento_entities WHERE (import = 'product')

$maxEntity - We get the max row_id value from catalog_product_entity table. For Example:

SELECT MAX(row_id) FROM catalog_product_entity WHERE (catalog_product_entity.created_in <= 1) AND (catalog_product_entity.updated_in > 1)

Get the max value between $maxCode and $maxEntity, then add +1 with the value.[(max((int)$maxCode, (int)$maxEntity) + 1 ]

Then update the catalog_product_entity table with the auto-increment value which we get from the above calculation.

4. In this step, the data from the "tmp_pimgento_entities_product" table are inserted/updated into the "catalog_product_entity" table.

Creating/updating entities: In this step, the data from the "tmp_pimgento_entities_product" table are inserted/updated into the "catalog_product_entity" table.In this case, we already have entity_id value in the temp table itself (_entity_id column in tmp_pimgento_entities_product), so the auto-increment value of the "catalog_product_entity" table is not at all used.

So I couldn't find any particular reason for the need for the "ALTER TABLE" query.

I ran the full import process by removing the "ALTER TABLE" query in my local, and it worked without any issues.

This ALTER TABLE query was executed for all types of imports, for example:

For attribute import, the query will be

ALTER TABLE catalog_product_entity AUTO_INCREMENT = 52691

For option import, the query will be

ALTER TABLE eav_attribute_option AUTO_INCREMENT = 719

For categories import, the query will be

ALTER TABLE catalog_category_entity AUTO_INCREMENT = 360

For family import, the query will be

ALTER TABLE eav_attribute_set AUTO_INCREMENT = 719

May I know what does the reason for the ALTER TABLE query in the import process? Is this line of code necessary for any reason?

https://github.com/akeneo/magento2-connector-community/blob/master/Helper/Import/Entities.php#L531