During a recent release of a new ASP.net website, the launch went horribly wrong and required a rollback.

Setup

Scenario

Whilst the code was in the development environment, we weren’t seeing any issues. Once the code was published to a staging environment and the load on the temporary under powered server increased, we started to see errors creeping in. At the time, it was written off as being a combination of Oracle 10g Release 1 Standard and the server simply not having the resources to handle the load. We had previously seen poorly written legacy code bring a Dual 3.2Ghz Xeon running Oracle 10g Standard Release 1 (dedicated server processes) to its knees with similar style random errors.

Searching online seemed to reveal a common trend; it was an Oracle Data Provider problem. Unfortunately, everything we found was related to the version 9 data provider – while we were already running version 10. After four developers spending a few hours trying to resolve the error, a support request was logged with Oracle via MetaLink. Over the following three hours and a continuous stream of phone and email correspondence with Oracle, one of their technicians informed us of a patch set available for our current Oracle Data Provider.

Patch Information

Out of list of bug fixes, two items should be highlighted:

1. 4020081: As we were running short of servers during the development phase, we had Oracle 10g Release 1 Standard installed on a standard desktop machine with a single hard drive. Once the application was released to testing, the Linux ‘load’ on this machine was regularly breaking 15-20. As such, this could have been contributing to the Data Provider Internal Error.
2. 3893458: The legacy version of this application had been running on the v10.1.0.301 of the Oracle Data Provider for nearly a year without any issues. That code base made very limited use of PL/SQL stored procedures, while all new code was being funneled through PL/SQL. Strangely, the legacy code would have been firing the ExecuteReader() method, while the new code was firing ExecuteNonQuery(). If this was the cause directly, you would have expected those to be around the other way. I think it is worth listing here, as the decision to funnel everything through PL/SQL is a fundamental shift in the application architecture.

It should be noted that without the help of the Oracle Technician, we would have never found this patch on MetaLink in a timely fashion; which I would consider a serious shortcoming of the site. However, after downloading and applying the patch manually, the random errors immediately vanished.

There was a lesson learned from this experience, the Oracle Data Provider was discounted as a likely problem early on because we were using a newer version of the provider than that of the error reports we were seeing. Looking back on it, we should have immediately upgraded the Oracle Data Provider to the latest public release, simply to rule out a bug in an older version.

Again today, working in ASP backing onto Oracle I had another 80020009 error. As I previously stated, the error code was as follows:

1. error '80020009'
2. Exception occurred.
3. /somefile.asp, line <number>

This time I immediately went to solve the problem based on my previous solution. As it turned out, the date was incorrect. Once corrected however, the error did not go away. I preceded to set the date field back to NULL and then back to a valid date; which was being stored in dd/mm/yyyy format, still nothing.

At this point I started hunting down the possible cause. I couldn’t display the date in that field at all through ASP, yet the same field in different tuples were just fine. I could use an isNull() on the date and it reported that there was in fact ‘something’ in the field. If I tried a Len(), I would get an error. The sub-type of the field in the recordset reported by VarType() for all other tuples was in fact 7 (VBDate), however the one in error was a 9 (VBObject).

At this point I was wondering whether Oracle had some how managed to store a damaged date, so I wrote a small query using SQL to do a comparison on the field against SYSDATE:

1. SELECT completeddate,
2. CASE
3. WHEN (completeddate > SYSDATE) THEN
4. 'TRUE'
5. ELSE
6. 'FALSE'
7. END as FutureEvent
8. FROM tblevents
9. WHERE eventid = 49401;

As expected, Oracle returned the correct values in every case. Which left me with no place to really go forward from here. To put the web application back into a working state, I used the VarType of the field to test whether I should or shouldn’t attempt to display the value; dirty hack but a working solution for the moment.

This leads me to believe, that some how ASP/ADO/something is some how reporting/munging the value of that field for some reason. I still don’t quite know how or why; all I know at this point is that it is frustrating and consumed my time on something that should have otherwise worked perfectly.

I was given another strange error tonight and once again searched around for a quick solution but it didn’t present itself immediately. The error was given was:

1. Error Type: (0x80004005)
2. Unspecified error
3. /aspfile.asp, line <linenumber>

I’m finding it increasingly frustrating that the error codes that ASP presents are far to broad. You get given an error and it can relate to one of 100 different things. At which point, you need to whittle down that list till you finally find one that roughly matches your scenario. I realise that the language is old and it isn’t as much of a problem these days, however if they know it errored and they can categorise the error into the 0x80004005 group, then they must of known exactly what the problem was. At which point, why not provide your developers a specific and useful error message; it would sure save them a lot of time.

Continuing on, in the end, the problem was caused by the name field I was updating, it was a reserved word. This brings me to my next point. You would think that when the person that created this Access database designd it (not me), that Access would have prompted him with a “This is a reserved word, you are not allowed to use it as either a table or field name”. Now, it might have presented him with it, clearly it wasn’t forceful enough as he named it with an invalid name regardless.

To give you an idea of what I was doing, here is a code snippet:

1. sSQL = "SELECT * FROM [Position/Title] WHERE lcase(position) = '" & LCase(Request.Form("position")) & "'"

In this case, not only was his table name wrong (who uses slashes in names, really!), but the field “position” was the reserved word. To save renaming the field, as I didn’t want to change the database, you can wrap the field names in square brackets [ ].

Thankfully Access provided a way around the problem, which was simple and convenient. Now if only the error reporting was concise.