CODEWITHSUNDEEP
mysqlsql-server-2008mysql-workbench
DaveO
DaveO

Reputation: 1989

Encoding issues when migrating from MSSQL SQL Server to (Linux) MySQL

I'm using MySQL Workbench and successfully migrating a SQL Server database used with SilverStripe PHP CMS to MySQL on Linux. Problem is when the content is displayed on the Linux web server I have to change encoding to Western (Windows-1252) to get the content to display correctly. The site on Windows IIS with SQL Server displays correctly with the default UTF-8 encoding.

In the manual migration editing section on MySQL Workbench some columns say Collation Latin1_General_CI_AS migrated to utf8_general_ci so I gather this is correct.

The site is setting <meta http-equiv="Content-type" content="text/html; charset=utf-8" /> in the HTML.

Migration process:

  1. Use MySQL Workbeanch to migrate from SQL Server to MySQL on production server
  2. Export MySQL database on production server to *.sql file
  3. Import *.sql file into Linux server using PHPMyAdmin and default UTF-8 encoding

I'm not sure where in the migration process I need to fix this?

Upvotes: 2

Views: 2779

Answers (2)

Mixthos
Mixthos

Reputation: 1087

This might not be about the migration process at all.

If you are using PHP to access the new database, the connection charset might be incorrect. After setting up the connection you should set the connection charset to UTF-8.

$db = new MySQLi(HOST, USER, PASSWORD, DATABASE);
$db->set_charset('utf8');

Or, if you are not using MySQLi:

mysql_connect(/*...*/);
mysql_set_charset('utf8');

Upvotes: 1

Tomas
Tomas

Reputation: 59445

There are 3 possibilities of what could have gone wrong:

  1. Encoding miss-configuration at the original SQL-server setup. Look at this post for more details on how this can happen: https://stackoverflow.com/a/20824533/684229. In this case, the encoding in the SQL-database is incorrect, but due to wrong setup it displays correctly.

  2. You made a mistake in the migration process. Then the encoding in the new MySQL database is incorrect.

  3. The encoding in the new MySQL database is correct, but there is an encoding miss-configuration at the new Linux MySQL setup, which makes it look incorrect.

To check which case applies, you have to check the encoding in both databases by some independent tool which for sure (200% at least!!!) has the encoding configured correctly. I would use PHPMyAdmin in case of Linux, I don't know what's available on SQL server. But make sure that this tool is configured correctly, otherwise you will get fooled!!!

Post the result and I will expand my answer accordingly.

EDIT: Dave, I have numbered the steps of your migration process. Please check the encoding of your MySQL database at two points - right after step 1 (before you do the export & import) as well as after you export & import in step 3. This will have to detect the exact point where it went wrong.

Upvotes: 1

Related Questions