Unhandled rejection SequelizeDatabaseError: ER_BLOB_CANT_HAVE_DEFAULT: BLOB/TEXT column 'timezone' can't have a default value

[ruben-farma]

I created a docker image from the Dockerfile in the repo. with this modification: RUN npm install --production RUN npm install mysql

# -------------------------------------------------------------------
# Minimal dockerfile from alpine base
#
# Instructions:
# =============
# 1. Create an empty directory and copy this file into it.
#
# 2. Create image with: 
#   docker build --tag timeoff:latest .
#
# 3. Run with: 
#   docker run -d -p 3000:3000 --name alpine_timeoff timeoff
#
# 4. Login to running container (to update config (vi config/app.json): 
#   docker exec -ti --user root alpine_timeoff /bin/sh
# --------------------------------------------------------------------
FROM alpine:3.8

EXPOSE 3000

LABEL org.label-schema.schema-version="1.0"
LABEL org.label-schema.docker.cmd="docker run -d -p 3000:3000 --name alpine_timeoff"

RUN apk add --no-cache \
    git \
    make \
    nodejs npm \
    python \
    vim

RUN adduser --system app --home /app
USER app
WORKDIR /app
RUN git clone https://github.com/timeoff-management/application.git timeoff-management
WORKDIR /app/timeoff-management

RUN npm install --production 
RUN npm install mysql

CMD npm start

Then I run it with docker-compose and the config files in readonly option as volume:

app:
    image: timeoff
    container_name: 'timeoff_app'
    restart: unless-stopped
    environment:
      - NODE_ENV=production
    volumes:
#      - ./volumes/config:/app/timeoff-management/config
      - ./volumes/config:/app/timeoff-management/config:ro
    ports:
      - '3000:3000'
    depends_on:
      - "db"
  db:
    image: 'mariadb:10.1.22'
    container_name: 'timeoff_db'
    restart: unless-stopped
    volumes:
      - ./volumes/db:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=timeoff
      - MYSQL_DATABASE=timeoff
      - MYSQL_USER=timeoff
      - MYSQL_PASSWORD=timeoff

-db.json

  "production": {
    "username": "timeoff",
    "password": "timeoff",
    "database": "timeoff",
    "host": "timeoff_db",
    "dialect": "mysql"
  }

[Node: 8.14.0, CLI: 2.5.1, ORM: 3.35.1, mysql: ^2.17.1]

docker logs timeoff_app

> 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.<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:525:10)
    at Query._callback (/app/timeoff-management/node_modules/mysql/lib/Connection.js:491: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: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:91:28)
    at Socket.<anonymous> (/app/timeoff-management/node_modules/mysql/lib/Connection.js:525: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)

[idonca]

Hello, I had the same issue and I updated the following packages in package.json: "sequelize": "^5.21.2", "sequelize-cli": "^5.5.1", "mysql": "2.17.1", "mysql2": "2.0.2", And after that, that step completed successfully, but I encountered another problem: Executing (default): SHOW INDEX FROM 'BankHolidays' FROM 'database_production' Executing (default): ALTER TABLE 'BankHolidays' ADD INDEX 'bank_holidays_company_id' ('companyId') Unhandled rejection SequelizeDatabaseError: Key column 'companyId' doesn't exist in table

And there is the query that is creating the table: BankHolidays:

Executing (default): CREATE TABLE IF NOT EXISTS 'BankHolidays' ('id' INTEGER NOT NULL auto_increment , 'name' VARCHAR(255) NOT NULL, 'date' DATETIME NOT NULL, 'createdAt' DATETIME NOT NULL, 'updatedAt' DATETIME NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;

And is clearly that in the query is not defined the field in the table.

Thank you, -Ionut

[ruben-farma]

Hello @idonca Can you share with me your config to run timeoff?

[Mr-Reca]

I'm having the same problem as @idonca

I'm trying to run the app in Kubernetes and I have the same error. I'm running using NODE_ENV=production env var and I hardcoded MySQL params in config/db.json (it should be nice -also safer- to introduce MySQL params through env vars).

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

Executing (default): CREATE TABLE IF NOT EXISTS `audit` (`id` INTEGER NOT NULL auto_increment , `entity_type` VARCHAR(255) NOT NULL COMMENT 'Type of the entity which change is tracked. E.g. USER, LEAVE etc', `entity_id` INTEGER NOT NULL COMMENT 'ID of the entity defined by entityType', `attribute` VARCHAR(255) NOT NULL COMMENT 'Attribute of the entity which chnage is to be recorded', `old_value` VARCHAR(255) COMMENT 'Old value converted to STRING', `new_value` VARCHAR(255) COMMENT 'New value converted to STRING', `at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `audit` FROM `timeoff`
Executing (default): CREATE TABLE IF NOT EXISTS `BankHolidays` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255) NOT NULL, `date` DATETIME NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `BankHolidays` FROM `timeoff`
Executing (default): ALTER TABLE `BankHolidays` ADD INDEX `bank_holidays_company_id` (`companyId`)
Unhandled rejection SequelizeDatabaseError: Key column 'companyId' doesn't exist in table
    at Query.formatError (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:244:16)
    at Query.handler [as onResult] (/app/timeoff-management/node_modules/sequelize/lib/dialects/mysql/query.js:51:23)
    at Query.execute (/app/timeoff-management/node_modules/mysql2/lib/commands/command.js:30:14)
    at Connection.handlePacket (/app/timeoff-management/node_modules/mysql2/lib/connection.js:412:32)
    at PacketParser.Connection.packetParser.p [as onPacket] (/app/timeoff-management/node_modules/mysql2/lib/connection.js:70:12)
    at PacketParser.executeStart (/app/timeoff-management/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.Connection.stream.on.data (/app/timeoff-management/node_modules/mysql2/lib/connection.js:77:25)
    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)

My Dockerfile is the same as the repo, but I added the following lines:

RUN cat config/db.json | jq '.production.password |=  "<my_passwd>"' | jq '.production.database |= "timeoff"' | jq '.production.host |= "<mysql_host>"' > config/db.json
RUN cat package.json | jq '.dependencies.sequelize |= "^5.21.2"' | jq '.dependencies["sequelize-cli"] |= "5.5.1"' > package.json
RUN npm install mysql mysql2

[Mr-Reca]

399

[elray]

I had the same problem here using NODE_ENV=production with MariaDb 5.5.65.

If I commented out the default value in lib/model/db/company.js ~ln 85

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

Everything works. It also clears up #399