Managing evolutions in production environment

Question

We are in the process of deploying a new application using play 2.1.1 to production and are having some real issues with it and the very limited documentation didn't help much...

So it was time to update to a new version, we ran our usual stop/upgrade/start scripts but they failed. For some reason, play was refusing to apply the evolutions. When starting it kept saying

Oops, cannot start the server. @6elnj89fh: Database 'default' needs evolution!

This was even though we tried setting applyEvolutions.default=true both through command line and in the application_prod.conf file. It also complained that

WARNING! This script contains DOWNS evolutions that are likely destructives

which doesn't make much sense to me since we are going up in version so the downs should not be applied anyway. But it seems this might have been the reason it was refusing to apply the evolutions.

At this point I wasn't so worried as I assumed that there is some manual way to apply evolutions. After extensive searching it looks as though... There was support for this in play 1 but not in play 2. In dev mode you can just press a button in the browser to apply the evolutions but in prod mode I could find NO WAY OF MANUALLY APPLYING EVOLUTIONS. Is this true or did I miss it? I really think this is an important feature! (In hind sight I could have applied the scripts manually and disabled the evolutions plugin but then I would have lost the evolutions tracking which is useful..)

I also wonder how you would go about "backing" your database as I am sure we will get to a point when we need to do that at some point. If there was a manual way to do this it would probably have an optional version argument to downgrade the database. E.g. if you are at version 5 and need to go back to 4 you run play apply-evolutions 4 which would then apply the downs from version 5 and update the evolutions db accordingly. I could apply the downs manually but then again the problem is the evolutions db will once again be in an invalid state...

Getting more desperate I tried all the settings I could find to get the server up again and added the -DapplyDownEvolutions.default=true option. I assumed this setting would apply downs only when choosing to downgrade the DB (although there seems to be no such option) but what it in fact did was to apply the ups and then instantly apply the downs (I found this out later in troubleshooting as the server now finally started - without any message whatsoever - but gave a cryptic error message when visiting the site). Is that what this setting is supposed to do? If it is I can't understand why the setting even exists. I can't think of any scenario where you would want to apply ups and then instantly downs while migrating to a newer database version. Can someone shed some light on this setting?

At this point I could finally get the app running once again by manually re-running the appropriate "UPs".

At this point we are working on basically re-writing scripts for evolutions handling on our own to have some better control of what is run and to enable going back.. It would be much better to be able to use play functionality for this so I am hoping someone can shed some light on this. If not, maybe this rant can help someone in a similar situation...

Answer 1

Edit: Updated for Play 2.5

---

We're using Play's evolutions for production since about 3+ years and never had serious issues with it.

I recommend having a staging environment, where you run your evolutions against a test database first. The test database should have the exact same version as the production database. You WILL make mistakes in your evolutions, and this is a way to find them before they go to the production server.

Recommended settings

For our production system, we have the following setting enabled:

play.evolutions.db.default.autoApply=true

The setting autoApply makes sure that evolutions are applied automatically, without user interaction. Obviously, this is what we want when upgrading our production database.

For our staging/testing system, we have both settings enabled:

play.evolutions.db.default.autoApply=true
play.evolutions.db.default.autoApplyDowns=true

The second setting applyDownEvolutions makes sure that also DOWNS evolutions are applied automatically. We do NOT want this on our production system, because it may lead to data loss (since DOWNS evolutions often contain things like DROP TABLE etc).

On the testing system however, if you're testing different branches or versions of your application, you may want to switch between different database versions. In this case, you may want to automatically down and upgrade your database as new branches are tested.

Recovering after evolutions failure

Keep in mind that if one evolution fails due to an SQL error (on production or testing system), you'll have to restore the database to a sane state manually. You can do this by looking at the play_evolutions table. There Play keeps track of applied evolutions and their errors. The last entry shows the last applied evolution and also the error that was encountered.

From the error message, you can usually track down the bad SQL and fix your evolutions script. You can then revert the database to the previous evolution version, and remove the failed evolution entry from the play_evolutions table. Play then thinks that the new evolution hasn't been applied yet, and will run it again.

Hope this helps.