My SQL V8

Hi,
It appears that "groups" is now a reserved word as of MYSQL 8.0.2 and so when you do an initial install of Traccar on a MySQL database it fails half way through creating the tables as groups needs to be specified as groups.
Is there a fix for this or just run an older version of MySQL?
Anton Tananaev7 years ago
I'm just shocked by the pace they introduce new keywords:
https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-G
We just fixed the problem with "admin" and now this.
Anton Tananaev7 years ago
Can you provide wrapper log?
Dorian Woolger7 years ago
Hi, I'm afraid that log is gone now. When I get a moment, I will reproduce for you. Whenever I do MySQL dev (and its been a while) I just got into the habbit of always using the back-ticks, that way there was never an issue.
Anton Tananaev7 years ago
The problem is that MySQL is not the only database that we need to support. Other databases don't support back-ticks.
Dorian Woolger7 years ago
WARNING|wrapper|Service traccar|18-06-09 22:44:00|YAJSW: yajsw-stable-12.12
WARNING|wrapper|Service traccar|18-06-09 22:44:00|OS   : Windows 8.1/6.3/amd64
WARNING|wrapper|Service traccar|18-06-09 22:44:00|JVM  : Oracle Corporation/1.8.0_151/C:\Program Files\Java\jre1.8.0_151/64
INFO|wrapper|Service traccar|18-06-09 22:44:00|start delay: 0
INFO|wrapper|Service traccar|18-06-09 22:44:00|started process with pid 1608
INFO|1608/0|Service traccar|18-06-09 22:44:01|[INFO] StandardFileSystemManager - Using "C:\Windows\TEMP\vfs_cache" as temporary files store.
INFO|1608/0|Service traccar|18-06-09 22:44:01|SLF4J: Class path contains multiple SLF4J bindings.
INFO|1608/0|Service traccar|18-06-09 22:44:01|SLF4J: Found binding in [jar:file:/C:/Program%20Files%20(x86)/Traccar/lib/slf4j-simple-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO|1608/0|Service traccar|18-06-09 22:44:01|SLF4J: Found binding in [jar:file:/C:/Program%20Files%20(x86)/Traccar/lib/logback-classic-1.2.3.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO|1608/0|Service traccar|18-06-09 22:44:01|SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
INFO|1608/0|Service traccar|18-06-09 22:44:01|SLF4J: Actual binding is of type [org.slf4j.impl.SimpleLoggerFactory]
INFO|1608/0|Service traccar|18-06-09 22:44:02|[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
INFO|1608/0|Service traccar|18-06-09 22:44:02|[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.Liquibase - Clearing database change log checksums
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BIT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - DELETE FROM traccar.DATABASECHANGELOGLOCK
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO traccar.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0)
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT LOCKED FROM traccar.DATABASECHANGELOGLOCK WHERE ID=1
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.lockservice.StandardLockService - Successfully acquired change log lock
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.changelog.StandardChangeLogHistoryService - Creating database history table with name: traccar.DATABASECHANGELOG
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35) NULL, DESCRIPTION VARCHAR(255) NULL, COMMENTS VARCHAR(255) NULL, TAG VARCHAR(255) NULL, LIQUIBASE VARCHAR(20) NULL, CONTEXTS VARCHAR(255) NULL, LABELS VARCHAR(255) NULL, DEPLOYMENT_ID VARCHAR(10) NULL)
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - UPDATE traccar.DATABASECHANGELOG SET MD5SUM = NULL
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.lockservice.StandardLockService - Successfully released change log lock
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT COUNT(*) FROM traccar.DATABASECHANGELOGLOCK
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT LOCKED FROM traccar.DATABASECHANGELOGLOCK WHERE ID=1
INFO|1608/0|Service traccar|18-06-09 22:44:03|[main] INFO liquibase.lockservice.StandardLockService - Successfully acquired change log lock
INFO|1608/0|Service traccar|18-06-09 22:44:05|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT MD5SUM FROM traccar.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL LIMIT 1
INFO|1608/0|Service traccar|18-06-09 22:44:05|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT COUNT(*) FROM traccar.DATABASECHANGELOG
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.changelog.StandardChangeLogHistoryService - Reading from traccar.DATABASECHANGELOG
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT * FROM traccar.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.users (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(128) NOT NULL, email VARCHAR(128) NOT NULL, hashedpassword VARCHAR(128) NOT NULL, salt VARCHAR(128) NOT NULL, readonly BIT(1) DEFAULT 0 NOT NULL, administrator BIT(1) DEFAULT 0 NOT NULL, map VARCHAR(128) DEFAULT 'osm' NOT NULL, language VARCHAR(128) DEFAULT 'en' NOT NULL, distanceunit VARCHAR(128) DEFAULT 'km' NOT NULL, speedunit VARCHAR(128) DEFAULT 'kmh' NOT NULL, latitude DOUBLE DEFAULT 0 NOT NULL, longitude DOUBLE DEFAULT 0 NOT NULL, zoom INT DEFAULT 0 NOT NULL, CONSTRAINT PK_USERS PRIMARY KEY (id))
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.changelog.ChangeSet - Table users created
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users ADD CONSTRAINT uk_user_email UNIQUE (email)
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.changelog.ChangeSet - Unique constraint added to users(email)
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.devices (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(128) NOT NULL, uniqueid VARCHAR(128) NOT NULL, status VARCHAR(128) NULL, lastupdate timestamp NULL, positionid INT NULL, CONSTRAINT PK_DEVICES PRIMARY KEY (id))
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.changelog.ChangeSet - Table devices created
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.devices ADD CONSTRAINT uk_device_uniqueid UNIQUE (uniqueid)
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.changelog.ChangeSet - Unique constraint added to devices(uniqueid)
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.user_device (userid INT NOT NULL, deviceid INT NOT NULL)
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.changelog.ChangeSet - Table user_device created
INFO|1608/0|Service traccar|18-06-09 22:44:06|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.user_device ADD CONSTRAINT fk_user_device_userid FOREIGN KEY (userid) REFERENCES traccar.users (id) ON DELETE CASCADE
INFO|1608/0|Service traccar|18-06-09 22:44:07|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to user_device (userid)
INFO|1608/0|Service traccar|18-06-09 22:44:07|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.user_device ADD CONSTRAINT fk_user_device_deviceid FOREIGN KEY (deviceid) REFERENCES traccar.devices (id) ON DELETE CASCADE
INFO|1608/0|Service traccar|18-06-09 22:44:07|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to user_device (deviceid)
INFO|1608/0|Service traccar|18-06-09 22:44:07|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE INDEX user_device_user_id ON traccar.user_device(userid)
INFO|1608/0|Service traccar|18-06-09 22:44:07|[main] INFO liquibase.changelog.ChangeSet - Index user_device_user_id created
INFO|1608/0|Service traccar|18-06-09 22:44:07|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.positions (id INT AUTO_INCREMENT NOT NULL, protocol VARCHAR(128) NULL, deviceid INT NOT NULL, servertime timestamp NOT NULL, devicetime timestamp NOT NULL, fixtime timestamp NOT NULL, valid BIT(1) NOT NULL, latitude DOUBLE NOT NULL, longitude DOUBLE NOT NULL, altitude FLOAT NOT NULL, speed FLOAT NOT NULL, course FLOAT NOT NULL, address VARCHAR(512) NULL, attributes VARCHAR(4096) NOT NULL, CONSTRAINT PK_POSITIONS PRIMARY KEY (id))
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - Table positions created
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.positions ADD CONSTRAINT fk_position_deviceid FOREIGN KEY (deviceid) REFERENCES traccar.devices (id) ON DELETE CASCADE
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to positions (deviceid)
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE INDEX position_deviceid_fixtime ON traccar.positions(deviceid, fixtime)
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - Index position_deviceid_fixtime created
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.server (id INT AUTO_INCREMENT NOT NULL, registration BIT(1) DEFAULT 1 NOT NULL, latitude DOUBLE DEFAULT 0 NOT NULL, longitude DOUBLE DEFAULT 0 NOT NULL, zoom INT DEFAULT 0 NOT NULL, map VARCHAR(128) NULL, language VARCHAR(128) NULL, distanceunit VARCHAR(128) NULL, speedunit VARCHAR(128) NULL, bingkey VARCHAR(128) NULL, mapurl VARCHAR(128) NULL, readonly BIT(1) DEFAULT 0 NOT NULL, CONSTRAINT PK_SERVER PRIMARY KEY (id))
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - Table server created
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO traccar.server (registration, latitude, longitude, zoom) VALUES (1, 0, 0, 0)
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - New row inserted into server
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO traccar.users (name, email, hashedpassword, salt, administrator) VALUES ('admin', 'admin', 'D33DCA55ABD4CC5BC76F2BC0B4E603FE2C6F61F4C1EF2D47', '000000000000000000000000000000000000000000000000', 1)
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - New row inserted into users
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - ChangeSet changelog-3.3::changelog-3.3::author ran successfully in 2312ms
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT MAX(ORDEREXECUTED) FROM traccar.DATABASECHANGELOG
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO traccar.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('changelog-3.3', 'author', 'changelog-3.3', NOW(), 1, '8:ea6f202534dc7845fd1a49e1d8384d9c', 'createTable tableName=users; addUniqueConstraint constraintName=uk_user_email, tableName=users; createTable tableName=devices; addUniqueConstraint constraintName=uk_device_uniqueid, tableName=devices; createTable tableName=user_device; addForeignK...', '', 'EXECUTED', NULL, NULL, '3.6.1', '8580645774')
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.groups (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(128) NOT NULL, groupid INT NULL, CONSTRAINT PK_GROUPS PRIMARY KEY (id))
INFO|1608/0|Service traccar|18-06-09 22:44:08|[main] INFO liquibase.changelog.ChangeSet - Table groups created
INFO|1608/0|Service traccar|18-06-09 22:44:09|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.user_group (userid INT NOT NULL, groupid INT NOT NULL)
INFO|1608/0|Service traccar|18-06-09 22:44:09|[main] INFO liquibase.changelog.ChangeSet - Table user_group created
INFO|1608/0|Service traccar|18-06-09 22:44:09|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.user_group ADD CONSTRAINT fk_user_group_userid FOREIGN KEY (userid) REFERENCES traccar.users (id) ON DELETE CASCADE
INFO|1608/0|Service traccar|18-06-09 22:44:09|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to user_group (userid)
INFO|1608/0|Service traccar|18-06-09 22:44:09|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.user_group ADD CONSTRAINT fk_user_group_groupid FOREIGN KEY (groupid) REFERENCES traccar.groups (id) ON DELETE CASCADE
INFO|1608/0|Service traccar|18-06-09 22:44:09|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to user_group (groupid)
INFO|1608/0|Service traccar|18-06-09 22:44:09|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.devices ADD groupid INT NULL
INFO|1608/0|Service traccar|18-06-09 22:44:10|[main] INFO liquibase.changelog.ChangeSet - Columns groupid(INT) added to devices
INFO|1608/0|Service traccar|18-06-09 22:44:10|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users MODIFY language VARCHAR(128) NULL
INFO|1608/0|Service traccar|18-06-09 22:44:10|[main] INFO liquibase.changelog.ChangeSet - Null constraint dropped from users.language
INFO|1608/0|Service traccar|18-06-09 22:44:11|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users ALTER language DROP DEFAULT
INFO|1608/0|Service traccar|18-06-09 22:44:11|[main] INFO liquibase.changelog.ChangeSet - Default value dropped from users.language
INFO|1608/0|Service traccar|18-06-09 22:44:11|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users DROP COLUMN language
INFO|1608/0|Service traccar|18-06-09 22:44:11|[main] INFO liquibase.changelog.ChangeSet - Column users.language dropped
INFO|1608/0|Service traccar|18-06-09 22:44:11|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.server DROP COLUMN language
INFO|1608/0|Service traccar|18-06-09 22:44:12|[main] INFO liquibase.changelog.ChangeSet - Column server.language dropped
INFO|1608/0|Service traccar|18-06-09 22:44:12|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users MODIFY map VARCHAR(128) NULL
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.changelog.ChangeSet - Null constraint dropped from users.map
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users ALTER map DROP DEFAULT
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.changelog.ChangeSet - Default value dropped from users.map
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users MODIFY distanceunit VARCHAR(128) NULL
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.changelog.ChangeSet - Null constraint dropped from users.distanceunit
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users ALTER distanceunit DROP DEFAULT
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.changelog.ChangeSet - Default value dropped from users.distanceunit
INFO|1608/0|Service traccar|18-06-09 22:44:13|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users MODIFY speedunit VARCHAR(128) NULL
INFO|1608/0|Service traccar|18-06-09 22:44:14|[main] INFO liquibase.changelog.ChangeSet - Null constraint dropped from users.speedunit
INFO|1608/0|Service traccar|18-06-09 22:44:14|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users ALTER speedunit DROP DEFAULT
INFO|1608/0|Service traccar|18-06-09 22:44:14|[main] INFO liquibase.changelog.ChangeSet - Default value dropped from users.speedunit
INFO|1608/0|Service traccar|18-06-09 22:44:14|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users ADD twelvehourformat BIT(1) DEFAULT 0 NOT NULL
INFO|1608/0|Service traccar|18-06-09 22:44:14|[main] INFO liquibase.changelog.ChangeSet - Columns twelvehourformat(BOOLEAN) added to users
INFO|1608/0|Service traccar|18-06-09 22:44:14|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.server ADD twelvehourformat BIT(1) DEFAULT 0 NOT NULL
INFO|1608/0|Service traccar|18-06-09 22:44:15|[main] INFO liquibase.changelog.ChangeSet - Columns twelvehourformat(BOOLEAN) added to server
INFO|1608/0|Service traccar|18-06-09 22:44:15|[main] INFO liquibase.changelog.ChangeSet - ChangeSet changelog-3.5::changelog-3.5::author ran successfully in 7048ms
INFO|1608/0|Service traccar|18-06-09 22:44:15|[main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO traccar.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('changelog-3.5', 'author', 'changelog-3.5', NOW(), 2, '8:194e3ae7ec6d201917136e04a26e237b', 'createTable tableName=groups; createTable tableName=user_group; addForeignKeyConstraint baseTableName=user_group, constraintName=fk_user_group_userid, referencedTableName=users; addForeignKeyConstraint baseTableName=user_group, constraintName=fk_u...', '', 'EXECUTED', NULL, NULL, '3.6.1', '8580645774')
INFO|1608/0|Service traccar|18-06-09 22:44:15|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.events (id INT AUTO_INCREMENT NOT NULL, type VARCHAR(128) NOT NULL, servertime timestamp NOT NULL, deviceid INT NULL, positionid INT NULL, geofenceid INT NULL, attributes VARCHAR(4096) NOT NULL, CONSTRAINT PK_EVENTS PRIMARY KEY (id))
INFO|1608/0|Service traccar|18-06-09 22:44:15|[main] INFO liquibase.changelog.ChangeSet - Table events created
INFO|1608/0|Service traccar|18-06-09 22:44:15|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.events ADD CONSTRAINT fk_event_deviceid FOREIGN KEY (deviceid) REFERENCES traccar.devices (id) ON DELETE CASCADE
INFO|1608/0|Service traccar|18-06-09 22:44:16|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to events (deviceid)
INFO|1608/0|Service traccar|18-06-09 22:44:16|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.devices ADD motion VARCHAR(128) NULL
FINEST|1608/0|Service traccar|18-06-09 22:44:16|[main] INFO liquibase.changelog.ChangeSet - Columns motion(VARCHAR(128)) added to devices
FINEST|1608/0|Service traccar|18-06-09 22:44:16|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.geofences (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(128) NOT NULL, description VARCHAR(128) NULL, area VARCHAR(4096) NOT NULL, attributes VARCHAR(4096) NOT NULL, CONSTRAINT PK_GEOFENCES PRIMARY KEY (id))
FINEST|1608/0|Service traccar|18-06-09 22:44:16|[main] INFO liquibase.changelog.ChangeSet - Table geofences created
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.user_geofence (userid INT NOT NULL, geofenceid INT NOT NULL)
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.changelog.ChangeSet - Table user_geofence created
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.user_geofence ADD CONSTRAINT fk_user_geofence_userid FOREIGN KEY (userid) REFERENCES traccar.users (id) ON DELETE CASCADE
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to user_geofence (userid)
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.user_geofence ADD CONSTRAINT fk_user_geofence_geofenceid FOREIGN KEY (geofenceid) REFERENCES traccar.geofences (id) ON DELETE CASCADE
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to user_geofence (geofenceid)
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.group_geofence (groupid INT NOT NULL, geofenceid INT NOT NULL)
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.changelog.ChangeSet - Table group_geofence created
FINEST|1608/0|Service traccar|18-06-09 22:44:17|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.group_geofence ADD CONSTRAINT fk_group_geofence_groupid FOREIGN KEY (groupid) REFERENCES traccar.groups (id) ON DELETE CASCADE
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to group_geofence (groupid)
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.group_geofence ADD CONSTRAINT fk_group_geofence_geofenceid FOREIGN KEY (geofenceid) REFERENCES traccar.geofences (id) ON DELETE CASCADE
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to group_geofence (geofenceid)
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.device_geofence (deviceid INT NOT NULL, geofenceid INT NOT NULL)
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.changelog.ChangeSet - Table device_geofence created
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.device_geofence ADD CONSTRAINT fk_user_device_geofence_deviceid FOREIGN KEY (deviceid) REFERENCES traccar.devices (id) ON DELETE CASCADE
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to device_geofence (deviceid)
FINEST|1608/0|Service traccar|18-06-09 22:44:18|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.device_geofence ADD CONSTRAINT fk_user_device_geofence_geofenceid FOREIGN KEY (geofenceid) REFERENCES traccar.geofences (id) ON DELETE CASCADE
FINEST|1608/0|Service traccar|18-06-09 22:44:19|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to device_geofence (geofenceid)
FINEST|1608/0|Service traccar|18-06-09 22:44:20|[main] INFO liquibase.executor.jvm.JdbcExecutor - CREATE TABLE traccar.notifications (id INT AUTO_INCREMENT NOT NULL, userid INT NOT NULL, type VARCHAR(128) NOT NULL, attributes VARCHAR(4096) NOT NULL, CONSTRAINT PK_NOTIFICATIONS PRIMARY KEY (id))
FINEST|1608/0|Service traccar|18-06-09 22:44:20|[main] INFO liquibase.changelog.ChangeSet - Table notifications created
FINEST|1608/0|Service traccar|18-06-09 22:44:20|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.notifications ADD CONSTRAINT fk_notifications_userid FOREIGN KEY (userid) REFERENCES traccar.users (id) ON DELETE CASCADE
FINEST|1608/0|Service traccar|18-06-09 22:44:20|[main] INFO liquibase.changelog.ChangeSet - Foreign key constraint added to notifications (userid)
FINEST|1608/0|Service traccar|18-06-09 22:44:20|[main] INFO liquibase.executor.jvm.JdbcExecutor - ALTER TABLE traccar.users ADD attributes VARCHAR(4096) NULL
FINEST|1608/0|Service traccar|18-06-09 22:44:21|[main] INFO liquibase.changelog.ChangeSet - Columns attributes(VARCHAR(4096)) added to users
FINEST|1608/0|Service traccar|18-06-09 22:44:21|[main] INFO liquibase.changelog.ChangeSet - ChangeSet changelog-3.6::changelog-3.6::author ran successfully in 5360ms
FINEST|1608/0|Service traccar|18-06-09 22:44:21|[main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO traccar.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('changelog-3.6', 'author', 'changelog-3.6', NOW(), 3, '8:1ec7941b03749f24e365bc6126b07e1a', 'createTable tableName=events; addForeignKeyConstraint baseTableName=events, constraintName=fk_event_deviceid, referencedTableName=devices; addColumn tableName=devices; createTable tableName=geofences; createTable tableName=user_geofence; addForeig...', '', 'EXECUTED', NULL, NULL, '3.6.1', '8580645774')
FINEST|1608/0|Service traccar|18-06-09 22:44:21|[main] INFO liquibase.executor.jvm.JdbcExecutor - UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|[main] ERROR liquibase.changelog.ChangeSet - Change Set changelog-3.7::changelog-3.7::author failed.  Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups)' at line 1 [Failed SQL: UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups)]
FINEST|1608/0|Service traccar|18-06-09 22:44:21|[main] INFO liquibase.lockservice.StandardLockService - Successfully released change log lock
FINEST|1608/0|Service traccar|18-06-09 22:44:21|java.lang.reflect.InvocationTargetException
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at java.lang.reflect.Method.invoke(Unknown Source)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at org.rzo.yajsw.app.WrapperJVMMain.executeMain(WrapperJVMMain.java:60)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at org.rzo.yajsw.app.WrapperJVMMain.main(WrapperJVMMain.java:43)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set changelog-3.7::changelog-3.7::author:
FINEST|1608/0|Service traccar|18-06-09 22:44:21|     Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups)' at line 1 [Failed SQL: UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups)]
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:78)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.Liquibase.update(Liquibase.java:202)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.Liquibase.update(Liquibase.java:179)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.Liquibase.update(Liquibase.java:175)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at org.traccar.database.DataManager.initDatabaseSchema(DataManager.java:306)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at org.traccar.database.DataManager.<init>(DataManager.java:88)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at org.traccar.Context.init(Context.java:353)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at org.traccar.Main.main(Main.java:35)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	... 6 more
FINEST|1608/0|Service traccar|18-06-09 22:44:21|Caused by: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups)' at line 1 [Failed SQL: UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups)]
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:356)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1211)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	... 15 more
FINEST|1608/0|Service traccar|18-06-09 22:44:21|Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups)' at line 1
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at java.lang.reflect.Constructor.newInstance(Unknown Source)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.Util.getInstance(Util.java:408)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2440)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
FINEST|1608/0|Service traccar|18-06-09 22:44:21|	... 20 more
INFO|wrapper|Service traccar|18-06-09 22:44:21|calling onStop
INFO|wrapper|Service traccar|18-06-09 22:44:21|Win service stop - timeout: 30000
INFO|wrapper|Service traccar|18-06-09 22:44:21|Win service wrapper.control -> stopping application
INFO|wrapper|Service traccar|18-06-09 22:44:21|Win service stop - after shutdown
INFO|wrapper|Service traccar|18-06-09 22:44:21|Win service stop - before notify
INFO|wrapper|Service traccar|18-06-09 22:44:21|Win service terminated
Anton Tananaev7 years ago
Thanks. We already implemented a solution:
https://github.com/traccar/traccar/pull/3919
Dorian Woolger7 years ago
Wow, now thats quick support. Many thanks.