Webtrends crashing

I've run into yet more instances of WebTrends crashing while reading the web server logs. However, I've found that if you have FastTrends turned on the log parser will crash when reading the bad lines, but the next time you run the analysis the bad lines will be skipped. It may take more than one pass to get past the lines causing WebTrends problems, but otherwise the analysis seems to work just fine.

MySQL Stored Procedure Crashing Server?

I'm in the process of converting the IERI student assessment data into a relational format. Currently the data is spread betwee nsixteen different tables (one for each year/subject combination). The method I was attempting to use to transform the data was as follows:

1. Create a temporary table for each form of each test (Algebra Form A, Algebra Form B, etc.) containing a single row for each student/answer combination.
2. Use a stored procedure to take the data from the temporary table and insert it into the tables the will contain the final data.
   

Creating the temporary table is tedious in that a self-join has to be done on the original table for each answer. However, this is well worth it as the data is pretty much in a usable format for insertion into the final tables.

Unfortunately, I ran into a major problem when attempting to get the stored procedure to work. The basic and programmatic flow I designed for the procedure seems to be fine. I was able to test with some dummy data and had no problems. When I added the SQL code to do the necessary selects I encountered a serious error … the MySQL daemon appeared to crash. The server is quick to restart after a crash so it's hard to say without doing some investigation, but it does appear to be a crash.

At first I thought maybe I was having a memory limit problem since the amount of data (10700 rows) is fairly large compared to what we normally work with. (Our server isn't top-notch either.) However, after watching resource utilization (using top) I was unable to see any significant usage during temporary table creation or stored procedure operation. Actually, the server seemed to crash immediately after the stored procedure was called.

The stored procedure consisted of a few variables, a few SQL statement executions (one to populate variables, another to insert data into tables), and a cursor for accessing data from the temporary table. Overall, the structure of the stored procedure seems to be fine and is in line with many samples I've seen on dev.mysql.com. The single-use SQL statements are run using PREPARE and EXECUTE since they utilize variables that are populated by the cursor.

Through much trial and error I was able to determine that the single-use SELECT statement I was using to populate a variable is the source of the crash. Upon execution of that statement the server crashes. The specific problem seems to be when I access a table, as I can use a more basic SELECT (something like SELECT 10 INTO @recordref) and everything seems fine.

Since I don't see any way to get this working as things now stand I'm going to do everything through SQL queries. In the meantime, though, I decided to try doing the transformation as a couple of queries that use subqueries to set up the relational-friendly data tables. Though this is really a one-use setting, there's some thought out there that currently in MySQL it can be faster to do things in this manner (see Roland Bouman: Refactoring MySQL Cursors).

As a reminder, when using the MySQL Query Browser a script is better for something like this rather than a query. The main difference between the two access methods is that all the queries in a script are run in one connection/session whereas each query is run in its own connection. Using a script will allow the creation of temporary tables which, in this particular instance, should speed things up noticeably. First I'll develop all the code I need as queries then create one large script to process all the data.

Sometime in the next few weeks I think I'll upgrade to a newer version of MySQL and see if that solves the problem. If that doesn't work I'll post a question on the forums and see if anyone has any thoughts. After that I'll think about posting a bug report. I'm a bit leery about doing so after my mozilla bug reporting experience, but as long as I'm thorough I shouldn't be given the smack down.

IN Condition Tricks

Not your type, huh? Change for the better.
I needed to pull some data from our contact database (ichaos) based on ZIP code. The list of codes to use was fairly lengthy and I was concerned about the length of a possible WHERE clause since the data is stored as text to accommodate both the 5-digit and 9-digit US formats (not to mention postal codes from other countries).

Even though the column is stored as text I decided to use the IN() condition to provide the condition. This worked much better than I expected. Not only were the specific matches returned (we used the 5-digit variation in the conditional), but MySQL also returned any variations on the provided condition.

The following SQL statement:

SELECT … WHERE post_code IN (12345);

returns not only the records matching 12345 but also any that match on the first five characters (such as 12345-6789).

The reason this search appears to work appears to do with type conversion (see Type Conversion in Expression Evaluation). Since we are attempting to compare a string to a number the two arguments are converted to floating point numbers by default. To have better control over the comparison we could use the LEFT() function to truncate the zip code to 5 digits, then the CAST() function to control the conversion of the column used in the statement.


Your custom order
While performing my search I noticed another useful tip for the IN() condition in the comments of the SELECT syntax documentation page. If you use an IN() condition in the ORDER BY clause you can specify a custom ordering scheme for a column. So the following statement:

SELECT … ORDER BY %column_name% IN (%value1%, %value2%), %column_name%;

will order the column first by the values specified in the IN() condition followed by the default ordering scheme for the type of data stored in that column.


Update 2006/12/05:
After some further testing I've found that the custom ordering trick doesn't work as I thought. What it does is group the records matching the condition and sort those as a group. It does not sort the values according to the order specified in the parenthesis. If you want specific control of the ordering you'll have to do a separate IN() condition for each value, thus grouping all the records with that value.

What this means is that my previous example will place all records with %value1% and %value2% at the top, but the sorting of those values will be whatever the default is (the order entered in the database unless other sorting options have been specified).

To get the results I had been seeking you have to use something more like the following:

SELECT … ORDER BY %column_name% IN (%value1%), %column_name% IN (%value2%), %column_name%;

Windows 2003 web server returns 503 Service Unavailable

Yes, lots of problems today ... but at least they're all easy to fix.

After setting up my new web sites I was getting a "Service Unavailable" response from the server. Turns out that once you create the sites you have to start the application pool associated with those sites. It's a fairly minor additional step, but it'd be nice if starting a site would start the associated application pool (or at least remind you to do so).

Oh, and don't forget to create/turn on the appropriate entries in the Web Service Extensions folder as well (such as for the asp processor).

Domain literals not supported by IIS in Windows

A little misleading of a title, but it gets the point across nicely. The MMC GUI tools don't support the creation of a domain in the SMTP console. The solution, though, is fairly easy. Shut down IIS and edit the metabase directly. If you're lucky enough to have Windows 2003 it's super-easy since the metabase in in XML format.

The procedure I used was to creat a domain of the literal sans brackets first. This makes it easy to find the appropriate entry in the metabase. Then I edited the metabase to add the brackets back in. Easy.

CDONTS not included in Windows 2003

I was setting up a new server today when I realized that Windows Server 2003 does not include cdonts.dll. We have a number of older scripts that use this function to send out mail. Eventually we'll be moving all our stuff to open source, but in the meantime I'd rather not rewrite these scripts to use cdosys.dll (mainly because it requires more work and I'm lazy). Luckily the solution is easy enough ... install the cdonts.dll file from a Windows 2000 Server installation disk.

Microsoft was kind enough to provide the info I needed:

• Migrate CDONTS (IIS 6.0)
• Windows Server 2003 does not install CDONTS
  

I have actually already run into this, so I'm not sure why I hadn't noted it before.

Mass Mailing: Alerts

E-mail alert regarding early-bird workshop registration deadline sent out to 2642 recipients at 1:30 PM on 18 Sep 2006.