I have a distributed setup of two tomcat nodes with two hsql dbs. both DBs are connected to both tomcat nodes via HA-JDBC. I have a filter table with some columns using Blobs datatype.
When i deactivate and then again activate my one hsqldb instance i see some issue with LOB_IDS table in hsqldb. Earlier before deactivating hsql instance i had 4 entries in LOB_IDS table. Now after activating hsqldb i see 8 entries in LOB_IDS. It looks like ha-jdbc didnt' delete old LOB_IDS entries and because of syncing filter table 4 new entries got created in LOB_IDS table. But issue is i still see next_value for LOB_ID in information_schema.system_sequences table as 6 which is in sync with other active hsql db. This inconsistency in LOB_ID values between LOB_IDS table and LOB_ID value in information_schema.system_sequences table causes problem while creating new filter after syncing inactive database with active one.
Is this a bug in ha-jdbc or i need to use some specific configuration to fix this issue. I am using FullSynchronizationStrategy for DB syncing in HA-JDBC v2.0.16.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is a bug. During activation, HA-JDBC synchronizes sequences as well as user tables. Currently, for hsqldb, this includes all sequences listed in the INFORMATION_SCHEMA.SYSTEM_SEQUENCES table. Unfortunately, this table includes both user and system sequences. To avoid the problem above, HA-JDBC should skip sequences from non-user schemas.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I have a distributed setup of two tomcat nodes with two hsql dbs. both DBs are connected to both tomcat nodes via HA-JDBC. I have a filter table with some columns using Blobs datatype.
When i deactivate and then again activate my one hsqldb instance i see some issue with LOB_IDS table in hsqldb. Earlier before deactivating hsql instance i had 4 entries in LOB_IDS table. Now after activating hsqldb i see 8 entries in LOB_IDS. It looks like ha-jdbc didnt' delete old LOB_IDS entries and because of syncing filter table 4 new entries got created in LOB_IDS table. But issue is i still see next_value for LOB_ID in information_schema.system_sequences table as 6 which is in sync with other active hsql db. This inconsistency in LOB_ID values between LOB_IDS table and LOB_ID value in information_schema.system_sequences table causes problem while creating new filter after syncing inactive database with active one.
Is this a bug in ha-jdbc or i need to use some specific configuration to fix this issue. I am using FullSynchronizationStrategy for DB syncing in HA-JDBC v2.0.16.
This is a bug. During activation, HA-JDBC synchronizes sequences as well as user tables. Currently, for hsqldb, this includes all sequences listed in the INFORMATION_SCHEMA.SYSTEM_SEQUENCES table. Unfortunately, this table includes both user and system sequences. To avoid the problem above, HA-JDBC should skip sequences from non-user schemas.