# mysqli_fetch_field() mysqlnd & libmysql differences - Thread: https://externals.io/message/56415 - Original archive: https://news-web.php.net/php.internals/56415 - Started by Daniel Convissor on 2011-11-18T21:06:19+00:00 - Messages: 18 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #56415 — Daniel Convissor — 2011-11-18T21:06:19+00:00 URL: https://externals.io/message/56415#56415 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 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 --- ## #56417 — Daniel Convissor — 2011-11-19T00:52:54+00:00 URL: https://externals.io/message/56415#56417 · In reply to: #56415 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 -- 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 --- ## #56418 — Christopher Jones — 2011-11-19T01:09:46+00:00 URL: https://externals.io/message/56415#56418 · In reply to: #56415 > 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/ --- ## #56421 — Daniel Convissor — 2011-11-19T02:41:34+00:00 URL: https://externals.io/message/56415#56421 · In reply to: #56418 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 --- ## #57425 — johannes.schlueter@oracle.com — 2012-01-19T12:50:47+00:00 URL: https://externals.io/message/56415#57425 · In reply to: #56415 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 --- ## #57426 — Ulf Wendel — 2012-01-19T13:09:12+00:00 URL: https://externals.io/message/56415#57426 · In reply to: #57425 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.) 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 --- ## #57430 — Daniel Convissor — 2012-01-19T19:27:05+00:00 URL: https://externals.io/message/56415#57430 · In reply to: #57426 Gentlemen: > Am 19.01.2012 13:50, schrieb Johannes Schlüter: > > […] 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 --- ## #57432 — Ulf Wendel — 2012-01-19T19:40:52+00:00 URL: https://externals.io/message/56415#57432 · In reply to: #57430 Am 19.01.2012 20:27, schrieb Daniel Convissor: > Gentlemen: > > > > […] > > Yes, my server is set to UTF-8 in my.cnf: > character-set-server = utf8 > > > > […] > > > […] > 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 --- ## #57433 — Daniel Convissor — 2012-01-19T20:38:58+00:00 URL: https://externals.io/message/56415#57433 · In reply to: #57430 Hi: > > > […] > > Yes, my server is set to UTF-8 in my.cnf: > character-set-server = utf8 > > > > […] > > > […] > 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 --- ## #57465 — Daniel Convissor — 2012-01-21T13:31:07+00:00 URL: https://externals.io/message/56415#57465 · In reply to: #57425 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 --- ## #57479 — johannes.schlueter@oracle.com — 2012-01-23T09:40:04+00:00 URL: https://externals.io/message/56415#57479 · In reply to: #57465 Hi, > Hi Johannes: > > > […] > > 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 --- ## #57486 — Daniel Convissor — 2012-01-24T02:56:00+00:00 URL: https://externals.io/message/56415#57486 · In reply to: #57479 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 --- ## #57892 — Daniel Convissor — 2012-02-17T13:38:11+00:00 URL: https://externals.io/message/56415#57892 · In reply to: #57486 Hi Johannes and Ulf: > Hi Johannes: > > > […] > > 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 --- ## #57913 — johannes.schlueter@oracle.com — 2012-02-21T11:37:34+00:00 URL: https://externals.io/message/56415#57913 · In reply to: #57892 > > […] > > 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 --- ## #57998 — Daniel Convissor — 2012-02-24T14:54:46+00:00 URL: https://externals.io/message/56415#57998 · In reply to: #57913 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 --- ## #58004 — Simon Schick — 2012-02-24T18:25:35+00:00 URL: https://externals.io/message/56415#58004 · In reply to: #57998 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: > > > […] > > [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 --- ## #58006 — Daniel Macedo — 2012-02-24T18:38:35+00:00 URL: https://externals.io/message/56415#58006 · In reply to: #58004 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 --- ## #62846 — johannes.schlueter@oracle.com — 2012-09-05T23:31:13+00:00 URL: https://externals.io/message/56415#62846 Hi, > Hi Johannes: > > > > […] > > 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