mysqli_fetch_field() mysqlnd & libmysql differences

13 years ago by Daniel Convissor — view source

unread

Hi Folks:

The output of mysqli_fetch_field() produces different results when
running against mysqlnd than it does against libmysql. I've mentioned
issues along these lines a couple times on the list in the midst of
other threads relating to MySQL handling, but nobody has really
addressed it directly.

The "length" property is what's tripping up my unit tests. I'm building
PHP 5.4 from svn for both tests. The only difference between them is
the with-mysqli declaration. Here is a table summarizing the situation:

type libmysql mysqlnd

TEXT 65535 196605
CHAR(2) 2 6

Is this intended behavior?

Below is a test script that produce those results.

Thanks,

--Dan

<?php
$db = new mysqli(null, $_ENV['MYSQL_TEST_USER'],
$_ENV['MYSQL_TEST_PASSWD'], $_ENV['MYSQL_TEST_DB'],
null, $_ENV['MYSQL_TEST_SOCKET']);

$result = $db->query("CREATE TEMPORARY TABLE phptest_fk (
txt TEXT NULL,
chr CHAR(2) default 'df' NOT NULL)");

if (!$result) {
echo "OOPS: $db->error\n";
exit(1);
}

$result = $db->query("INSERT INTO phptest_fk VALUES ('One', 'c1')");

$result = $db->query("SELECT * FROM phptest_fk");

$info = $result->fetch_field();
echo "$info->name $info->length\n";
$info = $result->fetch_field();
echo "$info->name $info->length\n";
?>

--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

13 years ago by Daniel Convissor — view source

unread

Hi Again:

type libmysql mysqlnd

CHAR(2) 2 6

Oh, by the way, I saw a CHAR(20) field come out as 60 in mysqlnd. So it
looks like the size is being multiplied by 3. Perhaps that's a clue for
debugging.

Thanks,

--Dan

13 years ago by Christopher Jones — view source

unread

Hi Folks:

The output of mysqli_fetch_field() produces different results when
running against mysqlnd than it does against libmysql. I've mentioned

Can you log a bug for this?

Chris

--
Email: christopher.jones@oracle.com
Tel: +1 650 506 8630
Blog: http://blogs.oracle.com/opal/

13 years ago by Daniel Convissor — view source

unread

Hi Chris:

Can you log a bug for this?

https://bugs.php.net/bug.php?id=60333

Thanks,

--Dan

13 years ago by johannes.schlueter@oracle.com — view source

unread

Hi,

The "length" property is what's tripping up my unit tests. I'm building
PHP 5.4 from svn for both tests. The only difference between them is
the with-mysqli declaration. Here is a table summarizing the situation:

type libmysql mysqlnd

TEXT 65535 196605
CHAR(2) 2 6

Is this intended behavior?

Your server seems to be configured for UTF-8 by default. In my tests the
behavior for both libraries (myslqnd & libmsql) is the same if you mind
the character set (use SET NAMES etc.)

The documentation tells

    unsigned long length
    
    The width of the field. This corresponds to the display length,
    in bytes.
    
    The server determines the length value before it generates the
    result set, so this is the minimum length required for a data
    type capable of holding the largest possible value from the
    result column, without knowing in advance the actual values that
    will be produced by the query for the result set.
    
    http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html

So it is working in bytes and has to hold all possible values.

I'll mark #60333 as bogus (expected behavior) once master works for
login ...

johannes

Johannes Schlüter, MySQL Engineering Connectors and Client Connectivity

ORACLE Deutschland B.V. & Co. KG, Riesstr. 25, D-80992 München
Registergericht: Amtsgericht München, HRA 95603 Geschäftsführer: Jürgen Kunz
Komplementärin: ORACLE Deutschland Verwaltung B.V.
Hertogswetering 163/167, 3543 AS Utrecht, Niederlande
Handelsregister der Handelskammer Midden-Niederlande, Nr. 30143697
Geschäftsführer: Alexander van der Ven, Astrid Kepper, Val Maher

13 years ago by Ulf Wendel — view source

unread

Am 19.01.2012 13:50, schrieb Johannes Schlüter:

The "length" property is what's tripping up my unit tests. I'm building
PHP 5.4 from svn for both tests. The only difference between them is
the with-mysqli declaration. Here is a table summarizing the situation:

type libmysql mysqlnd

TEXT 65535 196605
CHAR(2) 2 6

Is this intended behavior?

Your server seems to be configured for UTF-8 by default. In my tests the
behavior for both libraries (myslqnd& libmsql) is the same if you mind
the character set (use SET NAMES etc.)

ACK, likely a bogus report.

"MySQLnd always assumes the server default charset. This charset is sent
during connection hand-shake/authentication, which mysqlnd will use.

Libmysql uses the default charset set in the my.cnf or by an explicit
call to mysqli_options() prior to calling mysqli_real_connect(), but
after mysqli_init().", http://www.php.net/manual/en/mysqli.construct.php

Ulf

13 years ago by Daniel Convissor — view source

unread

Gentlemen:

Am 19.01.2012 13:50, schrieb Johannes Schlüter:

Your server seems to be configured for UTF-8 by default. In my tests the
behavior for both libraries (myslqnd& libmsql) is the same if you mind
the character set (use SET NAMES etc.)

Yes, my server is set to UTF-8 in my.cnf:
character-set-server = utf8

"MySQLnd always assumes the server default charset. This charset is
sent during connection hand-shake/authentication, which mysqlnd will
use.

Libmysql uses the default charset set in the my.cnf or by an
explicit call to mysqli_options() prior to calling
mysqli_real_connect(), but after mysqli_init().",
http://www.php.net/manual/en/mysqli.construct.php

From the documentation exceprt, above, the test code in
https://bugs.php.net/bug.php?id=60333 should be using the server's
default character set under both mysqlnd and libmysql. So shouldn't
they both come back with the same answer? Or am I misunderstanding
something?

Thanks,

--Dan

13 years ago by Ulf Wendel — view source

unread

Am 19.01.2012 20:27, schrieb Daniel Convissor:

Gentlemen:

Am 19.01.2012 13:50, schrieb Johannes Schlüter:

Your server seems to be configured for UTF-8 by default. In my tests the
behavior for both libraries (myslqnd& libmsql) is the same if you mind
the character set (use SET NAMES etc.)

Yes, my server is set to UTF-8 in my.cnf:
character-set-server = utf8

"MySQLnd always assumes the server default charset. This charset is
sent during connection hand-shake/authentication, which mysqlnd will
use.

Libmysql uses the default charset set in the my.cnf or by an
explicit call to mysqli_options() prior to calling
mysqli_real_connect(), but after mysqli_init().",
http://www.php.net/manual/en/mysqli.construct.php

From the documentation exceprt, above, the test code in
https://bugs.php.net/bug.php?id=60333 should be using the server's
default character set under both mysqlnd and libmysql. So shouldn't
they both come back with the same answer? Or am I misunderstanding
something?

mysqlnd simply does not read MySQL server config. It defaults to actual
connection of the server.

Ulf

13 years ago by Daniel Convissor — view source

unread

Hi:

Am 19.01.2012 13:50, schrieb Johannes Schlüter:

Your server seems to be configured for UTF-8 by default. In my tests the
behavior for both libraries (myslqnd& libmsql) is the same if you mind
the character set (use SET NAMES etc.)

Yes, my server is set to UTF-8 in my.cnf:
character-set-server = utf8

"MySQLnd always assumes the server default charset. This charset is
sent during connection hand-shake/authentication, which mysqlnd will
use.

Libmysql uses the default charset set in the my.cnf or by an
explicit call to mysqli_options() prior to calling
mysqli_real_connect(), but after mysqli_init().",
http://www.php.net/manual/en/mysqli.construct.php

From the documentation exceprt, above, the test code in
https://bugs.php.net/bug.php?id=60333 should be using the server's
default character set under both mysqlnd and libmysql. So shouldn't
they both come back with the same answer? Or am I misunderstanding
something?

Hmm. I added a call to $db->character_set_name() in the test script.
Under mysqlnd it returns "utf8" while under libmysql it returns
"latin1". So that explains the difference. But that leads me to
the next head scratcher: why is libmysql not using "the default charset
set in the my.cnf?"

Thanks,

--Dan

13 years ago by Daniel Convissor — view source

unread

Hi Johannes:

The documentation tells
    http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html
    
So it is working in bytes and has to hold all possible values.

That's how MySQL's internal API works. At the same time, PHP's users
expect PHP to behave as documented. http://php.net/mysqli_fetch_field says
length is "The width of the field, as specified in the table
definition."

Thanks,

--Dan

13 years ago by johannes.schlueter@oracle.com — view source

unread

Hi,

Hi Johannes:
The documentation tells
    http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html
    
So it is working in bytes and has to hold all possible values.
That's how MySQL's internal API works. At the same time, PHP's users
expect PHP to behave as documented. http://php.net/mysqli_fetch_field says
length is "The width of the field, as specified in the table
definition."

We have to live with the information the server gives us. The server
gives us the information in bytes. And frankly in PHP a string has no
encoding information, so in many cases that is a good answer. But
nonetheless the documentation should be improved/fixed.

johannes

13 years ago by Daniel Convissor — view source

unread

Hi Johannes:

We have to live with the information the server gives us. The server
gives us the information in bytes. And frankly in PHP a string has no
encoding information, so in many cases that is a good answer. But
nonetheless the documentation should be improved/fixed.

Further experimenting reveals that libmysql has the same behavior of
multiplying the length of string fields by 3 when the character set is
set to "utf8".

A related problem was found by the test script. libmysql is completely
ignoring the character set information in my.cnf. Plus, if one forces a
character set by calling options(SET NAMES utf8) before connecting,
character_set_name() still returns unexpected information.

Can you please take a look at this test script:
http://www.analysisandsolutions.com/php/libmysql.ignoring.character.set.php

Thanks,

--Dan

13 years ago by Daniel Convissor — view source

unread

Hi Johannes and Ulf:

Hi Johannes:

We have to live with the information the server gives us. The server
gives us the information in bytes. And frankly in PHP a string has no
encoding information, so in many cases that is a good answer. But
nonetheless the documentation should be improved/fixed.

Further experimenting reveals that libmysql has the same behavior of
multiplying the length of string fields by 3 when the character set is
set to "utf8".

A related problem was found by the test script. libmysql is completely
ignoring the character set information in my.cnf. Plus, if one forces a
character set by calling options(SET NAMES utf8) before connecting,
character_set_name() still returns unexpected information.

Can you please take a look at this test script:
http://www.analysisandsolutions.com/php/libmysql.ignoring.character.set.php

Can one of you please examine why libmysql is not acting as documented
and reply here in the near future?

Thanks,

--Dan

13 years ago by johannes.schlueter@oracle.com — view source

unread

A related problem was found by the test script. libmysql is completely
ignoring the character set information in my.cnf. Plus, if one forces a
character set by calling options(SET NAMES utf8) before connecting,
character_set_name() still returns unexpected information.

Can you please take a look at this test script:
http://www.analysisandsolutions.com/php/libmysql.ignoring.character.set.php

Can one of you please examine why libmysql is not acting as documented
and reply here in the near future?

In my tests it worked like I expect:

the initial charset is the one configured
changing the charset affects the behavior

Mind two things:

You said

/etc/my.cnf settings are (no other my.cnf files exist):
- default-character-set = utf8
- character-set-server = utf8

In which section of the my.cnf file? Both for the server, or for the
client?

SET NAMES changes the setting on the server. The client won't know
about this and still assume the old charset was used. Use
mysqli_set_charset().

In summary: charsets and encoding are a mess, especially if you have
many places to configure those. One small oversight and all is messed.

As reference:
http://dev.mysql.com/doc/refman/5.5/en/charset-configuration.html

johannes

Thanks,

--Dan

--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

13 years ago by Daniel Convissor — view source

unread

Hi Johannes:

You said

/etc/my.cnf settings are (no other my.cnf files exist):

default-character-set = utf8

character-set-server = utf8

In which section of the my.cnf file? Both for the server, or for the
client?

[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8

Thanks for looking into this,

--Dan

13 years ago by Simon Schick — view source

unread

Hi, Daniel

I'd also set the collation to utf8_unicode_ci. Here's a link to the full
diff of the my.cnf file I am using on my dev-server:
https://github.com/SimonSimCity/webserver-configuration/blob/master/mysql/patch.diff

Bye
Simon

2012/2/24 Daniel Convissor danielc@analysisandsolutions.com

Hi Johannes:

You said

/etc/my.cnf settings are (no other my.cnf files exist):

default-character-set = utf8

character-set-server = utf8

In which section of the my.cnf file? Both for the server, or for the
client?

[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8

Thanks for looking into this,

--Dan

--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

13 years ago by Daniel Macedo — view source

unread

Quick note:

If you're not storing Belarusian, Macedonian, Serbian, or Ukrainian or
have no need for proper sorting of the extra letters in these
languages NOR the support of expansions and ligatures; I would revert
to using utf8_general_ci, which is slightly faster but converts all
chars to their latin equivalents when sorting.

Just my 0,02 €

Daniel

12 years ago by johannes.schlueter@oracle.com — view source

unread

Hi,

Hi Johannes:
   unsigned long length

   The width of the field. This corresponds to the display length,
   in bytes.

   The server determines the length value before it generates the
   result set, so this is the minimum length required for a data
   type capable of holding the largest possible value from the
   result column, without knowing in advance the actual values that
   will be produced by the query for the result set.

   http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html
Pardon me for looping back around to this old discussion. I had a
moment to look at this in PEAR::DB the other day. A new perspective
came to mind...

It seems the field length in the C API is there to aid C programmers
with memory allocation.

The field length in PHP is there for PHP programmers to reverse engineer
database structures.

These are different purposes and the output should reflect such.

For example, the userland PHP field length could lead to someone dumping
a structure that has a VARCHAR(10). The exported metadata would say
VARCHAR(30). Then it gets imported and dumped again, and now we're up
to VARCHAR(90). Not fun.

This is all correct - but there isn't much we can do. The server provides this data. Trying to "fix" it on the client side will likely become a mess. If we create a mess I prefer doing it in user land (db2, mdb, doctrine, propel, ...) where "everybody" can debug/fix it.

Johannes

Thanks for your reconsideration,
--Dan

--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335