So close but no cigar. Syntax errors when converting to mySQL

guenter strubinsky 7 years ago

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:

...
&lt;changeSet author="author" id="changelog-3.7"&gt;
    &lt;update tableName="devices"&gt;
      &lt;column name="groupid"/&gt;
      &lt;where&gt;groupid NOT IN (SELECT id FROM groups)&lt;/where&gt;
    &lt;/update&gt;
    &lt;addForeignKeyConstraint baseColumnNames="groupid" baseTableName="devices" constraintName="fk_device_group_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="groups"/&gt;
    &lt;update tableName="groups"&gt;
      &lt;column name="groupid"/&gt;
      &lt;where&gt;groupid NOT IN (SELECT id FROM (SELECT DISTINCT id FROM groups) AS groups_ids)&lt;/where&gt;
    &lt;/update&gt;
...

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

Anton Tananaev 7 years ago

What are you trying to do? Just use MySQL with official version of Traccar?

What version of Traccar are you using?

guenter strubinsky 7 years ago

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

Anton Tananaev 7 years ago

Use latest MySQL 5.7.

guenter strubinsky 7 years ago

you mean I should use an older mySQL version?

NOT 8.0.11 as in https://dev.mysql.com/downloads/

Anton Tananaev 7 years ago

Yes.

guenter strubinsky 7 years ago

Yes, now it succeeded. Great! Should this not be a note on the 'migrate to mysql' page?

Anton Tananaev 7 years ago

Next release should fix the problem with new version of MySQL, so it will be working soon.

guenter strubinsky 7 years ago

That's great. Thank you very much! Is there a planned release date?

Anton Tananaev 7 years ago

No, but hopefully within next few weeks.

Daniel Tôrres 7 years ago

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)
...

guenter strubinsky 7 years ago

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.