Databases
StackOverflow2013
Postsdbo
POHSQLDB index inconsistency
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHSQLDB index inconsistency
    primarykey
    data
    text
    <p>I've encountered a strange behaviour of HSQLDB in our java project. Very simple queries returned inconsistent results. I traced the problem to indices of some tables being not up to date. Unfortunately I'm not at all an expert neither in SQL nor dealing with databases from java.</p> <p>The HSQLDB version is 2.2.8. I tried to update to 2.2.9 but that didn't help. </p> <p>Our database is very simple. It contains several independent tables. There are no relations between them. Each table is equipped with indices, one of them unique index combining several columns. Here is an example:</p> <pre><code>CREATE CACHED TABLE PUBLIC.MYTABLE( A BIGINT DEFAULT -9999 NOT NULL, B BIGINT DEFAULT -9999 NOT NULL, C NUMERIC(12,7) DEFAULT -9999.0000000 NOT NULL, D INTEGER DEFAULT -9999 NOT NULL); CREATE INDEX IDX1 ON PUBLIC.MYTABLE( A ); CREATE INDEX IDX2 ON PUBLIC.MYTABLE( B ); CREATE INDEX IDX3 ON PUBLIC.MYTABLE( C ); CREATE UNIQUE INDEX MYTABLE_PRIMARY_KEY ON PUBLIC.MYTABLE( A, B ); </code></pre> <p>We usually insert rows and query tables from java program and never update existing rows. Insert operation looks something like this:</p> <pre><code>Connection con = ...; // get connection from PreparedStatement stmt; con.setAutoCommit( false ); stmt = this.con.prepareStatement( String.format( "insert into %s values (?,?,?,?)", "MYTABLE")); stmt.setLong(1, data1); stmt.setLong(2, data2); stmt.setDouble(3, data3); stmt.setInt(4, data4); stmt.addBatch() </code></pre> <p>and somewhere else we execute the batch</p> <pre><code>stmt.executeBatch(); stmt.clearBatch(); con.commit(); </code></pre> <p>The code is executed in loop, after which we close statement and connection with appropriate methods. Also the connection url has "shutdown=true" at the end, so we expected the database to shutdown correctly.</p> <p>As I mentioned earlier, I noticed the problem when SQL queries such as</p> <pre><code>SELECT COUNT(*) FROM MYTABLE </code></pre> <p>began returning unreasonable results, such as it returned only about half of the expected number of rows. As an experiment I deleted the unique index from the table and all the queries worked correctly. This is why I think that for some reason the index is not properly updated on insert. The clue to look into index came from googling and reading comments on message boards and forums. </p> <p>As described in <a href="https://stackoverflow.com/a/12569539/1703316">Disable and rebuild an index in HSQLDB</a> I used </p> <pre><code>SHUTDOWN COMPACT </code></pre> <p>and that healed all tables in our database, all the data is there and all queries return correct results. I suspect that the author of that question had issue similar to mine. </p> <p>Ok, my data is safe, so I want to understand and correct the source of the problem. </p> <p>Is index supposed to be updated on insert? Or this is a false assumption and we need to execute SHUTDOWN COMPACT every time we update the database?</p> <p>Is it possible that a unique index over multiple columns is the source of the problem?</p> <p>I would appreciate any suggestions or directions. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USstys
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHSQLDB index inconsistency
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload