Unique index or primary key violation in FullText indexes tables (error 23001)

[lvr123]

Hello,

I get a org.h2.jdbc.JdbcSQLException: Unique index or primary key
violation: CONSTRAINT_INDEX_4 ON FT.WORDS(NAME); when I update the
value of a fulltext indexed field.

It's the second time I get this. The first time, I just dropped the db
and recreate it.
This I'm willing to grasp what I did wrong and if possible just drop
and recreate the index.

Can somebody tell what's going on ?
Regards,

Laurent

org.h2.jdbc.JdbcSQLException: Unique index or primary key violation:
CONSTRAINT_INDEX_4 ON FT.WORDS(NAME); SQL statement:
INSERT INTO FT.WORDS(NAME) VALUES(?) [23001-118]
at org.h2.message.Message.getSQLException(Message.java:105)
at org.h2.message.Message.getSQLException(Message.java:116)
at org.h2.message.Message.getSQLException(Message.java:75)
at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:
154)
at org.h2.index.BtreeLeaf.add(BtreeLeaf.java:65)
at org.h2.index.BtreeIndex.add(BtreeIndex.java:228)
at org.h2.table.TableData.addRow(TableData.java:125)
at org.h2.command.dml.Insert.update(Insert.java:100)
at org.h2.command.CommandContainer.update(CommandContainer.java:72)
at org.h2.command.Command.executeUpdate(Command.java:208)
at org.h2.jdbc.JdbcPreparedStatement.execute
(JdbcPreparedStatement.java:176)
at org.h2.fulltext.FullText$FullTextTrigger.getWordIds(FullText.java:
891)
at org.h2.fulltext.FullText$FullTextTrigger.insert(FullText.java:846)
at org.h2.fulltext.FullText$FullTextTrigger.fire(FullText.java:809)
at org.h2.schema.TriggerObject.fireRow(TriggerObject.java:184)
at org.h2.table.Table.fireRow(Table.java:794)
at org.h2.table.Table.fireAfterRow(Table.java:787)
at org.h2.command.dml.Update.update(Update.java:137)
at org.h2.command.CommandContainer.update(CommandContainer.java:72)
at org.h2.command.Command.executeUpdate(Command.java:208)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal
(JdbcPreparedStatement.java:139)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate
(JdbcPreparedStatement.java:128)

[Kerry Sainsbury]

Could it be that you have a unique index on the column, and you've attempted to insert the same value twice...?

It would help if you included the definition of your table and its indexes.

Cheers
Kerry

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[lvr123]

On Dec 2, 8:05 pm, Kerry Sainsbury <ke...@fidelma.com> wrote:
> Could it be that you have a unique index on the column, and you've attempted
> to insert the same value twice...?

Actually, I read something like this in an early topic, and it was not
possible to build a test case. And this is also the second time it
arises.
Because the problem is not in one of my indexes but in one of the
fulltext FT. schema indexes, in which tables and indexes are managed
by the H2 engine.

My opinion is that this is a bug in H2.

I managed to "solve" the problem by deleting all records in the table
FT.WORDS. This is not a sound manipulation. Before I deleted all, I
had 4 entries in that table with ids 1,2,3,5. I don't how the fulltext
engine works. Is that normal that I had that gap in the ids of the
FT.WORDS table ?

Laurent

>
> It would help if you included the definition of your table and its indexes.

-- Initialisation des capacités FullText
CREATE ALIAS IF NOT EXISTS FT_INIT FOR
"org.h2.fulltext.FullText.init";
CALL FT_INIT();

-- Création des tables
CREATE TABLE ENTRY (ID INT NOT NULL IDENTITY,URL VARCHAR(750) NOT
NULL,PATH VARCHAR(500) NOT NULL,FILENAME VARCHAR(250) NOT NULL,RATING
CHAR(2) NOT NULL,SAMPLERATE FLOAT(6) NOT NULL,BITRATE INT NOT
NULL,DESCRIPTION VARCHAR(500),IDTYPE INT,ALLTAGS VARCHAR(2000) NOT
NULL DEFAULT '') ;
CREATE TABLE ENTRYTYPE (ID INT NOT NULL,LABEL VARCHAR(50) NOT NULL) ;
ALTER TABLE ENTRYTYPE ADD CONSTRAINT ENE_PK PRIMARY KEY(ID);
ALTER TABLE ENTRY ADD CONSTRAINT ENY_UK UNIQUE(URL);
ALTER TABLE ENTRY ADD CONSTRAINT ENY_ENE_FK FOREIGN KEY (IDTYPE)
REFERENCES ENTRYTYPE(ID);

-- Les index FullTExt
CALL FT_CREATE_INDEX('PUBLIC', 'ENTRY', 'ALLTAGS');

-- Les types de base
insert into entrytype (id,label) values (1,'RAW');
insert into entrytype (id,label) values (2,'CLEANED');
insert into entrytype (id,label) values (3,'SYNTHESE');
insert into entrytype (id,label) values (4,'MIX');
insert into entrytype (id,label) values (5,'STOMPER');

[lvr123]

Has anybody had a chance to look at this ?
I deleted all the FT.WORDS entries. This solved the problem only for a
couple of hours. The problem is back by now.
In the meantime, I recreated my database without the fulltext
indexing, which was the main reason I went to H2 in place of HSQLDB.

If nobody knows the solution to this, then is there an SQL statement
to remove and recreate a Fulltext Index, or to rebuild it ? So that if
I get the error again, I could in my application rebuild the index,
with the hopes that I solves the problem.

Regards,

Laurent

[Thomas Mueller]

Hi,

This sounds like a bug in the fulltext search. It looks like a
concurrency problem. I think I know where the problem is and how to
fix it, but I like to verify it really is concurrency.

What is your database URL? Do you use multiple threads / connections
to insert or update data?

Regards,
Thomas

[lvr123]

The Url is file:/D:/work/ZamplesExplorer/Default;IFEXISTS=TRUE
And this is a single use application and most of these calls have not
been optimized yet are done from the EventDispatchThread.

As far as i remember everything worked fine until I did some action
which triggered the error for all futur calls.
The error was a badly handled attempt to insert a value which violated
a Unique key. Not a in table directly related to table which has the
fulltext index.

Is there something a can look at in the DB (I put it aside) or in the
logs to help ?

Regards,

Laurent


On Dec 5, 11:00 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> This sounds like a bug in the fulltext search. It looks like a
> concurrency problem. I think I know where the problem is and how to
> fix it, but I like to verify it really is concurrency.
>
> What is your database URL? Do you use multiple threads / connections
> to insert or update data?
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

> triggered the error for all futur calls.
> The error was a badly handled attempt to insert a value which violated
> a Unique key. Not a in table directly related to table which has the
> fulltext index.
>
> Is there something a can look at in the DB (I put it aside) or in the
> logs to help ?

Maybe... Could you send me the .trace.db file if it contains any
exceptions? Could you even send me the database?

Regards,
Thomas

[Thomas Mueller]

Hi,

Thanks for your help! I can now reproduce the problem. The problem is
that currently, before inserting or deleting data, FT_INIT() has to be
called (only for the native fulltext search; not for the Lucene
search).

That's a bug of course, I will fix it for the next release.

Regards,
Thomas

[Brett Ryan]

Hi Thomas, I see this seems to be a problem with 1.4.192. I don't know the exact steps to reproduce, however; what I am doing is dropping all objects then loading using runscript.

I rebuild using the following:

    drop all objects;

    runscript from '/Users/bryan/Development/db/backup/local_mmm.sql.gz' compression gzip;

    CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init";

    CALL FT_INIT();

    CALL FT_DROP_INDEX('PUBLIC', 'CARDSADDRESS');

    CALL FT_CREATE_INDEX('PUBLIC', 'CARDSADDRESS', null);

 At the point before FT_INIT() no schema for FT exists. After FT_INIT FT schema and tables exist with no content. FT_DROP_INDEX was used just to see if it would help, it didn't.

Once I run FT_CREATE_INDEX I get:

Error code 23505, SQL state 23505: Unique index or primary key violation: "PRIMARY_KEY_1 ON FT.MAP(WORDID, ROWID) VALUES (1, 57336, 727241)"; SQL statement:

INSERT INTO FT.MAP(ROWID, WORDID) VALUES(?, ?) [23505-192]

No trace information is present from the DB.

Note, I have successfully created this index in the past.

[Brett Ryan]

Note that I am using server mode (the NetBeans IDE plugin). I just shut down the database, completely deleted the files, then successfully executed the above.