javascript - Sequelize can't create table but when I run the same in MySQL CLI it works

I am using sequelize and have run into a weird error:

Executing (default): CREATE TABLE IF NOT EXISTS `groups` (`id` INTEGER NOT NULL auto_increment , `groupname` VARCHAR(255), `groupkey` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `username` VARCHAR(255) UNIQUE, `password` VARCHAR(255), `salt` VARCHAR(255), `token` VARCHAR(255), `group_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `messages` (`id` INTEGER NOT NULL auto_increment , `message` VARCHAR(255), `group_id` INTEGER, `user_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB;
Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'crew.users' (errno: 150)
    at module.exports.Query.formatError (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:160:16)
    at Query._callback (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:38:23)
    at Query.Sequence.end (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query.ErrorPacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/ubuntu/public/server/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:746:14)
Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'crew.messages' (errno: 150)
    at module.exports.Query.formatError (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:160:16)
    at Query._callback (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:38:23)
    at Query.Sequence.end (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query.ErrorPacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/ubuntu/public/server/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:746:14)

The weird part is, when I try executing those commands in MySQL CLI, it works perfectly:

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `groups` (`id` INTEGER NOT NULL auto_increment , `groupname` VARCHAR(255), `groupkey` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `username` VARCHAR(255) UNIQUE, `password` VARCHAR(255), `salt` VARCHAR(255), `token` VARCHAR(255), `group_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `messages` (`id` INTEGER NOT NULL auto_increment , `message` VARCHAR(255), `group_id` INTEGER, `user_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Here is how I am defining the tables:

var dbconfig = {};
dbconfig.database = process.env.database || 'crew';
dbconfig.username = process.env.username || 'root';
dbconfig.password = process.env.password || '';
dbconfig.hostname = process.env.hostname || 'localhost';

var sequelize = new Sequelize(dbconfig.database, dbconfig.username, dbconfig.password, {
  host: dbconfig.hostname
});

var User = sequelize.define('users', {
  username: {
    type: Sequelize.STRING, 
    unique: true
  },
  password: Sequelize.STRING,
  salt: Sequelize.STRING,
  token: Sequelize.STRING,
  group_id: {
    type: Sequelize.INTEGER,
    references: 'groups',
    referencesKey: 'id'
  }
});

var Message = sequelize.define('message', {
  message: Sequelize.STRING,
  group_id: {
    type: Sequelize.INTEGER,
    references: 'groups',
    referencesKey: 'id'
  },
  user_id: {
    type: Sequelize.INTEGER,
    references: 'users',
    referencesKey: 'id'
  }
});

var Group = sequelize.define('groups', {
  groupname: Sequelize.STRING,
  groupkey: Sequelize.STRING
});


Group.sync({force: true});
User.sync({force: true});
Message.sync({force: true});

I am using sequelize and have run into a weird error:

Executing (default): CREATE TABLE IF NOT EXISTS `groups` (`id` INTEGER NOT NULL auto_increment , `groupname` VARCHAR(255), `groupkey` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `username` VARCHAR(255) UNIQUE, `password` VARCHAR(255), `salt` VARCHAR(255), `token` VARCHAR(255), `group_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `messages` (`id` INTEGER NOT NULL auto_increment , `message` VARCHAR(255), `group_id` INTEGER, `user_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB;
Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'crew.users' (errno: 150)
    at module.exports.Query.formatError (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:160:16)
    at Query._callback (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:38:23)
    at Query.Sequence.end (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query.ErrorPacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/ubuntu/public/server/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:746:14)
Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'crew.messages' (errno: 150)
    at module.exports.Query.formatError (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:160:16)
    at Query._callback (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:38:23)
    at Query.Sequence.end (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query.ErrorPacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/ubuntu/public/server/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:746:14)

The weird part is, when I try executing those commands in MySQL CLI, it works perfectly:

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `groups` (`id` INTEGER NOT NULL auto_increment , `groupname` VARCHAR(255), `groupkey` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `username` VARCHAR(255) UNIQUE, `password` VARCHAR(255), `salt` VARCHAR(255), `token` VARCHAR(255), `group_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `messages` (`id` INTEGER NOT NULL auto_increment , `message` VARCHAR(255), `group_id` INTEGER, `user_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Here is how I am defining the tables:

var dbconfig = {};
dbconfig.database = process.env.database || 'crew';
dbconfig.username = process.env.username || 'root';
dbconfig.password = process.env.password || '';
dbconfig.hostname = process.env.hostname || 'localhost';

var sequelize = new Sequelize(dbconfig.database, dbconfig.username, dbconfig.password, {
  host: dbconfig.hostname
});

var User = sequelize.define('users', {
  username: {
    type: Sequelize.STRING, 
    unique: true
  },
  password: Sequelize.STRING,
  salt: Sequelize.STRING,
  token: Sequelize.STRING,
  group_id: {
    type: Sequelize.INTEGER,
    references: 'groups',
    referencesKey: 'id'
  }
});

var Message = sequelize.define('message', {
  message: Sequelize.STRING,
  group_id: {
    type: Sequelize.INTEGER,
    references: 'groups',
    referencesKey: 'id'
  },
  user_id: {
    type: Sequelize.INTEGER,
    references: 'users',
    referencesKey: 'id'
  }
});

var Group = sequelize.define('groups', {
  groupname: Sequelize.STRING,
  groupkey: Sequelize.STRING
});


Group.sync({force: true});
User.sync({force: true});
Message.sync({force: true});

Share Improve this question asked Apr 13, 2015 at 22:06 Arian Faurtosh 18.5k23 gold badges80 silver badges117 bronze badges

Add a comment |

2 Answers 2

Sorted by: Reset to default 17

Figured it out!! Hopefully this will help others.

So basically the commands below are async operations, they get executed at different times, resulting in sequelize to fail, since the tables are dependent on other tables, order of execution matters.

Group.sync({force: true});
User.sync({force: true});
Message.sync({force: true});

Instead of doing the above, if you call sync() on sequelize instead, sequelize knows exactly what order the commands need to executed in.

sequelize.sync();

const sequelize = new Sequelize(database, user, password, {
  host,
  port,
  dialect: "postgres",
  logging: false,
  sync: true, //create the table if it not exists
});

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

javascript - Sequelize can't create table but when I run the same in MySQL CLI it works - Stack Overflow

2 Answers 2

与本文相关的文章

评论列表(0)