Migration from H2 to Mysql
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
servertimein the tabletc_eventshas changed. This can be addressed by adding the linesed -i 's/REPLACE INTO tc_events(id, type, servertime/REPLACE INTO tc_events(id, type, eventtime/' $F.2to the script - I got another error about a missing field/column
tokenin thetc_userstable, this can be adressed by removing those fields (and the corresponding values) from the changed.sql.2file 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.2I 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.2And 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.)
Hello,
I would also like to share my experiences with database migration from H2 to MySQL.
After reading the forum, I decided to use the SQuirreL SQL Client program, because it is free. I do not like software in trial mode.
In short, the entire migration procedure in SQuirreL SQL is simple: connect to both databases (old and new) and select Copy Table in the old one and Paste Table in the new one, but to get to this stage I had to search the Internet a bit. Maybe someone will benefit from my experiences :)
Download and install SQuirreL SQL Client. Select the options related to H2 and MySQL (although I do not know what this gives...). After installing, we need libraries to support databases. It seems to me that it is best to use those used by Traccar. So from the lib directory, which is where tracker-server.jar is, we copy to the lib directory where SQuirreL is installed appropriate files ... in my case h2-2.2.220.jar and mysql-connector-j-8.1.0.jar.
In MySQL (I assume that the reader is familiar with MySQL and phpMyAdmin and will be able to handle this, if not, you need to ask Google) create a database (e.g. traccar) and a user account that has full permissions to manage this database. Write down the username and password. Open MySQL for access from the network (unless SQuirreL is on the same host).
Now stop and make a full backup of Traccar :) You can read on the forum that it is worth blocking the ports through which Traccar receives data from locators, because they will buffer this data and send it in bulk when the server comes back to life. I did that, but my devices simply lost data from the upgrade period ;)
Modify Traccar's configuration so that it uses MySQL and start the server. When it starts, stop it :) This operation will cause Traccar to create all the necessary tables, indexes, etc. in the MySQL database. Without it, the Paste Table operation will also create a table, but the data types are sometimes different than those created by the application.
Copy Traccar's H2 database to a local disk (or share it as a CIFS resource), specifically we need the data/database.mv.db file.
Create connections to both databases in SQuirreL SQL Client (i.e. add Aliases: on the left side select the Aliases tab and Create a new alias):
H2: name - any, driver - H2, URL -
jdbc:h2:c:\traccar_data\database[important: here we provide the path and file name with the database, but without the extension, i.e. without ".mv.db"], user - sa, password - empty.MySQL: name - any, driver - MySQL Driver, URL -
jdbc:mysql://mysql_server_ip:mysql_port/traccar_database_name, user and password consistent with those created in point 2.
- Connect SQuirreL to both databases. Select aliases one by one and connect to them. The list of tables can be found:
for H2: alias name -> DATABASE -> PUBLIC -> BASE TABLE
for MySQL: alias name -> traccar_database -> TABLE
Check if the MySQL tables are empty. This concerns tables with names starting with
tc_, leave the two tables with different names intact. If any table has more than 0 rows (top tab Row Count), right-click on the table name (tree on the left) and select the Delete Records option.At this stage, a certain problem appeared, which may be better solved, but I did not find such a solution. So I will write how I managed ... although I think the method is not the best :/ In the H2 database, table names are written in uppercase letters (e.g.
TC_EVENTS). In the MySQL database, they are lowercase (tc_events). So when we do a direct Copy Table in the H2 alias and Paste Table in the MySQL, a new table will be created. I dealt with it this way: I changed the table name in MySQL (right mouse button -> MySQL -> Rename) to uppercase letters and only then selected the Paste Table option and patiently waited for the data to copy. After copying, I changed the name back to lowercase letters.Start Traccar Server.
BTW1: The copying time is mainly the time it takes to copy the tc_postions table. For my H2 database of about 700MB, copying took about 1.5 hours.
BTW2: I also encountered another minor problem. It turned out that from the conversion point of view, some records from the tc_positions table did not want to be copied. In my case, it was the content of the FIXTIME column. Once I identified the problem, I edited the content of the database. I selected the table and from the context menu the option Contents Tab SQL Filter and added the filter FIXTIME = {ts '1970-01-01 01:00:00.0'}. It turned out that I had 4 rows that were causing a problem (after converting the timestamp, it was impossible to enter them into MySQL). I turned on the edit mode (Make Editable), changed the date to 1970-01-02, turned off the edit mode (Undo Make Editable) and now Copy Table and Paste Table worked without a problem. How to identify the problematic entries? I made a table dump in SQL format (Scripts -> Create INSERT statements file from selected tables) of the original table and the target fragment that was made until the error occurred. In this way I found the line that could not be saved to the MySQL database and then all I had to do was determine why and how to fix it.
Regards,
Marcin
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.