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

[guarinogabriel]

Preconditions

1. Magento 1.9.3.9
2. Magento 2.2.6
3. PHP 7.1.21
4. MySQL 5.7
5. Environment: MacOS with Valet Plus
6. Magento 1 database with bundle products

Steps to reproduce

1. Run the migration using the following command:
   sudo php bin/magento migrate:data -r -a -vvv /path/to/magento2-root/vendor/magento/data-migration-tool/etc/opensource-to-commerce/1.9.3.9/config.xml

Expected result

1. The migration works properly and the default mapped entities are migrated

Actual result

1. The migration is failing on the catalog_product_bundle_option_value table migration step (table with 27899 records):

[2018-09-29 22:05:24][DEBUG][mode: data][stage: data migration][step: Map Step][table: catalog_product_bundle_option_value]: migrating
100% [============================] Remaining Time: < 1 sec

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

Additional notes

Observation #1:

The following method is failing: Migration\ResourceModel\Adapter\Mysql::insertMultiple()

Method code:

/**
     * @param string $documentName
     * @param array $records
     * @return bool
     */
    protected function insertMultiple($documentName, $records)
    {
        $bind = [];
        $values = [];
        $colNum = count($records[0]);
        $fields = array_keys($records[0]);
        foreach ($records as $record) {
            foreach ($record as $value) {
                $bind[] = $value;
            }
            $values[] = '(' . implode(',', array_fill(0, $colNum, '?')) . ')';
        }
        if ($values && $fields) {
            $insertSql = sprintf(
                'INSERT INTO %s (%s) VALUES %s',
                $documentName,
                sprintf('`%s`', implode('`,`', $fields)),
                implode(',', $values)
            );
            $statement = $this->resourceAdapter->getConnection()->prepare($insertSql);

            $statement->execute($bind);
        }

        return true;
    }

Observation #2:

The error is thrown when the last batch from that table is tried to be inserted through SQL (all the previous batches/records were successfully imported).

Here is a log of the batch that was contained in the $bind variable and failed in the $statement->execute($bind) method:

2018-09-28T23:14:01+00:00 INFO (6): Array
(
    [0] => 88427
    [1] => 70166
    [2] => 0
    [3] => Main Bearings
    [4] => 34646
    [5] => 88428
    [6] => 70167
    [7] => 0
    [8] => Rod Bearings
    [9] => 34646
    [10] => 88429
    [11] => 70168
    [12] => 0
    [13] => Full Gasket/Sealing Set
    [14] => 34646
    [15] => 88430
    [16] => 70169
    [17] => 0
    [18] => Thrust Washer
    [19] => 34646
    [20] => 88431
    [21] => 70170
    [22] => 0
    [23] => Timing Belt A
    [24] => 34646
    [25] => 88432
    [26] => 70171
    [27] => 0
    [28] => Timing Belt B
    [29] => 34646
    [30] => 88433
    [31] => 70172
    [32] => 0
    [33] => Oil Pump
    [34] => 34646
    [35] => 88434
    [36] => 70173
    [37] => 0
    [38] => Piston Set
    [39] => 34647
    [40] => 88435
    [41] => 70174
    [42] => 0
    [43] => Piston Rings
    [44] => 34647
    [45] => 88436
    [46] => 70175
    [47] => 0
    [48] => Main Bearings
    [49] => 34647
    [50] => 88437
    [51] => 70176
    [52] => 0
    [53] => Rod Bearings
    [54] => 34647
    [55] => 88438
    [56] => 70177
    [57] => 0
    [58] => Full Gasket/Sealing Set
    [59] => 34647
    [60] => 88439
    [61] => 70179
    [62] => 0
    [63] => Piston Set
    [64] => 34648
    [65] => 88440
    [66] => 70180
    [67] => 0
    [68] => Piston Rings
    [69] => 34648
    [70] => 88441
    [71] => 70181
    [72] => 0
    [73] => Main Bearings
    [74] => 34648
    [75] => 88442
    [76] => 70182
    [77] => 0
    [78] => Rod Bearings
    [79] => 34648
    [80] => 88443
    [81] => 70183
    [82] => 0
    [83] => Full Gasket/Sealing Set
    [84] => 34648
    [85] => 88444
    [86] => 70185
    [87] => 0
    [88] => Timing Chain Kit
    [89] => 34648
    [90] => 88445
    [91] => 70186
    [92] => 0
    [93] => Oil Pump
    [94] => 34648
    [95] => 88452
    [96] => 70193
    [97] => 0
    [98] => Piston Set
    [99] => 34650
    [100] => 88453
    [101] => 70194
    [102] => 0
    [103] => Piston Rings
    [104] => 34650
    [105] => 88454
    [106] => 70195
    [107] => 0
    [108] => Main Bearings
    [109] => 34650
    [110] => 88455
    [111] => 70196
    [112] => 0
    [113] => Rod Bearings
    [114] => 34650
    [115] => 88456
    [116] => 70197
    [117] => 0
    [118] => Full Gasket/Sealing Set
    [119] => 34650
    [120] => 88457
    [121] => 70198
    [122] => 0
    [123] => Thrust Washer
    [124] => 34650
    [125] => 88458
    [126] => 70199
    [127] => 0
    [128] => Timing Belt
    [129] => 34650
    [130] => 88459
    [131] => 70200
    [132] => 0
    [133] => Oil Pump
    [134] => 34650
    [135] => 88460
    [136] => 70187
    [137] => 0
    [138] => Piston Set
    [139] => 34649
    [140] => 88461
    [141] => 70188
    [142] => 0
    [143] => Piston Rings
    [144] => 34649
    [145] => 88462
    [146] => 70189
    [147] => 0
    [148] => Main Bearings
    [149] => 34649
    [150] => 88463
    [151] => 70190
    [152] => 0
    [153] => Rod Bearings
    [154] => 34649
    [155] => 88464
    [156] => 70191
    [157] => 0
    [158] => Full Gasket/Sealing Set
    [159] => 34649
    [160] => 88465
    [161] => 70192
    [162] => 0
    [163] => Thrust Washer
    [164] => 34649
    [165] => 88466
    [166] => 70211
    [167] => 0
    [168] => Piston Set
    [169] => 34652
    [170] => 88467
    [171] => 70212
    [172] => 0
    [173] => Piston Rings
    [174] => 34652
    [175] => 88468
    [176] => 70213
    [177] => 0
    [178] => Main Bearings
    [179] => 34652
    [180] => 88469
    [181] => 70214
    [182] => 0
    [183] => Rod Bearings
    [184] => 34652
    [185] => 88470
    [186] => 70215
    [187] => 0
    [188] => Head Gasket Set
    [189] => 34652
    [190] => 88471
    [191] => 70216
    [192] => 0
    [193] => Lower Gasket Set
    [194] => 34652
    [195] => 88472
    [196] => 70217
    [197] => 0
    [198] => Thrust Washer
    [199] => 34652
    [200] => 88482
    [201] => 70218
    [202] => 0
    [203] => Piston Set
    [204] => 34653
    [205] => 88483
    [206] => 70219
    [207] => 0
    [208] => Piston Rings
    [209] => 34653
    [210] => 88484
    [211] => 70220
    [212] => 0
    [213] => Main Bearings
    [214] => 34653
    [215] => 88485
    [216] => 70221
    [217] => 0
    [218] => Rod Bearings
    [219] => 34653
    [220] => 88486
    [221] => 70222
    [222] => 0
    [223] => Full Gasket/Sealing Set
    [224] => 34653
    [225] => 88487
    [226] => 70223
    [227] => 0
    [228] => Freeze Plug Set (Brass)
    [229] => 34653
    [230] => 88488
    [231] => 70224
    [232] => 0
    [233] => Balance Shaft Bearings
    [234] => 34653
    [235] => 88489
    [236] => 70225
    [237] => 0
    [238] => Timing Chain Kit
    [239] => 34653
    [240] => 88490
    [241] => 70226
    [242] => 0
    [243] => Oil Pump
    [244] => 34653
    [245] => 88491
    [246] => 70227
    [247] => 0
    [248] => Piston Set
    [249] => 34654
    [250] => 88492
    [251] => 70228
    [252] => 0
    [253] => Piston Rings
    [254] => 34654
    [255] => 88493
    [256] => 70229
    [257] => 0
    [258] => Main Bearings
    [259] => 34654
    [260] => 88494
    [261] => 70230
    [262] => 0
    [263] => Rod Bearings
    [264] => 34654
    [265] => 88495
    [266] => 70231
    [267] => 0
    [268] => Full Gasket/Sealing Set
    [269] => 34654
    [270] => 88496
    [271] => 70232
    [272] => 0
    [273] => Thrust Washer
    [274] => 34654
    [275] => 88497
    [276] => 70233
    [277] => 0
    [278] => Piston Set
    [279] => 34655
    [280] => 88498
    [281] => 70234
    [282] => 0
    [283] => Piston Rings
    [284] => 34655
    [285] => 88499
    [286] => 70235
    [287] => 0
    [288] => Main Bearings
    [289] => 34655
    [290] => 88500
    [291] => 70236
    [292] => 0
    [293] => Rod Bearings
    [294] => 34655
    [295] => 88501
    [296] => 70237
    [297] => 0
    [298] => Full Gasket/Sealing Set
    [299] => 34655
    [300] => 88502
    [301] => 70238
    [302] => 0
    [303] => Thrust Washer
    [304] => 34655
    [305] => 88503
    [306] => 70239
    [307] => 0
    [308] => Timing Chain Kit
    [309] => 34655
    [310] => 88504
    [311] => 70240
    [312] => 0
    [313] => Oil Pump
    [314] => 34655
    [315] => 88505
    [316] => 70241
    [317] => 0
    [318] => Piston Set
    [319] => 34656
    [320] => 88506
    [321] => 70242
    [322] => 0
    [323] => Piston Rings
    [324] => 34656
    [325] => 88507
    [326] => 70243
    [327] => 0
    [328] => Main Bearings
    [329] => 34656
    [330] => 88508
    [331] => 70244
    [332] => 0
    [333] => Rod Bearings
    [334] => 34656
    [335] => 88509
    [336] => 70245
    [337] => 0
    [338] => Full Gasket/Sealing Set
    [339] => 34656
    [340] => 88510
    [341] => 70246
    [342] => 0
    [343] => Thrust Washer
    [344] => 34656
    [345] => 88511
    [346] => 70247
    [347] => 0
    [348] => Piston Set
    [349] => 34657
    [350] => 88512
    [351] => 70248
    [352] => 0
    [353] => Piston Rings
    [354] => 34657
    [355] => 88513
    [356] => 70249
    [357] => 0
    [358] => Main Bearings
    [359] => 34657
    [360] => 88514
    [361] => 70250
    [362] => 0
    [363] => Rod Bearings
    [364] => 34657
    [365] => 88515
    [366] => 70251
    [367] => 0
    [368] => Full Gasket/Sealing Set
    [369] => 34657
    [370] => 88516
    [371] => 70252
    [372] => 0
    [373] => Thrust Washer
    [374] => 34657
    [375] => 88517
    [376] => 70253
    [377] => 0
    [378] => Freeze Plug Set (Brass)
    [379] => 34657
    [380] => 88518
    [381] => 70254
    [382] => 0
    [383] => Timing Chain Kit
    [384] => 34657
    [385] => 88519
    [386] => 70255
    [387] => 0
    [388] => Oil Pump
    [389] => 34657
    [390] => 88520
    [391] => 70256
    [392] => 0
    [393] => Piston Pin Bushings
    [394] => 34657
    [395] => 88521
    [396] => 70201
    [397] => 0
    [398] => Piston Set
    [399] => 34651
    [400] => 88522
    [401] => 70202
    [402] => 0
    [403] => Piston Rings
    [404] => 34651
    [405] => 88523
    [406] => 70203
    [407] => 0
    [408] => Main Bearings
    [409] => 34651
    [410] => 88524
    [411] => 70204
    [412] => 0
    [413] => Rod Bearings
    [414] => 34651
    [415] => 88525
    [416] => 70205
    [417] => 0
    [418] => Head Gasket Set
    [419] => 34651
    [420] => 88526
    [421] => 70206
    [422] => 0
    [423] => Lower Gasket Set
    [424] => 34651
    [425] => 88527
    [426] => 70207
    [427] => 0
    [428] => Freeze Plug Set (Brass)
    [429] => 34651
    [430] => 88528
    [431] => 70208
    [432] => 0
    [433] => Cam Bearings
    [434] => 34651
    [435] => 88529
    [436] => 70209
    [437] => 0
    [438] => Timing Chain Kit
    [439] => 34651
    [440] => 88530
    [441] => 70210
    [442] => 0
    [443] => Oil Pump
    [444] => 34651
    [445] => 88531
    [446] => 32102
    [447] => 0
    [448] => Head Gasket Sets
    [449] => 19348
    [450] => 88532
    [451] => 32103
    [452] => 0
    [453] => Head Bolt Kits
    [454] => 19348
    [455] => 88540
    [456] => 70257
    [457] => 0
    [458] => Main Bearings
    [459] => 88541
    [460] => 61366
    [461] => 0
    [462] => Piston Set
    [463] => 27028
    [464] => 88542
    [465] => 61367
    [466] => 0
    [467] => Piston Rings
    [468] => 27028
    [469] => 88543
    [470] => 61369
    [471] => 0
    [472] => Rod Bearings
    [473] => 27028
    [474] => 88544
    [475] => 61370
    [476] => 0
    [477] => Full Gasket/Sealing Set
    [478] => 27028
    [479] => 88545
    [480] => 61371
    [481] => 0
    [482] => Balance Shaft Bearings
    [483] => 27028
    [484] => 88546
    [485] => 61372
    [486] => 0
    [487] => Timing Chain Kit
    [488] => 27028
    [489] => 88547
    [490] => 61373
    [491] => 0
    [492] => Oil Pump
    [493] => 27028
)

Thanks for your feedback in advance.

[victor-v-rad]

Hi @guarinogabriel

Please try this fix 13728b3

[guarinogabriel]

Hi @victor-v-rad,

Thanks for your feedback. I applied that fix but the issue is still reproducible:

[2018-10-02 15:47:47][DEBUG][mode: data][stage: data migration][step: Map Step][table: catalog_product_bundle_option_value]: migrating
100% [============================] Remaining Time: < 1 sec

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

Here is the exception trace:

Exception trace:
 () at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:182
 PDOStatement->execute() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:182
 Migration\ResourceModel\Adapter\Mysql->insertMultiple() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Adapter/Mysql.php:137
 Migration\ResourceModel\Adapter\Mysql->insertRecords() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/ResourceModel/Destination.php:52
 Migration\ResourceModel\Destination->saveRecords() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/Step/Map/Data.php:175
 Migration\Step\Map\Data->perform() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/Mode/AbstractMode.php:79
 Migration\Mode\AbstractMode->runStage() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:119
 Migration\Mode\Data->runData() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/Mode/Data.php:69
 Migration\Mode\Data->run() at /Users/gabriel/Projects/migration/vendor/magento/data-migration-tool/src/Migration/Console/MigrateDataCommand.php:51
 Migration\Console\MigrateDataCommand->execute() at /Users/gabriel/Projects/migration/vendor/symfony/console/Command/Command.php:241
 Symfony\Component\Console\Command\Command->run() at /Users/gabriel/Projects/migration/vendor/symfony/console/Application.php:843
 Symfony\Component\Console\Application->doRunCommand() at /Users/gabriel/Projects/migration/vendor/symfony/console/Application.php:193
 Symfony\Component\Console\Application->doRun() at /Users/gabriel/Projects/migration/vendor/magento/framework/Console/Cli.php:104
 Magento\Framework\Console\Cli->doRun() at /Users/gabriel/Projects/migration/vendor/symfony/console/Application.php:117
 Symfony\Component\Console\Application->run() at /Users/gabriel/Projects/migration/bin/magento:23

migrate:data [-r|--reset] [-a|--auto] [--] <config>

[guarinogabriel]

@victor-v-rad - Any thoughts on the error that is happening with that table?

Thanks!

[victor-v-rad]

You can send dump of this table or link to me and I will try to migrate reproduce

[guarinogabriel]

Hi @victor-v-rad,

I confirm that I just sent you the dump file via email.

Thanks,
Gabriel

[victor-v-rad]

Internal ticket MAGETWO-95648 to fix it