Mysql migration error


#1

Hello. I have been using sqlite for my application so far, and everything has been fine. I changed to mysql for deployment, and when trying to run migrations, I get this error.

After scouting the internet for a solution I discovered this is not AdonisJS related.

I have tried making all foreign key fields not nullable, but to no avail. Any other ideas are highly appreciated. Thanks.


#2

You correctly stated that this is not Adonis related. This is purely a database issue.

I suggest you run your migration with debug flags set and watch for actual SQL queries. Then run those queries manually and observe what is actually breaking.

To see the queries you can run migration like this:

DEBUG=knex:* node ace ...

#3
  • Do you have data in the database already?
  • Have you tried running the migration on an empty database?
  • Do column data types match on PK and FK?

#4

Can you share the code of the migration in which u get this error?


#5

Thanks, @moltar for replying. The database has no data, I ran it on an empty database with same results, and yes, the PK and FK columns have the same data types. Any ideas why this would still be a problem ?


#6

Hello @virk. Thanks a lot for replying. Here’s my tokens table migration.


'use strict'

const Schema = use('Schema')

/**
 * The 'tokens' table schema
 */
class TokensSchema extends Schema {
  /**
   * Create tokens table
   * @method up
   *
   * @returns {void}
   */
  up () {
    this.create('tokens', table => {
      table.increments()
      table.integer('user_id').unsigned().references('id').inTable('users')
      table.string('token', 40).notNullable().unique()
      table.string('type', 80).notNullable()
      table.boolean('is_revoked').defaultTo(false)
      table.timestamps()
    })
  }
  /**
   * Drop tokens table
   * @method down
   *
   * @returns {void}
   */
  down () {
    this.drop('tokens')
  }
}

module.exports = TokensSchema


And here’s my users table migration:


'use strict'

const Schema = use('Schema')

/**
 * The 'users' table schema
 */
class UserSchema extends Schema {
  /**
   * Create users table
   * @method up
   *
   * @returns {void}
   */
  up () {
    this.create('users', table => {
      table.integer('id')
      table.string('name', 80).notNullable()
      table.string('email', 254).notNullable().unique()
      table.string('password', 60).notNullable()
      table.timestamps()
    })
  }

  /**
   * Drop users table
   * @method down
   *
   * @returns {void}
   */
  down () {
    this.drop('users')
  }
}

module.exports = UserSchema



#7
table.integer('user_id').unsigned()
table.integer('id')

That’s your problem. Unsigned is a different type.


#8

I hope you are creating the users table first?


#9

Yes, the users table migration runs first.


#10

This wasn’t the case before. I only added unsigned() after some research from the internet. And by the way, I just noticed the default migrations for a new adonis app are exactly like this, and they work fine. Can’t seem to figure out what the problem is.


#11

Can you post raw SQL output from the debug output?


#12

Yup that’s the problem @moltar has pointed. Lemme expand on it.

For the users table, you are using table.integer('id') . Which creates a signed integer.

Whereas in the tokens schema, you are setting foreign key to be unsigned, which is a mis-match.

What you need is to use the increments method and not the integer method.

So it will be as follows.

  up () {
    this.create('users', table => {
      table.increments('id')
    })
  }

#13

Hi @moltar, thanks for the tip about debug flags on migrations-- I’d never seen them. Is there a resource I can use to learn more about them?


#14

See Knex.js docs


#15

Will do, thanks


#16

obviously, mine are working, i did this :

  up() {
    this.create('people', (table) => {
      table.increments('id')
      table.string('name', 100).notNullable()
      table.string('code', 100).notNullable().unique()
      table.integer('parent_id').unsigned().nullable()
      table.timestamps()
      table.index('code')
      table.foreign('parent_id').references('people.id')
    })
  }