Migrations in 0.8 using MySQL

[nathasm]

I'm using MySQL for my backend.

In 0.7.3 I created a number of migration files that resemble:

exports.up = function(knex, Promise) {
  return knex.transaction(function(trx) {
    return trx.schema.createTable('ages', function(t) {
      t.increments('id').primary();
      t.string('name').unique().notNull();
    });
  });
};

After upgrading to 0.8, I am getting the following errors on inserting my tables

Knex:warning - migrations failed with error: release savepoint trx5 - ER_SP_DOES_NOT_EXIST: SAVEPOINT trx5 does not exist
TypeError: expecting an array, a promise or a thenable

    See http://goo.gl/s8MMhc

    at PromiseArray.init [as _init] (/Users/nsmith/projects/cohortable/node_modules/knex/node_modules/bluebird/js/main/promise_array.js:42:27)
    at Promise._settlePromiseAt (/Users/nsmith/projects/cohortable/node_modules/knex/node_modules/bluebird/js/main/promise.js:528:21)
    at Promise._settlePromises (/Users/nsmith/projects/cohortable/node_modules/knex/node_modules/bluebird/js/main/promise.js:646:14)
    at Async._drainQueue (/Users/nsmith/projects/cohortable/node_modules/knex/node_modules/bluebird/js/main/async.js:177:16)
    at Async._drainQueues (/Users/nsmith/projects/cohortable/node_modules/knex/node_modules/bluebird/js/main/async.js:187:10)
    at Async.drainQueues (/Users/nsmith/projects/cohortable/node_modules/knex/node_modules/bluebird/js/main/async.js:15:14)
    at process._tickCallback (node.js:442:13)

Is there a new way to handle transactional migrations?

[tgriesser]

Can you run that script but with DEBUG=knex:tx on the command line before running: e.g.

$ DEBUG=knex:tx knex migrate:latest

0.8 now automatically wraps migrations in a transaction, but my guess is that because MySQL doesn't support transactions for DDL stuff it's not liking the fact that we're trying to use a savepoint here.

[nathasm]

Migration File

exports.up = function(knex, Promise) {
  return knex.transaction(function(trx) {
    return trx.schema.createTable('ages', function(t) {
      t.increments('id').primary();
      t.string('name').unique().notNull();
    });
  });
};

Output

# DEBUG="knex:tx" knex --knexfile server/config/knexfile.js migrate:latest
Working directory changed to ~/project/server/config
[ { sql: 'show tables like ?',
    output: [Function],
    bindings: [ 'knex_migrations' ] } ]
{ method: 'select',
  options: {},
  bindings: [],
  sql: 'select `name` from `knex_migrations` order by `id` asc' }
{ method: 'select',
  options: {},
  bindings: [],
  sql: 'select max(`batch`) as `max_batch` from `knex_migrations`' }
  knex:tx trx4: Starting top level transaction +0ms
  knex:tx trx5: Starting nested transaction +2ms
[ { sql: 'create table `ages` (`id` int unsigned not null auto_increment primary key, `name` varchar(255) not null) default character set utf8',
    bindings: [] },
  { sql: 'alter table `ages` add unique ages_name_unique(`name`)',
    bindings: [] } ]
  knex:tx trx5 error running transaction query +9ms
  knex:tx trx5: releasing connection +0ms
  knex:tx trx4: releasing connection +1ms
Knex:warning - migrations failed with error: rollback to savepoint trx5 - ER_SP_DOES_NOT_EXIST: SAVEPOINT trx5 does not exist
TypeError: expecting an array, a promise or a thenable
    See http://goo.gl/s8MMhc
    at PromiseArray.init [as _init] (/Users/nsmith/project/node_modules/knex/node_modules/bluebird/js/main/promise_array.js:42:27)
    at Promise._settlePromiseAt (/Users/nsmith/project/node_modules/knex/node_modules/bluebird/js/main/promise.js:528:21)
    at Promise._settlePromises (/Users/nsmith/project/node_modules/knex/node_modules/bluebird/js/main/promise.js:646:14)
    at Async._drainQueue (/Users/nsmith/project/node_modules/knex/node_modules/bluebird/js/main/async.js:177:16)
    at Async._drainQueues (/Users/nsmith/project/node_modules/knex/node_modules/bluebird/js/main/async.js:187:10)
    at Async.drainQueues (/Users/nsmith/project/node_modules/knex/node_modules/bluebird/js/main/async.js:15:14)
    at process._tickCallback (node.js:442:13)

[tgriesser]

So it looks like because DDL stuff causes implicit commits in mysql it messes savepoints up - https://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

Going to try and figure out the cleanest workaround.

[tgriesser]

Also just FYI, having knex.transaction there isn't doing anything for you if you're using mysql, so probably best to remove it.

[tgriesser]

Ok this is fixed in 0.8.1 - basically it'll warn if a savepoint doesn't exist. Since we know we created it, it means something else caused an implicit commit, for example a DDL statement in mysql.

[LeviRosol]

FWIW, this issue appears to be back in 0.10.0-rc1

I got this version via npm install knex -g

When I specified 0.9.0 via npm install knex@0.9.0 -g this issue went away.

[elhigu]

@LeviRosol probably the problem of 0.10.0-rc1 is not this one, please try again when 0.10 is released

[tjwelde]

I also wasted an hour on this problem. Maybe npm install knex shouldn't install it with an unstable version

[LeviRosol]

@elhigu The problem I had with 0.10.0.rc-1 is exactly this problem. Same migration, same error.

I'll second @goldquest in that the default install probably shouldn't be an rc version, but instead the stable 0.9.0 version.

[tgriesser]

@goldquest / @LeviRosol Just changed the npm dist-tag back to 0.9.0 for latest

[elhigu]

@LeviRosol I just tried this with knex 0.10.0-rc1, the error message was:

Mikaels-MBP:temp mikaelle$ dropdb test; createdb test; DEBUG="knex:tx" node_modules/.bin/knex migrate:latest
  knex:tx trx3: Starting top level transaction +0ms
  knex:tx trx3: releasing connection +7ms
  knex:tx trx4: Starting top level transaction +4ms
  knex:tx trx5: Starting nested transaction +1ms
  knex:tx trx5: releasing connection +9ms
  knex:tx trx4: releasing connection +1ms
TypeError: expecting an array, a promise or a thenable

    See http://goo.gl/s8MMhc

    at PromiseArray.init [as _init] (/Users/mikaelle/temp/node_modules/bluebird/js/main/promise_array.js:42:27)
    at Promise._settlePromiseAt (/Users/mikaelle/temp/node_modules/bluebird/js/main/promise.js:579:21)
    at Promise._settlePromises (/Users/mikaelle/temp/node_modules/bluebird/js/main/promise.js:697:14)
    at Async._drainQueue (/Users/mikaelle/temp/node_modules/bluebird/js/main/async.js:123:16)
    at Async._drainQueues (/Users/mikaelle/temp/node_modules/bluebird/js/main/async.js:133:10)
    at Immediate.Async.drainQueues [as _onImmediate] (/Users/mikaelle/temp/node_modules/bluebird/js/main/async.js:15:14)
    at processImmediate [as _immediateCallback] (timers.js:383:17)

The error didn't mention anything about, which did seem to be a part of the problem originally in this ticket.

Knex:warning - migrations failed with error: rollback to savepoint trx5 - ER_SP_DOES_NOT_EXIST: SAVEPOINT trx5 does not exist

Anyways this is fixed before 0.10.0 by #1199

[rhys-vdw]

Just changed the npm dist-tag back to 0.9.0 for latest

My bad. I added a beta tag to rc1, but apparently I had to reassign the latest tag as well.

Thanks for that @tgriesser.