Sometimes tables seem empty! #17825
Description
Sometimes the following query returns zero rows despite there being 100's of thousands of rows in the table:
SELECT * FROM bank.accounts ORDER BY id DESC LIMIT 1;
This issue is under discussion on the forum here:
https://forum.cockroachlabs.com/t/sometimes-my-700-000-row-table-seems-empty/900
I will add details to reproduce here in a moment....
Here we go:
Machine: amd64, 2GByte RAM, 128GB SSD.
OS: Debian GNU/Linux 9
Kernel: Linux jhctech2 4.9.0-3-amd64 #1 SMP Debian 4.9.30-2+deb9u3 (2017-08-06) x86_64 GNU/Linux
cockroachdb:
$ cockroach version
Build Tag: v1.0.4
Build Time: 2017/07/27 17:54:36
Distribution: CCL
Platform: linux amd64
Go Version: go1.8.3
C Compiler: gcc 6.3.0
Build SHA-1: 5b75726
Build Type: release
Node.js: v8.2.1
I start three instances of cockroach on this machine, as per the getting started instructions on the web site:
$ nohup cockroach start --certs-dir=certs --store=node1 --host=localhost --port=26257 --http-port=8080 --http-host=localhost &
$ nohup cockroach start --certs-dir=certs --store=node2 --host=localhost --port=26258 --http-port=8081 --http-host=localhost --join=localhost:26257 &
$ nohup cockroach start --certs-dir=certs --store=node3 --host=localhost --port=26259 --http-port=8082 --http-host=localhost --join=localhost:26257 &
I start filling up the example bank account table, 100 inserts persecond, from node.js using pg and this code:
const fs = require('fs')
const config = {
host: 'localhost',
port: 26257,
user: 'root',
database: 'bank',
// this object will be passed to the TLSSocket constructor
ssl: {
rejectUnauthorized: false,
ca: fs.readFileSync('/home/rsm/certs/ca.crt').toString(),
key: fs.readFileSync('/home/rsm/certs/client.root.key').toString(),
cert: fs.readFileSync('/home/rsm/certs/client.root.crt').toString()
}
}
const { Pool } = require('pg')
const pool = new Pool(config)
const query = {
text: 'INSERT INTO accounts(id, balance) VALUES($1, $2)',
values: [24, 666]
}
setInterval(function () {
query.values[0]++
pool.query(query, (err, res) => {
if (err) {
console.log(err.stack)
} else {
console.log(res.rows)
}
})
}, 10)
pool.query('SELECT * FROM accounts', (err, res) => {
if (err) {
throw err
}
console.log('accounts:', res.rows)
})
Then start querying the table for the last record using the sql shell:
root@:26257/> SELECT * FROM bank.accounts ORDER BY id DESC LIMIT 1;
+--------+---------+
| id | balance |
+--------+---------+
| 788130 | 666 |
+--------+---------+
(1 row)
root@:26257/> SELECT * FROM bank.accounts ORDER BY id DESC LIMIT 1;
+----+---------+
| id | balance |
+----+---------+
+----+---------+
(0 rows)
root@:26257/> SELECT * FROM bank.accounts ORDER BY id DESC LIMIT 1;
+--------+---------+
| id | balance |
+--------+---------+
| 875072 | 666 |
+--------+---------+
(1 row)
NOTE: The EMPTY row returned. This happens one in 20 or thrirty queries.
The following code that makes the same query produces the same error from pg.
const fs = require('fs')
const config = {
host: 'localhost',
port: 26257,
user: 'root',
database: 'bank',
ssl: {
rejectUnauthorized: false,
ca: fs.readFileSync('/home/rsm/certs/ca.crt').toString(),
key: fs.readFileSync('/home/rsm/certs/client.root.key').toString(),
cert: fs.readFileSync('/home/rsm/certs/client.root.crt').toString()
}
}
const { Pool } = require('pg')
const pool = new Pool(config)
let previousId = 0;
setInterval(function () {
pool.query('SELECT * FROM bank.accounts ORDER BY id DESC LIMIT 1;', (err, res) => {
let id = 0;
if (err) {
throw err
}
if (res.rows.length < 1) {
console.log('Error: Empty result');
} else {
id = res.rows[0].id
if (id < previousId) {
console.log('Error: Low result')
} else {
previousId = id
}
console.log(res.rows[0].id)
}
})
}, 100)