Problem with unicode and probably MySQL

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?
Screenshot 2022-12-28 at 1.48.17 PM

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.

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

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.

Hi @alk0v,

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

Hello. Yes. Sure.
Feel free to use this project

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:

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

Could you check please a Collation in your DB?

Hi @alk0v is this what you mean?

exactly. try to change it on my side. thanks

I solved the problem.

It describes here:

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).

1 Like

Amazing! Thanks so much for this helpful walk through

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

Ah no okay. Thanks for letting me know! :thinking:

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:

1 Like

Finally I solved the problem. I set default collation on server side to utf8mb4_unicode_ci for all my tables.

1 Like

It works fine with SELECT but doesnt work with INSERT

1 Like

So. It works fine with utf8_general_ci in both directions (SELECT and INSERT/UPDATE)

1 Like