ALTER TABLE thing MODIFY description BLOB(999999);
ALTER TABLE thing MODIFY description LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Thank you so much Nic for creating the script, it really helps us on fixing the incorrect encoding on our 30GB database size of MySQL data.

Our Best Regards

So awesome to hear!

I modified and tested your script from GitHub to convert latin1_swedish_ci -> utf8mb4 and the transition went fairly well. I’ll share bugs on Github as requested. Just wanted to say thanks first!

@Luca I don’t fully understand the difference you’re pointing out.

The code is https://github.com/nicjansma/mysql-convert-latin1-to-utf8/blob/master/mysql-convert-latin1-to-utf8.php#L125


$colDefault = '';
if ($col->COLUMN_DEFAULT !== null) {
$colDefault = "DEFAULT '{$col->COLUMN_DEFAULT}'";
}


The above DEFAULT ' is a single apostrophe, not a double apostrophe? So this output doesn’t make sense, which has a double apostrophe in it:


MODIFY `grouplevel` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”all”,


Did something get changed when copied/pasted possibly?

Regardless, please open a Github issue if you think there’s an problem here:

https://github.com/nicjansma/mysql-convert-latin1-to-utf8/issues

MODIFY `grouplevel` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”all”,
MODIFY `start` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT ””,

!!! ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘all”,
MODIFY `start` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT ””,’ at line 6

result in this example NOT NULL DEFAULT ”all”,
should be NOT NULL DEFAULT ‘all’,
so i’ve removed apex here $colDefault = “DEFAULT {$col->COLUMN_DEFAULT}”;

Interesting! I would assume it would work that way as well, but haven’t tested it. I’m using MediaWiki for a few sites as well, so I may have to try it out soon!

Hi @Guru! That entirely depends on your data set, the processing power of the machine, etc. My guess is it should be similar to the time it takes to duplicate (or export) a table.

Yeah, so much confusion around that! I had updated a note in the README for the script: https://github.com/nicjansma/mysql-convert-latin1-to-utf8/commit/4f10abf9599e1c8979c5ee515c8d6dd8d29cb306

Hi BBking!

Hm, line 201 of the current script doesn’t have any code: https://github.com/nicjansma/mysql-convert-latin1-to-utf8/blob/master/mysql-convert-latin1-to-utf8.php#L201

Did you make edits?

Would you mind opening a Github issue? https://github.com/nicjansma/mysql-convert-latin1-to-utf8/issues

and the tables don’t change; either in encoding nor in content. Any ideas?
Thanks

This article was indeed helpful.
For me i was looking this
mysql> SELECT MyID, MyColumn, CONVERT(MyColumn USING utf8)
FROM MyTable
WHERE CONVERT(MyColumn USING utf8) IS NULL

The script worked for me without any problems.

MySQL’ latin1 is NOT iso-8859-1(5).
it is Windows1252, also known as CP1252. check the conversion tables to confirm.

MySQL foolishly call it Latin1. For ALL other systems, latin1=iso-8859-1(5) .
Thanks MySQL for the confusion.
thousands of devs, including me, fall for the trap.

For that case, you may want to do something like this after the ALTER TABLE command:

sqlExec($targetDB, “UPDATE `$tableName` SET `$colName` = TRIM(TRAILING 0x00 FROM `$colName`)”, $pretend);

Looks like the character encoding of the email sent out (from whatever email client they’re using) might be specified improperly, and possibly, SquirrelMail notices the error and corrects it.

Sounds like an issue with the Thunderbird display engine or the sending email app though, not MySQL.

j :

Used your script, but seems like there is a character limit to it.

Could you explain more? Are you saying you had a column with data, and after the conversion, some of the rows had their data truncated?

I get this error when working with some of my data:

Warning (Code 1366): Incorrect string value: ‘\xFCrttem…’ for column ‘name’ at row 1

Here’s an example:

select unhex(‘426164656E2D57FC727474656D626572672C2044452C204445’) with_fc
, unhex(‘426164656E2D57C3BC727474656D626572672C2044452C204445’) with_c3bc;

They could both evaluate to “Baden-Württemberg, DE, DE”, but only the second option works with hex and utf8.

It may be that I have to convert from latin1 to utf16 and then to utf8.

Error Number: 1366

Incorrect string value: ‘\xD1\x80\xD0\xB5\xD0\xB3…’ for column ‘content’ at row 1

At last got worked! After
ALTER TABLE `med_news` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
Thank you, very much! It was utf8_general_ci before.

But how to know which these characters are “\xD1\x80\xD0\xB5\xD0\xB3”?

Thanks a lot for the code and explanation

Are you using Windows cmd window? Some other folks are reporting issues on Windows here: http://bugs.mysql.com/bug.php?id=30131

Is it reporting exactly which characters are the issue after “Incorrect string value”?

Thank you and.. “Any ideas?” 😀

Are you using PHP on your website? Make sure you’re talking to the database in the right charset, for example:

mysql_set_charset(‘utf8’);

Does MySQL workbench report the colums as being utf8 now?

Thanks for this very informational post although I have some problems that I can not fix with your guidelines.

The problem is that on our website we see invalid utf8 characters showing as �. I use MySQL workbench and if I select the column with the problem I also see a � as the query result.

So I though the script should fail on these columns. But the script never failed. then I though maybe I should get a list of all such values that are not valid as you suggested. So I ran this query:

mysql> SELECT MyID, MyColumn, CONVERT(MyColumn USING utf8)
FROM MyTable
WHERE CONVERT(MyColumn USING utf8) IS NULL
…

and it return 0 results.

Does this mean that the data is actually proper utf8? (conversion does not fail). if so, why is it showing as � in MySQL workbench when I view the value of that specific column?

Any help on this will be greatly appreciated. Once again thanks for sharing this with us.