MySQL Bugs: #15556: wrong type returned for DECIMAL col w/mysql 5.0.x

Bug #15556	wrong type returned for DECIMAL col w/mysql 5.0.x
Submitted:	7 Dec 2005 19:43	Modified:	9 Jan 2007 15:41
Reporter:	Ray Zimmerman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S1 (Critical)
Version:	3.0002-4.00	OS:	Linux (Linux, Mac OS X 10.4 (Tiger))
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
When connecting to mysql 5, it returns the type of a DECIMAL column as SQL_VARCHAR, as opposed to SQL_DECIMAL returned when the mysql server is version 4.1.

The details of my setup:

	Mac OS X Tiger 10.4.3
	perl 5.8.6
	DBI 1.47
	DBD::mysql 3.0002 (and 3.0002_04) (built with mysql-5.0.16 libraries)
	mysql 5.0.16
	mysql 4.1.15

See also thread at <http://lists.mysql.com/perl/3696>.

How to repeat:
The script below demonstrates the bug, outputing ...

   value   12  SQL_VARCHAR

... when connecting to a mysql 5.0.16 server on localhost, vs ...

   value    3  SQL_DECIMAL

... when connecting to a mysql 4.1.15 server on localhost.

#!/usr/bin/perl -w
use DBI;

## get DBI type map
my %map;
foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
	$map{&{"DBI::$_"}} = $_;
}

my $dbh = DBI->connect('DBI:mysql:test', 'test');
my $table = 'mysql5bug';
my $drop   = "DROP TABLE IF EXISTS $table";
my $create = "CREATE TABLE $table (value decimal(5,2));";
my $select = "SELECT * FROM $table WHERE 1 = 0";

## create table and get column types
$dbh->do($drop)		or die $dbh->errstr;
$dbh->do($create)	or die $dbh->errstr;
my $sth = $dbh->prepare( $select );
my $rv = $sth->execute;
my $fields = $sth->{NAME};
my $types  = $sth->{TYPE};

## print out column types
foreach (0..$#$fields) {
	printf("%8s  %3d  %s\n", $fields->[$_], $types->[$_], $map{$types->[$_]});
}

## cleanup
$dbh->do($drop)		or die $dbh->errstr;
$sth->finish;
$dbh->disconnect;

1;

Thank you for a bug report. Verified just as described with 
DBD::mysql 3.0002_04 built with mysql-5.0.18 libraries (ChangeSet@1.1992, 2005-12-09 00:58:59+03:00) on Linux:

[openxs@Fedora 5.0]$ perl -w 15556.pl
   value   12  SQL_VARCHAR

The same perl script, build with the same libraries, gives 

[openxs@Fedora 4.1]$ perl -w ../5.0/15556.pl
   value    3  SQL_DECIMAL

when connecting to 4.1.17-BK.

See also bug #14923

Bug #14923 was marked as a duplicate of this one.

Unfortunately, this bug still exists in DBD-mysql-3.0003 using the following versions:

	Mac OS X Tiger 10.4.6
	perl 5.8.6
	DBI 1.49
	DBD::mysql 3.0003 (built with libraries from either mysql version below)
	mysql 5.0.21
	mysql 4.1.19

Will it help if I beg?  :-)  I'm setting this to S1 (Critical) since this is the only thing that keeps me from being able to upgrade to mysql 5.0.

I confirm this bug. This bug is critical for me.

My system:
Linux 2.6.16 (Debian)
perl 5.8.8
DBI 1.50
DBD::mysql 2.2027
mysql 5.0.20

Confirmed that the bug still exists in DBD-mysql-3.0004 using the following
versions:

	Mac OS X Tiger 10.4.6
	perl 5.8.6
	DBI 1.50
	DBD::mysql 3.0004 (built with libraries from either mysql version below)
	mysql 5.0.21
	mysql 4.1.19

Confirmed that the bug still exists in DBD-mysql-3.0006 using the following
versions:

	Mac OS X Tiger 10.4.6
	perl 5.8.6
	DBI 1.51
	DBD::mysql 3.0006 (built with libraries from either mysql version below)
	mysql 5.0.22
	mysql 4.1.20

mysqlcom.h field type values changed drastically between 4.1 and 5.0, hence many resort to varchar. I will add a bunch of code  for 5.0 and above to give correct types.

mysql_com.h: hmm, mysql 5.0 "decimal" is "newdecimal".

My big trouble I solve with this nasty hack:

--- dbdimp.c.orig	2006-06-10 01:47:26.000000000 +0200
+++ dbdimp.c	2006-07-27 07:41:04.353125097 +0200
@@ -1039,6 +1039,7 @@
     case FIELD_TYPE_MEDIUM_BLOB: return &SQL_GET_TYPE_INFO_values[20];
     case FIELD_TYPE_LONG_BLOB:   return &SQL_GET_TYPE_INFO_values[21];
     case FIELD_TYPE_STRING:      return &SQL_GET_TYPE_INFO_values[22];
+    case FIELD_TYPE_NEWDECIMAL:  return &SQL_GET_TYPE_INFO_values[1];
     default:                     return &SQL_GET_TYPE_INFO_values[0];
   }
 }

I'm looking forward to DBD::mysql with correct mysql 5.0 types.

This is *still* not fixed in 3.0007! Can we *please* get a fix for this into the next version. I still stuck on mysql 4.1 because of this bug.

Confirmed this bug still exists in 3.0008.

Ray,
if you have time, try 3.0008_1 (dev). I see source code and there are some changes about new mysql column types.

I'm not able to build 3.0008_1 with the 5.0.26 libraries and headers. I get the following error when I run make:

% make
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm
cc -c  -I/usr/local/perl-5.8.6/lib/site_perl/5.8.6/darwin-2level/auto/DBI -I/usr/local/mysql/include -Os -arch ppc -fno-common -DDBD_MYSQL_INSERT_ID_IS_GOOD -g  -fno-common -DPERL_DARWIN -no-cpp-precomp -fno-strict-aliasing -pipe -I/usr/local/include -Os   -DVERSION=\"3.0008_1\" -DXS_VERSION=\"3.0008_1\"  "-I/usr/local/perl-5.8.6/lib/5.8.6/darwin-2level/CORE"   dbdimp.c
dbdimp.c: In function 'mysql_dr_connect':
dbdimp.c:1702: error: 'ulong' undeclared (first use in this function)
dbdimp.c:1702: error: (Each undeclared identifier is reported only once
dbdimp.c:1702: error: for each function it appears in.)
dbdimp.c:1702: error: parse error before numeric constant
make: *** [dbdimp.o] Error 1

I can build it successfully against 4.1.21, but I when I run that with a 5.0.26 server I still see this bug (and one of the tests fails as well).

The DECIMAL type problem is fixed in the DBD::mysql subversion tree, and will be in the next release (probably 4.01).

I confirm that this bug is fixed in DBD::mysql-4.001.

The problem of not being able to compile is http://bugs.mysql.com/bug.php?id=22227 and the patch from http://lists.mysql.com/commits/12385 fixes it. For some reason, though, it appears this patch never actually made it into 5.0.27 (at least for Mac OS X pkg installs)?