title: Grails database migration changelog woes
body: <p>I'm struggling to follow <a href="http://blog.springsource.org/2011/08/17/countdown-to-grails-2-0-database-migrations/" rel="nofollow">this</a> tutorial.  Here are the steps I have taken:</p>
  
  <p><strong>1.)</strong> I first create my empty changelog.  Before I run this command I set my development <code>dbCreate</code> to <code>create</code>.</p>
  
  <p><code>grails dbm-create-changelog</code></p>
  
  <p><strong>2.)</strong> Then I generate the initial gorm changelog.  The tutorial says I should use the <code>prod</code> flag, but since my production database is empty I drop the <code>prod</code> flag.  Before I run this command I comment out my development <code>dbCreate</code> setting.</p>
  
  <p><code>grails dbm-generate-gorm-changelog --add changelog-1.0.groovy</code></p>
  
  <p>At this point my <code>migrations</code> folder contains the files <code>changelog.groovy</code> and <code>changelog-1.0.groovy</code>.  I will refer to <code>changelog-1.0.groovy</code> as <code>changelog #1</code></p>
  
  <p><strong>3.)</strong> I now attempt to initialize my production database with the changelog I just created.  I set the database migration <code>updateOnStart</code> and <code>updateOnStartFileNames</code> in my config as indicated in the tutorial I linked to.  My production database exists, but is empty (no tables).  The <code>dbCreate</code> setting for my production environment has been commented out.</p>
  
  <p><code>grails prod run-app</code></p>
  
  <p><strong>THE PROBLEM:</strong></p>
  
  <p>At first glance this appears to have worked, but <strong>when I run <code>grails prod dbm-gorm-diff</code> and generate <code>changelog #2</code> I see changes that should not exist.  I can also see that the changesets are legit when compared to the production database's current state.  This means the production database was not initialized correctly with <code>changelog #1</code>.</strong>  No changes should exist since I changed nothing.  I ran <code>grails prod dbm-gorm-diff</code> immediately after I ran <code>grails prod run-app</code>.</p>
  
  <p>There are five changesets in the <code>changelog #2</code>.  I have verified that all of these changesets are represented in the <code>changelog #1</code>.  The first four changeset in <code>changelog #2</code> all have <code>unique: "true"</code>.  These four changesets are also the only changesets to contain <code>unique: "true"</code> in <code>changelog #1</code>.  I am still at a loss to explain why the fifth changeset exists.  There is nothing remarkable about it that I can see, though it is for the <code>user_role</code> table used by the <a href="http://grails.org/plugin/spring-security-core/" rel="nofollow">Spring Security plugin</a>.  Here is <code>changelog #2</code>:</p>
  
  <pre><code>databaseChangeLog = {
  
      changeSet(author: "typoknig (generated)", id: "1341970550063-1") {
          createIndex(indexName: "authority_unique_1341970549765", tableName: "role", unique: "true") { // This table is for the Spring Security plugin's "Role" domain class.
              column(name: "authority")
          }
      }
  
      changeSet(author: "typoknig (generated)", id: "1341970550063-2") {
          createIndex(indexName: "name_unique_1341970549772", tableName: "foo", unique: "true") { // This is just a table for one of my domain classes that happens to have a field with a unique constraint.
              column(name: "path")
          }
      }
  
      changeSet(author: "typoknig (generated)", id: "1341970550063-3") {
          createIndex(indexName: "name_unique_1341970549774", tableName: "bar", unique: "true") { // This is just a table for one of my domain classes that happens to have a field with a unique constraint.
              column(name: "name")
          }
      }
  
      changeSet(author: "typoknig (generated)", id: "1341970550063-4") {
          createIndex(indexName: "username_unique_1341970549777", tableName: "user", unique: "true") { // This table is for the Spring Security plugin's "User" domain class.
              column(name: "username")
          }
      }
  
      changeSet(author: "typoknig (generated)", id: "1341970550063-5") {
          createIndex(indexName: "FK143BF46A5FBC0B79", tableName: "user_role") { // This table is for the Spring Security plugin's "UserRole" domain class.
              column(name: "role_id")
          }
      }
  }
  </code></pre>
  
  <p><strong>What do I need to do to make sure my production database is initialized correctly?</strong></p>
  
  <p><strong>UPDATE #1:</strong></p>
  
  <p>I don't know why, but <code>createIndex</code> isn't working with <code>unique: "true"</code>.  For the first four changesets I simply moved <code>unique: "true"</code> to the location where the column in question was originally being created in <code>changeset #1</code>.  This leaves only the fifth changeset in <code>changelog #2</code> to take care of.  I still don't see any issues with the changeset, so I don't understand why it is not being applied.</p>
  
  <p><strong>UPDATE #2:</strong></p>
  
  <p>I found that I can move all the calls to <code>createIndex</code> out of the individual changesets into the corresponding changeset where the table (which the index belongs to) was created.  <strong>This seems to have solved all my issues.</strong>  Unless someone can provide a compelling reason not to, I think I will make this part of my changelog generation work flow.  For example these changesets:</p>
  
  <pre><code>changeSet(author: "typoknig (generated)", id: "1342037835503-31") {
      createTable(tableName: "user_role") {
          column(name: "role_id", type: "bigint") {
              constraints(nullable: "false")
          }
  
          column(name: "user_id", type: "bigint") {
              constraints(nullable: "false")
          }
      }
  }
  
  changeSet(author: "typoknig (generated)", id: "1342037835503-103") {
      createIndex(indexName: "FK143BF46A4E6CF59", tableName: "user_role") {
          column(name: "user_id")
      }
  }
  
  changeSet(author: "typoknig (generated)", id: "1342037835503-104") {
      createIndex(indexName: "FK143BF46A5FBC0B79", tableName: "user_role") {
          column(name: "role_id")
      }
  }
  </code></pre>
  
  <p>Would be come this changeset:</p>
  
  <pre><code>changeSet(author: "typoknig (generated)", id: "1342037835503-31") {
      createTable(tableName: "user_role") {
          column(name: "role_id", type: "bigint") {
              constraints(nullable: "false")
          }
  
          column(name: "user_id", type: "bigint") {
              constraints(nullable: "false")
          }
      }
      createIndex(indexName: "FK143BF46A5FBC0B79", tableName: "user_role") {
          column(name: "role_id")
      }
      createIndex(indexName: "FK143BF46A4E6CF59", tableName: "user_role") {
          column(name: "user_id")
      }
  }
  </code></pre>