Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi, On Sep 6, 2012, at 1:24, Daniel Convissor wrote: > Hi Johannes: > > On Thu, Jan 19, 2012 at 01:50:47PM +0100, Johannes Schlüter wrote: >> >>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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi Johannes: On Thu, Jan 19, 2012 at 01:50:47PM +0100, Johannes Schlüter wrote: > > 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. 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
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 > Hi Johannes: > > > 1) 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 > > -- > PHP Internals - PHP Runtime Development Mailing List > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi Johannes: > 1) 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
On Fri, 2012-02-17 at 08:38 -0500, Daniel Convissor wrote: > > 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: 1) 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? 2) 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 > -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi Johannes and Ulf: On Mon, Jan 23, 2012 at 09:56:00PM -0500, Daniel Convissor wrote: > 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 -- 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
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 -- 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi, On Sat, 2012-01-21 at 08:31 -0500, Daniel Convissor wrote: > 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
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 -- 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi: On Thu, Jan 19, 2012 at 02:27:05PM -0500, Daniel Convissor wrote: > On Thu, Jan 19, 2012 at 02:09:12PM +0100, Ulf Wendel wrote: > > 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 -- 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Am 19.01.2012 20:27, schrieb Daniel Convissor: Gentlemen: On Thu, Jan 19, 2012 at 02:09:12PM +0100, Ulf Wendel wrote: 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Gentlemen: On Thu, Jan 19, 2012 at 02:09:12PM +0100, Ulf Wendel wrote: > 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 -- 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Am 19.01.2012 13:50, schrieb Johannes Schlüter:
On Fri, 2011-11-18 at 16:06 -0500, Daniel Convissor wrote:
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) 26
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
--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi, On Fri, 2011-11-18 at 16:06 -0500, Daniel Convissor wrote: > 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) 26 > > 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi Chris: > Can you log a bug for this? https://bugs.php.net/bug.php?id=60333 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
On 11/18/2011 01:06 PM, Daniel Convissor wrote: 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.jo...@oracle.com Tel: +1 650 506 8630 Blog: http://blogs.oracle.com/opal/ -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
Hi Again: > type libmysql mysqlnd > --- > CHAR(2) 26 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 -- 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DEV] mysqli_fetch_field() mysqlnd & libmysql differences
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) 26
Is this intended behavior?
Below is a test script that produce those results.
Thanks,
--Dan
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
--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
