Data Management

Manage session state on Web farms using Perl and MySQL

Managing session state in a Web farm presents many challenges. See how one Builder.com member built a solution that mimics an IIS/COM+ solution in Perl and MySQL.


This article was originally published in the Builder.com Web Development Zone e-newsletter.

Basic ideas can still be good ideas. For instance, I recently read an article about maintaining session information on IIS using a COM+ object and storing session information in SQL Server. The information was stored by generating a GUID for a user, storing that GUID as the identity of the user in a SQL database, and retrieving key-value pairs from that database by looking up the user’s GUID. This GUID was stored as an HTTP cookie that was passed back and forth from the client to the server and vice versa.

This concept provides a method for storing session information on a Web farm apart from using session variables. Session variables are unique to one server, so on a Web farm, if a user requests a page from one server and a subsequent request is sent to another server, the user loses the session data.

My self-imposed challenge
I decided to develop a way to mimic the technique I read about but to use Perl with a MySQL data backend. Here are the details of my development process.

How I did it
First, I created two tables, Session and SessionInfo, to store session information. The Session table contains two fields: session_id (the GUID for the user) and date_created (a timestamp field). The SessionInfo table has three fields: session_id (the GUID and foreign key to the Session table), key_name, and key_value.

My logic behind creating, retrieving, and updating the data is as follows:
  • If the page is requested without any information passed to it, a new GUID is created and XML is returned containing the new GUID.
  • If the page is requested with GUID information, all key-value pairs are returned in XML format.
  • If the page is requested with GUID as well as key-value information, key-value pairs are created or updated, and all of the data is returned again in XML format.

The request XML string should be formatted as:
<root><session><session_id/><key_name/><key_value/></session></root>

Now that we have the foundation for our application, it’s time to create it. Listing A contains my script for accomplishing all of these tasks:

The code first creates $request and $response objects to handle form data and produce response HTTP, respectively. Next, it creates the header for the return HTTP. Then, it looks for XML data in the txtXML key. (This can be in either POST data or in the query string.)

A connection to the MySQL database is established, at which point the script looks for a session_id in the request XML. If there isn't a session_id, one is created using the Scriptlet.TypeLib ActiveX object for non-Win32 machines. (Another method must be established to create a GUID.) This value is inserted into the Session table, and the resulting XML is created from a query based on the GUID.

If there is a session_id and a key_name, the information is either updated or inserted into the SessionInfo table, depending on whether the key_name already exists in the SessionInfo table. Finally, the complete XML is returned, containing all of the key-value pairs associated with the current session_id.

The result is a quick, cheap Perl script for creating, accessing, and updating session information that can be stored on a central repository, which in this case is a MySQL database.

Since this script is a basis for future development, remember to change the host name for the MySQL connection to a central MySQL server once it's established. Also, it never hurts to perform a little housecleaning and optimization.

Editor's Picks