Mysql – DBI database handle with AutoCommit set to 0 not returning proper data with SELECT

This is a tricky one to explain (and very weird), so bear with me. I will explain the problem, and the fix for it, but I would like to see if anyone can explain why it works the way it works 🙂

I have a web application that uses mod_perl. It uses MySQL database, and I am writing data to a database on regular basis. It is modular, so it also has its own 'database' type of a module, where I handle connection, updates, etc. database::db_connect() subroutine is used to connect to database, and AutoCommit is set to 0.

I made another Perl application (standalone daemon), that periodically fetches data from the database, and performs various tasks depending on what data is returned. I am including database.pm module in it, so I don't have to rewrite/duplicate everything.

Problem I am experiencing is:

Application connects to the database on startup, and then loops forever, fetching data from database every X seconds. However, if data in the database is updated, my application is still being returned 'old' data, that I got on the initial connection/query to the database.

For example – I have 3 rows, and column "Name" has values 'a', 'b' and 'c' – for each record. If I update one of the rows (using mysql client from command line, for example) and change Name from 'c' to 'x', my standalone daemon will not get that data – it will still get a/b/c returned from MySQL. I captured the db traffic with tcpdump, and I could definitely see that MySQL was really returning that data. I have tried using SQL_NO_CACHE with SELECT as well (since I wasn't sure what was going on), but that didn't help either.

Then, I have modified the DB connection string in my standalone daemon, and set AutoCommit to 1. Suddenly, application started getting proper data.

I am puzzled, because I thought AutoCommit only affects INSERT/UPDATE types of statements, and had no affect on SELECT statement. But it seemingly does, and I don't understand why.

Does anyone know why SELECT statement will not return 'updated' rows from the database when AutoCommit is set to 0, and why it will return updated rows when AutoCommit is set to 1?

Here is a simplified (taken out error checking, etc) code that I am using in standalone daemon, and that doesn't return updated rows.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Data::Dumper;
$|=1;

my $dsn = "dbi:mysql:database=mp;mysql_read_default_file=/etc/mysql/database.cnf";
my $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 0, AutoCommit => 0});
$dbh->{mysql_enable_utf8} = 1;

while(1)
{
    my $sql = "SELECT * FROM queue";
    my $stb = $dbh->prepare($sql);
    my $ret_hashref = $dbh->selectall_hashref($sql, "ID");
    print Dumper($ret_hashref);
    sleep(30);
}

exit;

Changing AutoCommit to 1 fixes this. Why?

Thanks 🙂

P.S: Not sure if it anyone cares, but DBI version is 1.613, DBD::mysql is 4.017, perl is 5.10.1 (on Ubuntu 10.04).