0

I have a Grails domain class and it's relevant DB table on SQL Server. I want to add a new property/column jPId of type integer to the existing domain/table. Here is the code I have:

class JobCode{

    int jPId // This is the new property/column I want to add.

    //Other Properties which already have corresponding columns in DB

    static mapping = {
        jPId defaultValue: 0
    }

    static constraints = {
        jPId nullable:true // tried commenting this line as well
        //Other properties
    }
}

My DataSource config file has the DB Mode set to 'Update', so that, I can it will perform any ALTER operations. Now, when I restart the server, I expect it to create the new integer/number column in the DB. But it throws the below error and fails to create the column:

ERRORorg.hibernate.tool.hbm2ddl.SchemaUpdateUnsuccessful: alter table EF_JobCode add jpid int not null
ERRORorg.hibernate.tool.hbm2ddl.SchemaUpdateALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'jpid' cannot be added to non-empty table 'EF_JobCode' because it does not satisfy these conditions.

I am clueless as I have no idea what am I missing ! I have given the int jPId a default value as well in the code above and also declared it as nullable:true ( commented it too as I was not sure if nullable:true is applicable to primitive types ). Nothing worked and I keep seeing the above error.

Improve this question
3
  • 1
    Can you specify the actual column name you want in the mapping and try? jPId column: "JP_ID", defaultValue: 0? or whatever you want the name to be other than "JP_ID". Commented Feb 28, 2014 at 1:50
  • I just tried with column too, but in vain. What's annoying is that Grails is trying to alter the table using the command alter table EF_JobPremium add jpid int not null ... despite me mentioning nullable:true. Commented Feb 28, 2014 at 2:16
  • That's a Hibernate thing - it's protecting you from yourself - see my answer Commented Feb 28, 2014 at 2:19

1 Answer 1

Reset to default
3

A nullable primitive doesn't make sense in Hibernate/GORM. If there's a null value in the database, zero is not an appropriate value in Groovy/Java since zero is a valid value and often very different from null, depending on your business rules. Likewise for boolean/Boolean - false is not the same as null. Always specify the object types for numbers and booleans to allow database nulls to be Groovy/Java nulls.

Further, don't use dbCreate='update'. When columns are added they're added as nullable regardless of what you specified in the constraints, and you have to fix the previous rows to have valid values and change the column to not-null. Further, many changes that might seem reasonable will not be done. One example is a column widening. Even though this won't result in data loss, Hibernate won't do it. It also won't add indexes, and there are other related issues. Use create-drop until you get tired of losing data every restart and switch to database migrations, e.g. http://grails.org/plugin/database-migration

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I think I should agree that I rather choose Wrapper class types instead of the primitives. However, it still doesn't clear my mind why it can't accept an int type property when I give it a default value. In my case a 0 is a good default value and I wanted it to work with the primitive int as there are other instances in application where some of the properties were created as 'double' or 'boolean' during our create-drop state.
About dbCreate='update', well we are in the middle of a release and I don't have that luxury to choose anything different from what we as a team decided. But, can you throw more light on why create-drop is better than update? When we are working on test data, we don't want the tables to be cleaned, rebuilt and reloaded with data on every restart of the server right?
There's no easy solution. create-drop deletes everything, but you're always in sync, and you can populate data in BootStrap (although this is impractical for large data). You can also keep a known-good export and re-import that as needed. update is too timid and does unexpected things, so I avoid it completely. Migrations can be a lot of work, but I think once you get used to the process it's not that bad. It comes down to picking the least bad option for your workflow :)
If I may ask, why am I not able to add a new column by defining a new property 'Integer customId = 0' in the domain class? The DB is still in UPDATE mode. I can create the new column only by setting customId nullabe:true. But I want it to be non null field, so I have to ALTER the table in the backend. I don't like it as the Grails domain would then say nullable:true and in the backend we modified it to be non-null field.
Don't use update, use migrations. Own your code, your database, and your changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.