×

IDMWORKS Blog

SailPoint 7.2.p2 With MySQL 8 Compatibility Issue


While setting up the new SailPoint environment (SailPoint version 7.2 and MySQL database version 8.0.11), after creating the IdentityIQ schema in the MySQL database by executing the creation script “”, we tried to start the application server(tomcat) and the server comes up and running, but the initialization of the identityIQ web application fails with the following error:

“localhost-startStop-1 springframework.context.support.ClassPathXmlApplicationContext:550 – Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘versionChecker’ defined in class path resource [configBeans.xml]: Invocation of init method failed; nested exception is java.lang.RuntimeException: Unable to check IdentityIQ database version: Cannot create PoolableConnectionFactory (Could not create connection to database server.)”

Analyzing The Problem
What does the above error mean? Clearly it means that there is some problem occurring while IdentityIQ attempts to set up the connection to the MySQL database.

But why is IdentityIQ facing the issue while connecting to database?

While installing IdentityIQ, we generate the scripts to create the user and the necessary tables in the different databases (Oracle, MySQL etc.) by executing the command: Iiq schema

So if we carefully review the script generated for the MySQL database, we can observe that a set of commands are generated to be executed in the script. The example/syntax of the command generated to create the user in database and to provide the privileges on the database are:

GRANT ALL PRIVILEGES ON identityiq.* TO ‘identityiq’ IDENTIFIED BY ‘<Password as mentioned in iiq.properties>’;

GRANT ALL PRIVILEGES ON identityiq.* TO ‘identityiq’@’%’ IDENTIFIED BY ‘<Password as mentioned in iiq.properties>’;

GRANT ALL PRIVILEGES ON identityiq.* TO ‘identityiq’@’localhost’ IDENTIFIED BY ‘<Password as mentioned in iiq.properties>’;

When we try to execute the above commands manually by connecting to MySQL, the commands are failing because the syntax of the commands are not as per the syntax that is accepted in the 8.0.11 version of the MySQL database.

Hence, the IdentityIQ and IdentityIQPlugin user creation and Grant privilege operations for both the users (IdentityIQ and IdentityIQPlugin) in database are failing.

Also, the out-of-the-box MySQL driver that gets shipped with IdentityIQ is “MySQL-connector-java-5.1.28-bin”, but this driver is not compatible with the MySQL database version 8.0.11 and causes issues while connecting.

The Solution
The above problem can be fixed by performing the below steps:

  1. Creating the IdentityIQ and IdentityPlugin users and granting the privileges to IdentityIQ and IdentityIQPlugin users by executing the commands with syntax compatible with 8.0.11 version of MySQL database.

Below are the commands syntax to create and grant the privilege to the users:

Creating IdentityIQ and IdentityiIQPlugin Users:

CREATE USER ‘identityiq’@’localhost’ IDENTIFIED BY ‘<Password mentioned in iiq.properties>’;

CREATE USER ‘identityiq’@’%’ IDENTIFIED BY ‘<Password mentioned in iiq.properties>’;

CREATE USER ‘identityiq’@’identityiq’ IDENTIFIED BY ‘<Password mentioned in iiq.properties>’;

CREATE USER ‘identityiqPlugin’@’localhost’ IDENTIFIED BY ‘<Password mentioned in iiq.properties>’;

CREATE USER ‘identityiqPlugin’@’%’ IDENTIFIED BY ‘<Password mentioned in iiq.properties>’;

CREATE USER ‘identityiqPlugin’@’identityiqPlugin’ IDENTIFIED BY ‘<Password mentioned in iiq.properties>’;

Granting privileges on database to IdentityIQ and IdentityIQPlugin Users:

GRANT ALL PRIVILEGES ON identityiq.* TO ‘identityiq’@’localhost’  WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON identityiq.* TO ‘identityiq’@’%’  WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON identityiq.* TO ‘identityiq’@’identityiq’  WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON identityiqPlugin.* TO ‘identityiqPlugin’@’localhost’  WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON identityiqPlugin.* TO ‘identityiqPlugin’@’%’  WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON identityiqPlugin.* TO ‘identityiqPlugin’@’identityiqPlugin’  WITH GRANT OPTION;

  1. Download the MySQL driver that is compatible with the version 8.0.11 i.e. “MySQL-connector-java-8.0.11-bin.jar”. Copy the driver at the following path (This path is as per tomcat application server installation:

<Tomcat_Home>/webapps/identityiq/WEB-INF/lib

 

(before copying the updated driver, make sure to remove the older one)

  1. Start the application server.



Problem #2
Once performing the above step, when we start the application server, the initialization of the IdentityIQ webapp will still fail and will throw the following error in the startup logs:

“2018-07-04 09:16:27,474  WARN localhost-startStop-1 springframework.context.support.ClassPathXmlApplicationContext:550 – Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘versionChecker’ defined in class path resource [configBeans.xml]: Invocation of init method failed; nested exception is java.lang.RuntimeException: Unable to check IdentityIQ database version: Cannot create PoolableConnectionFactory (The server time zone value ‘Coordinated Universal Time’ is unrecognized or represents more than one time zone. Youmust configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)”

The Solution
If we carefully analyze the above error, it is evident that the connector is unable to resolve the server time zone and we need to provide clarity to the driver about the time zone of the server.

We can achieve this by specifying the time zone parameter in the connecting string (data Source URL) in the iiq.properties.

  1. Navigate to the location <Tomcat_Home>/webapps/identityiq/WEB-INF/classes
  2. Open the iiq.properties and modify the parameter “url

Old value of datasource.url:

dataSource.url=jdbc:MySQL://localhost/identityiq?useServerPrepStmts=true&tinyInt1isBit=true&useUnicode=true&characterEncoding=utf8

New value of datasource.url:

jdbc:MySQL://localhost/identityiq?useServerPrepStmts=true&tinyInt1isBit=true&useUnicode=true&characterEncoding=utf8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

  1. Similarly search for the parameter “url” and modify the existing value to specify the time zone.


Old value of pluginsDataSource.url:

jdbc:MySQL://localhost/identityiqPlugin?useServerPrepStmts=true&tinyInt1isBit=true&useUnicode=true&characterEncoding=utf8

New value of pluginDataSource.url:

jdbc:MySQL://localhost/identityiqPlugin?useServerPrepStmts=true&tinyInt1isBit=true&useUnicode=true&characterEncoding=utf8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

  1. Restart the server.



The application server and identityIQ applications should work fine now.

Questions, comments or concerns? Feel free to reach out to us below, or email us at IDMWORKS to learn more about how you can protect your organization and customers.

Leave a Reply

Your email address will not be published. Required fields are marked *