What does it mean knex_migration_lock table

[onesick]

Hi there.

I created a migration file by runnign CLI knex migrate:create

I have created schema and ran CLI (knex migrate:latest) and I have been getting

Using environment: development
Knex:warning - Cant take lock to run migrations: Migration table is already locked
Knex:warning - If you are sue migrations are not running you can release lock manually by deleting all the rows from migrations lock table: knex_migrations_lock
undefined

this consol out. What does it mean and how can fix them? I have been struggling for a long time and it would be really appreciated if you could provide help.

Thank you

[elhigu]

Looks like for some reason migration lock was not cleared correctly... maybe e.g. if DB connection was forcekilled or something like that...

If you are sure that you are not running migrations elsewhere, you can release the lock with:

DELETE FROM knex_migrations_lock WHERE id <> 0;

I was worrying about this when this feature was in process of being done... Do you have any possible reasons why it could have been left on?

Lock is there to protect from running migrations from multiple clients e.g. in AWS Elastic Beanstalk at the same time and leaving DB in bad state, but cleaning it off should be robust enough so that even if connection close because of client is force killed it should not be left on.

[onesick]

Hi,

Thanks for your reply. This is my first time using Knex, and I have no idea where the error could have been caused. I just created the db using 'createdb database_development' command on CLI.
I have just created a git repo https://github.com/onesick/knex-tutorial so you can take a look.
I tried to use

DELETE FROM knex_migrations_lock;

from my connection.js, but it did not work. Do I need to use something like knex('knex_migrations_lock')?
Since I did not create this table I do not know where and how to put that command on.

Thank you

[elhigu]

Hi, @onesick sorry about the delay in response. You can delete all the rows in psql console.

Something like this would do:

Mikaels-MacBook-Pro:temp mikaelle$ psql database_development
psql (9.5.0)
Type "help" for help.

database_development=# DELETE FROM knex_migrations_lock;
DELETE 1
database_development=# 

Please tell me if you are able to reproduce the problem somehow.

[onesick]

Finally I got it working! for some reason when I did \d command in 'database_development' or just \d from psql console to debug earlier I was not able to find 'knex_migrations_lock' table. But it was showing now somehow. So I just erased them as you instructed and now I have successfully integrated the schema. Thank you! I will close the ticket.

[jhnferraris]

I'm getting this error now.

Knex:warning - Cant take lock to run migrations: Migration table is already locked
Knex:warning - If you are sue migrations are not running you can release lock manually by deleting all the rows from migrations lock table: Migrations_lock

jhn:develop jhnferraris$ psql invoice
psql (9.5.3)
Type "help" for help.

invoice=# \dt
              List of relations
 Schema |        Name        | Type  | Owner
--------+--------------------+-------+-------
 public | Migrations         | table | root
 public | Migrations_lock    | table | root

What I have is this.

I tried

DELETE FROM Migrations_lock id <>0 but the response is

invoice=# DELETE FROM Migrations_lock where id <> 0;
ERROR:  relation "migrations_lock" does not exist
LINE 1: DELETE FROM Migrations_lock where id <> 0;

I tried dropping and creating my db but it still throws that error.
Any help?

[elhigu]

@jhnferraris Do you have some example code for reproducing the error?

[westmaas]

@elhigu (caveat, I'm very new to knex/express/node) I'm seeing this, I did delete all rows in the table, but still get the error. Here is the code that is doing it (inside a test - not sure I'll always want to do it this way, but playing with it for now).

beforeEach(function(done) {
    knex.migrate.rollback()
    .then(function() {
      knex.migrate.latest()
      .then(function() {
        return knex.seed.run()
        .then(function() {
          done();
        });
      });
    });
  });

Error just for completeness:

Knex:warning - Can't take lock to run migrations: Migration table is already locked
Knex:warning - If you are sure migrations are not running you can release the lock manually by deleting all the rows from migrations lock table: knex_migrations_lock
Unhandled rejection MigrationLocked: Migration table is already locked

[elhigu]

@westmaas There is bug in your code. You are not returning promise from call to knex.migrate.latest() (also you don't have to write promise christmas tree here flat then-chain works just fine and by returning promise you can avoid need for done callback).

beforeEach(function() {
  return knex.migrate.rollback()
    .then(function() {
      return knex.migrate.latest();
    })
    .then(function() {
      return knex.seed.run();
    });
});

[westmaas]

@elhigu Thanks, definitely much cleaner looking and more correct, but unfortunately, I am still getting the same error (again, would not surprise me if this is user error, let me know what else would be useful to provide).

I did a few more tests - I basically started the db over from scratch and was able to use the command line knex tool to roll forward and backward as many times as I liked and all was well. Then I tried again to do it as part of a test, I got the same error, and then (presumably as expected) the command also started failing with the same error

[elhigu]

If you can give me some minimized (one file calling .latest and .rollback + one migration file) test project / example which reproduces the problem and I can take a look. Probably you will spot the problem by yourself while reducing the test code to minimum.

You can also set DEBUG=knex:* and see what kind of queries knex is doing and why locking is not working e.g. if some queries are happening in strange order.

Btw. if I recall correctly there is now knex.migrate.forceFreeMigrationLock() or some function like that so that you don't have to remove all rows... removing all rows kind of makes system a bit vulnerable a certain race condition, however I don't know how that could possibly occur in your case.

[westmaas]

@elhigu as soon as I read this I knew what it was, and as we suspected, user error. For anyone else, make sure you didn't leave an errant knex.migrate somewhere else in your code - in my case the result of a tutorial that had you migrate to the latest in order to bootstrap, and I never removed it.

[machineghost]

For the benefit of anyone finding this later (like myself), the latest version of the fix command is:

DELETE FROM migrations_lock WHERE is_locked = 1;

(evidently the id column was removed)

[itaysabato]

Hi,

This error seems to happen to me whenever two processes are trying to migrate to the latest version at the same time (one gets an error and the other doesn't).

From the documentation (and my expectations) it seems one process should simply wait for the lock to be released and then continue without any issues. Is there a way around this?

Thanks.

[MrEfrem]

I too have the same problem. I haven't rows in migrations_lock but I permanently receive the error:

Using environment: development
Knex:warning - Can't take lock to run migrations: Migration table is already locked
Knex:warning - If you are sure migrations are not running you can release the lock manually by d
eleting all the rows from migrations lock table: migrations_lock
undefined

Database - PostgreSQL.

[elhigu]

http://knexjs.org/#Notes-about-locks

Just set all rows to 0 in migrations_lock table

[MrEfrem]

No, that doesn't help. I have migrated a database from MySQL to PostrgeSQL and had type bigint of field is_locked. Maybe therefore I saw the error. I have removed the table migrations_lock and I have started migration again. Now it's ok.

[slim-hmidi]

I got this issue when I forgot the server launched in different terminal and I executed the test in another terminal. Probably, can help anyone who missed server running when launching the server.

[LDrawe]

I got this error too and removing the migrate.rollback() line solved for me

[pkantsedalov]

@MrEfrem thank you very much for your comment. PostgreSQL really has this problem: if you have bigint as an id column data type in your knex_migrations_lock table, then this error persists.

[Decstro]

I'm doing something and when I try to run the tests I get these errors:

Can't take lock to run migrations: the migration table is already locked
If you are sure that the migrations are not running, you can release the lock manually by running 'knex migrate:unlock
MigrationLocked: Migration table is already locked

What I understand is that the database is blocked by the lock system of knex because apparently I am using the db in another service or not if it is because the tests run in parallel, total I have tried several things:

1. Remove the migration_lock table from mysql workbench
2. 'knex migrate:unlock' try to use this command (an error came out)
3. Removed rows from migration table
4. Restart the pc
5. Delete the database and run the migration again, then use db-refresh and nothing.

I don't know what to do, please help me

[vsamma]

Hi @Decstro,

I just found this thread and maybe this response is too late for you or you already found a solution, but my first idea would be that your first point is the problem: You shouldn't delete the "migration_lock" table but the entries in it. Although I have one entry in there with "is_locked" value "0".