Icon

The content of this documentation refers to all the Nuxeo products and modules. You may want to check the Nuxeo Platform technical documentation, the Nuxeo Studio documentation, the Core Developer Guide, or the Administration Guide.
Contributors, don't hesitate to move pages in the relevant spaces

Skip to end of metadata
Go to start of metadata

The Nuxeo Platform supports MySQL as a backend database. If you choose MySQL for your Nuxeo project, you should know that in the past, we have encountered some limitations that could impact your user experience.

Limitations That Impact Functionality

  • MySQL did not have a datetime data type with a precision less than 1 second (reference) until MySQL 5.6.4 (reference). This means that events occurring in the same second cannot be properly ordered, which means that document versioning could be inaccurate if a document is changed more than once in a single second. Nuxeo will be updated to support this new feature in the future.
  • Prior to MySQL 5.6, configuration of fulltext features required external plugins to change things like stop words or stemming rules (reference). Since MySQL 5.6 this is not a problem anymore (reference).
In this section

 

  • It is not possible to get proper relevance ranking for boolean fulltext results, or to normalize the relevance between 0 and 1. This can lead to imprecise fulltext results.
  • MySQL has a 64 KB row size limit, which means that it is not possible to put more than a few long VARCHAR() columns in a table or a schema. This means any custom schemas created for Nuxeo have to be carefully defined to not contain too many string fields. (Note: MySQL is not alone in having row size limitations, and since Nuxeo 5.3 it's possible to use a TEXT/CLOB column to work around the size limit by specifying the field as <field type="largetext">; see NXP-3993.)
  • In MySQL, equality tests for strings are case-insensitive. This means that some custom application queries may confuse uppercase and lowercase.

Limitations That Impact Performance

  • The lack of array datatypes in the stored procedures language leads to poor ACL checking performance. This means that large data sets will 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 concurrent behavior problems when there are writes due to MyISAM locking issues. This means that concurrent updates to different documents may end up causing user-visible errors. At this time, the locking impact is unknown for MySQL 5.6 and InnoDB fulltext tables.

Other Limitations

  • MySQL loses connections unexpectedly, which requires configuring ;autoReconnect=true as a workaround, which could cause transaction problems. (It's better to configure MySQL with an near-infinite wait_timeout).
  • Prior to MySQL 5.6, fulltext support was not transactional as fulltext required MyISAM tables (reference). Since MySQL 5.6 this is not a problem anymore, as InnoDB tables can be used (reference). See the InnoDB Full-text Search in MySQL 5.6 articles: part 1part 2part 3.

 

See also the limitations of other databases.

Related Content

 

4 Comments

  1. Anonymous

    Using any RDBMS for full-text search is begging for trouble IMHO.  Tried Lucene?

    If the "gratuitous nonstandard syntax" leads to more complex Nuxeo internals, you probably have a coupling problem anyways.

    1. Lucene is very nice but when you want a relational (JOIN-based) clause and fulltext search in the same query there's no way to do it efficiently with just Lucene. So fulltext search in the database, when available and correctly implemented, is really an important bonus.

      With Lucene it's also impossible to update just a field in a document, you have to rewrite all the document, which is very bad for efficient updates.

      Anyway Nuxeo can work with Lucene, but this is less integrated than everything-in-the-RDBMS.

      Regarding the syntax of course it's a non-issue once you've written an abstraction layer, but still, it's gratuitous.

    2. Note also that we're using Lucene, but not for our core repository service (because, mostly, because we need to do JOINS between regular fields and fulltext indexes).

      See this blog post for our use of Lucene in the context of semantic content analysis.

      We also have integrated external search engine, such as Solr (based on Lucene). 

  2. Anonymous

    I am a fan of MySql personally so I just wanted to say thank you for having real and current information about why MySql may not work in your situation.  So often the criticisms are by people who obviously have no clue what they are talking about or are parroting arguments from years ago.

    Most of the above do not particularly impact me in my own work.  For me, MySql is often the best solution by a large margin.  However, I agree that some of these do need to be addressed for MySql to be applicable more broadly.  PostgreSQL (and indeed other databases as well) can certainly be the right tool for the job in many cases.

    Again, thank you for raising the level of dialogue to specific points about concrete issues.