Page MenuHomePhabricator
Create Task
Maniphest T344939

Log when the lookup fails on attempting to INSERT to actor_data (because the proxy/behaviour doesn't exist)
Closed, DeclinedPublic
Actions

Description

Following T344499: Move behaviors/proxies/tunnels processing into an independent script, attempting to INSERT to actor_data can fail because the proxy/behaviour doesn't exist. (This should happen rarely.)

Log the actor data when this happens.

How to respond to these logs will be decided in T344941: Respond to data update and import errors.

QA Results - iPoid

ACStatusDetails
1❌https://phabricator.wikimedia.org/T344939 here

Related Objects
Search...

Event Timeline

Tchanders created this task.Aug 24 2023, 4:59 PM
Tchanders mentioned this in T344499: Move behaviors/proxies/tunnels processing into an independent script.
Tchanders updated the task description. (Show Details)Aug 24 2023, 5:12 PM
Tchanders moved this task from Ready 🎬 (ONLY IF YOU HAVE NO MORE CODE TO REVIEW) to QA/Testing 🐞 on the Anti-Harassment (AHaT Sprint 32 - Baseball Cap) board.Oct 18 2023, 5:42 PM

Should be solved by get-properties and import-properties - ready for QA.

GMikesell-WMF moved this task from QA/Testing 🐞 to Blocked/Stalled 🚧 on the Anti-Harassment (AHaT Sprint 32 - Baseball Cap) board.Oct 18 2023, 10:47 PM
GMikesell-WMF moved this task from Blocked/Stalled 🚧 to QA/Testing 🐞 on the Anti-Harassment (AHaT Sprint 32 - Baseball Cap) board.
Tchanders moved this task from Backlog to Deployment blockers on the iPoid-Service board.Oct 23 2023, 6:47 PM
GMikesell-WMF subscribed.Oct 24 2023, 8:56 PM

@Tchanders Dom and I were taking a look at this, and based on the details in the description, we weren't sure if we were doing this correctly or not. If this isn't correct, can you please add some steps to the description? Thanks!

Status: ❌FAIL
Environment: Ipoid: cf65a5b31f14f37e392cf1269bc1f717e4c7c9d0 Fri Oct 20 18:40:03 2023 +0000
OS: macOS Sonoma 14.0
Browser: Chrome 117- Visual Studio Code
Skins. n/a
Device: MBA M2
Emulated Device:: n/a
Test Links: n/a

statement.sql used

INSERT INTO actor_data (ip,org,client_count,types,conc_city,conc_state,conc_country,countries,location_country,risks) VALUES ('6bcaac10-88d8-4e2c-8e98-ae27d8840e26','Foobar',10,16,'Foobar','Foobar','NL',10,'AZ',2);@@@@@INSERT INTO actor_data_behaviors (actor_data_id, behavior_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM behaviors WHERE behavior = 'wizwit') );@@@@@INSERT INTO actor_data_proxies (actor_data_id, proxy_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM proxies WHERE proxy = 'wizwit2' ) );@@@@@INSERT INTO actor_data_tunnels (actor_data_id, tunnel_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM tunnels WHERE operator = 'TUNNELBEAR_VPN' ) );@@@@@

❌AC1: https://phabricator.wikimedia.org/T344939

Steps

  1. In VS, we cd ipoid than ran docker-compose exec web node init-db.js
  2. Listed the folders with docker-compose exec web ls /tmp
  3. Copied the statement.sql into my local tmp folders with docker-compose exec web cp /tmp/statements.sql tmp
  4. Changed the data for behavior and proxy to "wizwit" and "wizwit2" respectively, since they don't exist. The whole data used can be seen above.
  5. Copied it back into the docker tmp folder with docker-compose exec web cp tmp/statements.sql /tmp
  6. Import the file with docker-compose exec web ./import.sh
  7. Got the 'ER_BAD_NULL_ERROR' as seen below

Error

Importing properties generated by diff...
Resetting and generating substatement files...
Importing /tmp/sub/query_split_aa.sql...
{"name":"data-import","hostname":"8607fac2092c","pid":566,"level":50,"err":{"message":"(conn=37, no: 1048, SQLState: 23000) Column 'behavior_id' cannot be null\nsql: INSERT INTO actor_data_behaviors (actor_data_id, behavior_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM behaviors WHERE behavior = 'foobar') ); - parameters:[]","name":"SqlError","stack":"SqlError: (conn=37, no: 1048, SQLState: 23000) Column 'behavior_id' cannot be null\nsql: INSERT INTO actor_data_behaviors (actor_data_id, behavior_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM behaviors WHERE behavior = 'foobar') ); - 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:575: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:145:26)\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_BAD_NULL_ERROR"},"yesterdayFile":"","todayFile":"tmp/reprod1.json.gz","batchFile":"/tmp/sub/query_split_aa.sql","statement":"'INSERT INTO actor_data_behaviors (actor_data_id, behavior_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = \\'6bcaac10-88d8-4e2c-8e98-ae27d8840e26\\'), (SELECT pkid FROM behaviors WHERE behavior = \\'foobar\\') );'","msg":"(conn=37, no: 1048, SQLState: 23000) Column 'behavior_id' cannot be null\nsql: INSERT INTO actor_data_behaviors (actor_data_id, behavior_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM behaviors WHERE behavior = 'foobar') ); - parameters:[]","time":"2023-10-24T17:04:33.767Z","v":0}
{"name":"data-import","hostname":"8607fac2092c","pid":566,"level":50,"err":{"message":"(conn=37, no: 1048, SQLState: 23000) Column 'proxy_id' cannot be null\nsql: INSERT INTO actor_data_proxies (actor_data_id, proxy_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM proxies WHERE proxy = 'foobar2' ) ); - parameters:[]","name":"SqlError","stack":"SqlError: (conn=37, no: 1048, SQLState: 23000) Column 'proxy_id' cannot be null\nsql: INSERT INTO actor_data_proxies (actor_data_id, proxy_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM proxies WHERE proxy = 'foobar2' ) ); - 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:575: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:145:26)\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_BAD_NULL_ERROR"},"yesterdayFile":"","todayFile":"tmp/reprod1.json.gz","batchFile":"/tmp/sub/query_split_aa.sql","statement":"'INSERT INTO actor_data_proxies (actor_data_id, proxy_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = \\'6bcaac10-88d8-4e2c-8e98-ae27d8840e26\\'), (SELECT pkid FROM proxies WHERE proxy = \\'foobar2\\' ) );'","msg":"(conn=37, no: 1048, SQLState: 23000) Column 'proxy_id' cannot be null\nsql: INSERT INTO actor_data_proxies (actor_data_id, proxy_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM proxies WHERE proxy = 'foobar2' ) ); - parameters:[]","time":"2023-10-24T17:04:33.775Z","v":0}
{"name":"data-import","hostname":"8607fac2092c","pid":566,"level":50,"err":{"message":"(conn=37, no: 1048, SQLState: 23000) Column 'tunnel_id' cannot be null\nsql: INSERT INTO actor_data_tunnels (actor_data_id, tunnel_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM tunnels WHERE operator = 'TUNNELBEAR_VPN' ) ); - parameters:[]","name":"SqlError","stack":"SqlError: (conn=37, no: 1048, SQLState: 23000) Column 'tunnel_id' cannot be null\nsql: INSERT INTO actor_data_tunnels (actor_data_id, tunnel_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM tunnels WHERE operator = 'TUNNELBEAR_VPN' ) ); - 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:575: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:145:26)\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_BAD_NULL_ERROR"},"yesterdayFile":"","todayFile":"tmp/reprod1.json.gz","batchFile":"/tmp/sub/query_split_aa.sql","statement":"'INSERT INTO actor_data_tunnels (actor_data_id, tunnel_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = \\'6bcaac10-88d8-4e2c-8e98-ae27d8840e26\\'), (SELECT pkid FROM tunnels WHERE operator = \\'TUNNELBEAR_VPN\\' ) );'","msg":"(conn=37, no: 1048, SQLState: 23000) Column 'tunnel_id' cannot be null\nsql: INSERT INTO actor_data_tunnels (actor_data_id, tunnel_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM tunnels WHERE operator = 'TUNNELBEAR_VPN' ) ); - parameters:[]","time":"2023-10-24T17:04:33.777Z","v":0}
node:internal/process/promises:279
            triggerUncaughtException(err, true /* fromPromise */);
            ^

SqlError: (conn=37, no: 1048, SQLState: 23000) Column 'behavior_id' cannot be null
sql: INSERT INTO actor_data_behaviors (actor_data_id, behavior_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM behaviors WHERE behavior = 'foobar') ); - parameters:[]
    at Object.module.exports.createError (/srv/service/node_modules/mariadb/lib/misc/errors.js:64:10)
    at PacketNodeEncoded.readError (/srv/service/node_modules/mariadb/lib/io/packet.js:575:19)
    at Query.readResponsePacket (/srv/service/node_modules/mariadb/lib/cmd/parser.js:58:28)
    at PacketInputStream.receivePacketBasic (/srv/service/node_modules/mariadb/lib/io/packet-input-stream.js:85:9)
    at PacketInputStream.onData (/srv/service/node_modules/mariadb/lib/io/packet-input-stream.js:145:26)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  sqlMessage: "Column 'behavior_id' cannot be null",
  sql: "INSERT INTO actor_data_behaviors (actor_data_id, behavior_id) VALUES( (SELECT pkid FROM actor_data WHERE ip = '6bcaac10-88d8-4e2c-8e98-ae27d8840e26'), (SELECT pkid FROM behaviors WHERE behavior = 'foobar') ); - parameters:[]",
  fatal: false,
  errno: 1048,
  sqlState: '23000',
  code: 'ER_BAD_NULL_ERROR'
}
GMikesell-WMF updated the task description. (Show Details)Oct 24 2023, 8:57 PM
GMikesell-WMF moved this task from QA/Testing 🐞 to Blocked/Stalled 🚧 on the Anti-Harassment (AHaT Sprint 32 - Baseball Cap) board.
dom_walden moved this task from Deployment blockers to Blocked on the iPoid-Service board.Oct 26 2023, 12:08 PM
Tchanders added a project: Trust and Safety Product Sprint.Oct 27 2023, 10:43 AM
Tchanders edited projects, added Trust and Safety Product Sprint (Sprint Bodhrán); removed Trust and Safety Product Sprint.Oct 30 2023, 4:53 PM
Tchanders moved this task from Priority Backlog to Needs QA on the Trust and Safety Product Sprint (Sprint Bodhrán) board.
GMikesell-WMF added a comment.Nov 6 2023, 6:35 PM

@Tchanders as per our conversation, I ran the following steps below.

Steps

  1. To generate files, I ran docker-compose exec web npm run integration
  2. In VS, we cd ipoid then ran docker-compose exec web node init-db.js
  3. Listed the folders with docker-compose exec web ls ./tmp
  4. Import the file with docker-compose exec web ./import.sh
  5. After receiving the Error: Cannot find module './tmp/properties.json'

2023-11-06_10-30-32.png (476×982 px, 90 KB)

  1. I then created a properties.json file in the ./tmp folder with the data below :
{"ip":"185.186.83.70","organization":"TelKos L.L.C","as":{"number":206262,"Organization":"TelKos L.L.C"},"client":{"count":2,"types":["MOBILE"],"behaviors":["FILE_SHARING"],"proxies":["OXYLABS_PROXY"],"concentration":{"geohash":"srwcr5ugt","city":"Shtime","state":"Ferizaj","country":"XK","skew":182,"density":1},"countries":1},"tunnels":[{"type":"PROXY","anonymous":false}],"location":{"country":"AL"},"risks":["CALLBACK_PROXY","GEO_MISMATCH"]}
  1. After saving the file, I ran steps 3 to confirm and steps 4 to execute but got the following below, TypeError: Cannot read properties of undefined (reading 'length').

2023-11-06_10-21-19.png (1×2 px, 249 KB)

GMikesell-WMF updated the task description. (Show Details)Nov 6 2023, 6:36 PM
GMikesell-WMF moved this task from Needs QA to In Progress on the Trust and Safety Product Sprint (Sprint Bodhrán) board.
Tchanders edited projects, added Trust and Safety Product Sprint; removed Trust and Safety Product Sprint (Sprint Bodhrán), Anti-Harassment (AHaT Sprint 32 - Baseball Cap).Nov 7 2023, 3:58 PM
Tchanders moved this task from Blocked to Deployment blockers on the iPoid-Service board.

Thanks @GMikesell-WMF .

I was wrong that this specific bug had been fixed, but it's much less of a problem now, because the pipeline should always run get-properties.js before running import.sh. (Hence having to test it outside of the pipeline context.)

I'll move this out of the current sprint, but keep it open and queued.

STran moved this task from Deployment blockers to Backlog on the iPoid-Service board.Jan 24 2024, 8:59 AM
kostajh subscribed.Jan 29 2024, 1:40 PM

Shall we close this task, @STran @Tchanders ?

kostajh added a comment.Jan 29 2024, 1:45 PM

@Tchanders @STran shall we decline this task?

Tchanders closed this task as Declined.Feb 7 2024, 5:24 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL