V4 mysql startup/creation error

INFO|20216/0|Service traccar|18-09-10 00:37:32|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT COUNT(*) FROM traccar.DATABASECHANGELOG
INFO|20216/0|Service traccar|18-09-10 00:37:32|[main] INFO liquibase.changelog.StandardChangeLogHistoryService - Reading from traccar.DATABASECHANGELOG
INFO|20216/0|Service traccar|18-09-10 00:37:32|[main] INFO liquibase.executor.jvm.JdbcExecutor - SELECT * FROM traccar.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
INFO|20216/0|Service traccar|18-09-10 00:37:32|[main] INFO liquibase.executor.jvm.JdbcExecutor - UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups)
INFO|20216/0|Service traccar|18-09-10 00:37:32|[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)]

the 'groups' table wants a database associated. If you chose 'traccar as the database the statement
UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM groups) should be
UPDATE traccar.devices SET groupid = NULL WHERE groupid NOT IN (SELECT id FROM traccar.groups)

Since I don't have the resources to analyse where the statement is created, do I have to reinstall completely after the bug is fixed or is there a 'nicer' way. The server goes down and even after having ran the statement successfully manually, the generated statement is called again and fails again and the server goes down again...

Especially since this is a new install and there are no devices yet in the first place.

Anton Tananaev6 years ago

The issue is not with statement. It's with new MySQL version. Use version 5.7.x.

guenter strubinsky6 years ago

I read on this site that V4 adapted to mysql 8. Was I wrong?

Anton Tananaev6 years ago

This is a relevant thread:

https://github.com/traccar/traccar/issues/4035

We have updated names, but migration history is still there with old names.

guenter strubinsky6 years ago

Why does the system fail again?
Does it not first create the tables and then runs those updates? If that were so, an option could be not to abort in this phase, but fail while logging the failure. We could run those updates manually.

This update stmt is any way redundant at this stage and could be left out completely since all tables should be empty.

Is there a way to cheat the system in believing all's well and not run the same script and fail continuously? I just upgraded to V4 and a clean database slate which invited to upgrade the database engine as well.

Anton Tananaev6 years ago

Your questions indicate lack of understanding how it works. If you want to contribute with some suggestions, please take time to understand how Liquibase migration work.

At the moment it's not possible to do anything, unless you go and manually edit all changelog files.

guenter strubinsky6 years ago

You are correct, I do not know how liquibase works, but as dba I know how one creates clones of a database and how to use the respective tools like the MySQL Workbench.

Still not a liquibase expert, I can say that there are free and open source tools that extract the metadata of a database, even seed data and create the DDL as well as the insert statements to clone the original. The cheapest and easiest form of duplication is a backup of an existing working instance of the database in question and allowing us to restore that database into our environment. That does not require the additional expertise in liquibase to have a brand-spanking-new complete and intact working copy of the latest version that was produced (and updated). As long as table names and column names and types are not changed (which would break all our software accessing those), all is well for clean installations.

If you have a working instance of an empty but complete database, would you please create a dump through the MySQL Workbench (V8) of the database like I did with the objects and data that were created until it broke?

Here (obviously incomplete as a result of the abort in the middle & therefore ONLY AS A POC): https://drive.google.com/file/d/1M0dQR56eWXanyqu7ooJSZJMRvuPSGc1-/view?usp=sharing

I and others -as I am sure- would very much appreciate this as a temporary or semipermanent solution to having to wait or learn liquibase.

I don't want to install 5.7 just to get the tables (port conflicts aso.), but I guess if you guys don't have time for that I can run an install on 5.7, dump it, load it into 8, check for completeness and dump again in v8 just to be sure and leave it out for everyone.

Please let me know if you prefer that.

Thank you

Anton Tananaev6 years ago

Unfortunately I don't have time. Please share your backup if you manage to get it.

guenter strubinsky6 years ago

I consider this beta since I have not tested it extensively but it seems to work.

https://drive.google.com/drive/folders/1vS4Ei-_P0KDfpKJeH5bj4J9bGCNeS-Y0?usp=sharing

Run the script at your own risk on mysql 8.x. It will create the database 'traccar' and create the objects and preloads the data as in the original. Since the tables in this version have names different to V3, software accessing the tables from version 3 of traccar will fail. This is not to be considered a fault of this script.

Once the script ran successfully to an end you should be able to run the traccar application. If you find errors, please fix them and share them. So will I.

You will find 2 scripts:
DumpTraccar_mysql8_20180910.sql (this is the dump from the new mysql 8 instance after having been loaded from DumpTraccar_mysql57.sql which was generated with version 5.7).

I would suggest you use 'DumpTraccar_mysql8_20180910.sql'.

https://drive.google.com/drive/folders/1vS4Ei-_P0KDfpKJeH5bj4J9bGCNeS-Y0?usp=sharing

In the scripts you will find the statement below. Running the script implies consent:
"Unless otherwise stated, the software on this site is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. THERE IS NO WARRANTY FOR THE SOFTWARE, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS PROVIDE THE SOFTWARE "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE IS WITH YOU. SHOULD THE SOFTWARE PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER, BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE SOFTWARE (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE SOFTWARE TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF SUCH HOLDER HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES."