During an update, we encountered a 'Duplicate entry' error while trying to insert an IP.
The previous import was done on 10 Nov. The update attempt was 16 Nov. No other imports had been done in between:
SELECT feed_file_yesterday, feed_file_today, COUNT(*) FROM import_status GROUP BY feed_file_today; +-----------------------+-----------------------+----------+ | feed_file_yesterday | feed_file_today | COUNT(*) | +-----------------------+-----------------------+----------+ | | /tmp/20231110.json.gz | 3286 | | /tmp/20231110.json.gz | /tmp/20231116.json.gz | 2003 | +-----------------------+-----------------------+----------+ 2 rows in set (0.041 sec)
This leaves the database in a corrupted state, where it contains data from two different days. This is a problem because the next update will assume that the database contents are the same as the old data file, for diffing purposes. T344941 is trying to address this.
Attempting to reproduce this locally: the IP in the backtrace doesn't appear in the data file for November 10, but it does in November 16. So somehow we have done duplicate inserts based on November 16 data alone.
{"name":"data-import","hostname":"ipoid-production-daily-updates-28335548-g7dck","pid":28159,"level":50,"err":{"message":"(conn=60822097, no: 1062, SQLState: 23000) Duplicate entry 'REDACTED' for key 'ip'\nsql: INSERT INTO actor_data (ip,org,client_count,types,conc_city,conc_state,conc_country,countries,location_country,risks) VALUES ('REDACTED,'REDACTED',2,1,'','','',0,'TH',2); - parameters:[]","name":"SqlError","stack":"SqlError: (conn=60822097, no: 1062, SQLState: 23000) Duplicate entry 'REDACTED' for key 'ip'\nsql: INSERT INTO actor_data (ip,org,client_count,types,conc_city,conc_state,conc_country,countries,location_country,risks) VALUES ('REDACTED','REDACTED',2,1,'','','',0,'TH',2); - parameters:[]\n at Object.module.exports.createError (/srv/service/node_modules/mariadb/lib/misc/errors.js:64:10)\n at PacketNodeEncoded.readError (/srv/service/node_modules/mariadb/lib/io/packet.js:582:19)\n at Query.readResponsePacket (/srv/service/node_modules/mariadb/lib/cmd/parser.js:58:28)\n at PacketInputStream.receivePacketBasic (/srv/service/node_modules/mariadb/lib/io/packet-input-stream.js:85:9)\n at PacketInputStream.onData (/srv/service/node_modules/mariadb/lib/io/packet-input-stream.js:135:20)\n at Socket.emit (node:events:513:28)\n at addChunk (node:internal/streams/readable:315:12)\n at readableAddChunk (node:internal/streams/readable:289:9)\n at Socket.Readable.push (node:internal/streams/readable:228:10)\n at TCP.onStreamRead (node:internal/stream_base_commons:190:23)","code":"ER_DUP_ENTRY"},"yesterdayFile":"/tmp/20231110.json.gz","todayFile":"/tmp/20231116.json.gz","batchFile":"/tmp/sub/query_split_aacza.sql","statement":"'INSERT INTO actor_data (ip,org,client_count,types,conc_city,conc_state,conc_country,countries,location_country,risks) VALUES (\\'REDACTED\\',\\'REDACTED\\',2,1,\\'\\',\\'\\',\\'\\',0,\\'TH\\',2);'","msg":"(conn=60822097, no: 1062, SQLState: 23000) Duplicate entry 'REDACTED' for key 'ip'\nsql: INSERT INTO actor_data (ip,org,client_count,types,conc_city,conc_state,conc_country,countries,location_country,risks) VALUES ('REDACTED','REDACTED',2,1,'','','',0,'TH',2); - parameters:[]","time":"2023-11-16T14:39:36.057Z","v":0}
node:internal/process/promises:279
triggerUncaughtException(err, true /* fromPromise */);