Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Supported by

Deletion of study from database

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!

Comments

  • 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

  • edited March 21

    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!

  • edited March 22

    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 :)

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

Sign In or Register to comment.

agen judi bola , sportbook, casino, togel, number game, singapore, tangkas, basket, slot, poker, dominoqq, agen bola. Semua permainan bisa dimainkan hanya dengan 1 ID. minimal deposit 50.000 ,- bonus cashback hingga 10% , diskon togel hingga 66% bisa bermain di android dan IOS kapanpun dan dimana pun. poker , bandarq , aduq, domino qq , dominobet. Semua permainan bisa dimainkan hanya dengan 1 ID. minimal deposit 10.000 ,- bonus turnover 0.5% dan bonus referral 20%. Bonus - bonus yang dihadirkan bisa terbilang cukup tinggi dan memuaskan, anda hanya perlu memasang pada situs yang memberikan bursa pasaran terbaik yaitu http://45.77.173.118/ Bola168. Situs penyedia segala jenis permainan poker online kini semakin banyak ditemukan di Internet, salah satunya TahunQQ merupakan situs Agen Judi Domino66 Dan BandarQ Terpercaya yang mampu memberikan banyak provit bagi bettornya. Permainan Yang Di Sediakan Dewi365 Juga sangat banyak Dan menarik dan Peluang untuk memenangkan Taruhan Judi online ini juga sangat mudah . Mainkan Segera Taruhan Sportbook anda bersama Agen Judi Bola Bersama Dewi365 Kemenangan Anda Berapa pun akan Terbayarkan. Tersedia 9 macam permainan seru yang bisa kamu mainkan hanya di dalam 1 ID saja. Permainan seru yang tersedia seperti Poker, Domino QQ Dan juga BandarQ Online. Semuanya tersedia lengkap hanya di ABGQQ. Situs ABGQQ sangat mudah dimenangkan, kamu juga akan mendapatkan mega bonus dan setiap pemain berhak mendapatkan cashback mingguan. ABGQQ juga telah diakui sebagai Bandar Domino Online yang menjamin sistem FAIR PLAY disetiap permainan yang bisa dimainkan dengan deposit minimal hanya Rp.25.000. DEWI365 adalah Bandar Judi Bola Terpercaya & resmi dan terpercaya di indonesia. Situs judi bola ini menyediakan fasilitas bagi anda untuk dapat bermain memainkan permainan judi bola. Didalam situs ini memiliki berbagai permainan taruhan bola terlengkap seperti Sbobet, yang membuat DEWI365 menjadi situs judi bola terbaik dan terpercaya di Indonesia. Tentunya sebagai situs yang bertugas sebagai Bandar Poker Online pastinya akan berusaha untuk menjaga semua informasi dan keamanan yang terdapat di POKERQQ13. Kotakqq adalah situs Judi Poker Online Terpercayayang menyediakan 9 jenis permainan sakong online, dominoqq, domino99, bandarq, bandar ceme, aduq, poker online, bandar poker, balak66, perang baccarat, dan capsa susun. Dengan minimal deposit withdraw 15.000 Anda sudah bisa memainkan semua permaina pkv games di situs kami. Jackpot besar,Win rate tinggi, Fair play, PKV Games