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:
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 scripttoken
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 commandMy 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.)
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.
The file generated is what my script will operate from.
Next, prepare the MySQL database:
Here's the script:
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:
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.