JWPL creating indexes every time Java code is run

[Mark Sengupta]

I know it is supposed to do this the first time we connect to a fresh imported DB, but in my case every time  I restart my java code
it does the same thing. However, it takes constant amount of time for the queries that I am making.
I mean if I am searching for "Japan" it would take 300 secs but if I searched for both "Japan" and "Olympics" it would take ~300 secs.

Is this behavior normal? or am I missing something? Thanks in advance

[Torsten Zesch]

300s for a query sounds like the indexes are not created at all.
300ms would sound more reasonable.

Could you look into the database properties whether the indexes are
written at all?
Are there any error messages indicating that the indexing is not
working properly?

-Torsten

> --
> You received this message because you are subscribed to the Google Groups
> "jwpl-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jwpl+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Mark Sengupta]

Thanks for your quick response. Could you please let me know where I should look at?

I am pretty new to mysql. Thanks again.

You received this message because you are subscribed to a topic in the Google Groups "jwpl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jwpl/UcWDJ2FkZCE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jwpl+uns...@googlegroups.com.

[Torsten Zesch]

Sorry, I cannot really help with this one, as this has little to do
with JWPL and mostly with Mysql.

The easiest way is probably to login into the mysql shell and then say
"describe TABLENAME"

-Torsten

[Mark Sengupta]

Sure. Not a problem. I didn't find any issue in the desc tablename command.

However, is there any way to create those indexes again?

[Torsten Zesch]

You can manually create the indexes (given that you have the rights to
do so - if you don't have them this might also be a problem for JWPL).
At startup, the Hibernate framework within JWPL shows which indexes it
tries to create. You can do the same at the mysql console with some
more direct feedback whether it worked.

-Torsten

[Mark Sengupta]

Thanks. I'll update you with the status soon :-)

[Mark Sengupta]

Hello, This is what I did:

1. I checked that I have all rights to the mysql database
2. I dropped all the tables and re-imported the data. Ran the code and at the first step from SQL prompt (using command: show processlist;) it said the following: STATUS: copy to tmp table |  INFO: create index nameIndex on Page (name)
3. The program produced an output which is great!
4. Ran the same program again: this time show processlist gave the following: STATUS: copy to tmp table | INFO: alter table page_inlinks add index FK91C2BC04F9E0A429 (id), add constraint FK91C2BC04F9E0A429 foreig 
5. Again show processlist gave the following message (this takes about 300 secs remember):  STATUS: Repair by sorting | INFO: alter table page_inlinks add index FK91C2BC04F9E0A429 (id), add constraint FK91C2BC04F9E0A429 foreig
6. Again did a show processlist and got: Repair by sorting | alter table page_outlinks add index FK95F640DBF9E0A429 (id), add constraint FK95F640DBF9E0A429 forei
   
7. Step 4-6 took a long time (same as Step 2) which is the first time.
8. And again the output is produced after 300ish seconds.
   
9. Next time when you run the code, the process repeats and takes the same amount of time to generate the output.

Any direction/advice would be really helpful. I am using version 0.9.2 and the latest wiki dump. Thanks :-)

[Torsten Zesch]

Thanks for investigating.
I still cannot really track down where the problem could be.
Hibernate should not re-create indexes once they are created.

1) Could you please verify again that the indexes are indeed created
and present once you are over the 300s indexing step.
2) If this is the case and hibernate keeps re-creating indexes, could
you try to manually add the indexes

-Torsten

[Oliver Ferschke]

Hi,

unfortunately, I also don't have an immediate solution.

Did you use the 0.9.2 from a jar or directly from the Maven repository?

What's the version of your MySQL server?

Is it possible for you to check JWPLs behavior with a read-only account to the database?

It would be interesting to see if it produces the expected results while being faster (because it can't write).

Of course, the initial index creation should still be performed with a full access account.

Regards,

Oliver

-- 
-------------------------------------------------------------------
Oliver Ferschke, M.A.
Research Scientist
Ubiquitous Knowledge Processing Lab
Information Center for Education
German Institute for International Educational Research
Solmsstraße 75, D-60486 Frankfurt am Main, Germany

www.ukp.tu-darmstadt.de | www.dipf.de
-------------------------------------------------------------------

---

Von: jw...@googlegroups.com [jw...@googlegroups.com]" im Auftrag von "Mark Sengupta [markse...@gmail.com]
Gesendet: Donnerstag, 12. Juni 2014 02:13
An: jw...@googlegroups.com
Betreff: Re: [jwpl-users] JWPL creating indexes every time Java code is run

[Mark Sengupta]

Thanks to both of you for your quick responses.

I think we have found a solution to the problem: a read-only user with just select privileges worked just fine and the

results (which obviously looks good) are produced pretty fast. Every time my user with all privileges are trying to

run the java code, it tries to create the indexes again even when they are present (I checked this). I am using the 0.9.2 jar and not
from the Maven repository. The Wikipedia version is the latest stable English dump with about ~4.3M entities.

Thanks again and let's see if I run across any other issues. So far it works great :-)

[Torsten Zesch]

Thanks for sharing your solution.

So far it always worked for me also when using a user with write
privileges - strange.
We will monitor if the same happens for other JWPL users too.

-Torsten

[Oliver Ferschke]

It definitely should work for users with write permission. I also never had a problem with that - I always work on databases with write permission.
Maybe some other reader of this list has an idea ... ?

-Oliver

-----Ursprüngliche Nachricht-----
Von: jw...@googlegroups.com [mailto:jw...@googlegroups.com] Im Auftrag von Torsten Zesch
Gesendet: Donnerstag, 12. Juni 2014 20:52

[Oliver Ferschke]

Now I'm getting the same issues as Mark.

With a write-permission user, JWPL always tries to update foreign key constraints, which takes a long time.

Even if I let it finish, it does the same thing next time.

With a readonly user, I get

    org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000388: Unsuccessful: alter table ....

and it works again, but I guess something needs to be fixed there. 

-Oliver

>>>> >> >> > jwpl+unsubscribe@googlegroups.com.

>>>> >> >> > For more options, visit https://groups.google.com/d/optout.
>>>> >> >>
>>>> >> >> --
>>>> >> >> You received this message because you are subscribed to a
>>>> >> >> topic in the Google Groups "jwpl-users" group.
>>>> >> >> To unsubscribe from this topic, visit
>>>> >> >> https://groups.google.com/d/topic/jwpl/UcWDJ2FkZCE/unsubscribe.
>>>> >> >> To unsubscribe from this group and all its topics, send an
>>>> >> >> email to

>>>> >> >> jwpl+unsubscribe@googlegroups.com.

>>>> >> >>
>>>> >> >> For more options, visit https://groups.google.com/d/optout.
>>>> >> >
>>>> >> >
>>>> >> > --
>>>> >> > You received this message because you are subscribed to the
>>>> >> > Google Groups "jwpl-users" group.
>>>> >> > To unsubscribe from this group and stop receiving emails from

>>>> >> > it, send an email to jwpl+unsubscribe@googlegroups.com.

>>>> >> > For more options, visit https://groups.google.com/d/optout.
>>>> >>
>>>> >> --
>>>> >> You received this message because you are subscribed to a topic
>>>> >> in the Google Groups "jwpl-users" group.
>>>> >> To unsubscribe from this topic, visit
>>>> >> https://groups.google.com/d/topic/jwpl/UcWDJ2FkZCE/unsubscribe.
>>>> >> To unsubscribe from this group and all its topics, send an email
>>>> >> to

>>>> >> jwpl+unsubscribe@googlegroups.com.

>>>> >> For more options, visit https://groups.google.com/d/optout.
>>>> >
>>>> >
>>>> > --
>>>> > You received this message because you are subscribed to the
>>>> > Google Groups "jwpl-users" group.
>>>> > To unsubscribe from this group and stop receiving emails from it,

>>>> > send an email to jwpl+unsubscribe@googlegroups.com.

>>>> > For more options, visit https://groups.google.com/d/optout.
>>>>
>>>> --
>>>> You received this message because you are subscribed to a topic in
>>>> the Google Groups "jwpl-users" group.
>>>> To unsubscribe from this topic, visit
>>>> https://groups.google.com/d/topic/jwpl/UcWDJ2FkZCE/unsubscribe.
>>>> To unsubscribe from this group and all its topics, send an email to

>>>> jwpl+unsubscribe@googlegroups.com.

>>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>>
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "jwpl-users" group.
>> To unsubscribe from this group and stop receiving emails from it,

>> send an email to jwpl+unsubscribe@googlegroups.com.

>> For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> You received this message because you are subscribed to a topic in
>> the Google Groups "jwpl-users" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/jwpl/UcWDJ2FkZCE/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to

>> jwpl+unsubscribe@googlegroups.com.

>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "jwpl-users" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to jwpl+unsubscribe@googlegroups.com.

> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "jwpl-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jwpl+unsubscribe@googlegroups.com.

[Oliver Ferschke]

Changing         

        p.setProperty("hibernate.hbm2ddl.auto","update");

to

        p.setProperty("hibernate.hbm2ddl.auto","validate");

gets rid of this issue, but I think I remember vaguely that this will cause problems because it doesn't allow indexes to be created after initial import of the database.