Export import problem

goran.ostrom posted 7 months ago in Import/Export

Hi, I have, for me, a strange problem with charset. I move the databases - about 40 - to a new server.

Via c # and ExportToFile I have created sql files. MariaDB version 10.1 and MS Server 2008. If I open this file I can see the swedish characters.

Now I have tried the following in a MS Server 2016, MariaDB version

  • Import via C # ImportFromFile. = swedish characters looks like rubbish
  • A large file via Heidesql - open sql and run = swedish characters looks like rubbish
  • The same large file via Heidesql, but only a small part - one table (ctrl+F9) = Swedish characters are ok.

I have tried to set DEFAULT CHARSET=utf8 but no difference. I also get an error with ENGINE=InnoDB.

I get nuts :) And I don't understand what I do wrong...

Can anyone who knows give me the right direction how to solve the problem?

ansgar posted 7 months ago

That's confusing. You're not talking about MS SQL Server, but MariaDB on some MS Server Windows, or?

When you open a .sql file in HeidiSQL for importing, it is really important to select the right encoding of the file in the open-file-dialog. There may be "Auto-detect" selected, but that fails in many cases! Select UTF-8 for example when you can verify (with Notepad++ for example) your file is UTF-8.

Your tables have some utf8 collation?

goran.ostrom posted 7 months ago

Thanks for answer. It's MariaDB 10.2 on a window server 2016. Works very well in all other cases. MS SQL is too slow :) The only function I'm miss in MariaDB is a way to trigger response to my C# code when a table change. For now I have to poll changes with repeatable select statements. But that is another subject and not connected to Heidi.

I think Auto-detect is the answer to why it differs, when I use Heidi, between large file and small part of a file. For now I did it manually via Heidi - table by table, but I'm going to do some more tests with Auto-detect turned off. And I have to set collation to utf8mb4_swedich_ci when creating the database in my code and also find a way to set utf8 when I export data to sql via C# ExportToFile. Thanks for the direction :)

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