MySQL Bugs: #14343: mysqli php-extension

Bug #14343	mysqli php-extension
Submitted:	26 Oct 2005 18:25	Modified:	10 Dec 2005 11:45
Reporter:	Vladimir Struchkov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql 4.1.14, php 5.0.4	OS:	Linux (Linux RH4)
Assigned to:	Andrey Hristov	CPU Architecture:	Any

Description:
Now I'm writing small blog search system. To get user information I do some query to database:

'select c.id,c.type,c.name,c.title ctitle,c.friends_spidered,c.content_spidered,c.priority,c.blocked,c.do_index,c.cache,c.preview,
c.title,c.content_updated,c.image_url,c.base_url,c.spider_now,min(published) as min, max(published) as max, count(*) as cnt from channel as c left join posting as p on c.id=p.channel_id where c.name='user_name' group by c.id'

In mysql console client I've got result:
*************************** 1. row ***************************
              id: 6267
            type: user
            name: user_name
          ctitle: Title
friends_spidered: 2005-10-18 22:00:18
content_spidered: 2005-10-26 00:35:35
        priority: 80
         blocked: 0
        do_index: 1
           cache: 1
         preview: 1
           title: Title
 content_updated: 2005-10-26 00:34:41
       image_url: http://blog/userpic/12345/
        base_url: http://blog/users/user_name/
      spider_now: 0
             min: 2005-03-02 10:14:29
             max: 2005-10-26 00:34:41
             cnt: 266

That's right.

I write test php script

<?php

$mysqli=&new mysqli('database','user','pass');
$result=$mysqli->query('select c.id,c.type,c.name,c.title ctitle,c.friends_spidered,c.content_spidered,c.priority,c.blocked,c.do_index,c.cache,c.preview,c.title,c.content_updated,c.image_url,c.base_url,c.spider_now,min(published) as min, max(published) as max, count(*) as cnt from channel as c left join posting as p on c.id=p.channel_id where c.name=\'user_name\' group by c.id');

$row=$result->fetch_assoc();
print_r($row);

$result->close();

?>

I've got right result once more.

After mysql 4.1 I thinked about prepare statement...It must be good...

I use console mysql client to test my query

mysql> prepare stmt from 'select c.id,c.type,c.name,c.title ctitle,c.friends_spidered,c.content_spidered,c.priority,c.blocked,c.do_index,c.cache,c.preview,c.title,c.content_updated,c.image_url,c.base_url,c.spider_now,min(published) as min, max(published) as max, count(*) as cnt from channel as c left join posting as p on c.id=p.channel_id where c.name=? group by c.id';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @name='user_name';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @name;

Oh...yeah...Result is right.

Next I write php script using prepare statement....

<?php

$mysqli=&new mysqli('database','user','pass');

$name='username';

$stmt=&$mysqli->prepare('select c.id,c.type,c.name,c.title ctitle,c.friends_spidered,c.content_spidered,c.priority,c.blocked,c.do_index,c.cache,c.preview,c.title,c.content_updated,c.image_url,c.base_url,c.spider_now,min(published) as min, max(published) as max, count(*) as cnt from channel as c left join posting as p on c.id=p.channel_id where c.name=? group by c.id');
$stmt->bind_param('s',$name);
$stmt->execute();
$stmt->bind_result($id,$type,$name,$ctitle,$friends_spidered,$content_spidered,$priority,$blocked,$do_index,$cache,$preview,$title,$content_updated,$image_url,$base_url,$spider_now,$min,$max,$cnt);
$stmt->fetch();

print_r(array($id,$type,$name,$ctitle,$friends_spidered,$content_spidered,$priority,$blocked,$do_index,$cache,$preview,$title,$content_updated,$image_url
,$base_url,$spider_now,$min,$max,$cnt));

?>

I see:

Array
(
    [0] => 6267
    [1] => user
    [2] => username
    [3] => Title
    [4] => 2005-10-18 22:00:18
    [5] => 2005-10-26 00:35:35
    [6] => 7957695013524078672
    [7] => 7019260658101256192
    [8] => 4332498068852178945
    [9] => 8243105133760610305
    [10] => 7151266924620414977
    [11] => Title
    [12] => 2005-10-26 00:34:41
    [13] => http://blog/userpic/12345/
    [14] => http://blog/users/user_name/
    [15] => 738148811955765248
    [16] => 2005-03-02 10:14:29
    [17] => 2005-10-26 00:34:41
    [18] => 266
)

Oh my God....This number is like Monty's  bank accounts :-)

How to repeat:
every time

I forgot to write :
Linux web01.ljseek.com 2.6.13-1.1526_FC4smp #1 SMP Wed Sep 28 19:28:24 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux

Thank you for a problem report. Please, send the results of SHOW CREATE TABLE for tables channel and posting.

I changed severity, because S1 is about server crash, not strange results in php script only.

I send info.txt in files section.

Thank you for the additional information. 

Can you, please, send the dumps of these tables' data (you may upload the dump using the File tab, as private, if you want), or, at least, the content of rows with id=6267 from both tables.

Have you tried to use PHP 5.0.5, by the way? Have you rebuilt PHP with mysql 4.1.14 libraries?

My php is built with 4.14 library now.

I put my dump into ftp.mysql.com (/pub/mysql/upload), becase it's about 230Kb.
Filename bug14343.dump.sql.bz2  (or dump.sql.bz2 if somebody wasn't delete it, accidentally I give to file idiot name, but after that put file with right name)

Vladimir,

Would you, please, upload the dump to our ftp server once more. I was busy working and had not downloaded it immediately (my fault), and it was deleted somehow before I got a chance to work with it.

Ok. filename /pub/mysql/upload/bug-14343.dump.sql.bz2
It's strange...but I think that old file (name bug14343.dump.sql.bz2) is existing now.

Still do not see it... Please, send it to me by email: valeriy at mysql dot com

Hello,
is it possible to modify your PHP sources?
If possible could you modify ext/mysqli/mysqli_api.c
around like 298 (in my sources) there is the following:
stmt->result.buf[ofs].val = (char *)emalloc(sizeof(long));
could you please change it to :
stmt->result.buf[ofs].val = (char *)emalloc(sizeof(int));

I have doubts that this is the problem because 8bytes (long on 64bit) are allocated but for libmysql a tinyint is 4 bytes so the other 4 bytes are untouched and contain  garbage but then intepreted at fetch.

Thank you!

I install php and php-extension from rpm-files and can't modify source

Valdimir,

I've got your file by email. Let me check, if it is a 64-bit problem only...

Hi,
5.0.4 (PHP) was released 31-Mar-2005, 5.0.5 -> 05-Sep-2005. In April there was a change in the sources of php-mysqli which I think fixes the problem you report.
The following script tests the existance of the bug:

php -r '$c= new mysqli("localhost","root","secret");$c->query("DROP TABLE IF EXIST test.tint_test;");$c->query("CREATE TABLE test.tint_test (a tinyint(3));");$c->query("INSERT INTO test.tint_test VALUES (123)");$s=$c->stmt_init();$s->prepare("select a from test.tint_test");$s->execute();$s->bind_result($v);$s->fetch();var_dump($v);$c->query("DROP TABLE EXIST test.tint_test;");'

lsmy005:/usr/local/src/php-5.0.4 # sapi/cli/php -r '$c= new mysqli("localhost","root","mysql50");$c->query("DROP TABLE IF EXIST test.tint_test;");$c->query("CREATE TABLE test.tint_test (a tinyint(3));");$c->query("INSERT INTO test.tint_test VALUES (123)");$s=$c->stmt_init();$s->prepare("select a from test.tint_test");$s->execute();$s->bind_result($v);$s->fetch();var_dump($v);$c->query("DROP TABLE EXIST test.tint_test;");'
int(4357062704804921467)

lsmy005:/usr/local/src/php5-200511091130 # sapi/cli/php -r '$c= new mysqli("localhost","root","mysql50");$c->query("DROP TABLE IF EXIST test.tint_test;");$c->query("CREATE TABLE test.tint_test (a tinyint(3));");$c->query("INSERT INTO test.tint_test VALUES (123)");$s=$c->stmt_init();$s->prepare("select a from test.tint_test");$s->execute();$s->bind_result($v);$s->fetch();var_dump($v);$c->query("DROP TABLE EXIST test.tint_test;");'
int(123)

So, using PHP 5.0.5 or later should be ok.

Thank you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/