Fix Linq 2 NHibernate for MySQL

If you use NHibernate with MySQL and Linq 2 NHibernate to simplify fetching data you may face with problem: queries created by Linq2NH use square brackets by default. That is fine for SQL Server but won't work in MySQL which uses backticks ``.

For MySQL we need to instruct NHibernate to use backticks instead of square brackets. It can be done by setting interceptor in NH config:

public class NHConfiguration
{
    public static Configuration Build(string connStr)
    {
        var config = Fluently.Configure()
            .Database(
                MySQLConfiguration.Standard
                    .ConnectionString(connStr)
                    .AdoNetBatchSize(100)
                    .DoNot.ShowSql()
            )
            .Mappings(cfg =>
            {
                // add mappings
            })
            .ExposeConfiguration(x =>
            {
with backticks for MySQL
                x.SetInterceptor(new ReplaceBracesWithBackticksInterceptor());
            });

        return config.BuildConfiguration();
    }
}

in its OnPrepareStatement method we just replace square brackets with backticks:

public class ReplaceBracesWithBackticksInterceptor : EmptyInterceptor
{
    public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        return sql.Replace("[", "`").Replace("]", "`");
    }
}

After that Linq2NH will start working in MySQL.

Generate database schema for MySQL using NHibernate hbm2ddl tool

NHibernate has hbm2ddl tool which allows automatically export database tables schema based on provided mappings. I.e. we may define mapping using C# for some POCO class:

public class User
{
    public virtual int Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

like that:

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Table("[User]");
        Id(x => x.Id, "UserId");
        Map(x => x.FirstName);
        Map(x => x.LastName);
     }
}

and then based on this mapping hbm2ddl will generate the following SQL code for creating User table:

create table `User` (
    UserId INTEGER NOT NULL AUTO_INCREMENT,
    FirstName TEXT,
    LastName TEXT,
    primary key (UserId)
);

That is convenient because we don't need to maintain database schema separately - any change in C# models and mappings will be automatically reflected in db schema. However in order to use hbm2ddl for MySQL we need to add few tweaks to the export code:

• MySQL uses back ticks instead of square brackets
• MySQL syntax requires semicolon after each code line in generated SQL
  

First of all we need to create Fluent NHibernate configuration for MySQL syntax:

var config = Fluently.Configure()
    .Database(
        MySQLConfiguration.Standard
            .ConnectionString(connectionString)
            .AdoNetBatchSize(100)
            .DoNot.ShowSql()
    )
    .Mappings(cfg =>
    {
        cfg.FluentMappings.AddFromAssemblyOf<UserMap>()
            .Conventions.Setup(mappings =>
            {
                mappings.AddAssembly(typeof(UserMap).Assembly);
            });
    });

With this Fluent NHibernate config export code will look like this:

var nhConfig = ... // see above
var export = new SchemaExport(nhConfig);
var sb = new StringBuilder();
export.Create(schema =>
{
    schema = schema.Replace("[", "`").Replace("]", "`");
    if (schema.EndsWith("\r\n"))
    {
        schema = schema.Substring(0, schema.Length - 2) + ";\r\n";
    }
    else
    {
        schema += ";";
    }
    sb.Append(schema);
}, false);

Console.WriteLine(sb.ToString());

Here we replace square brackets by back ticks and add semicolon to the end of each added line. After that we will have valid SQL code for MySQL syntax with database schema.

Profile MySQL db with Neor Profile SQL

If you need to profile MySQL db you may use builtin MySQL shell profiler (CLI). Also there is free GUI alternative - Neor Profile SQL. There are few things which should be done before to use it.

When you launch Profile SQL it asks to establish connection using default parameters for localhost:

If you click Test button you may get "Test is failed" error even with correct credentials of the root user. In order to avoid it you need to enable native MySQL password for the root user:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '...'

after that connection should be successful.

But that is not all. If after that you will run application which connects to MySQL you won't see sessions and queries in profiler - because profiler works as a proxy with own port (4040 by default):

and in order to collect data your application should connect to profiler (not to MySQL directly). I.e. we need to change port in connection string from MySQL port (3306 by default) to profiler port (4040):

{
  "ConnectionStrings": {
    "Default": "Server=localhost;Port=4040;Database=Test;User ID=test;Password=..."
  }
}

If after that connection will fail with System.IO.IOException (from MySql.Data.Common.Ssl namespace methods) add ";SSL Mode=None" to connection string:

{
  "ConnectionStrings": {
    "Default": "Server=localhost;Port=4040;Database=Test;User ID=test;Password=...;SSL Mode=None"
  }
}

After that application should connect to profiler successfully and you should see profiled data.

One important difference between MySQL installation on Windows and Linux

MySQL may be installed both on Windows and Linux. However there is one important change which I would like to know before to install it on Linux :) In this post I will share this finding.

Under the hood MySQL tables data is stored in files on file system. Windows file system is case insensitive and if you try to create table like that:

create table `UserInfo` (
    ...
)

this table will be created with lowercase name anyway (even though you used Camel case in SQL statement. In this example it will be created with "userinfo" name). But at the same time you may read data from this table using all following queries:

select * from UserInfo
select * from userInfo
select * from userinfo

From other side Linux file system is case sensitive which means that by default table will be created there with exactly the same characters registry which was used in SQL statement. So if table was created using SQL statement above then you need to use exactly the same name also in SQL queries:

select * from UserInfo

Other queries will fail with table not found error:

select * from userInfo
select * from userinfo

If you develop cross-platform application which works with MySQL it may cause compatibility issues: code which works in Windows won't work in Linux because of different characters registry in table names used in SQL queries. In order to avoid this error we may configure MySQL on Linux so all tables will be created with lowercase name - i.e. same way how it works on Windows. It can be done by adding the following configuration setting to /etc/my.cnf.d/mysql-server.cnf file (this example is for Red Hat Linux. For other Linux distributives MySQL config file may be located in other folder e.g. in /etc/mysql and may have different name):

[mysqld]
...
lower_case_table_names=1

NOTE: it should be done during MySQL installation before to run database instance service. It is not possible to change this property when MySQL is already installed (in this case you will need to uninstall it and install again). With this approach code will be the same and will work both on Windows and Linux.