Using existing mysql database on new server
Looks like you haven't copied some database tables (e.g. DATABASECHANGELOG).
I retried with fresh DB. And I am sure I have the table which you specified in last comment.
Here is fresh log.
WARNING|wrapper|Service traccar|17-01-16 10:37:09|YAJSW: yajsw-beta-12.07
WARNING|wrapper|Service traccar|17-01-16 10:37:09|OS : Windows Server 2012 R2/6.3/amd64
WARNING|wrapper|Service traccar|17-01-16 10:37:09|JVM : Oracle Corporation/1.8.0_111/C:\Program Files\Java\jre1.8.0_111/64
INFO|wrapper|Service traccar|17-01-16 10:37:09|start delay: 0
INFO|wrapper|Service traccar|17-01-16 10:37:09|started process with pid 3092
INFO|3092/0|Service traccar|17-01-16 10:37:10|[INFO] StandardFileSystemManager - Using "C:\Windows\TEMP\vfs_cache" as temporary files store.
INFO|3092/0|Service traccar|17-01-16 10:37:10|[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Started.
INFO|3092/0|Service traccar|17-01-16 10:37:11|INFO 1/16/17 10:37 AM: liquibase: Clearing database change log checksums
INFO|3092/0|Service traccar|17-01-16 10:37:11|INFO 1/16/17 10:37 AM: liquibase: Successfully acquired change log lock
INFO|3092/0|Service traccar|17-01-16 10:37:11|INFO 1/16/17 10:37 AM: liquibase: Creating database history table with name: traccar38.DATABASECHANGELOG
INFO|3092/0|Service traccar|17-01-16 10:37:11|INFO 1/16/17 10:37 AM: liquibase: Successfully released change log lock
INFO|3092/0|Service traccar|17-01-16 10:37:12|INFO 1/16/17 10:37 AM: liquibase: Successfully acquired change log lock
INFO|3092/0|Service traccar|17-01-16 10:37:12|INFO 1/16/17 10:37 AM: liquibase: Reading from traccar38.DATABASECHANGELOG
INFO|3092/0|Service traccar|17-01-16 10:37:12|INFO 1/16/17 10:37 AM: liquibase: ./schema/changelog-master.xml: changelog-3.3::changelog-3.3::author: Marking ChangeSet: changelog-3.3::changelog-3.3::author ran despite precondition failure due to onFail='MARK_RAN':
INFO|3092/0|Service traccar|17-01-16 10:37:12| ./schema/changelog-master.xml : Not precondition failed
INFO|3092/0|Service traccar|17-01-16 10:37:12|
INFO|3092/0|Service traccar|17-01-16 10:37:12|INFO 1/16/17 10:37 AM: liquibase: ./schema/changelog-master.xml: changelog-3.5::changelog-3.5::author: Marking ChangeSet: changelog-3.5::changelog-3.5::author ran despite precondition failure due to onFail='MARK_RAN':
INFO|3092/0|Service traccar|17-01-16 10:37:12| ./schema/changelog-master.xml : Not precondition failed
INFO|3092/0|Service traccar|17-01-16 10:37:12|
INFO|3092/0|Service traccar|17-01-16 10:37:12|INFO 1/16/17 10:37 AM: liquibase: ./schema/changelog-master.xml: changelog-3.6::changelog-3.6::author: Marking ChangeSet: changelog-3.6::changelog-3.6::author ran despite precondition failure due to onFail='MARK_RAN':
INFO|3092/0|Service traccar|17-01-16 10:37:12| ./schema/changelog-master.xml : Not precondition failed
INFO|3092/0|Service traccar|17-01-16 10:37:12|
INFO|3092/0|Service traccar|17-01-16 10:37:13|INFO 1/16/17 10:37 AM: liquibase: ./schema/changelog-master.xml: changelog-3.7::changelog-3.7::author: Data updated in devices
INFO|3092/0|Service traccar|17-01-16 10:37:13|SEVERE 1/16/17 10:37 AM: liquibase: ./schema/changelog-master.xml: changelog-3.7::changelog-3.7::author: Change Set changelog-3.7::changelog-3.7::author failed. Error: Can't write; duplicate key in table '#sql-d52_1e1' [Failed SQL: ALTER TABLE traccar38.devices ADD CONSTRAINT fk_device_group_groupid FOREIGN KEY (groupid) REFERENCES traccar38.groups (id) ON UPDATE RESTRICT ON DELETE SET NULL]
INFO|3092/0|Service traccar|17-01-16 10:37:13|INFO 1/16/17 10:37 AM: liquibase: changelog-3.7::changelog-3.7::author: Successfully released change log lock
INFO|3092/0|Service traccar|17-01-16 10:37:13|java.lang.reflect.InvocationTargetException
INFO|3092/0|Service traccar|17-01-16 10:37:13| at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at java.lang.reflect.Method.invoke(Unknown Source)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at org.rzo.yajsw.app.WrapperJVMMain.executeMain(WrapperJVMMain.java:60)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at org.rzo.yajsw.app.WrapperJVMMain.main(WrapperJVMMain.java:43)
INFO|3092/0|Service traccar|17-01-16 10:37:13|Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set changelog-3.7::changelog-3.7::author:
INFO|3092/0|Service traccar|17-01-16 10:37:13| Reason: liquibase.exception.DatabaseException: Can't write; duplicate key in table '#sql-d52_1e1' [Failed SQL: ALTER TABLE traccar38.devices ADD CONSTRAINT fk_device_group_groupid FOREIGN KEY (groupid) REFERENCES traccar38.groups (id) ON UPDATE RESTRICT ON DELETE SET NULL]
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.changelog.ChangeSet.execute(ChangeSet.java:619)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.Liquibase.update(Liquibase.java:214)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.Liquibase.update(Liquibase.java:192)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.Liquibase.update(Liquibase.java:188)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at org.traccar.database.DataManager.initDatabaseSchema(DataManager.java:142)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at org.traccar.database.DataManager.<init>(DataManager.java:68)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at org.traccar.Context.init(Context.java:176)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at org.traccar.Main.main(Main.java:35)
INFO|3092/0|Service traccar|17-01-16 10:37:13| ... 6 more
INFO|3092/0|Service traccar|17-01-16 10:37:13|Caused by: liquibase.exception.DatabaseException: Can't write; duplicate key in table '#sql-d52_1e1' [Failed SQL: ALTER TABLE traccar38.devices ADD CONSTRAINT fk_device_group_groupid FOREIGN KEY (groupid) REFERENCES traccar38.groups (id) ON UPDATE RESTRICT ON DELETE SET NULL]
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:309)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:113)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1277)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1259)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.changelog.ChangeSet.execute(ChangeSet.java:582)
INFO|3092/0|Service traccar|17-01-16 10:37:13| ... 15 more
INFO|3092/0|Service traccar|17-01-16 10:37:13|Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-d52_1e1'
INFO|3092/0|Service traccar|17-01-16 10:37:13| at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at java.lang.reflect.Constructor.newInstance(Unknown Source)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.Util.getInstance(Util.java:408)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
INFO|3092/0|Service traccar|17-01-16 10:37:13| at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:307)
INFO|3092/0|Service traccar|17-01-16 10:37:13| ... 20 more
INFO|wrapper|Service traccar|17-01-16 10:37:13|calling onStop
INFO|wrapper|Service traccar|17-01-16 10:37:13|Win service stop - timeout: 30000
INFO|wrapper|Service traccar|17-01-16 10:37:13|Win service wrapper.control -> stopping application
INFO|wrapper|Service traccar|17-01-16 10:37:13|Win service stop - after shutdown
INFO|wrapper|Service traccar|17-01-16 10:37:13|Win service stop - before notify
INFO|wrapper|Service traccar|17-01-16 10:37:13|Win service terminatedWell, it's definitely not a fresh database. You must be missing something.
I created new db, imported data from existing db and then tried to run server with this new db.
I don't understand why you claim that it's a fresh database when you manually import the data there. So, it's not fresh, it's a copy. Apparently you haven't copied everything, so it fails to start.
Well, all I wanted to do is switch to new db server and import existing devices and users into this db ( I don't care about positions table data). I tried with empty db, got it working, but when I manually copied my existing users I was unable to login to server interface, was getting error. Also devices data failing to import due to reference key error with group. ( I had no group in existing database.)
How do you do this kind of job when you upgrade to newer version or change db servers?
Upgrade is automatically handled by Traccar.
Migration of MySQL database can be easily done with mysqldump tool. I have never had any problems. You can just disable foreign key checks for the import.
That's how I am doing, exporting using mysqldump tool and then importing using same too. No problem in importing and exporting but server service fails to start after doing all of this. Strange.
Logs which I shared means missing data?
As I said, your logs indicate that you haven't copied everything.
I tried few more things and somehow copied users and devices to new db.
But when I try to login to my dashboard, I get this error message
NullPointerException (PermissionsManager:161 < *:259 < SessionResource:94 < ...)
Anyone, any idea about this?
You have some problem with your data consistency. It seems like you are missing some user records.
The problem is with your database, not Traccar server. If you don't know how to manage MySQL database, I would recommend to find someone who does.
ok, thanks Anton for the help.
Will write here once I have the solution to this problem.
I finally, shifted my database with all my existing data to new server ( AWS RDS service) and its running perfectly now. Here is the procedure I followed.
- Took backup of existing database (only data without schema) ( except DATABASECHANGELOG and DATABASECHANGELOGLOCK).
- Uninstalled traccar server, and then installed fresh copy of traccar and pointed this new server to use new database.
- Started traccar server ( it automatically created every necessary table).
- Imported all backedup data to new database.
- Restarted traccar server and all set.
DATABASECHANGELOG and DATABASECHANGELOGLOCK tables were the problem points, I also faced some other errors like invalid session and null pointer etc. I have mentioned these errors in my previous comments to this thread. I fixed these errors by setting "expirationtime" column of users table to "null" which had 00:00 kind of values in it ( which is invalid date time).
So, everything else is working fine now.
Thanks for sharing the information.
Hi Anton,
I have existing running 3.9 version on AWS which has MySql installed on same instance. But, to increase performance of this server I made separate RDS instance on AWS where I am trying to host just database for this running version of traccar.
What I did is, I extracted data from the working version of traccar and imported this data into new RDS instance which has everything same as it was in source database.
But, when I try to run service it stops automatically and this is what I am reading in log.
Let me know what is the best way to solve this problem.