Migration from H2 to Mysql

After many years of putting this off because it was just too painful, I finally got around to doing this. In order to make life easier for the next guy, I've built upon the other excellent suggestions in this forum and written a shell script which does the heavy lifting and should make migrating relatively easy. As always, YMMV.

1. Stop the traccar server and make a copy of the database files to work on
2. Download RazorSQL (free 30 day trial) and install
3. Connect to the DB copy with Razor and make an export of the entire database
4. Expand "PUBLIC"
5. Right click on "Tables" and select "Database Conversion" -> "Convert Multiple Tables"
6. Select schema "PUBLIC"
7. All tables should be selected, click Continue
8. Set database type to MySQL
9. Set a file in "Generate SQL Script File"
10. Tick "Inserts Only (No DDL)"
11. Click Convert

The file generated is what my script will operate from.

Next, prepare the MySQL database:

1. Create the new MySQL database and configure traccar to connect to it (user, permissions etc)
2. Start and then stop traccar, so it creates the database tables for us
3. Ensure you can connect to the new database yourself

Here's the script:

#!/bin/bash

F=$1
DB=$2

if [[ -z "$F" ]] || [[ -z "$DB" ]]; then
    echo usage: $0 filename
    exit 1
fi

echo "SET FOREIGN_KEY_CHECKS=0;" > $F.2
grep -v DATABASECHANGELOG $F >> $F.2
sed -i -e 's/INSERT INTO \(.*\) VALUES/INSERT INTO \L\1 \UVALUES/' $F.2
sed -i 's/INSERT/REPLACE/' $F.2
echo "SET FOREIGN_KEY_CHECKS=1;" >> $F.2
mysql -f $DB < $F.2

So put the script in a file, mark it executable and run it with the output file from RazorSQL as the first argument and the MySQL database name as the second argument.

Here's what the script does:

1. It temporarily disables foreign key checks on the database so that we can dump rows in out of order
2. It removes the DATABASECHANGELOG tables from the input, we don't need those
3. It lowercases the table and row names (MySQL uses lowercase, H2 uses uppercase)
4. It switches from using INSERT commands to REPLACE commands, so we overwrite the few rows which already exist in the DB, like the default admin user
5. Finally it connects to the database and runs the SQL

I had some rows which failed to load because the dates were invalid (apparently they had GPS around the time of christ) but that was OK because they were event rows and I didn't want that bad data anyway. Just watch the output (if there is any) and use your brain to determine whether it's a problem or not.

Hopefully this is helpful to someone.

Thanks for sharing this.

Hello.

What is stored in tc_keystore ? What keys?

I migrated database thanks to this guide to MariaDB and looks fine but I had problem with this table.
Some encoded keys was stored there and I migrated without this table. Is it needed?

We use those keys for user tokens in various places.

ok, but Is ok to exclude this table during migration? I had something in this table but it generate errors during import via mysq < command

You need to have the table, but I think if it's empty, Traccar will generate new keys automatically.

yes, empty table exist.

Super. Thanks Anton :)

Just to say a huge thank you for this, as it has saved me a huge amount of time!!!

I've had to make a few modifications for the script to work.

I changed grep -v to grep -av so it uses a text file, as the export from RazorSQL produced a text file.
Also the mysql command needed to have to user and password set so it became mysql -u root -proot -f $DB < $F.2 with user and password both being "root". Can of course just use "-p" to have the script prompt for the password. Other than that, great job!

A few more hints as of 2024-02-28 here:

• The column name for servertime in the table tc_events has changed. This can be addressed by adding the line sed -i 's/REPLACE INTO tc_events(id, type, servertime/REPLACE INTO tc_events(id, type, eventtime/' $F.2 to the script
• I got another error about a missing field/column token in the tc_users table, this can be adressed by removing those fields (and the corresponding values) from the changed .sql.2 file the script creates before feeding it into the mysql command

My full script

#!/bin/bash

# See https://www.traccar.org/forums/topic/migration-from-h2-to-mysql/

F=$1
DB=$2

if [[ -z "$F" ]] || [[ -z "$DB" ]]; then
    echo usage: $0 filename
    exit 1
fi

echo "SET FOREIGN_KEY_CHECKS=0;" > $F.2
grep -av DATABASECHANGELOG $F >> $F.2
sed -i -e 's/INSERT INTO \(.*\) VALUES/INSERT INTO \L\1 \UVALUES/' $F.2
sed -i 's/INSERT/REPLACE/' $F.2
sed -i 's/REPLACE INTO tc_events(id, type, servertime/REPLACE INTO tc_events(id, type, eventtime/' $F.2
echo "SET FOREIGN_KEY_CHECKS=1;" >> $F.2

I then used this pyhon script to remove the offending token fields from the users (I don't have the sed skills to do that):

#!/usr/bin/python3
import re
import sys

paths = sys.argv[1:]
if len(paths) == 0:
    print("Error: please supply the path as an argument!")
    exit()

counter = 0
for path in paths:
    with open(path, "r", encoding="utf-8") as f:
        lines = []
        for l in f.readlines():
            if l.startswith("REPLACE INTO tc_users(id"):
                parts = re.split('\(|\)', l)
                token_index = [i for i, p in enumerate(parts[1].split(", ")) if p == "token"][0]
                parts[1] = parts[1].replace("token, ", "")
                # Replace the value at the position of the token
                parts[3] = ', '.join([p for i, p in enumerate(parts[3].split(", ")) if i != token_index])
                out = f'{parts[0]}({parts[1]}){parts[2]}({parts[3]}){parts[4]}'
                lines.append(out)
                counter += 1
            else:
                lines.append(l)
    with open(path, "w") as f:
        f.writelines(lines)


print(f"Replaced {counter} users in {paths[0]}")

You can run it with:

python3 name-of-the-script.py traccar.sql.2

And it will make the changes to the file.

Then finally you add it to the database using:

mysql -u traccar -p -f traccar < traccar.sql.2

(this assumes a database with the name traccar is present and a user named traccar both exists and has the priviledges to edit said database.)