[philiptellis] /bb|[^b]{2}/
Never stop Grokking


Saturday, March 29, 2008

Checking transactions in MySQL

I'd been doing some stress testing of my mysql application today, and I was hitting some weird cases. Several transactions were deadlocking - this was expected - but the number of records that got inserted into my table was more than the number that I expected after subtracting errors.

My test was fairly simple:
  1. Fork 15 processes
  2. Insert and update 100 records in each process, running each INSERT/UPDATE pair inside one transaction
  3. ROLLBACK on error
Either the INSERT or the UPDATE was expected to fail due to deadlock, and the whole transaction should have rolled back leaving no record in the table.

Before I go on, I should mention that I was using InnoDB, which does support transactions.

What I expected was that the total number of records in the table + the total number of INSERT/UPDATE aborts due to deadlock should be equal to 1500 (15*100). What I was actually seeing, was completely off. At one point I had 400 errors and 1299 records in the table - that's 199 extra records, and at another point I had 344 errors with 1497 records left over. On inspection of the records, I found that many of them had bad data. In most cases, it looked as though the UPDATE had never run, and in other cases, it looked as though the UPDATE ran on more than one record.

Now, my code looked something like this:

mysql_query("START TRANSACTION", $db);

$result = mysql_query("INSERT ...", $db);
if(!$result)
{
log_error($db);
mysql_query("ROLLBACK", $db);
return false;
}

$result = mysql_query("UPDATE ...", $db);
if(!$result)
{
log_error($db);
mysql_query("ROLLBACK", $db);
return false;
}

if(mysql_affected_rows($db) != 1)
{
log_error("bad update");
mysql_query("ROLLBACK", $db);
return false;
}

mysql_query("COMMIT", $db);
return true;
So if anything failed, there should have been no record in the table, yet I was seeing records that did the INSERT, but not the UPDATE, or that did the UPDATE for more than one record. Control was getting in to all the if conditions - I was getting log messages for that, but the ROLLBACK was never taking effect.

Again, before anyone asks, the log_error() function I wrote uses debug_backtrace to log the filename and line number that it was called from.

I stared at the screen for a while wondering what the problem could be. The MySQL docs said a few things:
  • InnoDB supports transactions
  • AUTOCOMMIT is On by default
  • Use START TRANSACTION or BEGIN to turn AUTOCOMMIT OFF until the next ROLLBACK OR COMMIT
  • ROLLBACK rolls back everything until the last START TRANSACTION if a transaction is in effect, or the last statement (if AUTOCOMMIT is OFF)
  • COMMIT commits the last statement or transaction depending on whether AUTOCOMMIT is OFF and a transaction is active or not
Nothing explained why ROLLBACK wasn't working here.

The only possibility I could think of, was that the START TRANSACTION itself failed, but when I checked the mysql docs, there was no mention of START TRANSACTION ever failing. Anyway, I decided to check, so I changed the start of the code to this:

if(!mysql_query("START TRANSACTION", $db))
{
log_error($db);
return false;
}
And immediately I started seeing errors on this line.

The error I got was:
   Lost connection to MySQL server during query
Searching on Yahoo! got me this link on the MySQL reference manual. The second last point on that page answered my question immediately:

You can also encounter this error with applications that fork child processes, all of which try to use the same connection to the MySQL server. This can be avoided by using a separate connection for each child process.

That's exactly what I was doing. I was opening the db connection before forking, so each child inherited a copy of that connection, and as soon as one of them closed it, all the others would fail.

So, I changed my code so that the db connection was opened and closed inside each child process, and now my results are consistent. I still get deadlocks, but the sum of errors seen and records added does add up correctly to the expected total.

Moral of the story: It may be okay to ignore the return values of COMMIT and ROLLBACK, but always check the return value of START TRANSACTION.

Friday, March 07, 2008

Creative Vista Webcam VF0330 on Ubuntu

Got back home a couple of days ago, and there was a new webcam sitting on my box. It was being used on windows, which I'm naturally allergic to. I decided to try and get it working on my ubuntu laptop.

Now I've set up webcams on linux before, and it was a pain, but it got done. This was over five years ago, so I'd assumed things may have gotten easier by now. They hadn't. New webcams were out, with just as bad support as there was five years ago. All the same, documentation on the web is good if you know where to find it. That's what this doc is for.

The first thing to do was find out what model my webcam was. I knew it was Creative, since that's what was written on the cam itself. Plugged it in, and dmesg just said that a new USB device had been plugged in. Then I ran lsusb, which gave me this:

Bus 005 Device 001: ID 0000:0000
Bus 001 Device 013: ID 041e:405f Creative Technology, Ltd
Bus 001 Device 001: ID 0000:0000
Bus 002 Device 001: ID 0000:0000
Bus 004 Device 001: ID 0000:0000
Bus 003 Device 001: ID 0000:0000

So the vendor ID was 041e — which is Creative, and the product ID was 405f, which probably maps onto some name, but that's immaterial.

Most webcams (well, creative ones at least) work with the ov511 driver that comes packaged with most distros, but this cam didn't, so I started searching around for support for this particular product ID. Found out from RastaGeeks that this was a Creative Vista Webcam VF0330 (the VF0330 matched the model number at the back of the cam), and that it was supported by the ov51x-jpeg driver, version 1.5.2 or higher.

The Ubuntu Webcam page had instructions on setting this up, which I followed and met with success.

Now, chances are that you'll find other information as well about this driver. One of the steps I'd followed was to install the ov51x-jpeg-source and module-assistant packages using apt, and then build and install the module using module-assistant. Unfortunately, this installed an older version of ov51x-jpeg, which didn't work with the camera. That led me to believe that the driver didn't work, until I tried the newer version.

If you've done this, then you will need to apt-get remove ov51x-jpeg-modules-2.6.22-14-generic first, then install the new driver and then run depmod -A.

...===...