title: DBI database handle with AutoCommit set to 0 not returning proper data with SELECT?
body: <p>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 :)</p>
  
  <p>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 <code>AutoCommit</code> is set to 0.</p>
  
  <p>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.</p>
  
  <p>Problem I am experiencing is:</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Then, I have modified the DB connection string in my standalone daemon, and set <code>AutoCommit</code> to 1. Suddenly, application started getting proper data.</p>
  
  <p>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.</p>
  
  <p>Does anyone know why SELECT statement will not return 'updated' rows from the database when <code>AutoCommit</code> is set to 0, and why it will return updated rows when <code>AutoCommit</code> is set to 1?</p>
  
  <p>Here is a simplified (taken out error checking, etc) code that I am using in standalone daemon, and that doesn't return updated rows.</p>
  
  <pre><code>#!/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-&gt;connect($dsn, undef, undef, {RaiseError =&gt; 0, AutoCommit =&gt; 0});
  $dbh-&gt;{mysql_enable_utf8} = 1;
  
  while(1)
  {
      my $sql = "SELECT * FROM queue";
      my $stb = $dbh-&gt;prepare($sql);
      my $ret_hashref = $dbh-&gt;selectall_hashref($sql, "ID");
      print Dumper($ret_hashref);
      sleep(30);
  }
  
  exit;
  </code></pre>
  
  <p>Changing <code>AutoCommit</code> to 1 fixes this. Why?</p>
  
  <p>Thanks :)</p>
  
  <p>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).</p>