dbm-generate-gorm-changelog and dbm-generate-changelog both fail to generate the correct byte array MySQL type

[robertsantiago]

I have a Grails application and using the database migration plugin (DBM) it fails to generate the correct MySQL type for byte arrays.

I have the following domain field with constraints and mapping:

byte[] byteData

static constraints = {
    byteData(nullable: false, maxSize: 250000)
}

static mapping = {
    byteData(column: 'byte_data', type: 'binary')
}

Without the DBM plugin, the table in question would generate a field of type MEDIUMBLOB. If the maxSize constraint is removed, then the generated type would be TINYBLOB. I would expect the DBM plugin to generate from either the GORM classes or from the MySQL database the change sets for the byte array with its size constraint correctly but this is not the case.

When I use **dbm-generate-gorm-changelog** or **dbm-generate-changelog** they both result in the same column command inside the changeset as follows:

column(name: "byte_data", type: "BLOB") {
    constraints(nullable: "false")
}

It seems that the commands are ignoring the maxSize constraint specified above which causes a problem when we assign arrays longer than what the BLOB type allows.

We can manually change the type to MEDIUMBLOB in the change set although it seems like a hack. Not sure if this is a known problem or if it is a problem at all. I would appreciate any advice on this issue.

[puneetbehl]

Please specify the version of Grails® framework and the plugin version.

[robertsantiago]

Grails: 4.0.1 Migration plugin: 3.1.0

[robertsantiago]

The problem also happens in the latest version of Grails and the latest versions of the migration plugin and Liquibase:

Grails: 5.1.1 Migration plugin: 4.0.0-RC3 Liquibase: 4.6.2

[abcfy2]

Maybe this issue is the same as https://github.com/grails/grails-database-migration/issues/187 ?

[abcfy2]

Maybe you should use sqlType: 'binary' instead of type: 'binary'

[robertsantiago]

abcfy2 Thanks for the help. Unfortunately, using sqlType: 'binary' still yields a similar result but without the constraint:

column(name: "byte_data", type: "BLOB")

[robertsantiago]

Correction: I changed the constraint to nullable: true and that was what caused the result without the constraint. When using nullable: false again, the result of using abcfy2 suggestion (sqlType: 'binary') is the same as above.

[abcfy2]

I find the reason, that's liquibase limitation. You can't use this in dbm-generate-gorm-changelog. You can find full description in my comment: https://github.com/grails/grails-database-migration/issues/187#issuecomment-1003840577

Maybe you have to drop and create your database and use:

./grailsw dbm-create-changelog changelog.groovy
./grailsw dbm-gorm-diff v1_init.groovy

[abcfy2]

Liquibase BLOB type logic can be found here: https://github.com/liquibase/liquibase/blob/6a609ce2cacb67d9a3aeda3f23e942752f935229/liquibase-core/src/main/java/liquibase/datatype/core/BlobType.java#L19

dbm-generate-gorm-changelog will use GormDatabase not MysqlDatabase, which does not connect to MySQL, so liquibase will not use MySQL BINARY type to convert this sqlType.

So you have to use dbm-gorm-diff, not dbm-generate-gorm-changelog.

[robertsantiago]

abcfy2, your insight is very close. Indeed creating the changelog from the database and not from GORM is the right workaround. I had to:

1. Recreate the database from GORM.
2. Use dbm-generate-changelog which will generate the correct type. (using dbm-create-changelog creates an empty databaseChangeLog)

In addition, creating the changelog from the database using dbm-generate-changelog works in both Grails versions 4.0.1 and 5.1.1.

Thanks very much for your help.