error about null value in jobs table during SHOW JOBS

[thoszhang]

On a roachprod cluster, I imported a tpcc fixture using fixtures import. One of the table imports appeared to be hanging. When I ran SHOW JOBS, I got this error:

root@localhost:26257/defaultdb> show jobs;
pq: crdb-internal-jobs-table: internal error: Non-nullable column "jobs:status" with no value! Index scanned was "primary" with the index key columns (id) and the values (443313296369975297)
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/row/fetcher.go:1253: in finalizeRow()
github.com/cockroachdb/cockroach/pkg/sql/row/fetcher.go:995: in NextRow()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/tablereader.go:160: in Next()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/tablereader.go:277: in Next()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/base.go:174: in Run()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/processors.go:801: in Run()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/flow.go:566: in func1()

HINT: You have encountered an unexpected error inside CockroachDB.
[...]

I can read the row from the jobs table, though, and the values are not null:

root@localhost:26257/defaultdb> select * from system.jobs where id=443313296369975297;
          id         |  status   |             created              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     payload                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |                                                                   progress
+--------------------+-----------+----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
  443313296369975297 | succeeded | 2019-04-15 20:08:01.558233+00:00 | \012\341\010IMPORT TABLE tpcc.public.customer (c_id INT8 NOT NULL, c_d_id INT8 NOT NULL, c_w_id INT8 NOT NULL, c_first VARCHAR(16), c_middle CHAR(2), c_last VARCHAR(16), c_street_1 VARCHAR(20), c_street_2 VARCHAR(20), c_city VARCHAR(20), c_state CHAR(2), c_zip CHAR(9), c_phone CHAR(16), c_since TIMESTAMP, c_credit CHAR(2), c_credit_lim DECIMAL(12,2), c_discount DECIMAL(4,4), c_balance DECIMAL(12,2), c_ytd_payment DECIMAL(12,2), c_payment_cnt INT8, c_delivery_cnt INT8, c_data VARCHAR(500), PRIMARY KEY (c_w_id, c_d_id, c_id), INDEX customer_idx (c_w_id, c_d_id, c_last, c_first)) CSV DATA ('experimental-workload:///csv/tpcc/customer?interleaved=false&row-end=75000&row-start=0&seed=1&version=2.0.1&warehouses=10', 'experimental-workload:///csv/tpcc/customer?interleaved=false&row-end=150000&row-start=75000&seed=1&version=2.0.1&warehouses=10', 'experimental-workload:///csv/tpcc/customer?interleaved=false&row-end=225000&row-start=150000&seed=1&version=2.0.1&warehouses=10', 'experimental-workload:///csv/tpcc/customer?interleaved=false&row-end=300000&row-start=225000&seed=1&version=2.0.1&warehouses=10') WITH "nullif" = 'NULL'\022\004root\030\327\217\212\255\363\322\341\002 \371\214\274\231\366\322\341\002J\004\010\001\020\001j\247\015\012\355\010\012\352\010\012\010customer\030D >(\001:\012\010\313\210\243\205\230\325\357\312\025B\026\012\004c_id\020\001\032\010\010\001\020@\030\0000\003 \0000\000B\030\012\006c_d_id\020\002\032\010\010\001\020@\030\0000\003 \0000\000B\030\012\006c_w_id\020\003\032\010\010\001\020@\030\0000\003 \0000\000B\031\012\007c_first\020\004\032\010\010\007\020\020\030\0000\007 \0010\000B\032\012\010c_middle\020\005\032\010\010\007\020\002\030\0000\010 \0010\000B\030\012\006c_last\020\006\032\010\010\007\020\020\030\0000\007 \0010\000B\034\012\012c_street_1\020\007\032\010\010\007\020\024\030\0000\007 \0010\000B\034\012\012c_street_2\020\010\032\010\010\007\020\024\030\0000\007 \0010\000B\030\012\006c_city\020\011\032\010\010\007\020\024\030\0000\007 \0010\000B\031\012\007c_state\020\012\032\010\010\007\020\002\030\0000\010 \0010\000B\027\012\005c_zip\020\013\032\010\010\007\020\011\030\0000\010 \0010\000B\031\012\007c_phone\020\014\032\010\010\007\020\020\030\0000\010 \0010\000B\031\012\007c_since\020\015\032\010\010\005\020\000\030\0000\000 \0010\000B\032\012\010c_credit\020\016\032\010\010\007\020\002\030\0000\010 \0010\000B\036\012\014c_credit_lim\020\017\032\010\010\003\020\002\030\0140\000 \0010\000B\034\012\012c_discount\020\020\032\010\010\003\020\004\030\0040\000 \0010\000B\033\012\011c_balance\020\021\032\010\010\003\020\002\030\0140\000 \0010\000B\037\012\015c_ytd_payment\020\022\032\010\010\003\020\002\030\0140\000 \0010\000B\037\012\015c_payment_cnt\020\023\032\010\010\001\020@\030\0000\003 \0010\000B \012\016c_delivery_cnt\020\024\032\010\010\001\020@\030\0000\003 \0010\000B\031\012\006c_data\020\025\032\011\010\007\020\364\003\030\0000\007 \0010\000H\026RJ\012\007primary\020\001\030\001"\006c_w_id"\006c_d_id"\004c_id0\0030\0020\001@\000@\000@\000J\020\010\000\020\000\032\000 \000(\0000\0008\000@\000Z\000z\002\010\000\200\001\000Z`\012\014customer_idx\020\002\030\000"\006c_w_id"\006c_d_id"\006c_last"\007c_first0\0030\0020\0060\0048\001@\000@\000@\000@\000J\020\010\000\020\000\032\000 \000(\0000\0008\000@\000Z\000z\002\010\000\200\001\000`\003j\025\012\011\012\005admin\020\002\012\010\012\004root\020\002\200\001\001\210\001\003\230\001\000\262\001\217\002\012\007primary\020\000\032\004c_id\032\006c_d_id\032\006c_w_id\032\007c_first\032\010c_middle\032\006c_last\032\012c_street_1\032\012c_street_2\032\006c_city\032\007c_state\032\005c_zip\032\007c_phone\032\007c_since\032\010c_credit\032\014c_credit_lim\032\012c_discount\032\011c_balance\032\015c_ytd_payment\032\015c_payment_cnt\032\016c_delivery_cnt\032\006c_data \001 \002 \003 \004 \005 \006 \007 \010 \011 \012 \013 \014 \015 \016 \017 \020 \021 \022 \023 \024 \025(\000\270\001\001\302\001\000\350\001\000\362\001\004\010\000\022\000\370\001\000\200\002\000\022yexperimental-workload:///csv/tpcc/customer?interleaved=false&row-end=75000&row-start=0&seed=1&version=2.0.1&warehouses=10\022~experimental-workload:///csv/tpcc/customer?interleaved=false&row-end=150000&row-start=75000&seed=1&version=2.0.1&warehouses=10\022\177experimental-workload:///csv/tpcc/customer?interleaved=false&row-end=225000&row-start=150000&seed=1&version=2.0.1&warehouses=10\022\177experimental-workload:///csv/tpcc/customer?interleaved=false&row-end=300000&row-start=225000&seed=1&version=2.0.1&warehouses=10\032,\010\001\022\014\010\000\020\000\032\004NULL \000\032\014\010\000\020\000\030\000 \000(\0000\000"\006\010\000\022\000\030\000(\0002\002\010\000(\313\210\243\205\230\325\357\312\0250> | \015\000\000\200?\020\372\214\274\231\366\322\341\002j\035\022\020\015\365n>\015\365n>\015\365n>\015\365n>\032\004\000\000\200?"\003"\001\315
(1 row)

Time: 5.398913ms

The job seems to have completed, and the imported table shows up in the output of SHOW TABLES.

[thoszhang]

I hit the same problem today (also seen in the admin UI):

root@localhost:26257/defaultdb> show jobs;
pq: crdb-internal-jobs-table: internal error: Non-nullable column "jobs:status" with no value! Index scanned was "primary" with the index key columns (id) and the values (443863418153828353)
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/row/fetcher.go:1253: in finalizeRow()
github.com/cockroachdb/cockroach/pkg/sql/row/fetcher.go:995: in NextRow()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/tablereader.go:160: in Next()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/tablereader.go:277: in Next()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/base.go:174: in Run()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/processors.go:801: in Run()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/flow.go:566: in func1()

HINT: You have encountered an unexpected error inside CockroachDB.

Please check https://github.com/cockroachdb/cockroach/issues to check
whether this problem is already tracked. If you cannot find it there,
please report the error with details at:

    https://github.com/cockroachdb/cockroach/issues/new/choose

If you would rather not post publicly, please contact us directly at:

    [email protected]

The Cockroach Labs team appreciates your feedback.

root@localhost:26257/defaultdb> select * from system.jobs where id = 443863418153828353;
          id         |  status   |             created              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        payload                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |                                                          progress
+--------------------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
  443863418153828353 | succeeded | 2019-04-17 18:46:05.404916+00:00 | \012\254\005IMPORT TABLE tpcc.public.item (i_id INT8 NOT NULL, i_im_id INT8, i_name VARCHAR(24), i_price DECIMAL(5,2), i_data VARCHAR(50), PRIMARY KEY (i_id)) CSV DATA ('experimental-workload:///csv/tpcc/item?interleaved=false&row-end=33333&row-start=0&seed=1&version=2.0.1&warehouses=2100', 'experimental-workload:///csv/tpcc/item?interleaved=false&row-end=66666&row-start=33333&seed=1&version=2.0.1&warehouses=2100', 'experimental-workload:///csv/tpcc/item?interleaved=false&row-end=99999&row-start=66666&seed=1&version=2.0.1&warehouses=2100', 'experimental-workload:///csv/tpcc/item?interleaved=false&row-end=100000&row-start=99999&seed=1&version=2.0.1&warehouses=2100') WITH "nullif" = 'NULL'\022\004root\030\254\252\252\342\344\327\341\002 \252\301\223\344\344\327\341\002J\004\010\001\020\001j\373\006\012\315\002\012\312\002\012\004item\0309 4(\001:\012\010\223\230\205\217\240\353\225\313\025B\026\012\004i_id\020\001\032\010\010\001\020@\030\0000\003 \0000\000B\031\012\007i_im_id\020\002\032\010\010\001\020@\030\0000\003 \0010\000B\030\012\006i_name\020\003\032\010\010\007\020\030\030\0000\007 \0010\000B\031\012\007i_price\020\004\032\010\010\003\020\002\030\0050\000 \0010\000B\030\012\006i_data\020\005\032\010\010\007\0202\030\0000\007 \0010\000H\006R2\012\007primary\020\001\030\001"\004i_id0\001@\000J\020\010\000\020\000\032\000 \000(\0000\0008\000@\000Z\000z\002\010\000\200\001\000`\002j\025\012\011\012\005admin\020\002\012\010\012\004root\020\002\200\001\001\210\001\003\230\001\000\262\001?\012\007primary\020\000\032\004i_id\032\007i_im_id\032\006i_name\032\007i_price\032\006i_data \001 \002 \003 \004 \005(\000\270\001\001\302\001\000\350\001\000\362\001\004\010\000\022\000\370\001\000\200\002\000\022wexperimental-workload:///csv/tpcc/item?interleaved=false&row-end=33333&row-start=0&seed=1&version=2.0.1&warehouses=2100\022{experimental-workload:///csv/tpcc/item?interleaved=false&row-end=66666&row-start=33333&seed=1&version=2.0.1&warehouses=2100\022{experimental-workload:///csv/tpcc/item?interleaved=false&row-end=99999&row-start=66666&seed=1&version=2.0.1&warehouses=2100\022|experimental-workload:///csv/tpcc/item?interleaved=false&row-end=100000&row-start=99999&seed=1&version=2.0.1&warehouses=2100\032,\010\001\022\014\010\000\020\000\032\004NULL \000\032\014\010\000\020\000\030\000 \000(\0000\000"\006\010\000\022\000\030\000(\0002\002\010\000(\223\230\205\217\240\353\225\313\02504 | \015\000\000\200?\020\253\301\223\344\344\327\341\002j\031\022\014\343lf>\343lf>\343lf>\032\004\000\000\200?"\003"\001\302
(1 row)

Time: 14.998326ms

The problem fixed itself within a few minutes.

[irfansharif]

Saw this out in the wild for from a user running v19.1.5. We previously saw this in #34878, and assumed it to be fixed in #35371, but this is not the case as v19.1.5 contains #35371.

[irfansharif]

It's interesting to note that from the debug zip retrieved from above, the faulty Job IDs straddle range boundaries. For e.g. we have the following:

  "span": {
    "start_key": "/Table/15/1/544682127234662401",
    "end_key": "/Table/15/1/544834436854808577/1/1"
  },

  "span": {
    "start_key": "/Table/15/1/544834436854808577/1/1",
    "end_key": "/Table/15/1/544971757032931329/0"
  },

Where 544971757032931329 was the Job ID that errored out.

[irfansharif]

Ok, I'm fairly certain I understand what's happening here. The fact that the buggy Job IDs straddled range boundaries was a good (read: lucky) thread to pull on. Seeing as how we most recently observed this panic in v19.1.5, I took a look through our release notes since then to land on https://www.cockroachlabs.com/docs/releases/v19.2.2.html. There I found the following:

CockroachDB now prevents a number of panics from the SQL layer caused by an invalid range split. These would usually manifest with messages containing encoding errors like "found null on not null column". #42859

Looking through the original PR that was backported to 19.2 (#42833), and the discussions present in #42056, the issue stems from the fact we seemed to have split between column families of the same row, which is evidently illegal. Note the fetcher behavior described in #42056 (comment), specifically:

the fetchers assume that when a range boundary is hit, all the kv's for a row have been retrieved already.

What I suspect happened here was more or less the same. When attempting to fetch the specific row, we hit the range boundary, and assumed we'd fetched all the KV's for the row. In this case the "status" KV was stored in the next row, and we failed to fetch it. What the fetcher then observed was a NULL column value for a non-NULLable column, thus erroring out as observed above.

[irfansharif]

I'm going to go ahead and (optimistically) close this issue as it's already been fixed.