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.

stubbing out java.util.Random with EasyMock and Spring

Assuming you are familiar with Spring and EasyMock, here is a little tutorial on how to stub out randomness for your JUnit tests.

First I probably need to give a little context on how I have my test framework set up with Spring. I have two spring config files, one of them is just for testing and injects stubs/mock objects where appropriate. I have a base test class that extends AbstractDependencyInjectionSpringContextTests. It has members (and setters) for all of my classes that I want to test, and all of the stubs that get injected.

I make all of my classes to test scope="prototype" and my stubs are all singletons, so that I can easy get access to the same stub that will be injected into my class under test. Before running a test, I set up the stubs appropriately, and get them ready to replay. After the test, I call EasyMock.reset() to reset them for the next test (since they are singleton).

Ok, on to the example. Let's say you have a method that uses java.util.Random:


public class MyRandomClass
{
  private void Random rand;

  public int doSomethingRandom()
  {
    return rand.nextInt();
  }

  public void setRand(Random rand)
  {
     this.rand = rand;
  }
}


In your real spring config file you would have something like this:

<bean id="myRandomClass" class="MyRandomClass">
   <property name="rand" ref="rand"/>
</bean>

<bean id="rand" scope="prototype" class="java.util.Random"></bean>


And in your test spring config you would set up your 'rand' bean like this:

<bean id="rand" class="org.easymock.classextension.EasyMock" factory-method="createMock">
   <constructor-arg value="java.util.Random"/>
</bean>


Notice that I am using the EasyMock Class Extension because Random does not have an interface (Sun should add one, in my opinion). The regular EasyMock library can only mock interfaces, and the class extension adds the ability to mock classes themselves.

Now when setting up your test case, you can specify what 'random' values you want, like this:


EasyMock.expect(rand.nextInt()).andReturn(0);
EasyMock.expect(rand.nextInt()).andReturn(1);
...


And then you know what to expect:

assertEquals("returned wrong result", 0, myRandomClass.doSomethingRandom());
assertEquals("returned wrong result", 1, myRandomClass.doSomethingRandom());


That's it! (at least for this simple toy example)

One thing I noticed is that when using the EasyMock Class Extension, you have to remember to use the org.easymock.classextension.EasyMock createMock(), reset(), replay(), verify() etc. methods on any actual classes that you stub/mock. But I was able to use org.easymock.EasyMock.expect(), etc. methods when setting up the stubs.

using Spring in an Axis Web Service Impl

This is a re-post from another blog of mine:

I have an existing code base using Axis for web services, and am working on integrating Spring (2.0) into the system, for transaction management.

This is not straightforward, because Spring likes to be the one that creates your objects, so that it can inject dependencies. But with web services, Axis creates the service implementation class, not the Spring container. A 'hack' is necessary as an alternative to the standard Spring injection. And while it is not straightforward, it's not difficult, either. It just seemed that way to me because all the examples I found on the internet were confusing.

Here's the short version of how to get things working:

1. If you are generating a Skeleton class with wsdl2java (-skeletonDeploy), stop doing it.
2. Create a wrapper class for your service impl. For me this meant renaming my MyServiceSoapBindingImpl class to MyServiceImpl, and then regenerating MyServiceSoapBindingImpl. MyServiceSoapBindingImpl is now the wrapper class, and you have it contain a MyServiceImpl object and delegate all calls to that object.
3. Change the wrapper class to extend ServletEndpointSupport, and make sure that your wsdd points to that wrapper class.
4. Override ServletEndpointSupport.onInit(), and get the real impl (MyServiceImpl) as a bean using getWebApplicationContext().getBean("myServiceImplBean");

Now your impl class can use Spring for dependency injection just like any other class.

Here's the long version:

30,000 feet

What we want is to have a service interface (MyService), a service impl (MyServiceImpl) with the actual code, and be able to inject dependencies into MyServiceImpl (dao, etc.).

public interface MyService
{
public int doSomething();
}

public class MyServiceImpl implements MyService
{
private HelperBean myHelperObj;

public void setMyHelperObj(HelperBean myHelperObj)
{
this.myHelperObj = myHelperObj;
}

public int doSomething()
{
return myHelperObj.doTheSomething();
}
}

We want to be able to set this up in Spring:


<bean id="helperObj" class="HelperBean"></bean>

<bean id="myService" class="MyServiceImpl">
<property name="myHelperObj" ref="helperObj"/>
</bean>


And then set that service class up as a web service. This means that your wsdd would have something like this:


<service name="MyService" provider="java:RPC"></service>
...
<parameter name="className" value="MyServiceImpl"/>
...
</service>

The problem is that since MyServiceImpl is your web service class, Axis is in charge of it, not Spring. Since Spring does not create the class, it can't inject the HelperObj!

So let's see what the workaround is. First, I need to explain how I have things set up.

Axis/service class setup

I generate the wsdl from the service interface using the ant java2wsdl task, and then generate the stub, locator, etc. classes from the wsdl using the ant wsdl2java task. Because of this, my service impl class is actually named MyServiceSoapBindingImpl, just because that's what the ant task generates. The classes generated are:

• MyServiceSoapBindingStub
• MyServiceService
• MyServiceServiceLocator
• MyServiceSoapBindingImpl

Originally we were also generating a Skeleton class for the service (skeletonDeploy="yes"). This ended up causing me headaches, and I'm not really sure what the purpose of the Skeleton class is, anyway. Bottom line: don't do it.

To get around Spring not being able to inject dependencies into your service impl, it's necessary to make your service impl class (the one that your deploy.wsdd points to) a wrapper around the real impl class that you want to inject dependencies into. The wrapper class extends Spring's ServletEndpointSupport class, which provides an onInit method that you can override to get access to the spring application context:

public class MyServiceImpl implements MyService
{
private HelperBean myHelperObj;

public void setMyHelperObj(HelperBean myHelperObj)
{
this.myHelperObj = myHelperObj;
}

public int doSomething()
{
return myHelperObj.doTheSomething();
}
}

public class MyServiceSoapBindingImpl extends ServletEndpointSupport implements MyService
{
private MyService impl;

protected void onInit() throws ServiceException
{
impl = (MyService) getWebApplicationContext().getBean("myService");
}

public int doSomething()
{
return impl.doSomething();
}
}

So the 'hack' is that you have to use ServletEndpointSupport and access the Spring context directly, but from that point on, your real impl class can behave like any other Spring bean.

The part that messed me up was the Skeleton class. Since the wsdd pointed to the Skeleton class instead of the Impl, Spring for some reason didn't call my onInit() callback method. Hopefully this helps someone else having the same problem.

web.xml

Your web.xml doesn't need to have anything special. Just the normal configation. I got confused because all the examples I found on the web made it seem that you had to register a DispatcherServlet, which is not necessary just for what we are trying to do here.