Upgrade script future ideas

[pboguslawski]

(1) To avoid problems like https://github.com/znuny/Znuny/issues/245 in the future (>6) Znuny versions consider implementing incremental migration script (maybe otrs.Console.pl Maint::Migrate instead of separate upgrade script if possible) with saving migration version counter in db and skipping all already done migration steps in future migration runs. This should:

• simplify Znuny updates (just teach admins to run otrs.Console.pl Maint::Migrate on every update; extra executions should not change anything in DB and not generate any load on system),
• protect upgrade process against reapplying already applied changes (downgrades should be disallowed probably),
• speed up things (no need to search for existence of changes which may be dangerous like in issue above - comparing migration version from DB should be sufficient to skip steps).

Similar solution is already used in other projects like https://github.com/pressly/goose

(2) Upgrade process from v5 to v6 was not optimal because there are many separate schema changes applied in serial for same table; mysql is rewriting same tables a few times to apply all changes which makes whole upgrade process very long for systems with large tables; consider applying all changes (i.e. indexes, column changes) to given table in one query if possibe, not separate queries.

[rkaldung]

Hello @pboguslawski,

(1) The bug you have mentioned is there for a long time and was not found until now. In general, the migration process works well and is proven. This does not indicate that it's complete error free, but the same goes with every other possibility. Your arguments to simplify updates, prevent load and speed up thing might be have a foundation, especially for you. On the other hand, every change comes with costs, and costs could be time, load etc. This is why we always, without exemptions, strongly advise to perform a test migration.

(2) There might be many possibilities to adjust a migration to change the order of schema changes, table modifications etc. to meet your criteria. Especially for the migration from 5 to 6 we won't change anything, this path is proven and works. Changing anything here just creates a new risk. Especially for major version changes (5 => 6) a test migration, with production data if possible, is a must have and gives you an pretty good overview on the required duration (=downtime) for the upgrade process.

[pboguslawski]

This does not indicate that it's complete error free, but the same goes with every other possibility.

Sure. But bugs should be resolved. Consider readding (in https://github.com/znuny/Znuny/issues/245) missing objects in upgrade script to 6.x+ if not already present in db.

This is why we always, without exemptions, strongly advise to perform a test migration.

Testing upgrades is separate topic from optimal coding. Just avoid rewriting same tables N times during upgrade if possible. If not possible easily - users will have to wait longer for upgrade to complete (much longer in case of large tables).

Especially for the migration from 5 to 6 we won't change anything, this path is proven and works.

This issue is not about changing 5->6 upgrade procedure but to avoid such problems in upgrade procedures to future Znuny versions.