User sessions

[colinmollenhour]

Is it possible to somehow capture a user_id from one update and attach that to records that don't have a user_id column but that occurred from the same transaction or thread?

For example, if the client ran this:

UPDATE users SET last_activity = NOW() WHERE user_id = 5;
BEGIN TRANSACTION;
INSERT INTO events (`message`) VALUES ('asd');
COMMIT;

I'd like to know the user_id responsible for the insert event which is known because the same thread did an update on the PK of the users table with the user_id. This makes the assumption that the client is using one thread per user which I think is a safe assumption to make with a common PHP app, right?

I want to do this with dozens of tables and with updates so not just inserts, so I don't think simply adding a user_id column to every table just for this purpose is a good solution.

Is this already possible with Maxwell? Perhaps related to #2064 ?

[osheroff]

@colinmollenhour moved this to discussions.

[colinmollenhour]

Ahh sorry, I always forget about Discussions.. :)

[osheroff]

This is a pretty common use case, but there's nothing native to maxwell that can support this. Generally what folks end up doing is like:

inside the transaction:

INSERT into audit_log_table set user_id=XXX, ...

then in their stream processor they grab all the events inside a transaction and correlate the user back to it. You can do the same without doing it inside the transaction, but you'll have to keep some side-state in the stream processor denoting the last-setup-user per thread.

[colinmollenhour]

With MySQL's thread model do you know if threads get re-used between requests when using say Apache+mod_php? E.g. if another request was unauthenticated would their transaction get attributed to the user of the previous request or some other connection?

[osheroff]

depends on your setup, but yes, it's quite typical to have a connection pool inside your webserver that will keep a connection (and thus the "thread") alive between requests.

[osheroff]

If you're dead-set on doing it inside maxwell, I think there's maybe an interesting approach where like;

javascript filters get exposed a persistent (database backed) KV store -- the most recent release gave them persistent in-memory state which got me thinking this way.

then they can decorate rows as needed based on this store.

IDK though it feels like downstream in the stream processor is still more the right place.

[colinmollenhour]

Yeah, it seemed that the state could be used that way. I'm still trying to figure out if I can skip having an intermediate stream processor and use a directly supported consumer. For example, ClickHouse can read straight from Kafka so it would be great to go: MySQL -> Maxwell -> Kafka -> ClickHouse without having to introduce anything between Kafka and ClickHouse.

[osheroff]

well? hm. Maybe try to prototype it; the latest release of maxwell will let you at least see if the solution might work -- ie you can stash the user_id in the new javascript-filter-global-state-object and then add it to subsequent stream objects. If that works we could talk about how to maybe make the global state persistent.

Oftentimes you'll need at least a little help from the application to pull this stuff off.

[colinmollenhour]

Thanks for the discussion, Ben. I'm very early in design still so I have some other things to confirm first, but I'll let you know how it goes if I try this route. Thanks for maintaining Maxwell as well!