php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35536 mysql_field_type returns 'unknown' for DECIMAL columns in MySQL 5.0+
Submitted: 2005-12-03 22:33 UTC Modified: 2005-12-05 14:21 UTC
From: nevis2us at infoline dot su Assigned: tony2001 (profile)
Status: Closed Package: MySQL related
PHP Version: 4.4.1 OS: Linux Gentoo
Private report: No CVE-ID: None
 [2005-12-03 22:33 UTC] nevis2us at infoline dot su
Description:
------------
mysql_field_type returns 'unknown' for INT(11) columns
in MySQL 5.0.16 Same results with PHP 5.0.5
No problems with MySQL 4.1.15
Also works for decimal types in 5.0


Reproduce code:
---------------
Call mysql_field_type for an INT column in MySQL 5.0.16


Expected result:
----------------
Should return 'int'


Actual result:
--------------
'unknown'


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-12-03 23:08 UTC] tony2001@php.net
Works perfectly fine here.
Please check which mysqlclient libs your PHP uses: they has to be of the same version as the server.
 [2005-12-04 19:02 UTC] nevis2us at infoline dot su
Sorry for the first hasty report. It was misleading at best.
Here comes a simple test which hopefully may help to troubleshoot the actual problem I'm having with
PHP 4.4.1(gentoo dev-lang/php-4.4.1-r2 ebuild) and
MySQL 5.0.16(gentoo dev-db/mysql-5.0.16-r3 ebuild)

CREATE TABLE test.tmp (x INT, y DECIMAL, z REAL);
INSERT INTO test.tmp VALUES (1, 1, 1);

SELECT x, y, z FROM test.tmp;
-- column types as returned by mysql_field_type
-- for relevant columns in the resource set
--	int		unknown         real
--	1		1		1

SELECT SUM(x), SUM(y), SUM(z) FROM test.tmp;
--	unknown	        unknown	        real
--	1		1		1

SELECT ROUND(x), ROUND(y), ROUND(z) FROM test.tmp;
--	int		unknown	        real
--	1		1		1

SELECT AVG(x), AVG(y), AVG(z) FROM test.tmp;
--	unknown	unknown	real
--	1.0000	1.0000	1

SELECT MIN(x), MIN(y), MIN(z) FROM test.tmp;
--	int		unknown	        real
--	1		1		1

SELECT x/2, y/2, z/2 FROM test.tmp;
--	unknown	unknown	real
--	0.5000	0.5000	0.5

DROP TABLE test.tmp;
 [2005-12-04 21:39 UTC] tony2001@php.net
Round 2:
please check the version number of mysqlclient used with PHP.
 [2005-12-04 22:03 UTC] nevis2us at infoline dot su
Actually the first thing I did.
The Client API version is 5.0.16 (mysql_get_client_info)
The server is 5.0.16-log (mysql_get_server_info)
 [2005-12-04 22:14 UTC] tony2001@php.net
Still can't reproduce it and your SQL code doesn't add any value. 
There is a known incompatibility between MySQL 4.1 client and 5.0 server, so please report that to MySQL AB.
 [2005-12-04 22:36 UTC] nevis2us at infoline dot su
I've read the upgrading guide. Thanks.
The problem is I've tried it on fresh install of 5.0.16
after rebuilding related PHP modules.
So this is not client incompatibility issue. Unfortunately.
I'm using an apache 2 module. Does it make any difference?
 [2005-12-04 22:42 UTC] tony2001@php.net
I can't think of a way that makes the same code work for me and makes it fail for you, and I'm sure your PHP receives wrong data from the client library, so it's definitely NOT PHP issue.
 [2005-12-05 00:53 UTC] nevis2us at infoline dot su
MySQL 5.0 Reference Manual ->
2.10.2. Upgrading from Version 4.1 to 5.0 ->
C API Changes:
Incompatible change:
...
This error occurs because the 4.1 client libraries do not support the new MYSQL_TYPE_NEWDECIMAL  type value added in 5.0
...

I do not see you handle either MYSQL_TYPE_NEWDECIMAL or FIELD_TYPE_NEWDECIMAL anywhere in php-4.1.1 source.
mysqli extension in php-5.0.5 seems to be aware of this new type. But wasn't it obvious the problem reported is about mysql extension? Did you even bother to try?
 [2005-12-05 10:03 UTC] tony2001@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc.

If possible, make the script source available online and provide
an URL to it here. Try to avoid embedding huge scripts into the report.


 [2005-12-05 13:35 UTC] nevis2us at infoline dot su
The following patch fixes NEWDECIMAL bug in PHP 4.4.1

--- ext/mysql/libmysql/mysql_com.h	2003-07-28 11:28:55.000000000 +0400
+++ ext/mysql/libmysql/mysql_com.h	2005-12-05 14:12:08.000000000 +0300
@@ -125,6 +125,7 @@
 			FIELD_TYPE_DATE,   FIELD_TYPE_TIME,
 			FIELD_TYPE_DATETIME, FIELD_TYPE_YEAR,
 			FIELD_TYPE_NEWDATE,
+			FIELD_TYPE_NEWDECIMAL=246,
 			FIELD_TYPE_ENUM=247,
 			FIELD_TYPE_SET=248,
			FIELD_TYPE_TINY_BLOB=249,

--- ext/mysql/php_mysql.c	2005-04-08 02:23:01.000000000 +0400
+++ ext/mysql/php_mysql.c	2005-12-05 14:12:56.000000000 +0300
@@ -2088,6 +2088,7 @@
 		case FIELD_TYPE_FLOAT:
 		case FIELD_TYPE_DOUBLE:
 		case FIELD_TYPE_DECIMAL:
+		case FIELD_TYPE_NEWDECIMAL:
 			return "real";
 			break;
 		case FIELD_TYPE_TIMESTAMP:
 [2005-12-05 13:43 UTC] tony2001@php.net
Next time please give the clear reproduce case instead of just saying "do so and so".

There is also SET, ENUM and NEWDATE that aren't handled in this switch.
Btw, PDO_MYSQL also needs these types.
 [2005-12-05 14:12 UTC] nevis2us at infoline dot su
The clear reproduce case would take too much php programming
and wasn't worth the effort. Thanks for your help Tony.
 [2005-12-05 14:21 UTC] tony2001@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.


 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 11:01:28 2024 UTC