full text search errors

[clint]

I seriously need this text search to work, but I am having strange
problems.

My SQL is fairly simple, the whole database is not very complex, but I
need it online very very soon.

The error I get is that FT_CREATE_INDEX is reported as "can't find
method":

------------

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'STORY', 'TITLE' );
Function "FT_CREATE_INDEX" not found; SQL statement:
CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'STORY', 'TITLE' ) [90022-140]
90022/90022 (Help)

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'AUTHOR', 'NAME');
Function "FT_CREATE_INDEX" not found; SQL statement:
CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'AUTHOR', 'NAME') [90022-140]
90022/90022 (Help)

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'TAG', 'NAME');
Function "FT_CREATE_INDEX" not found; SQL statement:
CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'TAG', 'NAME') [90022-140]
90022/90022 (Help)

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'PHOTOGRAPHER', 'NAME');
Function "FT_CREATE_INDEX" not found; SQL statement:
CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'PHOTOGRAPHER', 'NAME')
[90022-140] 90022/90022 (Help)

-------

the part I can't figure out is that this works just fine on my intel
macintosh powerbook. the above errors are on my friend's intel
macintosh mini. There is an O/S difference, I have 10.5, he has 10.6

is that o/s diff the cause?

--------

I have not tried the Lucene indexing yet. That is next.

otherwise, I am very happy with H2's abilities and performance. I've
been using it all year, multiple databases, some tiny, some with
millions of records.

-- clint

[Thomas Mueller]

Hi,

I don't think the problem is related to the operating system. Does
your application install the fulltext methods? This is done by
executing CREATE ALIAS FT_INIT ... and CALL FT_INIT(). This should be
documented I believe.

Regards,
Thomas

> --
> 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.
>
>

[clint]

yes, I've already done that...note that I said this works just fine on
my laptop.

lucene is worse...that doesn't work anywhere.


I am doing this by cutting and pasting the exact code from the
documentation...which I hoped was going to be ok...

-- clint



On Aug 10, 3:09 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> I don't think the problem is related to the operating system. Does
> your application install the fulltext methods? This is done by
> executing CREATE ALIAS FT_INIT ... and CALL FT_INIT(). This should be
> documented I believe.
>
> Regards,
> Thomas
>

[Kerry Sainsbury]

Hi Clint,

Maybe you could provide the specific steps you are following so we could perhaps reproduce the problem? What version of H2 are you using?

Also, what do you mean specifically by "lucene is worse...that doesn't work anywhere" -- do you have actual error messages?

Thanks
Kerry

[clint]

here's my .bat file to start the DB:
__________________

set JAVA_HOME=C:\Progra~2\Java\

set CLASSPATH=%JAVA_HOME%\lib\tools.jar;c:\h2\bin\h2-1.2.135.jar;c:
\h2\bin\lucene-core-3.0.2.jar

\progra~2\java\jre6\bin\java -cp "h2-1.2.135.jar;%H2DRIVERS%;%CLASSPATH
%" -Xincgc org.h2.tools.Server -tcp -tcpAllowOthers -web -
webAllowOthers

------------------------------

you can see I have the lucene jar on there, and I'm using H2 V135



here's my SQL: (note that the whole thing can be run over and over,
everything is cleared and recreated)

________________________

drop schema if exists magazine_index;
//this punts the full-text-search stuff
drop schema if exists FT;
drop schema if exists FTL;

CREATE SCHEMA magazine_index;

//this recreates FT
CREATE ALIAS IF NOT EXISTS FT_INIT FOR
"org.h2.fulltext.FullText.init";
CALL FT_INIT();

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
"org.h2.fulltext.FullTextLucene.init";
CALL FTL_INIT();

set schema magazine_index;

//do I need these table drops with the whole db drop above?
drop table issue_x_mag if exists;
drop table story_x_author if exists;
drop table story_x_photographer if exists;
drop table story_x_issue if exists;
drop table tag_x_story if exists;
drop table magazine if exists;
drop table tag if exists;
drop table issue if exists;
drop table author if exists;
drop table photographer if exists;
drop table story if exists;
drop table nmra_member if exists;



create table magazine (
mytime timestamp default current_timestamp(),
mag_id integer not null identity primary key,
name varchar(250)
);

//legitimate enterers of new data.
create table nmra_member (
mytime timestamp default current_timestamp(),
member_id integer not null identity primary key, //probably not
significant or valuable.
name varchar (200),
nmra_number integer, //verified with HQ.
entry_count integer default 0 //how many entries this person has
made.
);

create table issue (
mytime timestamp default current_timestamp(),
issue_id integer not null identity primary key,
mag_id integer,
// story foreign key (),
month varchar(30),
year integer,
constraint is1 foreign key (mag_id) references magazine(mag_id)
);

//needed?
create table issue_x_mag (
issue_id integer,
mag_id integer,
constraint im1 foreign key (issue_id) references issue
(issue_id),
constraint im2 foreign key (mag_id) references magazine
(mag_id)
);

create table author (
mytime timestamp default current_timestamp(),
auth_ID integer not null identity primary key,
name varchar(150)
);

create table story (
mytime timestamp default current_timestamp(),
story_id integer not null identity primary key,
title varchar(250),
page integer
);

create table photographer (
mytime timestamp default current_timestamp(),
photog_id integer not null identity primary key,
name varchar(150)
);

create table tag (
mytime timestamp default current_timestamp(),
tag_id integer not null identity primary key,
name varchar(30)
);

create table tag_x_story (
mytime timestamp default current_timestamp(),
tag_id integer,
story_id integer,
constraint ts1 foreign key (tag_id) references tag(tag_id),
constraint ts2 foreign key (story_id) references
story(story_id)
);

//because a story could have multiple authors
create table story_x_author (
auth_id integer,
story_id integer,
constraint sa1 foreign key (auth_ID) references author
(auth_ID),
constraint sa2 foreign key (story_id) references story
(story_id)
);

//because a story could have multiple photographers
create table story_x_photographer (
photog_id integer,
story_id integer,
constraint sp1 foreign key (photog_ID) references photographer
(photog_id),
constraint sp2 foreign key (story_id) references story
(story_id)
);

//because an issue has multiple stories
create table story_x_issue (
issue_id integer,
story_id integer,
constraint si1 foreign key (issue_ID) references issue
(issue_id),
constraint si2 foreign key (story_id) references story
(story_id)
);

create table errors (
mytime timestamp default current_timestamp(),
error_id integer not null identity primary key,
description varchar(2000)
);


create index story_x_author_index1 on story_x_author(auth_id);
create index story_x_author_index2 on story_x_author(story_id);
create index story_x_issue_index1 on story_x_issue(story_id);
create index story_x_issue_index2 on story_x_issue(issue_id);

create index story_index on story(story_id);
create index author_index on author(auth_id);


//do these have to run AFTER putting some data in? and then run again
and again?

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'STORY', 'TITLE' );

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'AUTHOR', 'NAME');

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'TAG', 'NAME');

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'PHOTOGRAPHER', 'NAME');

CALL FTL_CREATE_INDEX('MAGAZINE_INDEX', 'STORY', 'TITLE' );
CALL FTL_CREATE_INDEX('MAGAZINE_INDEX', 'AUTHOR', 'NAME');
CALL FTL_CREATE_INDEX('MAGAZINE_INDEX', 'TAG', 'NAME');
CALL FTL_CREATE_INDEX('MAGAZINE_INDEX', 'PHOTOGRAPHER', 'NAME');


insert into magazine(name) values ('Model Railroader');
insert into magazine(name) values ('1:87 Scale');
insert into magazine(name) values ('30 Guild');
insert into magazine(name) values ('On30 Interchange');
insert into magazine(name) values ('Big Train Operator');
insert into magazine(name) values ('Brass Browser');
insert into magazine(name) values ('Brass Buyers Guide');
insert into magazine(name) values ('Brass Modeler and Collector');
insert into magazine(name) values ('Canadian Railway Modeler');
insert into magazine(name) values ('Classic Toy Trains');
insert into magazine(name) values ('Garden Railways');
insert into magazine(name) values ('Live Steam');
insert into magazine(name) values ('Grand Scales Quarterly');
insert into magazine(name) values ('Greater Tinplater');
insert into magazine(name) values ('Great Model Railroads');
insert into magazine(name) values ('HO Model Trains');
insert into magazine(name) values ('Model Trains');
insert into magazine(name) values ('Clear Block');
insert into magazine(name) values ('Colorado Narrow Gauge Quarterly');
insert into magazine(name) values ('Dispatchers Office');
insert into magazine(name) values ('Finelines');
insert into magazine(name) values ('Finescale Railroader');
insert into magazine(name) values ('Outdoor RR');
insert into magazine(name) values ('Inside Track');
insert into magazine(name) values ('LGB Telegram');
insert into magazine(name) values ('Light Iron Digest');
insert into magazine(name) values ('Lion Roars');
insert into magazine(name) values ('Maine 2-foot Quarterly');
insert into magazine(name) values ('Mainline Modeler');
insert into magazine(name) values ('Miniature Locomotive');
insert into magazine(name) values ('Miniature Railroading');
insert into magazine(name) values ('Model Builder');
insert into magazine(name) values ('Modelmaker Magazine');
insert into magazine(name) values ('Model Rail News');
insert into magazine(name) values ('Model Railroad Planning');
insert into magazine(name) values ('Model Railroad Collector');
insert into magazine(name) values ('Model Railroading');
insert into magazine(name) values ('1001 Model Railroading Ideas');
insert into magazine(name) values ('Great World of Model
Railroading');
insert into magazine(name) values ('Modeltec');
insert into magazine(name) values ('Model Transport');
insert into magazine(name) values ('MRCS Dispatch');
insert into magazine(name) values ('N Scale');
insert into magazine(name) values ('N Scale Railroading');
insert into magazine(name) values ('N-TRAK Newsletter');
insert into magazine(name) values ('Narrow Gauge and Short Line
Gazette');
insert into magazine(name) values ('NASG Dispatch');
insert into magazine(name) values ('NER Coupler');
insert into magazine(name) values ('North American Live Steamer');
insert into magazine(name) values ('NMRA Bulletin');
insert into magazine(name) values ('Scale Rails');
insert into magazine(name) values ('O Gager');
insert into magazine(name) values ('O Gauge Modeler');
insert into magazine(name) values ('O Scale');
insert into magazine(name) values ('O Gauge Railroading');
insert into magazine(name) values ('O Scale News');
insert into magazine(name) values ('O Scale Trains');
insert into magazine(name) values ('Prototype Modeler');
insert into magazine(name) values ('Railmodel Journal');
insert into magazine(name) values ('Railmodel Magazine');
insert into magazine(name) values ('Railroad Model Craftsman');
insert into magazine(name) values ('Railroad Modeler');
insert into magazine(name) values ('S Gaugian');
insert into magazine(name) values ('Scale Coupler');
insert into magazine(name) values ('Scale Steam Pictorial');
insert into magazine(name) values ('Slim Gauge News');
insert into magazine(name) values ('Sn3 Modeler');
insert into magazine(name) values ('Steam in the Garden');
insert into magazine(name) values ('Toy Trains');
insert into magazine(name) values ('Western Prototype Modeler');
insert into magazine(name) values ('S Gauge Herald');
insert into magazine(name) values ('Whistle Stop');
insert into magazine(name) values ('O Gauge Modeler');
insert into magazine(name) values ('TTOS Bulletin');
insert into magazine(name) values ('Switcher');
insert into magazine(name) values ('Model Railroad Equipment Buyers
Guide');
insert into magazine(name) values ('Rail Line News');
insert into magazine(name) values ('Rail Protomodels');
insert into magazine(name) values ('S/Sn3 Buyers/Modeling Guide');
insert into magazine(name) values ('Toy and Train Album');
insert into magazine(name) values ('Toy and Model Trains');
insert into magazine(name) values ('Train Collectors Quarterly');
insert into magazine(name) values ('Delaware Valley/Atlantic Division
Express');
insert into magazine(name) values ('Turntable');
insert into magazine(name) values ('Yard Limits');





insert into story(title, page) values ('Test Story', 23);
insert into author (name) values ('Clint Hyde');
insert into issue(month, year, mag_id) values ('January', 1995, 1);

insert into author (name) values ('Bill Smith');
insert into story(title, page) values ('Review', 74);
insert into issue(month, year, mag_id) values ('August', 2004, 1);

insert into story_x_author(auth_id, story_id) values (1,1);
insert into story_x_author(auth_id, story_id) values (2,2);
insert into story_x_issue(issue_id, story_id) values (1,1);
insert into story_x_issue(issue_id, story_id) values (2,2);
insert into issue_x_mag(issue_id, mag_id) values (1,1);
insert into issue_x_mag(issue_id, mag_id) values (2,1);

select * from magazine;
select * from story;
select * from author;
select * from issue;
select * from issue_x_mag;
select * from story_x_issue;
select * from story_x_author;

select magazine.name, issue.month, issue.year, story.title,
author.name
from story, author, story_x_author, story_x_issue, issue,
issue_X_mag, magazine
where story_x_issue.story_id = story.story_id
and story_x_issue.issue_id = issue.issue_id
and story.story_id = story_x_author.story_id
and story_x_author.auth_id = author.auth_id
and issue_x_mag.issue_id = issue.issue_id
and issue_x_mag.mag_id = magazine.mag_id;


select * from ft_search_Data('clint', 0, 0);

______________________________

here's the first error msg from H2 [running the exact sql above!]
running on Win 7:

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
"org.h2.fulltext.FullTextLucene.init";
Update count: 0
(0 ms)

CALL FTL_INIT();
Exception calling user-defined function: "init(conn24:
url=jdbc:default:connection user=SA):
org.apache.lucene.index.IndexReader.indexExists(Ljava/lang/String;)Z";
SQL statement:
CALL FTL_INIT() [90105-135] 90105/90105 (Help)
org.h2.jdbc.JdbcSQLException: Exception calling user-defined function:
"init(conn24: url=jdbc:default:connection user=SA):
org.apache.lucene.index.IndexReader.indexExists(Ljava/lang/String;)Z";
SQL statement:
CALL FTL_INIT() [90105-135]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.message.DbException.convertInvocation(DbException.java:
295)
at org.h2.engine.FunctionAlias
$JavaMethod.getValue(FunctionAlias.java:395)
at org.h2.expression.JavaFunction.getValue(JavaFunction.java:36)
at org.h2.command.dml.Call.query(Call.java:61)
at org.h2.command.CommandContainer.query(CommandContainer.java:80)
at org.h2.command.Command.executeQuery(Command.java:132)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:275)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:134)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NoSuchMethodError:
org.apache.lucene.index.IndexReader.indexExists(Ljava/lang/String;)Z
at
org.h2.fulltext.FullTextLucene.getIndexModifier(FullTextLucene.java:
262)
at org.h2.fulltext.FullTextLucene.init(FullTextLucene.java:101)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.h2.engine.FunctionAlias
$JavaMethod.getValue(FunctionAlias.java:383)
... 7 more

at org.h2.engine.SessionRemote.done(SessionRemote.java:528)
at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:
158)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:
172)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebApp.getResult(WebApp.java:1300)
at org.h2.server.web.WebApp.query(WebApp.java:1001)
at org.h2.server.web.WebApp$1.next(WebApp.java:965)
at org.h2.server.web.WebApp$1.next(WebApp.java:968)
at org.h2.server.web.WebThread.process(WebThread.java:161)
at org.h2.server.web.WebThread.run(WebThread.java:88)
at java.lang.Thread.run(Unknown Source)

________________________


yesterday I rebooted my Win 7 PC. Now it appears that the internal
text search is going to work...but it still complains about lucene...

and no matter what, this seems not to run on my friend's server, which
is where I need it to end up, so that it's on the web.

-- clint

On Aug 11, 4:45 pm, Kerry Sainsbury <ke...@fidelma.com> wrote:
> Hi Clint,
>
> Maybe you could provide the specific steps you are following so we could
> perhaps reproduce the problem? What version of H2 are you using?
>
> Also, what do you mean specifically by "lucene is worse...that doesn't work
> anywhere" -- do you have actual error messages?
>
> Thanks
> Kerry
>

[clint]

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'STORY', 'TITLE' );

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'AUTHOR', 'NAME');

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'TAG', 'NAME');

CALL FT_CREATE_INDEX('MAGAZINE_INDEX', 'PHOTOGRAPHER', 'NAME');

> Hi Clint,
>
> Maybe you could provide the specific steps you are following so we could
> perhaps reproduce the problem? What version of H2 are you using?
>
> Also, what do you mean specifically by "lucene is worse...that doesn't work
> anywhere" -- do you have actual error messages?
>
> Thanks
> Kerry
>

[Kerry Sainsbury]

Hi Clint,

The error is caused by....

Caused by: java.lang.NoSuchMethodError:
org.apache.lucene.index.IndexReader.indexExists(Ljava/lang/String;)

I see you are using lucene 3.0.2, which has an IndexReader that doesn't have an indexExists(String method) anymore:

http://lucene.apache.org/java/3_0_2/api/core/org/apache/lucene/index/IndexReader.html

The 2.x flavours of lucene did have an indexExists(String) method...

http://lucene.apache.org/java/2_4_0/api/org/apache/lucene/index/IndexReader.html

... which inspired me to Google some more, and it looks like Lucene 3.0 is being actively worked on, but isn't available yet:  http://code.google.com/p/h2database/issues/detail?id=147

Try again with Lucene 2.9 maybe?

Hope this helps.

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.

[Prowler]

Hello Clint,

Please let me clarify a few things.

Kerry is correct in that your problem is caused by the use of Lucene
3.0.2 with H2 ,
since there have been many API changes in Lucene 3 that make it
incompatible with Lucene 2.x versions.

Nevertheless Lucene 3 IS ready for production/deployment. (We have
been using it for the past
6 months in production (outside H2) and is really reliable).

The problem is that H2 is not compatible with Lucene 3, which is an
issue already reported.
See: http://code.google.com/p/h2database/issues/detail?id=147

Your best bet for now is to go with a 2.9.x version as Kerry has
already suggested.

Just my 2c.

Best Regards,
Panayiotis Vlissidis

[clint]

roger that one, folks...I'm on it. Will see about getting the older
version and fire it up...

y'all are excellent. sure wish the documentation had said this...

-- clint

[clint]

roger that one, folks...I'm on it. Will see about getting the older
version and fire it up...

y'all are excellent. sure wish the documentation had said this...

-- clint



On Aug 13, 5:51 am, Prowler <pvlissi...@gmail.com> wrote:

[clint]

nope, still not getting there....the same problems still occur.

with lucene 2.9.3, FLT_INIT is ok, but the FTL_CREATE_INDEX fn is
still missing.

with lucene 2.4.1, FTL_INIT gets an error:

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
"org.h2.fulltext.FullTextLucene.init";
Update count: 0
(0 ms)

CALL FTL_INIT();

Error while indexing document [0-135] FULLTEXT/0 (Help)

-- clint


[this seems like something easy enough to regression test on the
nightly build...]

[sorry about double-postings, something network-y wrong on my machine]

[Thomas Mueller]

Hi,

I'm not sure if you read this: http://h2database.com/html/tutorial.html#fulltext

Could you try the example there and post the results?

> CALL FTL_INIT();
> Error while indexing document [0-135] FULLTEXT/0 (Help)

Could you post the complete error message and stack trace, including
all root causes please?

Regards,
Thomas

[clint]

I have read that tutorial stuff repeatedly...hoping I gain new
insight...

unfortunately, success or failure here is very inconsistent. sometimes
the sql works, sometimes it does not. it seems

----------------------- here's an error msg that is actually a little
bit more informative ---------------------------

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
"org.h2.fulltext.FullTextLucene.init";
Update count: 0
(0 ms)

CALL FTL_INIT();
Error while indexing document [0-135] FULLTEXT/0 (Help)

java.sql.SQLException: Error while indexing document
at
org.h2.fulltext.FullTextLucene.convertException(FullTextLucene.java:
220)
at org.h2.fulltext.FullTextLucene.init(FullTextLucene.java:103)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.h2.engine.FunctionAlias
$JavaMethod.getValue(FunctionAlias.java:383)

at org.h2.expression.JavaFunction.getValue(JavaFunction.java:36)
at org.h2.command.dml.Call.query(Call.java:61)
at org.h2.command.CommandContainer.query(CommandContainer.java:80)
at org.h2.command.Command.executeQuery(Command.java:132)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:275)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:134)
at java.lang.Thread.run(Unknown Source)

Caused by: java.sql.SQLException: Error while indexing document
at
org.h2.fulltext.FullTextLucene.convertException(FullTextLucene.java:
220)
at
org.h2.fulltext.FullTextLucene.getIndexModifier(FullTextLucene.java:
266)
at org.h2.fulltext.FullTextLucene.init(FullTextLucene.java:101)
... 12 more
Caused by: org.apache.lucene.store.LockObtainFailedException: Lock
obtain timed out: SimpleFSLock@C:\Users\Clinton Hyde\mag-db\write.lock
at org.apache.lucene.store.Lock.obtain(Lock.java:85)
at org.apache.lucene.index.IndexWriter.init(IndexWriter.java:1070)
at org.apache.lucene.index.IndexWriter.<init>(IndexWriter.java:
686)
at org.apache.lucene.index.IndexModifier.init(IndexModifier.java:
174)
at org.apache.lucene.index.IndexModifier.<init>(IndexModifier.java:
141)
at
org.h2.fulltext.FullTextLucene.getIndexModifier(FullTextLucene.java:
264)
... 13 more

at org.h2.engine.SessionRemote.done(SessionRemote.java:528)
at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:
158)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:
172)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebApp.getResult(WebApp.java:1300)
at org.h2.server.web.WebApp.query(WebApp.java:1001)
at org.h2.server.web.WebApp$1.next(WebApp.java:965)
at org.h2.server.web.WebApp$1.next(WebApp.java:968)
at org.h2.server.web.WebThread.process(WebThread.java:161)
at org.h2.server.web.WebThread.run(WebThread.java:88)
at java.lang.Thread.run(Unknown Source)

------------------------------------------------------------------------

note that there is a lucene failure there to get a write lock...that
at least makes some amount of sense, although why it wouldn't be able
to do that in my home-dir I don't know.

------------------------------------------------------------------------


so on my Win 7 pc, this HAS worked ok--but not always:

drop table test if exists;
drop schema ft if exists;
drop schema ftl if exists;

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

CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
"org.h2.fulltext.FullTextLucene.init";

CALL FTL_INIT();

CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR);
INSERT INTO TEST VALUES(1, 'Hello World');
CALL FT_CREATE_INDEX('PUBLIC', 'TEST', NULL);
SELECT * FROM FT_SEARCH('Hello', 0, 0);
SELECT * FROM FT_SEARCH_DATA('Hello', 0, 0);
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', NULL);
SELECT * FROM FTL_SEARCH_DATA('Hello', 0, 0);
________________________

I can still get a variety of errors, from INIT failures, to this
craziness:


CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR);

Update count: 0
(0 ms)

INSERT INTO TEST VALUES(1, 'Hello World');
Update count: 1
(0 ms)

CALL FT_CREATE_INDEX('PUBLIC', 'TEST', NULL);
Table "TEST" not found; SQL statement:
CREATE TRIGGER IF NOT EXISTS "PUBLIC"."FT_TEST" AFTER INSERT, UPDATE,
DELETE ON "PUBLIC"."TEST" FOR EACH ROW CALL "org.h2.fulltext.FullText
$FullTextTrigger" [42102-135] 42S02/42102 (Help)
org.h2.jdbc.JdbcSQLException: Table "TEST" not found; SQL statement:
CREATE TRIGGER IF NOT EXISTS "PUBLIC"."FT_TEST" AFTER INSERT, UPDATE,
DELETE ON "PUBLIC"."TEST" FOR EACH ROW CALL "org.h2.fulltext.FullText
$FullTextTrigger" [42102-135]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.schema.Schema.getTableOrView(Schema.java:405)
at org.h2.command.ddl.CreateTrigger.update(CreateTrigger.java:91)
at org.h2.command.CommandContainer.update(CommandContainer.java:
70)
at org.h2.command.Command.executeUpdate(Command.java:199)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:
176)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.fulltext.FullText.createOrDropTrigger(FullText.java:707)
at org.h2.fulltext.FullText.createTrigger(FullText.java:689)
at org.h2.fulltext.FullText.createIndex(FullText.java:162)

----------------------------------------------------------





HOWEVER: with MY SQL file, the FTL things continue to fail in the ways
I have reported...if I try to make a subset of my sql, I get even
stranger errors--I had to kill the server, delete the database files
themselves, and then it was ok.. There are still some residual errors
on your side about this..

what appears to have been necessary: the database (or FT/FTL indexes)
were corrupted early on, and that could not ever recover. Once I
actually deleted those files, things seem to work ok--but still not
always. Sometimes the same failure occur anyway. Inconsistent,
although the error is much likelier with Lucene.


related question: how do I get the lucene index cleared/empty? If I
run the above example more than once, lucene is reporting it more than
once. DROP SCHEMA works ok for FT, but seems not to affect FTL.

Later I will test this on my laptop, and then on my friend's server.

-- clint


On Aug 14, 12:53 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Kerry Sainsbury]

Does anybody have a recommended version of Lucene that Clint should use with H2?

It feels like lots of different versions have been tried at this point, so I'm not surprised if things have gotten a bit confused.

Cheers
Kerry

[clint]

well, NOW I'm embarrassed...

it turns out that (for reasons I've now forgotten), between when I
started doing this (with H2-129) and now (H2-140), I had to introduce
my own schema. I think because I'm going to work more than one tool
and index, and I need to keep those things separated easily.

Which means that at the top of my SQL, I have CREATE SCHEMA, and then
SET SCHEMA. which means that the rest of m SQL after the "USE" is in a
different namespace. Unfortunately, I had the INIT functions called
*before* the SET SCHEMA, which meant THEY were in the startup
namespace (which is guess is "PUBLIC"), so when I tried to call
FT_SEARCH_DATA, that function could not be found because of namespace
mismatch. [I was doing all this via the web-browser window]

So I'd get one set of errors. If I re-ran my sql in the browser
window, it appeared that "SET" was remembered, and then I'd get a
different set of errors. If I cleared things, restarted H2, deleted
the db files, I'd be back to the first errors. and there were other
variations...

if the error messages had said some of this, I'd have figured it out a
lot sooner...

so I think I have this figured out...

btw, this is with Lucene 2.9.3, which I guess I'm staying with for a
while...

I'll try this out on my PC this evening...

On Aug 15, 4:48 pm, Kerry Sainsbury <ke...@fidelma.com> wrote:
> Does anybody have a recommended version of Lucene that Clint should use with
> H2?
>
> It feels like lots of different versions have been tried at this point, so
> I'm not surprised if things have gotten a bit confused.
>
> Cheers
> Kerry
>

> > h2-database...@googlegroups.com<h2-database%2Bunsu...@googlegroups.com>
> > .

[Thomas Mueller]

Hi,

> Caused by: org.apache.lucene.store.LockObtainFailedException: Lock
> obtain timed out: SimpleFSLock@C:\Users\Clinton Hyde\mag-db\write.lock

It looks like the Lucene index is already open, or the write.lock is
not removed after killing the process.

> if the error messages had said some of this, I'd have figured it out a lot sooner...

So the problem was the object existed, but in a different schema? This
is not a very common problem. However I will still add a feature
request for "If a database object was not found in the current schema,
but one with the same name existed in another schema, included that in
the error message."

Regards,
Thomas

[clint]

This solution works good on my Win 7 PC, as well as my OSX 10.5 laptop


On Aug 17, 2:22 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > Caused by: org.apache.lucene.store.LockObtainFailedException: Lock
> > obtain timed out: SimpleFSLock@C:\Users\Clinton Hyde\mag-db\write.lock
>
> It looks like the Lucene index is already open, or the write.lock is
> not removed after killing the process.

or something like that (other process has it?)

>
> > if the error messages had said some of this, I'd have figured it out a lot sooner...
>
> So the problem was the object existed, but in a different schema?

that was exactly it. It was my fault that it happened that way, I
completely did not realize that was what was going on, it was mostly a
result of evolved, rather than designed, SQL, so there was some
ordering dependence that I was not recognizing--and the error msgs
were very confusing.

> This
> is not a very common problem. However I will still add a feature
> request for "If a database object was not found in the current schema,
> but one with the same name existed in another schema, included that in
> the error message."

that would have been very helpful for me :)

later today, or tomorrow, I will try this on my friend's server.


>
> Regards,
> Thomas

[clint]

I was successful with this today--s/w runs on the server just
fine...website is online and functional...not too pretty tho...well,
that's a diff issue.

Thanks for the help on stuff...now the next trick is to install it as
a daemon on OSX.

-- clint