Suggestions for a good migration tool to convert H2 to MySQL?
I need help to convert each table to .sql so I can manually import it to the MySQL database. doing it automatically raises a lot of errors
Why don't you use the version of the application that I put in my comment?
it is easy and automatic
on the other hand, share your database configuration, the one found in the traccar configuration file.
Hi, Thanks for your help... I was able to move the database to my local machine and did the migrations. Everything works well now on my local machine (after 2 sleepless nights).
I have copied everything to the MySQL database, i also fixed the auto_increment issue and exported the database as traccar.sql file.
I have imported the file to my VPS server, But now I'm faced with a new challenge when trying to connect to traccar...
2022-02-13 11:58:02 INFO: HikariPool-1 - Start completed.
2022-02-13 11:58:03 INFO: Clearing database change log checksums
2022-02-13 11:58:03 INFO: Successfully acquired change log lock
2022-02-13 11:58:04 INFO: Successfully released change log lock
2022-02-13 11:58:04 INFO: Successfully acquired change log lock
2022-02-13 11:58:04 INFO: Cannot load service: liquibase.parser.ChangeLogParser: liquibase.parser.core.json.JsonChangeLogParser Unable to get public no-arg constructor
2022-02-13 11:58:04 INFO: Cannot load service: liquibase.parser.ChangeLogParser: liquibase.parser.core.yaml.YamlChangeLogParser Unable to get public no-arg constructor
2022-02-13 11:58:06 INFO: Reading from traccarx.DATABASECHANGELOG
2022-02-13 11:58:07 INFO: Cannot load service: liquibase.hub.HubService: Provider liquibase.hub.core.StandardHubService could not be instantiated
2022-02-13 11:58:07 INFO: Successfully released change log lock
2022-02-13 11:58:07 ERROR: Main method error - Table 'tc_attributes' already exists - SQLSyntaxErrorException (... < DataManager:298 < *:91 < Context:298 < Main:120 < ...)
Please help.
How can I connect a new traccar installation to an existing database table?
I know traccar tries to create tables during installation, How do I install with an existing database?
Replace following lines in default configuration traccar file:
<entry key='database.driver'>org.h2.Driver</entry>
<entry key='database.url'>jdbc:h2:/home/user/Documents/traccar/target/database</entry>
<entry key='database.user'>sa</entry>
<entry key='database.password'></entry>With configuration parameters for Microsoft SQL Server:
<entry key='database.driver'>com.microsoft.sqlserver.jdbc.SQLServerDriver</entry>
<entry key='database.url'>jdbc:sqlserver://[serverName][\instanceName];user=[userName];password=[password];databaseName=[database];</entry>
<entry key='database.user'>[userName]</entry>
<entry key='database.password'>[password]</entry>Where:
[serverName] - server name or address
[\instanceName] - instance (optional)
[userName] - database user name
[password] - user password
[database] - database name
Hello guys,
Thanks for your assistance. I was able to fix the migration using squirrel SQL. I had to access the database remotely, I edit the MySQL configuration following the steps outlined here to temporarily allow remote access to my VPS server database from my local pc... coupled with the steps outlined in this thread. Everything pulled together... my devices are now online.
Thank you Anton for such great software!
Big thanks to @rjk for the step by step instructions. It was a massive help. Also to @cmgodoy for the useful sed command.
I have a few points to add myself having gone through this process today:
if you are reading this just starting out with Traccar, for the love of god, use mysql now! If you don't, you will need to migrate at some point and it's painful. Installing mysql and adding 3/4 lines of config to make it work is much better than migrating a load of data
when you convert the tables from h2 format to a mysql readable file, you want INSERTs only, not the table structure. I didn't realise this was going to happen so ended up editing the files afterwards to remove the CREATE TABLE clauses but there is an option in RazorSQL to not include structure
RazorSQL is not free software, it's a time limited demo with 30 days for free. So bear that in mind, once you first run it you need to be finished with it in 30 days (I assume there will be some way of bypassing this but that's even more to figure out).
if you have some data in your tables then importing into mysql will likely take several hours. My tc_events table had about 150k rows and tc_positions had about 500k rows. Importing took about 3 hours (just from local files but a fairly low powered VM). So make sure you have time available!
the order in which you import tables into mysql matters. For example if you try to import TC_USER_GEOFENCE before importing TC_USERS & TC_GEOFENCES, it will fail due to unmet foreign key constraints (i.e. the things referred to in the USER_GEOFENCE table don't exist). It's fairly common sense to work out, just import all the tables that define specific objects such as users or devices before importing the purely relational tables that link things together.
a couple of tables will fail because they have things in them already. E.G. the users table already has a user with ID 1. @rjk said they edited the file to import and changed the ID but I preferred to connect to mysql and remove the offending record (which will then be re-inserted on import).
if you are running traccar inside docker but mysql on the host machine as I am then you'll need to do the following too:
sysctl -w net.ipv4.conf.docker0.route_localnet=1iptables -t nat -I PREROUTING -i docker0 -d 172.17.0.1 -p tcp --dport 3306 -j DNAT --to 127.0.0.1:3306iptables -t filter -I INPUT -i docker0 -d 127.0.0.1 -p tcp --dport 3306 -j ACCEPTand leave mysql bound to 127.0.0.1 on the host. Configure traccar inside docker to connect to 172.17.0.1 (or whatever IP the docker0 interface has on the host, if it's different edit those commands above too). Make sure to make those commands survive a reboot too, edit the sysconfig file and use something like iptables-persistent
I'm migrating the data from the h2 database to mysql.