After Flowable upgrade to 7.0.0 I get MySQL 8 error sql_require_primary_key

Flowable Engine
1

After the upgrade of flowable to 7.0.0, I am getting ready to deploy it to production and there we are using an external MySQL 8. It looks like MySQL has enforced the rule that all tables need to have a PK.

When I try to run the app I get this error:

liquibase.exception.DatabaseException: 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. [Failed SQL: (3750) CREATE TABLE flowable_prod.FLW_EV_DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35) NULL, DESCRIPTION VARCHAR(255) NULL, COMMENTS VARCHAR(255) NULL, TAG VARCHAR(255) NULL, LIQUIBASE VARCHAR(20) NULL, CONTEXTS VARCHAR(255) NULL, LABELS VARCHAR(255) NULL, DEPLOYMENT_ID VARCHAR(10) NULL)]

How do I fix this issue?

Thank you.

2

Hey @vcarri202_comcast

based on the mysql documentation the default value for sql-require-primary-key is false.
What do you mean with external MySQL? You are not hosting it by yourself? If so, I suggest you check to however is providing you with that external MySQL instance.

greetings

Christopher

3

Inside our company we have to use DBaaS. The company provides a MySQL database for us to use. They enforced the rule that all tables should have a private key. How would I get around this issue?

4

Hey @vcarrin1,

The table you are having issues with is FLW_EV_DATABASECHANGELOG which is the Liquibase Database Changelog table for the event registry engine. According to the Liquibase documentation about this table. It explicitly does not have a primary key

Is it possible for you to disable this check on some tables only? An alternative is to add a key manually on the table. This is explained in the Liquibase forum Why does databasechangelog not have a primary key? - Community Answers - Liquibase

To quote from there

Hope this helps.

Btw, which version are you migrating from? The Event Registry Engine was added in 6.5.0. You could disable that engine through ProcessEngineConfigurationImpl#setDisableEventRegistry. However, you would be missing on some nice features that you get with that engine ;).

Cheers,
Filip

5

Thank you so much for your answer. That makes a lot of sense and it helps a lot. I will take a closer look soon.

6

I am upgrading from 6.4.0 to 7.0.0.

How do I add the PK for Liquibase? I tried these 2 cases and it didn’t work.

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
                        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

 <changeSet id="00000000000005" author="dxAccessControl">
        <addPrimaryKey tableName="databasechangelog" columnNames="id, author, filename" constraintName="PK_databasechangelog" />
 </changeSet>

</databaseChangeLog>

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
                        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet id="00000000000005" author="dxAccessControl">
        <createTable tableName="databasechangelog">
            <column name="id" type="varchar(255)">
                <constraints primaryKey="true" primaryKeyName="PK_databasechangelog" nullable="false" />
            </column>
            <column name="author" type="varchar(255)">
                <constraints primaryKey="true" primaryKeyName="PK_databasechangelog" nullable="false" />
            </column>
            <column name="filename" type="varchar(255)">
                <constraints primaryKey="true" primaryKeyName="PK_databasechangelog" nullable="false" />
            </column>
        </update>
    </changeSet>

</databaseChangeLog>```