Thursday, October 19, 2006

Change == Opportunity

Bob Field posted his reactions to the recent MySQL announcement to offer two versions of the MySQL Server product: Enterprise Server and Community Server.

I feel somewhat similarly; the change has the potential to give greater value to both the corporate customers of MySQL and their community users. It will be interesting to see how this develops as we go forward.

By making less frequent releases of MySQL, the company could put more work into quality control, and this could enable them to provide an overall better product. Not that they aren't quite healthy and competitive in that area, but QC that can always be improved.

This would be good of course for MySQL's Enterprise customers, because the product will become an even better technology for stable, secure, performant data management.

But it could also benefit the open source community. The improvements that go into MySQL Enterprise will still be available for download in source form. Anyone can build the MySQL server to get these improvements at any time. Well, almost anyone. :-) It's not that easy to build the sources. But recent documentation (tutorial 1, tutorial 2, tutorial 3) and tools have made it a lot easier to build MySQL from source.

I don't mind the reduction in frequency of prebuilt binaries. The number of people who eagerly upgrade to the latest binaries is a tiny percentage of the most dedicated power users, based on the types of questions I see online. Most people upgrade once every two or three years. The few who stay current can check out the code and build it, so they'll be even more current than they have been in the past. There is a school of thought that power users of open-source software should be building from source anyway.

I usually recommend to newbies to use XAMPP or a similar prebuilt software stack. It will be interesting to see if XAMPP adopts MySQL ES or CS.

How the division of the code develops over time is largely in the hands of community developers. One area that could benefit form contributions is the MySQL build tools.

MySQL Server also needs support for popular IDE tools like Visual Studio and Eclipse. Also the Build Farm Initiative has started to address continuous integration systems. These are areas where the community can help greatly.

But it's also in the hands of MySQL AB, because they still control both the ES and CS code trees. There needs to be a more reliable process for community contributions to be integrated into the official code.

Wednesday, October 11, 2006

Catch-22 of the Active Database

People frequently ask if they can do fancy things in triggers, such as writing to the filesystem, sending an email, or notifying other applications of data changes.

I always recommend against doing things like this. Calling an external processes from a trigger or UDF is very difficult to get right, and it is very easy to cause serious problems with your application.

Say for instance that you want to send an email from within a trigger, to notify someone of a data change.

An operation that spawned the trigger may be rolled back, or it may fail for another reason (violated a constraint, etc.). But the call out to the external process occurs anyway. So the email is sent, but then the database change turns out not to be committed. Thus someone has been notified of a change that effectively hasn't happened.

Even if the operation is successful, it may not be committed immediately. But triggers fire at the time of the operation, not the time of the commit. So the email could be received and the recipient goes to look for the new data. They might not find it, because it's still pending a commit.

It's better to perform notifications to external processes in your application code. After you have confirmed that the database change happened successfully, and the transaction was committed, you can notify other external applications directly.

This doesn't quite work for secondary operations, though. For instance, your app does an UPDATE, and the update trigger does another SQL operation, like INSERT. That INSERT operation is the one about which you want to notify some person or application. How can the application know that the INSERT was successful? How can your app know the values that were inserted, so that it can include those in the notification?

Another similar example is if your app does an operation which affects other data via a foreign key declared with cascading referential integrity rules. For instance, your app does a DELETE, and a dependent table which refers to that table cascades the delete. The deletes on the dependent table are the changes about which you want to notify some person or application. How does the calling application know which tables were affected by cascading operations?

I don't have answers to these latter problems yet. But in most cases, your application does know the tables and rows on which it operates. In those cases, the application should generate notification events or perform other actions outside the database.