Suggestions for a good migration tool to convert H2 to MySQL?

I've tried a few different tools, but none of them seem to be able to properly convert H2 to MySQL without a bunch of errors or forgetting some of the database items. Does anyone have a suggestion for a tool that will work reliably?
Alternatively, would someone be able to take my H2 file and convert it to a working .sql script that I could then import into my db?

h2 has a cli tool I never got to use it because I migrated to mysql early. https://opensource-soa.blogspot.com/2009/03/how-to-use-h2-shell.html

I saw a suggestion for SqurrelSQL, and I also found RazorSQL, but neither of them seem to be able to complete without error. I just am not sure of the correct way to do this.

Has no one actually done this before?

OK, I got this done successfully, but it was a gigantic pain in the ass. I'll share what I did, and then you guys can laugh at me for not knowing the correct faster way to do it.

So first of all, I experimented with SquirrelSQL and also RazorSQL (trial version). I think that either one may have worked, but I ended up using RazorSQL most of the time. After many false starts with exporting/importing data and attempting a full all-in-one migration, what I ended up having to do was change the Traccar config over to MySQL, create a blank database, start Traccar so that it would create the schema inside the new database, and then transfer the old data from H2 by hand.

Pre-migration prep:

1. Install MySQL (or whatever DB you want to move to) and ensure that communications are working.
2. Backup your Traccar config (/opt/traccar/conf/traccar.xml) by copying it to a safe place.

Migration steps:

1. Close the ports for your incoming devices so that they begin caching their GPS positions locally - to prevent those from being lost while we perform the next steps.
2. Shut down the Traccar service (/opt/traccar/bin/stopDaemon.sh
3. Make a backup of the H2 database by copying it to a safe location. In my case, I had a single device that has been running for 4 years, and my 1GB 1 Core AWS Lightsail server was slowing down to a crawl because the database had reached more than 400MB in size. I copied the db (/opt/traccar/data/database.mv.db) to my local PC via SFTP.
4. Change the Traccar config to use the new empty database.
5. Start the Traccar service (/opt/traccar/bin/startDaemon.sh) and it should automatically populate the tables and default data in the new database.
6. Important! Stop the Traccar service so that it doesn't conflict with your data import.
7. Open the H2 database with RazorSQL. In my case, I also deleted any tables that had no data in them, in order to not get confused. (Note: Also delete the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables, because those do not need to be migrated - Traccar automatically created and populated those when setting up the blank DB)
8. Select each table that has data and choose the Database Conversion > Table option. Using the multiple tables option makes troubleshooting harder, don't do that.
9. Convert each of the H2 tables to a .sql file (assuming MySQL) and don't use RazorSQL to upload the data to the new db automatically.
10. In my case, the H2 table names and column names were all UPPER CASE, and the MySQL tables and columns were all lower case (except for 2 of them, oddly enough), so I had to manually change all of the records to have lowercase table names and column names in the exported sql files by using find-and-replace in Notepad++.
11. Once the case conversion is done, import the tables one-by-one into the new empty database, watching for errors along the way. In my case, tc_positions took almost 6 hours to import more than 700k rows (via a remote connection). In more than one case, I got an error about unique IDs - one in particular was tc_users which creates a default user - since I couldn't delete the user due to foreign key constraints, I simply changed its ID from 1 to 2, and then I was able to import my old user.
12. Once all the data is imported, start the Traccar service.
13. Re-open the ports to allow your devices to reconnect and dump their cached data into the new database.

Rejoice! Don't forget to set up a proper database backup for your new database.

Thanks for sharing. I think I did something similar when I had to do a migration, but it was a few years back, so I don't remember details.

I used the following command to convert to lowercase:

sed -e 's/INSERT INTO \(.*\) VALUES/INSERT INTO \L\1 \UVALUES/' input.sql > output.sql

Thank you sir @rjk for sharing your method in migrating from H2 Database to MySQL. Cheers!

Hello, I leave you how I have made several migrations of implementations that I have made of traccar.

Excuse my English translated.

Application used: Full Convert Ultimate 21.4.1644.0 (64-bit)
The most recent versions have removed H2 from service.

It doesn't matter if you have MySQL, SQL Server, PostgreSQL...

First
Install the database

Second
Stop the Traccar service so that the devices remain storing in internal memory.

Third
In the configuration file place the parameters according to the installed database.

Fourth
Start the traccar service so it can create the new tables and then stop it

Fifth
open Full Convert Ultimate and place the data file, select which database to migrate to, select 1 worker to make it less difficult for the server and mark that the tables already exist so that they are not created again and keep the INDEX.

Sixth
Wait for the data to migrate and register the traccar service again

With this you will have all the data.

Note: keep in mind that if you have many records in the TC_POSITION table, it will take a long time

Hi @Ronald, how can I contact u?

Reminder for everyone to not share personal contact details.

Sorry Anton

the rules of the forum and collaboration in the community does not allow sharing personal data.

But you can ask any question and I will gladly help you here.

Greetings!

I am now able to connect to both databases in squirrel SQL. when I try to paste the tables in MySQL, I get this error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"tc_users" ( "id", "name", "email", "hashedpassword", "salt", "readonly", "ad...' at line 1
SQLState:  42000
ErrorCode: 1064

I don't know where I can access the SQL code for pasting so I can check for the errors. please help

why do you want to merge two databases?
explain to me what you want to do or what you are doing so I can understand