MySQL limitations

MySQL has some major limitations, mainly fulltext, datetime precision, size limitations if you define your own schemas, and poor optimizer performance. That being said, MySQL is still supported for use with Nuxeo.

Below is a partial list of the limitations we encountered, in the context of the needs of Nuxeo EP.

Limitations that impact functionality

• It has no datetime data type with a precision less than 1 second (reference). This means that events occurring in the same second cannot be properly ordered (this impacts versioning).
• It has poor fulltext configuration, and you need to use external plugins to change stop words or stemming rules (reference). This means that you cannot customize the fulltext behavior.
• There is no way to get proper relevance ranking for boolean fulltext results, and no way to normalize the relevance between 0 and 1. This means that fulltext results can sometimes be a bit imprecise.
• It has a 64 KB row size limit, which means that you cannot put more than a few VARCHAR() columns with non-ridiculous sizes in a table and therefore in a schema. This means that your custom schemas have to be carefully defined to not contain too many string fields. (Note however that MySQL is not alone in having row size limitations, and since Nuxeo 5.3 you can use a TEXT/CLOB column which works around this size limit, by specifying the field as <field type="largetext">; see NXP-3993.)
• It does case-insensitive equality tests on strings, for instance both 'a' = 'A' and 'a' IN ('A', 'B') are true. This means that some custom application queries may confuse uppercase and lowercase.

Limitations that impact performance

• It has a poor query optimizer. This leads to bad performance on big data sets with complex queries (and sometimes simple ones).
• The lack of array datatypes in the stored procedures language leads to poor ACL checking performance. This means that big data sets will always have problems doing security checks efficiently.
• Triggers are not activated by cascaded foreign key actions (reference).
• Foreign key on delete cascade operations are limited to 15 levels deep, which means that recursive deletion of folders that deep cannot be done by the database alone (reference).
• Fulltext operations have very bad concurrent behavior when there are writes due to MyISAM locking issues. This means that concurrent updates to different documents may end up causing user-visible errors.

Other limitations

• It loses connections when it decides to, and you have to use hacks like ;autoReconnect=true to try to bypass that, but this may cause transaction problems.
• The fulltext support is not transactional as fulltext requires MyISAM tables (reference).

All these problems lead us to recommend not using MySQL in production if you have a choice.

See also the limitations of other databases.