I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table.
Now, I also want to use transactions for access to all the other tables. So there arises the problem - if I rollback the transaction (or transaction do it by itself due to deadlock or whatever), my
INSERT INTO log_table logs are also lost on rollback, which is a big problem.
I used to work around this by using
MyISAM table for
log_table which worked pretty good (as MyISAM doesn't support transactions, INSERTs into it were immediate, and were not undone on transaction rollback). However,
MyISAM tables are on the way out in new versions, and are not really working all that good in cluster/replication/hotbackup environment which I want to pursue, so I am looking for another solution.
Only one I've found is using two concurrent connections to the MySQL server (one for
INSERT INTO log_table with
autocommit=1, and another one for running all the other stuff in
COMMIT transactions). However that solution is problematic due to having a high number of connections (and per-connection resource usage) already.
Any other ways to preserve such log across rollbacks?
Note: I do not want to spool logs locally on the client running the application if at all possible (due to multitude of reasons; including clients low or nonexistant resources [thin client-alike], flaky connections [I want to know at which point big transactions die] and problematic low-tech users [who WILL try to kill clients mid-run if they can do it undetected]. While some of that issues can be worked around, that is a lot of extra work).