TOTVS Fluig English

Árvore de páginas

Index


Approved MySQL versions

To check the approved versions of MySQL, go to Portability Matrix.


Step-by-step configuration of MySQL

The settings below must be made before creating the platform database.

PLEASE NOTE

Upgrading to MySQL 8.0, i.e. when you already have a database created in previous versions, is a complex process that involves a lot of configuration and requires a lot of attention.

We recommend consulting a database specialist to carry out these procedures due to the complexity of the settings.

However, we have listed some support materials that we recommend you read before proceeding with the update:

https://dev.mysql.com/doc/refman/8.0/en/upgrading.html

https://dev.mysql.com/blog-archive/inplace-upgrade-from-mysql-5-7-to-mysql-8-0/

Attention!

For security reasons, it is highly recommended that you encrypt your password according to the procedure in the database password encryption documentation.


Creating a user and granting privileges

To create a new user in a MySQL database we use the CREATE USER statement, according to the syntax below:

CREATE USER 'sha2user'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'password';

Where:

  • sha2user: Name of user to be created;
  • localhost: Host who will access;
  • password: Password of user to be created;


Once the user has been created, it is necessary to grant it the necessary system privileges, according to the syntax below:

GRANT all ON fluig.* TO 'sha2user'@'localhost';

Where:

  • sha2user: Name of previously created user;
  • localhost: Host of previously created user;


Connection parameter

At this stage, a specific configuration is made for the version of MySQL 8.0. For the other versions of MySQL, the default settings remain.

If the configuration is for version MySQL 8.0, you need to change the connection parameter in the file domain.xml. To do that, access the directory \appserver\domain\configuration and locate the tags <connection-url>. The parameter of this tag should be changed from: ?useSSL=false to ?allowPublicKeyRetrieval=true&amp;useSSL=false


Case sensitive

By default, table aliases are case sensitive on Unix, but not on Windows. To avoid problems caused by this feature, in MySQL, it is necessary to add the system variable lower_case_table_names to the MySQL setup file.

MySQL in versions prior to 8.0

If you use the MySQL database in versions prior to 8.0, simply include this variable in the file my.ini (Windows) or my.cnf (Linux).

lower_case_table_names=1

MySQL in the 8.0 version

If you use the MySQL database in version 8.0 in Windows, you don't need to do any setup. The lower_case_table_names variable is already configured by default with the value 1.


On Linux, this setting is REQUIRED when installing the MySQL server, changing the variable lower_case_table_names to the value 1.

As this is a system variable that cannot be changed later, it is very important to configure it correctly during installation.


For more information on using and setting up case sensitive in the 8.0 version, click here.


Engines

You also need to confirm that the tables will be created correctly in the database. To do that, go to Command Line Client of MySQL, check the engine being used by entering the command:

SHOW ENGINES\G

Locate the engine InnoDB, as shown in the image below:



If the engine InnoDB is not presented as Default, the parameter default_storage_engine should also be added to the file, as follows:

Linux: my.cnf / Windows: my.ini 
default_storage_engine=InnoDB


Memory

You can also define the amount of memory to be used by MySQL. On a dedicated server, the best practice is to allocate 75% of the available memory to the database:

Linux: my.cnf / Windows: my.ini
innodb_buffer_pool_size=<valor em bytes>


  • For example, to allocate 128 MB of memory we would have: innodb_buffer_pool_size=134217728.


Creating tables

If you create forms with many fields, most of which are TEXT fields, MySQL can restrict the creation of tables where the sum of the fields exceeds the allowed limit. 

We recommend making the following settings in the database to resolve this restriction:

Linux: my.cnf / Windows: my.ini
innodb_log_file_size=512M
innodb_log_buffer_size=800M
innodb_strict_mode=0


Confirm changes

After making the settings described in the previous steps, you need to restart MySQL and confirm that the changes have been applied.

To check if the case sensitive setup was configured succcessfully, run the SQL command below. If, in the result, the column value has the value 1, then the setup is correct.

SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';

Also confirm the change of Engine to InnoDB if the parameter default_storage_engine has been added to the file:

SHOW ENGINES\G

If the engine InnoDB appears as Default , the setup has been applied successfully.


Attention!

  1. Timezone: The MySQL database has timezone validation, so in order to install the platform with MySQL, the server where the platform is installed must have a valid timezone (recognized by MySQL).
  2. SSL: If you need to use SSL, you need to change the useSSL parameter to true. This parameter is found in the connection string inside the file domain.xml.


Database creation with UTF-8 encoding

The platform needs a database with charset UTF-8. To create the bank with this feature, use the following SQL command, replacing "bank_name" with the name of the bank to be created:

MySQL in versions prior to 8.0
CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
MySQL in the 8.0 version
CREATE DATABASE nome_do_banco CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

PLEASE NOTE!

As of version 8.0, charset utf8 and collate utf8_general_ci are discontinued and will soon no longer be usable.

If you are upgrading to version 8.0, i.e. you already have a database created with charset utf8 and collate utf8_general_ci, you need to plan to convert the database and tables to charset utf8mb4 and collate utf8mb4_general_ci.

We recommend consulting a database specialist to carry out these procedures. There are tools that perform this conversion and also SQL commands that can be run manually on your database.

There is no standard for "database_name", each client can name it as they wish, i.e.: fluig, fluig-approval or mycompany.


Driver Download

To use the MySQL database, you need to download the MySQL driver separately.

Therefore, before you start installing/updating the platform, follow the steps below:


  1. Download the driver directly from the MySQL website;

    Attention!

    The MySQL driver must be the same version as the MySQL database you have installed. To find out which versions are approved, go to the Portability Matrix.

  2. In the field Select Platform, click Independent Platform;
  3. Click the Download button next to the ZIP folder version;
  4. Click No thanks, just start my download, and it will start downloading a file named mysql-connector-java-XX.Y.ZZ.zip or mysql-connector-j-XX.Y.ZZ.zip, where XX.Y.ZZ is the current driver version;
  5. Unzip the downloaded file. A folder named mysql-connector-java-XX.Y.ZZ or mysql-connector-j-XX.Y.ZZ.zip will be created;
  6. This folder has a file named  mysql-connector-java-XX.Y.ZZ-bin.jar or mysql-connector-j-XX.Y.ZZ.jar. This file is the driver.

During the installation of the platform, you will need to indicate the path where the MySQL's driver is stored.


How do I update it?

The Fluig update does not allow you to update the driver via the installer. This procedure must be carried out manually, following the steps below:

  1. Go to the folder <instalação Fluig>\appserver\modules\com\mysql\main
  2. Delete the file mysql-connector-java.jar from this folder
  3. Copy the file mysql-connector-java-XX.Y.ZZ-bin.jar or mysql-connector-j-XX.Y.ZZ.jar (downloaded from the website and unzipped) to the folder <instalação Fluig>\appserver\modules\com\mysql\main
  4. Rename the copied file (mysql-connector-java-XX.Y.ZZ-bin.jar or mysql-connector-j-XX.Y.ZZ.jar) to mysql-connector-java.jar


Optimizing MySQL database performance

Attention!

Mysql's default limit for text fields is 195, and all tests with forms on the platform are carried out within this standard.

We do not recommend changing the parameter innodb_log_file_size for exporting “to” fields, as this could compromise the platform's performance.

When we work with a high volume of data and users, the database can suffer from poor performance. An investment in the physical capacity of servers is always good, but there are times when tuning will represent significant performance gains.

For more information on tuning and MySQL performance optimization, consult the official documentation available at MySQL Optimization.