Lightspeed, MySQL and Unicode

Hello,

Consider the following code snippet:

string text1 = "汉语/漢語 Hànyǔ; 华语/華語 Huáyǔ";
using (mUnitOfWork mUnitOfWork = context.CreateUnitOfWork())
{
    mUnitOfWork.Add(new Entity1() { Text1 = text1 });
    mUnitOfWork.SaveChanges();
}

using (mUnitOfWork mUnitOfWork = context.CreateUnitOfWork())
{
    foreach (var entity1 in mUnitOfWork.Entity1s)
    {
        var t = entity1.Text1;
        // check t
    }
}

Looking at the result and in the database, unicode characters are not persisted.  I had a look at previous forum entries but they were written in 2009 for a different version of Lightspeed. I used Lightspeed to create the migration and database.

I'm using MySQL 5.5.9 and Lightspeed 4.0.811.18502.

Is here anything I need to do in order to save UNICODE strings to the database? This issue is critical for us as we have users in the east and middle east.

Werner

There shouldn't be anything you need to do at the LightSpeed end. Unfortunately right now I only have SQL Server to test with, but I can confirm that we can definitely round-trip Unicode characters through that.

The issue is probably the configuration of the MySQL column.  Looking at our migrations, it looks like we map string properties to VARCHAR(n) columns, whereas we would be better off mapping them to NVARCHAR(n) columns in order to support Unicode.  As I said, I don't have a copy of MySQL on this machine to test with, so I can't verify this right now.

Could you try manually changing the column from VARCHAR to NVARCHAR and let us know if that fixes the problem?  If it does, I'll get the database sync and migrations updated to reflect this.

There is no NVARCHAR in MySQL 5.  

I changed the collation of the database to UTF-8 and that made no difference. I then tried to change the column collation to UTF-8 and that made no difference either.

Any other ideas?

Collation reflects only comparisons (equality and sort order) I believe, not how characters are stored (or what characters may be stored).  Looks like you need to set up the character set when you create the database:

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8

and you should be able to update existing Latin-1 tables with:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Looking at the MySQL docs and Googling around I don't *think* we need to do anything with the client-side settings, but the documentation doesn't seem very clear on this...

Hi Werner,

Just checking back to see if you had any joy with this.  If so, we'd be interested to know whether you needed to do anything more than the CHARACTER SET thing, so we can advise other customers when they run into the same thing.  Thanks!

Hi Ivan,

I finally had the time to investigate this further. In order to store characters in UNICODE, you have to add "Character Set=utf8" to your connection string as in:

server=localhost;User Id=root;password=password;Character Set=utf8;database=sample;

It seems you have to change the charset of MySQL to a unicode charset as well.

Werner