New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migrations in 0.8 using MySQL #805

Closed
nathasm opened this Issue May 1, 2015 · 12 comments

Comments

Projects
None yet
7 participants
@nathasm

nathasm commented May 1, 2015

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

This comment has been minimized.

Owner

tgriesser commented May 1, 2015

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

This comment has been minimized.

nathasm commented May 1, 2015

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 tgriesser added transactions and removed migrations labels May 1, 2015

@tgriesser

This comment has been minimized.

Owner

tgriesser commented May 1, 2015

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

This comment has been minimized.

Owner

tgriesser commented May 1, 2015

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 added a commit that referenced this issue May 1, 2015

Fix for #805, warn for non-existent savepoints
In MySQL if a savepoint is missing, it means something
else (most likely DDL) triggered an implicit commit. In this
case rather than erroring, let's give a warning message
so it's possible to use the same codepath as a DB where
this actually works.
@tgriesser

This comment has been minimized.

Owner

tgriesser commented May 1, 2015

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.

@tgriesser tgriesser closed this May 1, 2015

@LeviRosol

This comment has been minimized.

LeviRosol commented Feb 12, 2016

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

This comment has been minimized.

Collaborator

elhigu commented Feb 13, 2016

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

@tjwelde

This comment has been minimized.

tjwelde commented Feb 13, 2016

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

@LeviRosol

This comment has been minimized.

LeviRosol commented Feb 13, 2016

@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

This comment has been minimized.

Owner

tgriesser commented Feb 14, 2016

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

@elhigu

This comment has been minimized.

Collaborator

elhigu commented Feb 14, 2016

@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

This comment has been minimized.

Collaborator

rhys-vdw commented Feb 15, 2016

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment