I came pretty far, but it broke at the statement:
UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups);
The reason being that the table name needs the [schema/database].[table] notation.
UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM traccar.groups);
works fine. I am now somewhere between heaven (successful completion) and hell (failure) during the creation and preloading of the database.
I found as stated in the log:
...
FINEST|20916/0|Service traccar|18-07-27 16:05:00|[main] INFO liquibase.executor.jvm.JdbcExecutor - UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups)
FINEST|20916/0|Service traccar|18-07-27 16:05:00|[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|20916/0|Service traccar|18-07-27 16:05:00|[main] INFO liquibase.lockservice.StandardLockService - Successfully released change log lock
FINEST|20916/0|Service traccar|18-07-27 16:05:00|java.lang.reflect.InvocationTargetException
FINEST|20916/0|Service traccar|18-07-27 16:05:00| at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
FINEST|20916/0|Service traccar|18-07-27 16:05:00| at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
FINEST|20916/0|Service traccar|18-07-27 16:05:00| at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
FINEST|20916/0|Service traccar|18-07-27 16:05:00| at java.lang.reflect.Method.invoke(Unknown Source)
FINEST|20916/0|Service traccar|18-07-27 16:05:00| at org.rzo.yajsw.app.WrapperJVMMain.executeMain(WrapperJVMMain.java:60)
FINEST|20916/0|Service traccar|18-07-27 16:05:00| at org.rzo.yajsw.app.WrapperJVMMain.main(WrapperJVMMain.java:43)
FINEST|20916/0|Service traccar|18-07-27 16:05:00|Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set changelog-3.7::changelog-3.7::author:
...
the statement but in a format unbeknownst to me:
...
<changeSet author="author" id="changelog-3.7">
<update tableName="devices">
<column name="groupid"/>
<where>groupid NOT IN (SELECT id FROM groups)</where>
</update>
<addForeignKeyConstraint baseColumnNames="groupid" baseTableName="devices" constraintName="fk_device_group_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="groups"/>
<update tableName="groups">
<column name="groupid"/>
<where>groupid NOT IN (SELECT id FROM (SELECT DISTINCT id FROM groups) AS groups_ids)</where>
</update>
...
I don't know how this worked with others. Maybe I have some settings for MySQL to be strict in the sql rules requiring <schema>.<table>notation.
Is there a way to create the complete db set up script having the change sets applied, so that they can be run 'edited' or is there a switch in the mysql interpreter to loosen the rules.
guenter
What are you trying to do? Just use MySQL with official version of Traccar?
What version of Traccar are you using?
I did; it ran for a month on H2 and I decided to change the DB.
It failed when autocreating the empty mySql database.
Software landscape:
I am using the 8.0.11 community edition of mySQL with a standard installation
and
2018-07-27 16:04:44 INFO: Operating system name: Windows 10 version: 10.0 architecture: amd64
2018-07-27 16:04:44 INFO: Java runtime name: Java HotSpot(TM) 64-Bit Server VM vendor: Oracle Corporation version: 25.181-b13
2018-07-27 16:04:44 INFO: Memory limit heap: 5458mb non-heap: 0mb
2018-07-27 16:04:44 INFO: Character encoding: UTF-8 charset: UTF-8
2018-07-27 16:04:44 INFO: Version: 3.17-SNAPSHOT
Yes, now it succeeded. Great! Should this not be a note on the 'migrate to mysql' page?
Next release should fix the problem with new version of MySQL, so it will be working soon.
That's great. Thank you very much! Is there a planned release date?
No, but hopefully within next few weeks.
I solved the problem by changing the changelog-3.7.xml file, which is in the schema directory, informing the name of the database before the groups table, as below:
...
groupid NOT IN (SELECT id FROM ***databasename.***groups)
...
groupid NOT IN (SELECT id FROM (SELECT DISTINCT id FROM ***databasename.***groups) AS groups_ids)
...
Yeah, the schema was missing. I did not want to make manual changes in my version since I would introduce possible inconsistencies in later versions.
I heard, that the issue has been taken care of in V4, but I am too scary-cat to take the jump now! In production environments, we do generally wait for the first Service packs to come out before we go through whole number version updates.
I came pretty far, but it broke at the statement:
UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups);The reason being that the table name needs the [schema/database].[table] notation.
UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM traccar.groups);works fine. I am now somewhere between heaven (successful completion) and hell (failure) during the creation and preloading of the database.
I found as stated in the log:
the statement but in a format unbeknownst to me:
I don't know how this worked with others. Maybe I have some settings for MySQL to be strict in the sql rules requiring <schema>.<table>notation.
Is there a way to create the complete db set up script having the change sets applied, so that they can be run 'edited' or is there a switch in the mysql interpreter to loosen the rules.
guenter