Migration vom MySQL (Azure) to MariaDB

Svena month ago

Hi.
I've been struggling with a problem for four days now.
It shouldn't be a problem at all, as I've successfully completed the process several times over the past few years.

My Traccar runs in Docker - the database is on Microsoft AZURE ("Flexible Azure Database for MySQL Server"). Everything has been running smoothly for a long time - not a single Traccar update has ever caused any problems. My task now is to migrate the entire database from the aforementioned AZURE service to a MariaDB, which also runs in Docker.

I perform the following steps:

  1. Complete dump of the existing DB:
    mysqldump -h XXXXX.mysql.database.azure.com -u sven -p traccar > traccar_dump.sql

  2. Stop the Docker container "traccar"

  3. Adapt the traccar.xml to the new DB

  4. Create an empty DB "traccar" in the new MariaDB.
    4.1 Create a user with grant permissions for the "traccar" DB
    (same username and password as before on Azure)

--> From here, I tested various solutions. None worked - however, the error messages were different.

5.1 Importing the dump using
mysql -h XXX.YYY.ionos.com -u sven -p traccar < traccar_dump.sql

Then I restart the Docker container "traccar."
Traccar doesn't start. The log keeps showing:

ERROR: Main method error - Table 'tc_attributes' already exists - SQLSyntaxErrorException (... < DatabaseModule:99 < <gener:-1 < *:-1 < ... < MainModule:139 < <gener:-1 < ...)

My second option also fails:

5.2 Before importing the dump again, I restart the Docker container. Traccar starts immediately and creates all tables in the new database (MariaDB). I stop the container and try importing the dump again (same command as above in 5.1). Then I start the Docker container and traccar starts. My devices are visible with the last known location. As soon as I address one of them and it connects to Traccar, the following error repeatedly appears in the log (as long as the device is online):

INFO: [T026e539c] connected 2025-07-13 13:32:26
INFO: [T026e539c: h02 < 80.187.116.15] *HQ,9171089463,V1,113453,A,5048.1872,N,01256.8069,E,031.80,265,130725,FFFFBBFF,262,01,35120,551#
2025-07-13 13:32:26
WARN: [T026e539c] error - Cannot invoke "Object.getClass()" because "object" is null - NullPointerException (CacheKey:22 < CacheGraph:95 < CacheManager:291 < *:325 < *:164 < ConnectionManager:157 < ...)

I've run out of ideas for a solution and my rudimentary SQL knowledge. What am I missing? Am I stupid?

The most important thing: At the end of the day, I still have a complete and functioning Traccar installation. I simply retrieve my previous traccar.xml and restart the container – and everything runs smoothly again. In the next few weeks, however, I would like to move more and more of my services away from AZURE – also for cost reasons.
There's no rush, and no data loss has occurred. But I think I can't avoid asking the community and Anton for help...

Thank you & best regards,
Sven

Anton Tananaev25 days ago

The first error likely indicates that you were missing some important tables in the database.

The second error means that you're missing some objects in the database. Basically broken references.

Sven23 days ago

Thank you for your quick reply and the clarification.
Is there any chance I can avoid the error in the first scenario described? Is it possible that the tables in question are already missing from my old database? Or does the error occur during the dump? Would there even be a way to verify this, considering that everything works perfectly in my original Azure installation?

Best regards.

Anton Tananaev23 days ago

There must be some issue with the migration itself. Maybe there's an issue with upper-case or lower-case table names or something like that.

Sven18 days ago

Thank you for your reply. I'm sorry to bother you again and ask questions – I'm ashamed of it and of course I know that you can't provide support here for free. In the meantime, I've made countless other attempts – but without success.

I've now created another dump of my working database and imported it into the MariaDB instance. Without starting Traccar, I deleted the databasechangelog and databasechangeloglock tables in the new DB. I connected both servers – old and new – in DBeaver and created a comparison of the two databases. The comparison report can be found here:
Report

The differences are highlighted in blue.
There are many differences in "Data Type" and "Extra":
int vs. int(11)
auto_increment INVISIBLE vs. auto_increment

I'm at a loss as to how to proceed.
Maybe someone can help me...

Thank you!! :)

Anton Tananaev18 days ago

What I would do is migrate the data only. Basically let Traccar create all the database tables first, then copy the data. Make sure you don't copy changelog table data. That should already be there.

Sven15 days ago

After 3 weeks, I finally did it!! :)

It finally took another 10 attempts - but my system is now finally running as intended. The previously described comparison of the two databases using DBeaver ultimately yielded no results. I had Traccar create a new database again and immediately stopped the Docker container. Using the "MySQL Workbench" tool, I created a dump of my old database, which contained only the data, as Anton recommended. This tool allows you to dump all tables individually in a single step - so I received a total of 48 individual SQL files (not including the databasechangelog table). I also used this tool again for the import, which was successful for 40 of the 48 tables and resulted in an error in eight cases. The first one was the tc_servers table: The content already existed - resulting in a DUPLICATE error. The Workbench tool skipped this table - everything was fine. The next seven errors were even stranger – I haven't found anything similar in the forum so far. I hope, I can describe them clearly and correctly with my rudimentary SQL knowledge:

In each of the tables

  1. tc_device_driver
  2. tc_device_geofence
  3. tc_user_device
  4. tc_user_driver
  5. tc_user_geofence
  6. tc_user_group
  7. tc_user_notifications

there was an additional column that shouldn't have been there.
It was named "my_row_id" and was located at the very beginning of the table.
This column didn't exist in the newly created tables.

In the dump file, it looked like this:

'INSERT INTO `tc_device_driver` (`my_row_id`, `deviceid`, `driverid`) VALUES (1,1,5),(2,2,4),(3,3,6);'

Waste no time - I fixed the faulty section directly in the text editor (for all affected tables):

'INSERT INTO `tc_device_driver` (`deviceid`, `driverid`) VALUES (1,5),(2,4),(3,6);'

Then I imported the remaining seven dumps and restarted the Docker container immediately.
Traccar started in less than 20 seconds without any error messages in the log... :)

Maybe my message will help someone else - even if my explanation seems a bit amateurish.
Thank you for your help, Anton!