Barry brought an interesting point to me today. He needed to find if there existed any values for a particular set. For example this was his test dataset:


CREATE TABLE test (
id INT UNSIGNED NOT NULL,
foo INT UNSIGNED
);

INSERT INTO test (id, foo)
VALUES
(1, 1),
(1, 2),
(1, NULL),
(3, 4),
(3, 2),
(3, 3),
(4, 3),
(4, NULL),
(4, 5),
(5, 1),
(5, 1),
(5, NULL);


In this example he wanted to return all ids and if that id set contained any foos with a value of 1 or 2.

After bandying about for a bit he discovered that you can actually apply a limiter within an aggregate function.

So to find out which ids had a corresponding foos of 1 or 2 could be found from the following:

mysql> SELECT id, MAX(foo IN (1,2)) FROM test GROUP BY id;
+----+-------------------+
| id | MAX(foo IN (1,2)) |
+----+-------------------+
| 1 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 1 |
+----+-------------------+
4 rows in set (0.00 sec)


You could also apply a SUM function to determine how many results you actually had that matched your criteria.

January 13th, 2009 | Programming | 2 comments

Database backups are not a difficult thing, but they are tedious and only important when there is a crisis at hand. Recently we ran into a situation where backups failed to protect us from some worst practices and we had to scrape together the binlogs and old dumps to resurrect the table.

What follows is a true story although the names and places have been changed.

At 13:45 a developer execute the following query

DELETE FROM table
return 159 rows effected


He intended to execute a SELECT * FROM table, and just remove the WHERE clause from a previous query but it turned out to be a DELETE FROM table WHERE ID = #.

As soon as the trigger was pulled he realized his mistake. I was brought in to resurrect the data because I previously performed all backups.

Off we go.

1. Stop replication
2. Flush logs
3. if you can do the processing on the server wonderful, other wise copy the binlogs to a recovery system.
4. mysqlbinlog -d mydb mysql-binlog.[0-9]* | sed '/table/,/;/!d' > recovery_file
5. start replication

From the recovery_file we had a list of every statement issued against the table and we could replay all transactions to return the table to it’s original state with a little editing.

We are now reviewing our backup and recovery practices. I hope this was educational for you as it was for me.

I found the key point for the sed command in this document, and this tutorial helped greatly.

August 12th, 2008 | Programming | No comments