Multiple Applications in the same DB

Support
#1

Hello,
I am using JMIX 1.5.4.
I want to create 3 applications which will be using the same DB as primary data source. I tried it and I get the following error:
ERROR: duplicate key value violates unique constraint "SEC_ROLE_ASSIGNMENT_pkey".
Probably I will get this error for every system table - key.
Can you please guide me to resolve my issue?

Thank you,
Ilias

1 Like
#2

H Ilias,

I’m mostly a DBA so hopefully someone can chime in with the Jmix side of accomplishing this technically. But typically I see this when the application layer wants to assign a primary key instead of letting the database do it automatically.

The error you are seeing is from the database connector returning the error that the primary key you are entering for an entry in one system conflicts with the unique value constraint on that column. For example the application is trying to assign a primary key value of “1029388” when another record already has that value as a primary key.

As a DBA I recommend fixing this by using UUID as my primary key, and having the database generate the UUID. But someone with more developer experience can probably speak to solving this another way if that solution is not possible.

Thank you,
Oran

#3

Hi Oran and thank you for your answer,

I know that the error was about duplicated key. I am trying to make multiple application use the same DB and schema and the default liquibase scripts are throwing this error, so I was hoping for a way to run the default scripts only in one of the applications.

I found a way to make it work by commenting out these lines in every application except the main one:

<!--    <include file="/io/jmix/securitydata/liquibase/changelog.xml"/>-->
<!--    <include file="/io/jmix/quartz/liquibase/changelog.xml"/>-->
<!--    <include file="/io/jmix/email/liquibase/changelog.xml"/>-->

Now I am trying to find a way of keeping different databasechangelog tables for each application, e.g. app1_databasechangelog , app2_databasechangelog, etc. .

#5

You would have to start with taking the UUID generation database side. So in the JPA file eliminate the line with @JmixGeneratedValue. This will prevent Jmix from generating the default uuid value (Note, I don’t know how this will impact Jmix, generally application layers have to manage this for things like offline persistence, but fully online applications don’t usually bother too much).

The next step I would take is creating a separate database user for each application to manage permission structures for the application (I know it is usually the same for each application, but experience has taught me the separate users makes debugging way simpler, just be careful of the table permissions structures).

    @JmixGeneratedValue
    @Column(name = "uuid", nullable = false)
    @Id
    private UUID id;

Now that the databse is directly managing the UUID creation there should be less collision checking because the database is doing the uuid generation. From there I’m at the end of my knowledge, but if you try and then post any future issues we can try and debug together from there.

Edit. I forgot to address the liquidbase changelog issue. Typically you address this be adding the database as a seperate database store, and them managing users through ldap instead of through the database. This is because liquidbase is more configurable when utilizing it as an additional data source. If this is not possible I’d recommend using one schema for the users and then a seperate schema for the data so that jmix recognizes it as seperate sources and the database can still manage cross schema data management.

Thank you,
Oran

#6

You can easily configure it for Liquibase started by the application, for example:

main.liquibase.database-change-log-table=SAMPLE_DBCHANGELOG
main.liquibase.database-change-log-lock-table=SAMPLE_DBCHANGELOGLOCK

But Studio currently ignores these properties, which will cause problems when updating the database both by Studio and by the application. I’ve created an issue for improvement: Use Liquibase properties specified for data store when running Liquibse : JST-4922

Perhaps you could define these properties only in the production environment by providing them on the command line or by defining in a separate profile-specific properties file.

Regards,
Konstantin