MySQL is being developed by the Oracle company and is available in several commercial versions but also as MySQL Community Edition version that is free of charge. The installation files of MySQL can be obtained for example from

Included in the installation package (since version MySQL 5.6) there is the administration tool Workbench and also the ODBC drivers.

In order to work with the MySQL database in the PROMOTIC system it is necessary to have the 32-bit drivers installed on the computer even if the installed database itself is 64-bit.

The database can be filled:
- by the PmaTrendGroup object, if in this object the "Storage type" configurator is set to Database MySQL cyclic
- by the PmaAdo object
- by the PmaDatabase object (obsolete) via named ODBC source

MySQL characteristics
- The community version is available free of charge.
- The functionality is not limited in the free version.
- From the functionality and configuration point of view, the MySQL is simpler than MS SQL Server.
- The database runs as Windows service.
- It allows easy backups, setup of access permissions, transactions, replications, etc.
- It is functional on most operating systems Windows OS, Linux and OS X.
- PROMOTIC can be set in order to have the trends and alarms/events writing its data into MySQL.
- It has its own programming language for trigger definition and procedures.
- It is possible to choose one of two standard database engines: MyISAM or InnoDB.
- It is often used for Web applications

Properties of basic database engines
MyISAM database engine is not suitable for frequent writing. It is more optimised for frequent reading from the database and does not support transactions. When multiple users acces the table it uses a lock on the whole table level.
Therefore this engine is not recommended to be used in the PROMOTIC system for writing trends, alarms and events.

InnoDB engine is better for frequent writing, it supports transactions, and if multiple users access the table it locks only the corresponding records.
This database engine is recommended to be used in the PROMOTIC system for writing trends, alarms and events.

There are also some disadvantages of the InnoDB engine. One of them is e.g. the problem of SELECT COUNT(*) command type. The speed of execution of such command by this engine highly depends on the table size. If the table contains approx. 300-400 thousands records then this command execution may take up to one second. Therefore keep in mind that using this command may in some cases significantly slow down your application.

Recommended settings of the MySQL database for usage in the PROMOTIC system
For more demanding applications (high number of PmaTrendGroup objects, frequent writing of alarms etc.) it is necessary to configure the MySQL database properly to prevent overload:

1. In the configuration file my.ini it is necessary to modify the entry "default-storage-engine" to InnoDB so all new created tables use the InnoDB engine. If there are existing tables it is recommended to convert them into InnoDB e.g. by the Workbench administration tool.

2. Then set the InnoDB engine entry "innodb_flush_log_at_trx_commit" to 0 or 2 (it is set to 1 by default). This option defines the physical data writing process to the database.
0 - writes data and transaction log to disk approx. once a second - this lowers the number of disc operations
1 - writes the transaction log to disk on every writing - it is safe but slow if a large number of writing operations is demanded
2 - writes into transaction log after the transaction is confirmed, but the physical writing to disk is really completed approx. once a second - this lowers the number of disk operations

3. It is also recommended to set the cache memory capacity for data and table indexes by entry "innodb_buffer_pool_size". For dedicated database servers the recommended value is 70% - 80% of installed operating memory.

4. MySQL is configured by default to maintain client connection for 8 hours (28800 seconds) unless the client performs any activity. After that time, the connection is automatically closed by the server. Such behavior causes PROMOTIC application to lose connection with the database server and then can not write necessary data to the database.

Possible solutions:
A) On the MySQL serveru side - increasing the time period (parameters wait_timeout and/or interactive_timeout) in MySQL server
B) On the PROMOTIC application side - making the PROMOTIC application perform periodically any activity with the database before the connection times-out (i.e. write or read data to/from the database)

Solution procedure for different objects in the PROMOTIC application:
1. Alarms/Events - all PmaAlarmGroup/PmaEventGroup objects with the same "ConnectionString" parameter share a single connection to the database. When using MySQL, it is necessary to ensure that something is written to the database at least once within the timeout period (interaction_timeout). In practice, just create one event group and create an "app alive" event in the application every 8 hours (or even more often, e.g. every hour). This will ensure that the connection to the alarm database is not closed.
2. Trends - all PmaTrendGroup objects with the same "ConnectionString" parameter share a single connection to the database. Therefore, it is necessary to ensure that at least one PmaTrendGroup writes something into the database at least once within the timeout period. This is ensured in most applications - the situation that the application writes trends only once every 8 hours or even less often is very rare in practice.
3. PmaAdo - here the designer must ensure that some MySQL server query is activated at least once in a defined timeout period (or more often). Additionally, the PmaAdo object can be disconnected/reconnected to the database if the server automatically disconnects.
An example of command that can be used to maintain connection while keeping the MySQL server load at minimum:

For next descriptions to the MySQL, see:
PROMOTIC 9.0.27 SCADA system documentation MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r.o.