Unable to get text in Chinese via mysql_fdw

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL

Other

538 stars 162 forks source link

Unable to get text in Chinese via mysql_fdw #313

Closed tom10271 closed 1 month ago

tom10271 commented 1 month ago

I tried to create server with and without setting character_set 'utf8mb4'

CREATE SERVER rds FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'my-rds.us-west-2.rds.amazonaws.com', port '3306');
CREATE SERVER rds FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'my-rds.us-west-2.rds.amazonaws.com', port '3306', character_set 'utf8mb4');

However result selected for value contains Chinese is all displayed as ?.

surajkharage19 commented 1 month ago

Hi @tom10271,

Thanks for reporting this issue.

Would it be possible for you to share additional details about this issue so that we can help you better?

What is the encoding setting on MySQL and Postgres database? I mean these Chinese chars are getting displayed properly on MySQL side?
The other tables (without mysql_fdw) able to display the Chinese chars on the Postgres side? this will give us the information about this is something specific to mysql_fdw or Postgres in general. Sometimes Postgres client_encoding is not set correctly and the chars do not get displayed.
If you strongly suspect that this is specific to mysql_fdw then please share one simple reproducible test case for the same along with some sample data.

tom10271 commented 1 month ago

Would it be possible for you to share additional details about this issue so that we can help you better?

What is the encoding setting on MySQL and Postgres database? I mean these Chinese chars are getting displayed properly on MySQL side?
- Yes it is displaying properly if tables are natively in PostgreSQL
The other tables (without mysql_fdw) able to display the Chinese chars on the Postgres side? this will give us the information about this is something specific to mysql_fdw or Postgres in general. Sometimes Postgres client_encoding is not set correctly and the chars do not get displayed.
- All tables contains Chinese characters become ?

However I do have a solution by setting character_set 'utf8' inspired by https://github.com/EnterpriseDB/mysql_fdw/issues/2#issuecomment-14564509 https://github.com/EnterpriseDB/mysql_fdw/issues/2#issuecomment-2376609881

It seems auto is not really working.

surajkharage19 commented 1 month ago

Thanks for the update @tom10271.

As mentioned by you, after setting character_set to utf8, you can able to see characters correctly. Please let us know if you need further assistance on this.

tom10271 commented 1 month ago

Thank you for providing support. Yeah everything solved and working fine, just would be great if this fdw would work out of the box. Not sure why the default auto is not working which would be confusing to other.