Syntax error or access violation: 1071 Specified key was too long

Care to share a structure dump of the table in question?

Also, what's your core version.

As mentioned in #2. Please share the output of drush sqlq "SHOW CREATE TABLE courier_template_collection;". It seems like you are using http://drupal.org/project/courier and the entity in question is http://cgit.drupalcode.org/courier/tree/src/Entity/TemplateCollection.php. I'll ping @dpi about it.

@chx: I'm looking at TemplateCollection::baseFieldDefinitions() and this entity has two DER basefields. One field(owner) with cardinality 1 and other(templates) with unlimited. I don't think it is related to #2766193: Add tests for multiple base fields because field with an unlimited cardinality will have a separate table and basefield in question here is owner not templates.

courier_template_collection	CREATE TABLE `courier_template_collection` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `context` varchar(255) CHARACTER SET ascii DEFAULT NULL COMMENT 'The ID of the target entity.',
 `owner__target_id` int(10) unsigned DEFAULT NULL COMMENT 'The ID of the target entity.',
 `owner__target_type` varchar(32) DEFAULT NULL COMMENT 'The Entity Type ID of the target entity.',
 PRIMARY KEY (`id`),
 KEY `courier_template_collection__b3acb6e86e` (`context`),
 KEY `courier_template_collection__2d7659b160` (`owner__target_id`,`owner__target_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='The base table for courier_template_collection entities.'

Thank you!

This looks like a core bug (when the new spec of an alter is a varchar and has an index defined on it then the indexing shortening mechanism should fire) but fixing it there is a bother so let's just play dumb contrib author and code around it. Semantically this is more correct anyways.

Nice fix! Let's file a core bug as well. Can we somehow test this fix?

Yes, yes, we can. It's untrivial to test, it's ugly to test but it's doable.

Created #2770321: MySQL Schema class needs to check for existing indexes for alter queries for #9 and created #2770319: Testbot MySQL config doesn't use large prefix hiding potential errors for not failing patch in #13.
This is ready imo let's wait for @platinum1 to test it.

1. +++ b/dynamic_entity_reference.install
   @@ -5,6 +5,7 @@
    /**
   
   +++ b/src/Tests/Update/DerUpdateTest.php
   @@ -35,6 +35,8 @@ class DerUpdateTest extends UpdatePathTestBase {
   +    // Cause 1071 Specified key was too long; max key length is 767 bytes.
   +    \Drupal::database()->query('ALTER TABLE {entity_test__field_test} ROW_FORMAT=compact');
   

   This is a mysql bug https://bugs.mysql.com/bug.php?id=68453 and as it turns out if you set innodb_large_prefix OFF the error will go away.

2. +++ b/src/Plugin/Field/FieldType/DynamicEntityReferenceItem.php
   @@ -99,12 +99,12 @@ class DynamicEntityReferenceItem extends EntityReferenceItem {
   -        'type' => 'varchar',
   +        'type' => 'varchar_ascii',
   ...
   -        'type' => 'varchar',
   +        'type' => 'varchar_ascii',
   

   I don't think we should change this at all to fix a core bug #2770321: MySQL Schema class needs to check for existing indexes for alter queries. As far as this issue is concerned I think it is a won't fix and we should fix it in core.

Core doesn't support forcing large indexes (there's no ROW_FORMAT support as far as I can see) which a 255 + 32 utf8mb4 would create but semantically this is correct because machine names are ASCII and not higher Unicode characters.

Ok, let's commit #11 instead of #13. We don't need to test mysql bug in DER.

We need to, we actually need to because people will have COMPACT tables and we need to make sure we are doing the right update even for them. Yes, if the alterTable method would truncate the index itself as the core issue says it should then there would be no problem but it's core and I expect it to be not fixed any time soon since I won't. The only change we need to do there is to wrap it in if (Database::getConnection()->driver() == 'mysql').

Like this.

Opsie, that was just the fail patch, this is the complete one.

Seems like all the DB drivers are happy.

Thanks for the valuable report and the quick feedback! Committed.