Fasten your seatbelts ...

... or how to safely run data manipulation statements in your database.
Reading posts on Devshed's forums I sometime notice people doing maintenance work on their data without any safety net apart from occasional ages old backups ;-).
Anyway I think there's no need for a restore if you just issued the wrong update query, I mean, transactions are here for this, it's just a matter of educating people ...
Say you have a database structure like this

CREATE TABLE `test`.`users` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB; 

and

CREATE TABLE `test`.`agent` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `actiontime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_agent_1` FOREIGN KEY `FK_agent_1` (`id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
ENGINE = InnoDB;

not very nice, it's just an example, but notice that I'm also using real foreign keys (real because these are InnoDB tables).
That's because of the many database engines only InnoDB supports transactions (BDB used to support them but has been discontinued and Falcon is not ready for prime time).

Now let's see how this tables content looks like

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | f        |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:21:44 |
+----+---------------------+
3 rows in set (0.00 sec)

Nothing much really, now, say we want to change the actiontime of agent 'f', but we are unshure of the synthax and we don't want to damage our data, that's how we should do:

mysql> set autocommit='OFF';
Query OK, 0 rows affected (0.00 sec)

Actually we have just fastened our seatbelts!!! Autocommit is off and the database will make permanent changes on request only, now on with the query

mysql> update agent a inner join users u on a.id = u.id set a.actiontime=now() where
u.username = 'f';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Looks ok, but let's check what's really happened

mysql> select * from agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:27:07 |
+----+---------------------+
3 rows in set (0.00 sec)

Ok, we updated the right row!
At the same time users are querying the database, but, as we haven't committed work, they see the "old" data (note, this depends on transaction isolation level)

mysql> select * from test.agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:21:44 |
+----+---------------------+
3 rows in set (0.00 sec)

You see? But, as we are satisfied with changes, it's time to commit work, to actually make those changes permanent.

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

At this time users other than the one which actually changed data will start seeing the updated version, see it by yourself

mysql> select * from test.agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:21:44 |
+----+---------------------+
3 rows in set (0.00 sec)
commit by the other user happened between
these selects
mysql> select * from test.agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:27:07 |
+----+---------------------+
3 rows in set (0.00 sec)

This was an easy case, but transactions are a safety net because you can rollback changes, see it in action

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | f        |
+----+----------+
3 rows in set (0.00 sec)

mysql> delete from users;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from users;
Empty set (0.00 sec)

 :eek: I just deleted all my precious users!!!

mysql> rollback;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | f        |
+----+----------+
3 rows in set (0.00 sec)

But I had my safety net :-D

Here you can find the thread that prompted me to write this post.

I seem to hit a bug (severe or not) every time I post, this is no exception, see bug 35318, but again and again MySQL's support team showed it's dedication, solving and closing it in less than 8h!!!