Full database install&upgrade with Flyway in production environments ?

[Olivier Sarrat]

Hi everyone,

I have installed locally my first v2.2-beta3 (AtolCD branch as released on 2nd september) yesterday evening.
Pretty great !

And one of the good thing I found is that I didn't have even to pass almost any SQL script to initialize a database.
Apart from some repository data (Country table notably), I just had to create my first org and first userlogin, and everything was already ready !
Does that mean that this Sigmah automatic database upgrading system (as stated in issue #851) system is now production-ready ?
If I just add the missing repository data in the flyway V1_... sql script, would that be all of it ?


I have a tested this while taking care to keep this paramater in hibernate configuration file in WEB-INF\classes\META-INF\persistence.xml
<property name="hibernate.hbm2ddl.auto" value="update" />

Have a nice day,

Olivier.

--

Olivier Sarrat
Ingénieur Systèmes d'Information / Information System Engineer

>>Sigmah (Twitter, Facebook, Linkedin, Google+)
Animateur du projet / project facilitator

>>Groupe URD (Twitter, Facebook)
La Fontaine des Marins
26170 Plaisians
Tel: + 33(0)4 75 28 29 35

Mobile: +33 (0)6.34.31.42.07    -    Skype: osarrat.urd

Par respect pour l'environnement,

n'imprimez ce mail qu'en cas d'absolue nécessité

 

[Brendan Le Ny]

Hi Olivier,

Le 09/09/2016 à 16:07, Olivier Sarrat a écrit :
> Does that mean that this Sigmah automatic database upgrading system

> <http://www.sigmah.org/issues/view.php?id=851> (as stated in issue #851
> <http://www.sigmah.org/issues/view.php?id=851>) system is now
> production-ready ?

For now, we still have to call flyway maven plugin to migrate data so,
if you didn't call it, i guess your database have not be upgraded with
the flyway scripts. You may face problems when using sigmah for real
(even if starting Sigmah should works).

> If I just add the missing repository data in the flyway V1_... sql
> script, would that be all of it ?

What data are you talking about? If it is the
newOrganizationLaunchScript.postgresql.sql file, we can't integrate that
since it has to be parameterized by the user and flyway can't guess
those data.

> I have a tested this while taking care to keep this paramater in
> hibernate configuration file in WEB-INF\classes\META-INF\persistence.xml
> <property name="hibernate.hbm2ddl.auto" value="update" />

Well, that's why your database was ready even if flyway was not called.
This is the "hibernate auto update" mode which should be disabled in
favor of flyway.

This mode makes Hibernate update your database schema automagically, on
Hibernate starting to match the model (the annotations present in the
Java entities...). Hibernate will create missing tables, missing
columns, etc...

It seems fine and productive and while its totally OK to use for a
quick-and-easy development, this should be avoided in favor of flyway
for many reasons. The most obvious one is that flyway will just create
empty tables and new columns, without guessing how to migrate data which
are already in the tables (see, for example, V3_project_teams.sql, line
34: Hibernate will not do that insert statement which is required for
Sigmah to works). Hibernate also ignore everything about stored
procedures which are created in the flyway scripts, etc.

I hope that helps.

Have a nice week,

--
Brendan Le Ny, Code Lutin
bl...@codelutin.com
(+33) 02 40 50 29 28

[Olivier Sarrat]

Hi everyone,

First of all, thank you Brendan for your replies.

I have my reply now : Flyway won't be used for database upgrade in production environment.
For three reasons:
- it can't execute some rare high-level orders like installing Postgres extension (see topic "[sigmah-dev] Your Sigmah environment require an upgrade (install pg_trgm posgresql)")
- it doesn't allow a step by step control of the database upgrade that sysadmin like to have
- it is not compatible with alternative solution for database upgrade, like the Java migration application developed by Netapsys for migrating budget ratio

As a consequence, I have "de-deprecated-ed" the Schema Changelog in the wiki : http://wiki.sigmah.org/doku.php?id=contributorguide:schemachangelog
And I have added a link to it from the Administrator Guide : http://wiki.sigmah.org/doku.php?id=administratorguide:administratorguide

Does this seem reasonable to all?

Best,

Olivier.

[Brendan Le Ny]

Hi,

Le 11/10/2016 à 18:48, Olivier Sarrat a écrit :
> - it is not compatible with alternative solution for database upgrade,
> like the Java migration application developed by Netapsys for migrating
> budget ratio

It seems we were overwhelmed by the merging issues and I did not spot
that but I would have suggest to use Flyway too.

Actually, Flyway is compatible with doing that (migrating with Java
code, not SQL code). You just have to write a Java class instead of a
.sql file. See the "Java-based migrations" doc:

https://flywaydb.org/documentation/migration/java

Flyway will just give you a java.sql.Connection which is what is needed
to run the code written to migrate budget ratios. You are with a plain
old JDBC API to do what you want on the database.

There is a risk for someone to forget this step in the migration process
(and the exact moment when to do it).

Another issue is the fact that we should deliver a migrate.jar file and
nothing is done in the build process to create it. I guess Mohamed built
it manually, i don't know how (some IDE?). Creating a new artifact in
the build process would require a new module, and changing Sigmah to be
a maven multi-module project, introducing complexities... I think
pluging everything to flyway would be less painful.

But, if it's OK for the production people, everything is fine.

> As a consequence, I have "de-deprecated-ed" the Schema Changelog in the
> wiki : http://wiki.sigmah.org/doku.php?id=contributorguide:schemachangelog
> And I have added a link to it from the Administrator Guide :
> http://wiki.sigmah.org/doku.php?id=administratorguide:administratorguide
>
> Does this seem reasonable to all?

Depends whose time is it ;) Maybe we could publish all .sql file beside
the .war file on each release.

Also, you could improve the page by replacing

```

by

```sql

I will enable syntax-highlighting (well... not truly necessary given
that it's just about copy-pasting the files).

My two cents...

[Olivier Sarrat]

Hi,

Thanks to a comment from tdegigry on issue #1065 , I realized that I forget to include latest Flyway SQL scripts in my production database initialisation script.

This reopens the issue of using Flyway in production environments.
For each release, we need to provide two SQL deliverables:

1. the Minimum Data Kit SQL script : includes the schema and some default data (like Country table content)
2. a set of SQL scripts for data updates

And we haven't thought on how to include those two deliverables in addition to the .war file in the Releasing process documented in this wiki page part.
1.=> the first deliverable is get manually by dumping an empty MinimumDataKit database of the former version on which has been applied all update scripts
2.=> the second deliberable is also produced manually on the Schema Changelog wiki page.

But this very manual process is weak, and may lead to forget some steps as it occurs with issue #1065. How can we improve it now that we have Flyway used for the developer environment ?
An issue here is that we decided to have potentially several versions of the same SQL update scripts in different SQL Flyway scripts if we found some bugs or lack in former version of that script. It is the case for "migrate_budget_fields.sql" what we have 3 times.
Any idea on this matter taking into consideration this issue ?

Best,

Olivier.

[Brendan Le Ny]

Hi Olivier,

Le 12/09/2017 à 13:06, Olivier Sarrat a écrit :
> Thanks to a comment from tdegigry on issue #1065

> <http://www.sigmah.org/issues/view.php?id=1065#c2511> , I realized that

> I forget to include latest Flyway SQL scripts in my production database
> initialisation script.

Frequent mistake, human :)

> But this very manual process is weak, and may lead to forget some steps
> as it occurs with issue #1065. How can we improve it now that we have
> Flyway used for the developer environment ?

As explained above, Code Lutin use Flyway on multiple projects with
great success: the reduction of time spent trying to reproduce bugs that
are eventually diagnosed as "someone forgot to migrate" is definitely an
improvement (I was tired losing my time with that).

It's now part of our basic toolkit for every projects using an SQL
persistence.

Plugging flyway and make it migrate production database at the start of
.war could be done with lines of codes somewhere in the app :

https://flywaydb.org/documentation/api/

Others approaches (wiki, documenting...) which are human-based always
lead to errors. Plus, I know Sigmah is deployed on multiple production
instances (plus multiple staging instances, plus demo instance... and
more?) and it's hard, and error-prone (if human with pen-and-paper
process) to record which migration was applied on what instance.

> An issue here is that we decided to have potentially several versions of
> the same SQL update scripts in different SQL Flyway scripts if we found
> some bugs or lack in former version of that script. It is the case for
> "migrate_budget_fields.sql" what we have 3 times.
> Any idea on this matter taking into consideration this issue ?

I understand your point.

That's the way Flyway is designed: 1 migration = 1 file. 1 file should
contain the full statements to pass. So every-time you change the body
of a stored procedure, you have to copy-paste the whole body in a new
migration file, making it hard to see the real change (a good practice,
is to commit the copy-pasted file, and add another commit with the
changes to the procedure, so one can see the changes easily).

You should have a look at a new concept, introduced in Flyway 4:
"repeatable migrations".

Contrary to the numbered migrations (which Flyway will apply once, and
only once), a repeatable migration may be applied multiple times,
every-time the file change.

The documentation explain it:

https://flywaydb.org/documentation/migration/repeatable

I guess the idea is to put each of the Sigmah procedures in one separate
file. Those files can be modified by the developer as needed, we will
keep one file per procedure and those files can be tracked with git just
like regular java source files. Note that we will still need basic
numbered migrations for schema changes (adding columns, etc.).

It seem to solve your issue. To be honest, we never used repeatable
migrations so I don't have a success story to tell but I don't see any
trivial pitfall (maybe updating two dependent procedures? or, maybe
thighly coupled procedures should be in a same file?). That would
require to upgrade to Flyway 4 (we use 3) but it seems straightforward.

Hope that helps...