MySQL

MySQL is supported from CMS Fiona 6.6.

For the supported database version, please refer to the system requirements. We recommend using the commercial version of MySQL including support, in case you require competent assistence. Such a version can be purchased from us.

If you plan to store large blobs (100 MB or more) in the 32-bit version of the database, we recommend testing the system with large blobs prior to using it productively. Due to the MySQL configuration it might be necessary to use the 64-bit version of the database server.

When creating the database tables, CMS Fiona uses the InnoDB storage engine by default. Using this engine is obligatory.

To be able to use CMS Fiona with a MySQL database, MySQL must have been installed on the machine running the CMS. To check whether the CMS can connect to the database, run the following commands as the CMS user and provide the database administrator's login data:

mysqladmin -u MySQL-Administrator -p -h MySQL-Server ping
mysql -u MySQL-Administrator -p -h MySQL-Server -e ""

Please note that the standard settings of the MySQL server (my.cnf) need to be changed for running CMS Fiona:

• The MySQL server needs to be operated with the READ-COMMITTED global transaction isolation level. Please specify this value for the transaction-isolation option in the [mysqld] section.

  transaction_isolation = read-committed

• Furthermore, the database server needs to be configured to use the UTF-8 character set.

  character-set-server = utf8
  default-character-set = utf8

• The global SQL mode of the MySQL server must be set to TRADITIONAL. Please specify this value for the sql_mode option in the [mysqld] section.

  sql_mode = traditional

Please note that in particular with large amounts of data the MySQL configuration needs to be adapted to ensure database operation:

• In the innodb_data_file_path option delete the database file size restriction (max:128M) or set it to the required value.

• To ensure best performance, the option innodb_buffer_pool_size should be increased. Depending on the database size and the amount of available RAM, it should be set to at least 512M.

• For single large transactions (large binaries), adapt the innodb_log_file_size (to approximately 25% of innodb_buffer_pool_size) and innodb_log_buffer_size options.

• Likewise, we recommend setting the max_allowed_packet option to 1G.

• To prevent the database server from closing its connections to the CMS, increase the values of net_write_timeout to 1800 and wait_timeout to at least 3600. As an alternative to changing wait_timeout, you can set the slaveIdleTimeout parameter of the CMS to a value less than wait_timeout. This can be done in the tuning.xml file.

• We recommend activating innodb_file_per_table and to deactivate innodb_data_file_path in the MySQL configuration file my.cnf. This change has the effect that an individual file is used for each database table, making data access faster. See below for a description of how to proceed.

• In MySQL 5.0.32 and later, the innodb_rollback_on_timeout parameter is available. In case of a timeout, not only the last statement of a transaction is rolled back but the transaction as such if this setting has been activated. This was the default in MySQL prior to version 5.0.13 and should be activated for data consistency reasons.

If your MySQL server is not running on the standard port 3306, the port needs to be specified in the database configuration of the instance concerned (mysql.xml in the config directory). The port is queried by the install-db script used for database creation.

if a MySQL database is created using install-db, it will be configured to permit access only from the machine on which the script was executed. To permit access from a different machine, the access permissions must be changed on the MySQL server accordingly.

analyze table tablename1, tablename2, ...
flush tables;