Updating the MySQL Database Settings

Use the following procedure to update settings for the MySQL database. Refer to Required MySQL Database Settings when making the changes. Also see the subtopics that follow the procedure.

To configure the MySQL database, do the following:

1. Within your MySQL installation, do one of the following:

   • As root user in Linux, open the my.cnf file (typically located in /etc/).

   • As a Windows administrator, open my.ini file (typically located in C:\ProgramData\MySQL\MySQL Server``version).

2. Edit the settings as shown in the previous table. (If necessary, click the appropriate Property link in the table for a description of how to configure a given setting.)
3. After you have updated the settings, restart the database server.

Storage Engine

Specify InnoDB for the default-storage-engine property. By default in MySQL, InnodDB is already specified for this property, so you most likely will not need to change it.

To verify the current default storage engine, use the following command:

• SELECT * FROM INFORMATION_SCHEMA.ENGINES;

If you need to add the default-storage-engine property (or update the current value of this property), use the appropriate procedure:

• In Linux, add the following line to the [mysqld] section of the my.cnf file (or update the existing property value):

  • default-storage-engine=innodb

• In Windows, add the following line to the [mysqld] section of the my.ini file (or update the existing property value):

  • default-storage-engine=innodb

Character Set

Specify utf8mb4 for the character-set-server property when installing the MySQL database server for Code Insight. (This value is applied at the database/schema level.)

info

Ensure that the character-set-server value is set to utf8mb4. Any other value has been know to produce undesirable results during a scan, forcing users to have to set up the database again since no rollback options are available. As protection, the Code Insight installer will not proceed with the installation once it detects a value other than utf8mb4 for the character-set-server property in the database.

To verify the current character set, use the following command:

• SELECT @@character_set_database;

If you need to add the character-set-server property (or update the current value to the required value), use the appropriate procedure:

• In Linux, add the following line to the [mysqld] section of the my.cnf file (or update the existing property to the required value):

  character-set-server=utf8mb4 

• In Windows, add the following line to the[mysqld] section of the my.ini file (or update the existing property to the required value):

  character-set-server=utf8mb4 

Collation

Select utf8mb4_unicode_ci for the collation-server property when installing the MySQL database server for Code Insight. (This value is applied at the database/schema level.)

info

Ensure that the collation-server value is set to utf8mb4_0900_ai_ci (for MySQL 8.0) or utf8mb4_unicode_ci.(for MySQL 5.7). Any other value has been know to produce undesirable results during a scan, forcing users to have to set up the database again since no rollback options are available. As protection, the Code Insight installer will not proceed with the installation once it detects a value other than utf8mb4_unicode_ci for the collation-server property in the database.

To verify the current collation, use the following command:

• SELECT @@collation_database;

If you need to add the collation-server property (or update the current value to the required value), use the appropriate procedure:

• In Linux, add the following line to the [mysqld] section of the my.cnf file (or update the existing property to the required value):

  • collation-server=utf8mb4_unicode_ci

• In Windows, add the following line to the[mysqld] section of the my.ini file (or update the existing property to the required value):

  • For MySQL 8.0: collation-server=utf8mb4_0900_ai_ci

  • For MySQL 5.7: collation-server=utf8mb4_unicode_ci

InnoDB Buffer Pool Size

Set the innodb_buffer_pool_size property to at least 12G (gigabytes).

To verify the current InnoDB buffer pool size, use the following command. The returned value is in gigabyte (G) units.

• SELECT @@innodb_buffer_pool_size/1024/1024/1024;

If you need to add the innodb_buffer_pool_size property (or update the current value of this property), use the appropriate procedure:

• In Linux, add the following line to the [mysqld] section of the my.cnf file (or update the existing property value):

  • innodb_buffer_pool_size=12G

• In Windows, add the following line to the [mysqld] section of the my.ini file (or update the existing property value):

  innodb_buffer_pool_size=12G 

InnoDB Log File Size

Set the innodb_log_file_size property to at least 8G (gigabytes).

To verify the current InnoDB log file size, use the following command. The returned value is in gigabyte (G) units.

• show variables like 'innodb_log_file_size%';

If you need to add the innodb_log_file_size property (or update the current value of this property), use the appropriate procedure:

• In Linux, add the following line to the [mysqld] section of the my.cnf file (or update the existing property value):

  • innodb_log_file_size=8G

• In Windows, add the following line to the [mysqld] section of the my.ini file (or update the existing property value):

  • innodb_log_file_size=8G

Maximum Allowed Packets

Set the max_allowed_packet property to 100M (megabytes).

To verify the current maximum packet size, use the following command. The returned value is in megabyte (M) units.

• SHOW VARIABLES LIKE 'max_allowed_packet';

If you need to add the max_allowed_packet property (or update the current value of this property), use the appropriate procedure:

• In Linux, add the following line to the [mysqld] section of the my.cnf file (or update the existing property value):

  • max_allowed_packet=100M

• In Windows, add the following line to the [mysqld] section of the my.ini file (or update the existing property value):

  • max_allowed_packet=100M