prisma: Not able to set utf8mb4 character collation, running into encoding issues with emojis.

Running into this issue as well, have there been any updates?

@PanMan I’m not sure what’s causing this. I’ll let you know if I find anything related.

@janpio yes, the reason I was able to notice was because I had multiple servers. And one with SSL had this issue. but the one without SSL worked fine. So tried adding additional logs and figured out that charset for client and connection is changing.

[
  { Variable_name: 'character_set_client', Value: 'utf8mb3' },
  { Variable_name: 'character_set_connection', Value: 'utf8mb3' },
  { Variable_name: 'character_set_database', Value: 'utf8mb4' },
  { Variable_name: 'character_set_filesystem', Value: 'binary' },
  { Variable_name: 'character_set_results', Value: 'utf8mb3' },
  { Variable_name: 'character_set_server', Value: 'utf8mb4' },
  { Variable_name: 'character_set_system', Value: 'utf8mb3' }
] 

I’m trying to find the root cause. I’ll try to setup and share a sample code.

No, to me this looks like a potential real bug which needs to be confirmed by anyone on our side doing a reproduction using the information your provided (which sounds elaborate enough - which is great).

There is no parameter to set the encoding/collation with Prisma right now (see https://www.prisma.io/docs/concepts/database-connectors/mysql#connection-details), as that never came up as a problem. If we can confirm this, and can not find anything else wrong, this might be the time to introduce that parameter.

No, we do not give ETAs about bug fixes or functionality. We get to it, when we get to it.

Hey Everyone,

I was able to conditionally reproduce this.

• When the SSL is enforced, the charset changes to utf8mb3 and I get the emoji issue
• When the SSL is not enforced, the charset remains utf8mb4; it works as expected.

Hi there,

Ok so I came across this issue about the utf8mb3 error when everything is set to utf8mb4.

Just to let know in my case this was an issue coming from the DB server indeed, but because in MariaDB > 10.6 there’s a variable old_mode which was empty before and now the default value has changed.

The documentation regarding this variable tells us a bit more:

UTF8_IS_UTF8MB3

From MariaDB 10.6.1, the main name of the previous 3 byte utf character set has been changed to utf8mb3. If set, the default, utf8 is an alias for utf8mb3. If not set, utf8 would be an alias for utf8mb4.

and to confirm:

mysql> show variables like 'old_mode';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| old_mode      | UTF8_IS_UTF8MB3 |
+---------------+-----------------+
1 row in set (0.03 sec)

mysql> 

In my case I have a MariaDB Galera Cluster behind MaxScale, I had to add old_mode = "" to all nodes configuration and restart. After restarting all the nodes check if the configuration has been applied:

mysql> show variables like 'old_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_mode      |       |
+---------------+-------+
1 row in set (0.02 sec)

mysql> 

And any issue related should be fixed.

Thanks for looking into this!

I have since set up multiple prisma servers, and never had this issue again, but I also don’t know what caused it in this instance… I think there we still run with the workaround to do “SET NAMES utf8mb4” on the server-side… I think I can open a new RDS instance for you, but that would be the same as you starting an RDS instance I think… our live servers now have the “set names” thing applied, so this doesn’t happen there.

I’m having the same issue: Prisma is connecting using UTF8mb3, not mb4. I’m sure this is based on some setting deep in my new database, but in the meantime it is causing issues. The best article about it for RDS I found is:

https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-3-parameters-related-to-security-operational-manageability-and-connectivity-timeout/

Running SET NAMES utf8mb4 fixes it for that connection, but there is no way to run this for all prisma connections (in a pool). I’m currently testing the init_connect option in that article, which seems to work, but it feels like a workaround: Prisma should have settings what charset to use with a connection.

Can you share that data you are trying to insert to your database? Can you also share an example CREATE TABLE for one of your tables in that database?