Pantheon Community

EntityStorage SQL Exception

Hello,
I am getting an SQL Exception in Drupal 8 deployed on pantheon when using vendor modules (apigee kickstart developer portal and apigee monetization). I have queried this with the Vendor and they believe it is to do with the way that pantheon has set up the database table when Drupal core is deployed. The actual error is:

Drupal\Core\Entity\EntityStorageException : SQLSTATE[HY000]: General error: 1270 Illegal mix of collations (ascii_general_ci,IMPLICIT), (utf8mb4_general_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE) for operation ’ IN ': SELECT tag, invalidations FROM {cachetags} WHERE tag IN ( :tags__0, :tags__1 ); Array ( [:tags__0] => rate_plan:payment_method_-credit_card_payment_method-credit_cardvanilla_rp [:tags__1] => rate_plan:payment_method-credit_card_payment_method-credit_card–_vanilla_rp:values ) in Drupal\apigee_edge\Entity\Storage\EdgeEntityStorageBase->withController() (line 220 of /srv/bindings/002185bf61f34e9ba6096f6fd9c9db39/code/web/modules/contrib/apigee_edge/src/Entity/Storage/EdgeEntityStorageBase.php ).

I checked the character set and collation for the ‘cachetags’ table and both columns have different values:

select table_name,column_name,character_set_name,collation_name from information_schema.columns where table_schema=‘pantheon’ and table_name=‘cachetags’;

+------------+---------------+--------------------+------------------+
| table_name | column_name   | character_set_name | collation_name   |
+------------+---------------+--------------------+------------------+
| cachetags  | tag           | ascii              | ascii_general_ci |
| cachetags  | invalidations | NULL               | NULL             |
+------------+---------------+--------------------+------------------+

2 rows in set (0.110 sec)

How can I change the character set and collation to utf8mb4/utf8mb4_general_ci?

2 Likes

Hello!
Thanks for your question. Happy to report there’s a doc for that!

Basically, you’re going to use a Drush command via Pantheon’s command-line tool Terminus to convert the database. Instructions on converting your db collation is detailed in our Platform Considerations doc: https://pantheon.io/docs/platform-considerations#emoji-support.

Important notes: back up the database first, and try it on the test environment before proceeding w/your live site.

Hope that helps.

2 Likes

Hi,
Can you confirm the command line tool can be run on a database hosted on Pantheon environment? I do not have command line access to it and it would not be possible to modify the configs (my.cnf and settings.php) and also the instructions seem to be more aligned to a local database - which would be ok if I had my own AMP stack.
Regards,
Sunin

PS - A support ticket now exists for the issue. Ref: 458941.

Thanks for the link. Having looked at the info, I did not think they were suitable for database that was hosted on pantheon and one which I did not have direct access to.
I did manage to however work out some form of SQL command I can run on the database - after establishing a connection via terminus. It boiled down to running the following command to alter the character set of ‘tag’ field in cachetags table:

alter table cachetags modify tag varchar(255) character set utf8mb4 collate utf8mb4_general_ci;

after running

ALTER TABLE cachetags ROW_FORMAT=DYNAMIC;

to prevent ‘index column size too large’.

This appears to resolve the issue.

Hi,
The above instructions are from Pantheon’s technical documentation, and are specifically for working with databases hosted on Pantheon’s environment. I’m glad to hear you got your issue sorted!

2 Likes