Deletion of study from database

[Deleted User]

Hi! I am wondering if deleting a study through the GUI thoroughly deletes the data from the H2 database? The database is surprisingly large, and am wondering if there are old studies on there. Is there a way to view the contents of the database via command line?

Many thanks!

kri

Good question. Although JATOS deletes all study asset files and everything in the DB (like study results, study data, batches), the H2 database itself might keep some kind of backup. I know from MySQL that they keep a history of all changes (and therefor all data) in their binary log. Maybe H2 does something similar.

You can always have a look at the content of your H2 - there are options: https://stackoverflow.com/a/2761825/1278769. But I doubt that they will show you anything useful, I mean nothing more then JATOS will show you.

I did a short Google Search and it showed me that H2 has something called 'Transaction Log' that might be the cause for your large H2 file size.

From http://www.h2database.com/html/features.html:

Logging and Recovery - Whenever data is modified in the database and those changes are committed, the changes are written to the transaction log (except for in-memory objects). The changes to the main data area itself are usually written later on, to optimize disk access. If there is a power failure, the main data area is not up-to-date, but because the changes are in the transaction log, the next time the database is opened, the changes are re-applied automatically.

Hope this helps. Causes the large file size a problem for you or are you just curious?

Best,

Kristian

[Deleted User]

Hi Kristian,

I apologize for the late follow-up. I ran a copy of my JATOS file locally, deleted all studies on the GUI, but never actually found jatos.mv.db to change size at all (remained 6.997 GB). This is interesting to me for two reasons:

• We accidentally deleted some data from our server, and we'd like to recover it if possible, if it's still on the database.
• In the future, I'd like to fully delete data for IRB reasons, and to free up space on our server.

I don't have nearly enough knowledge about databases/H2 to access the database via SSH or the local .db file. Any resources you could provide me for either of these steps? Thanks!

[Deleted User]

In fact, I used the first link you provided above to ultimately navigate the database via a local server. Like you suggested, the tables/columns in the database did not include any old data. After deleting all users/studies via the GUI, the database appeared completely empty, but remained at 7 GB. It was only after executing 'DROP TABLE COMPONENTRESULT' (despite the table appearing to be empty) that the size fell to 1 GB.

Not sure if this is related to the log file you mentioned, but either way I'd love to find a way to truly reduce size without needing to manually delete H2 data. Thanks :)

kri

It might be what is talked about in these SO questions/answers: https://stackoverflow.com/a/48146189/1278769 or https://stackoverflow.com/a/63564535/1278769. It might be that H2 deletes your data by filling it with empty space but doesn't actually shrink your database file. They talk about that a `SHUTDOWN COMPACT` in the H2 console can help - have you ever tried it?

And for a bigger JATOS system I recommend to use MySQL. There one can turn off the history (https://www.jatos.org/JATOS-with-MySQL.html#optional-deactivate-mysqls-binary-log).

And for everyone else reading this: I have to warn everyone to delete tables from a database used by JATOS. JATOS will most likely not start again next time (unless you fix it manually). JATOS handles table creation and changes by itself and doesn't like manual changes.