MySQL migration issue

[tananaev]

@Abyss777, we have renamed all tables, but the problem is that original names are still used in the old changelogs, so Traccar fails on new versions of MySQL:

https://www.traccar.org/forums/topic/traccar-40-mysql/

Any suggestions how to fix it? What do you think about collapsing all changelog into a single one for new version? Not sure if we can make condition like this, but ideally I want:

• If database already contains any of old Traccar tables, just use current changelogs.
• If it's a new installation, run a single combined changelog which will generate all necessary tables in one step.

Eventually (after a year or two) we can drop old history completely and just rely on the new changelog history that starts with 4.0 schema.

[Abyss777]

I agree with your suggestion. The history is too long now, some things changed twice.
We can drop old history even earlier, it is not hard to migrate in two steps.
Clean 4.0 will be huge, but I think it is OK.

As far as I can see in liquibase documentation we can use preconditions in changelog-master.

• If some old table (that we never renamed) exists apply old history
• If neither old table nor new table exist apply 4.0-clean
• Apply new changelogs 4.1, 4.2 ...

[tananaev]

That would be perfect. Can you try to implement it?

[Abyss777]

Sure. It will take some time...

[Abyss777]

36 tables... A lot of features were implemented for last three years...
I've exported clean changelog with help of http://www.liquibase.org/documentation/generating_changelogs.html
It is need a lot of makeup and porting db-dependent changes, but I think it is easier than trace all changes through our changelogs.
Working

[tananaev]

Nice. Let me know if there are any issues.

[tananaev]

Have you had any progress? I am thinking of releasing a new version soon with all my recent changes to the project. It would be ideal to include this as well.

[Abyss777]

I'm really sorry it is not finished yet. Have no enough time to finish. I'll do my best to give at least a draft in a couple of days.

[tananaev]

That's fine. Take your time. Just wanted to know if there's any progress on it.

[jishnsyed]

Hi, I am new in traccar. Can anyone tell me how can i learn about traccar.

[tananaev]

@jishnsyed, please don't comment on unrelated threads. With behaviour like this you will quickly get ban and not help. There is plenty of information about Traccar available on the official website and forum. Read it before asking for anything.

[Abyss777]

I'm experiencing the same problem with current code https://stackoverflow.com/questions/50021821/how-do-i-set-sql-mode-in-the-jdbc-connection-string-for-mysql-8
But with mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64)
How have you solved it?

[tananaev]

Haven't tried with MySQL yet. I'll check. For now you can downgrade driver version.

[Abyss777]

Come up with some problems, include can be on changelog level only and at the same time preconditions on changlog level applies to any other. Hope will manage to solve this...

[tananaev]

I have created a bug report for MySQL issue:

https://bugs.mysql.com/bug.php?id=92485

We might need to downgrade version if they don't fix it before release.

[tananaev]

Any updates?

[Abyss777]

Sorry, I have a lot of work and managed spend only small pieces of time.
Here is my progress:

1. I've polished clean 4.0 changelog, that works on h2,mysql,mssql,postgresql. (hope I did not wasted the time) changelog-4.0-clean.zip
2. I'm afraid we can't just make two brunches in changelogs history. It is not what liqubase developers intended to do. http://www.liquibase.org/documentation/trimming_changelogs.html

The one of way out that looks like preconditions do not change changelog checksum. May be we can add preconditions to all our changelogs.

[tananaev]

Can we put new 4.0-clean changelog first in line and run it only if database is empty? And then for all other changelogs have pre-condition that 4.0-clean hasn't run yet.

[Abyss777]

run it only if database is empty?

I would not call it check for emptiness, but it is already has check for existence of positions and tc_positions tables. At the current state it is equivalent I think.

I think we should add by two preconditions to all chagesets according to its content and 4.0-clean already run.

Also I think we should use preconditions in future more frequent. For all changesets for example.

[tananaev]

I have implemented migration paths. Please check and see if you can find any issues.

I have tested 2 scenarios on H2 database:

1. Clean installation. Only changelog-4.0-clean is executed and everything else is skipped.
2. Migration from 3.10 to latest. Everything is executed except changelog-4.0-clean.

Seems to work as expected, unless I missed something. Can you please test some other databases.

One question I have about clean changelog. Why are you using blob.type property? Did we have it before?

[Abyss777]

Why are you using blob.type property? Did we have it before?

No we did not have it. It is related to #3223 I have found this solution in liquibase documentation. Otherwise we have to have separate changeset for postgresql. This solution looks cleaner and plane.

[Abyss777]

Nice, we moved to Paris :)

I've tested it on MSSQL, PostgreSQL and MySQL with some test objects and test data.
3.17 -> 4.0 -> 4.0 with fixes -> Current
Got next results:

1. I had to downgrade liquibase-core to 3.5.3 to make it work with my MS SQL 2008 R2 in all tested traccar versions.
2. I had to downgrade mysql-connector-java to 5.1.46 in Current version to make it work with mysql 5.7.23
3. Migration went well for all DBs.

I've implemented the fix for #4097 but can not adjust correct precondition.

<changeSet author="author" id="changelog-4.0-ms">

    <preConditions onFail="MARK_RAN">
      <dbms type="mssql" />
    </preConditions>
    
    <sql>
      IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tg_groups_delete')
      BEGIN
      DROP TRIGGER tg_groups_delete
      END;
    </sql>

    <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
    </sql>

    <sql>
      IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tg_users_delete')
      BEGIN
      DROP TRIGGER tg_users_delete
      END;
    </sql>

    <sql>
      CREATE TRIGGER tg_users_delete
      ON tc_users FOR DELETE
      AS BEGIN
      DELETE FROM tc_user_user WHERE manageduserid IN (SELECT deleted.id FROM deleted)
      END
    </sql>

  </changeSet>

Clean install will cause recreating triggers.
SQLs check trigger existence, we can improve changelog-4.0-clean-mssql to work for not only for clean, but also with dirty 4.0 migration.

[tananaev]

You have moved to Paris?

[Abyss777]

Unfortunately not, test-generator.py have moved :)

[tananaev]

Oh yeah. Forgot about that. The reason is actually that we have new free Ban reverse geocoder that works only in France, but useful for testing, so I moved location.

[Abyss777]

I like it, why not to move every couple of releases :)

[tananaev]

About triggers. Do we actually need any pre-condition?

[Abyss777]

I'm testing adjusted changelog-4.0-clean-mssql to work in both cases without preconditions. Will send a PR after finished.