Using a MySQL database

To use MySQL as the Application Security database, you must configure the MySQL options file. For information about the supported versions of MySQL, see the Application Security Software System Requirements document.

Application Security requires that all database schema collations be case-sensitive . If your installation is case-insensitive, Application Security does not work correctly.

If you use SSL to connect Application Security to MySQL, OpenText recommends that you increase the allowed number of concurrent client connections by increasing the value of the max_connections system variable (in the my.cnf file). This can prevent the Too many connections error from occurring.

To configure the MySQL 8.0 options file:

  1. Stop MySQL server.
  2. Go to the MySQL server installation directory.

  3. Open the MySQL options file in a text editor.

    To locate the options files and the order in which they are read, run the following command from a terminal: mysql --help.

    • On Windows systems, the default options file is my.ini.

      The default location for MySQL 8.0 is C:\ProgramData\MySQL\MySQLServer 8.0\.

    • On Linux systems, the default options file is my.cnf.

  4. In both the [mysqld] and [mysqldump] sections, set max_allowed_packet to 1G.

    If the [mysqldump] section is not there, create it.

  5. In the [mysqld] section, configure the settings described in the following table. If a listed setting is not included in the file, add it.

    SettingValue
    default_storage_engineINNODB
    innodb_buffer_pool_size

    512M (OpenText recommends 10GB or more)

    The best performance is achieved when all data and indexes fit.

    Together with per-connection memory, the innodb_lock_wait_timeout value must not exceed the total available memory on the server. You can estimate the maximum memory usage as follows:

    max_connections * max_allowed_packet + innodb_buffer_pool_size

    An innodb_buffer_pool_size value between 60 and 80 percent of available memory is appropriate.

    The larger the innodb_buffer_pool_size value, the less disk I/O is needed to access data in tables. On a dedicated database server, you can set this to up to 80% of the machine physical memory size. However, be prepared to scale back this value if you see any of the following:

    • Competition for physical memory causes paging in the operating system.
    • InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.
    • The address space must be contiguous, which can cause problems on Windows systems with DLLs that load at specific addresses.
    • The time to initialize the buffer pool is proportional to its size. On large installations, this initialization time might be extensive. For example, on a modern Linux x86_64 server, initialization of a 10 GB buffer pool takes approximately 6 seconds. For more information, see the MySQL 8.0 Reference Manual.
    innodb_lock_wait_timeout

    300 (recommended) Expressed in seconds

    innodb_log_file_size

    512M

    max_allowed_packet1G

    sql-mode

    "TRADITIONAL"

  6. Save the file, and then restart MySQL server.