Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:17209
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Message-ID: <42D053D1.4060002@hristov.com>
Date: Sun, 10 Jul 2005 00:46:41 +0200
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8b2) Gecko/20050219
MIME-Version: 1.0
To: internals@lists.php.net
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Deficiency in pdo_mysql driver
From: php@hristov.com (Andrey Hristov)

  Hi all,
I decided to take a look at the MySQL PDO driver just to help
with 2 more eyes looking at the code. I spotted one problem which
already hit mysqli (bug #32013). The problem is that when binding
result sets libmysql use optimistic approach and gives back not
the maximal length of the data in a column (CHAR/BLOB/TEXT) of the
result set but the maximal length of the column (xxx/65535/65535).
mysqli used to allocate this maximal length which can be a problem
in the cases when the BLOB is for example MEDIUMBLOB or larger (respectively
MEDIUMTEXT) because in this case at least 16MB has to be allocated.
The solve the problem the PDO MySQL driver has to inspect what is the
type of the column and appropriately let libmysql to calculate the
maximal length of a column of the result set. Of course this imposes
a performance deficiency but what is better - worse performance or
memory hit? Not to mention that many shared hostings use memory limit
of 8MB which will be hit by just one query against a MEDIUMTEXT
column.

The idea is to call mysql_stmt_attr_set() with param
STMT_ATTR_UPDATE_MAX_LENGTH and 1 as third param so libmysql update
the metadata and return the maximal length of the column in the
result set. The order has to be :
mysql_stmt_prepare()
mysql_stmt_execute()
mysql_stmt_attr_set()
mysql_stmt_result_metadata()
and now max_length is ok. Of course this is only applicable when the
user wants a buffered result set (I would say that he/she will be
forced to use buffered sets if they use wide columns).


-=-=
Additional information not related to PDO MySQL:
With mysqli the user has to use the following sequence or he/she will
hit the wall:
mysqli_stmt_prepare()
mysqli_stmt_execute()
mysqli_stmt_store_result()
mysqli_stmt_bind_result();
mysqli_stmt_fetch()...

(binding should be done after mysqli_stmt_store_result() has been called
because the latter updates max_legnth in the MYSQL_FIELD structures,
otherwise if bind_result() has been called before store_result() then
mysqli allocates the "length" from the MYSQL_FIELD structure which is
the width of the column).

-=-=

Here is a simple demo program in C (not error checking to make it short):
#include <stdio.h>
#include "mysql.h"

#define SELECT123 "SELECT * FROM t123"

int main(int argc, char**argv)
{
   MYSQL_RES *meta_res;
   MYSQL_STMT *prep_stmt = NULL;
   MYSQL_BIND *bind;
   unsigned int i, col_num;


   MYSQL *rconn;
   MYSQL_FIELD *fields;
   my_bool tmp=1;

   mysql_library_init(argc, argv, NULL);
   rconn = mysql_init(NULL);
   rconn = mysql_real_connect(rconn, "127.0.0.1", "root",
         "secret", "test", 0, NULL, 0);

   prep_stmt= mysql_stmt_init(rconn);
   mysql_stmt_prepare(prep_stmt, SELECT123,
	strlen(SELECT123));
   mysql_stmt_execute(prep_stmt);

   mysql_stmt_attr_set(prep_stmt,
	STMT_ATTR_UPDATE_MAX_LENGTH, &tmp);

   mysql_stmt_store_result(prep_stmt);

   meta_res= mysql_stmt_result_metadata(prep_stmt);

   col_num= mysql_num_fields(meta_res);
   printf("\nNumber of columns in the result: %d\n", col_num);
   fields= mysql_fetch_fields(meta_res);
   for (i=0; i < col_num; i++) {
     printf("LENGTH=%d  MAX_LEN=%d\n",
         fields[i].length, fields[i].max_length);
   }
   mysql_free_result(meta_res);
}


Regards,
Andrey

P.S.
1)I haven't cooked a patch for pdo_mysql/mysql_statement.c because
I don't have pdo compiled locally (lame excuse :) ).
2)The problem was fixed in mysqli with the following patch:
http://marc.theaimsgroup.com/?l=php-cvs&m=111462445903347&w=2