Sequelize trying to insert row with all NULLs during bulkCreate

[TylerBrock]

What you are doing?

Inserting a large number of rows into postgres from another db through sequelize's bulkCreate() feature. Every object in the other db has the primary key for the schema (the only one required to be not null). I suspected the driver for the the other database but as you can see from my code I check to see if it returns objects that are simply null.

Here is the code to write a batch:

  async writeBatch() {
    await this.model.bulkCreate(this.batch);
    this.batch = [];
  }

Here is the code to clone a collection:

  async cloneOne(cursor) {
    while(await cursor.hasNext()) {
      const doc = await cursor.next();

      // check to see if cursor is producing objects that are just null for some reason
      if (doc && typeof doc === 'object' && Object.keys(doc).length) {
        this.batch.push(doc);
        if (this.batch.length === 1000) {
          await this.writeBatch();
        }
      } else {
        console.error('Bad doc', JSON.stringify(doc, null, 4));
      }
    }
    if (this.batch.length) {
      await this.writeBatch();
    }
  }

What do you expect to happen?

I want them to all deterministically be inserted without error

What is actually happening?

Eventually in cloning one of the tables I get the following error but it is not limited to this table or reproducible. The error is always the same. Sequelize is trying to insert a row with all nulls for some reason and postgres is saying it cannot accept the row:

 value in column "objectId" violates not-null constraint
    at Query.formatError (/home/ec2-user/mondo/node_modules/sequelize/lib/dialects/postgres/query.js:357:14)
    at Query.<anonymous> (/home/ec2-user/mondo/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/ec2-user/mondo/node_modules/pg/lib/query.js:133:8)
    at Connection.<anonymous> (/home/ec2-user/mondo/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/ec2-user/mondo/node_modules/pg/lib/connection.js:121: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: 'null value in column "objectId" violates not-null constraint',
  parent: 
   { error: null value in column "objectId" violates not-null constraint
       at Connection.parseE (/home/ec2-user/mondo/node_modules/pg/lib/connection.js:554:11)
       at Connection.parseMessage (/home/ec2-user/mondo/node_modules/pg/lib/connection.js:381:17)
       at Socket.<anonymous> (/home/ec2-user/mondo/node_modules/pg/lib/connection.js:117:22)
       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: 'error',
     length: 237,
     severity: 'ERROR',
     code: '23502',
     detail: 'Failing row contains (null, null, null, null, null, null, null, null, null, null).',
     hint: undefined,
     position: undefined,
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: 'public',
     table: 'Assignment',
     column: 'objectId',
     dataType: undefined,
     constraint: undefined,
     file: 'execMain.c',
     line: '1732',
     routine: 'ExecConstraints',
     sql: 'INSERT INTO "Assignment" ("objectId","createdAt","updatedAt","agent","group","isUnread","thread","lead","leadFacingNumber") VALUES ... // lots of rows
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) // WTF?
...

I've tried looking to see if the adjacent rows have any problems that would cause an issue but it doesn't look like they have any. My question to you all is: "Have I confirmed that the cursor is producing acceptable documents for insertion and that something is going wrong inside of sequelize?"

Dialect: mysql / postgres / sqlite / mssql / any
Database version: 9.6.1
Sequelize version: latest

[janmeier]

I'm pretty sure this is (at least partly) an issue I'm your code. Perhaps timing related.

It's almost impossible for us to debug if you can't provide a self-contained test case

[janmeier]

Your check can still add rows that contain all nulls, since you are only checking if the object contains any keys, not whether those keys are null

[TylerBrock]

I'm going to change the constraint to if (doc && typeof doc === 'object' && doc._id !== null) _id is mapped to objectId (our primary key in this case)

[TylerBrock]

Not trying to be difficult but how would you write the code to avoid a race here? I'm not sure I can see where it could be happening. It seems fairly clear that the following should be true:

• Document checked to not be null (and have the non-null primary key field)
• Document inserted into batch
• The batch is written
• Opon returning from writing the batch, clear batch, start filling batch

However, you are correct, there could be bugs in my code, bugs in babel, bugs in Node, bugs in sequelize or bugs in the other database driver. Given the above though, I thought it would be good to check with you all to see what you thought.

[felixfbecker]

@TylerBrock please try to create an SSCCE. You can find a template here

[TylerBrock]

Really hard to make an SSCCE for this since it happens 40 million rows into 50 million worth of copying. I was using NodeJS 7.2.0, as soon as I changed to 6.9.2 the problem went away. I think there is some jankyness in 7 right now. Going to close this for the time being.

[Tevinrobbery530]

@TylerBrock https://re-enactment.github.io/xqhqKR6nMsU9W387j8ggp9THfGFYLbCz4Nau98C3bkOUgPcxyPzBejG/