[PDOException (42000)] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ORDER BY `entity_attrib ute_id` ASC LIMIT 392' at line 1

[snakezap]

Hello,

I try migration webshop but i have problem.

Preconditions

1. Magento 1.9.3.7 => Magento 2.3.5-P1
2. PHP 7.3.19-1 | 10.3.22-MariaDB

Steps to reproduce

php7.3 bin/magento migrate:data -r -a vendor/magento/data-migration-tool/etc/opensource-to-opensource/1.9.3.7/config.xml

Actual result

In Mysql.php line 110:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syn tax to use near ')) ORDER BY entity_attribute_id ASC LIMIT 392' at line 1, query was: SELECT eav_entity_attribute.* FROM eav_entity_attribute WHERE (entity_attribute_id >= 0) AND (attribute_id IN ()) ORDER BY entity_attri bute_id ASC LIMIT 392

In Mysql.php line 91:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syn tax to use near ')) ORDER BY entity_attribute_id ASC LIMIT 392' at line 1

Product is OK (can be informations missing...) Categories is OK Page CMS is OK Block CMS is OK Order/invoice/shipping/credit memo is not imported Customer not imported Review not imported

Thank you very much for your help.

[m2-assistant[bot]]

Hi @snakezap. Thank you for your report. To help us process this issue please make sure that you provided sufficient information.

Please, 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.

[ymln]

Looks like #829 fixes this.

[snakezap]

Thank you "ymln". I have new error now : Warning: Error while sending QUERY packet. PID=850 in /www/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php on line 182

[Litiano]

@snakezap change mysql config max_allowed_packet.

[HeiderSati]

Hi All, I added my comment that should fix this issue to the other ticket, please see my fix below:

Ok,... Debugged the damn thing,... now understood what's going on:

1) Download the latest "Data Migration Tool" (I'm on Magento CLI 2.3.5 at the time I wrote this response) 2) Goto vendor/magento/data-migration-tool/src/Migration/Step/Eav/Data.php 3) Go to line 528, you should see the following function:

private function migrateCustomEntityAttributes()
{
    $this->progress->advance();
    $sourceDocName = 'eav_entity_attribute';
    $destinationDocument = $this->destination->getDocument(
        $this->map->getDocumentMap($sourceDocName, MapInterface::TYPE_SOURCE)
    );
    $recordsToSave = $destinationDocument->getRecords();
    $customAttributeIds = $this->modelData->getCustomAttributeIds();
    $customEntityAttributes = $this->source->getRecords(
        $sourceDocName,
        0,
        $this->source->getRecordsCount($sourceDocName),
        new \Zend_Db_Expr(sprintf('attribute_id IN (%s)', implode(',', $customAttributeIds)))
    );
    foreach ($customEntityAttributes as $record) {
        $record['sort_order'] = $this->getCustomAttributeSortOrder($record);
        $record['attribute_group_id'] = $this->mapAttributeGroupIdsSourceDest[$record['attribute_group_id']];
        $record['entity_attribute_id'] = null;
        $destinationRecord = $this->factory->create(['document' => $destinationDocument, 'data' => $record]);
        $recordsToSave->addRecord($destinationRecord);
    }
    $this->saveRecords($destinationDocument, $recordsToSave);
}

4) The above code builds the CustomAttributeIDs by calling the getCustomAttributes() function which takes the AttributeIDs from the (Desintation) database "assuming" it's a vanilla/new database, and then compare these with the source database attributeIDs, if anything is found on the destination that is not in source then it's taken into the array. This would work if the destination database is brand-new. However, if the Data Migration Tool was run previously then the results would be NULL, with that the getRecords() line would fail causing this nasty error because the system is trying to select with the condition of "attribute_id IN ())"

This clearly have never been tested on a database that has already been migrated previously, or in other words, this would NEVER work for a system that was already imported.

5) Before the fix: IMPORTANT NOTE: I am always totally against editing release/firmware files of Magento Original Releases, however, unfortunately on this occasion, it's a must as the out-of-the-box does not work with an existing set of data unless you are migrating into a brand-new/fresh M2 install, which is impractical since you would build things and then re-migrate and then go live.

IMPORTANT: From a high level perspective; a gentle note: I take no responsibility for any damage you may cause to your data, use this procedure on "your own risk":

6a) Now the Fix: The new logic will be: A) Read the DEST attributes in the same way. B) Do a simple check (Do I have a NULL array?) C) If NULL then nohing needs to be imported, and the function should succeed. D) If we have something in the array, then proceed normally.

6b) Another Fix:

Just go into the source database (i.e. your production site), and add any dummy attribute there, this would cause the scrip to import nicely since the array won't be NULL anymore.

7) Now the fix is just to add the following logic:

    $customAttributeIds = $this->modelData->getCustomAttributeIds();
    if ($customAttributeIds)
    {
  .... do the stuf...             
    }

8) Now the fix in detail: Replace the above function with:

private function migrateCustomEntityAttributes()
{
    $this->progress->advance();
    $sourceDocName = 'eav_entity_attribute';
    $destinationDocument = $this->destination->getDocument(
        $this->map->getDocumentMap($sourceDocName, MapInterface::TYPE_SOURCE)
    );
    $recordsToSave = $destinationDocument->getRecords();
    $customAttributeIds = $this->modelData->getCustomAttributeIds();

    if ($customAttributeIds)        // Added by Heider to ensure that we process ONLY if there are new ATTRs
    {
        $customEntityAttributes = $this->source->getRecords(
            $sourceDocName,
            0,
            $this->source->getRecordsCount($sourceDocName),
            new \Zend_Db_Expr(sprintf('attribute_id IN (%s)', implode(',', $customAttributeIds)))
        );
        foreach ($customEntityAttributes as $record) {
            $record['sort_order'] = $this->getCustomAttributeSortOrder($record);
            $record['attribute_group_id'] = $this->mapAttributeGroupIdsSourceDest[$record['attribute_group_id']];
            $record['entity_attribute_id'] = null;
            $destinationRecord = $this->factory->create(['document' => $destinationDocument, 'data' => $record]);
            $recordsToSave->addRecord($destinationRecord);
        }
        $this->saveRecords($destinationDocument, $recordsToSave);
    }

}

I hope this helps.

Regards Heider