Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Unable to create MySQL tables (ER_CANNOT_ADD_FOREIGN) #3

Open
Mr-Reca opened this issue Sep 28, 2020 · 3 comments
Open

Unable to create MySQL tables (ER_CANNOT_ADD_FOREIGN) #3

Mr-Reca opened this issue Sep 28, 2020 · 3 comments

Comments

@Mr-Reca
Copy link

Mr-Reca commented Sep 28, 2020

Hello,

I'm trying to use the container in a k8s cluster. When I try to run it, it returns me some errors and it doesn't create the required tables.

I'm using nosinovacao/timeoff-management:20.06.0 and I've configured the environment variables MYSQL_PASSWORD, MYSQL_HOST, MYSQL_USER and MYSQL_DATABASE. They are connected and I granted ALL PRIVILEGES on the database to that user.

Should I open an issue to the main repo?

This is all the log (removing sensible content)

========= PRINTING CONFIGURATION =========
{
  "allow_create_new_accounts" : true,
  "send_emails"              : false,
  "application_sender_email" : "",
  "email_transporter" : {
    "host" : "<email_host>",
    "port" : 25,
    "auth" : {
      "user" : "<username>",
      "pass" : "<password>"
    }
  },
  "crypto_secret" : "<this_is_a_secret>",
  "application_domain" : "<this_is_a_domain>",
  "promotion_website_domain" : "<this_is_a_domain>"
}

> [email protected] 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.35.1, mysql: ^2.18.1]

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.<anonymous> (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:526:10)
    at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:488: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:92:8)
    at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:526: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:79:18)
    at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:526: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:198: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:384:9)
    at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:518:18)
    at new Promise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:103: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:384:9)
    at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:518:18)
    at new Promise (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:103: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:547:31)
    at Promise._settlePromise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:729:18)
    at _drainQueueStep (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues [as _onImmediate] (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:15: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 \'2020\', `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:79:18)
    at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:526: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:198: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:384:9)
    at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:518:18)
    at new Promise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:103: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:384:9)
    at Promise._resolveFromExecutor (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:518:18)
    at new Promise (/app/timeoff-management/node_modules/retry-as-promised/node_modules/bluebird/js/release/promise.js:103: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:547:31)
    at Promise._settlePromise (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:729:18)
    at _drainQueueStep (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues [as _onImmediate] (/app/timeoff-management/node_modules/sequelize/node_modules/bluebird/js/release/async.js:15: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 \'2020\', `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 \'2020\', `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! [email protected] 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 [email protected] 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!     /root/.npm/_logs/2020-09-28T13_37_58_892Z-debug.log

> [email protected] start /app/timeoff-management
> node bin/wwww

Unhandled rejection SequelizeDatabaseError: ER_BLOB_CANT_HAVE_DEFAULT: BLOB, TEXT, GEOMETRY or JSON 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.<anonymous> (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:526:10)
    at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:488: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:92:8)
    at Protocol._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/app/timeoff-management/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:526: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)
@joaopires
Copy link
Contributor

Hello @Mr-Reca. Don't open the issue on the main repo. We will look into it.

Thank you

@Mr-Reca
Copy link
Author

Mr-Reca commented Sep 28, 2020

Thanks @joaopires

Some months ago I identified this issue here, in case it helps:
timeoff-management/timeoff-management-application#398

@joaopires
Copy link
Contributor

joaopires commented Mar 8, 2021 •
edited
Loading

Hi @Mr-Reca

Check out nosinovacao/timeoff-management:21.03.1. I believe this issue is solved now. Feel free to try and give feedback here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@joaopires @Mr-Reca