audit_trail table not created in MySQL

[sarbogast]

Up until now I was using the default H2 database in my project.
Now I'm migrating to using a regular MySQL database.
My datasource is configured with dbCreate = 'create-drop' so it should recreate all tables on startup.
I also enabled SQL logging to make sure that the tables are all created. And indeed in the logs, I have the following SQL query:

create table audit_trail (
    id varchar(255) not null,
    actor varchar(255),
    class_name varchar(255),
    date_created datetime not null,
    event_name varchar(255),
    last_updated datetime not null,
    new_value varchar(65534),
    old_value varchar(65534),
    persisted_object_id varchar(255),
    persisted_object_version bigint,
    property_name varchar(255),
    uri varchar(255),
    primary key (id)
) ENGINE=InnoDB`

And yet, once my application has started, there is no trace of this table in my database.

I am using Grails 3.2.6, with MySQL connector 6.0.6. The default generator for my identifiers is uuid2, which is why I commented out this instruction in the AuditTrail class:

/**
     * AuditTrails are reported to the AuditLog table.
     * This requires you to set up a table or allow
     * Grails to create a table for you. (e.g. DDL or db-migration plugin)
     */
class AuditTrail implements Serializable {
    @SuppressWarnings("GroovyUnusedDeclaration")
    private static final long serialVersionUID = 1L

    @SuppressWarnings("GroovyUnusedDeclaration")
    static auditable = false

    String id
    Date dateCreated
    Date lastUpdated

    String actor
    String uri
    String className
    String persistedObjectId
    Long persistedObjectVersion = 0

    String eventName
    String propertyName
    String oldValue
    String newValue

    static constraints = {
        actor(nullable: true)
        uri(nullable: true)
        className(nullable: true)
        persistedObjectId(nullable: true)
        persistedObjectVersion(nullable: true)
        eventName(nullable: true)
        propertyName(nullable: true)

        // for large column support (as in < 1.0.6 plugin versions), use
        oldValue(nullable: true, maxSize: 65534)
        newValue(nullable: true, maxSize: 65534)
    }

    static mapping = {

        // Set similiar when you used "auditLog.tablename" in < 1.1.0 plugin version.
        // table 'audit_log'

        // Remove when you used "auditLog.cacheDisabled = true" in < 1.1.0 plugin version.
        cache usage: 'read-only', include: 'non-lazy'

        // Set similar when you used "auditLog.useDatasource" in < 1.1.0 plugin version.
        // datasource "yourdatasource"

        // Set similar when you used "auditLog.idMapping" in < 1.1.0 plugin version. Example:
        // id generator: 'uuid2', type: 'string', length: 36

        // no HQL queries package name import (was default in 1.x version)
        // autoImport false

        //noinspection GroovyAssignabilityCheck
        version false
    }

    /**
     * Deserializer that maps a stored map onto the object
     * assuming that the keys match attribute properties.
     */
    private void readObject(ObjectInputStream input) throws IOException, ClassNotFoundException {
        def map = input.readObject()
        map.each { k, v -> this."$k" = v }
    }

    /**
     * Because Closures do not serialize we can't send the constraints closure
     * to the Serialize API so we have to have a custom serializer to allow for
     * this object to show up inside a webFlow context.
     */
    private void writeObject(ObjectOutputStream out) throws IOException {
        def map = [
                id: id,
                dateCreated: dateCreated,
                lastUpdated: lastUpdated,

                actor: actor,
                uri: uri,
                className: className,
                persistedObjectId: persistedObjectId,
                persistedObjectVersion: persistedObjectVersion,

                eventName: eventName,
                propertyName: propertyName,
                oldValue: oldValue,
                newValue: newValue,
        ]
        out.writeObject(map)
    }

    @Override
    String toString() {
        String actorStr = actor ? "user ${actor}" : "user ?"
        "audit log ${dateCreated} ${actorStr} " +
                "${eventName} ${className} " +
                "id:${persistedObjectId} version:${persistedObjectVersion}"
    }
}

My audit logging configuration is the following:

grails.plugin.auditLog.auditDomainClassName = 'com.adessa.unbox.AuditTrail'
grails.plugin.auditLog.logIds = true
grails {
    plugin {
        auditLog {
            actorClosure = { request, session ->
                if (request.applicationContext.springSecurityService.principal instanceof String) {
                    return request.applicationContext.springSecurityService.principal
                }
                def username = request.applicationContext.springSecurityService.principal?.username
                if (grails.plugin.springsecurity.SpringSecurityUtils.isSwitched()) {
                    username = grails.plugin.springsecurity.SpringSecurityUtils.switchedUserOriginalUsername + " AS " + username
                }
                return username
            }
        }
    }
}

And when I try to run the create table query I get in the logs on the DB itself, it works fine, indicating there is not error there.

[robertoschwald]

Can you change from "create-drop" to "update" and check again?

[sarbogast]

Not better. I dropped all the tables, changed dbCreate to update, started the app, all the tables are created except audit_trail. And when I reboot the app. I can see the create table query in the logs again, but the table is still not created. Really weird.

[sarbogast]

I just tried to set up the database migration plugin to try and generate the changelog from GORM, since I intended to do that afterwards anyway. And when I tried to run dbm-update, I got an error when trying to create the audit_log table:

2017-03-30 17:18:42 ERROR liquibase - changelog.groovy: changelog.groovy::1490886842354-1::sarbogast (generated): Change Set changelog.groovy::1490886842354-1::sarbogast (generated) failed.  Error: Column length too big for column 'new_value' (max = 21845); use BLOB or TEXT instead [Failed SQL: CREATE TABLE unbox.audit_log (id VARCHAR(255) NOT NULL, actor VARCHAR(255) NULL, class_name VARCHAR(255) NULL, date_created datetime NOT NULL, event_name VARCHAR(255) NULL, last_updated datetime NOT NULL, new_value VARCHAR(65534) NULL, old_value VARCHAR(65534) NULL, persisted_object_id VARCHAR(255) NULL, persisted_object_version BIGINT NULL, property_name VARCHAR(255) NULL, uri VARCHAR(255) NULL)]

Could that be related?
Could it be because my DB is in utf8_general_ci?

[sarbogast]

I think I figured it out. Here are the changes I made to my AuditTrail domain class:

static constraints = {
        actor(nullable: true)
        uri(nullable: true)
        className(nullable: true)
        persistedObjectId(nullable: true)
        persistedObjectVersion(nullable: true)
        eventName(nullable: true)
        propertyName(nullable: true)

        // for large column support (as in < 1.0.6 plugin versions), use
        oldValue(nullable: true)
        newValue(nullable: true)
    }

    static mapping = {

        // Set similiar when you used "auditLog.tablename" in < 1.1.0 plugin version.
        table 'audit_log'

        // Remove when you used "auditLog.cacheDisabled = true" in < 1.1.0 plugin version.
        cache usage: 'read-only', include: 'non-lazy'

        // Set similar when you used "auditLog.useDatasource" in < 1.1.0 plugin version.
        // datasource "yourdatasource"

        // Set similar when you used "auditLog.idMapping" in < 1.1.0 plugin version. Example:
        // id generator: 'uuid2', type: 'string', length: 36

        // no HQL queries package name import (was default in 1.x version)
        // autoImport false

        //noinspection GroovyAssignabilityCheck
        version false

        oldValue type: 'text'
        newValue type: 'text'
    }

Because if I just limited the size of both columns to 21000, I had another error saying the row was too big anyway.
With that, I was able to get the audit_log table created correctly with dbCreate='create-drop', and also to get dbm-update to work with the database-migration plugin.

If that makes sense, I think you should change the quickstart script to generate text columns for oldValue and newValue.

[sarbogast]

What's weird though is that Grails didn't mention any error when trying to create the database in the first place.

[robertoschwald]

Thanks for that. Seems to be a changed behaviour in Grails 3.2.
Good to know it works with Grails 3.2. Haven't had a chance to test it, yet.

[robertoschwald]

Checked again. It's weird, because now you use the default String column length constraints, which should be 255, unless it has changed in Grails 3.2.

In your initial comment, you had 65535, which automatically mapped to text type in older Grails versions. Need to check what has changed in 3.2. If we add "text" by default, it will break existing installations and users need to update their schema, as the default in older Grails versions is VARCHAR 255.

[sarbogast]

Whenever I wanted a TEXT column, I've always had to add a 'type' mapping like I did here. This mapping instruction overrides the default type as inferred from the GORM type. I don't think it will break anything.

[sarbogast]

See here: http://docs.grails.org/latest/ref/Database%20Mapping/type.html
It is there since at least Grails 2.0.0: http://docs.grails.org/2.0.0/ref/Database%20Mapping/type.html

[robertoschwald]

Thats not the point. The point is, that users which do not use the large column mode do not have a CLOB type in their db. If we set it to text type by default, they need to update their schema.

[sarbogast]

I see.

[robertoschwald]

So up to Grails 3.0.x and MariaDB 10.1, maxSize: 65534 automatically maps to LONGTEXT. Will change the template.