Cidram: Tracking problem

[mikeruss1]

using PDO

IP Tracking page giving errors
Warning: count(): Parameter must be an array or an object that implements Countable in /var/sites/w/xxxxx/public_html/cidram/vault/frontend.php on line 3288

Warning: uasort() expects parameter 1 to be array, string given in /var/sites/w/xxxx/public_html/cidram/vault/frontend.php on line 3301

Warning: Invalid argument supplied for foreach() in /var/sites/w/xxx/public_html/cidram/vault/frontend.php on line 3304

cidram error page also throwing a group of errors since this morning, probably with each block(?).

[2020-01-04T10:35:46+00:00] Error at outgen.php:L229 (error code 8): "A non well formed numeric value encountered". Eep.. Something went wrong during "Tracking".
[2020-01-04T10:35:46+00:00] Error at outgen.php:L237 (error code 8): "A non well formed numeric value encountered". Eep.. Something went wrong during "Tracking".
[2020-01-04T10:35:46+00:00] Error at outgen.php:L237 (error code 2): "Illegal string offset '137.226.113.28'". Eep.. Something went wrong during "Tracking".
[2020-01-04T10:35:46+00:00] Error at outgen.php:L237 (error code 8): "Array to string conversion". Eep.. Something went wrong during "Tracking".
[2020-01-04T10:35:46+00:00] Error at outgen.php:L244 (error code 8): "A non well formed numeric value encountered". Eep.. Something went wrong during "Tracking".
[2020-01-04T10:35:46+00:00] Error at outgen.php:L244 (error code 2): "Illegal string offset '137.226.113.28'". Eep.. Something went wrong during "Tracking".
[2020-01-04T10:35:46+00:00] Error at outgen.php:L244 (error code 2): "Illegal string offset 'Count'". Eep.. Something went wrong during "Tracking".

I did a signature update this morning, there were 3 to do, one failed with a update sigatures, so repeated and seemed to be OK

[mikeruss1]

Tracking page produces
Currently tracking 1 IP.
and no results.
Stats says there are some 1400 blocks

[mikeruss1]

logs are working, the last error in errorlog coincides with the last block

[mikeruss1]

maybe the tracking file has a max size of 64K ?
this is the end of the tracking file
s:14:"137.226.113.28";a:2:{s:5:"Count";i:1;s:4:"Time";i:1578723162;}s:14:"35.178.168.230";a:2:{s:5:"Count";i:1;s:4:"Time";i:1578724339;}s:14:"34.253.184.145";a:2:{s:5:"Count";i:1;s:4:"Time";i:1578731153;}s:12:"78.46.42.235";a:2:{s:5:"Count";i:1;s:4:"Time";i:1578731935;}s:14:"159.138
Total Size: 63.99 KB

[mikeruss1]

checked the table, Key is Varchar which has a maximum size of 65000 odd bytes ?

[mikeruss1]

needs to be mediumtext at least ??

[Maikuolan]

Ah.. I think I see the problem now.

Not a bug specific to IP tracking or to PDO alone per se, but a bug that could occur when the two come together. The current data type used for the field in the cache table responsible for the cache entry's data is limited to 64K, and as IP tracking grows, eventually exceeding 64K.. the data is truncated, can't be unserialised correctly, resulting in the cache handler returning non-array data, thus causing those warnings to appear.

I hadn't considered the possibility of that particular problem when coding in the data types for the cache table's fields in the cache handler's routine for generating the cache table in the first place.

[mikeruss1]

delete tracking and change to mediumtext ?

[Maikuolan]

needs to be mediumtext at least ??

Yeah.. That would probably do the trick, I reckon. If the IP tracking data somehow ends up exceeding 16MB, it's probably about time to purge it and start afresh anyhow.

[mikeruss1]

umm .. sql error, cant have a blob with a KEY

[Maikuolan]

Before updating the cache handler itself, I'll need to re-read the documentation for the various database drivers supported by PDO (or rather, those supported by the cache handler, I suppose) to check whether any of them don't support mediumtext, or whether there are other, better options available in a general context for those various database drivers. But yeah; changing mediumtext should be fine (assuming whatever database system is used supports doing so).

[mikeruss1]

OK, will delete tracking for now, and await further instructions

[Maikuolan]

umm .. sql error, cant have a blob with a KEY

Interesting.. The particular field in question shouldn't have any key set. :-/

A quick copy/paste (for reference) of the table I drew up in the docs a while back (recently edited when I fixed that other PDO bug the other day):

╔══════════════════════════════════════════════╗
║ DATABASE "cidram"                            ║
║ │╔═══════════════════════════════════════════╩═════╗
║ └╫─TABLE "Cache" (UTF-8)                           ║
║  ╠═╪═FIELD══CHARSET═DATATYPE═════KEY══NULL═DEFAULT═╣
║  ║ ├─"Key"──UTF-8───VARCHAR(128)─PRI──×────×       ║
║  ║ ├─"Data"─UTF-8───TEXT─────────×────×────×       ║
╚══╣ └─"Time"─×───────INT(>=10)────×────×────×       ║
   ╚═════════════════════════════════════════════════╝

The Data field is the field you'll want to modify (because that's the field that'll be exceeding 64K for IP tracking at the moment). We won't need to touch Key or Time.

[mikeruss1]

OK, my confusion. Shall I leave it at text for now while you checkout if mediumtext will cause problems elsewhere ?

[DanielRuf]

Hm, this will not scale well. Can this not be aggregated from multiple rows? There not be so much data in one field. This makes it harder for the DB server to cache queries and results.

[DanielRuf]

It should not update fields and just write append rows / entries. Otherwise it would get slower over time (big bottleneck).

[mikeruss1]

thats about a week of traffic

[Maikuolan]

Hm, this will not scale well. Can this not be aggregated from multiple rows? There not be so much data in one field. This makes it harder for the DB server to cache queries and results.

That's very true, and good point. Might be more worthwhile taking that approach, rather than worrying about changing the field's data type at all. I mean.. even mediumtext, while sounding good right now, will only remain good until it.. doesn't. Changing needs in the future, future not yet implemented features, etc; might end up needing to change data types again in the future.

It should not update fields and just write append rows / entries. Otherwise it would get slower over time (big bottleneck).

Yeah.. I agree. TBH, if CIDRAM had've been designed with PDO in mind from the start, I most likely would've taken a different approach, and just appended rows/entries instead of updating fields. But, because CIDRAM started off with flatfiles in mind, originally being structured around that approach, with PDO as a later consideration, it ended up as it currently is.

Might be worth somewhat rethinking the design there.

[mikeruss1]

OK you ponder. Bear in mind it will break again in about 7 days

to confirm, Tracking and Stats deleted, Data field left as Text

[DanielRuf]

You can manually change the field type already but we should find a lomgterm solution which works with any amount of traffic.

[mikeruss1]

OK - Data changed to mediumtext - thanks

[mikeruss1]

re design, suggest a new Tracking table. One record per IP address, indexed by IP address.

saves messing around with serialized arrays, and likely faster.

[DanielRuf]

I would not create an index on the IP address, an auto_increment is sufficient.

Data has to be serialized anyway.

[mikeruss1]

not necessarily, my way to check if a user has been blocked you only need to read one record, with auto-increment you would need to read the whole file, then parse it.

certainly its a bigger change, but should be more efficient

[DanielRuf]

No, the id should be the log entry id, you can still have an extea ip field and check if count > 0.

[mikeruss1]

for info ..
the Cache DB field is running at about 225K bytes. The increase in size is now slow as it deletes a collection of IPs every day, and adds broadly the same number of new ones.

[Maikuolan]

There's an idea I've been toying with in my head a bit, but I haven't attempted to code anything for it, nor attempted any implementation of it anywhere. There are quite a few different "items" cached by CIDRAM currently, with IP tracking being the main "item", and some other examples being the DNS reverses, the DNS forwards, various lookups to various APIs (e.g., SFS, ABuseIPDB, etc). Many (though not all) of them contain numerous IP addresses as part of their values. The idea I'd been toying with, was whether we might perhaps be able to "invert" these "items", for the cases where those items would normally be storing IP addresses as their values, as such that, where some data needs to be cached pertaining to an IP address, that IP address is stored as its own item in the cache, with all relevant cached data for that IP address just stored as part of that one item.

The main benefit being, of course, that it should resolve the problem of running out of available bytes in the cache for IP tracking or for any other of the various things that CIDRAM caches. Though, some of the downsides include, that any existing cache data might become invalid, the moment that someone updates to the latest commit in a future scenario where something like this ends up being implemented (might raise a few questions from some users initially, but also not really a big deal, so whatever), that it might be slightly more complicated trying to clear IP tracking from the IP tracking page (seeing as it would would need to then iterate through all cache entries to look for tracking information to delete, rather than just deleting the single IP tracking cache entry as is the case currently.. possibly might be slightly slower too, but probably not by too much), and that the actual cache data page would probably look a lot more messy, seeing as it'd be showing a bunch of IP addresses immediately, rather than everything separated into different categories as is the case currently (probably the biggest downsides, and possibly might annoy a few users, if they start finding it difficult to find things in the list of IP addresses and so on).

[mikeruss1]

I think I agree with you ?

essentially have 1 record per IP address keyed on IP address and date ?

it might be worth confirming how many entries are likely - I have 3000 after 6 weeks, increasing very slowly now

MYSQL will eat that !

[Maikuolan]

essentially have 1 record per IP address keyed on IP address and date ?

Yep; that's the idea. :-)