How to export/backup settings and devices list from the db file?
How to export/backup settings and devices list from the db file '/opt/traccar/data/database.mv.db'?
Use external JDBC tools. For example SQuirreL SQL Client.
How to export/backup only the essential device setup data not stored in the traccar.xml file?
Not sure I understand how this is different from the first question.
How to reduce the size of the db file?
Clear old data. Already provided you a link to how it can be done in MySQL. If you want to use H2, which is not recommended, you can probably do something similar using H2 console client.
Hi Anton, thank you.
The second question is about how to reduce the file size to only essential setting and not non-essential event records which I'm assuming takes up the vast majority of my 1GB db file. The setting for the devices (names, IDs, notifications, and boundaries) should be relatively small.
Running the command directly from the script to reduce the size doesnt work. I provided the error messages. The H2 tool on Linux required java to be installed. If that is the only way, I will try it. But there seems to be lighter weight db tools just to run an SQL command on a db file. I tried Sqlite3 but it didnt recognize the db file for some reason.
Why would Sqlite3 recognize a file from completely different database engine?
Traccar comes with JRE, so you don't need to install anything separately.
I know it runs on java, but when I type java at my terminal it tells me to install jre.
The db file is just binary data. Are there no tools that can read the formats of more than one engine? I'll assume from your rely there arent.
Traccar does not set global path for java, so you have to specify full path when you run it.
Every database has its own binary format that is almost never compatible with one another. There are some tools that can work with multiple database via drivers, like the one I mentioned above. But why use external tools when Traccar comes with everything you need out of the box? You can use H2 console to connect to the default H2 database and execute any SQL command there.
Found java at: /opt/traccar/jre/bin/java
root@instance-5-fixed:~# /opt/traccar/jre/bin/java ./squirrel-sql-4.3.0-standard.jar
Error: Could not find or load main class ..squirrel-sql-4.3.0-standard.jar
Caused by: java.lang.ClassNotFoundException: //squirrel-sql-4/3/0-standard/jarStill working on it..
Squirrel is a standard tool that comes with Traccar. I haven't used it for a while, so not sure what's the right way to run it. I was talking about H2 console, that you mentioned previously.
Thanks. I installed H2 following https://stackoverflow.com/questions/18575112/installing-and-starting-the-h2-database-on-ubuntu/18575218.
I edited the java location to absolute path in the sh file. But then got:
The Web Console server could not be started. Possible cause: another server is already running at http://10.138.0.8:8082?key=d65eae4d434892ee0be3599529a59ed6f87f00dd8e4dedb2d7b748baca0170af
Root cause: Exception opening port "8082" (port may be in use), cause: "java.net.BindException: Address already in use (Bind failed)" [90061-200]I could try changing the port from 8082 if there is a conflict, but I'm running traccar on a GCP cloud server, so I dont have a browser to use the tool in.
However, originally I got the same error as above for SquirrelSQL so I think I just need to point it to the java somehow. Might just install java.. nope didnt help.
Got an H2 tool on shell following here: https://stackoverflow.com/questions/2760887/frontend-tool-to-manage-h2-database
http://opensource-soa.blogspot.com/2009/03/how-to-use-h2-shell.html
No, it doesn't look like it can run without a browser. Anyone know of any shell tools for an H2 database?
http://www.h2database.com/html/tutorial.html
Why can't you connect from your local browser?
Also, H2 does provide a shell, as far as I know. You even had a command for it on GitHub, as I recall.
Took me a while to get back to this.. was looking for a shell tool because I'm connecting through SSH to GCP with a bunch of firewall ports denied.. would have to set that up..
Anyway, making some progress.. installed the H2 tool following the link below and used the command below that and got a sql> prompt.
https://www.developersoapbox.com/h2-database-basics/
java -cp h2-1.4.200.jar org.h2.tools.Shell -url "jdbc:h2:file:/opt/traccar/data/database.mv.db" -driver "org.h2.Driver" -user "" -password ""I was able to run the SQL commands from the https://www.traccar.org/clear-history/ script with no errors, however the size of the ..mv.db file didn't change. Any suggestions?
DELETE FROM tc_positions WHERE fixTime < DATE(DATE_ADD(NOW(), INTERVAL -7 DAY)) AND id NOT IN (SELECT positionId FROM tc_devices WHERE positionid IS NOT NULL) LIMIT 10000"
DELETE FROM tc_events WHERE eventTime < DATE(DATE_ADD(NOW(), INTERVAL -7 DAY)) LIMIT 10000"I ran each command 3 times but the file size is still 1472831488 (1.4GB).
It could be that deleting old data doesn't actual reduce the file size. It might be just clearing space internally, so it can be reused by new records. Basically, file won't reduce in size, but it won't increase either.
A couple of details to update:
I followed this SO page:
https://stackoverflow.com/questions/41469066/why-does-recreating-h2-database-reduce-the-size-drastically
..to this GH issue:
https://github.com/h2database/h2database/issues/301
..which describes the same problem I'm having. Running the DELETE commands increases the db file size greatly. At one point it was up to 3.6GBs from 1.4, and I only had 2.6 of free space to make a copy. It is back down to 2.1GBs (which shows it can reduce in size). So, I made a copy and tried the commands suggested above with no success. I got the following error when using -sql "SHUTDOWN DEFRAG": Error: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database is already closed. So I tried it on the main copy, and it gave the same error.
I also found this page saying similar problems and fixes, but not for the last error. It also says "H2 is not recommended for production" and suggests mySQL/mariaDB or Postgres.
https://github.com/metabase/metabase/issues/6510
I'm stumped at the moment. Is there any way to convert this db file into one I can use with another tool? Or any ideas on how to get 'SHUTDOWN DEFRAG;' working? - Thanks
If you want to migrate to a proper database, there are quite a few forum threads about it.
How to export/backup settings and devices list from the db file '/opt/traccar/data/database.mv.db'?
How to export/backup only the essential device setup data not stored in the traccar.xml file?
How to reduce the size of the db file?
I have tried several methods using mysql and sqlite on linux described here:
https://github.com/traccar/traccar/issues/4764