"SQLSTATE[HY000]: General error: 1267 Illegal mix of collations" when filtering webforms with non-ASCII characters

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=': SELECT "name", "data" FROM "config" WHERE "collection" = :collection AND "name" IN ( :names__0 ); Array ( [:collection] => [:names__0] => user.role.ö ) in Drupal\Core\Config\DatabaseStorage->readMultiple() (line 111 of core/lib/Drupal/Core/Config/DatabaseStorage.php). Drupal\Core\Database\StatementWrapperIterator->execute(Array, Array) (Line: 851) Drupal\Core\Database\Connection->query('SELECT [name], [data] FROM {config} WHERE [collection] = :collection AND [name] IN ( :names__0 )', Array, Array) (Line: 111) Drupal\Core\Config\DatabaseStorage->readMultiple(Array) (Line: 95) Drupal\Core\Config\CachedStorage->readMultiple(Array) (Line: 165) Drupal\Core\Config\ConfigFactory->doLoadMultiple(Array) (Line: 136) Drupal\Core\Config\ConfigFactory->loadMultiple(Array) (Line: 181) Drupal\Core\Config\Entity\ConfigEntityStorage->doLoadMultiple(Array) (Line: 312) Drupal\Core\Entity\EntityStorageBase->loadMultiple(Array) (Line: 262) Drupal\Core\Entity\EntityStorageBase->load('ö') (Line: 578) Drupal\webform\WebformEntityListBuilder->getQuery('ö', '', '') (Line: 542) Drupal\webform\WebformEntityListBuilder->getTotal('ö', '', '') (Line: 234) Drupal\webform\WebformEntityListBuilder->buildInfo() (Line: 168) Drupal\webform\WebformEntityListBuilder->render() (Line: 23) Drupal\Core\Entity\Controller\EntityListController->listing('webform') call_user_func_array(Array, Array) (Line: 123) Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}() (Line: 638) Drupal\Core\Render\Renderer->executeInRenderContext(Object, Object) (Line: 121) Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->wrapControllerExecutionInRenderContext(Array, Array) (Line: 97) Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}() (Line: 181) Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1) (Line: 76) Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 53) Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 48) Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 28) Drupal\Core\StackMiddleware\ContentLength->handle(Object, 1, 1) (Line: 32) Drupal\big_pipe\StackMiddleware\ContentLength->handle(Object, 1, 1) (Line: 106) Drupal\page_cache\StackMiddleware\PageCache->pass(Object, 1, 1) (Line: 85) Drupal\page_cache\StackMiddleware\PageCache->handle(Object, 1, 1) (Line: 48) Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 51) Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 36) Drupal\Core\StackMiddleware\AjaxPageState->handle(Object, 1, 1) (Line: 51) Drupal\Core\StackMiddleware\StackedHttpKernel->handle(Object, 1, 1) (Line: 741) Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Comments & Activities

Issue created by @frankADsystopia.de
Comment 3 months ago →
frankADsystopia.de
Comment 3 months ago →
frankADsystopia.de
Version updated
Comment 3 months ago →
cilefen
I put the full stack trace in the issue description.
Comment 3 months ago →
🇨🇦Canada Liam Morland Ontario, CA 🇨🇦
Note that this does not happen on Postgres; it works as expected.
Comment 2 months ago →
🇯🇵Japan kensuke.kondo
I faced a similar problem when searching for the word '退会'. (It means 'unsubscribe' in Japanese.)
My environment is as follows.

Modules
drupal/core-recommended 10.3.2
drupal/webform 6.2.7

DB
MySQL
- 5.7.29 on local
- 5.7.40-log on acquia
Comment 2 months ago →
🇯🇵Japan kensuke.kondo
This filter returns a webform that contains permissions for the retrieved user name or user role ID.
In WebformEntityListBuilder, `$this->getEntityStorage('user_role')->load($keys)` returns error when $keys contains non-ASCII characters.
https://git.drupalcode.org/project/webform/-/blob/6.3.x/src/WebformEntit...

It is possible to handle this error in the Webform module, but it seems to me that an Issue could be created as a Drupal core issue.
I would like to hear your opinions.
Comment 2 months ago →
cilefen
Yes, 🐛 Validate view_name in ajaxView() Needs work , for example.
Comment about 2 months ago →
🇯🇵Japan kensuke.kondo
Sorry, An issue with the same content had already been created.

https://www.drupal.org/project/drupal/issues/3415478 🐛 \Drupal::entityTypeManager()->getStorage('user_role')->load() can't load non-english string Active
Comment about 2 months ago →
🇨🇦Canada Liam Morland Ontario, CA 🇨🇦
@kensuke.kondo are you saying that this issue would be resolved by fixing the core issue 🐛 \Drupal::entityTypeManager()->getStorage('user_role')->load() can't load non-english string Active ?
Comment about 2 months ago →
cilefen
Config entity names cannot be non-ASCII so there is no way to search for the cited strings. For Webform, if this UI is searching some entity metadata (like Title), that, I think is fine, but these non-ASCII chars must be ignored when searching config entity names (aka machine names). So, maybe this specific Webform tool is fixable.
Status changed to Closed: duplicate about 2 months ago11:18am 19 September 2024
Comment about 2 months ago →
cilefen
Comment about 2 months ago →
🇦🇺Australia jannakha Brisbane!
here's a solution to the underlying problem

https://www.drupal.org/project/drupal/issues/3475540 📌 Throw an understandable exception when there is an attempt to load config entities with disallowed characters Needs work

"SQLSTATE[HY000]: General error: 1267 Illegal mix of collations" when filtering webforms with non-ASCII characters

Problem/Motivation

Steps to reproduce

Comments & Activities