0

My web app requires some initial database scripts to be executed during installation. These are just some CREATE TABLE scripts that need to be run. I'm working on dockerizing it.

I've been trying to use the Docker 1.12's swarm mode to create multiple container replicas of my web app which use a shared database. (An Oracle 12c or MySQL instance).

The problem is that when I start multiple copies of the container, they all try to run the SQL scripts simultaneously and fail. I tried using Flyway to handle the database migrations but to no avail.

What should be the way to handle such a situation?

user2664256
  • 35
  • 4
  • Surely your app wants to check the existence of those tables first, that is your DB migration does only what has not been done yet or your app will be useless... – Oleg Sklyar Sep 17 '16 at 20:52
  • @OlegSklyar do you want to convert that into an answer such as `create table IF NOT EXISTS thing ( col1 int not null, col2 int not null, age int not null ); ` which gives a warning / error 1050 [link](https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html) ? – Drew Sep 17 '16 at 21:23
  • @Drew Thanks, done. – Oleg Sklyar Sep 17 '16 at 21:49

1 Answers1

1

Generally, DB migration jobs should be versioned with every new script executing only after all the earlier ones are done. With this as a precondition one can either design a tool that runs those jobs one after another if not already run or one lets the jobs run always, but designs them in a way to check that they have not been done before.

Otherwise, all application data are at risk of being wiped off at next deployment. You should be able to deploy any time keeping and reusing the existing data (with docker or without).

In your "simple" case a DB check of the following sort would help:

create table IF NOT EXISTS thing (col1 int not null, col2 int not null, age int not null );

Thanks @Drew for prompting me to put my comment into an answer.

Oleg Sklyar
  • 9,834
  • 6
  • 39
  • 62
  • Shouldn't DB migration tools like Flyway (that I mentioned) handle this? Flyway does create a schema_version table to handle the versioned migrations. But it fails while creating this same table when running migrations. See [this] (https://github.com/flyway/flyway/issues/1435). Also, the execution of these DB scripts is just a one time thing. After this hibernate kicks in and handles all the db operations. – user2664256 Sep 17 '16 at 21:51
  • AFAIK flyway generally should as it is exactly the principle it applies, but I am not an expert in flyway to tell you how to configure the versioning. However, flyway is normally used at deployment times rather than at startup, or at least separately from startup as it only needs to be run once even if you deploy 100 instances. Running it in parallel (apart from making little sense on a single DB) may potentially lead to race conditions of the sort you describe. – Oleg Sklyar Sep 17 '16 at 21:57
  • Actually, you might want to read this: http://stackoverflow.com/questions/39196505/docker-1-12-multiple-replicas-single-database – Oleg Sklyar Sep 17 '16 at 21:59
  • Apparently I was the one who asked that question. But I thought my concern shouldn't have been around using Flyway but on understanding the database design. – user2664256 Sep 17 '16 at 22:13
  • If that is your concern, then do not run flyway or any other DB migration on every start of your application. Your app will be happy to start quickly, which will help immensely in case of crash, your data will be written once (per migration run) and remain consistent. Do by all means automate migration (single) followed by startup (in parallel on docker), but provide for that a separate startup script from the one you would use to just perform a (re)start. – Oleg Sklyar Sep 17 '16 at 22:17
  • I really like your idea of using a migration just once before deploying my app and then configure all of them to just use the same database but I don't think that would fit well into the CD pipeline we've set-up as we want our app to be served as a microservice. I think avoiding flyway and running our own migration with _create if not exists_ could be better.But just one more thing, with Oracle, catching the ORA-00955 is the only way for this, is it not? – user2664256 Sep 17 '16 at 22:24
  • IMHO CD pipelines should help you develop reliable applications rather than prevent it. So if you ask me, redesign your CD pipeline to fit your needs. From MySQL docs "The keywords IF NOT EXISTS prevent an error from occurring if the table exists". As for Oracle check this http://stackoverflow.com/questions/15436942/oracle-create-table-if-it-does-not-exist – Oleg Sklyar Sep 17 '16 at 22:31