Problem/Motivation

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'entity_test_base_field_display_field__test_display_configurable__format' is too long: CREATE TABLE {entity_test} ( `id` INT unsigned NOT NULL auto_increment COMMENT 'The ID of the test entity.', `type` VARCHAR(255) NOT NULL COMMENT 'The bundle of the test entity.', `uuid` VARCHAR(128) NOT NULL COMMENT 'The UUID of the test entity.', `langcode` VARCHAR(12) NOT NULL COMMENT 'The language code of the test entity.', `name` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The name of the test entity.', `user_id` INT unsigned NOT NULL COMMENT 'The ID of the associated user.', `test_no_display__value` VARCHAR(255) NULL DEFAULT NULL, `test_no_display__format` VARCHAR(255) NULL DEFAULT NULL, `test_display_configurable__value` VARCHAR(255) NULL DEFAULT NULL, `test_display_configurable__format` VARCHAR(255) NULL DEFAULT NULL, `test_display_non_configurable__value` VARCHAR(255) NULL DEFAULT NULL, `test_display_non_configurable__format` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `entity_test_base_field_display_field__uuid__value` (`uuid`), INDEX `entity_test_base_field_display_field__user_id__target_id` (`user_id`), INDEX `entity_test_base_field_display_field__test_no_display__format` (`test_no_display__format`), INDEX `entity_test_base_field_display_field__test_display_configurable__format` (`test_display_configurable__format`), INDEX `entity_test_base_field_display_field__test_display_non_configurable__format` (`test_display_non_configurable__format`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'The base table for entity_test_base_field_display entities.'; Array ( ) in Drupal\Core\Database\Connection->query() (line 569 of /var/www/d8/core/lib/Drupal/Core/Database/Connection.php).

Proposed resolution

Truncate the maximum length and append some hash of the index name.

Remaining tasks

User interface changes

API changes

CommentFileSizeAuthor
#8 interdiff.txt829 bytesdawehner
#8 drupal-2301875-8.patch3.71 KBdawehner
#7 schema-2301875-7.patch3.71 KBplach
#7 schema-2301875-7.interdiff.txt619 bytesplach
#4 schema-2301875-4.patch3.71 KBdawehner
#4 interdiff.txt2.56 KBdawehner
#3 schema-2301875-3.patch2.96 KBdawehner
#1 2301875.patch858 bytesdawehner

Comments

dawehner’s picture

dawehner
Primary language German
commented
StatusFileSize
new 2301875.patch858 bytes

Maybe something along like

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

We found the problem, EntityTestBaseFieldDisplay has the same base table as EntityTest, so it doesn't install itself a table, somehow, the order of entities has to be different for you, so it this one is checked first.

dawehner’s picture

dawehner
Primary language German
commented
Status: Active » Needs review
StatusFileSize
new schema-2301875-3.patch2.96 KB

Indeed a krsort() as well as a ksort() for the entity definitions in the module handler works fine.

I wonder whether we should also rename the table for that test entity?

Here is a patch which ensures that the index has the right name. This allows me to run these tests again.

dawehner’s picture

dawehner
Primary language German
commented
StatusFileSize
new interdiff.txt2.56 KB
new schema-2301875-4.patch3.71 KB

Here is a unit test.

plach’s picture

plach
he/him
Primary language Italian
Location Venezia
commented

I wonder whether we should also rename the table for that test entity?

Yep, I think so: different entity types are not supposed to share tables.

+++ b/core/lib/Drupal/Core/Entity/Schema/ContentEntitySchemaHandler.php
@@ -258,6 +258,33 @@ protected function getFieldSchemaData($field_name, array $field_schema, array $c
+      $entity_type = substr($entity_type_id, 0, 34);

I think the limit can be 36.

Also what about factoring out this logic in a Component static method so we can re-use it both here and when generating the field table name?

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

Yeah, we should absolutely update the table and also check if there are more of those. That module has lots of copy & pasted entity types.

plach’s picture

plach
he/him
Primary language Italian
Location Venezia
commented
StatusFileSize
new schema-2301875-7.interdiff.txt619 bytes
new schema-2301875-7.patch3.71 KB 
+++ b/core/lib/Drupal/Core/Entity/Schema/ContentEntitySchemaHandler.php
@@ -258,6 +258,33 @@ protected function getFieldSchemaData($field_name, array $field_schema, array $c
+   * Generates a safe schema identifier (name of an index, column name etc.).
+   * @param string $entity_type_id

Fixed missing blank line :)

dawehner’s picture

dawehner
Primary language German
commented
StatusFileSize
new drupal-2301875-8.patch3.71 KB
new interdiff.txt829 bytes

There we go.

dawehner’s picture

dawehner
Primary language German
commented

Related issue

#2302057: Provide a component to truncate sql strings to a certain level

dawehner’s picture

dawehner
Primary language German
commented
Issue summary: View changes
plach’s picture

plach
he/him
Primary language Italian
Location Venezia
commented
Issue summary: View changes
Status: Needs review » Reviewed & tested by the community

Awesome, thanks!

Please add Berdir in the commit message, he found out what was going on...
(genius at work :)

plach’s picture

plach
he/him
Primary language Italian
Location Venezia
commented
Issue summary: View changes

crosspost...

plach’s picture

plach
he/him
Primary language Italian
Location Venezia
commented

@dawehner:

We should open also a follow-up to rename tables...

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
commented
Status: Reviewed & tested by the community » Fixed

Committed 5135cbb and pushed to 8.x. Thanks!

  • alexpott committed 5135cbb on 8.x 
    Issue #2301875 by dawehner, plach: Fixed Invalid SQL when running tests.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.