MySql “Nested transactions are not supported” exception

If you are getting an unexpected “Nested transactions are not supported” exception from your MySql database then you may have hit this bug.

I firstly suspected Spring.NET, then the MySql ADO.NET driver. We scratched our heads for a couple of days on this one before I found that bug report.

To make a long story short, simply commenting out the query_cache_size option in the my.ini file solved it. What effect this will have on performance I haven’t determined yet.

Using MySQL in heterogeneous environments.

If like us you have a requirement to run MySQL in a mixed Windows and Linux environment and you want your database to be portable you will need to change the default case sensitive table name behaviour of MySQL on one of the operating systems. On Windows the default behaviour is case insensitive, on Linux the default behaviour is case sensitive.

I like my table names to stay in the same case I scripted them in so we chose to change Windows to be case sensitive. The fix is pretty simple – you just need to add the following line to your my.ini after [mysqld] and restart your mysql service.

lower_case_table_names = 0

Existing objects won’t suddenly change case, but any new tables you add to your database will appear in the same case you scripted it in. See the mysql documentation for more details.