Hi,
I'm doing a clean install of traccar, when running with de default H2 it works perfect. But I want switch to MariaDB, I'm running into the following:
did clean install, changed the db to mysql in the config. When I then restart traccar it starts creating databases without error. The last database it creates before restarting:
2020-09-23 12:20:59 INFO: Table tc_users created
2020-09-23 12:20:59 INFO: ALTER TABLE traccar.tc_device_command ADD CONSTRAINT fk_device_command_commandid FOREIGN KEY (commandid) REFERENCES traccar.tc_commands (id) ON DELETE CASCADE
2020-09-23 12:21:23 INFO: HikariPool-1 - Starting...
2020-09-23 12:21:24 INFO: HikariPool-1 - Start completed.
Only then it runs into a 'waiting for changelog lock', if i change this by manually in de database it keeps rebooting:
2020-09-23 11:48:00 INFO: HikariPool-1 - Starting...
2020-09-23 11:48:01 INFO: HikariPool-1 - Start completed.
2020-09-23 11:48:03 INFO: Clearing database change log checksums
2020-09-23 11:48:04 INFO: SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
2020-09-23 11:48:04 INFO: SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
2020-09-23 11:48:04 INFO: SELECT `LOCKED` FROM traccar.DATABASECHANGELOGLOCK WHERE ID=1
2020-09-23 11:48:05 INFO: Successfully acquired change log lock
2020-09-23 11:48:05 INFO: SELECT MD5SUM FROM traccar.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL LIMIT 1
2020-09-23 11:48:05 INFO: UPDATE traccar.DATABASECHANGELOG SET MD5SUM = NULL
2020-09-23 11:48:08 INFO: Successfully released change log lock
2020-09-23 11:48:08 INFO: SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
2020-09-23 11:48:08 INFO: SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
2020-09-23 11:48:08 INFO: SELECT `LOCKED` FROM traccar.DATABASECHANGELOGLOCK WHERE ID=1
2020-09-23 11:48:08 INFO: Successfully acquired change log lock
2020-09-23 11:48:08 INFO: Can not use class liquibase.parser.core.json.JsonChangeLogParser as a Liquibase service because org.yaml.snakeyaml.constructor.BaseConstructor is not in the classpath
2020-09-23 11:48:08 INFO: Can not use class liquibase.parser.core.yaml.YamlChangeLogParser as a Liquibase service because org.yaml.snakeyaml.constructor.BaseConstructor is not in the classpath
2020-09-23 11:48:13 INFO: SELECT MD5SUM FROM traccar.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL LIMIT 1
2020-09-23 11:48:13 INFO: SELECT COUNT(*) FROM traccar.DATABASECHANGELOG
2020-09-23 11:48:13 INFO: Reading from traccar.DATABASECHANGELOG
2020-09-23 11:48:13 INFO: SELECT * FROM traccar.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2020-09-23 11:48:15 INFO: CREATE TABLE traccar.tc_attributes (id INT AUTO_INCREMENT NOT NULL, `description` VARCHAR(4000) NOT NULL, type VARCHAR(128) NOT NULL, attribute VARCHAR(128) NOT NULL, expression VARCHAR(4000) NOT NULL, CONSTRAINT PK_TC_ATTRIBUTES PRIMARY KEY (id))
2020-09-23 11:48:15 ERROR: Change Set changelog-4.0-clean::changelog-4.0-clean::author failed. Error: Table 'tc_attributes' already exists [Failed SQL: (1050) CREATE TABLE traccar.tc_attributes (id INT AUTO_INCREMENT NOT NULL, `description` VARCHAR(4000) NOT NULL, type VARCHAR(128) NOT NULL, attribute VARCHAR(128) NOT NULL, expression VARCHAR(4000) NOT NULL, CONSTRAINT PK_TC_ATTRIBUTES PRIMARY KEY (id))]
2020-09-23 11:48:16 INFO: Successfully released change log lock
2020-09-23 11:48:16 ERROR: Main method error - Table 'tc_attributes' already exists - SQLSyntaxErrorException (... < DataManager:312 < *:90 < Context:292 < Main:137 < ...)
2020-09-23 11:48:29 INFO: HikariPool-1 - Starting...
2020-09-23 11:48:30 INFO: HikariPool-1 - Start completed.
Is there anyone who can point me in the right direction?
It seems that at 'first' start with MySQL not all tables are created before the service restarts.
Found a solution:
It seems that at 'first' start with MySQL not all tables are created before the service restarts, it seems the ALTER commands take a long time (sometimes up to 30 seconds) to run. If you are on a raspberry pi, like me, it helps to optimize MariaDB first, google for the cnf files for your RPi X. I didn't optimized before taking steps below, could be that you dont need to take these steps if you optimize.
journal:
Sep 23 13:09:10 HomeAssist systemd[1]: traccar.service: Watchdog timeout (limit 5min)!
Changed the value of watchdog timeout:
: systemctl edit --full traccar.service
changed the value of WatchdogSec, in my case 9000 (2,5 hour) was enough.
running traccar on raspberrypi 4 btw.
Did a clean install on raspberry pi 4 (2gb), just optimize mariaDB BEFORE running traccar for the first time with Mysql (mariadb) as DB.
/etc/mysql/mariadb.conf.d/50-server.cnf
adding:
innodb_buffer_pool_size = 1G
innodb_log_file_size = 100M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
Hi I had the same problem after we upgraded to mariadb with tc_attributes.
This is my solution.
Before we start I am using ubuntu. This might not work on other operating systems.
We upgraded to mariadb and after importing the traccar database and setting all the correct permissions I had the above issue mentioned in the previous posts with tc_attributes.
I stopped traccar and then dropped the database ie deleted the database and all its tables. I then recreated the database eg traccar_database.
I made sure the user password etc were correct and matched the traccar.xml file
I then started traccar server and allowed it to create all the tables took a few mins once the tables were created and the usual devices were reporting that unknown device I stopped traccar server.
I then imported the old tables into the newly created tables my command line looked like this
mysql -u root -psomemysqlrootpassword traccar_database < /tmp/mysql_restore/data/mysql_databases/traccar_database.sql
This folder /tmp/mysql_restore/data/mysql_databases/ is where I had the backup saved to so I could restore the database, your folder will most probably will be different.
After I completed the import which took a good few mins.
I started traccar server and problem solved traccar server started with out issue.
Everything is working fine no error messages.
I hope this helps some one who is in a similar situation as I was.
Sorry it is not more concise.
Regards
Hi,
I'm doing a clean install of traccar, when running with de default H2 it works perfect. But I want switch to MariaDB, I'm running into the following:
did clean install, changed the db to mysql in the config. When I then restart traccar it starts creating databases without error. The last database it creates before restarting:
Only then it runs into a 'waiting for changelog lock', if i change this by manually in de database it keeps rebooting:
Is there anyone who can point me in the right direction?