Transactions

[Anon]

I've just read this in the MySQL-Doc:
To be safe with MySQL, you only need to have backups and have the update logging turned on. With this you can recover from any situation that you could with any transactional database. It is, of course, always good to have backups, independent of which database you use.

Can anyone explain me, how to setup some form of undo-last-query-whatever-it-was with help of the update log only? Say, I change a couple of values in a table, how could I recover them afterwards? Afaik, the update log doesn't keep some kind of backup-data, which should be necessary for these operations.

Am I missing sth. real obvious??

Thanks for help boys and girls

[[deleted]]

Transactions let you do a query on the condition that you can decide to make the changes active (commit) or forget the changes (rollback).

Once a transaction has been committed, there's no turning back, it's final.
So you can't go back and undo a query you did last week for example. You have to decide "commit/rollback" after any number of queries, but at least once before you disconnect from the database (or hit the end of script) otherwise you'll get either a forced commit or forced rollback.

The transaction log (or query-log as MySQL calls it) contains all the querys that were executed on the database.
So, in the event of a crash, you can restore the database from your last backup, and then proceed to execute all the queries in the transaction logs up to the point of the crash.

So transactions or logs wil not let you undo any random query, only the last query or set of queries since the last BEGIN WORK command.
But, only the latest versions of MySQL support transactions.

If you want transactions, up grade to the latest MySQL with Berkeley transactions libraries, or change to PostgreSQL (which supports transactions 'properly')