[Flowable 6.4.1.3] - DB errors on production environments

Hey there,
the surrounding environment is:

  1. vagrant machine hosting a fresh DB2 scheme with a user which has revoked CREATEIN, ALTERIN, DROPIN capabilities (no DDL like in production).
  2. script from flowable.db2.all.create.sql executed from a DBA-capable user.
  3. Websphere OpenLiberty running the deployments downloaded from the main Flowable site (6.4.1.3).

The application fails because the *.all.create.sql inserts values with ACT_GE/ID_PROPERTY for 6.3.1.0 so the DBchecker on SpringBoot fails expecting a 6.4.1.3 one.

Moreover, the parameter spring.liquibase.enabled=false is not enough to put Liquibase to sleep, so it tries to execute CREATE TABLE scripts, failing because of no CREATE grant.

Any advice?

(I peeked inside flowable-engine GitHub, found many and I built myself a “custom” 6.3.1.0 to 6.4.1.3 sql script, but some of them are not working so I’m totally unsure of the DB integrity… and Liquibase trying to perform CREATE still there…)

Thanks, any suggestion will be appreciated. :wink:

See: Disable Auto creation of DB Tables

This could be a bug, the *.all.create.sql should probably insert the last version.

The parameter spring.liquiase.enabled is already set to false by default via the FlowableLiquibaseEnvironmentPostProcessor. That property is to disable the Spring Boot Liquibase auto configuration and not the one from Flowable. Some of the engines create their own Liquibase instances to perform the update.

Keep in mind that not all engines have the entire create sql written in there. For example the flowable-cmmn-db-changelog.xml has more entries then the custom SQL. You probably need to build the SQL from that changelog. There are other flowable-*-db-changelog.xml out there as well.

What @Robinyo pointed to is a way to disable the auto creation.

Hope this helps,
Filip

Yep I already took a look into that but it refers to the configuration via flowable.cfg.xml and, instead, I’m using the “other” way providing an application.properties for Springboot…
S̶o̶ ̶d̶u̶n̶n̶o̶ ̶w̶h̶a̶t̶’̶s̶ ̶t̶h̶e̶ ̶s̶p̶e̶c̶u̶l̶a̶r̶ ̶p̶a̶r̶a̶m̶ ̶f̶o̶r̶ ̶"̶ ̶d̶a̶t̶a̶b̶a̶s̶e̶S̶c̶h̶e̶m̶a̶U̶p̶d̶a̶t̶e̶"̶ ̶t̶o̶ ̶b̶e̶ ̶u̶s̶e̶d̶ ̶f̶o̶r̶ ̶t̶h̶e̶ ̶a̶p̶p̶l̶i̶c̶a̶t̶i̶o̶n̶.̶p̶r̶o̶p̶e̶r̶t̶i̶e̶s̶,̶ ̶i̶s̶ ̶i̶t̶ ̶t̶h̶e̶ ̶s̶a̶m̶e̶?̶

I just tried with the param flowable.database-schema-update=false but it did not sorted the desired behaviour, Liquibase still tries to perform table creations…

Thank you for your feedback Filip, so, is there a “complete” way to find out what scripts will provide me a consistend database structure for a 6.4.1.3 deployment?

Because peeking into the docs the upgrade “should” be available in the package too, and it’s not the case… So I’ve to find a reliable and repeatable way to extract the missing DDL/DMLs in this case and for future updates too.

Thank you!

Using the flowable.database-schema-update=false property should work. Can you perhaps show us the logs that you are getting>

The upgrades over Liquibase are indeed not available, we need to look into that and decide what would be the best approach from our side. Perhaps we need to provide set of commands to users so they can generate the SQL for their DB of choice.

@filiphr @grimpressive

Just wondering, wouldn’t the requirement be to support:

  • a fresh install in a new environment (DEV, TEST, PROD)
  • an upgrade to an existing environment

A flag to enable/disable schema creation:

  • FLOWABLE_DATABASE_SCHEMA_CREATE (-> dbSchemaCreate())
  • flowable.database-schema-create=false

A flag to enable/disable schema updates:

  • FLOWABLE_DATABASE_SCHEMA_UPDATE (-> dbSchemaCheckVersion() && dbSchemaUpdate())
  • flowable.database-schema-update=true

@grimpressive

If the schema update requires the creation of a new table (for example) then you will need to have enabled (GRANT) the appropriate privileges (CREATE).

Hi Filip,
I already tried it, because I found it to be… uhm… “appropriate” when peeking here:
https://www.flowable.org/docs/userguide/index.html#springBootFlowableProperties
but Liquibase still tries to perform DDLs. I’ll provide a full server log asap…

Yep, but that’s not the real case scenario.
In a production environment we deliver our software to the customer IT dept for the installation but the DB is in charge of other departments, and due to security reason they need to validate the DDLs before they perform, by hand, DDL execution for an install from scratch or upgrades. So no temp-Schema updates (i.e. green-blue deployment) because the DB schema is just a shared one between different appliances.

Sound like you have a requirement to support:

  • a fresh install in a new environment (DEV, TEST, PROD)
  • an upgrade to an existing environment

Additionally, you would like it if your schema versioning tool (liquibase, flyway) supported SQL so that database migration could be performed manually.

A blue-green deployment, so you:

  1. maintain two copies of your production environment (“blue” and “green”)
  2. route all traffic to the the blue environment by mapping production URLs to it
  3. deploy and test any changes to the application in the green environment
  4. “flip the switch” by mapping URLs onto green and un-mapping them from blue

Ref:

Yep. Blue green depl. will not be the case, we just provide software and db scripts, it will be the customer it dept to decide how to apply, or not, a pattern. So we need to stick with the most “basic” approach: a tgz with software and an another one with create_.sql and update_.sql.
Stating that flowable will be embedded, it’s me to decide when and how to deliver to a customer a newer version of it, so it’s me, again, knowing which version they previously had and which one I am delivering them. No versioning tool at all, in this case. The db integrity will be validated by the DBManager classes inside Flowable, looking around the database for the various ACT_GE/ID_PROPERTY containing the desired sw version.

Hi again,

here a tgz (25Kb) having:

  • application.properties
  • server.xml
  • console.log (server.log in OpenLiberty)

Hello,
Is there any news about this issue? I am in the same situation, I tried to locate the right sql files to provide to my DBA team but no luck, I have the mismatch version error on the table, looking in the code, it is because the ACT_ID_PROPERTY table does not have the right schema.version.

As a test, I started the Flowable engine with the flowable.databaseSchemaUpdate set to true and the database is now rightly updated with the right version.

But I can’t go in production with this setting, I need to identify where it is done but even a grep on the sql files with the table name did not help me to identify the file.

Any lead will be welcome because for now, we are stuck with the 6.4.0.

What exactly are you looking for? Upgrade sql scripts?
If so, for which engine - what are you using right now?

The process engine for example has upgrade scripts you can find in https://github.com/flowable/flowable-engine/tree/master/modules/flowable-engine/src/main/resources/org/flowable/db/upgrade

Hi joram
To summarize, we started from a running version 6.4.0, we want to upgrade to 6.4.1.
To do that, I applied on my existing database these 2 scripts:

And changed the version of our dependency to 6.4.1, then, the application does not start with this error:

org.flowable.common.engine.api.FlowableWrongDbException: version mismatch: library version is ‘6.4.1.3’, db version is 6.4.0.0 Hint: Set <property name=“databaseSchemaUpdate” to value=“true” or value=“create-drop” (use create-drop for testing only!) in bean processEngineConfiguration in flowable.cfg.xml for automatic schema creation
at org.flowable.idm.engine.impl.db.IdmDbSchemaManager.schemaCheckVersion(IdmDbSchemaManager.java:76)
at org.flowable.idm.engine.impl.db.IdmDbSchemaManager.performSchemaOperationsIdmEngineBuild(IdmDbSchemaManager.java:147)
at org.flowable.idm.engine.impl.SchemaOperationsIdmEngineBuild.execute(SchemaOperationsIdmEngineBuild.java:29)
at org.flowable.idm.engine.impl.SchemaOperationsIdmEngineBuild.execute(SchemaOperationsIdmEngineBuild.java:24)
at org.flowable.common.engine.impl.interceptor.DefaultCommandInvoker.execute(DefaultCommandInvoker.java:22)
at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53)
at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:71)
at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
at org.flowable.idm.engine.impl.IdmEngineImpl.(IdmEngineImpl.java:45)
at org.flowable.idm.engine.IdmEngineConfiguration.buildIdmEngine(IdmEngineConfiguration.java:166)
at org.flowable.idm.engine.configurator.IdmEngineConfigurator.configure(IdmEngineConfigurator.java:57)
at org.flowable.common.engine.impl.AbstractEngineConfiguration.configuratorsAfterInit(AbstractEngineConfiguration.java:887)
at org.flowable.engine.impl.cfg.ProcessEngineConfigurationImpl.init(ProcessEngineConfigurationImpl.java:1002)
at org.flowable.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:903)
at org.flowable.spring.SpringProcessEngineConfiguration.buildProcessEngine(SpringProcessEngineConfiguration.java:72)
at org.flowable.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:60)
at org.flowable.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:32)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:171)

After investigation in the code, I found that this check uses the schema.version from the ACT_ID_PROPERTY table which still contains 6.4.0.

I imagine that I missed an sql script which I can’t find (but maybe I arrived at a wrong conclusion).

I hope I am clear :slight_smile:

Yes you were clear.

If you are to look at https://github.com/flowable/flowable-engine/tree/master/modules/flowable-idm-engine/src/main/resources/org/flowable/idm/db/upgrade directory it appears that there were no changes in the IDM tables for 6.4.0 -> 6.4.1

You can try (this is untested) to apply the flowable.all.upgradestep.6412.to.6413.identity.sql file, which is just:

update ACT_ID_PROPERTY set VALUE_ = ‘6.4.1.3’ where NAME_ = ‘schema.version’;

and then restart. It should get you past that issue.

I’ll talk to our DB gurus and see about making a permanent fix.

Thank you for your quick answer dbmalkovsky

It’s working.