Database Schema Name is not properly applied to SQL statements

Description

I have Activiti tables deployed in H2 database with specified schema named 'ENGINE'. I am not using additional table prefix. Engine configuration has property set "databaseSchema" = "ENGINE" accordingly.

When deploying a process I get SQL exception indicating table cannot be found, because he internal SQL statement does not reflect the schema name.
SQL statement: select * from ACT_GE_PROPERTY where NAME_ = ? [42102-189]

As a work-around we set additional property "databaseTablePrefix" = "ENGINE." to force schema name be applied to all tables, but still get SQL exception in inner joins.

Please make sure schema name is applied to ALL sql statements. This should be independent from table-prefix setting, so all table names are resolved to {schema}.{prefix}table

Environment

H2 in-memory database, but possibly affects other databases too.

Activity

Show:
Joram Barrez
May 11, 2016, 12:32 PM

I can see why it's confusing, but hence why the tablePrefixIsSchema was introduced I assume (see my previous comment).

As a matter of fact, the schema is never used in any of the queries, see for example https://github.com/Activiti/Activiti/blob/master/modules/activiti-engine/src/main/resources/org/activiti/db/mapping/entity/Property.xml, only the prefix is added there.

To do it as you propose would mean all sql statements would need to be revised, which is a serious amount of work (plus it's possible to add the schema to the jdbc url as noted above, which is (as I tested these two) possible for at least postgres and mysql ).

Andrey Nikolov
May 12, 2016, 12:03 AM

Thanks for looking at this issue!

I was not aware of the parameter 'tablePrefixIsSchema' perhaps since it was introduced later on in attempt to legalize the repurpose of {prefix} to designate {schema}. No surprise all this is causing confusion to other users as well (https://forums.activiti.org/content/how-set-database-table-prefix-when-using-spring-boot)

One reason you are not experiencing the problem is that you are setting SET SCHEMA in the URL itself instead of during SpringProcessEngineConfiguration bean creation. Setting it in the URL does not work for me because we have two schemas (ENGINE and SERVICE) for the same URL connection.

Why don't we solve the confusion in the following way:

1. Remove the command SET SCHEMA from JDBC URL in your configuration. This will help expose the issue.
2. If property 'databaseSchema' is set, run SET SCHEMA when initializing the engine. This should allow proper operation of the current SQL statements which refer to {prefix}table.
3. If property 'databaseTablePrefix' is set, append to the table name to get actual name as currently implemented. Do not accept property to contain 'dot' since this leads to abusing 'prefix' to designate 'schema'.
4. Now that you have 'prefix' and 'schema' completely decoupled and independent from each another, phase out 'tablePrefixIsSchema' property.
5. Things should be good so far... but ultimately refactoring all SQL statements to refer to {schema}.{prefix}table instead of {prefix}table will allow to remove the dependency on SET SCHEMA pre-initialization command in (2).

Really hope to see this sorted out in 5.21 or 6.0 since we are designing a system for multiple database environments and any DB specific tweaks are pain to deal with.

Andrey Nikolov
May 12, 2016, 1:25 AM
Edited

Simply stated, make the engine data source initialization part of the engine boot-up configuration.

If 'databaseSchema' property is set, run CREATE SCHEMA IF NOT EXISTS {schema} ; SET SCHEMA {schema} ; during the bean construction.

This should resolve the problem while keeping existing SQL statements intact.

Joram Barrez
May 12, 2016, 6:55 AM

> Why don't we solve the confusion in the following way:

We can't stop accepting the dot suddenly, because that would break many people's install. Backwards compatibility has always been key to Activiti.

https://forums.activiti.org/content/how-set-database-table-prefix-when-using-spring-boot is not related to this issue, this forum post is about how to set the property in Spring Boot environment (which is done in a Spring Boot specific way but was not exposed)

> Really hope to see this sorted out in 5.21 or 6.0 since we are designing a system for multiple database environments and any DB specific tweaks are pain to deal with.

If it's that important to you, feel free to provide a Pull Request Currently, this is not a high priority issue for is, finishing the v6 final is.

Andrey Nikolov
May 12, 2016, 9:42 PM

So, would it be feasible to move the database initialization from URL into the engine configuration? This is where it belongs since engine usage afterwards depends on CREATE SCHEMA + SET SCHEMA pre-requisites.

No change in SQL statements, and prefix = schema + dot notation would still work, so backwards compatibility is preserved.

Thanks for your consideration!

Assignee

Joram Barrez

Reporter

Andrey Nikolov

Labels

None

Components

Affects versions

Priority

High
Configure