Upgrading from 4.14 to 5.9 - Duplicate column name 'calendarid' - SQLSyntaxErrorException

I am moving my traccar V4.14 to a new server (both Ubuntu), so far I have...

1. Backed up the MySQL database from the old server
2. Installed MySQL fresh on the new server
3. Restored the MySQL back up to the new server
4. Run the V5.9 traccar install on the new server

and I get the following error in the log file...

2023-11-07 20:28:49  INFO: Column tc_commands_queue.description dropped
2023-11-07 20:28:49  INFO: ChangeSet changelog-5.5::changelog-5.5::author ran successfully in 956ms
2023-11-07 20:28:51  INFO: Columns motionstreak(BOOLEAN) added to tc_devices
2023-11-07 20:29:10  INFO: Table tc_reports created
2023-11-07 20:29:16  INFO: Foreign key constraint added to tc_reports (calendarid)
2023-11-07 20:29:36  INFO: Table tc_user_report created
2023-11-07 20:29:59  INFO: Foreign key constraint added to tc_user_report (userid)
2023-11-07 20:30:05  INFO: Foreign key constraint added to tc_user_report (reportid)
2023-11-07 20:30:26  INFO: Table tc_group_report created
2023-11-07 20:30:29  INFO: Foreign key constraint added to tc_group_report (groupid)
2023-11-07 20:30:52  INFO: Foreign key constraint added to tc_group_report (reportid)
2023-11-07 20:30:53  INFO: Table tc_device_report created
2023-11-07 20:31:04  INFO: Foreign key constraint added to tc_device_report (deviceid)
2023-11-07 20:31:36  INFO: Foreign key constraint added to tc_device_report (reportid)
2023-11-07 20:31:36  INFO: ChangeSet changelog-5.6::changelog-5.6::author ran successfully in 166637ms
2023-11-07 20:31:40  INFO: Columns commandid(INT) added to tc_notifications
2023-11-07 20:31:45  INFO: Foreign key constraint added to tc_notifications (commandid)
2023-11-07 20:31:45  INFO: ChangeSet changelog-5.7::changelog-5.7::author ran successfully in 5912ms
2023-11-07 20:32:06  INFO: Column tc_devices.geofenceids dropped
2023-11-07 20:32:07  INFO: Columns geofenceids(VARCHAR(128)) added to tc_positions
2023-11-07 20:32:07  INFO: ChangeSet changelog-5.8::changelog-5.8::author ran successfully in 13812ms
2023-11-07 20:32:29  INFO: Columns calendarid(INT) added to tc_devices
2023-11-07 20:33:32  INFO: Operating system name: Linux version: 5.15.0-87-generic architecture: amd64
2023-11-07 20:33:32  INFO: Java runtime name: OpenJDK 64-Bit Server VM vendor: Eclipse Adoptium version: 17.0.6+10
2023-11-07 20:33:32  INFO: Memory limit heap: 941mb non-heap: 0mb
2023-11-07 20:33:32  INFO: Character encoding: UTF-8 charset: UTF-8
2023-11-07 20:33:32  INFO: Version: 5.9
2023-11-07 20:33:32  INFO: Starting server...
2023-11-07 20:33:32  INFO: HikariPool-1 - Starting...
2023-11-07 20:33:33  INFO: HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@6e4ea0bd
2023-11-07 20:33:33  INFO: HikariPool-1 - Start completed.
2023-11-07 20:33:33  INFO: Clearing database change log checksums
2023-11-07 20:33:33  INFO: Waiting for changelog lock....
2023-11-07 20:33:43  INFO: Waiting for changelog lock....
2023-11-07 20:33:53  INFO: Waiting for changelog lock....
2023-11-07 20:34:04  INFO: Waiting for changelog lock....
2023-11-07 20:34:14  INFO: Waiting for changelog lock....
2023-11-07 20:34:24  INFO: Waiting for changelog lock....
2023-11-07 20:34:34  INFO: Waiting for changelog lock....
2023-11-07 20:34:44  INFO: Waiting for changelog lock....
2023-11-07 20:34:55  INFO: Successfully acquired change log lock
2023-11-07 20:34:55  INFO: Successfully released change log lock
2023-11-07 20:35:00  INFO: Reading from traccar.DATABASECHANGELOG
2023-11-07 20:35:51  INFO: Reading from traccar.DATABASECHANGELOG
2023-11-07 20:36:00  INFO: Successfully acquired change log lock
2023-11-07 20:36:00  INFO: Using deploymentId: 9389360421
2023-11-07 20:36:00  INFO: Reading from traccar.DATABASECHANGELOG
2023-11-07 20:36:00 ERROR: ChangeSet changelog-5.9::changelog-5.9::author encountered an exception.
2023-11-07 20:36:00  INFO: Update command encountered an exception.
2023-11-07 20:36:08  INFO: Successfully released change log lock
2023-11-07 20:36:08  INFO: Command execution complete
2023-11-07 20:36:08 ERROR: Main method error - Duplicate column name 'calendarid' - SQLSyntaxErrorException (... < DatabaseModule:96 < <gener:-1 < *:-1 < ... < MainModul
e:126 < ...)
2023-11-07 20:36:21  INFO: Operating system name: Linux version: 5.15.0-87-generic architecture: amd64
2023-11-07 20:36:21  INFO: Java runtime name: OpenJDK 64-Bit Server VM vendor: Eclipse Adoptium version: 17.0.6+10
2023-11-07 20:36:21  INFO: Memory limit heap: 941mb non-heap: 0mb
2023-11-07 20:36:21  INFO: Character encoding: UTF-8 charset: UTF-8
2023-11-07 20:36:21  INFO: Version: 5.9

I have checked all the tables and cannot find a duplicate 'calendarid' in any table, so I presume the error must be generated from a JOIN somewhere ?

Any help in fixing this would be very grateful :-)

Well, you won't find it because it fails on trying to add a duplicate calendarid column.

So is it this command that fails...

2023-11-07 20:32:29  INFO: Columns calendarid(INT) added to tc_devices

I see you had this:

Waiting for changelog lock....

It indicated that you had a lock. It probably means that you had a partially executed migration. You have to fix those before remove the lock.

I had the similiar issue, I backup up devices, users, table and imported on a fresh install.

I retried the upgrade for a third time, and it worked ???

Thanks all for the advice :-)