I learned today that NHibernate should not be used with MySQL’s MyISAM table engine, but only with InnoDB. This is because NHibernate produces, and relies on, numerous foreign keys and MyISAM does not support referential integrity. If you’re like me, and you produce your database schema from your code, then you’ll see the referential integrity […]

This article was posted by Independent Software, a website and database application development company based in Maputo, Mozambique. Our website offers regular write-ups on technical and design issues, ranging from details at code level to 3D Studio Max rendering. Read more about Independent Software's philosophy, or get in touch with Independent Software.

I learned today that NHibernate should not be used with MySQL’s MyISAM table engine, but only with InnoDB. This is because NHibernate produces, and relies on, numerous foreign keys and MyISAM does not support referential integrity.

If you’re like me, and you produce your database schema from your code, then you’ll see the referential integrity that your code requires automatically expressed in the tables that are generated. By default, NHibernate uses InnoDB (merely because this is MySQL’s default table engine) and there doesn’t seem to be any way to change this, and nor should you want to.

The reason I tried to switch to MyISAM (all tables were in InnoDB initially) is because I ran out of row space for my tables. With InnoDB, MySQL allows a maximum row size of up to 8000 bytes. Each field contributes a few bytes to this: a few bytes for each INT, a byte for each TINYINT, another few for each CHAR field and so on. TEXTs and BLOBs (LONG or not) are an exception to the rule: they take up 256 bytes of space in the row (768 if you use UNICODE) and the rest of their content is stored elsewhere. As a consequence, enthusiastic use of LONGTEXT fields will eat up row space quickly. My table had over 20 LONGTEXTs, so I got the MySQL 139 error when I tried to add another field, which indicates that I’d run out of row space.

MyISAM does not impose such a restriction, but has fewer features than InnoDB (referential integrity being one important feature lacking). However, it turns out that is is better to split a big InnoDB table in smaller ones (as I did) than to switch to MyISAM.

See also:

Did this article help you out? Please help us find more time to write useful guides & articles like this by donating a buck or two. It'll keep us coffee-fueled. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *