Pitfalls when migrating from MSSQL to MySQL and BLToolkit

We have MSSQL 2008
We want MySQL version 5.x

Why it may be necessary?

For developers on .NET, exchanging MSSQL for MySQL is probably the same as switching from Mercedes to something simpler. As they say, you quickly get used to the good.
But there are at least two reasons to do this.

• Save on licenses
• Get simple master-slave replication

In our case, working with the MSSQL database is performed through a LINQ provider.
During the transition, I would not like to lose this opportunity, so for working with MySQL, the choice fell on BLToolkit .

Migrating

The simplest is to rewrite the code. BLToolkit, in contrast to the MS provider, belongs to the class of light ORM, therefore there are slightly different constructions for connecting to the database, but the LINQ expressions will remain the same.
')
Do you think it remains to transfer the data and everything will work?
As if not so.

We transfer data

For migration, we used the MySQL Migration Toolkit.
Most types of MSSQL fields were exported without problems, but two fields for a segment were refused:

• varchar
• datetime2

In our case, we in the source database changed varchar to nvarchar, and datetime2 to datetime. With the first, everything is clear - the field simply began to store characters in unicode, while in the second we did not have reasons for which the developers needed to store the datetime with an accuracy of 100 nanoseconds, except for the fact that if we put DateTime.Now into the database, and then extract, then values ​​may not be equal due to rounding (some functional test written in this way fell and the developer solved the problem using datetime2).

LINQ mapping

A timestamp type field is exported to a field with a similar type, but its behavior is slightly different.
In the .NET class for MSSQL, it is represented by some unfinished Binary type, with the result that you have to make hacks for LINQ like this
In MySQL, it translates into a normal datetime, but there are two pitfalls

1. After export, you need to initialize all timestamp values ​​(for example, SET NULL for the timestamp field), because after export there will be solid zeros that BLToolkit does not understand
2. Set DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , otherwise the field will not be automatically updated, as we used to in MSSQL. (Read about the features of the MySQL timestamp update )

At this stage, you most likely have a project already going on and maybe something even works.

If mapping does not work because the corresponding fields in the table are not found for some class fields, then this is possible because for MS LINQ, the required class fields should be marked with the [Column] attribute, and for BLToolkit, on the contrary, extra fields should be marked with the attribute [MapIgnore]

The next unpleasant surprise may be the fact that BLToolkit does not screen field names, so you should not use any key words like Key in your tables for the field name. When translating a LINQ expression into a SQL query, it will fall with a syntax error.

The benefits of functional testing

If your programs are not covered by tests, then this is very bad.
Only due to a simple functional test that checked that the returned field in the database is NULL equal to the null string in .NET, it turned out that the BLToolkit defaults to string.Empty.
Here there would be an unpleasant surprise if somewhere costs if(value == null) .

To change this behavior you need to register the attribute of the mapping class
[NullValue(typeof(string), null)]

The last pitfall I can tell you about is the Guid field mapping.
As you know in MySQL there is no special type for storing guid.
For this, it uses char (36).
BLToolkit uses the Guid.Parse function when displaying.
For example, this code should be used to display the char (36) fields in Guid.

 [TableName("Boxes")] public class Box: { [PrimaryKey] public Guid BoxId { get; set; } } 

Everything looks great until someone tries to insert something different from the Guid into the table.
And even though in the mapping class, you define this field as string , BLToolkit will still try to make Guid.Parse
For example, such a code will fall down when trying to read from the char (36) field StringMayContainsGuid something that is not a guid.

 [TableName("Boxes")] public class Box: { public string StringMayContainsGuid { get; set; } } 

A simple solution in this case is to change the field type to varchar.

As a tool for finding such problems, I wrote a simple functional test that re-reads all the data from the tables:

 [TestFixture, Category("Functional")] public class DbFunctionalTest { private readonly IMysqlClient mysqlClient = new MysqlClient(); //        [Test] public void ReadAllTables() { var dbMappingClasses = from classType in Assembly.GetExecutingAssembly().GetTypes() where classType.IsClass && classType.GetCustomAttributes(typeof(TableNameAttribute), true).Length > 0 && classType.Namespace == typeof(DbFunctionalTest).Namespace select classType; mysqlClient.PerformRequest(db => { foreach (Type dbMappingClass in dbMappingClasses) { var tableName = ((TableNameAttribute) dbMappingClass.GetCustomAttributes(typeof (TableNameAttribute), true).First()).Name; try { db.SetCommand(CommandType.Text, string.Format("SELECT * FROM {0}", tableName)).ExecuteList(dbMappingClass); }catch(Exception e) { throw new Exception(string.Format("Can not read all records from table {0}", tableName), e); } } }); } } 

Finally

I hope the described experience will be useful to someone.
But in fact, my insidious plan was to get even more benefit from your comments, so welcome!