null value in column "_id" violates not-null constraint

[mrgordon]

I synced over a Mongo collection without any issues and now have MoSQL tailing the oplog to keep the Postgres table up to date. I did not see any warnings or errors from MoSQL during import but now that we're tailing the oplog I see messages that say:

WARN MoSQL: Ignoring row (_id=): PG::Error: ERROR: null value in column "_id" violates not-null constraint

Is MoSQL trying to pull records from Mongo before the automatically generated _id field is populated? Any workaround?

[nelhage]

Interesting. Can you share your collections.yml? If you run the tailer with -vvv, it should print a "processing op: ..." line shortly before that WARN line -- can you send me that op? Feel free to redact any values, I am mostly interested in the structure of what the op is.

[mrgordon]

some_dbname:
  conversions:
    :columns:
    - _id: TEXT
    - accurate: BOOLEAN
    - adjusted_amount: DOUBLE PRECISION
    - amount: DOUBLE PRECISION
    - country: TEXT
    - currency: TEXT
    - external_id: TEXT
    - external_type: TEXT
    - finished_at: TIMESTAMP
    - job_id: INTEGER
    - judgments: INTEGER
    - response: TEXT
    - started_at: TIMESTAMP
    - tainted: BOOLEAN
    - uid: TEXT
    - worker_id: INTEGER
    - bonus_amount: DOUBLE PRECISION
    :meta:
      :table: conversions

DEBUG MoSQL: processing op: {"ts"=>seconds: 1360355811, increment: 76, "h"=>-5783231566697040009, "op"=>"u", "ns"=>"some_dbname.conversions", "o2"=>{"_id"=>BSON::ObjectId('51156129153d40539f0020ad')}, "o"=>{"job_id"=>166980, "judgments"=>8, "finished_at"=>2013-02-08 20:36:49 UTC, "currency"=>"cents", "adjusted_amount"=>1.23, "country"=>"USA", "worker_id"=>16055423, "amount"=>1.23, "uid"=>"ABC", "accurate"=>true, "external_id"=>"123", "external_type"=>"something", "tainted"=>true, "started_at"=>2013-02-08 20:33:46 UTC}}

DEBUG MoSQL: upsert some_dbname.conversions: _id=

DEBUG MoSQL: Transformed: [nil, true, 1.23, 1.23, "USA", "cents", "123", "something", 2013-02-08 20:36:49 UTC, 166980, 8, nil, 2013-02-08 20:33:46 UTC, true, "ABC", 16055423, nil]
E, [2013-02-08T20:36:51.449228 #18490] ERROR -- : PG::Error: ERROR: null value in column "_id" violates not-null constraint: INSERT INTO "conversions" ("_id", "accurate", "adjusted_amount", "amount", "country", "currency", "external_id", "external_type", "finished_at", "job_id", "judgments", "response", "started_at", "tainted", "uid", "worker_id", "bonus_amount") VALUES (NULL, true, 1.23, 1.23
002, 'USA', 'cents', '123', 'something', '2013-02-08 20:36:49.548000+0000', 166980, 8, NULL, '2013-02-08 20:33:46.000000+0000', true, 'ABC', 16055423, NULL) RETURNING "_id"

[nelhage]

Thanks, that was helpful. I've found the bug -- we're not correctly interpreting certain update operations, which our ODM never performs, so we didn't see the bug. I'll push a patch shortly.

[nelhage]

This should be fixed in git. Are you able to give that version a try?

[mrgordon]

Thanks for the quick assistance. I have switched to the github master and I don't see any warnings in a few minutes of testing so I expect that the issue is indeed fixed. I'll let you know if they return.

Sounds like it's time for a reimport.

[nelhage]

Yeah, I would reimport. Sorry for that hastle.

On Fri, Feb 8, 2013 at 2:32 PM, Matthew Gordon notifications@github.comwrote:

Thanks for the quick assistance. I have switched to the github master and
I don't see any warnings in a few minutes of testing so I expect that the
issue is indeed fixed. I'll let you know if they return.

Sounds like it's time for a reimport.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5#issuecomment-13316465..

[mrgordon]

You made it so easy it isn't much of a hassle ;)