Traccar fails to update past 5.8 (Can't DROP 'geofenceids'; check that column/key exists)

I wanted to share this that's been causing me a headache for a few hours, for anyone that may encounter it in the future.

Scenario:

• You're on Linux (in my specific case, Ubuntu Server 22.04, but based on the issue, I don't think the OS version is that important as long as it runs systemd)

• You're updating from any version from 4.0 (only reason I mention this is because it's where this install I'm working on was. I do not know if it applies to previous versions) to the latest (at the time of writing, 5.11).

• Your Traccar DB is quite large (in this case, 12Gb)

• You get the install package, unzip, and run the executable.

• You start the Traccar service.

• Service starts and as you check systemctl status, you see it's going through the schema updates (where applicable) for each version between your own and 5.11. The terminal output looks something like this:
  

• Eventually, you get to 5.7 or 5.8 which seem to be much larger in terms of DB updates, and run into a problem. Traccar service restarts after 10 minutes withouth the update procedure finishing. It's worth noting that the DB update is still continuing in the background.

• You start seeing ""Waiting for changelog lock...." line after line in the traccar log. And think, what now?

Here's what you do.

• Stop the traccar service (sudo service traccar stop)

• Let the DB update finish. Ignore the logs. You'll know the update's finished because you will no longer see a temp file in /var/lib/mysql/traccar-db-name. You'll also notice CPU usage for the mysql process goes down to almost 0 when you run "top -c". Be patient, this process can take a long time. Whatever you do, don't kill it the db task.

• Once finished. Start the traccar service (sudo service traccar start).

• You'll see the following errors in the traccar logs:
  

• Stop the traccar service (sudo service traccar stop).

Full credit goes to "Danko" for solving this part of the riddle (https://www.traccar.org/forums/topic/serveur-58/). We need to set the DB to how it was before the 5.8 update procedure failed. To do this, In the terminal, access the MySQL command line:

mysql -u traccar_db_user -p  (change traccar_db_user for the actual database user).
USE traccar_db; (change traccar_db for the actual database name)
SET sql_mode = '';
ALTER TABLE tc_devices ADD geofenceids VARCHAR(128);
ALTER TABLE tc_positions DROP COLUMN geofenceids;

That last command will take a long time, again, be very patient, this fully depends on your setup, db size, resource allocation, etc. In my case, it took 45m.

Once it finishes, exit the mysql command line (just type exit and press enter). We're now back in the command line. With Danko's part of the riddle solved, we can now continue with our fix.

If we simply start the traccar service again, we will spend 10 minutes again in the update schema and the whole issue starts again. Don't do this. If you do, start all the steps from the begging once more.

The reason this issue is happening is because the Traccar installation script creates a systemd service file (/etc/systemd/system/traccar.service), specifying a 10 minute timeout where, if there's no activity at all in 10 minutes, the service should be restarted. Because the update procedure is doing a MySQL update (and waits for it to report back as OK), systemd does not see this as traccar service activity and therefore restarts the traccar service after 10 minutes, screwing the whole thing up. This is an oversight which I hope can get fixed in future Traccar install scripts.

To fix this:

• Edit the service file (sudo nano /etc/systemd/system/traccar.service).
• Find the line "WatchdogSec=600"
• Change it to more than 600 seconds (even if it's temporal, for the update), for example, 6000.
• Save and exit nano ("ctrl+x" followed by "y").
• Run this command (sudo systemctl daemon-reload)
• Now, finally, start the traccar service (sudo service traccar start).
• Since the service won't be auto-restarted after 10 minutes this time round, it should now, hopefully, finish the update procedure successfully.

That's it, you're now on Traccar 5.11

I hope you find this helpful and saves you a lot of searching.

You can also manually do those changes as an alternative.

Anton, you mentioned doing this in Danko's thread too but you also mentioned not having a guide. I did not find a guide either in my search, which is why I looked into this further. As far as I'm concerned, this can be fixed in the installer script (I've reported it as a bug -> https://github.com/traccar/traccar/issues/5258) but of course, I have no control of whether this will/won't happen.

This thread is mainly for those already affected by this issue (that perhaps have stayed back at 5.6 due to it) and so it can be found when people look up the issue.

i.e. no need to do it manually, with the steps provided but if anyone can and wants to provide those manual steps, by all means, please do.

Anton, just saw this has been added as a fix for the next version on github, thanks for your work.

No worries. It's pretty common problem, so I think it makes sense to have a better solution.

i followed the steps above to update to the last versionbut when I am running the ALTER TABLE tc_positions DROP COLUMN geofenceids;
it keeps saying the ERROR 1091 (42000): Can't DROP 'geofenceids'; check that column/key exists

I keep getting the error - Can't DROP 'geofenceids'; check that column/key exists - SQLSyntaxErrorException (... < DatabaseModule:99 < <gener:-1 < *:-1 < ... < MainModule:138 < <gener:-1 < ...

Have you checked if the column exists?

the geofenceid column doesn't exist at tc_devices or the tc_position

Then what's the point of running that query?

i am trying to fix this error on the logs

I feel like you're not telling us some critical pieces of information. This error won't just pop up by itself. It likely means you had a failed migration. And when that happens, there's a lock on the database first.

as i mentioned i tried to update from 5.6 to 6.6.
I stopped traccar removed the old files run the traccar.run and then I put the traccar.xml inside the conf. then it started to show this message.
i used my backup and then I tried to upgrade via traccal_tools the same thing

If that's the case, it means your database was already corrupted from same past actions.

at the 5.6 everything worked great