3.0 > 3.1 Wrong database schema version

diggah 10 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)
diggah 10 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)
diggah 10 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
diggah 10 years ago

Workaround is :

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

Anton Tananaev 10 years ago

There is a script for database migration:

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

diggah 10 years ago

Thank you!

romale 10 years ago

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

Anton Tananaev 10 years ago

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

rngtng 10 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 Tananaev 10 years ago

Thanks for the script.