Can you specify the table collation when creating a table via db_create_table() (or hook_schema())? I didn't find any documentation (or issue) on this, but I hope it is possible? Otherwise, this is definitely a feature request for D8.

CommentFileSizeAuthor
#7 drupal-1144644-7-enabling-myqsl-collation.patch2.93 KBBarisW

Comments

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Title: How to specify the collation when creating a database table? » Enable specifying the collation when creating a database table
Version: 7.x-dev » 8.x-dev
Category: support » feature

I'll take that as a "no".

Feature request: While the "general_ci" collation makes sense in most cases, there are some when this leads to unexpected, and unwanted, results. E.g., when using primary / unique keys of type varchar, "A" and "à" (and lots of others) will be treated equivalently, resulting in possible collissions and, consequently, exceptions. If a module developer knows that this might happen for a module, they should be able to specify another collation to circumvent this (utf8_bin, probably).
A "collation" key in the schema structure should do. Alternatively, I'd also be content with a "binary" key (defaulting to FALSE) which switches to binary collation (utf8_bin) when set.

fabsor’s picture

fabsor commented

I agree that this could be an issue, however, you can set a global setting in settings.php, which affects all tables. Which setting would take precedence upon installation?

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented

Yes, I know, but that doesn't solve the issue. We want to be able to choose the collation from the module, not depend on the user to change that setting. For most tables, the default setting is the better choice after all.

sylvain lecoy’s picture

sylvain lecoy commented

We should add a collation field in a column definition:

<?php
'description' => 'Stores character data.',
    'fields' => array(
      'cid' => array(
        'description' => 'Primary Key: Unique character ID.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'uid' => array(
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
        'description' => "User's {users}.uid.",
      ),
      'name' => array(
        'type' => 'varchar',
        'length' => 32,
        'not null' => TRUE,
        'description' => "Character's name.",
        'collation' => 'latin1_general_cs', // I need case sensitive storage for this field only !
      ),
?>
jefkin’s picture

jefkin commented

drunken monkey ++

Sylvain Lecoy ++

Nice expression of the needed configuration options Sylvain

BarisW’s picture

BarisW commented

Deleted my comment, thought this was a Search API DB issue, but it seemed a Drupal Core thing.

BarisW’s picture

BarisW commented
Status: Active » Needs review
StatusFileSize
new drupal-1144644-7-enabling-myqsl-collation.patch2.93 KB

Patch attached to add this in D8. Allows setting collation on a table-base or on a column-base.

pancho’s picture

pancho
Location UTC+2 🇪🇺 EU
commented
Issue tags: +database collation, +D8MI, +needs profiling

Nice, but what about performance?
Crell says that joining tables with different collations leads to indexes being ignored and thus incredibly slow queries, see #772678-47: Database default collation is not respected.
Does this also apply to differing collations within a table? We should find out.

michlis’s picture

michlis commented
Assigned: Unassigned » michlis
Issue summary: View changes

Patch applies to latest D8 version, testing in progress.

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

marvil07’s picture

marvil07 commented
Status: Needs review » Needs work
Issue tags: +Needs tests
Related issues: +#1237252: DB Case Sensitivity: Allow BINARY attribute in MySQL

Adding related issue: binary collation in mysql can be specified now after #1237252.

It has been a while since this patch was written, it does not apply anymore, moving to NW, also I am wondering about how to test this.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.6 was released on August 1, 2018 and is the final bugfix release for the Drupal 8.5.x series. Drupal 8.5.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.6.0 on September 5, 2018. (Drupal 8.6.0-rc1 is available for testing.)

Bug reports should be targeted against the 8.6.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.8.x-dev

Drupal 8.6.x will not receive any further development aside from security fixes. Bug reports should be targeted against the 8.8.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.9.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.7 was released on June 3, 2020 and is the final full bugfix release for the Drupal 8.8.x series. Drupal 8.8.x will not receive any further development aside from security fixes. Sites should prepare to update to Drupal 8.9.0 or Drupal 9.0.0 for ongoing support.

Bug reports should be targeted against the 8.9.x-dev branch from now on, and new development or disruptive changes should be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.2.x-dev

Drupal 8 is end-of-life as of November 17, 2021. There will not be further changes made to Drupal 8. Bugfixes are now made to the 9.3.x and higher branches only. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.15 was released on June 1st, 2022 and is the final full bugfix release for the Drupal 9.3.x series. Drupal 9.3.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.4.x-dev branch from now on, and new development or disruptive changes should be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.9 was released on December 7, 2022 and is the final full bugfix release for the Drupal 9.4.x series. Drupal 9.4.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.5.x-dev branch from now on, and new development or disruptive changes should be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

mxr576’s picture

mxr576
Primary language Hungarian
Location Hungary
commented

Just referenced this issue on #3507777: User names uniqueness is no longer accent-insensitive because 14 years later I guess we are still suffering the consequence of general_ci collation described in comment #1.

generalredneck’s picture

generalredneck
him/his
Primary language English
Location Texas, USA 🇺🇸
commented

Same. I've been looking into an issue where sorting changed with the version of mysql version where some punctuation in-explicitly started sorting before numbers instead of after numbers and it lead me the collation route. see #3564836: In Mysql 5.7/8.0 Negative decimal numbers sort incorrectly

Version: 11.x-dev » main

Drupal core is now using the main branch as the primary development branch. New developments and disruptive changes should now be targeted to the main branch.

Read more in the announcement.