Note 1050: Table 'gorp_migrations' already exists

[JalfResi]

I have a program that attempts to apply any migrations it has not applied run after connecting to the database. However, when I run my program I get:

Note 1050: Table 'gorp_migrations' already exists

The migrations are stored using bindata. Here is the function definition:

// Open returns a DB reference for a data source.
func Open(dataSourceName string) (*DB, error) {
    db, err := sql.Open("mysql", dataSourceName)

    if err != nil {
        return nil, err
    }

    db.Ping()
    if err != nil {
        return nil, err
    }

    migrations := &migrate.AssetMigrationSource{
        Asset:    Asset,
        AssetDir: AssetDir,
        Dir:      "migrations",
    }

    n, err := migrate.Exec(db, "mysql", migrations, migrate.Up)
    if err != nil {
        return nil, err
    }
    fmt.Printf("Applied %d migrations!\n", n)

    return &DB{db}, nil
}

Any pointers on what I'm doing wrong? It looks like the call to CreateTablesIfNotExists() isn't working correctly; it appears to be returning an error and returning.

[JalfResi]

IS there anymore information you need from me to help diagnose this issue? The Open function gets called by main() once on app start. If clear the schema, run the app, the migrations all go in correctly. If I add a new migration or run the app a second time with an existing up to date schema, the above error message occurs. Should I be check to see if the migration table is up to date somehow before attempting to run any migrations?

[JalfResi]

Got to the bottom of this at last!

Turns out my DSN had strict=true (MySQL strict mode on), where all "failed" creates, which normally result in a warning, are promoted to errors,hence the error message.

I'm not sure if sql-migrate should acknowledge this; should the call to create the migrations table fail (as it may if the migrations table already exists), and if strict mode is on, should sql-migrate move on without returning an error? After all, the user may want strict mode on for their own queries, but is uninterested if the sql-migrate table already exists; they want to know if the newer migrations were successful or not.

So basically, sql-migrate cannot be used with strict mode on! It might be worth mentioning this in the documentation/read-me too.

[rubenv]

This feels like a gorp bug. Sql-migrate uses CreateTablesIfNotExists, so it shouldn't fail.

[JalfResi]

Good point. Not sure how gorp would resolve this though; if you use gorp with strict mode on then you may WANT an error if CreateTablesIfNotExists fails. However, in the context of sql-migrate, that situation is NOT an error. Maybe if sql-migrate switched off strict mode before make the call to CreateTablesIfNotExists, then switched it back on afterwards (if strict mode was on to begin with)?

[rubenv]

I might be misunderstanding things here. Are you saying that "CREATE TABLE ... IF NOT EXISTS" causes a failure in MySQL strict mode if the table already exists?

[JalfResi]

Yes.

OSX Yosemite MySQL 5.6.16.

[rubenv]

Right, that's just idiotic behavior. The whole point of "IF NOT EXISTS" is to safely add a table if it doesn't already exist.

My advice: don't use MySQL strict mode, apparently it breaks SQL behavior, so it's not worth your time.

[JalfResi]

Exactly. I've had to remove the strict flag for now to get my program working, but I really would like to work with strict mode as it prevents MySQL from truncating data, screwing up dates etc.

[fgrosse]

The issue is tracked on go-gorp/gorp#284