Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.Problem/Motivation
Created an entity with the basetable name "group". Got MySQL error several times. However, my module was fully working. What i noticed is that the error comes from the entity annotation.
It is possible to create a table with MySQL when using prereserved names like group, with, in, select, .. if your SQL statment uses inverted comma. The annotation cannot handle this so the sql statement generator (i have no clue how D8 creates sql statments) should automatically do this.
Example:
Faulty D8 Annotation
/**
* Defines the select entity
*
* @ContentEntityType(
* id = "select_entity",
* label = @Translation("Selector"),
* base_table = "select",
* entity_keys = {
* "id" = "sid",
* },
* )
*/SQL generates
CREATE TABLE select (ID int); fails
SQL should generate
CREATE TABLE ´select´ (ID int); works
Proposed resolution
For developer experience all table names should be available so D8 needs to create statements with inverted comma.
Remaining tasks
- Check for contra arguments
- Implement the new auto generation for statements
Comments
Comment #1
steamx CreditAttribution: steamx commentedComment #2
dawehnerDrupal kinda of never supported reserved keywords afaik, given that we never went with using "user" as base table for users.
Comment #3
steamx CreditAttribution: steamx commentedWell the problem is not really about reserved keywords but that drupal creates "faulty" sql statements. From my point of view the statement should use inverted comma. The reserved keyword thing is just a bug that comes from not using those.
Comment #4
steamx CreditAttribution: steamx commentedComment #5
BerdirQuoting identifiers is a very messy business and hasn't been done for reasons. It's not always pretty, but neither is quoting it.
Entity types and table names should be prefixed with a module name anyway to avoid clashes.
See http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html#.VE9... for a detailed explanation of how quoting results in different results depending on the database and the problems that come with it.
I'm also pretty sure that there are multiple existing issues around this topic.
Comment #19
quietone CreditAttribution: quietone at PreviousNext commented@steamx, Thank you for the idea!
Comment #5 is correct that are multiple issues of this topic.
The proposal doesn't met the Criteria for evaluating proposed changes. In this case, there is not demonstrated demand and support for the change.
Comment #20
quietone CreditAttribution: quietone at PreviousNext commentedIn Slack, #contribute @Berdir said that this is actually a duplicate of #2986452: Database reserved keywords need to be quoted as per the ANSI standard, I have added that as related and changed the status here.
Thanks @Berdir