search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
532 stars 163 forks source link

Mysql database charset encoding #171

Open jaaknt opened 6 years ago

jaaknt commented 6 years ago

I have problem that in mysql database and tables are declared as latin1 charset, however actually text fields are saved in cp1257 encoding. What currently happens when I create foreign table like CREATE FOREIGN TABLE profitfdw.xxx ( lastname character varying(70), firstname character varying(50) , KID bigint NOT NULL ) SERVER mysql_server OPTIONS ( dbname 'big_lt', table_name 'kliendid' );
is that when i select firstname field latin1 characters are converted to UTF8 that is not correct.

What should be changed in mysql (or mysql_fdw) side that character conversion works correctly?

I have tried in mysql side ALTER TABLE xxx CHARACTER SET cp1257; alter database big_lt character set cp1257; but result is same.

How mysql_fdw deteremines mysql table encoding?

mysql> SHOW VARIABLES LIKE 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | cp1257 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+

lifeboy commented 5 years ago

I see no-one has responded to this? I have the same problem. When I try to import a schema from MySQL with this command: imips_reporting=# IMPORT FOREIGN SCHEMA imips limit to ("users","accounts","product_purchases","compound_transactions","compound_transaction_items","transaction_histories","transaction_categories","transaction_codes","transaction_sources","transaction_types") FROM SERVER mysql_imips INTO public; and get this response: ERROR: failed to connect to MySQL: Can't initialize character set SQL_ASCII (path: /usr/share/mysql/charsets/)

I can't find anything on how to fix this?

matiaskusack commented 5 years ago

Same here.

I'm using Postrgre 10.6. Any ideas?

matiaskusack commented 5 years ago

Ok, I've got it. Postgre DB enconding should not be SQL_ASCII. I've changed it to UTF8 and it Works.

jackrabbithanna commented 4 years ago

So found a way to make it work, and keep Postgres with SQL_ASCII I copied /usr/share/mysql/charsets/ascii.xml -> /usr/share/mysql/charsets/sql_ascii.xml

And added this bit to /usr/share/mysql/charsets/Index.xml `

Western US ASCII us us-ascii csascii iso-ir-6 iso646-us

` Obviously recommended that you convert your PostgresDB to UTF8 or whatever .. but this appears to make the FDW work. I am able to query a MySQL View via this FDW in PostgreSQL.

I wonder how bad of an idea this is?