How to remove lock DATABASECHANGELOGLOCK Default H2 Database

Hello,

Excuse me for my noob question, but I have an erro in my Traccar Server, after rebooting the system I had this issue.

I been researching and the solution is:
They say to apply this fix:

update DATABASECHANGELOGLOCK
set locked = 0

but I dont know how to access H2 database, you know like the command to enter like mysql

mysql -u root -p 

what is the command line to manually unlock the database?

Here is my log:

cat /opt/traccar/logs/tracker-server.log
2019-03-27 08:21:04  INFO: HikariPool-1 - Starting...
2019-03-27 08:21:05  INFO: HikariPool-1 - Start completed.
2019-03-27 08:21:06  INFO: Clearing database change log checksums
2019-03-27 08:21:06  INFO: SELECT COUNT(*) FROM PUBLIC.DATABASECHANGELOGLOCK
2019-03-27 08:21:06  INFO: SELECT COUNT(*) FROM PUBLIC.DATABASECHANGELOGLOCK
2019-03-27 08:21:06  INFO: SELECT LOCKED FROM PUBLIC.DATABASECHANGELOGLOCK WHERE ID=1
2019-03-27 08:21:06  INFO: Waiting for changelog lock....
2019-03-27 08:21:16  INFO: SELECT LOCKED FROM PUBLIC.DATABASECHANGELOGLOCK WHERE ID=1
2019-03-27 08:21:16  INFO: Waiting for changelog lock....
2019-03-27 08:21:26  INFO: SELECT LOCKED FROM PUBLIC.DATABASECHANGELOGLOCK WHERE ID=1
2019-03-27 08:21:26  INFO: Waiting for changelog lock....
2019-03-27 08:21:36  INFO: SELECT LOCKED FROM PUBLIC.DATABASECHANGELOGLOCK WHERE ID=1
2019-03-27 08:21:36  INFO: Waiting for changelog lock....
...
2019-03-27 08:25:46  INFO: SELECT LOCKED FROM PUBLIC.DATABASECHANGELOGLOCK WHERE ID=1
2019-03-27 08:25:46  INFO: Waiting for changelog lock....
2019-03-27 08:25:56  INFO: SELECT LOCKED FROM PUBLIC.DATABASECHANGELOGLOCK WHERE ID=1
2019-03-27 08:25:56  INFO: Waiting for changelog lock....
2019-03-27 08:26:06  INFO: SELECT ID,LOCKED,LOCKGRANTED,LOCKEDBY FROM PUBLIC.DATABASECHANGELOGLOCK WHERE ID=1
2019-03-27 08:26:06 ERROR: Main method error - Could not acquire change log lock.  Currently locked by 192.168.1.220 (192.168.1.220) since 3/26/19, 9:11 AM - LockException (... < DataManager:310 < *:90 < Context:389 < Main:110 < ...)

1. You can try searching forum. Someone provided solution before.
2. You can also use any JDBC database client to connect.
3. You should not be using H2 database in production. You will have issues.

OK. As Anton says, don't use H2. But if you do and you don't want to loose your DB, here are the step to remove lock.

1. stop traccar

2. cd to the traccar root. (should be something like /opt/traccar or /usr/opt/traccar)

3. start H2 shell

java -cp lib/h2*.jar org.h2.tools.Shell -url “jdbc:h2:/opt/traccar/data/database” -user sa

1. verify status of the DB. So in H2 shell run the query:

sql> SELECT * FROM PUBLIC.DATABASECHANGELOGLOCK;

output is something like :

ID | LOCKED | LOCKGRANTED             | LOCKEDBY
1  | TRUE   | 2019-04-04 20:17:21.569 | traccar (172.19.0.3)

1. clear lock

sql> update PUBLIC.DATABASECHANGELOGLOCK set locked=0 WHERE ID=1;

1. verify with

sql> SELECT * FROM PUBLIC.DATABASECHANGELOGLOCK;
ID | LOCKED | LOCKGRANTED             | LOCKEDBY
1  | FALSE  | 2019-04-04 20:17:21.569 | traccar (172.19.0.3)

(now locked is FALSE)

1. restart traccar

If using docker, add following steps
stop container
copy your DB in a subdirectory of "data" dir (for example "old")
start container
connect inside container with "docker exec -ti traccar bash"¨
update DB as above just use the path to the old DB

java -cp lib/h2*.jar org.h2.tools.Shell -url “jdbc:h2:/opt/traccar/data/old/database” -user sa

stop container
copy your db back to original location
restart container

Hope it helps

Hi,
You now have to run
java -cp lib/h2*.jar org.h2.tools.Shell -url "jdbc:h2:/opt/traccar/data/database" -driver org.h2.Driver -user sa

@Anton Tananaev
Given the number of people who have this issue, don't you think you could implement some kind of watchdog ?

• The server write a timestamp every 5 minutes to the DB.
• After a crash when the system restart, it check if the timestamp is older than 10 minute and if it is the case it deletes the lock.

We don't do that because database can be in corrupted state.

This lock is only therr to prevent access to the DB by 2 traccar processes at the same time. Right ?
After a crash, if a db is corrupted, preventing access to the DB does not bring much ?

Lock is there during schema migration. If your process died in the middle of the migration, it probably means that migration partially failed and you need to go and finish migration manually, or at least check schema to make sure it's correct.

Hi Anton: Please Help server not found
http://190.238.200.63:8082/

root@odoo15:~# systemctl status traccar.service
● traccar.service - traccar
     Loaded: loaded (/etc/systemd/system/traccar.service; enabled; vendor prese>
     Active: active (running) since Thu 2023-01-12 15:44:26 UTC; 3min 51s ago
   Main PID: 861 (java)
      Tasks: 23 (limit: 18948)
     Memory: 7.4G
     CGroup: /system.slice/traccar.service
             └─861 /opt/traccar/jre/bin/java -jar tracker-server.jar conf/tracc>

ene 12 15:44:26 odoo15 systemd[1]: Started traccar.
lines 1-10/10 (END)...skipping...
● traccar.service - traccar
     Loaded: loaded (/etc/systemd/system/traccar.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2023-01-12 15:44:26 UTC; 3min 51s ago
   Main PID: 861 (java)
      Tasks: 23 (limit: 18948)
     Memory: 7.4G
     CGroup: /system.slice/traccar.service
             └─861 /opt/traccar/jre/bin/java -jar tracker-server.jar conf/traccar.xml

ene 12 15:44:26 odoo15 systemd[1]: Started traccar.
~

How is your problem related to the thread? And what have you tried so far?

I know its a old post but just wanted to give shout out. It worked like charm, thank you so much redge76

Hi redge76 and Anton

I tried your solution in the 3rd post. But instead of starting the shall I got an error message:

Exception in thread "main" java.sql.SQLException: No suitable driver found for “jdbc:h2:/opt/traccar/
data/database”
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:706)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)
        at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:335)
        at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:270)
        at org.h2.tools.Shell.runTool(Shell.java:146)
        at org.h2.tools.Shell.main(Shell.java:80)

Now, I don’t now what to do…

Can you please help me?

Try this:

java -cp lib/h2*.jar org.h2.tools.Shell -url "jdbc:h2:/opt/traccar/data/database" -driver org.h2.Driver -user sa

Thank you, Anton. That worked.

But now, clearing the lock failed:

sql> SELECT * FROM PUBLIC.DATABASECHANGELOGLOCK;
ID | LOCKED | LOCKGRANTED                | LOCKEDBY
1  | TRUE   | 2023-07-20 21:56:47.681645 | Pi4-xxx
(1 row, 3 ms)

sql> update PUBLIC.DATABASECHANGELOGLOCK set locked=0 WHERE ID=1;
Error: org.h2.jdbc.JdbcSQLNonTransientException: The database is read only; SQL statement:
update PUBLIC.DATABASECHANGELOGLOCK set locked=0 WHERE ID=1 [90097-214]

Is Traccar server running? You can only have one process accessing the database at the same time.

No, I entered

sudo systemctl stop traccar

And if I try to reach the server via browser I get service unavailable returned…