search

timeoff-management / timeoff-management-application

Simple yet powerful absence management software for small and medium size business (community edition)
https://TimeOff.Management
MIT License
958 stars 589 forks source link

Error when running db-update and using MySQL #329

Open fparente1 opened 5 years ago

fparente1 commented 5 years ago

Hi! I'm trying to run the app un production mode using MySQL, and when the db-update script runs I keep getting the following error

` [4mSequelize [Node: 6.10.3, CLI: 2.5.1, ORM: 3.34.0, mysql: ^2.16.0]

Loaded configuration file "config/db.json". Using environment "production". == 20171219-allowance-adjustment-per-year: migrating ======= { SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:96:13) at Socket.emit (events.js:188:7) at readableAddChunk (_stream_readable.js:176:18) at Socket.Readable.push (_stream_readable.js:134:10) at TCP.onread (net.js:547:20) name: 'SequelizeDatabaseError', message: 'ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint', parent: { Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint at Query.Sequence._packetToError (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:96:13) at Socket.emit (events.js:188:7) at readableAddChunk (_stream_readable.js:176:18) at Socket.Readable.push (_stream_readable.js:134:10) at TCP.onread (net.js:547:20)

   at Protocol._enqueue (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:144:48)
   at Connection.query (/app/timeoff-management/node_modules/mysql/lib/Connection.js:200:25)
   at /app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:40:21
   at Promise._execute (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/debuggability.js:313:9)
   at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:483:18)
   at new Promise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:79:10)
   at Query.run (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:39:17)
   at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:849:20
   at /app/timeoff-management/node_modules/retry-as-promised/index.js:39:21
   at Promise._execute (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/debuggability.js:313:9)
   at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:483:18)
   at new Promise (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:79:10)
   at retryAsPromised (/app/timeoff-management/node_modules/retry-as-promised/index.js:29:10)
   at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:848:12
   at tryCatcher (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)
   at Promise._settlePromiseFromHandler (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:512:31)
   at Promise._settlePromise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:569:18)
   at Promise._settlePromise0 (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:614:10)
   at Promise._settlePromises (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:694:18)
   at _drainQueueStep (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:138:12)
   at _drainQueue (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:131:9)
   at Async._drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:147:5)
   at Immediate.Async.drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:17:14)
   at runCallback (timers.js:672:20)
   at tryOnImmediate (timers.js:645:5)
   at processImmediate [as _immediateCallback] (timers.js:617:5)
 code: 'ER_CANNOT_ADD_FOREIGN',
 errno: 1215,
 sqlMessage: 'Cannot add foreign key constraint',
 sqlState: 'HY000',
 index: 0,
 sql: 'CREATE TABLE IF NOT EXISTS `user_allowance_adjustment` (`id` INTEGER NOT NULL auto_increment , `year` INTEGER NOT NULL DEFAULT \'2019\', `adjustment` INTEGER NOT NULL DEFAULT 0, `carried_over_allowance` INTEGER NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL, `user_id` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;' },

original: { Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint at Query.Sequence._packetToError (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:96:13) at Socket.emit (events.js:188:7) at readableAddChunk (_stream_readable.js:176:18) at Socket.Readable.push (_stream_readable.js:134:10) at TCP.onread (net.js:547:20)

   at Protocol._enqueue (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:144:48)
   at Connection.query (/app/timeoff-management/node_modules/mysql/lib/Connection.js:200:25)
   at /app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:40:21
   at Promise._execute (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/debuggability.js:313:9)
   at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:483:18)
   at new Promise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:79:10)
   at Query.run (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:39:17)
   at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:849:20
   at /app/timeoff-management/node_modules/retry-as-promised/index.js:39:21
   at Promise._execute (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/debuggability.js:313:9)
   at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:483:18)
   at new Promise (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:79:10)
   at retryAsPromised (/app/timeoff-management/node_modules/retry-as-promised/index.js:29:10)
   at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:848:12
   at tryCatcher (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)
   at Promise._settlePromiseFromHandler (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:512:31)
   at Promise._settlePromise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:569:18)
   at Promise._settlePromise0 (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:614:10)
   at Promise._settlePromises (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:694:18)
   at _drainQueueStep (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:138:12)
   at _drainQueue (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:131:9)
   at Async._drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:147:5)
   at Immediate.Async.drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:17:14)
   at runCallback (timers.js:672:20)
   at tryOnImmediate (timers.js:645:5)
   at processImmediate [as _immediateCallback] (timers.js:617:5)
 code: 'ER_CANNOT_ADD_FOREIGN',
 errno: 1215,
 sqlMessage: 'Cannot add foreign key constraint',
 sqlState: 'HY000',
 index: 0,
 sql: 'CREATE TABLE IF NOT EXISTS `user_allowance_adjustment` (`id` INTEGER NOT NULL auto_increment , `year` INTEGER NOT NULL DEFAULT \'2019\', `adjustment` INTEGER NOT NULL DEFAULT 0, `carried_over_allowance` INTEGER NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL, `user_id` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;' },

sql: 'CREATE TABLE IF NOT EXISTS user_allowance_adjustment (id INTEGER NOT NULL auto_increment , year INTEGER NOT NULL DEFAULT \'2019\', adjustment INTEGER NOT NULL DEFAULT 0, carried_over_allowance INTEGER NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES Users (id) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;' }

`

Seems like the table creation sequence is not right, am I missing something? Any help will be highly appreciated, thanks in advance.

vpp commented 5 years ago

hi @fparente1

Lets see what people who use it with MySQL would share.

App relies on Sequelize.js to deal with the SQLs.

fparente1 commented 5 years ago

Thanks @vpp

zefixlluja commented 5 years ago

Hi @fparente1 - which version of the application are you trying to run? I had the same issue using the 0.10 tag - however, using current master worked fine for me.

fparente1 commented 5 years ago

Hi @zefixlluja thanks for coming back :) I apologise for my late response, I was on holidays. I am using the latest from master too.

fparente1 commented 5 years ago

I just noticed that the error I;m getting today is slightly different, but still related to DB missing entities. For me it feels like that when Sequelize db:migrate runs the order of the table creation is not right?

` ========= RUNNING DB-UPDATE =========

TimeOff.Management@0.10.0 db-update /app/timeoff-management node node_modules/.bin/sequelize db:migrate --config=config/db.json --models-path=lib/model/db/

Sequelize [Node: 8.14.0, CLI: 2.5.1, ORM: 3.34.0, mysql: ^2.16.0]

Loaded configuration file "config/db.json". Using environment "production". == 20170103144054-add_default_date_format: migrating ======= Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'timeoffmanagement.Companies' doesn't exist at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20) == 20170103144054-add_default_date_format: migrated (0.055s) == 20170329060832-rename_allowence_to_allowance: migrating ======= Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'timeoffmanagement.Departments' doesn't exist at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20) == 20170329060832-rename_allowence_to_allowance: migrated (2.095s) == 20170530-add_auto_approve_flag_to_user: migrating ======= Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'timeoffmanagement.Users' doesn't exist at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20) == 20170530-add_auto_approve_flag_to_user: migrated (0.034s) == 20171218-company-wide-message: migrating ======= Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'timeoffmanagement.Companies' doesn't exist at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20) == 20171218-company-wide-message: migrated (0.026s) == 20171219-allowance-adjustment-per-year: migrating ======= { SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20) name: 'SequelizeDatabaseError', message: 'ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint', parent: { Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint at Query.Sequence._packetToError (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20)

at Protocol._enqueue (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/app/timeoff-management/node_modules/mysql/lib/Connection.js:200:25)
at /app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:40:21
at Promise._execute (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/debuggability.js:313:9)
at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:483:18)
at new Promise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:79:10)
at Query.run (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:39:17)
at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:849:20
at /app/timeoff-management/node_modules/retry-as-promised/index.js:39:21
at Promise._execute (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/debuggability.js:313:9)
at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:483:18)
at new Promise (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:79:10)
at retryAsPromised (/app/timeoff-management/node_modules/retry-as-promised/index.js:29:10)
at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:848:12
at tryCatcher (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:512:31)
at Promise._settlePromise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:569:18)
at Promise._settlePromise0 (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:614:10)
at Promise._settlePromises (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:694:18)
at _drainQueueStep (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:138:12)
at _drainQueue (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:131:9)
at Async._drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:147:5)
at Immediate.Async.drainQueues [as _onImmediate] (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:17:14)
at runCallback (timers.js:810:20)
at tryOnImmediate (timers.js:768:5)
at processImmediate [as _immediateCallback] (timers.js:745:5)
 code: 'ER_CANNOT_ADD_FOREIGN',
 errno: 1215,
 sqlMessage: 'Cannot add foreign key constraint',
 sqlState: 'HY000',
 index: 0,
 sql: 'CREATE TABLE IF NOT EXISTS `user_allowance_adjustment` (`id` INTEGER NOT NULL auto_increment , `year` INTEGER NOT NULL DEFAULT \'2019\', `adjustment` INTEGER NOT NULL DEFAULT 0, `carried_over_allowance` INTEGER NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL, `user_id` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;' },

original: { Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint at Query.Sequence._packetToError (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20)

at Protocol._enqueue (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/app/timeoff-management/node_modules/mysql/lib/Connection.js:200:25)
at /app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:40:21
at Promise._execute (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/debuggability.js:313:9)
at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:483:18)
at new Promise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:79:10)
at Query.run (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:39:17)
at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:849:20
at /app/timeoff-management/node_modules/retry-as-promised/index.js:39:21
at Promise._execute (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/debuggability.js:313:9)
at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:483:18)
at new Promise (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:79:10)
at retryAsPromised (/app/timeoff-management/node_modules/retry-as-promised/index.js:29:10)
at /app/timeoff-management/node_modules/sequelize/lib/sequelize.js:848:12
at tryCatcher (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:512:31)
at Promise._settlePromise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:569:18)
at Promise._settlePromise0 (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:614:10)
at Promise._settlePromises (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:694:18)
at _drainQueueStep (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:138:12)
at _drainQueue (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:131:9)
at Async._drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:147:5)
at Immediate.Async.drainQueues [as _onImmediate] (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:17:14)
at runCallback (timers.js:810:20)
at tryOnImmediate (timers.js:768:5)
at processImmediate [as _immediateCallback] (timers.js:745:5)
 code: 'ER_CANNOT_ADD_FOREIGN',
 errno: 1215,
 sqlMessage: 'Cannot add foreign key constraint',
 sqlState: 'HY000',
 index: 0,
 sql: 'CREATE TABLE IF NOT EXISTS `user_allowance_adjustment` (`id` INTEGER NOT NULL auto_increment , `year` INTEGER NOT NULL DEFAULT \'2019\', `adjustment` INTEGER NOT NULL DEFAULT 0, `carried_over_allowance` INTEGER NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL, `user_id` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;' },

sql: 'CREATE TABLE IF NOT EXISTS user_allowance_adjustment (id INTEGER NOT NULL auto_increment , year INTEGER NOT NULL DEFAULT \'2019\', adjustment INTEGER NOT NULL DEFAULT 0, carried_over_allowance INTEGER NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES Users (id) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;' } npm ERR! code ELIFECYCLE npm ERR! errno 1 npm ERR! TimeOff.Management@0.10.0 db-update: node node_modules/.bin/sequelize db:migrate --config=config/db.json --models-path=lib/model/db/ npm ERR! Exit status 1 npm ERR! npm ERR! Failed at the TimeOff.Management@0.10.0 db-update script. npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in: npm ERR! /app/.npm/_logs/2019-02-15T15_03_37_608Z-debug.log

TimeOff.Management@0.10.0 start /app/timeoff-management node bin/wwww

Unhandled rejection SequelizeDatabaseError: ER_BLOB_CANT_HAVE_DEFAULT: BLOB/TEXT column 'timezone' can't have a default value at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20) Executing (default): DELETE FROM Sessions WHERE expires < '2019-02-15 15:18:40' Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'timeoffmanagement.Sessions' doesn't exist at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query. (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:468:16) at Query.Sequence.end (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24) at Query.ErrorPacket (/app/timeoff-management/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/app/timeoff-management/node_modules/mysql/lib/Connection.js:502:10) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:601:20)

`

randolf-alegre commented 5 years ago

This what I did.

  1. Run the App. (By doing this it will create all the tables in the database.)
  2. Run the migration : node node_modules/.bin/sequelize db:migrate --config=config/db.json --models-path=lib/model/db/
  3. It will flagged an error: _Loaded configuration file "config/db.json". Using environment "development". == 20190118-chnage-type-value-for-api-token: migrating ======= { SequelizeDatabaseError: ER_PARSE_ERROR: 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 'PRAGMA foreignkeys=off' at line 1

to fix this, go to this migration and change PRAGMA foreign_keys=off; to SET FOREIGN_KEY_CHECKS=0. Change also PRAGMA foreign_keys=on; to SET FOREIGN_KEY_CHECKS=1

Then run the migration again and viola!!!

yunas commented 2 years ago

though I asked here but to perform it on a global level one can run this query in query editor. "SET GLOBAL FOREIGN_KEY_CHECKS = 0;"

Reference

randolf-alegre commented 2 years ago

Hi Yunas,

Sorry for the late reply. As far as I remember you need to change it to its specific migration file where you encounter the error.

On Wed, Sep 7, 2022 at 3:37 AM yunas @.***> wrote:

@randolf-alegre https://github.com/randolf-alegre this might look a lame request but can you please elaborate on where to change pragma? is it migration specific or for all migrations?

— Reply to this email directly, view it on GitHub https://github.com/timeoff-management/timeoff-management-application/issues/329#issuecomment-1238573269, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAUNAFW5JQU4Z5ZJERLBJH3V46MOFANCNFSM4GTA7N4Q . You are receiving this because you were mentioned.Message ID: <timeoff-management/timeoff-management-application/issues/329/1238573269@ github.com>