| Subject: | AutoCommit setting to 0/1 cannot be disabled when making a new connection |
I originally submitted this bug back in Nov 2007 to the MySQL Bug Tracker.
http://bugs.mysql.com/bug.php?id=32464
[16 Nov 2007 22: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 by coding in DBD::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