Home > Server > Administration > Database Configuration > MySQL
Server

MySQL

Updated: November 15, 2024

The Nuxeo Platform supports the following MySQL version:

MySQL 8.0

Configuration

Grants

Since Nuxeo 8.1, Nuxeo needs access to the information_schema.routines table to be able to determine if stored procedures need to be upgraded. All users have read access to this table, but Nuxeo needs to be able to write the stored procedures:

SET GLOBAL log_bin_trust_function_creators = 1;

Connections

Because MySQL by default likes to drop connections after a short idle time, if you don't use a datasource pool configuration that always checks for valid connections Nuxeo Platform may unexpectedly use a closed connection and this can cause various errors.

Therefore we strongly suggest that you configure MySQL to never drop idle connections, by using a huge value for wait_timeout.

Recent versions

MySQL 5.6 and MySQL 5.7 compatibility needs an update in the ROW_FORMAT used for the tables that my have large row sizes (see this for more). This is not yet automatically done by Nuxeo.

Limitations

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

  • Prior to MySQL 5.6.4, MySQL did not have a datetime data type with a precision less than 1 second (reference). Nuxeo now makes use of this high-resolution data type but this wasn't possible before.
  • 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).
  • It is not possible to get proper relevance ranking for boolean full-text 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 it is 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 prevented us from implementing optimizations related to descendants/ancestors checks and to security checks. This means that large data sets will have problems deleting documents and 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).
  • Full-text 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, full-text support was not transactional as full-text 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 1, part 2, part 3.
Related topics