CODEWITHSUNDEEP
mysqldebuggingloggingstored-procedures
Johannes
Johannes

Reputation: 6419

Is it possible to exclude certain queries/procedures from transaction rollbacks in MySQL?

The Setup

While working on some rather complex procedures I've started logging debug information into a _debug table, via a stored logging procedure: P_Log('message'), which just calls a simple INSERT query into the _debug table.

The complex procedures contain transactions, which are rolled back if an error is encountered. The problem is that any debug information that was logged during the course of the transaction is also rolled back. This is of course a little counter productive, since you want to be able to see the debug logs precisely when the procedure -does- fail.

The Question

Is there any way I can insert into _debug without having the inserts rolled back? The log is really only to be used in development, and I would only ever write to it, so I don't care if it would violate how transactions are intended to be used.

And just out of curiosity, how is this normally handled? it seems like being able to write arbitrary log information from inside transactions, to check states of variables, etc, regardless of said transactions being rolled back, would be absolutely crucial for debugging errors. What's the best practice here?

Possible alternatives

  1. storing logs in variables and only writing them at the end of the procedure.
    • the problem with this is that I want to be able to insert an arbitrary number of debug entries. creating a text variable and parcing that later would work, but seems very hacky.
  2. Using some built-in log in mysql
    • I'd actually fine with this, if it means I can write arbitrary text to it at will, but I haven't been able to find something like this so far.

Upvotes: 1

Views: 1431

Answers (1)

Vatev
Vatev

Reputation: 7590

The simplest way would be to change your logs table to MyISAM.

It does not support transactions and will completely ignore them. Also MyISAM is a bit faster when you only insert and select from it.

The only other solution that I know of is to create a separate connection for the logs.

Upvotes: 3

Related Questions