sql: inserted data appears not being inserted after all

[knz]

So here's the scenario:

create table mono (val int, sts bigint, node int, tb int)
insert into mono (val, sts, node, tb) values (-1, 0, -1, -1)
-- do 10 times:
   begin
      v := select max(val) from mono
      select * from mono where val = v
      n := select now()
      insert into mono (val, sts, node, tb) values (1 + v, n, 0, 0)
   commit
-- then at the end:
begin
select * from mono
commit

the INSERTs appear to work properly because I see v increase in value during the loop.
However the final SELECT always returns an empty set. I also observe an empty table if I query it from another client while this test is running.

Full network trace attached: error.txt
Client and server running on same machine. You can see that all statements are first prepared and that insert uses RETURNING. Not sure that's relevant.

[andreimatei]

multi-node? Are you doing the finals SELECT on a different connection? Skewed clocks?

[knz]

Nope, all on the same server, only 1 node in "cluster"

[knz]

Clarification: if I run the same test using the CLI client with sql -e, ie only execute without prepare (and simpler syntax, no RETURNING), the test works as expected.

[knz]

How to reproduce:

1. check out github.com/knz/jepsen
2. cd jepsen/cockroachdb
3. edit src/jepsen/cockroach.clj and change jdbc-mode to :cdb-local. Configure your username and ensure Jepsen can do ssh localhost with key auth (run a ssh-agent as needed). This will not be used during the test, only once at the beginning during initialization to get some system stats.
4. Optionally set insecure to true if you don't have certs set up (doesn't change test result)
5. start a local server
6. run lein test :only jepsen.cockroach-test/monotonic-test

You can also compare against Postgres by setting jdbc-mode to :pg-local and configure the dbname, pg-user and pg-passwd accordingly.

[knz]

As suggested by @andreimatei I changed the test to:

create table mono (val int, sts bigint, node int, tb int)
insert into mono (val, sts, node, tb) values (-1, 0, -1, -1)
-- do 10 times:
   begin
      v := select max(val) from mono
      select * from mono where val = v
      n := select now()
      insert into mono (val, sts, node, tb) values (1 + v, n, 0, 0)
      select * from mono  --- THIS IS NEW
   commit
-- then at the end:
begin
      v := select max(val) from mono    -- NEW
      select * from mono where val = v      -- NEW
      n := select now()      -- NEW
      insert into mono (val, sts, node, tb) values (1 + v, n, 0, 0)       -- NEW
      select * from mono  -- as previously
commit

During the loop I see the table growing as a result of select *, as expected.
However the final transaction fails as the server crashes with the same panic trace as #4656 .

What is different between the last transactions and the 10 transactions before? There's a pause of 2 seconds between the end of the loop and the last transaction. Perhaps this pause is sufficient to trigger #4656?

[knz]

(all this while looking at #4036 )

[knz]

Cannot reproduce the error after upgrading to the latest master revision.