Move over design patterns, here come crash patterns! I'll be talking about this concept in my "Server Down" CFUnited 2008 presentation in June. As many of you know, we here at Webapper have gotten loads of "server down" calls and emails over the years, so the purpose of my talk is to organize all of what we've seen into best practices for preventing performance and stability problems, and also for what to do when problems do arise. And one way we've started to organize this information is with the concept of "crash patterns". It's modeled after the familiar idea of design patterns, but applies instead to common/recurrent causes of performance and/or stability problems (the two usually go together). And, as with design patterns, once you come to understand crash patterns (both in general, and those that are specific to your applications), you can use them to make your life a whole lot easier. And when it comes to performance and stability, that means using the knowledge of crash patterns to avoid these problems in the first place, and it also means having optimal steps to take when/if problems do arise on your production systems.

I hope to see tens of thousands of you packed into the room in D.C! In the meantime, if you have any thoughts/comments/questions, please send them along, as I'd love to incorporate even more community/attendee input into my presentation.

ColdFusion 8 added the ability to specify a "validation query" that will be run every time a connection is reused in a connection pool. This is useful in cases where database servers or firewalls sometimes drop connections without warning, as it causes a new connection to be made to the database server for the page, so that no user sees an error caused by a stale database connection. A good validation query would be something like "SELECT 1" for SQL Server or Sybase, or "SELECT 1 FROM DUAL" for Oracle.

We've seen a few customers lately that have had the value "0" sneak in as their Validation Query for datasources in the ColdFusion Administrator. I've tried and failed to reproduce this, but zeros don't magically appear in that field for me; suffice to say that unrelated people were finding "0" as their validation query, and seeing SeeFusion report errors such as these in their coldfusion-out.log files:

...or...

...or...

Note that while SeeFusion is reporting the error, it's not causing the error.

n.b. If you read the stack from the bottom up, it's telling its story: QueryTag.executeQuery() calls DataSrcImpl.getConnection(), which tries to getCachedConnection(), which hits a JDBCPool object (connection pool) that does a .checkout(), followed by a .validate(). The validate() method calls (after passing through SeeFusion's driver wrapper) the db driver's Statement.execute() method, and that database driver throws the exception.

The deeper, more pernicious problem, is that there is no way to test validation queries in the ColdFusion administrator. And when an invalid validation query is used, connection pooling is basically disabled, forcing new connections to be made to the database for every page view.

Interestingly, when ColdFusion validates the datasource, it runs the validation query, but silently ignores the exception thrown. (We used SeeFusion to see which JDBC calls the ColdFusion Administrator is making.)

Feature request for the next ColdFusion 8 service pack: add a warning message when you save a datasource and the validation query fails!

-=-=-=-=-=-=-=-=- Update -=-=-=-=-=-=-=-=-

As it turns out, there's a bug in the ColdFusion 8 Admin API for datasources. Any datasource you create with the Admin API will have the validationQuery set to "0", no matter what value is actually set (and even if that value is intentionally set blank). This ColdFusion snippet demonstrates:

If you're using ColdFusion to access BLOBs stored in a Sybase server, you may have a problem.  Apparently, Sybase severely limits the timeframe wherein you're allowed to retrieve the actual binary data, which is supposed to be available until the end of the current transaction.  ColdFusion doesn't pull the backing binary data immediately, and when it does, Sybase's driver returns a 0-byte result.

We've implemented a workaround in SeeFusion's driver for this.  If you append ";convertBlobToBytes=true" to the end of the JDBC URL, SeeFusion (starting with version 4.0.7) will quietly convert any BLOB responses to byte array objects before returning the data to ColdFusion's JDBC getObject() call.  I'm not aware of any other drivers having this issue, but it should work for any other driver as well.

At Webapper, we're big fans of monitoring tools. You absolutely MUST be able to see what's going on inside your software in real time. We use MySQL on some of our production servers, and we've been using SQLyog as an administration GUI for many years. It's a great way to run MySQL databases--it's one of the fastest, most bug-free pieces of software I've ever seen--but we've always lamented the lack of tools for real-time monitoring, which are common with other databases like SQL Server. Enter, MONyog.

The same company that created SQLyog has released a tool called MONyog for monitoring and troubleshooting MySQL query activity. Here's a link to the screenshots:

MONyog for MySQL Monitoring

We'll be checking that out soon.

There's a copious quantity of information online about running MSSQL2k and MSSQL2k5 at the same time, but I just did it, and wanted to document my process.

First off, you must have SP4 installed on SQL Server 2000. (I'm running the MSDE edition.) Additionally, you have to have that version installed before installing 2005. (I'm using the Developer not Express edition.) MSSQL 2000 will be accessed using the default (non-named) instance, and MSSQL 2005 will use a named instance. (I chose MSSQL2005 as the instance name.)

I'm something of a stickler for keeping my data files outside of my program files directory ever since suffering a major system failure a while back. With data files on a separate drive, it's easier to run system backups of my data once a day, while reserving backups of my entire c: drive for every-now-and-then. I didn't want to have to make a setup.ini file for the SQL Server 2005 install just to move the data directory and I especially didn't like what I read in the docs about how the installer configures the data directory location. (I.e. inside a couple directories deep, like "MSSQL.1\MSSQL\Data" if you just change the data directory during install.)

But it's easy enough to install MSSQL 2005 in a next-next-next defaults kinda way and change both the default location for new data files and move the existing system data files to a different location.

You'll need to follow these instructions from Microsoft on moving the system databases. And to set the default data directory, just change it via the Database Settings node from the System Properties screen from within SQL Server Management Studio (the new Enterprise Manager).

So that's all there is to it. If you want to make a ColdFusion DSN to a SQL Server named instance, you enter the server like this: "LOCALHOST\INSTANCE_NAME", so in my case, it was "tanto\mssql2005".

Don't forget to update SQL Server to Service Pack 2. It's slower than snot to install, but who knows what kind of buffer overflows will be found in the RTM?

A blog entry at: http://www.coldfusionmuse.com/index.cfm/2005/6/10/sqlInterbase reminded me of how people don't fully understand the multithreaded nature of ColdFusion with respect to databases when looking for the next ID value for a table.

People quite often do something like this in a page:

However, this will give you duplicate IDs when two pages run concurrently. For example, request A and request B run at the same time:

Or worse, if there's no unique (eg, primary key) index on sometable.ID, you wind up with two identical ID values in the table.

Wrapping them in a transaction doesn't help. The default isolation level doesn't hold locks for reads, so the result is the same. Using a higher isolation level simply changes the failure mode to a deadlock. Better than a duplicate ID, but still a failure. An additional drawback of this method is that the database has to seek the index on ID every time, which for very large tables is expensive, relative to the following method.

The best cross-database method of implementing a unique ID is to create a table to store them. I usually name the table NextIDs(TableName varchar(50), NextID int)

Then, you start the transaction and do the update statement FIRST:

You /must/ do the update first, or you'll still have the race condition under the default transaction isolation level; if you set isolation to REPEATABLE_READ or SERIALIZABLE you'll get deadlocks when the race condition occurs.

To summarize:

Select max(id) is never a thread-safe way of implementing a unique identifier. If you want to implement a cross-database method of creating a unique, incrementing identifier, you should create a table for that purpose, and UPDATE before SELECTing from that table (in a transaction) to eliminate race conditions and deadlocks.

The development practice at Webapper does a fair bit of "version 2" system development. Our customer has an existing application, built maybe in ColdFusion, maybe in PHP or another scripting language. Maybe in Access forms, maybe as a desktop application. We'll use it as a prototype for creating a new webified application.

One part of these jobs is tracing how the old application works. Since there is never any documentation, and the source code is often too scary to look at (or no longer even available), it can be hard to find out the business rules underlying the software.

I'm working on such a project these days, and needed to find out where a particular string is being stored in the old database. The database is not normalized, there are no foreign key constraints, and there are inconsistent key names (like users.user_id and records.u_id as pkey and fkey, respectively).

I needed to find where in the database a particular string was being stored. I hunted in the most obvious tables and columns, but there are about 100 tables, and about 500 megs of data (lots of rows). I needed a SQL script to hunt down a particular keyword.

Google found Doug Hughes's stored proc, which I remember having used with success in the past, but today it threw a ton of errors and came up empty handed. A bit more Googling turned up Vyas's better stored proc. It worked like a charm. Hope this helps you!

About a year ago, I came across the Red-Gate suite of products for MSSQL. Between then and now, I've used SQL Compare and Data Compare about once a week, sometimes more often. These tools are one of those things that, once you have them, you ask yourself, "How did I survive without it?"

For example, let's say you're working on a database locally. You add a new table to your database, hooking up a couple foreign key constraints, and in the meantime, you remove an obsolete table.

But unbeknownst to you, your coworker (or Steve in my case), has added a different table to his copy of the same database. Oh and he added a few lookup records to a static table.

How would you synchronize these databases only using tools shipping with MSSQL?

Everyone would do it differently, but I'd start by reversing time and clicking the "Save changes to script" button on the new table designer in Enterprise Manager. Then I'd email that .sql file to Steve which would add it to his database. Oh and those new records he added? He'd set up a DTS to me or dump the data to a text file or script and send it to me to run from Query Analyzer. Or maybe he'd just synch his schema to mine and then restore a backup I took of my database. But then I'd have to zip up the bak file, email it to him... ugh.

These are all very do-able processes. In fact, I've spent a LOT of time managing this type of process. But it's a little kludgy, and far worse, it requires foresight, follow-through, manual merging and multiple steps.

Foresight to make scripted changes to the database and write down what changes are made.

Follow-through to actually push those changes to other servers.

Manual merging by the DBA of that server. (In the case of developer workstations, that means us.)

Multiple steps of scripting the change, sending it, getting him to update, and maybe back again. More steps means more chance for failure, more time spent.

The Red Gate tools eliminate all those headaches. Using SQL Compare, you just point your database to another database (LAN or Internet) and in a few short seconds, it identifies all differences. You check some boxes to declare which changes you want made, hit next-next-next, and it's all done. Then you can reverse the process to update in reverse.

Data Compare works similarly (although more slowly) to compare data in columns in tables. You can tell it only to compare specific tables (to avoid those ten-thousand-row-monsters) and you can save any comparison project to rapidly execute it in the future.

Best couple hundred bucks you'll ever spend if you work with MSSQL a lot.

I'm reformatting my laptop tonight and went to upgrade the Microsoft SQL Server Desktop Edition (MSDE) from the 1.0 RTM to SP4.

Damn the dreaded "The instance name specified is invalid" error!

I've installed this setup countless times and always had to futz, mess, finagle, tweak my setup command to get the darn thing to run. So today I'm documenting it here, forevermore:

Note that this works when you've already connected to MSDE and setup Mixed-Mode authentication (with an SA login) as opposed to just Windows authentication.

Hope this helps someone!

BTW, did you know you can run SQL 2005 with SQL 2000 on the same machine, so long as you've upgraded to SP4 on 2k? GOod to know, eh?

Greetings, all. We''re excited to announce that we formally released SeeFusion 4 on Monday! Here''s a quick summary of new/enhanced features:

• Flex 2 rich interface! From real-time charts to spreadsheet export, this is the crown jewel of SeeFusion 4. Never again can it be said that SeeFusion is great engineering with a dull UI!
• Active monitoring rules. Create user-defined server events that SeeFusion will react to automatically by killing requests, logging events to the SeeFusion database, or sending email alerts.
• Enhanced database logging, including logging of user-defined server events.
• Stack trace filtering. Quickly parse out only the parts of a stack trace that you want to see, filtering out all the rest.
• Debug output by IP address. Manually enter an IP address to view SeeFusion''s debug output for only that user, in order to facilitate addressing user-reported problems
• Streaming running requests. Use a convenient slider control to set a request duration threshold, in order to see all running requests beyond that limit quickly and easily, without having to change the overall slow request threshold.
• Improved trace() method. The SeeFusion.trace() method now gets called automatically for any unhandled exceptions. This can be useful in debugging errors with little or no information available, such as with some CFC errors.
• New 2-for-1 pricing model for individual servers. For any single physical server, every 2 ColdFusion instances that you want to monitor now require just 1 SeeFusion license.

Extended evaluation keys are available to anyone who''d like one. Just ask!

As always, send along any and all questions/comments to our support address--support (at) seefusion.com. Or, call us toll-free (US only) at 866.816.4700 (International customers dial {IDD prefix} + 1.970.223.2278).

Best,
Patrick Quinn
Co-Founder, President & CTO
Webapper Services, LLC