I am building a new NodeJS application with MySQL. I need to use the existing database schema. I have a mysql dump file that is loaded into the database (in a docker container). I am trying to generate models and migrations automatically and then run the migrations successfully. I am able to generate the models and migrations, however there is a SQL syntax error when running the generated migrations.
Here are the relevant versions:
Node10-alpine
"mysql": "^2.17.1",
"mysql2": "^1.6.5",
"sequelize": "^5.8.5",
"sequelize-auto": "^0.4.29",
"sequelize-auto-migrations": "^1.0.3"
I used the sequelize-auto module to generate the Models automatically. That works.
sequelize-auto -o "./models" -d sequelize_auto_test -h localhost -u username -p 5432 -x password -e mysql
I then attempted to use the sequelize-auto-migrations module to generate the Migrations and then run them automatically.
Generating the initial migration file works.
node ./node_modules/sequelize-auto-migrations/bin/makemigration --name <initial_migration_name>
However, when running the actual migration, there is a syntax error.
node ./node_modules/sequelize-auto-migrations/bin/runmigration
That works for many of the tables but then it runs into a syntax error.
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
sqlMessage:
'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \') ENGINE=InnoDB\' at line 1',
sql: 'CREATE TABLE IF NOT EXISTS `osw` () ENGINE=InnoDB;' },
sql: 'CREATE TABLE IF NOT EXISTS `osw` () ENGINE=InnoDB;' }
Here is the relevant model osw.js (generated by the sequelize-auto module):
/* jshint indent: 2 */
module.exports = function(sequelize, DataTypes) {
return sequelize.define('osw', {
OSWID: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: false,
primaryKey: true
},
IdentificationID: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: true,
references: {
model: 'itemidentification',
key: 'IdentificationID'
}
},
ProposedHours: {
type: DataTypes.DECIMAL,
allowNull: true
},
WorkStartDate: {
type: DataTypes.DATEONLY,
allowNull: true
},
WorkEndDate: {
type: DataTypes.DATEONLY,
allowNull: true
},
FormatID: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: true,
references: {
model: 'formats',
key: 'FormatID'
}
},
WorkLocationID: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: true
}
}, {
tableName: 'osw'
});
};
Here is the relevant part of the mysql dump file:
CREATE TABLE `OSW` (
`OSWID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`IdentificationID` int(10) unsigned DEFAULT NULL,
`ProposedHours` decimal(10,2) DEFAULT NULL,
`WorkStartDate` date DEFAULT NULL,
`WorkEndDate` date DEFAULT NULL,
`FormatID` int(10) unsigned DEFAULT NULL,
`WorkLocationID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`OSWID`),
KEY `OSW_FKIndex1` (`IdentificationID`),
KEY `OSW_Format` (`FormatID`),
CONSTRAINT `OSW_Format` FOREIGN KEY (`FormatID`) REFERENCES `formats` (`formatid`) ON DELETE SET NULL,
CONSTRAINT `OSW_Ident` FOREIGN KEY (`IdentificationID`) REFERENCES `itemidentification` (`identificationid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1147 DEFAULT CHARSET=utf8 PACK_KEYS=0;
UPDATE: I also ran the dump file through a SQL syntax checker and the syntax is valid. However I am still blocked by this error when attempting to run the migration.
Executing (default): CREATE TABLE IF NOT EXISTS `osw` () ENGINE=InnoDB;
{ SequelizeDatabaseError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE=InnoDB' at line 1
at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/mysql/query.js:239:16)
at Query.handler [as onResult] (/usr/src/app/node_modules/sequelize/lib/dialects/mysql/query.js:46:23)
at Query.execute (/usr/src/app/node_modules/mysql2/lib/commands/command.js:30:14)
at Connection.handlePacket (/usr/src/app/node_modules/mysql2/lib/connection.js:449:32)
at PacketParser.Connection.packetParser.p [as onPacket] (/usr/src/app/node_modules/mysql2/lib/connection.js:72:12)
at PacketParser.executeStart (/usr/src/app/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket.Connection.stream.on.data (/usr/src/app/node_modules/mysql2/lib/connection.js:79:25)
at Socket.emit (events.js:189:13)
at addChunk (_stream_readable.js:284:12)
at readableAddChunk (_stream_readable.js:265:11)
at Socket.Readable.push (_stream_readable.js:220:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
From previous event:
at Query.run (/usr/src/app/node_modules/sequelize/lib/dialects/mysql/query.js:39:12)
at runHooks.then (/usr/src/app/node_modules/sequelize/lib/sequelize.js:559:29)
From previous event:
at Promise.try.then.connection (/usr/src/app/node_modules/sequelize/lib/sequelize.js:559:12)
From previous event:
at Promise.resolve.retry (/usr/src/app/node_modules/sequelize/lib/sequelize.js:556:10)
at /usr/src/app/node_modules/retry-as-promised/index.js:70:21
at new Promise (<anonymous>)
at retryAsPromised (/usr/src/app/node_modules/retry-as-promised/index.js:60:10)
at Promise.try (/usr/src/app/node_modules/sequelize/lib/sequelize.js:543:30)
From previous event:
at Sequelize.query (/usr/src/app/node_modules/sequelize/lib/sequelize.js:500:23)
at promise.then (/usr/src/app/node_modules/sequelize/lib/query-interface.js:236:46)
at runCallback (timers.js:705:18)
at tryOnImmediate (timers.js:676:5)
at processImmediate (timers.js:658:5)
From previous event:
at QueryInterface.createTable (/usr/src/app/node_modules/sequelize/lib/query-interface.js:236:20)
at next (/usr/src/app/migrations/1-initial.js:538:48)
at /usr/src/app/migrations/1-initial.js:543:13
at new Promise (<anonymous>)
at Object.up (/usr/src/app/migrations/1-initial.js:531:16)
at Object.executeMigration (/usr/src/app/node_modules/sequelize-auto-migrations/lib/migrate.js:779:9)
at /usr/src/app/node_modules/sequelize-auto-migrations/bin/runmigration.js:92:17
at /usr/src/app/node_modules/async/dist/async.js:3110:16
at replenish (/usr/src/app/node_modules/async/dist/async.js:1011:17)
at /usr/src/app/node_modules/async/dist/async.js:1016:9
at eachLimit$1 (/usr/src/app/node_modules/async/dist/async.js:3196:24)
at Object.<anonymous> (/usr/src/app/node_modules/async/dist/async.js:1046:16)
at Object.<anonymous> (/usr/src/app/node_modules/sequelize-auto-migrations/bin/runmigration.js:89:7)
at Module._compile (internal/modules/cjs/loader.js:701:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:712:10)
at Module.load (internal/modules/cjs/loader.js:600:32)
at tryModuleLoad (internal/modules/cjs/loader.js:539:12)
at Function.Module._load (internal/modules/cjs/loader.js:531:3)
at Function.Module.runMain (internal/modules/cjs/loader.js:754:12)
at startup (internal/bootstrap/node.js:283:19)
at bootstrapNodeJSCore (internal/bootstrap/node.js:622:3)
name: 'SequelizeDatabaseError',
from NodeJS sequelize auto generate models and run migrations SQL syntax error
No comments:
Post a Comment