S h o r t S t o r i e s

// Tales from software development

MySQL Connector/NET hangs on MySQLDataReader.Read()

with one comment

This is a curious problem that I haven’t had the time to investigate fully but I’ve done enough to find a workaround…

The project I’m working is for managing patient data and is nearing completion. It takes patient data from a number of source systems although there are two main ones. Unfortunately, it’s just been discovered that the surnames of the patients in these two source systems don’t always match. We needed to understand why and the first step was to quantify the problem by comparing the surnames in the two systems to establish how many were different.

This was complicated by the fact that while one system used a MySQL database that could be queried directly the other system implemented a web services interface that only allowed a single patient to be queried at a time.

So, the solution required a SQL query to be executed against the database to retrieve all patients and then each row was processed by calling the web services method to get the corresponding patient on the other system. The surnames were compared and if they were different a message was written to a log file.

It was a simple enough bit of code, no more than about 50 lines of C# in total but it continually failed. Worse still, it didn’t throw an exception and exit, it just hung. It wasn’t possible to use the Visual Studio IDE to debug the code either because the program had to run in a production environment.

After the third attempt it became obvious that it was hanging at exactly the same point in the execution. After about 7 minutes and when it was processing patient surnames beginning with ‘CRAIG’. I added some trace output code and it appeared that the program was hanging in the loop where it read from the MySQLDataReader and then called the method that checked the surnames:

    while (reader.Read())
    {
        this.CompareLastName(reader.GetString("PatientID"), reader.GetString("LastName"));
    }

 
It was one of those moments when you begin to question your grip on reality – how could this code possibly hang ? I could understand that it might throw an exception but why would it just hang ?

This code was implemented using MySQL Connector/NET 6.0.3 which had been used extensively elsewhere in the project with only a couple of minor issues.

The more investigation I did the more it was clear that this was exactly where the problem was and it seemed to be time related – at least it always hung at the same stage of execution.

Finally, out of sheer desperation and a looming deadline, I changed the code to populate a Dictionary instead of calling the CompareLastName() method and then coded a foreach block to process the data in the Dictionary. That worked perfectly.

Once I had the results and was able to provide them to the teams responsible for maintaining the two source systems I was able to spend an hour or so trying to figure out why the original code didn’t work. The first thing I did was to pepper the code with trace output commands so I could see exactly where it was failing. It turned out that it was the MySQLDataReader.Read() method but, strangely, once the trace output methods were inserted, the method threw an exception rather than just hanging. The exception message was:

“Connection unexpectedly terminated.”

Not terribly helpful but better than nothing. I checked the default command timeout in the MySQL Connector/NET documentation – it’s 30 seconds. But the program was hanging after 7 minutes of execution. So, the problem wasn’t how long the connection had been open but how long it had been left idle. I’m guessing here but I think the problem was caused by one particular web services call that took long enough to return that the 30 seconds command timeout meant that the MySQL connection had been closed by the server.

I added a Default Command Timeout to the connection string that the program was using:

Database=vitality;Data Source=dbserver-43;User Id=vuser;Password=dh27ehdk;Default Command Timeout=300

 
So, the command timeout was now 5 minutes rather than 30 seconds.

I ran the program again… It worked perfectly.

I’m still not sure why it just hung and didn’t throw an exception. In future I’ll be more wary of code that potentially takes a long time to process a single row of data (e.g. has to make a web service call).

Advertisements

Written by Sea Monkey

June 9, 2010 at 2:00 pm

Posted in Debugging, Development

Tagged with ,

One Response

Subscribe to comments with RSS.

  1. I just spent hours of searching WHY my results were incomplete.. you saved my day 🙂

    Maarten

    August 25, 2011 at 10:13 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: