SuperDuper "Smart Update" doesn't stay smart

I use SuperDuper for backups on my work machine (MacBook Pro), and I have it set up to backup daily to an external drive using "Smart Update", which is supposed to be fast and only copy things that have changed. I really have no idea how it works, nor do I care, as long as it's working.

The problem is that it has started to take longer and longer to run. It had reached the point where it was taking 2 hours or more to finish (my drive is 120G and I don't back all of it up). I found very little help by searching on Google, so I asked the Sysadmin, who also uses SuperDuper, if he had seen the same problem. He said "I don't know, I only do full backups once a week".

That made me think that maybe SuperDuper just doesn't handle backing up repeatedly using Smart Update only. So I revised my schedule to do a full backup once a week, and Smart Updates daily. Success! My daily backups are back down to 15 minutes or less! I thought I'd post this in case it helps someone else.

SELECT DISTINCT with ORDER BY

I recently wrote a query in MySQL that didn't seem to be returning the right results, and at first I couldn't figure out why. Here is a toy example, where we are tracking pages and page views (one-to-many relationship):


create table page (
    page_id integer unsigned primary key,
    name varchar(32) not null,
    created datetime not null
) engine=InnoDB;

create table page_view (
    page_view_id integer unsigned primary key,
    page_id integer unsigned not null,
    created datetime not null,
    
    foreign key (page_id) references page (page_id) on delete cascade
) engine=InnoDB;


What I want to get is the most recently viewed pages. Let's say I have the following data in my tables:


mysql> select * from page;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       1 | page 1 | 2000-01-01 00:00:00 |
|       2 | page 2 | 2000-01-02 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
|       4 | page 4 | 2000-01-04 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from page_view;
+--------------+---------+---------------------+
| page_view_id | page_id | created             |
+--------------+---------+---------------------+
|            1 |       3 | 2000-01-01 00:00:00 |
|            2 |       1 | 2000-01-02 00:00:00 |
|            3 |       1 | 2000-01-03 00:00:00 |
|            4 |       3 | 2000-01-04 00:00:00 |
|            5 |       2 | 2000-01-05 00:00:00 |
|            6 |       4 | 2000-01-06 00:00:00 |
|            7 |       2 | 2000-01-07 00:00:00 |
+--------------+---------+---------------------+
7 rows in set (0.00 sec)


What I want to get back is page 2 (most recently viewed), then page 4, then page 3, then page 1.

So I write my query:


mysql> select distinct p.page_id, p.name, p.created from page p join page_view pv on p.page_id = pv.page_id order by pv.created desc;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       4 | page 4 | 2000-01-04 00:00:00 |
|       2 | page 2 | 2000-01-02 00:00:00 |
|       1 | page 1 | 2000-01-01 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)


That's not right at all! What's going on?

The problem is that I'm using distinct just on the page table, but ordering by the page_view table. Since there is a many-to-one, what is the database supposed to do when a page has multiple views? which view should it use for the order by?

What I wanted the query to do is first join, then order, then apply the distinct. That's not what MySQL does, though. It first joins, then applies the distinct, then orders the results (or something like that). You can think of it like MySQL going sequentially through the page_view table, finding rows with distinct page ids. So it would pick rows 1,2,5,6:


+--------------+---------+---------------------+
| page_view_id | page_id | created             |
+--------------+---------+---------------------+
|            1 |       3 | 2000-01-01 00:00:00 |
|            2 |       1 | 2000-01-02 00:00:00 |
|            5 |       2 | 2000-01-05 00:00:00 |
|            6 |       4 | 2000-01-06 00:00:00 |
+--------------+---------+---------------------+
4 rows in set (0.00 sec)


You can see that if you order those by created, you get the page order that the (badly written) query returned (4,2,1,3).

We can force MySQL to do things in the order we want by changing the query to:


mysql> select distinct p.page_id, p.name, p.created from (select p.page_id, p.name, p.created from page p join page_view pv on p.page_id = pv.page_id order by pv.created desc) as p;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       2 | page 2 | 2000-01-02 00:00:00 |
|       4 | page 4 | 2000-01-04 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
|       1 | page 1 | 2000-01-01 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)


But I think that's kind of a hack, and depends on MySQL doing the distinct in a certain order (I don't think order by in a subquery is standard sql, and shouldn't necessarily constraint the order of the entire query). So what's the "right" way to write this type of query?

Before I tackled that, I thought, "What would a strict database like PostgreSQL do with this type of query?" My hope was that it would throw it out altogether. And it does. Here's what I get:


postgres=# select distinct t1.id, t1.name, t1.created from table1 t1 join table2 t2 on t1.id = t2.table1_id order by t2.created desc;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list


That's much better, and the error message is very helpful, and makes sense. So here's the query I came up with that will give the correct results, and is correct SQL, in MySQL...:


mysql> select p.page_id, p.name, p.created from page p join (select page_id, max(created) as created from page_view group by page_id) v on p.page_id = v.page_id order by v.created desc;
+---------+--------+---------------------+
| page_id | name   | created             |
+---------+--------+---------------------+
|       2 | page 2 | 2000-01-02 00:00:00 |
|       4 | page 4 | 2000-01-04 00:00:00 |
|       3 | page 3 | 2000-01-03 00:00:00 |
|       1 | page 1 | 2000-01-01 00:00:00 |
+---------+--------+---------------------+
4 rows in set (0.00 sec)

...and in PostgreSQL:

postgres=# select p.page_id, p.name, p.created from page p join (select page_id, max(created) as created from page_view group by page_id) v on p.page_id = v.page_id order by v.created desc;
page_id |  name  |       created       
---------+--------+---------------------
       2 | page 2 | 2000-01-02 00:00:00
       4 | page 4 | 2000-01-04 00:00:00
       3 | page 3 | 2000-01-03 00:00:00
       1 | page 1 | 2000-01-01 00:00:00
(4 rows)


Is there a better performing query out there to do the same thing? I'd love to know, please leave a comment! :)

safely editing MySQL triggers in a production database

MySQL does not provide an atomic CREATE OR REPLACE TRIGGER, or an ALTER TRIGGER statement that will safely modify a trigger on a database while it is in use. The only way to update a TRIGGER is with a DROP and then a CREATE.

Why is that a big deal? Say, for example, you are using triggers to keep row counts up-to-date in a summary table. You may miss some inserts while you are issuing the DROP and then the CREATE. To verify this, I used mysqlslap. Here is my schema script:


drop table if exists triggertest.record_count;
create table triggertest.record_count
(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
count_name VARCHAR(64) NOT NULL,
count_value INTEGER UNSIGNED NOT NULL DEFAULT 1,
UNIQUE (count_name)
) ENGINE=InnoDB;

drop table if exists triggertest.record_table;
create table triggertest.record_table
(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
some_value VARCHAR(64) NOT NULL
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS triggertest.sp_increment_record_count;

DELIMITER |

CREATE PROCEDURE triggertest.sp_increment_record_count(IN countname VARCHAR(64))
BEGIN
INSERT INTO triggertest.record_count(count_name, count_value) VALUES (countname,1) ON DUPLICATE KEY UPDATE count_value = count_value + 1;
END
|

DELIMITER ;

DROP TRIGGER IF EXISTS triggertest.tr_record_table_ins;

CREATE TRIGGER triggertest.tr_record_table_ins AFTER INSERT ON triggertest.record_table
FOR EACH ROW CALL triggertest.sp_increment_record_count('record_table');


I used mysqlslap to run a lot of inserts against the record_table, and while that was running, I re-created the trigger by running this script a bunch of times:


DROP TRIGGER IF EXISTS triggertest.tr_record_table_ins;
CREATE TRIGGER triggertest.tr_record_table_ins AFTER INSERT ON triggertest.record_table
FOR EACH ROW CALL triggertest.sp_increment_record_count('record_table');


I then verified that the count_value in record_count was smaller than the number of records in record_table:


mysql> select * from record_count;
+----+--------------+-------------+
| id | count_name | count_value |
+----+--------------+-------------+
| 1 | record_table | 9944 |
+----+--------------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from record_table;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)

mysql>


At first, I was not sure there would be a solution to this. I realize that you can lock tables, but my first guess was that since ddl (DROP, CREATE, etc.) statements cause an implicit commit, that my locks would be released.

Fortunately, as the MySQL documentation explains, if you use LOCK TABLES, implicit commits don't release your locks. From the docs:

...statements that implicitly cause transactions to be committed do not release existing locks.

So the safe way to recreate my trigger is like this:

set autocommit=0;
lock tables triggertest.record_table write;
DROP TRIGGER IF EXISTS triggertest.tr_record_table_ins;
CREATE TRIGGER triggertest.tr_record_table_ins AFTER INSERT ON triggertest.record_table FOR EACH ROW CALL triggertest.sp_increment_record_count('record_table');
unlock tables;


I had some trouble testing this with mysqlslap, even with only one thread running inserts, because of some locking issues (the inserts would error out with a 'Lock wait timeout'), but I did get a few tests to make it through, so I could verify that the record_count matched the number of rows in the record_table. So the worst-case seems to be that some of the inserts on the production database may hit a lock wait timeout, but no inserts will miss firing the triggers!

UPDATE: Soon after writing this post, I came across this: http://code.openark.org/blog/mysql/why-of-the-week, which may explain why I was having so many problems with deadlocks when I tried to run against a database that was in use. I'm not talking about deadlocks where MySQL detects it and rolls back a transaction. Things would just lock up. No deadlock detected, no lock wait timeout, just locked up, until I killed a query. So while the solution above should work in theory, beware of MySQL locking bugs...

PostgreSQL transactions and error handling

I'm taking our web app that runs on MySQL and seeing what it would take to get it running on PostgreSQL. I just discovered one rather glaring difference between PostgreSQL and most other DBMSs.

Here is an example to demonstrate:

I have a table: my_table, with the following row:


----------------
| id  |  val   |
----------------
| 3   | row 3  |
----------------


Let's say I want to make sure I have rows for ids 1 through 5 in the table. This is a case where I would use MySQL's 'INSERT IGNORE' statement, which doesn't exist in PostgreSQL. I have at least two options: I can create a stored procedure to do it, or I can do it in code.

Let's say I create a procedure:


CREATE OR REPLACE FUNCTION my_proc() RETURNS void AS
$$
BEGIN
  FOR i IN 1..5 LOOP
  BEGIN
    INSERT INTO my_table(id, val) VALUES (i, 'row ' || i);
  EXCEPTION WHEN unique_violation THEN
  -- do nothing
  END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;


All is well, this type of exception handling is standard in stored procedures. Here's the entire test:


postgres=# create database my_test;
CREATE DATABASE
postgres=# \c my_test;
You are now connected to database "my_test".
my_test=# create table my_table (id INTEGER PRIMARY KEY, val VARCHAR(64));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "my_table_pkey" for table "my_table"
CREATE TABLE
my_test=# CREATE OR REPLACE FUNCTION my_proc() RETURNS void AS
my_test-# $$
my_test$# BEGIN
my_test$#   FOR i IN 1..5 LOOP
my_test$#   BEGIN
my_test$#     INSERT INTO my_table(id, val) VALUES (i, 'row ' || i);
my_test$#   EXCEPTION WHEN unique_violation THEN
my_test$#   -- do nothing
my_test$#   END;
my_test$#   END LOOP;
my_test$# END;
my_test$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
my_test=# insert into my_table values (3,'row 3');
INSERT 0 1
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
(1 row)

my_test=# select my_proc();
 my_proc 
---------
 
(1 row)

my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
  1 | row 1
  2 | row 2
  4 | row 4
  5 | row 5
(5 rows)

my_test=# 


Now here's the twist - you can't (really) do the same thing outside of a stored procedure (without using savepoints, as I'll get to later). Here's what happens:


my_test=# \set AUTOCOMMIT OFF
my_test=# delete from my_table where id <> 3;
DELETE 4
my_test=# commit;
COMMIT
my_test=# insert into my_table values (1, 'row 1');
INSERT 0 1
my_test=# insert into my_table values (2, 'row 2');
INSERT 0 1
my_test=# insert into my_table values (3, 'row 3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
my_test=# insert into my_table values (4, 'row 4');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
my_test=# insert into my_table values (5, 'row 5');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
my_test=# commit;
ROLLBACK
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
(1 row)

PostgreSQL forces you to rollback a transaction that hits any error. There is no exception handling! Granted, you can get around this by using savepoints:


my_test=# insert into my_table values (1, 'row 1');
INSERT 0 1
my_test=# insert into my_table values (2, 'row 2');
INSERT 0 1
my_test=# SAVEPOINT my_hack;
SAVEPOINT
my_test=# insert into my_table values (3, 'row 3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
my_test=# ROLLBACK TO SAVEPOINT my_hack;
ROLLBACK
my_test=# insert into my_table values (4, 'row 4');
INSERT 0 1
my_test=# insert into my_table values (5, 'row 5');
INSERT 0 1
my_test=# commit;
COMMIT
my_test=# select * from my_table;
 id |  val  
----+-------
  3 | row 3
  1 | row 1
  2 | row 2
  4 | row 4
  5 | row 5
(5 rows)

my_test=# 


But I see this as more of a hack than a real solution.

I can see arguments for both sides of this issue. I really like this conversation about the issue: http://www.nabble.com/25P02,-current-transaction-is-aborted,-commands-ignored-until-end-of-transaction-block-td3710080.html . But what bugs me is that stored procedures can do exception handling, but no one else can. Especially in my case, where I'm using Java and JDBC, which throws exceptions for any errors that come back. I would rather do my own exception handling, or at least have the option of doing my own.

So here's my vote for changing PostgreSQL to behave more like MySQL in this case.

should you name foreign key constraints in MySQL?

In MySQL, if you don't name your foreign key constraints, the database generates a name for them automatically. Foreign key constraint names must be globally unique within the database, so unless you have a reason to name them, it is probably more hassle than it is worth.

I happen to have a reason to want to name them, and our setup is probably not that uncommon, so others may discover that it is advantageous to name them, too. We have three different databases where I work - one for development ('dev'), a staging database for testing each iteration, and patches ('staging'), and, of course, our live database that the site runs on ('live'). Additionally, each developer has a local database on their personal machines, to develop against.

For each iteration, we create a single schema migration script as we are developing. It will likely get run in pieces on the dev database, and there may be multiple revisions to a table as the iteration develops. Usually the script is very final by the time it is run against staging, but there is always the possibility of additional changes late in the game.

So where foreign key constraint names come into play is when you want an alter statement that can be run against all the different databases without changing. If you leave the naming up to the database, they are typically named in a sequential fashion (first foreign key constraint will probably have a '_1' at the end, the next will have '_2', etc.).

The problem is that if you create and drop foreign keys in different orders on different databases, the names won't match up. The foreign key named 'blah_1' on dev might be on a different column than the one with the same name on staging. You have to alter them by name, so there is no way to have a single script that will run correctly on all of the databases.

Spring AOP and @annotation pointcuts

I'm working on a Spring-AOP and annotation-based solution for caching web service requests. Here's the overview:

I have created an Annotation named "Cacheable", that you use like this:


@Cacheable(seconds=60)
public Data getData(int id)
{
  ...
}


Then using Spring's AOP functionality, I want to wrap every method that is annotated as @Cacheable in around advice that uses memcache to return cached results.

The problem I ran into was getting access to the seconds attribute of the annotation in the advice (for setting the cache timeout).

What I didn't understand, and wasn't clear to me from the Spring docs, was how to pass the annotation to the advice.

(Note: I'm using schema-based aop configuration)

Normally, if you are just trying to match methods that are annotated in a pointcut expression, you would make a pointcut definition with "@annotation(com.xyz.AnnotationName)". But what if you want to have access to the annotation in the advice? Your advice looks like this:


public Object aroundCacheable(ProceedingJoinPoint pjp, Cacheable cachable) throws Throwable
{
  int timeout = cacheable.seconds();
  ...
}


Your pointcut expression has to specify what to pass as the cacheable parameter in the advice. So you have to modify the pointcut definition and add arg-names like this:


<aop:around pointcut="@annotation(cacheable)" method="aroundCacheable" arg-names="cacheable"/>


My understanding is that this tell Spring to look for an argument named cacheable in the advice method, and figure out the type from that. This seems a little strange because the pointcut definition is dependent on the advice. It seems like a pointcut should be self-contained, and not depend on how it is used. But maybe I'm missing something. I'll have to look into it more later, but for now, I'm just glad I got it working.

MySQL: triggers + replication = frustration

For the most part, I've been impressed with MySQL, but every once in a while I hit a problem that really surprises me. It seems like MySQL has this mentality that if there is a bug that is hard to fix, just document it, and then it's a feature and not a bug. Nice.

MySQL claims to have most of the power features of a robust RDBMS like triggers, foreign keys (although I consider that the most basic of features), stored procedures, etc. But it sure is frustrating to find out that most are incomplete.

Sure MySQL has foreign keys, and cascade deletes, but don't expect cascade deletes to fire triggers. That's a documented feature (bug).

Sure MySQL has triggers, but don't try using them if you are also using replication. We recently got bit by a feature (bug) where stored procedures or triggers that insert multiple records in tables with auto-increment don't work with replication. The auto-increment values on the replica will get off, and replication will break.

The problem is that before each insert statement in the binlog, there is a statement to set the auto-increment value. This is important to make sure that the values will always be the same between master and replica. On the master you may have two transactions that run in parallel, and use interleaved auto-increment values:

tx1: insert into table1 ... (uses auto-increment value 1)
tx2: insert into table1 ... (uses auto-increment value 2)
tx1: insert into table1 ... (uses auto-increment value 3)
tx1: commit;
tx2: commit;

In the binlogs, the transactions are serialized, so the auto-increment value has to be explicitly set:

tx1:
set auto-increment to 1;
insert into table1...
set auto-increment to 3;
insert into table1...

tx2:
set auto-increment to 2;
insert into table1...

But consider the case where the insert is on a table with a trigger that inserts a record into a second table (like an auditing table). The binlog only sets the auto-increment value for the actual insert statement. The trigger's insert will use whatever the replica's auto-increment value for the second table is set to. Since simultaneous transactions on the master are serialized in the binlogs, inserts on the second table may happen out of order, and auto-increment values will no longer match the master.

It seems that MySQL is not planning on fixing this bug in 5.0. My understanding is that in 5.1 the solution will be to use row-based replication. Statement based replication will still be broken, from what I can tell. I did see one bug report where someone said something about mixed mode replication, and switching to row-based temporarily for any statement or stored procedure call that will insert multiple records. Sounds like a can of worms to me.

Regardless of what happens in 5.1, there will be no solution for this in 5.0. And from my experience, I'll be nervous to move to 5.1 anytime soon. So it looks like I'll be stuck with this "feature" for a while to come. Nice.