Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 46308
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: rglaue [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: (no value)
Fixed in: (no value)



Subject: AutoCommit setting to 0/1 cannot be disabled when making a new connection
Download (untitled) / with headers
text/plain 2.9k
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
Download (untitled) / with headers
text/plain 3.5k
Hi hi! I fixed this and will release tomorrow. Version 4.011 - my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass', { mysql_no_autocommit_cmd => 1}); Or $dbh->{mysql_no_autocommit_cmd} I didn't want to re-write the Autocommit flag to be something other than boolean. Thank you for the bug report! On Fri May 22 15:23:07 2009, RGLAUE wrote: Show quoted text
> 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
See previous comment.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.