| Bug #16173 | Failure to execute prepared statement with negative numbers on 4.0.x servers | ||
|---|---|---|---|
| Submitted: | 4 Jan 2006 6:17 | Modified: | 28 Jan 2006 15:04 |
| Reporter: | Luoqi Chen | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connectors: DBD::mysql ( Perl ) | Severity: | S2 (Serious) |
| Version: | 3.0002 | OS: | FreeBSD (FreeBSD/Linux) |
| Assigned to: | CPU Architecture: | Any | |
[28 Jan 2006 15:04]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the problem you described on latest 4.0.27:
openxs@suse:~/dbs/4.0> bin/mysqld_safe --log &
[1] 23604
openxs@suse:~/dbs/4.0> Starting mysqld daemon with databases from /home/openxs/d
bs/4.0/var
openxs@suse:~/dbs/4.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.27-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show create table t;
+-------+-------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------+
| t | CREATE TABLE `t` (
`n` int(11) default NULL
) TYPE=MyISAM |
+-------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
openxs@suse:~/dbs/4.0> cat ~/work/16173.pl
#!/usr/bin/perl
use DBI;
my $DSN='DBI:mysql:test:suse';
my $DBUSER='root';
my $DBPASSWD='';
my $dbh=DBI->connect($DSN, $DBUSER, $DBPASSWD) or die print "$!\n";
my $sql='INSERT INTO t (n) VALUES (?)';
my $sth=$dbh->prepare($sql);
$sth->execute(-1);
print "$!\n";
exit;
openxs@suse:~/dbs/4.0> perl ~/work/16173.pl
openxs@suse:~/dbs/4.0> cat var/suse.log
/home/openxs/dbs/4.0/libexec/mysqld, Version: 4.0.27-log, started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
060126 18:27:25 1 Connect root@localhost on test
1 Query show databases
1 Query show tables
1 Field List t
1 Field List testaa
1 Field List testad
060126 18:27:28 1 Query show create table t
060126 18:27:29 1 Quit
060126 18:27:37 2 Connect root@localhost on test
2 Query SET AUTOCOMMIT=1
2 Query INSERT INTO t (n) VALUES ('-1')
2 Quit
As you can see, query was written to the general query log appropriately.
openxs@suse:~/dbs/4.0> perl -v
This is perl, v5.8.6 built for i586-linux-thread-multi
Copyright 1987-2004, Larry Wall
Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'. If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.
openxs@suse:~/dbs/4.0> uname -a
Linux suse 2.6.11.4-20a-default #1 Wed Mar 23 21:52:37 UTC 2005 i686 i686 i386 GNU/Linux
DBD::mysql 3.002 was built on this same machine in a default way agains 4.0.27 libraries.
Description: When using perl DBD 3.0002 with a 4.0.x server (or any server without server prepare support), execution of a prepared statement will skip any negative number when constructing the SQL query. For example, $sth = $dbh->prepare('INSERT INTO t (n) VALUES (?)'); $sth->execute(-1); will generate this query on the wire, INSERT INTO t (n) VALUES () This is traced down to an incorrect cast from sth to dbh in function mysql_st_internal_execute() that results in a random value to be passed as bind_type_guessing parameter to parse_params(), combined with incorrect handling of negative number when bind_type_guessing == 1. The attached patch fixes the first problem (incorrect cast). How to repeat: See the example in description. Suggested fix: --- dbdimp.c.orig 2005-09-23 17:15:45.000000000 -0700 +++ dbdimp.c 2006-01-03 21:23:13.664306880 -0800 @@ -402,7 +402,7 @@ STRLEN *slen_ptr, imp_sth_ph_t* params, int num_params, - bool bind_type_guessing) + int bind_type_guessing) { bool seen_neg, seen_dec; @@ -2310,7 +2310,8 @@ int use_mysql_use_result ) { - D_imp_dbh(sth); + D_imp_sth(sth); + D_imp_dbh_from_sth; STRLEN slen; char *sbuf = SvPV(statement, slen); char *salloc = parse_params(svsock,