We have a process where we need to test a SQL script before it runs against a production database. The approach is to execute the script within a transaction with a rollback statement at the end, capturing before/after logs illuminating the effects of the scripts.
select now();
start transaction;
select 'data before any changes', ...;
<insert / update / delete statements>;
select 'data after changes', ...;
rollback;
We've used this approach for many years with MSSQL, however we're having trouble implementing with mysql. If everything is working to-plan, things are great and it works exactly as expected. However, if we run into any errors (ex: typo in SQL or table constraint violation), the script aborts ... but COMMITS whatever ran before the error!!
How is this remotely considered an acceptable outcome? It drastically reduces the effectiveness of transactions if an error condition results in committing changes. I'm gobsmacked at this behavior--simply cannot understand the rationale.
I've been searching all over for a resolution to this and am coming up basically empty-handed. Options I've seen:
- Catch the error in client code and rollback. This isn't really an option, as our workflow process is just running the SQL script--and the "commit" needs to happen within the script rather than caller code (after testing the script, we run it without the transaction or rollback).
- Use
DECLARE ... HANDLER FOR ...;to rollback on error. This looked promising, but appears to be only supported within a stored procedure (or similar) and therefore not suitable for one-off scripts.
What am I missing? Thanks