Scan error on column index 0: unsupported driver -> Scan pair: []uint8 -> *time.Time

[Hector Parra]

Using sqlite3 I get the following error when running goose status

goose: status for environment 'development'
    Applied At                  Migration
    =======================================
2014/03/17 02:10:15 sql: Scan error on column index 0: unsupported driver -> Scan pair: []uint8 -> *time.Time

Creation of migration and migration itself are successful. Goose table entries appear to be correct.

Seems this is actually an unexpected value of Source field?

[Liam Staskawicz]

A quick search turns up a few issues in which people have had trouble storing/retrieving time.Time values:

• https://github.com/mattn/go-sqlite3/issues/42
• https://github.com/mattn/go-sqlite3/issues/44
• https://groups.google.com/forum/#!topic/golang-nuts/4ebvN6Bgv3M

Any chance these are relevant to your issue?

@jkl1337 - any idea what might be happening?

[Liam Staskawicz]

Can you give pull request #31 a try, and see if that resolves it for you?

[Liam Staskawicz]

@hgpa - ping, any idea when you might have a chance to test the proposed resolution?

[Hector Parra]

Sorry. I'm new to go and I'm not sure if I did this correctly. I "got" the fork, checked out the branch, and compiled:

$ go get bitbucket.org/jkl1337/goose/cmd/goose
$ cd $GOPATH/src/bitbucket.org/jkl1337/goose/
$ git checkout -b sqlite3 origin/sqlite3
$ go install bitbucket.org/jkl1337/goose/cmd/goose

I confirmed I compiled and am using latest changes by making my own little change. Afterwards I deleted DB and ran migrations.

Error still exists.

[Liam Staskawicz]

Ah, right. I think that looks about right for fetching the code, but my guess is that since this error has to do with the structure of the table, you might need to wipe your DB (hopefully this is in development and still feasible...eek!) and start again, so that the table gets created with the correct column type.

[John Luebs]

Well sorry about all this. Hector says he deleted the database, so what I think the problem is that the fork I have pushed to bitbucket references your repo in all the command imports. I didn't intend my fork to be go gettable at this time, just to submit pull requests. Hector, you may still be using the liamstask lib code unless you fixed the imports in the cmd/*.go files.

Just in case: Hopefully there wasn't enough time for anyone to get carried away with something complicated with the sqlite code, but if absolutely necessary the following should effectively fix an existing database without needing to recreate it:

PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE goose_db_version (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                version_id INTEGER NOT NULL,
                is_applied INTEGER NOT NULL,
                tstamp TIMESTAMP DEFAULT (datetime(''now''))
            );' WHERE NAME = 'goose_db_version';
PRAGMA writable_schema = 0;

This is safe because the underlying table structure is not changing with the update.

[Liam Staskawicz]

@jkl1337 - thanks. i just merged the fix, since it was straightforward enough. @hgpa - hopefully this makes it easier to verify the fix.

[Hector Parra]

That fixed it. Thank you.

[Liam Staskawicz]

reported as resolved.

[Erik Dubbelboer]

For anyone still seeing this error with the mysql driver. You have to set parseTime to true in your connection string. My config now looks like open: $DATABASE?parseTime=true