Avoiding MySQL’s MAX_JOIN_SIZE Limit With NHibernate
Posted by Davy Brion on March 30th, 2009
We’re working on an application which has to use a legacy MySQL database. So far, we haven’t really had any problems with MySQL (apart from the dreadful legacy schema but that’s another issue) but today, i started getting an exception with the following message:
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
There is a certain complex view in our application which apparently hits this default limit set by MySQL once there is a certain amount of data present. The view itself is a costly one… it really does need to do a whole lot of joins and rewriting it to use less joins would probably take a long time.
So i figured the better option in this case would be to set the SQL_BIG_SELECTS option to 1. The only problem was: how? It’s not a setting that you can pass through the connection string (or at least, i did not find a way to do so) and NHibernate is taking care of all of the database communication.
I remembered a trick i had used earlier, which is to extend NHibernate’s DriverConnectionProvider. It could then set the setting with the appropriate value whenever the connection is opened, like this:
public class CustomConnectionProvider : NHibernate.Connection.DriverConnectionProvider
{
public override IDbConnection GetConnection()
{
IDbConnection connection = base.GetConnection();
EnableBigSelects(connection);
return connection;
}
private void EnableBigSelects(IDbConnection connection)
{
using (var command = connection.CreateCommand())
{
command.CommandText = "SET SQL_BIG_SELECTS=1";
command.ExecuteNonQuery();
}
}
}
After that, you just set the DriverConnectionProvider to use in your hibernate.cfg.xml file like this:
<property name="connection.provider">MyProject.Infrastructure.NHibernate.CustomConnectionProvider, MyProject</property>
And all is well.
March 30th, 2009 at 1:08 pm
pretty stupid that mysql doesn’t let you specify this option on a per-database level. it seems hacky that you have to do this for every connection you make.
well, maybe they have a good reason to not allow this… but then i’m really curious as to what that reason might be
March 30th, 2009 at 1:12 pm
yeah you can set it for the entire server, or per connection
and setting it for the entire server is not something i’d want to do either