Unhandled rejection SequelizeDatabaseError

[jpdentone]

full error

Unhandled rejection SequelizeDatabaseError: ER_BLOB_CANT_HAVE_DEFAULT: BLOB, TEXT, GEOMETRY or JSON column 'timezone' can't have a default value at Query.formatError (/var/www/timeoff/node_modules/sequelize/lib/dialects/mysql/query.js:175:14) at Query._callback (/var/www/timeoff/node_modules/sequelize/lib/dialects/mysql/query.js:49:21) at Query.Sequence.end (/var/www/timeoff/node_modules/mysql/lib/protocol/sequences/Sequence.js:88:24) at Query.ErrorPacket (/var/www/timeoff/node_modules/mysql/lib/protocol/sequences/Query.js:90:8) at Protocol._parsePacket (/var/www/timeoff/node_modules/mysql/lib/protocol/Protocol.js:279:23) at Parser.write (/var/www/timeoff/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/var/www/timeoff/node_modules/mysql/lib/protocol/Protocol.js:39:16) at Socket.<anonymous> (/var/www/timeoff/node_modules/mysql/lib/Connection.js:103:28) 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)

any one got an idea how to fix/work around that?

[vpp]

@jpdentone I do not have exposure for app instance that run against MySQL.

So lets see what other people would advise.

[jpdentone]

I was able to fix that by changing the timezone datatyp in lib/model/db/company.js

timezone : { type : DataTypes.TEXT, allowNull : true, defaultValue : 'Europe/London', comment : 'Timezone current company is located in', },

to

timezone : { type : DataTypes.STRING, allowNull : true, defaultValue : 'Europe/London', comment : 'Timezone current company is located in', },

however after that I got another error

Unhandled rejection SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

Now I'm able to see the tables in the DB but the app still does not want to run

has anyone got this running on NODE_ENV=profuction / mysql?

[julienmalik]

Just to confirm the issue ; I had the exact same error deploying it yesterday. I just switched back to sqllite for the time being.

[vpp]

SQLite is the database I rely while developing the app and running cloud version.

There was assumption that using ORM would automatically transform the app into multi database dialect. Which clearly not a case unfortunate.

But on other hand there are definitely other people using the app with MySQL and Postgres so maybe there should be some extra configuration to be set.

Let's see what other people say.

On 4 Apr 2018, at 14:57, julienmalik notifications@github.com wrote:

Just to confirm the issue ; I had the exact same error deploying it yesterday. I just switched back to sqllite for the time being.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[marcos-muino-garcia]

I was able to run the app using postgresql as db after change this packages versions:

• "sequelize": "^3.30.2" (was "^3.19.2")
• "sequelize-cli": "^2.6.0" (was "2.5.1")

Also the postgresql package needs to be added:

• "pg": "^6.1.5" (the postgresql package)

After that, I could use postgres as dialect in the db.json.

Hope it helps!

[vpp]

Very good point!

I completely forgot that dependencies were not updated for quite a while.

On 4 Apr 2018, at 20:39, Marcos Muíño García notifications@github.com wrote:

I was able to run the app using postgresql as db after change this packages versions:

"sequelize": "^3.30.2" (was "^3.19.2") "sequelize-cli": "^2.6.0" (was "2.5.1") Also the postgresql package needs to be added:

"pg": "^6.1.5" (the postgresql package) After that, I could use postgres as dialect in the db.json.

Hope it helps!

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[jpdentone]

I updated all the dependencies and got the same error

Unhandled rejection SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

this is the package.json file after the update

"dependencies": { "bluebird": "^2.10.2", "body-parser": "^1.8.4", "connect-session-sequelize": "3.0.0", "cookie-parser": "^1.3.5", "csv": "~0.4.6", "debug": "~2.0.0", "dotenv": "^5.0.1", "express": "^4.13.4", "express-handlebars": "^3.0.0", "express-session": "^1.13.0", "formidable": "~1.0.17", "html-to-text": "^3.2.0", "ical-generator": "^0.2.7", "joi": "~12.0.0", "ldapauth-fork": "^2.5.2", "moment": "^2.22.0", "moment-timezone": "^0.5.14", "morgan": "^1.3.2", "mysql": "^2.15.0", "nconf": "^0.8.4", "node-uuid": "^1.4.7", "nodemailer": "^1.11.0", "nodemailer-smtp-transport": "^1.1.0", "optimist": "^0.6.1", "passport": "^0.3.2", "passport-local": "^1.0.0", "sequelize": "^3.32.1", "sequelize-cli": "^2.8.0", "serve-favicon": "^2.5.0", "sqlite3": "^3.1.1", "underscore": "^1.8.3", "validator": "^3.43.0" },

[simllll]

As a workaround you can just run this query manually @jpdentone :

CREATE TABLE IF NOT EXISTS `DepartmentSupervisor` 
(
    `created_at` DATETIME NOT NULL, 
    `department_id` INTEGER , 
    `user_id` INTEGER , 
    PRIMARY KEY (`department_id`, `user_id`), 
    /* FOREIGN KEY (`department_id`) REFERENCES `Departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,  */
    FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

[gazben]

@simllll It produces the same error

[simllll]

Try my fork, I have updated a lot regarding this https://github.com/simllll/timeoff-application

[gazben]

@simllll Kinf of same: node 9, mysql 5.7 docker environment

app_1  | == 20171219-allowance-adjustment-per-year: migrating =======
app_1  | 
app_1  | ERROR: Cannot add foreign key constraint
app_1  | 
app_1  | npm ERR! code ELIFECYCLE
app_1  | npm ERR! errno 1
app_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/`

[waqasraz]

I think it only works with SQLite. Here is what I am getting for @bardaos postgres requirements == 20171219-allowance-adjustment-per-year: migrating ======= { SequelizeDatabaseError: relation "Users" does not exist at Query.formatError (/home/ubuntu/timeoff-management/node_modules/sequelize/lib/dialects/postgres/query.js:357:14) at Query.<anonymous> (/home/ubuntu/timeoff-management/node_modules/sequelize/lib/dialects/postgres/query.js:88:19) at emitOne (events.js:96:13) at Query.emit (events.js:188:7) at Query.handleError (/home/ubuntu/timeoff-management/node_modules/pg/lib/query.js:143:8) at Connection.<anonymous> (/home/ubuntu/timeoff-management/node_modules/pg/lib/client.js:180:26) at emitOne (events.js:96:13) at Connection.emit (events.js:188:7) at Socket.<anonymous> (/home/ubuntu/timeoff-management/node_modules/pg/lib/connection.js:136:12) 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:551:20) name: 'SequelizeDatabaseError', message: 'relation "Users" does not exist',

"sequelize": "^3.30.2" "sequelize-cli": "^2.6.0" "pg": "^6.1.5"