Today I tried migrating my current project from using MSSQL Server to use MySQL Server. Theoretically this should be easy because my project uses NHibernate. As long as the structure of the MySQL database is the same as the MSSQL database I should be able to just change the NHibernate configuration.
I used the MySQL Migration Toolkit to convert my database. I ran into one error with the nvarchar(MAX) columns.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1)
The error was due to the column nvarchar(MAX), which corresponds to a length of -1. I changed the column data type to ntext and re-ran the migration, no problems this time. You could possibly change the mapping in the toolkit but I wasn't sure how to do that.
Next I downloaded the MySQL .Net Connector updated the nHibernate configuration and ran my application. Sure enough I had some errors.
No row with the given identifier exists
This was a bit of a curly one, it turned out I had inadvertently been using the MySQL Connector/Net 6.0 version on the MySQL 5.0 database server. The problem was that I had a table that had a Guid primary key which in MySQL is mapped to a column type VARCHAR(40). The error occured when a many-to-one relationship returned a null result. I knew there was a record there so there must have been some error in my setup. The MySQL Connector/Net 6.0 has an extra command parameter type Guid which the database did not appear to convert correctly and returned a null result. I changed to the MySQL Connector/Net 5.0 and everything worked correctly.
Its seems obvoius now the connector versions relate to the version of the database.