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 Any chance you're okay with me logging directly into your Retool account to take a closer look?
Hello. Yes. Sure.
https://retrofactory.retool.com/editor/104f3e42-856f-11ed-b621-bb701cb57280/BOM-Printer
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:
Could you check please a Collation in your DB?
exactly. try to change it on my side. thanks
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.0utf8mb4
collation; that is, theutf8mb4_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
andlatin1_swedish_ci
).
Amazing! Thanks so much for this helpful walk through
Too early to celebrate. It doesnt work with data from DB
Ah no okay. Thanks for letting me know!
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:
Hello.
Finally I solved the problem. I set default collation on server side to utf8mb4_unicode_ci for all my tables.
It works fine with SELECT but doesnt work with INSERT
So. It works fine with utf8_general_ci in both directions (SELECT and INSERT/UPDATE)