MySQL

Last modified 01 Feb 2022 19:38 +01:00
Deprecated
This functionality is deprecated since version 4.4. The functionality is still supported and maintained, but it will no longer be extended. The plan is to remove this functionality sooner or later. Users of this functionality are strongly encouraged to stop using this functionality and migrate to a newer equivalent.

Supported MySQL version is 5.6.4 and higher. MySQL in previous versions didn’t support dates/timestamps with second fraction precision.
Release notes for MySQL 5.6.4
Release notes for ConnectorJ 5.1.23

DO NOT FORGET TO RESTART TOMCAT AFTER CONFIGURATION CHANGE!!!
Character sets and collation

MySQL 5.6 until version 8 has quite messy utf8 charset support. For MySQL 5.6 we can use utf8 charset (alias pointing to utf8mb3 - not full utf8) with collations like utf8_general_ci (case insensitive) or utf8_bin (case sensitive), but these collations will break order by - they compare only character code points, so upper case characters will be before lower case characters. From Mysql 8, mysql team recomends charset utf8mb4. Collation for correct searches would be utf8mb4_0900_as_cs. utf8mb4 is mainly necessary for deployments with extensive use of accented characters.
Having MariaDB and MySQL Drivers in the Same Classpath

Since MariaDB driver is bundled within midpoint.war and we want to use MySQL JDBC driver, we’ll end up with two drivers that can handle jdbc:mysql://…​ To solve this issue we can follow MariaDB JDBC doc and use disableMariaDbDriver option. MariaDB driver will skip MySQL jdbc urls.

${midpoint-home}/config.xml repository configuration change

<configuration>
  <midpoint>
    <repository>
      <repositoryServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlRepositoryFactory</repositoryServiceFactoryClass>
      <database>mysql</database>
      <jdbcUsername>midpoint</jdbcUsername>
      <jdbcPassword>password</jdbcPassword>
      <jdbcUrl>jdbc:mysql://localhost:3306/midpoint?characterEncoding=utf8&amp;disableMariaDbDriver</jdbcUrl>
    </repository>
  </midpoint>
</configuration>

Note that any & in URL must be escaped (replaced by &) as is customary in XML.

Driver

Driver is not bundled due to licencing issues. Download MySQL JDBC Driver and insert driver (for instance mysql-connector-java-8.0.21.jar) on classpath either in deployed midpoint */webapps/midPoint/WEB-INF/lib or in */apache-tomcat/lib

You can also download the driver JAR from Maven repository: https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.21/mysql-connector-java-8.0.21.jar

Database create

CREATE DATABASE midpoint CHARACTER SET utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin DEFAULT COLLATE utf8_bin;
CREATE USER 'midpoint'@'localhost' IDENTIFIED BY 'password';
GRANT ALL on midpoint.* TO 'midpoint'@'localhost';
use midpoint;

Use proper sql script based on the distribution you are using either stable or snapshot:

For stable release check:

For snapshot check:

After starting tomcat in Windows via apache-tomcat\bin\startup.bat or Linux via apache-tomcat\bin\startup.sh open midpoint in browser http://localhost:8080/midpoint/admin/ Login:administrator Pass:5ecr3t and create new user. Check in MySQL table m_user where new user should be created: select * from m_user

Common issues

VARCHAR columns length

As we’re using VARCHAR(255) for string columns there are situations when your object properties can exceed this limit. For example shadow name exceeds 255 characters (it’s possible for long DNs in LDAP or Active Directory). If you try to save such object you’ll get an error "Data truncation: Data too long for column 'name_orig' at row 1" and object is not saved to database.

If you try just to alter column to bigger size, you’ll end up with “#1071 - Specified key was too long; max key length is 767 bytes” error (if there is an index or unique constraint on that column). Reason for that is that by default VARCHAR(255) is maximum size for an indexes (also unique constraints) using utf8 and InnoDB storage (see http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html).

There are two solutions for this situation:

  1. Update column definition and index definitions[WARNING] .Notice

This way you can store names with length up to 300 characters (in this sample code), but only first 255 chars from each value will be indexed. In case unique constraint index was used, uniqueness will be checked only against first 255 chars.

SQL
ALTER TABLE `m_shadow`
CHANGE COLUMN `name_norm` `name_norm` VARCHAR(300) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
CHANGE COLUMN `name_orig` `name_orig` VARCHAR(300) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,

DROP INDEX `iShadowNameOrig`,
ADD INDEX `iShadowNameOrig` (`name_orig`(255) ASC),
DROP INDEX `iShadowNameNorm`,
ADD INDEX `iShadowNameNorm` (`name_norm`(255) ASC);
+

  1. Updating only column definitions and turning on innodb_large_prefix feature (Not tested with MidPoint)
    http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix

Note that innodb_large_prefix is "ON" by default since MySQL 5.7.7 (and is deprecated as well), so this problem should not appear with the default settings of MySQL 5.7.7 and later. But make sure you have also set the correct innodb_file_format as well (please see here).

Packet for query is too large

When we’re using new LDAP Connector, usually generated resource schema in XML representation is bigger then default max_allowed_packet size in MySQL. To increase this value, see documentation for your MySQL version. In some cases this error is indicated as consequence for "No schema for resource" exception.

Sample my.cnf configuration file
[global]
. . .
max_allowed_packet = 256M
. . .

You need to restart MySQL after changing this configuration. Reload will not work.

Was this page helpful?
YES NO
Thanks for your feedback