UPGRADE FAILED V3.8 - 4.1 MSSQL

ilevans6 years ago
I have been trying to upgrade from V3.8 to V4.1 but have not been successful.
Assistance would be greatly appreciated.
Regards
tracker-server.log file follows.
2018-10-21 12:58:19  INFO: HikariPool-1 - Starting...
2018-10-21 12:58:19  INFO: HikariPool-1 - Start completed.
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT CASE ServerProperty('EngineEdition')
         WHEN 1 THEN 'Personal'
         WHEN 2 THEN 'Standard'
         WHEN 3 THEN 'Enterprise'
         WHEN 4 THEN 'Express'
         WHEN 5 THEN 'Azure'
         ELSE 'Unknown'
       END
2018-10-21 12:58:20  INFO: Clearing database change log checksums
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT COUNT(*) FROM DATABASECHANGELOGLOCK
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT COUNT(*) FROM DATABASECHANGELOGLOCK
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT LOCKED FROM DATABASECHANGELOGLOCK WHERE ID=1
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: Successfully acquired change log lock
2018-10-21 12:58:20  INFO: SELECT CONVERT([sysname], DATABASEPROPERTYEX(N'traccar', 'Collation'))
2018-10-21 12:58:20  INFO: SELECT CASE ServerProperty('EngineEdition')
         WHEN 1 THEN 'Personal'
         WHEN 2 THEN 'Standard'
         WHEN 3 THEN 'Enterprise'
         WHEN 4 THEN 'Express'
         WHEN 5 THEN 'Azure'
         ELSE 'Unknown'
       END
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT TOP 1 MD5SUM FROM DATABASECHANGELOG WHERE MD5SUM IS NOT NULL
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = NULL
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = NULL
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: Successfully released change log lock
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT COUNT(*) FROM DATABASECHANGELOGLOCK
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT COUNT(*) FROM DATABASECHANGELOGLOCK
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: SELECT LOCKED FROM DATABASECHANGELOGLOCK WHERE ID=1
2018-10-21 12:58:20  INFO: select schema_name()
2018-10-21 12:58:20  INFO: Successfully acquired change log lock
2018-10-21 12:58:21  INFO: SELECT CASE ServerProperty('EngineEdition')
         WHEN 1 THEN 'Personal'
         WHEN 2 THEN 'Standard'
         WHEN 3 THEN 'Enterprise'
         WHEN 4 THEN 'Express'
         WHEN 5 THEN 'Azure'
         ELSE 'Unknown'
       END
2018-10-21 12:58:21  INFO: select schema_name()
2018-10-21 12:58:21  INFO: SELECT TOP 1 MD5SUM FROM DATABASECHANGELOG WHERE MD5SUM IS NOT NULL
2018-10-21 12:58:21  INFO: select schema_name()
2018-10-21 12:58:21  INFO: select schema_name()
2018-10-21 12:58:21  INFO: SELECT COUNT(*) FROM DATABASECHANGELOG
2018-10-21 12:58:22  INFO: Reading from DATABASECHANGELOG
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = '8:ea6f202534dc7845fd1a49e1d8384d9c' WHERE ID = 'changelog-3.3' AND AUTHOR = 'author' AND FILENAME = 'changelog-3.3'
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = '8:194e3ae7ec6d201917136e04a26e237b' WHERE ID = 'changelog-3.5' AND AUTHOR = 'author' AND FILENAME = 'changelog-3.5'
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = '8:1ec7941b03749f24e365bc6126b07e1a' WHERE ID = 'changelog-3.6' AND AUTHOR = 'author' AND FILENAME = 'changelog-3.6'
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = '8:2e444e379f31d0b8e09273ec7ffe87a0' WHERE ID = 'changelog-3.7' AND AUTHOR = 'author' AND FILENAME = 'changelog-3.7'
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = '8:974c33d2fb399ef6477c3897450fb078' WHERE ID = 'changelog-3.7-notmssql' AND AUTHOR = 'author' AND FILENAME = 'changelog-3.7'
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: UPDATE DATABASECHANGELOG SET MD5SUM = '8:74d68027951f8d2ae6bb2e24df8365bc' WHERE ID = 'changelog-3.8' AND AUTHOR = 'author' AND FILENAME = 'changelog-3.8'
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: SELECT COUNT(*) FROM DATABASECHANGELOG
2018-10-21 12:58:22  INFO: Reading from DATABASECHANGELOG
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2018-10-21 12:58:22  INFO: SELECT CASE ServerProperty('EngineEdition')
         WHEN 1 THEN 'Personal'
         WHEN 2 THEN 'Standard'
         WHEN 3 THEN 'Enterprise'
         WHEN 4 THEN 'Express'
         WHEN 5 THEN 'Azure'
         ELSE 'Unknown'
       END
2018-10-21 12:58:22  INFO: Marking ChangeSet: changelog-4.0-clean::changelog-4.0-clean::author ran despite precondition failure due to onFail='MARK_RAN': 
          ./schema/changelog-master.xml : Not precondition failed

2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('changelog-4.0-clean', 'author', 'changelog-4.0-clean', GETDATE(), 19, '8:76b4849d11b000f0cb35bb8148e706f8', 'createTable tableName=tc_attributes; createTable tableName=tc_calendars; createTable tableName=tc_commands; createTable tableName=tc_device_attribute; createTable tableName=tc_device_command; createTable tableName=tc_device_driver; createTable tab...', '', 'MARK_RAN', NULL, NULL, '3.6.2', '0090702111')
2018-10-21 12:58:22  INFO: Marking ChangeSet: changelog-4.0-clean::changelog-4.0-clean-common::author ran despite precondition failure due to onFail='MARK_RAN': 
          ./schema/changelog-master.xml : Not precondition failed

2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('changelog-4.0-clean-common', 'author', 'changelog-4.0-clean', GETDATE(), 20, '8:1adabae3238e02ccc97cc422359a58a0', 'addForeignKeyConstraint baseTableName=tc_groups, constraintName=fk_groups_groupid, referencedTableName=tc_groups; addForeignKeyConstraint baseTableName=tc_user_user, constraintName=fk_user_user_manageduserid, referencedTableName=tc_users', '', 'MARK_RAN', NULL, NULL, '3.6.2', '0090702111')
2018-10-21 12:58:22  INFO: IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tg_groups_delete')
      BEGIN
      DROP TRIGGER tg_groups_delete
      END
2018-10-21 12:58:22  INFO: Custom SQL executed
2018-10-21 12:58:22  INFO: CREATE TRIGGER tg_groups_delete
      ON tc_groups FOR DELETE
      AS BEGIN
      UPDATE tc_groups SET groupid = NULL WHERE groupid IN (SELECT deleted.id FROM deleted)
      END
2018-10-21 12:58:22 ERROR: Change Set changelog-4.0-clean::changelog-4.0-clean-mssql::author failed.  Error: The object 'tc_groups' does not exist or is invalid for this operation. [Failed SQL: CREATE TRIGGER tg_groups_delete
      ON tc_groups FOR DELETE
      AS BEGIN
      UPDATE tc_groups SET groupid = NULL WHERE groupid IN (SELECT deleted.id FROM deleted)
      END]
2018-10-21 12:58:22  INFO: select schema_name()
2018-10-21 12:58:22  INFO: Successfully released change log lock
2018-10-21 12:58:22 ERROR: Main method error - Migration failed for change set changelog-4.0-clean::changelog-4.0-clean-mssql::author:
     Reason: liquibase.exception.DatabaseException: The object 'tc_groups' does not exist or is invalid for this operation. [Failed SQL: CREATE TRIGGER tg_groups_delete
      ON tc_groups FOR DELETE
      AS BEGIN
      UPDATE tc_groups SET groupid = NULL WHERE groupid IN (SELECT deleted.id FROM deleted)
      END] - MigrationFailedException (... < DataManager:306 < *:91 < Context:382 < Main:110 < ...)
Anton Tananaev6 years ago
Please try with the following fix:
https://github.com/traccar/traccar/commit/6f03d2da1bfb610eb7b572cfdf008893e159da7e
ilevans6 years ago
Hi Anton
Excellent, the fix worked a treat.
Many thanks
Ian