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 BEGIN/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).

  • 2
    Will audit plugins satisfy the things you use log_table for?
    – danblack
    Commented Apr 30, 2019 at 4:15
  • You have presented a good description of the two methods I can think of. (Suggest you refactor this Question to just ask the question, then write your own answer with the discussion of MyISAM and 2 connections. If no one comes along with something better, "accept" your own answer.)
    – Rick James
    Commented May 2, 2019 at 3:09
  • @danblack Thanks for pointer - but looking at the audit plugin docs I don't think so - it looks to me it's just like general query log but which can be somewhat filtered and stored in InnoDB table instead of text file. Tring to parse, match and extract useful data from that would be a nightmare, storage requirements would be enormous (as I'd need to log all queries to get what I want), and I'd miss other context anyway (like content of other tables at that moment etc) Commented May 3, 2019 at 10:23


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.