3.0 > 3.1 Wrong database schema version

diggah9 years ago

Using MySQL

2015-08-10 09:33:54  INFO: Operating System name: Linux version: 2.6.32-042stab108.5 architecture: amd64
2015-08-10 09:33:54  INFO: Java Runtime name: OpenJDK 64-Bit Server VM vendor: Oracle Corporation version: 24.85-b03
2015-08-10 09:33:54  INFO: Memory Limit heap: 494mb non-heap: 214mb
2015-08-10 09:33:54  INFO: Version: 3.1-SNAPSHOT
2015-08-10 09:33:55 ERROR: Wrong database schema version (0)
diggah9 years ago

I have such error in log : 

 ./traccar start
Starting traccar...
Waiting for traccar........
WARNING: traccar may have failed to start.




jvm 1    | WrapperSimpleApp Error: Encountered an error running main:
jvm 1    | WrapperSimpleApp Error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'user' already exists
jvm 1    | WrapperSimpleApp Error: 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
jvm 1    | WrapperSimpleApp Error: 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
jvm 1    | WrapperSimpleApp Error: 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
jvm 1    | WrapperSimpleApp Error: 	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.Util.getInstance(Util.java:383)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
jvm 1    | WrapperSimpleApp Error: 	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
jvm 1    | WrapperSimpleApp Error: 	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
jvm 1    | WrapperSimpleApp Error: 	at org.traccar.database.QueryBuilder.executeUpdate(QueryBuilder.java:412)
jvm 1    | WrapperSimpleApp Error: 	at org.traccar.database.DataManager.initDatabaseSchema(DataManager.java:181)
jvm 1    | WrapperSimpleApp Error: 	at org.traccar.database.DataManager.<init>(DataManager.java:64)
jvm 1    | WrapperSimpleApp Error: 	at org.traccar.Context.init(Context.java:98)
jvm 1    | WrapperSimpleApp Error: 	at org.traccar.Main.main(Main.java:26)
jvm 1    | WrapperSimpleApp Error: 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
jvm 1    | WrapperSimpleApp Error: 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
jvm 1    | WrapperSimpleApp Error: 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
jvm 1    | WrapperSimpleApp Error: 	at java.lang.reflect.Method.invoke(Method.java:606)
jvm 1    | WrapperSimpleApp Error: 	at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:325)
jvm 1    | WrapperSimpleApp Error: 	at java.lang.Thread.run(Thread.java:745)
diggah9 years ago
testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
jvm 1    | WrapperSimpleApp:
jvm 1    | WrapperSimpleApp Error: Encountered an error running main:
jvm 1    | WrapperSimpleApp Error: java.lang.RuntimeException
jvm 1    | WrapperSimpleApp Error:      at org.traccar.database.DataManager.initDatabaseSchema(DataManager.java:174)
jvm 1    | WrapperSimpleApp Error:      at org.traccar.database.DataManager.<init>(DataManager.java:64)
jvm 1    | WrapperSimpleApp Error:      at org.traccar.Context.init(Context.java:98)
jvm 1    | WrapperSimpleApp Error:      at org.traccar.Main.main(Main.java:26)
jvm 1    | WrapperSimpleApp Error:      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
jvm 1    | WrapperSimpleApp Error:      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
jvm 1    | WrapperSimpleApp Error:      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
jvm 1    | WrapperSimpleApp Error:      at java.lang.reflect.Method.invoke(Method.java:606)
jvm 1    | WrapperSimpleApp Error:      at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:325)
jvm 1    | WrapperSimpleApp Error:      at java.lang.Thread.run(Thread.java:745)
wrapper  | <-- Wrapper Stopped
diggah9 years ago

Workaround is :

create new database, start traccar and copy users, devices and user devices tables data to new database.

Anton Tananaev9 years ago

There is a script for database migration:

https://www.traccar.org/update-from-3-0-to-3-1/

diggah9 years ago

Thank you!

romale9 years ago

What about 3.0 H2 DB to 3.1 H2 DB migration?
Is there how to?
Thanks

Anton Tananaev9 years ago

Unfortunately I don't have a script for H2 database, but it should be fairly easy to modify MySQL one.

rngtng9 years ago

I used this to migrate my h2 database:

ALTER TABLE `USER` alter COLUMN name VARCHAR(128) NOT NULL;
ALTER TABLE `USER` alter COLUMN email VARCHAR(128) NOT NULL;
ALTER TABLE `USER` alter COLUMN hashedPassword VARCHAR(128) NOT NULL;
ALTER TABLE `USER` alter COLUMN salt VARCHAR(128) DEFAULT '' NOT NULL;
ALTER TABLE `USER` alter COLUMN readonly BIT DEFAULT 0 NOT NULL;
ALTER TABLE `USER` alter COLUMN admin BIT DEFAULT 0 NOT NULL;
ALTER TABLE `USER` alter COLUMN map VARCHAR(128);
ALTER TABLE `USER` alter COLUMN language VARCHAR(128);
ALTER TABLE `USER` alter COLUMN distanceUnit VARCHAR(128);
ALTER TABLE `USER` alter COLUMN speedUnit VARCHAR(128);
ALTER TABLE `USER` alter COLUMN latitude FLOAT DEFAULT 0 NOT NULL;
ALTER TABLE `USER` alter COLUMN longitude FLOAT DEFAULT 0 NOT NULL;

ALTER TABLE device alter COLUMN name VARCHAR(128) NOT NULL;
ALTER TABLE device alter COLUMN uniqueId VARCHAR(128) NOT NULL;
ALTER TABLE device alter COLUMN status VARCHAR(128);

ALTER TABLE device DROP  FOREIGN KEY device_ibfk_1;
ALTER TABLE device DROP FOREIGN KEY device_ibfk_2;
ALTER TABLE device ADD FOREIGN KEY (positionId) REFERENCES position (id) ON DELETE CASCADE;
ALTER TABLE device ADD FOREIGN KEY (dataId) REFERENCES data (id) ON DELETE CASCADE;


ALTER TABLE user_device alter COLUMN `read` BIT DEFAULT 1 NOT NULL;
ALTER TABLE user_device alter COLUMN `write` BIT DEFAULT 1 NOT NULL;

ALTER TABLE position alter COLUMN protocol VARCHAR(128);
ALTER TABLE position alter COLUMN valid BIT NOT NULL;
ALTER TABLE position alter COLUMN latitude FLOAT NOT NULL;
ALTER TABLE position alter COLUMN longitude FLOAT NOT NULL;
ALTER TABLE position alter COLUMN altitude FLOAT NOT NULL;
ALTER TABLE position alter COLUMN speed FLOAT NOT NULL;
ALTER TABLE position alter COLUMN course FLOAT NOT NULL;
ALTER TABLE position alter COLUMN address VARCHAR(512);
ALTER TABLE position alter COLUMN other VARCHAR(4096) NOT NULL;

CREATE INDEX position_deviceId_fixTime ON position (deviceId, fixTime);

ALTER TABLE data alter COLUMN protocol VARCHAR(128);
ALTER TABLE data alter COLUMN other VARCHAR(4096) NOT NULL;

ALTER TABLE data DROP FOREIGN KEY data_ibfk_1;
ALTER TABLE data ADD FOREIGN KEY (deviceId) REFERENCES device (id) ON DELETE CASCADE;

ALTER TABLE server alter COLUMN registration BIT NOT NULL;
ALTER TABLE server ADD COLUMN map VARCHAR(128);
ALTER TABLE server ADD COLUMN language VARCHAR(128);
ALTER TABLE server ADD COLUMN distanceUnit VARCHAR(128);
ALTER TABLE server ADD COLUMN speedUnit VARCHAR(128);
ALTER TABLE server alter COLUMN latitude FLOAT DEFAULT 0 NOT NULL;
ALTER TABLE server alter COLUMN longitude FLOAT DEFAULT 0 NOT NULL;
ALTER TABLE server alter COLUMN zoom INT DEFAULT 0 NOT NULL;

ALTER TABLE traccar ADD COLUMN version INT DEFAULT 0 NOT NULL;
ALTER TABLE traccar DROP COLUMN id;

INSERT INTO traccar (version) VALUES (301);
Anton Tananaev9 years ago

Thanks for the script.