Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Magento 1 to 2 - Data Migration Tool - ERROR: [PDOException] SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #103

Closed
Ganginator opened this issue Jun 27, 2016 · 10 comments

Comments

@Ganginator
Copy link

@Ganginator Ganginator commented Jun 27, 2016

After getting through connection snafu's, and clearing the integrity constraint errors by editing duplicate emails in customer_entity table, I've ran into this somewhat illusive issue:

"

[PDOException]
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

"

I've searched for this with little success.
If there are attributes that need to be added, or ignored, which is what smells like the issue, how would I do so, and how do I tell what ones are an issue?

Help is much appreciated.
I've been working on this migration for four days now, and would like to get through it soon.

@victor-v-rad
Copy link
Collaborator

@victor-v-rad victor-v-rad commented Jul 11, 2016

Hi @Ganginator

Please use -vvv parameter. It will show a call trace of the error which can give a clue about location where it happen

Loading

@ilol ilol closed this Jul 12, 2016
@Ganginator
Copy link
Author

@Ganginator Ganginator commented Jul 18, 2016

I do use that.

By increasing it's memory allowance I got it to pass through all the records, and then I've pinned down the issue to it's save function.
$this->destination->saveRecords( $destinationName, $destinationRecords );

Is there an easy way to get a list of the records / tokens that do not match?
I'm assuming that would make it easier to find the differences?

Loading

@victor-v-rad
Copy link
Collaborator

@victor-v-rad victor-v-rad commented Jul 19, 2016

You can use Xdebug and see what data $destinationRecords have and what the table name in $destinationName. When compare table columns and data from $destinationRecords to understand the difference.

Loading

@sehajmathur
Copy link

@sehajmathur sehajmathur commented Sep 15, 2016

Hi @Ganginator

Did you found any solution? I am getting the same error while migrating data from 1.7.2 to 2.1.1

Exception trace: () at /var/www/html/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:169 PDOStatement->execute() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:169 Migration\ResourceModel\Adapter\Mysql->insertMultiple() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:137 Migration\ResourceModel\Adapter\Mysql->insertRecords() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Destination.php:52 Migration\ResourceModel\Destination->saveRecords() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/Step/Customer/Data.php:164 Migration\Step\Customer\Data->perform() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/Mode/AbstractMode.php:73 Migration\Mode\AbstractMode->runStage() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:116 Migration\Mode\Data->runData() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:68 Migration\Mode\Data->run() at /var/www/html/vendor/magento/data-migration-tool/src/Migration/Console/MigrateDataCommand.php:51 Migration\Console\MigrateDataCommand->execute() at /var/www/html/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:257 Symfony\Component\Console\Command\Command->run() at /var/www/html/vendor/symfony/console/Symfony/Component/Console/Application.php:874 Symfony\Component\Console\Application->doRunCommand() at /var/www/html/vendor/symfony/console/Symfony/Component/Console/Application.php:195 Symfony\Component\Console\Application->doRun() at /var/www/html/vendor/magento/framework/Console/Cli.php:96 Magento\Framework\Console\Cli->doRun() at /var/www/html/vendor/symfony/console/Symfony/Component/Console/Application.php:126 Symfony\Component\Console\Application->run() at /var/www/html/bin/magento:23

Loading

@ghost
Copy link

@ghost ghost commented Nov 8, 2016

Hi, anyone been able to solve this? Migrating from 1.9.2.4 to 2.1.2. After resolving several integrety issues I also ran into this warning.

[PDOException]
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Exception trace:
() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:169
PDOStatement->execute() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:169
Migration\ResourceModel\Adapter\Mysql->insertMultiple() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:137
Migration\ResourceModel\Adapter\Mysql->insertRecords() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Destination.php:52
Migration\ResourceModel\Destination->saveRecords() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/Step/Customer/Data.php:164
Migration\Step\Customer\Data->perform() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/Mode/AbstractMode.php:73
Migration\Mode\AbstractMode->runStage() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:116
Migration\Mode\Data->runData() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:68
Migration\Mode\Data->run() at /data/web/magento2/vendor/magento/data-migration-tool/src/Migration/Console/MigrateDataCommand.php:51
Migration\Console\MigrateDataCommand->execute() at /data/web/magento2/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:257
Symfony\Component\Console\Command\Command->run() at /data/web/magento2/vendor/symfony/console/Symfony/Component/Console/Application.php:874
Symfony\Component\Console\Application->doRunCommand() at /data/web/magento2/vendor/symfony/console/Symfony/Component/Console/Application.php:195
Symfony\Component\Console\Application->doRun() at /data/web/magento2/vendor/magento/framework/Console/Cli.php:96
Magento\Framework\Console\Cli->doRun() at /data/web/magento2/vendor/symfony/console/Symfony/Component/Console/Application.php:126
Symfony\Component\Console\Application->run() at /data/web/magento2/bin/magento:23

Loading

@Yogesh7989
Copy link

@Yogesh7989 Yogesh7989 commented Dec 31, 2016

I am facing same issue is there any one getting solution for this issue. Help is much appreciated. Thanks in advance.

Loading

@dfelton
Copy link
Contributor

@dfelton dfelton commented Jan 16, 2017

@Yogesh7989, @Trabant74, @sehajmathur @Ganginator

I have narrowed down the root cause of this issue to be when there is a record in the customer_entity table, which is lacking records for all the EAV attributes defined in your customer_entity group.

I am referring to the EAV attributes defined in one of the following files depending on your situation (which, at this time also happens to be the same regardless of your situation):

What is going on here, is the Data Migration Tool is gathering up all the customer entity type's EAV attributes which Magento 1 stores in a foreign record in one of the customer_entity_* tables, that have now moved to the main customer_entity table in Magento 2.

While your data integrity checks may pass, this is still a data integrity issue on your end. The data integrity portion of the tool only checks to ensure all tables (referred to as a document) have been mapped out, columns (referred to as a field) have been mapped out, and foreign key checks pass in both databases prior to migration.

Somehow, bad customer records have been imported into your site possibly through direct SQL inserts or possibly using a PHP script and performing a Mage_Customer_Model_Customer::save() call on an object which actually didn't have all it's basic data that is typical for a Magento 1 site.

Here are two approaches to finding the problematic customer records in your database:

MySQL Approach (just outputs the entity_id of problematic customers)

SELECT 
    `main_table`.`entity_id`
FROM
    `customer_entity` AS `main_table`
LEFT JOIN
    `customer_entity_datetime` AS `at_dob`
    ON `at_dob`.`entity_id` = `main_table`.`entity_id`
    AND `at_dob`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'dob'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_datetime` AS `at_rp_token_created_at`
    ON `at_rp_token_created_at`.`entity_id` = `main_table`.`entity_id`
    AND `at_rp_token_created_at`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'rp_token_created_at'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_int` AS `at_default_billing`
    ON `at_default_billing`.`entity_id` = `main_table`.`entity_id`
    AND `at_default_billing`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'default_billing'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_int` AS `at_default_shipping`
    ON `at_default_shipping`.`entity_id` = `main_table`.`entity_id`
    AND `at_default_shipping`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'default_Shipping'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_int` AS `at_gender`
    ON `at_gender`.`entity_id` = `main_table`.`entity_id`
    AND `at_gender`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'gender'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_confirmation`
    ON `at_confirmation`.`entity_id` = `main_table`.`entity_id`
    AND `at_confirmation`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'confirmation'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_created_in`
    ON `at_created_in`.`entity_id` = `main_table`.`entity_id`
    AND `at_created_in`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'created_in'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_firstname`
    ON `at_firstname`.`entity_id` = `main_table`.`entity_id`
    AND `at_firstname`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'firstname'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_lastname`
    ON `at_lastname`.`entity_id` = `main_table`.`entity_id`
    AND `at_lastname`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'lastname'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_middlename`
    ON `at_middlename`.`entity_id` = `main_table`.`entity_id`
    AND `at_middlename`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'middlename'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_password_hash`
    ON `at_password_hash`.`entity_id` = `main_table`.`entity_id`
    AND `at_password_hash`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'password_hash'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_prefix`
    ON `at_prefix`.`entity_id` = `main_table`.`entity_id`
    AND `at_prefix`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'prefix'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_rp_token`
    ON `at_rp_token`.`entity_id` = `main_table`.`entity_id`
    AND `at_rp_token`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'rp_token'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_suffix`
    ON `at_suffix`.`entity_id` = `main_table`.`entity_id`
    AND `at_suffix`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'suffix'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
LEFT JOIN
    `customer_entity_varchar` AS `at_taxvat`
    ON `at_taxvat`.`entity_id` = `main_table`.`entity_id`
    AND `at_taxvat`.`attribute_id` = (
        SELECT `attribute_id`
        FROM `eav_attribute`
        WHERE `attribute_code` = 'taxvat'
        AND `entity_type_id` = (
            SELECT `entity_type_id` 
            FROM `eav_entity_type`
            WHERE `entity_type_code` = 'customer'
        )
    )
WHERE `at_dob`.`value` IS NULL
AND `at_rp_token_created_at`.`value` IS NULL
AND `at_default_billing`.`value` IS NULL
AND `at_default_shipping`.`value` IS NULL
AND `at_gender`.`value` IS NULL
AND `at_confirmation`.`value` IS NULL
AND `at_created_in`.`value` IS NULL
AND `at_firstname`.`value` IS NULL
AND `at_lastname`.`value` IS NULL
AND `at_middlename`.`value` IS NULL
AND `at_password_hash`.`value` IS NULL
AND `at_prefix`.`value` IS NULL
AND `at_rp_token`.`value` IS NULL
AND `at_suffix`.`value` IS NULL
AND `at_taxvat`.`value` IS NULL
;

PHP Approach (with a very dirty solution to the problem too)

Toss this file inside your Magento 1 site's MAGENTO_INSTALL_DIR/shell/ directory. Then run it from the command line. Please read the comments and change the code as you deem appropriate.

<?php

require 'abstract.php';

class Shell_CustomerCleanup extends Mage_Shell_Abstract
{
    public function run()
    {
        /** @var $customers Mage_Customer_Model_Resource_Customer_Collection */
        $customers = Mage::getResourceModel('customer/customer_collection');

        $customers
            ->addAttributeToFilter('dob', array('null' => true), 'left')
            ->addAttributeToFilter('rp_token_created_at', array('null' => true), 'left')
            ->addAttributeToFilter('default_billing', array('null' => true), 'left')
            ->addAttributeToFilter('default_shipping', array('null' => true), 'left')
            ->addAttributeToFilter('gender', array('null' => true), 'left')
            ->addAttributeToFilter('confirmation', array('null' => true), 'left')
            ->addAttributeToFilter('created_in', array('null' => true), 'left')
            ->addAttributeToFilter('firstname', array('null' => true), 'left')
            ->addAttributeToFilter('lastname', array('null' => true), 'left')
            ->addAttributeToFilter('middlename', array('null' => true), 'left')
            ->addAttributeToFilter('password_hash', array('null' => true), 'left')
            ->addAttributeToFilter('prefix', array('null' => true), 'left')
            ->addAttributeToFilter('rp_token', array('null' => true), 'left')
            ->addAttributeToFilter('suffix', array('null' => true), 'left')
            ->addAttributeToFilter('taxvat', array('null' => true), 'left')
        ;

        // We now have all your problematic customers. Now what do do with them?


        // Just output the Ids
        // echo implode(PHP_EOL, $customers->getAllIds());

        // Lets give them a 'created_in' value. These are still problematic
        // customer records, but at least they'll migrate. They will need to be
        // looked at and cleaned up later.
        //foreach ($customers as $customer) {
        //    $customer->setData('created_in', 'a galaxy far far away...');
        //    try {
        //        $customer->save();
        //    } catch (Exception $e) {
        //        echo $e->getMessage(),"\n";
        //    }
        //}
    }
}

$shell = new Shell_CustomerCleanup();
$shell->run();

NOTE This very problem can occur for customer_address records as well which have the same problem (lacking all data in the customer_address_entity_* tables that are about to be migrated into the customer_address_entity table).

Loading

@dfelton
Copy link
Contributor

@dfelton dfelton commented Jan 16, 2017

@victor-v-rad & @ilol

May I request that this issue be re-opened. While this is an issue with the data itself, I truly feel the Data Migration Tool could better handle this.

  • As is, it is the query found here that is entirely missing these problematic customer accounts I describe in the comment just above.
  • Following the code logic, it is then on this line that the Data Migration Tool will neglect to merge any additional data to the destination record.
  • Then... the logic of Migration\ResourceModel\Adapter\Mysql::insertMultiple() assumes all records supplied to all have a matching amount of field values for each insert record. Utilizing the first record of the array as the definition of fields for the insert query. Depending on if the first record contains the added customer EAV attributes, it may or may not get the field list correct.
  • However since these problematic records described above lack the additional EAV attribute information, the foreach loop which populates the binds gets improperly filled. Customer records which lack this collected EAV attribute data only have 10 array keys, while customer records which do contain the additional EAV attribute data will (with default configuration) have 25 array keys per record.

If I were to make a recommendation, it would be to add an else statement to this conditional to possibly throw an error to the user letting them know that their customer record is lacking all additional EAV attributes the tool is attempting to gather up. Another approach may be to add logic within this function to keep track of the array keys of the added EAV attributes by storing them within the first iteration of this foreach loop, and then add an else statement here which merges an array of null values for the array keys of all EAV attributes. This way all destination records remain the same size.

Loading

@dfelton
Copy link
Contributor

@dfelton dfelton commented Jan 17, 2017

For those who encounter this same problem due to issues with to their customer_address records, here is a PHP script for deleting the addresses using the Magento 1 models.

INSTALL_DIR/shell/address-cleanup.php

<?php

require 'abstract.php';

class Shell_AddressCleanup extends Mage_Shell_Abstract
{
    public function run()
    {
        /** @var $addresses Mage_Customer_Model_Resource_Address_Collection */
        /** @var $address Mage_Customer_Model_Address */
        $addresses = Mage::getResourceModel('customer/address_collection');
        $addresses
            ->addAttributeToFilter('city', array('null' => true), 'left')
            ->addAttributeToFilter('company', array('null' => true), 'left')
            ->addAttributeToFilter('country_id', array('null' => true), 'left')
            ->addAttributeToFilter('fax', array('null' => true), 'left')
            ->addAttributeToFilter('firstname', array('null' => true), 'left')
            ->addAttributeToFilter('lastname', array('null' => true), 'left')
            ->addAttributeToFilter('middlename', array('null' => true), 'left')
            ->addAttributeToFilter('postcode', array('null' => true), 'left')
            ->addAttributeToFilter('prefix', array('null' => true), 'left')
            ->addAttributeToFilter('region', array('null' => true), 'left')
            ->addAttributeToFilter('region_id', array('null' => true), 'left')
            ->addAttributeToFilter('street', array('null' => true), 'left')
            ->addAttributeToFilter('suffix', array('null' => true), 'left')
            ->addAttributeToFilter('telephone', array('null' => true), 'left')
            ->addAttributeToFilter('vat_id', array('null' => true), 'left')
            ->addAttributeToFilter('vat_is_valid', array('null' => true), 'left')
            ->addAttributeToFilter('vat_request_date', array('null' => true), 'left')
            ->addAttributeToFilter('vat_request_id', array('null' => true), 'left')
            ->addAttributeToFilter('vat_request_success', array('null' => true), 'left')
        ;

         foreach ($addresses as $address) {
             try {
                 $address->delete();
             } catch (Exception $e) {
                 echo $e->getMessage(),"\n";
             }
         }
    }
}

$shell = new Shell_AddressCleanup();
$shell->run();

Loading

@snoroozi
Copy link

@snoroozi snoroozi commented Apr 11, 2017

@dfelton Thanks so much for your contribution!

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
7 participants