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.
Activating innodb_file_per_table
for Databases
After activating innodb_file_per_table
and deactivating
innodb_data_file_path
in the MySQL configuration file,
my.cnf
, the databases concerned need to be deleted and created
again for the change to become effective. To do this, please proceed as
follows:
CM -dump
.my.cnf
and
restart the server.mysql
command line tool.cmdb.xml
(and tedb.xml
,
respectively).CM -restore
.Good database performance requires that the indexes of all tables are updated regularly.
analyze table tablename1, tablename2, ... flush tables;