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
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