Bug #32464 AutoCommit setting to 0/1 cannot be disabled when making a new connection
Submitted: 16 Nov 2007 21:59 Modified: 6 Jul 2010 15:03
Reporter: Russell Glaue Email Updates:
Status: Closed Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S4 (Feature request)
Version:3.0008 OS:Any
Assigned to:
Tags: autocommit, dbd, dbi, perl
Triage: D5 (Feature request)

[16 Nov 2007 21:59] Russell Glaue
Description:

Creating a new connection with DBD::mysql will always issue the SQL "set autocommit=0" or "set autocommit=1" when making a new connection.

If you are using MySQL Proxy, and are attempting to intercept a connection and block normal SQL queries, you will always get a Failed Connection: "Turning off/on AutoCommit failed." because "set autocommit=#" does not return successfully.

How to repeat:

Install MySQL Proxy 0.6.0 with any number of backend mysql servers.

Create a Lua script that will pass a connection past connect_server(), but in the read_query() function block any SQL statement that begins with "set".

-- test.lua --
function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
    local query = string.sub(packet, 2)
    local com_type,command = string.match(query, "^%s*(%w+)%s+(%S.*)" )
    if (string.upper(com_type) == 'SET') then
      print(string.format("SET SQL statement received: %s",command))
        out_msg = "We deny all SET SQL statements!"
        print(string.format("returning error: %s", out_msg))
        proxy.response = {
          type = proxy.MYSQLD_PACKET_ERR,
          errmsg = out_msg
        }
        return proxy.PROXY_SEND_RESULT
      end
    end
    return proxy.PROXY_SEND_QUERY
end
function connect_server()
  -- let proxy decide what backend to use
end
--

start proxy with --proxy-lua-script=test.lua

Connect to the Proxy service port (4040) with DBD::mysql

$dbh = DBI->connect( $dsn, $user, $pass { 'RaiseError' => 0 });

You will get an error: like
  $dbh is undef
  DBI::errstr(): Turning on AutoCommit failed

Suggested fix:
The current DBD::mysql AutoCommit options are
  { AutoCommit => 1} or { AutoCommit => 0 }
It would be great if we could have a third option
  { AutoCommit => undef }

This third option would cause DBD::mysql to never send the SQL statement "set autocommit=#".

We never want to send an SQL statement on connect when we want a successful SQL connection to MySQL Proxy, but want to block SQL queries using MySQL Proxy.

If the "set autocommit=#" SQL statement MUST be sent on connect without user control, then we want the option to ignore it in this case and still have a successful connection.

This "set autocommit on connect" matter-of-fact has never been a problem before MySQL Proxy came on to the scene. Now that MySQL Proxy is here, I ask that this be changed as I described.
This proposed change (AutoCommit => undef) should not affect any current DBD::mysql users who follow the documenation for AutoCommit.

You can still have the default be on my coding in DBI::mysql....

  %args = @_;
  if (!exisits $args{'AutoCommit'}) {
    # set AutoCommit to default of 1
  } elsif ((exisits $args{'AutoCommit')
            && (!defined $args{'AutoCommit'})) {
    # do not send the "set autocommit=#" sql statement
  } else {
    # set AutoCommit to value of $args{'AutoCommit'}
  }

Thank you.
-RG
[19 Jun 2009 1:11] Patrick Galbraith
Hi - I'm fixing this with 4.012

There is a new option to use with DBD::mysql:

my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
  { mysql_no_autocommit_cmd => 1});

Or

$dbh->{mysql_no_autocommit_cmd}
[6 Jul 2010 15:03] Russell Glaue
This bug was resolved.

https://rt.cpan.org/Public/Bug/Display.html?id=46308