Losing data on converting MySQL latin1_swedish_ci to utf8_unicode_ci

Question

When I try to convert data from latin1_swedish_ci to utf8_unicode_ci I loose data ! The TEXT column is cut at the first special character.

For example: Becomes:

Yet I tried many ways to convert my column and all solutions end up deleting data at the first special character!

I tried by phpMyAdmin or with this SQL request:

UPDATE `page` SET page_text = CONVERT(cast(CONVERT(page_text USING latin1) AS BINARY) USING utf8);

I also tried the php script :

https://github.com/nicjansma/mysql-convert-latin1-to-utf8/blob/master/mysql-convert-latin1-to-utf8.php

With all the time the same result, data are lost at first special character!

What should I do?

UPDATE

I could change the data to utf8 with

ALTER TABLE page CONVERT TO CHARACTER SET utf8mb4;

or

ALTER TABLE page CONVERT TO CHARACTER SET utf8;

without loosing data but it does not display properly special characters.

Using the php function utf8_encode($myvar); does display correctly special characters.

Answer 1

To convert a table, use

ALTER TABLE ... CONVERT TO ...

Or, to change individually columns, use

ALTER TABLE ... MODIFY COLUMN ...

Instead, you seem to have done something different. For further analysis, please provide SELECT col, HEX(col) ... before and after the conversion, plus the conversion used.

See "truncated" in this . The proper fix is found here, but depends on what you see from the HEX.