2

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?

Improve this question
2
  • That suggests that the content was not stored with the correct encoding to begin with, and/or the previous application was converting it between encodings. Commented Dec 27, 2013 at 22:09
  • 1
    Have you look at the *.sql file with a text editor to see how the content is ? Have you look at the file encoding itself ? Commented Jan 1, 2014 at 1:36

2 Answers 2

Reset to default
1
+100

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');
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

MySQL extension is deprecated as of PHP 5.5.0, and will be removed in the future. I suggest the MySQLi API.
I am. That's why I wrote the MySQLi example first.
This was it, thanks heaps. In SilverStripe I had to set the config to MySQLDatabase::set_connection_charset('utf8') which then configured the database layer.
@DaveO this was my option 3, unfortunatelly you didn't respond so I could not expand my answer.
@Tomos Sorry I just got back to this problem and immediately applied the solution Mixthos posted so I didn't get to the checks you requested.
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.