Moving text with illegal chars between latin1_bin tables

Havin_it

Hi,

I have a mysql-5.1 database where all tables are collated latin1_bin and the php-5.3 application that uses it is all in iso-8859-1. I'm changing the table structure (breaking one table down into three smaller ones) and so I have a PHP migration script that simply copies the relevant fields from the original table into the new tables.

However, I've discovered that some illegal (maybe the wrong term) characters such as accented French vowels, umlauts etc. have made their way into the original table through form data. Post-migration, these show up as corrupt 2-character strings in the new table.

I wonder if anyone can suggest a way I could either (a) systematically eliminate such characters from the original table, making sane replacements (e.g. unaccented vowels, vowel+"e" for umlauts) in the process; or (b) transfer them as-is into the new table? Their presence doesn't negatively affect the app, but the garbled results in the new table will somewhat.

Thanks in advance. Please let me know if you need any further info on the base system, app etc.

johanafm

Afaik, the only frech characters missing from ISO-8859-1 are

œ
Œ
Ÿ

They do however exist in Windows-1252, which is the same as ISO-8859-1 except for exchaning some control characters for printable characters in the range 0x80 to 0x9f. The above characters in Windows-1252 are mapped mapped into this range of characters as

œ: 9c
Œ: 8c
Ÿ: 9f

latin1 is another name for ISO-8859-1, but do note that MySQL's character set called "latin1" is not at all latin1 - it is in fact Windows-1252!

Since the above characters cannot be typed using iso-8859-1 they should either not have been posted in the first place, or since some (all?) browsers actually use Windows-1252 instead of ISO-8859-1, even when you explicitly set the charset to iso-8859-1, these characters should be posted with the Windows-1252 character codes, which also happens to be treated in the exact same way by MySQL. HTML5 actually specifies that both US-ASCII and ISO-8859-1 should be treated as Windows-1252! Thus, it's not until these characters are treated as iso-8859-1 outside of MySQL / the above mentioned browsers that they will be treated as their respective control codes.

They should however still not be represented as two separate printable characters or stored as double bytes.

This leads me to believe that you havn't always treated your data as ISO-8859-1 (since those bytes would still be valid). For example, in utf-8 they are represented as two byte characters.

Either way, if you do have them stored as a sequence of two bytes, and you have some way of assuring that those double bytes do not also actually exist to display a sequence of two characters matching those respective bytes, you could of course replace such sequences with the above byte values. After that, you might want to switch to serving your pages as Windows-1252 instead.

However, if you have to go through the process of moving data and do string replacements, you might as well switch to utf-8 while you're at it.

Havin_it

Hi johanafm, thanks for the very detailed reply. I had no idea windows-1252 was lurking behind these charsets!

I can shed a bit more light on the origin of the characters: they tend to be pasted into the form from an email, which is then parsed using PCRE. The matched strings are then pre-written into a second form (where manual editing is done where necessary) and this form is then fed into the db. The browser (Firefox) is reporting all the involved pages as iso-8859-1 as declared.

I'm quite open to changing the page declarations to utf-8, I don't see a problem with that. But as for making this change in the db, is that as simple as exporting the tables, changing the charset/collation declarations in the sql and re-importing? (I don't see any mention of doing it in-place.)

Weedpacket

Havin_it wrote:
But as for making this change in the db, is that as simple as exporting the tables, changing the charset/collation declarations in the sql and re-importing? (I don't see any mention of doing it in-place.)

Caveat: I don't use MySQL, but from what I can make out is that you use alter table commands to first convert the column to a binary type, and then convert it back into a textual-type that uses the new character set (you'd think the DBMS could do this transparently, but apparently not).

johanafm

Havin_it;10993238 wrote:
The browser (Firefox) is reporting all the involved pages as iso-8859-1 as declared.

I don't know exactly how Firefox handles all situations since I've almost only ever used utf-8. But when I did some testing after your original question, I at least discovered that sending 0x9c 0x8c and 0x9f to Firefox using iso-8859-1, those characters were displayed as Windows-1252.