Skip to content

Configuring QIE with MySQL

Minimum version

QIE requires MySQL 8.0 or later.

Create a QIE Database Schema

Using the MySQL Workbench or a similar tool, connect to the MySQL database server where the QIE database resides and create a database schema called 'qie' or whichever name you wish to use for the QIE related database.

Create a Database User with DB Owner Privileges to the QIE Database Schema

Create a database user to be used by QIE for connecting to the QIE database schema created above. This user must be configured with full privileges to the QIE database schema as the QIE application handles the creation and management of all database tables, indexes, etc. Ensure that the default database associated with the user is set to the QIE database schema created above.

Setup In-Memory Tables (High Availability Only)

In-Memory tables are required for High Availability in QIE. MySQL allows for In-Memory tables by default.

Download and Install MySQL JDBC Driver

Due to MySQL licensing constraints, QIE does not include the JDBC driver. Therefore, you must manually download and install it before connecting QIE to a MySQL database.

Installation Instructions:

  1. Download the latest MySQL Connector/J driver from: https://dev.mysql.com/downloads/connector/j/.

  2. Select the "Platform Independent" operating system option.

  3. Download the "Zip Archive." (You can skip the login/signup prompt by selecting "No thanks, just start my download.")

  4. Extract the downloaded ZIP file.

  5. Locate the mysql-connector-java-{version}.jar file within the extracted folder.

  6. Extract the contents of the .jar file (treat it as a ZIP archive) into the '/java/qie/jdbcDriver' directory.

Note

Windows Users, if you do not have a zip program installed that can extract the .jar file, just rename the .jar file to have an extension of .zip and then use windows explorer to unzip the file.

Configure the QIE Service Manager to connect to MySQL

Add the following Java Options in the Arguments on the Startup tab of the QIE Service Manager:

-Dconnection.driver=com.mysql.cj.jdbc.Driver
-Dconnection.url=jdbc:mysql://{server}:{port, default=3306}/{database, default=qie}
-Dhibernate.dialect=com.qvera.qie.persistence.MySQL8UnicodeDialect
-Dconnection.username=???
-Dconnection.password=???

Note

Use the following list to select your correct MySQL Server dialect:

MySQL 8, use MySQL8UnicodeDialect

MySQL 8.0, use MySQL80UnicodeDialect

MySQL 8.4, use MySQL84UnicodeDialect

Note

Refer to the Database Related Java Options section of this guide for a complete description of each of the above options.

MySQL 8 authentication plugin

MySQL 8 defaults to the caching_sha2_password authentication plugin. The MySQL Connector/J supports it natively but requires either a TLS connection or the driver-side allowPublicKeyRetrieval=true option to negotiate the initial hash exchange. If QIE fails to connect with Authentication plugin 'caching_sha2_password' cannot be loaded, either:

  • Enable TLS on the MySQL server and append ?useSSL=true (with an appropriate trust mode) to -Dconnection.url, or
  • Append ?allowPublicKeyRetrieval=true&useSSL=false to -Dconnection.url for a non-TLS setup, or
  • On MySQL 8.0 – 8.3 only, change the QIE MySQL user to use the legacy plugin: ALTER USER 'qie'@'%' IDENTIFIED WITH mysql_native_password BY '<password>'; (mysql_native_password is deprecated in MySQL 8.4 and unavailable on later versions).