Lorenzo Lorenzo - 4 days ago 7
MySQL Question

update domain column with nullable:true

Using

grails 2.2.5
&
mySql
&
database-migration:1.3.2
which is mentioned in this SO question setting column
deleted nullable:true
doesn't change the value into DB

At first I added the deleted property
Boolean deleted
without the
deleted nullable:true
so when I started the app into my DB I have Null : Non (which is No in French)

Then I have added
deleted nullable:true
but when I re-run the app nothing changed, I still have
Null : Non
(last line on the picture)

do I have to change it manually ?

enter image description here

The domain

class Comment {

Date dateCreated
Boolean deleted
String comment;
Boolean rootComment;
int vote;

static belongsTo = [contributor: Contributor, discussion: Discussion]
static hasOne = [project: Project]
static mapping = {
autoTimestamp true
deleted defaultValue: "0"
deleted nullable:true
}
}


Note that into Datasource.groovy I have
dbCreate = "update"


dataSource {
pooled = true
dbCreate = "update" // UPDATE
url = "jdbc:mysql://localhost:3306/soundsharedb"
driverClassName = "com.mysql.jdbc.Driver"
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
username = "xxx"
password = "xxx"
logSql = false
properties {
stOnBorrow = true
testWhileIdle = true
testOnReturn = true
validationQuery = 'SELECT 1'
}

}

Answer

Your best bet is to use a database migration to alter the nullable flag for that column. We use migrations for ALL our database operations -- creating/modifying/removing tables, columns, indices, the whole lot.

For the change you want to implement, a discrete step might be something akin to:

databaseChangeLog = {
    changeSet(author: "You", id: "some unique ID") {
        dropNotNullConstraint(columnDataType: "bit", columnName: "deleted", tableName: "comment") 
    }
}

Read up on the database-migration plugin for how to generate/write migrations and use them for programmatic database changes.

Comments