Mysql - latin1 (with latin2 content) to utf8

Question

I have a database (ubbthreads) encoded in latin1 with content from latin2 (polish characters).

Since latin2 is compatible with latin1 it looks fine on the website, however I cannot convert it in any way to utf8 (want to import the data to nodebb).

It also doesn't render characters correctly in console mysql as well as in mysql workbench.

I tried numerous of ways to do the conversion and all of this failed.

My last try was to convert mysqldump file first from latin1 to latin2, and I planned to do next conversion from latin2 tu utf8.

However, iconv breaks in first polish character attempt with illegal input sequence at position 11392

I need some tips how to do it in a correct way.

Answer 1

You can try something like:

INSERT INTO original_new()
SELECT CONVERT(CAST(CONVERT(`field` USING latin2) AS BINARY) USING utf8) 
FROM original

Answer 2

In MySQL, you state two independent things:

• The encoding of the bytes in the client, and
• The encoding in the column in the table.

That is, you can have one of them be utf8 and the other be latin2. This is without any use of CONVERT(), etc.

Since it is unclear what settings you have where, I can't be more specific.