Installation and Administration

MySQL

Updated: July 17, 2023

The Nuxeo Platform supports the following MySQL versions:

5.6, 5.7 (upcoming)

We always recommend that you use the latest stable version.

Configuration

Grants

Since Nuxeo 8.1, Nuxeo needs access to the mysql.proc table to be able to determine if stored procedures need to be upgraded. You can grant this access using:

GRANT SELECT ON `mysql`.`proc` TO 'nuxeo'@'%' 

(Adapt with the proper user name instead of nuxeo if needed, and a more restrictive IP address if you wish.)

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

  • 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.
  • MySQL does not have a data type corresponding to a datatime with a timezone.
  • 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 1part 2part 3.