Timeline for Character Encoding in Postgres
Current License: CC BY-SA 3.0
20 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 25, 2017 at 3:09 | comment | added | thatguy | Hi Evan. So sorry for my slow response. It turned out to be an issue with a proprietary module used in the Perl script that I didn't include as I sanitized the code that I posted. It was double encoding the data before inserting in to Postgres. I really appreciate your help. What's the appropriate way for me to give you credit for your effort to help me and to not mislead others if they stumble across this? Regardless, thank you. | |
| Feb 20, 2017 at 23:17 | comment | added | Evan Carroll | Did any of this work? | |
| Feb 14, 2017 at 22:30 | comment | added | Evan Carroll | Perl only knows one thing, is it or is it not utf8. That's the only flag. So if it's not utf8, perl is just going to dump it to the terminal as if it were bytes. If the terminal renders it (is not in utf8) that's fine.. It'll work.. for the terminal. But, PostgreSQL is only going to accept utf8. So if it's not utf8, you have decode it from whatever it is to unicode, and encode it back in utf8 in perl. | |
| Feb 14, 2017 at 22:23 | comment | added | thatguy | I'm looking at how to update dbd::mysql to experimental. If I'm already parsing the data in Perl before inserting it in to psql, would it make more sense for me to just reencode it differently inside Perl? I don't have plans to use MariaDB again. | |
| Feb 14, 2017 at 22:12 | comment | added | Evan Carroll |
I would try updating the MySQL DBD Driver to the development version, do that confirm with perl -MDBD::MySQL\ 9999 that you're running the development version, unset mysql_enable_utf8 from the connection string and see if it works.
|
|
| Feb 14, 2017 at 22:05 | comment | added | thatguy | Source column is utf8_general_mysql500_ci. My research earlier on this showed that MariaDB isn't as flexible as psql in utf8 and that it sometimes uses utf8mb4. | |
| Feb 14, 2017 at 21:59 | comment | added | Evan Carroll |
using mysql connect and output SHOW FULL COLUMNS FROM sourcetable; if that shows up as anything but utf8 perhaps you need to migrate to utf8
|
|
| Feb 14, 2017 at 21:57 | comment | added | Evan Carroll | Make sure your MySQL is storing in utf8, stackoverflow.com/q/1049728/124486 | |
| Feb 14, 2017 at 21:53 | comment | added | Evan Carroll |
That's not Unicode though. Clearly. So if that's in your database, then whatever put it there put something that's not Unicode. You can see pg_dump is connecting with client_encoding = utf8, and it's still putting that crap out. So the problem then is in whatever is inserting into Pg. Not whatever is outputting from Pg.
|
|
| Feb 14, 2017 at 21:50 | comment | added | thatguy | Edited to add test sql dump. That was created with mysql_enable_utf8 enabled (because without it my Perl script won't even display the data) but without pg_enable_utf8. The included record should display, "приветствую" and it does so if I extract it with a Perl script without pg_enable_utf8. | |
| Feb 14, 2017 at 21:47 | history | edited | thatguy | CC BY-SA 3.0 |
added 1258 characters in body
|
| Feb 14, 2017 at 20:37 | history | edited | Evan Carroll |
edited tags
|
|
| Feb 14, 2017 at 19:50 | comment | added | Evan Carroll |
Same thing with mysql_enable_utf8 these flags are forcing the driver to make assumptions that it probably should not be making. You're setting SvUTF_on on everything and wondering why all these assumptions don't hold true across multiple databases. And we still have no idea what's in MySQL.
|
|
| Feb 14, 2017 at 19:45 | comment | added | Evan Carroll |
Unset pg_enable_utf8. This should never bet set to 1. If your client encoding is right it'll do that anyway. This is making the driver assume (needlessly) that everything is stored in utf8, and that the client encoding is utf8. This is a bad assumption and can sting you and may be what's happening.
|
|
| Feb 14, 2017 at 19:27 | history | edited | thatguy | CC BY-SA 3.0 |
Edited to add requested code
|
| Feb 14, 2017 at 19:02 | answer | added | Evan Carroll | timeline score: 1 | |
| Feb 14, 2017 at 19:00 | comment | added | Evan Carroll | Can you show us the Perl script to insert the data into the database? My assumption is that perl is inserting non-utf8 data (because working with utf8 in perl is a PITA). That PostgreSQL assumes it's utf8, but that the terminal has no idea of how to display it, becuase it's not utf8. | |
| Feb 14, 2017 at 18:57 | history | edited | Evan Carroll | CC BY-SA 3.0 |
added 65 characters in body
|
| Feb 14, 2017 at 18:39 | review | First posts | |||
| Feb 14, 2017 at 18:49 | |||||
| Feb 14, 2017 at 18:37 | history | asked | thatguy | CC BY-SA 3.0 |