conversion of charset

vvucic posted 4 months ago in General

I connected successfully to MySQL server and choosed Tools/Bulk table editor. Tables do have collation in latin1_swedish_ci since all tables are in that encoding (see atatch. WHole table is in utf8mb4 I choosed that they should be converted to utf8. After clicking Update I have got for each table error: SQL ERROR 1253: COLLATION latin1_swedish_ci is not valid for CHARACTER SET utf8. Please advise how to change encoding opf each table.

2 attachment(s):
  • 2018-08-06-16_35_30-localhost-_-127.0.0.1-_-ojs-upgrade-endcoding-_-phpMyAdmin-4.7.4
  • 2018-08-06-16_35_53-localhost-_-127.0.0.1-_-ojs-upgrade-endcoding-_-phpMyAdmin-4.7.4
Misha v.3 posted 4 months ago

I tried to reproduce the issue and could not. All works without error. HeidiSQL 9.5.0.5280 & MySQL 5.7.19 installed on Win10. Played with MyISAM & InnoDB tables. Converted them from latin1_swedish_ci to utf8 as well as utf8mb4 and back.

What is your MySQL version?

vvucic posted 4 months ago

Hello, I checked my tables and noticed that it was my mistake. Sorry. I have issue that charactyers are garbled and I try to transcode database. is that possible to do with HeidiSQL?

Misha v.3 posted 4 months ago

This might be tricky as characters might be in wrong encoding in one table and in correct encoding in another table. So in general it is not possible to fix it automatically.

The only way I know is to manually dump table(s) into a file, transcode the file (you can use text editors like notepad++, notepad2, akelpad, etc for this step) and then load data back. Unfortunately, you have to exactly understand which transcoding is required. The good thing -- you can load transcoded data into a separate database and check. You may use HeidiSQL for dumping/loading, however I prefer to use mysqldump/mysql CLI for these tasks.

vvucic posted 4 months ago

Hello, The database is not much garbled. Actually, many tables are in latin1_swedish_ci and they should be turned into utf8. What happens is that many characters are double encoded and they appear as strange characters. I tried in geany to change all latin1 with utf8 but characters are still garbled after being imported. Some people mention iconv and forceutf8 php utilities but I am interested to learn is that possibel to do somsething on level of database itself.

Thanks

Misha v.3 posted 4 months ago

I would not even try to fix it on DB level.

Please login to leave a reply, or register at first.