H2 database delete old positions / reduce size whilst retaining device config?

Hello, and thankyou Anton for such wonderful software.

I would like to reduce the size of the H2 database occasionally, by removing position reports over x days old.
I am running Traccar server 3.6 on Raspberry Pi 3 (Raspbian Jessie).

I have tried unsuccessfully to connect to the H2 database file with Traccar disabled, using H2 Console http://www.h2database.com/html/tutorial.html#tutorial_starting_h2_console under Linux. The H2console reports the database is open (using the same JDBC parameters as in the Traccar config file ) but cannot find any Traccar tables. I am shutting the Traccer service down before connecting.

My coding skills are not quite up to the standard, but I would like to create a button on the server web portal that would run SQL against the H2 database direct from the Traccar application to remove old positions. But I am willing to try, if anyone has some advice on how best I could start this? (which files to look/edit for example).

If anyone has advice on how I might achieve this - either with H2console or via a custom button on the web UI?

I know that in an ideal world I would migrate to MySQL or other database, but I feel the H2 database works well and is less resource intensive - I am running the Traccar server on Raspberry Pi 3 and 12v battery power supply for my project, and it is already running a host of other services (map tile server, Wifi DNS/DHCP/hostAP, HTTPserver, tracker receiver service, local display service!) so I am trying to avoid adding MySQL too.

Thankyou for any advice you may give.
Steve

This feature is already implemented and will be available with next release. You will be able to set number of days that you want to store history for.

Thankyou for the reply. I look forward to the next release.

Hi Anton. Traccar version 3.8 - I do not see this option.This is implemented? Or will it in future versions?
H2 database is growing fast enough and slows down the server.How can I clean up the old records?
Thanks

It is available in 3.8. The config key is "database.positionsHistoryDays".

Thanks man

hi . in ver 3.11 how i can find ? database.positionsHistoryDays

You have to add it to the config file. It's not there by default.

Anton, then, if I have a huge H2 file and I add now this rule to config.xml the database will be cleaned or I'll have stored the old records (more than, for example, 90 days from today) and the new records will be stored those 90 days only?

Thanks.

    systemctl stop traccar
    sleep 2

    java -cp /opt/traccar/lib/h2*.jar org.h2.tools.Shell -url jdbc:h2:/data/traccar/data/database -driver org.h2.Driver -user sa \
        -sql "DELETE FROM tc_positions WHERE DATEDIFF(DAY, fixTime, CURRENT_DATE) >=7 AND id NOT IN (SELECT positionId FROM tc_devices WHERE positionid IS NOT NULL);"

    java -cp /opt/traccar/lib/h2*.jar org.h2.tools.Shell -url jdbc:h2:/data/traccar/data/database -driver org.h2.Driver -user sa \
        -sql "DELETE FROM tc_events WHERE DATEDIFF(DAY, eventTime, CURRENT_DATE) >=7;"

    systemctl restart traccar

UNLOCK H2 DATABASE !!!

systemctl stop traccar
sleep 2

    java -cp /opt/traccar/lib/h2*.jar org.h2.tools.Shell -url jdbc:h2:/data/traccar/data/database -driver org.h2.Driver -user sa \
        -sql "SELECT * FROM PUBLIC.DATABASECHANGELOGLOCK;UPDATE PUBLIC.DATABASECHANGELOGLOCK SET LOCKED=0 WHERE ID=1;SELECT * FROM PUBLIC.DATABASECHANGELOGLOCK;"

systemctl restart traccar

It works! Thank you!

COMPACT H2 DATABASE

systemctl stop traccar
sleep 2

     java -cp /opt/traccar/lib/h2*.jar org.h2.tools.Shell -url jdbc:h2:/data/traccar/data/database -driver org.h2.Driver -user sa \
            -sql "SHUTDOWN COMPACT;" >/dev/null

systemctl restart traccar

!!!! DANGER !!!

Don't use this Fucking Bitch H2 Database !!!

It's crashes and corrupts without any reason.

Never seen so a shitty buggy Database :-(

I warn people every time. It's good for testing, but definitely not usable for production.