Home of the Spider

Log Parsing

So I'm writing a log parser. We finally realized that without some knowledge of who's coming from where on our site, we have no way of making decisions regarding marketing.

There are two ways I could do this. The first is to build the tracking & reporting in to the current platform. Problem is, this platform is supposed to be replaced soon, at which point the whole tracking system becomes obsolete. Also, it adds processing for every page request, slowing down the site. The other option was to parse the log files. I liked this option because its portable and it doesn't interfere with the processing of the page itself.

Turns out that the log files are big, 200-300 Mb each. It takes quite some time to process files that large. But thats ok, I have time. Over the weekend, the parser read two weeks of logs. As the files were parsed, the database grew. As the database grew, the whole process began to grind to a halt, as querying the database for previous sessions and information about the user was taking up 100% of the CPU.

The log files contain all the requests the server handled, including request for images, css files and such that I'm not interested in for analysis. As I read the file line by line, I first make sure the line is one that I'm interested in before processing and storing the data. This save a lot of processing, because most of the requests are for images.

At this point I hit a problem. The website creates a persistent cookie that the log parser uses to identify users. This works fine, except for first-time visitors. A first-time visitor will (obviously) not be sending his cookie to the server, so cant get logged. The server will set a cookie that will be sent along on subsequent requests, but responses aren't logged either. So the first hit will be orphaned from subsequent hits.

The second hit, for non-bots will be for an image or a css file. This request will be logged along with his cookie. I don't have a perfect way to match these two requests up, but I figure IP address and user-agent should be reliable enough. So I can no longer just ignore all those image requests, I run an update query to update the user record with his cookie ID where the IP address and the user-agent match.

This is a lot of update queries. And these queries (obviously) cannot be cached. And about 95% of those queries do nothing, as there is matching record to update. I haven't had the opportunity to test this, but I think that first testing for a matching record to update would significantly speed things up. Why? Because the select query can be cached, 95% of the time there will be almost no database lookup at all.

---

Item of the Day: Decolav Wall Mounted Wrought Iron Basin

MySQL Server, Still Away

Well, please excuse the previous nonsense.

The cause of the error, "MySQL Server has gone away", is that the connection to the database becomes stale. So if the connection is left open for more then 8 hours without use, this error will be triggered. Previously, I suggested that using Application_OnEnd() to close the connection would solve this error.

That is not the case. Because the application doesn't necessarily end, OnEnd() never gets triggered, and the connection is never closed.

On a site with a steady stream of traffic, this may not be an issue. But for my application, an administrative management suite, there is a 12-16 hour break every night. This error is guaranteed to happen on a nightly basis.

So now, I can't rely on the database connection created in Global.ASA without making sure it isn't stale. Being that the whole point of creating the connection in Global.ASA is laziness, so I don't have to include a script to make the connection on each page. Well, that experiment didn't work. 

---

Item of the Day: Kitchen Pull Down Faucet - Rainforest WH3-2169 by Whitehaus

MySQL Server has Gone Away

Um, well, where did it go?

I gotta give props to the MySQL guys, that is one of the funniest error messages. Usually when I get it I'm not laughing, though. See, I dislike it when MySQL goes away without saying goodbye first.

The actual cause of the error itself is simple enough. A connection to the database opened in Global.asa* wasn't being closed and nothing'ed, so the next day the connect was still open. A connection left open and unused for a long period of time will trigger that error. Simple cause, easy to fix. I added the code to close the connection in application_onEnd, and all was good again.

* Note: Opening the database connection in Global.asa and using that connection throughout the application should only be done on very small sites. A connection can only execute one query at a time, so all other queries get queued. This can slow you website down to a crawl. I believe the MySQL ODBC driver has connection pooling built-in, so you should create a new connection for each request. This will only create a new object when all existing connections are busy. If there is an existing connection available, a new connection will not be created.

---

Item of the Day: Floor Mount Tub Filler: Reprise Series 5535.901 from Porcher

MySQL Migration Toolkit

I'm in the midst of setting up my new dedicated webserver, and boy am I glad about that. The last few days my site has been crawling due to traffic from other sites on my current server. (Eventually, someone will realize that you simply cant have a designer building your site and expect to handle any kind of traffic.)

Step 1 is moving the data. Installing MySQL is a breeze, and they have even nicely packaged all the GUI applications together, called "GUI Tools" making them that much easier to install. This package is considered a Release Candidate.

I had used the Migration Toolkit to transfer data from one server to another with some success, so I started off using it. That was not a smashing success. Firstly, it uses and old JDBC driver that will not communicate the passwords correctly, so I had to create a new user without a password. Ok, so I'm able to connect. But will it move the data? No. It transfered one table successfully, and then got stuck on the second. It didn't stop, just looked like it was in an endless loop and never reported an error.

I figured that there was probably some corrupt data or something in that table that the Migration Toolkit wasn't handling correctly, so I tried migrating one table at a time. This worked for most tables, but was very time consuming and defeated the purpose of the tool. It also didn't work for the one table I needed it for, the biggest table in the database, so I stopped the process.

I ended up transferring the data by simply backing up the old database, transferring it to the new server and restoring it. That process is slow, about 2 hours, but it worked for all the tables in the database.

From my experience, this product is far from ready for release.

---

Item of the Day: KWC Kitchen Waterstation

Pulling it all Together

In my two previous posts I discussed MySQL FIND_IN_SET() and GROUP_CONCAT(). The real beauty of these functions is how they complement each other, substantially reducing the amount of ColdFusion code and database queries needed for the same result.

When displaying a product, its nice to display the hierarchy of its categorization, like such: DVD > Documentary > World War II. To do that, I have to:

1. Get the product
   <cfquery>SELECT productInfo FROM Products WHERE ProductID = #ProductID#</cfquery> NOTE: this is unsecure, always user <cfqueryparam>
2. Get the category
   <cfquery>SELECT CategoryInfo FROM Categories WHERE CategoryID = #ProductInfo.CategoryID#</cfquery>
3. Use the text field containing the heirarchy to query for all the categories
   <cfquery>SELECT allCategories FROM Categories WHERE CategoryID IN (CategoryInfo.hierarchy) ORDER BY CategoryLevel</cfquery>
4. loop over the result to display
   <cfoutput query="allCategories">#CategoryTitle# > </cfoutput>

I've never like the structure of this code, seems like way too much work just to display the category titles.

Using FIND_IN_SET() and GROUP_CONCAT(), we can pull this information straight from the database in the original product query, using a sub-query.

<cfquery>SELECT productInfo, categoryInfo, (SELECT GROUP_CONCAT(CategoryTitle) FROM Categories WHERE FIND_IN_SET(CategoryID, CONCAT(categoryInfo.heirarchy, ',', categoryInfo.CategoryID))) as heirarchyText FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE ProductID = #ProductID#</cfquery>

This is much simpler. Now to display the hierarchy, you just output the variable #queryname.hierarchyText#.

I use the MySQL function CONCAT() to add the current category ID to the heirarchy, because in my database, the field containing the category does not reference itself. But when I pull the category titles, I want the current category included, so I add that ID to the list.   

---

Item of the Day: Porcher Bath Tub: Calvari 65200

MySQL GROUP_CONCAT

My current ecommerce platform, the worlds worst, is built in ColdFusion. Though I'm not a fan of ColdFusion, it is not the cause of the plaform's horribleness - languages are rarely the cause of bad programming, just a symptom.

Anyway, ColdFusion has all these nice little helper functions. One that comes in handy quite often is ValueList(). ValueList takes a column in a query and returns a delimited list of all the values in that column. I've used this function to total up a column by specifying a '+' as the delimiter and evaluating the result. This function is useful because there are many times where you just want to show a list of items, but the database returns them a seperate rows. So instead of looping through the result set, this gives you a nicely formatted list.

But many times retrieving this information requires a seperate query to the database. For example, to display a list of states, and the zip codes in that state. You'd query for the states, and then for each state you'd query the database for the zip codes in that state. You can't do a join, because then you'd get one record for each zip code, where I want one record for each state. And you can't do a sub-query because there is more than one zip code per state, and sub-queries cannot return more than one record. So you end up with 51 queries to database.

Ahhh, my favorite MySQL function: GROUP_CONCAT(). GROUP_CONCAT allows you to creat a delimited list (you can specify the delimiter using SEPERATOR) of the results of a query, in one record. Now you can get all the states, and for each state you get a delimited list of zip codes in that state, all in one query. That just got rid of 50 extra queries!

Here's an example of the above SQL:
SELECT s.StateID, s.StateName, (SELECT GROUP_CONCAT(z.ZipCode SEPERATOR ', ') FROM ZipCodes z WHERE z.StateID = s.StateID) as zipCodes
FROM States s

---

Item of the Day: Porcher Bathroom Vanity: Dune Console

MySQL FIND_IN_SET()

What I love about MySQL is the little things it does so well. Of course, I have no idea if MSSQL or Oracle have the exact same functionality, but I work with MySQL, so thats what I'll discuss.

As has been previously noted, the platform I'm currently stuck with is horrible. It allows you to create unlimited level hierarchy of product categories, but in its horrid way, made it very difficult to get the top-level group for a sub-group. So if I were displaying a category "History", and wanted to know if it the top-level category was "DVD" or "Books", I had to a separate query.

This always bothered me, because there is a field in each category that contains a comma delimited list of the hierarchy of the category's parents. But I had no way of searching that field, until i discovered the MySQL function FIND_IN_SET(). With this function, I can have a sub-query that does this "SELECT c2.CategoryTitle FROM Categories c2 WHERE FIND_IN_SET(c2.CategoryID, c.CategoryParentHistory) and c2.Level = 1", and that will give me the top-level group.

This has made my life so much easier.

---

Item of the Day: Stainless Steel Kitchen Sink: Franke MHX 610-33 Apron Front

Hero of the Day

Its nice to be the hero once.

Until quite recently, I worked for a marketing firm that also did websites. (sensing a problem yet?) This firm, whom we will refer to as XMG, recently decided to rebuild their platform from scratch (that itself is many stories for many other days.) Happily, its finally time to begin installing the new platform on this clients website. But first, they database must be upgraded to version 5, so developer "C" installs the latest build of mysql and begins importing the old data. At some point, this process is canceled.

Uh oh.

MySQL was not happy about that, and the clients website is dead. So developer "A" and "B" join the fun, trying to resuscitate the website. At some point, they are able to reinstall version 4, but cannot get the data back.

Then they call me, at this point quite desperate. And so begins the process of undoing a days worth of damage to the poor database.

The funny part is, the client was running MySQL version 5 the entire time.

---

Item of the day: Bathroom Vanity: Yang Santos Rosewood by Forms+Fixtures