Mysql – latin1 (with latin2 content) to utf8

encodingMySQL

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.

Best Answer

You can try something like:

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

Related Solutions

Mysql – No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

One or more of those character/text fields MAY have 0x00 for its content.

Try the following:

SELECT * FROM rt3 where some_text_field = 0x00 LIMIT 1;

If this returns any single row then try updating those character/text fields with:

UPDATE rt3 SET some_text_field = '' WHERE some_text_field = 0x00;

Afterwards, try another MYSQLDUMP ... ( and PostgreSQL import method ).

MySql: Change from latin1 to utf8

Setting the default character set and collation is completely safe. This will ensure that future DDL changes will use utf8, but will not affect existing columns that use latin1. Those will have to be converted to utf8. Make a backup of the data, because there are risks of data corruption (one example).

And for completeness, I will point out that adding the changes in the my.cnf will require a server restart.

Best Answer

Related Solutions

Mysql – No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

MySql: Change from latin1 to utf8

Related Question