Embeded Derby Database failures after run-away graph overloaded execution history

[[email protected]]

Hi,

We recently had an issue where an incorrectly configured event listener kicked off over 2million graph instance in just a few hours. Since that has happened, aside from very slow server response time, each time the "Delete Old Debug Files" Archivator is run the graph scheduler and graph listeners stop working.

This is what we see in the logs (full logs for each error are attached):

JBWEB000236: Servlet.service() for servlet Faces Servlet threw exception: java.lang.IllegalArgumentException: could not find dataTable with id 'tasksHistoryListTable'

JBWEB000236: Servlet.service() for servlet Faces Servlet threw exception: ERROR 40XL1: A lock could not be obtained within the time requested

JBWEB000236: Servlet.service() for servlet Faces Servlet threw exception: java.lang.NullPointerException


However when we reboot the server and start the application server the scheduler starts working again...

I have been attempting to run the "Delete old Execution Artifacts" job the clear the excess logs. It had been working to clear out about 30k at a time. But in the most recent run it is attempting to clear about 150k and has now been running for > 24 hours. Also, upon kicking off that instance we again saw the "A lock could not be obtained within the time requested" message in the logs even though the scheduler/listeners are sill working and the Delete Artifacts job still shows as "RUNNING"

So mainly my question is: Would their be an easier way to clear out these excess logs? What would the process be to totally reset the embedded Debry database and start fresh?

Thank you,

[cholastal]

Hi,

First I would like to clarify that the embedded Derby database is not a fully fledged database and should serve for basic testing purposes only, never for any kind of production. Even for advanced testing and/or development environments we recommend using a 3rd party, fully fledged SQL database.

However, since you're asking how to drop the database and start from scratch I assume this is some kind of testing instance and it is not crucial to preserve the data.
To your questions:

1. It would be possible to clear the artifacts manually, however, it would require you to do it precisely and consistently and it's quite tedious task if the another option is to drop the whole Derby database. If you want to do it this way, I will gather all the necessary steps for you, just let me know.

2. To drop the whole database you need to locate the temp folder which can be done by navigating into the CloverETL/CloverDX Server GUI -> Configuration -> System Info -> System Properties and search for the value of the "java.io.tmpdir" property. Navigate there on your filesystem and continue into the databases subfolder. Now you should see a folder called "cloverDb102", if you delete it, it will erase the whole database and will be created anew on the next CloverETL/CloverDX Server start. Also, please shut down the Server before you delete the folder.

Hope this helps.

[[email protected]]

Hi Lukas,

Thanks for the reply.

According to the service properties page on the Clover GUI the database should be contained under my /tmp directory. However I'm am only seeing clover log files stored there.

I do see a /databases/cloverdB102 folder within our /jboss-eap-6.3/bin directory. I assuming this it the correct directory?

[cholastal]

Hi,

It is rather unusual location but it is possible. I'm sorry for my mistake, the location of the database is not bound to the java.io.tmpdir variable but it is located in working directory by default and can be changed by a property. More information can be found in our documentation. I would suggest to create a backup of the directory (cloverDb102) and then delete it from there.

Also, it could be beneficial to utilize a "real" SQL database to prevent such an issue from happening in the future.

Hope this helps.