Problem/Motivation

When it tries to create the source table fails with this error.
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key
was too long; max key length is 3072 bytes: CREATE TABLE
{migrate_map_migrate_cases} (
`source_ids_hash` VARCHAR(64) NOT NULL COMMENT 'Hash of source
ids. Used as primary key',
`sourceid1` VARCHAR(32) CHARACTER SET ascii COLLATE ascii_general_ci
NOT NULL,
`sourceid2` INT NOT NULL,
`sourceid3` VARCHAR(255) NOT NULL,
`sourceid4` INT NOT NULL,
`sourceid5` INT NOT NULL,
`sourceid6` INT NOT NULL,
`sourceid7` VARCHAR(255) NOT NULL,
`sourceid8` DECIMAL(10, 2) NOT NULL,
`sourceid9` VARCHAR(255) NOT NULL,
`sourceid10` VARCHAR(255) NOT NULL,
`source_row_status` TINYINT unsigned NOT NULL DEFAULT 0 COMMENT
'Indicates current status of the source row',
`rollback_action` TINYINT unsigned NOT NULL DEFAULT 0 COMMENT
'Flag indicating what to do for this item on rollback',
`last_imported` INT unsigned NOT NULL DEFAULT 0 COMMENT 'UNIX
timestamp of the last time this row was imported',
`hash` VARCHAR(64) NULL DEFAULT NULL COMMENT 'Hash of source row
data, for detecting changes',
PRIMARY KEY (`source_ids_hash`),
INDEX `source` (`sourceid1`, `sourceid2`, `sourceid3`(191),
`sourceid4`, `sourceid5`, `sourceid6`, `sourceid7`(191), `sourceid8`,
`sourceid9`(191), `sourceid10`(191))
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8mb4 COMMENT
'Mappings from source identifier value(s) to
destination…';

Proposed resolution

I temporally fixed it removing the index but that means that everything will be slower, I need a better solution here.
Maybe it has something to be with the fact that I use InnoDB but this must work with this engine to, so we have to fix it.

Remaining tasks

User interface changes

API changes

Data model changes

Comments

edurenye created an issue. See original summary.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Murphysboro, IL, USA
CreditAttribution: mikeryan as a volunteer and at Virtuoso Performance commented
Title: in_map Sql fails when creating the source table » Map table cannot be created on InnoDB if source ID key is too large
Category: Bug report » Support request
Status: Active » Postponed (maintainer needs more info)

A consequence of #2843595: Add indexes to migrate_map_* tables, if your source IDs combined are too big to index in MySQL InnoDB.

I have to ask - why do you have 10 source ID fields? Do you really need 10 fields to uniquely identify each row of source data?

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Murphysboro, IL, USA
CreditAttribution: mikeryan as a volunteer and at Virtuoso Performance commented
Related issues: +#2843595: Add indexes to migrate_map_* tables
edurenye’s picture

edurenye CreditAttribution: edurenye as a volunteer commented
Status: Postponed (maintainer needs more info) » Closed (works as designed)

My bad, sorry. No, I don't need that many keys, I just thought I was defining the data types of the columns not the keys.
I guess it's difficult that anybody might need that many keys, so I close it.
Thank you very much.

mglaman’s picture

mglaman
Primary language English
Location WI, USA
CreditAttribution: mglaman as a volunteer commented

This causes issues when there are 5 keys, even. Such as a taxonomy term name, where keys matter to ensure parent mapping.

This is fine:

  keys:
    - Location1
    - Location2
    - Location3
    - Location4

This throws error over length

  keys:
    - Location1
    - Location2
    - Location3
    - Location4
    - Location5

Is there a way to somehow cause the \Drupal\migrate\Plugin\migrate\id_map\Sql::ensureTables to use MyISAM (if that'll solve the problem, even?)

EDIT:

Nevermind. MyISAM limit is 1,000.

mglaman’s picture

mglaman
Primary language English
Location WI, USA
CreditAttribution: mglaman as a volunteer commented

Since I commented I figure I'd share my solution.

ID Map plugin

<?php

namespace Drupal\mahmodule\Plugin\migrate\id_map;

use Drupal\migrate\Plugin\migrate\id_map\Sql;

/**
 * Defines the sql based ID map implementation.
 *
 * It creates one map and one message table per migration entity to store the
 * relevant information.
 *
 * @PluginID("sql_large_key")
 */
class LargeKeySql extends Sql {

  protected function getFieldSchema(array $id_definition) {
    $schema =  parent::getFieldSchema($id_definition);
    if ($schema['type'] == 'varchar') {
      $schema['length'] = 100;
    }
    return $schema;
  }

}

Migration

idMap:
  plugin: sql_large_key
source:
  plugin: csv_by_key
  path: data/SOURCE.csv
  header_row_count: 1
  keys:
    - Location1
    - Location2
    - Location3
    - Location4
    - Location5
    - Location6
2dareis2do’s picture

2dareis2do CreditAttribution: 2dareis2do commented

Hmm, I am getting similar issue. I have posted link here https://www.drupal.org/project/drupal/issues/3044897

The import starts ok then seems to choke, where the source id is around 300 characters. I don't really understand why this is as from what I can see the source id is hashed anyway!? If someone could explain what is happening during the import and what sourceid1 is I would love to know.

Currently testing on mariadb:10.1-2.3.5

The 'mi' alias is deprecated and will no longer work with Drush 9. Consider the use of 'mim' alias instead.                                                              [warning]
array(1) {
  ["guid"]=>
  string(63) "CAIiENKCdLvoamdLmEH0GpY4ItEqGQgEKhAIACoHCAowzuOICzCZ4ocDMN6YowY"
}
array(1) {
  ["guid"]=>
  string(170) "CBMieGh0dHBzOi8vd3d3LmxvbmRvbm5ld3NvbmxpbmUuY28udWsvaWNlLWhvY2tleS1hbGwtY2hhbmdlLWF0LXN0cmVhdGhhbS1hcy1hZGFtLWNhcnItdG8tcmVwbGFjZS1oZWFkLWNvYWNoLWplcmVteS1jb3JuaXNoL9IBAA"
}
array(1) {
  ["guid"]=>
  string(184) "CBMiggFodHRwOi8vd3d3LnN3bG9uZG9uZXIuY28udWsvamVyZW15LWNvcm5pc2gtcHJvdWQtb2YtaGlzLXRpbWUtYXQtc3RyZWF0aGFtLWljZS1ob2NrZXktYXMtaGVhZC1jb2FjaC10ZW51cmUtZW5kcy1pbi1wbGF5LW9mZi1kZWZlYXQv0gEA"
}
array(1) {
  ["guid"]=>
  string(300) "CBMiR2h0dHBzOi8vbGFib3VybGlzdC5vcmcvMjAxOS8wMy9sYWJvdXItbGVmdC10YWtlcy1vdmVyLWF0LXN0cmVhdGhhbS1hZ20v0gGSAWh0dHBzOi8vbGFib3VybGlzdC1vcmcuY2RuLmFtcHByb2plY3Qub3JnL3Yvcy9sYWJvdXJsaXN0Lm9yZy8yMDE5LzAzL2xhYm91ci1sZWZ0LXRha2VzLW92ZXItYXQtc3RyZWF0aGFtLWFnbS8_YW1wJmFtcF9qc192PTAuMSN3ZWJ2aWV3PTEmY2FwPXN3aXBl"
}
PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'sourceid1' at row 1 in                                                         [error]
/var/www/html/web/core/lib/Drupal/Core/Database/Statement.php:59
Stack trace:
#0 /var/www/html/web/core/lib/Drupal/Core/Database/Statement.php(59): PDOStatement->execute(Array)
#1 /var/www/html/web/core/lib/Drupal/Core/Database/Connection.php(625): Drupal\Core\Database\Statement->execute(Array, Array)
#2 /var/www/html/web/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php(358): Drupal\Core\Database\Connection->query('INSERT INTO {mi...', Array, Array)
#3 /var/www/html/web/core/lib/Drupal/Core/Database/Driver/mysql/Insert.php(32): Drupal\Core\Database\Driver\mysql\Connection->query('INSERT INTO {mi...', Array, Array)
#4 /var/www/html/web/core/lib/Drupal/Core/Database/Query/Merge.php(371): Drupal\Core\Database\Driver\mysql\Insert->execute()
#5 /var/www/html/web/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php(651): Drupal\Core\Database\Query\Merge->execute()
#6 /var/www/html/web/core/modules/migrate/src/MigrateExecutable.php(248): Drupal\migrate\Plugin\migrate\id_map\Sql->saveIdMapping(Object(Drupal\migrate\Row), Array, 3)
#7 /var/www/html/vendor/drush/drush/includes/drush.inc(722): Drupal\migrate\MigrateExecutable->import()
#8 /var/www/html/vendor/drush/drush/includes/drush.inc(713): drush_call_user_func_array(Array, Array)
#9 /var/www/html/web/modules/contrib/migrate_tools/migrate_tools.drush.inc(317): drush_op(Array)
#10 [internal function]: _drush_migrate_tools_execute_migration(Object(Drupal\migrate\Plugin\Migration), 'rss_google_node', Array)
#11 /var/www/html/web/modules/contrib/migrate_tools/migrate_tools.drush.inc(272): array_walk(Array, '_drush_migrate_...', Array)
#12 /var/www/html/vendor/drush/drush/includes/command.inc(422): drush_migrate_tools_migrate_import('rss_google_node')
#13 /var/www/html/vendor/drush/drush/includes/command.inc(231): _drush_invoke_hooks(Array, Array)
#14 /var/www/html/vendor/drush/drush/includes/command.inc(199): drush_command('rss_google_node')
#15 /var/www/html/vendor/drush/drush/lib/Drush/Boot/BaseBoot.php(67): drush_dispatch(Array)
#16 /var/www/html/vendor/drush/drush/includes/preflight.inc(66): Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#17 /var/www/html/vendor/drush/drush/drush.php(12): drush_main()
#18 {main}

Next Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'sourceid1' at row 1: INSERT INTO
{migrate_map_rss_google_node} (source_ids_hash, sourceid1, source_row_status, rollback_action, hash) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1,
:db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array
(
    [:db_insert_placeholder_0] => d73e46662cd939e18a9dd30b95f9605a173803b88ec480e81ace4873917621a2
    [:db_insert_placeholder_1] =>
CBMiR2h0dHBzOi8vbGFib3VybGlzdC5vcmcvMjAxOS8wMy9sYWJvdXItbGVmdC10YWtlcy1vdmVyLWF0LXN0cmVhdGhhbS1hZ20v0gGSAWh0dHBzOi8vbGFib3VybGlzdC1vcmcuY2RuLmFtcHByb2plY3Qub3JnL3Yvcy9sYWJvdXJsaXN0Lm9yZy8yMDE5LzAzL2xhYm91ci1sZWZ0LXRha2VzLW92ZXItYXQtc3RyZWF0aGFtLWFnbS8_YW1wJmFtcF9qc192PTAuMSN3ZWJ2aWV3PTEmY2FwPXN3aXBl
    [:db_insert_placeholder_2] => 3
    [:db_insert_placeholder_3] => 0
    [:db_insert_placeholder_4] => 
)
 in /var/www/html/web/core/lib/Drupal/Core/Database/Connection.php:686
Stack trace:
#0 /var/www/html/web/core/lib/Drupal/Core/Database/Connection.php(650): Drupal\Core\Database\Connection->handleQueryException(Object(PDOException), 'INSERT INTO
{mi...', Array, Array)
#1 /var/www/html/web/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php(358): Drupal\Core\Database\Connection->query('INSERT INTO {mi...', Array, Array)
#2 /var/www/html/web/core/lib/Drupal/Core/Database/Driver/mysql/Insert.php(32): Drupal\Core\Database\Driver\mysql\Connection->query('INSERT INTO {mi...', Array, Array)
#3 /var/www/html/web/core/lib/Drupal/Core/Database/Query/Merge.php(371): Drupal\Core\Database\Driver\mysql\Insert->execute()
#4 /var/www/html/web/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php(651): Drupal\Core\Database\Query\Merge->execute()
#5 /var/www/html/web/core/modules/migrate/src/MigrateExecutable.php(248): Drupal\migrate\Plugin\migrate\id_map\Sql->saveIdMapping(Object(Drupal\migrate\Row), Array, 3)
#6 /var/www/html/vendor/drush/drush/includes/drush.inc(722): Drupal\migrate\MigrateExecutable->import()
#7 /var/www/html/vendor/drush/drush/includes/drush.inc(713): drush_call_user_func_array(Array, Array)
#8 /var/www/html/web/modules/contrib/migrate_tools/migrate_tools.drush.inc(317): drush_op(Array)
#9 [internal function]: _drush_migrate_tools_execute_migration(Object(Drupal\migrate\Plugin\Migration), 'rss_google_node', Array)
#10 /var/www/html/web/modules/contrib/migrate_tools/migrate_tools.drush.inc(272): array_walk(Array, '_drush_migrate_...', Array)
#11 /var/www/html/vendor/drush/drush/includes/command.inc(422): drush_migrate_tools_migrate_import('rss_google_node')
#12 /var/www/html/vendor/drush/drush/includes/command.inc(231): _drush_invoke_hooks(Array, Array)
#13 /var/www/html/vendor/drush/drush/includes/command.inc(199): drush_command('rss_google_node')
#14 /var/www/html/vendor/drush/drush/lib/Drush/Boot/BaseBoot.php(67): drush_dispatch(Array)
#15 /var/www/html/vendor/drush/drush/includes/preflight.inc(66): Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#16 /var/www/html/vendor/drush/drush/drush.php(12): drush_main()
#17 {main}
2dareis2do’s picture

2dareis2do CreditAttribution: 2dareis2do commented

ok switching unique field to be the shorter link field and this migrates/imports the feed with any issues. Using GUID definitely as the unique field definitely seems to create problems in mysql.

This is the feed I was using.

https://news.google.com/rss/search?q=streatham&hl=en-GB&gl=UK&ceid=GB:en