Problem with unicode and probably MySQL

[alk0v]

Hi.
I use MySQL DB connected to ReTool. The table configured with utf8mb4_unicode_ci but I also tried with utf8_unicode_ci.
When I updated a cell with cyrillic text Контролер кольорового графічного дісплею (Ukrainian language), it successfully updated in the database but in the Retool I see Контролер кольорового графічного дісплею
How to fix it?

[ScottR]

What component type is the description field?

[alk0v]

Text Area but the same problem with a table

[ScottR]

I added Контролер кольорового графічного дісплею directly into a table in my db (Retool database) and selected it using a query and it works as expected....
When you run the query to get the data, what is the result?

[alk0v]

It's because I suspect MySQL issue too. I accidently dropped PGSQL provided by ReTool and connected my own MySQL. And I think problem not with the component but with processing SQL result.

[ScottR]

Yep. Perhaps the MySQL db should be UTF-8? I’ll need to research it and post back here

[alk0v]

I checked different encodings on the MySQL side.
Also I tried these queries on the ReTool's side:

SELECT CONVERT(description USING utf8) FROM i_projects WHERE id=3
SELECT CONVERT(CAST(description as BINARY) USING utf8) as column FROM i_projects WHERE id=3

Nothing changes.

[Tess]

Hi @alk0v,

Thanks for posting this! I haven't been able to reproduce this one Any chance you're okay with me logging directly into your Retool account to take a closer look?

[alk0v]

Hello. Yes. Sure.

[alk0v]

https://retrofactory.retool.com/editor/104f3e42-856f-11ed-b621-bb701cb57280/BOM-Printer
Feel free to use this project

[Tess]

Hi @alk0v I haven't found a solution yet, but I wanted to check in here still!

I noticed that I'm getting different results for the same query in my own MySQL db in Retool so I am wondering if this is caused by something on the MySQL side

My db:

Your db:

[alk0v]

@alk0v

Контролер кольорового графічного дісплею

Yes, but the same database with HeidiSQL software works fine.

[alk0v]

Could you check please a Collation in your DB?

[Tess]

Hi @alk0v is this what you mean?

[alk0v]

exactly. try to change it on my side. thanks

[alk0v]

I solved the problem.

It describes here:
https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

It because my SQL server have v.5.7
After all, utf8mb4 is known to the 8.0 client and the 5.7 server, so both of them recognize it. To understand this behavior, it is necessary to understand that when the client tells the server which character set it wants to use, it really tells the server the default collation for that character set. Therefore, the aforementioned behavior occurs due to a combination of factors:

• The default collation for utf8mb4 differs between MySQL 5.7 and 8.0 (utf8mb4_general_ci for 5.7, utf8mb4_0900_ai_ci for 8.0).
• When the 8.0 client requests a character set of utf8mb4, what it sends to the server is the default 8.0 utf8mb4 collation; that is, the utf8mb4_0900_ai_ci.
• utf8mb4_0900_ai_ci is implemented only as of MySQL 8.0, so the 5.7 server does not recognize it.
• Because the 5.7 server does not recognize utf8mb4_0900_ai_ci, it cannot satisfy the client character set request, and falls back to its default character set and collation (latin1 and latin1_swedish_ci).

[Tess]

Amazing! Thanks so much for this helpful walk through

[alk0v]

Too early to celebrate. It doesnt work with data from DB

[Tess]

Ah no okay. Thanks for letting me know!

[Tess]

Hi @alk0v

I checked with the resource engineering team internally, and they said it might be more helpful to try setting the charset from the resource config page: