IAM not starting with MYSQL8

Products

• Service Virtualization

Issue/Introduction

Trying to using MYSQL 8.0.20-11 for IAM in 10.7.2 with mysql-connector-j-8.0.32.jar or mysql-connector-j-8.0.32.jar and ran into issues starting IAM. Below are the steps followed:

 

. Placed the mysql-connector jar in in DevTest_Home/IdentityAccessManager/database/driver folders.

 

. Configured the below properties in iam.properties

iam.db.vendor=mysql
iam.db.url
iam.db.use
iam.db.password
iam.db.jdbc.driver.path

Started IAM and seen the below error server.log

Error has occurred while updating the database: liquibase.exception.DatabaseException: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

Environment

Release : 10.7.2

Cause

The issue is environment specific. If MYSQL requires Primary Keys for all created tables and if IAM doesn't create primary keys during the creation of the tables reported error is seen and IAM doesn't start.

Resolution

Running the modified MYSQL DDLs to create tables with primary keys and then starting IAM should resolve the issue. Recommend to follow the below steps:

 1. Keep IAM Service in stopped state.

 2. Exported sql dump (IAM_Home/database/dll folder) from a newly installed IAM with Mysql on a DB without REQUIRE PRIMARY KEY constraint. Modified the mysql.dll file to add primary key with ID, Author, Filename for databasechangelog and DATABASECHANGELOG_IAM_ENTITY tables.

 3. The attached sql dump file "mysqlwithdata.ddl" has the updated DDLs.

 4. Create a Blank schema with Charset UTF8 and collation as utf8_unicode_ci

 5. Executed The sql dump file against the DB

 6. Make sure IAM properties point to the correct DB and start IAM and it should start without any errors.