# mysqli tests breaking - Thread: https://externals.io/message/55091 - Original archive: https://news-web.php.net/php.internals/55091 - Started by Stas Malyshev on 2011-09-02T17:19:35+00:00 - Messages: 23 - 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 --- ## #55091 — Stas Malyshev — 2011-09-02T17:19:35+00:00 URL: https://externals.io/message/55091#55091 Hi! The situation with mysql tests has improved lately, but I still have the following tests breaking on my system. It would be nice if some of the maintainers would address it as I'm sure it happens to others too. new mysqli() [ext/mysqli/tests/mysqli_connect_oo_warnings.phpt] This one just times out trying to look up the invalid DNS name. This is a recent breakage, didn't happen before. EXPLAIN - metadata [ext/mysqli/tests/mysqli_explain_metadata.phpt] The reason is that plain SQL and prepared SQL return different data - catalog field sometimes is "def", sometimes `NULL` in MYSQL_FIELD structure returned by mysql_fetch_field_direct(). It may be mysql bug in which case test should be SKIPed for versions that have this bug. API vs. SQL LAST_INSERT_ID() [ext/mysqli/tests/mysqli_last_insert_id.phpt] The reason is that this test relies on LAST_INSERT_ID() being reset on SELECT. I have not observed such behavior neither via PHP not talking to Mysql server directly from CLI interface, so I have no idea why this test assumes such behavior. `mysqli_stmt_num_rows()` [ext/mysqli/tests/mysqli_stmt_num_rows.phpt] This test for some reason assumes that while `mysqli_stmt_num_rows()` would return 0 when applied to unbuffered statement it would start returning number of rows when the result processing is done. This is not the case for my ststem - it always returns 0, and this seems to be consistent with mysql documentation. This change was introduced by: r308394 | andrey | 2011-02-16 08:36:33 -0800 (Wed, 16 Feb 2011) | 3 lines fixed a problem in mysqlnd. 0 was always as num_rows returned for unbuffered sets (text protocol and PS). My environment is Mac OS X, libmysql version 5.1.46 (yes, I know it's old, but that's what is out there in production for many, so we have to support it). -- Stanislav Malyshev, Software Architect SugarCRM: http://www.sugarcrm.com/ (408)454-6900 ext. 227 --- ## #55092 — Ulf Wendel — 2011-09-02T18:03:58+00:00 URL: https://externals.io/message/55091#55092 · In reply to: #55091 Am 02.09.2011 19:19, schrieb Stas Malyshev: > My environment is Mac OS X, libmysql version 5.1.46 (yes, I know it's > old, but that's what is out there in production for many, so we have to > support it). Stas, please, always test against the latest and greatest. Otherwise you'll be testing against libmysql versions that are not going to see any updates. Ulf --- ## #55093 — Anthony Ferrara — 2011-09-02T18:15:56+00:00 URL: https://externals.io/message/55091#55093 · In reply to: #55092 > always test against the latest and greatest. Otherwise you'll be > testing against libmysql versions that are not going to see any updates. I would disagree with that statement. Tests should be run against all expected versions of the library. So if 5.1.46 is supported, it should pass the tests. Otherwise how will you know if you've broken a supported environment? > Am 02.09.2011 19:19, schrieb Stas Malyshev: > > […] > > Stas, > > please, always test against the latest and greatest. Otherwise you'll be > testing against libmysql versions that are not going to see any updates. > > Ulf --- ## #55094 — Stas Malyshev — 2011-09-02T18:27:15+00:00 URL: https://externals.io/message/55091#55094 · In reply to: #55092 Hi! > please, always test against the latest and greatest. Otherwise you'll be > testing against libmysql versions that are not going to see any updates. 5.1 is still a supported version of Mysql. It is run on many production servers, so I think we must support it - it's no use to run tests on the bleeding edge versions while production runs different ones. And production DB servers are upgraded very rarely, especially since 5.1 is still supported and good enough for many users. Having tests that run only on one version is next to useless, since that means I'd never know if the code actually works in production - which is the point of having the tests! If some specific test tests some fix that available only since specific version - the test must be marked as such and be skipped or disable the missing function in version that is too old. I'm not saying we should support all Mysql versions since the start of the project - but I do not see how 5.1 can be excluded. -- Stanislav Malyshev, Software Architect SugarCRM: http://www.sugarcrm.com/ (408)454-6900 ext. 227 --- ## #55095 — Daniel Convissor — 2011-09-02T19:10:05+00:00 URL: https://externals.io/message/55091#55095 · In reply to: #55091 Hi: While I mentioned the following in the "5.4 beta & tests" thread, I'll semi-re-post this here so it doesn't get lost in a thread not specifically about mysql tests. libmysql: http://www.analysisandsolutions.com/php/mysqli.test.failures.libmysql.tbz mysqlnd: http://www.analysisandsolutions.com/php/mysqli.test.failures.tbz mysqld Ver 5.1.41-3ubuntu12.10 for debian-linux-gnu on x86_64 ((Ubuntu)) 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 --- ## #55096 — Ulf Wendel — 2011-09-02T19:11:08+00:00 URL: https://externals.io/message/55091#55096 · In reply to: #55094 Am 02.09.2011 20:27, schrieb Stas Malyshev: > > > […] > > 5.1 is still a supported version of Mysql. It is run on many production > servers, so I think we must support it - it's no use to run tests on the Stas, I'm not arguing about test portability or the like. All I'm after is the counterpart to the checkbox entry "Try a snapshot (PHP x.y) (Feedback)", as found at bugs.php.net :-). This is no more than a friendly request to check against latest and greatest to avoid hitting bugs already fixed in libmysql. Latest GA is 5.1.58, if I'm not mistaken, http://dev.mysql.com/downloads/mysql/5.1.html . You are testing again 5.1.4x. Generally speaking, libmysql 5.1.4x won't see updates, just like PHP 5.2 won't. Regarding test portability ... you probably can imagine how annoyed I have been when orginally writing tests and hitting non portable stuff, deperately trying to actually test for something. Ulf --- ## #55103 — Stas Malyshev — 2011-09-02T20:07:54+00:00 URL: https://externals.io/message/55091#55103 · In reply to: #55096 Hi! > This is no more than a friendly request to check against latest and > greatest to avoid hitting bugs already fixed in libmysql. Latest GA is > 5.1.58, if I'm not mistaken, > http://dev.mysql.com/downloads/mysql/5.1.html . You are testing again > 5.1.4x. Generally speaking, libmysql 5.1.4x won't see updates, just like > PHP 5.2 won't. While it is a good advice in general, I seriously doubt the semantics of mysql_num_rows() or last_insert_id() SQL statement changed between 5.1.4x and 5.1.5x. That would be serious and profound change totally inaproppriate for such version and my quick check of the changelogs suggests nothing of the sort. So I'm afraid there's little hope that the failures I am observing are caused by not using 5.1.5x. > Regarding test portability ... you probably can imagine how annoyed I > have been when orginally writing tests and hitting non portable stuff, > deperately trying to actually test for something. I agree it is annoying, but we have to sort it out nevertheless. If we have tests that work only on some specific versions and break on others, we need either to identify breakage point and have them specifically pointed out (such as: "This test won't work on versions before X.Y.Z because mysql_foo_bar() function returns wrong value in foobar structure") and if possible, skipped out with informative message. Otherwise for any user that doesn't run exactly the same library version as test developer ran, the test suite doesn't work - he doesn't have the means to identify if something is wrong or not. -- Stanislav Malyshev, Software Architect SugarCRM: http://www.sugarcrm.com/ (408)454-6900 ext. 227 --- ## #55107 — Ulf Wendel — 2011-09-02T20:40:40+00:00 URL: https://externals.io/message/55091#55107 · In reply to: #55103 Am 02.09.2011 22:07, schrieb Stas Malyshev: > Hi! > > > > […] > > While it is a good advice in general, I seriously doubt the semantics of > mysql_num_rows() or last_insert_id() SQL statement changed between > 5.1.4x and 5.1.5x. That would be serious and profound change totally > inaproppriate for such version and my quick check of the changelogs > suggests nothing of the sort. So I'm afraid there's little hope that the > failures I am observing are caused by not using 5.1.5x. Stas, aren't we going a little too far here in our discussion? We agree on the basic points. I'm not saying that your issues shall be ignored nor am I saying tests shall not be made portable. (But I won't spend my weekend looking into them right now ;-); I have intentionally not replied to that part of your mail.) Being someone who has been forced to write many mysqli tests in the past I lost trust in changelogs and the like. Take https://bugs.php.net/bug.php?id=55001 from today. Shortly after my bug comment, a C variant of it has been checked on four platforms against six libmysql versions to dig it down for a mysql.com bug report. My 5.1.4x is not affected, someone else 5.1.4x is. I can't reproduce with >5.1.5, others can. Fun. > > […] > > I agree it is annoying, but we have to sort it out nevertheless. If we > have tests that work only on some specific versions and break on others, > we need either to identify breakage point and have them specifically > pointed out (such as: "This test won't work on versions before X.Y.Z > because mysql_foo_bar() function returns wrong value in foobar > structure") and if possible, skipped out with informative message. Argument taken. However, I'm afraid, we'll always see such things happen if any PHP extension depends on any external entity - be it a library or a database server. My thinking is that any such test issue should be handled as a day-to-day task. A task not only perfomed the week before a beta release. Any user should do the test run any time. You'd probably sign that. Need I say more, did I get your arguments? Ulf --- ## #55166 — Stas Malyshev — 2011-09-04T04:35:51+00:00 URL: https://externals.io/message/55091#55166 · In reply to: #55091 Hi! I've tried with latest mysql library and as I suspected, most of the issues still stay. The ones I'm most worried about are: 1. `mysqli_stmt_num_rows()` is expected to return number of rows after all rows were fetched while returning 0 before that. libmysql definitely doesn't do that, in full accordance with mysql docs which state row numbers can't be had for unbuffered queries. If we do want this new non-portable semantics in mysqlnd, we should have it in separate test, clearly marked as such, even though I'm not convinced having such semantics is a good idea at all (would lead to applications working with mysqlnd and not with libmysql - invitation for trouble). 2. last_insert_id semantics change. The test expects select queries to reset last insert it to 0, which does not happen with libmysql. Maybe it happens on later versions of mysql server, I do not know - but we can't have a test the is bound to specific server version, at least not without a check, and even better - separating it from portable tests. If it's specific to mysqlnd it's even worse - it means that some code that assumes libmysql behavior will be broken, and as last_insert_id() is a very frequently used function I think it's very dangerous. Also, new issue added with recent libmysql - newer builds of libmysql do not declare DBUG_OFF at all in my_config.h. See for example http://bugs.mysql.com/bug.php?id=60872. So we should take the absence of DBUG_OFF as a sign that debug is enabled. I would recommend checking if DEBUG_ON is defined and enabling debug only then. -- Stanislav Malyshev, Software Architect SugarCRM: http://www.sugarcrm.com/ (408)454-6900 ext. 227 --- ## #55210 — Ulf Wendel — 2011-09-05T09:29:58+00:00 URL: https://externals.io/message/55091#55210 · In reply to: #55166 Am 04.09.2011 06:35, schrieb Stas Malyshev: > The ones I'm most worried about are: > > 1. `mysqli_stmt_num_rows()` is expected to return number of rows after all > rows were fetched while returning 0 before that. libmysql definitely Yes, here the test assumes a marginally different behavior. The test needs an update. No, there is no BC break. > doesn't do that, in full accordance with mysql docs which state row > numbers can't be had for unbuffered queries. > If we do want this new non-portable semantics in mysqlnd, we should have > it in separate test, clearly marked as such, even though I'm not > convinced having such semantics is a good idea at all (would lead to > applications working with mysqlnd and not with libmysql - invitation for > trouble). Your description of the difference is wrong. You conclusion "invitation for trouble" must be a joke. a) The difference Pseudo-code: /* identical behavior */ mysqli_stmt_prepare(stmt, "SELECT ..."); mysqli_stmt_execute(stmt) do { assert(0 == mysqli_stmt_num_rows(stmt)); } while (mysqli_stmt_fetch(stmt); /* difference now */ printf("number of rows fetched: %d\n", `mysqli_stmt_num_rows()`); Output: libmysql: 0 rows fetched mysqlnd: rows fetched Difference: mysqlnd lifts a silly libmysql limitation. b) Practical relevance bugs.php.net - 0 related reports https://bugs.php.net/search.php?search_for=mysqli_stmt_num_rowsboolean=0&limit=All&order_by=id&direction=DESC&cmd=display&status=All&bug_type=All&php_os=&phpver=&cve_id=&assign=&author_email=&bug_age=0&bug_updated=0 code.google.com - nobody using the function ?! http://code.google.com/intl/en-EN/query/#q=mysqli_stmt_num_rows Code that relies on undefined behavior: num_rows = 0; while (mysql_stmt_fetch(stmt)) { num_rows++; } /* Bogus code - does not follow docs */ if (num_rows && 0 !== mysqli_stmt_num_rows(stmt) printf("Damn, API returns a meaninful value""); else printf("Jippie, MySQL folks lifted a limitation"); Worst that could happen is that someone relies on stmt_num_rows() @ mysqlnd here. Relying on any specific behavior is bogus. We are talking about C libmysql (libmysql vs. mysqlnd) differences here, thus we can check C API reference: "[...] Otherwise, the row count is unavailable unless you count the rows as you fetch them. ", http://dev.mysql.com/doc/refman/5.6/en/mysql-stmt-num-rows.html . There is no promise made in the documentation that num_rows is set to 0. "Unavailable" means that it can be set to any value. The return value can be , 0 (libmysql) or the actual number of rows (mysqlnd). None of this would violate the documentation. This leaves us with: - BC breakage is impossible because behavior is undefined - update test, issue gone Ulf --- ## #55212 — Stas Malyshev — 2011-09-05T09:37:16+00:00 URL: https://externals.io/message/55091#55212 · In reply to: #55210 Hi! > - BC breakage is impossible because behavior is undefined > - update test, issue gone OK, since you say it's not the intended behavior to be relied upon, I'll remove this check from the test (if nobody beats me to it). -- Stanislav Malyshev, Software Architect SugarCRM: http://www.sugarcrm.com/ (408)454-6900 ext. 227 --- ## #55213 — Pierre Joye — 2011-09-05T09:39:02+00:00 URL: https://externals.io/message/55091#55213 · In reply to: #55212 > Hi! > > > > […] > > OK, since you say it's not the intended behavior to be relied upon, I'll > remove this check from the test (if nobody beats me to it). I will! not! :-) -- Pierre @pierrejoye | http://blog.thepimp.net | http://www.libgd.org --- ## #55214 — Ulf Wendel — 2011-09-05T09:49:51+00:00 URL: https://externals.io/message/55091#55214 · In reply to: #55212 Am 05.09.2011 11:37, schrieb Stas Malyshev: > Hi! > > > > […] > > OK, since you say it's not the intended behavior to be relied upon, I'll > remove this check from the test (if nobody beats me to it). No, please don't change it that way. Returning a relevant value for stmt_num_rows() seems a valid feature request that makes perfectly sense to me and is somewhat in line with the vague non-PS documentation of the case. To the end user the message is "undefined, do not rely on". Towards the library implementor the message is "try to make libmysql become better in the future, keep reasonable value in mysqlnd implementation meanwhile". Do something like: if ($IS_MYSQLND) /* TO END USER: no promise on this assumption */ ... Please, do not drop the information of the difference by removal of the assertion. Ulf --- ## #55215 — Stas Malyshev — 2011-09-05T10:00:42+00:00 URL: https://externals.io/message/55091#55215 · In reply to: #55214 Hi! > Returning a relevant value for stmt_num_rows() seems a valid feature > request that makes perfectly sense to me and is somewhat in line with > the vague non-PS documentation of the case. It's not a good situation where mysqlnd and libmysql have different semantics and people are encouraged to rely on it (if you call it feature, you encourage people to use it, otherwise why call it feature?). It leads to code that works on mysqnd but not on libmysql, without any indication of it - you'll just install an app, and it would work on one server but not on the other, for unknown reason. But if you add it at least it should be clearly documented - both in the manual and in the tests - that this is a non-portable mysqnd-only semantics. And the tests should be changed accordingly, so libmysql won't fail. > To the end user the message is "undefined, do not rely on". Towards the > library implementor the message is "try to make libmysql become better > in the future, keep reasonable value in mysqlnd implementation meanwhile". > > Do something like: > > if ($IS_MYSQLND) > /* TO END USER: no promise on this assumption */ > ... > > Please, do not drop the information of the difference by removal of the > assertion. OK, please make the patch yourself, so it looks as you prefer. -- Stanislav Malyshev, Software Architect SugarCRM: http://www.sugarcrm.com/ (408)454-6900 ext. 227 --- ## #55216 — Ulf Wendel — 2011-09-05T10:11:57+00:00 URL: https://externals.io/message/55091#55216 · In reply to: #55215 Am 05.09.2011 12:00, schrieb Stas Malyshev: > Hi! > > > > […] > > It's not a good situation where mysqlnd and libmysql have different > semantics and people are encouraged to rely on it (if you call it > feature, you encourage people to use it, otherwise why call it > feature?). It leads to code that works on mysqnd but not on libmysql, > without any indication of it - you'll just install an app, and it would Well, those who want to reply on UNDEFINED behavior, shall fool themselves. It will be a great laugh for the rest of us. Ulf --- ## #55221 — Ulf Wendel — 2011-09-05T13:19:30+00:00 URL: https://externals.io/message/55091#55221 · In reply to: #55091 No mysqlnd-libmysql BC break here. Metadata and libmysql - there's hardly a better example why mysqlnd should be set as a default. With libmysql as a default, PHP 5.4 will have a "randomly" crashing default configuration. https://bugs.php.net/bug.php?id=55001 http://bugs.mysql.com/bug.php?id=62350 (Note how the issue is there, then gone and then back again depending on version...) Am 02.09.2011 19:19, schrieb Stas Malyshev: > EXPLAIN - metadata [ext/mysqli/tests/mysqli_explain_metadata.phpt] > The reason is that plain SQL and prepared SQL return different data - > catalog field sometimes is "def", sometimes `NULL` in MYSQL_FIELD > structure returned by mysql_fetch_field_direct(). It may be mysql bug in > which case test should be SKIPed for versions that have this bug. And, yet again metadata... PASS EXPLAIN - metadata [ext/mysqli/tests/mysqli_explain_metadata.phpt] libmysql 5.1.49 @ MySQL 5.1.49 libmysql 5.5.15 @ MySQL 5.1.49 libmysql 5.6.2-m5 @ MySQL 5.1.49 mysqlnd @ MySQL 5.1.49 mysqlnd @ MySQL 5.1.37 FAIL libmysql 5.1.37 @ MySQL 5.1.37 ... always use the latest and greatest. Libmysql bug, exact version range is not known to me. Feel free to change the test to be skipped during SKIPIF, for example, like this: require(connect.inc) if (!IS_MYSQLND && libmysql_version > ... && libmysql_version < ...) die(skip libmysql bug) At the first look, I can't find a related bug report at bugs.php.net. Looks like few people compare PS and non-PS metadata for EXPLAIN in their application. Ulf --- ## #55222 — Andrey Hristov — 2011-09-05T13:28:13+00:00 URL: https://externals.io/message/55091#55222 · In reply to: #55221 > No mysqlnd-libmysql BC break here. > > Metadata and libmysql - there's hardly a better example why mysqlnd > should be set as a default. With libmysql as a default, PHP 5.4 will > have a "randomly" crashing default configuration. > > https://bugs.php.net/bug.php?id=55001 > http://bugs.mysql.com/bug.php?id=62350 > > (Note how the issue is there, then gone and then back again depending on > version...) > > Am 02.09.2011 19:19, schrieb Stas Malyshev: > > […] > > And, yet again metadata... > > PASS EXPLAIN - metadata [ext/mysqli/tests/mysqli_explain_metadata.phpt] > > libmysql 5.1.49 @ MySQL 5.1.49 > libmysql 5.5.15 @ MySQL 5.1.49 > libmysql 5.6.2-m5 @ MySQL 5.1.49 > > mysqlnd @ MySQL 5.1.49 > mysqlnd @ MySQL 5.1.37 > > FAIL > > libmysql 5.1.37 @ MySQL 5.1.37 > > ... always use the latest and greatest. Libmysql bug, exact version > range is not known to me. Feel free to change the test to be skipped > during SKIPIF, for example, like this: > > require(connect.inc) > if (!IS_MYSQLND && libmysql_version > ... && libmysql_version < ...) > die(skip libmysql bug) > > At the first look, I can't find a related bug report at bugs.php.net. > Looks like few people compare PS and non-PS metadata for EXPLAIN in > their application. this is exactly what I mean. If there was a bug in a previous version and you try to use it, what do you want to see? SKIP or FAIL? I vote with 2 hands for FAIL, because so I can easily spot problems when migrating to a new version of PHP on my platform. If the test is made to PASS, it should not be skipped unless it is not possible to run it - like if it tests mysqli functions which are not available in libmysql's build. But otherwise let it go, and if it FAILs, this is outside of our problem. WE _should_ not try to hide problems of third party software. Our tests don't test only PHP, they test the whole platform they run on to convince the end user that he can rely on the results of PHP on this configuration. > Ulf > Andrey --- ## #55223 — Ulf Wendel — 2011-09-05T13:34:39+00:00 URL: https://externals.io/message/55091#55223 · In reply to: #55222 Am 05.09.2011 15:28, schrieb Andrey Hristov: > > > […] > > this is exactly what I mean. If there was a bug in a previous version > and you try to use it, what do you want to see? SKIP or FAIL? I vote Andrey, you are changing topic from MySQL something test failure to test writing in general. IMHO you are raising a general question worth a dedicated discussion thread, something like: "If a test depends on external entity, which has a bug, shall the test FAIL or be SKIPPed?" I'd appreciate if this is discussed independently of the example of a mysqli test failure discussed in the light of the upcoming PHP 5.4 beta. Ulf --- ## #55226 — Ulf Wendel — 2011-09-05T14:24:14+00:00 URL: https://externals.io/message/55091#55226 · In reply to: #55091 Libmysql only test, skipped if using mysqlnd. No mysqlnd-libmysql BC break here. Am 02.09.2011 19:19, schrieb Stas Malyshev: > new mysqli() [ext/mysqli/tests/mysqli_connect_oo_warnings.phpt] > This one just times out trying to look up the invalid DNS name. This is > a recent breakage, didn't happen before. Can't repeat. A wild guess: your systems network configuration has changed or whatever the external dependency may be, I do not know... PASS w network enabled on box: libmysql 5.1.37 libmysql 5.5.15 libmysql 5.6.2-m5 PASS w network disabled on box: libmysql 5.6.2-m5 You may be the right person to investigate as you claim to have observed a recent breakage. Ulf --- ## #55228 — Ulf Wendel — 2011-09-05T14:53:18+00:00 URL: https://externals.io/message/55091#55228 · In reply to: #55091 This is the one and only mysqlnd-libmysql difference of some practical relevance. I consider it at least questionable if libmysql is correct. If it was to be decided that mysqlnd is wrong, it is probably like five lines of code in mysqlnd to change, if need be. Am 02.09.2011 19:19, schrieb Stas Malyshev: > API vs. SQL LAST_INSERT_ID() [ext/mysqli/tests/mysqli_last_insert_id.phpt] > The reason is that this test relies on LAST_INSERT_ID() being reset on > SELECT. I have not observed such behavior neither via PHP not talking to > Mysql server directly from CLI interface, so I have no idea why this > test assumes such behavior. Personal observation and memory may not be the best reference here. What the test does is: DROP TABLE IF EXISTS test CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY) Engine=MyISAM INSERT INTO test(id) VALUES (1); printf("insert id for INSERT is: %d\n", mysqli_insert_id(link)); SELECT 1 FROM DUAL printf("insert id for SELECT is: %d\n", mysqli_insert_id(link)); Libmysql will print: insert id for INSERT is: 1 insert id for SELECT is: 1 Mysqlnd will print: insert id for INSERT is: 1 insert id for SELECT is: 0 At the end of the day, we are discussing C library differences again, thus we can consult the C API reference: "Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field, or have used INSERT or UPDATE to set a column value with LAST_INSERT_ID(expr).", http://dev.mysql.com/doc/refman/5.6/en/mysql-insert-id.html The test is using the function not after an INSERT but after a SELECT. The documentation continues: "The return value of mysql_insert_id() is always zero unless explicitly updated under one of the following conditions: - [...] INSERT - [...] INSERT multi-row ... MySQL version dependent - [...} INSERT...SELECT and [...] - [...] INSERT...SELECT and [...]" Followed by: "mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the statement that generates the value." I read: - "The return value of mysql_insert_id() is always zero unless explicitly updated under one of the following conditions" - "If you need to save the value for later, be sure to call mysql_insert_id() immediately after the statement that generates the value." If people like, we can call this mysqlnd interpretation a bug. In any case, I find no reference in the documentation that the value must NOT to be reset upon SELECT. Searching bugs.php.net gives one related case: https://bugs.php.net/bug.php?id=54190 Unfortunately, Andrey did not write down the MySQL bug he refers to. However, back in March he wrote something that makes be believe he interprets the manual similar to how I do: "mysqli calls internally "SHOW WARNINGS" to fetch the warnings from the server. The SHOW statement should reset insert_id in libmysql, but it does not." To sum up: - debatable issue - edge case going beyond primary use of function - edge case not explicitly covered by the documentation - five lines(?) to change, if need be - quick check: no bug report on bugs.php.net against mysqlnd Ulf --- ## #55230 — Andrey Hristov — 2011-09-05T15:07:24+00:00 URL: https://externals.io/message/55091#55230 · In reply to: #55228 Hi, > This is the one and only mysqlnd-libmysql difference of some practical > relevance. I consider it at least questionable if libmysql is correct. > > If it was to be decided that mysqlnd is wrong, it is probably like five > lines of code in mysqlnd to change, if need be. > > > Am 02.09.2011 19:19, schrieb Stas Malyshev: > > […] > > Personal observation and memory may not be the best reference here. What > the test does is: > > DROP TABLE IF EXISTS test > CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY) Engine=MyISAM > > INSERT INTO test(id) VALUES (1); > printf("insert id for INSERT is: %d\n", mysqli_insert_id(link)); > > SELECT 1 FROM DUAL > printf("insert id for SELECT is: %d\n", mysqli_insert_id(link)); > > > Libmysql will print: > > insert id for INSERT is: 1 > insert id for SELECT is: 1 and here we see a bug. The value in libmysql is not reset, as the Documentation states. Clear bug. > Mysqlnd will print: > > insert id for INSERT is: 1 > insert id for SELECT is: 0 > mysqlnd does what is expected, as in many other cases :) During mysqlnd's development we found numerous bugs in libmysql and reported them. mysqlnd provided support for multiple result sets from prepared statements more than year earlier than libmysql even had code to handle this, which is in MySQL 5.5 now. > > "mysql_insert_id() returns 0 if the previous statement does not use an > AUTO_INCREMENT value. If you need to save the value for later, be sure > to call mysql_insert_id() immediately after the statement that generates > the value." > Very clearly said. LAST_INSERT_ID() is another story, but it has nothing to do with the client library. Andrey --- ## #55231 — Rasmus Lerdorf — 2011-09-05T16:49:04+00:00 URL: https://externals.io/message/55091#55231 · In reply to: #55230 > and here we see a bug. The value in libmysql is not reset, as the > Documentation states. Clear bug. For cases where a test exposes a libmysql bug, especially if it is something that acts differently across different versions of libmysql, the test should remain a FAIL, but it should point to the mysql.com bug # so end users realize that they have a version of libmysql with that specific bug. In cases where there is no agreement whether something is a bug or just undefined behaviour it would be really nice if the library authors could work this out and agree on a common behaviour and failing that PHP should try to mask the internal Oracle/MySQL squabbles and present consistent behaviour to the user. -Rasmus --- ## #55237 — Ulf Wendel — 2011-09-05T20:43:06+00:00 URL: https://externals.io/message/55091#55237 · In reply to: #55231 Am 05.09.2011 18:49, schrieb Rasmus Lerdorf: > In cases where there is no agreement whether something is a bug or just > undefined behaviour it would be really nice if the library authors could > work this out and agree on a common behaviour and failing that PHP > should try to mask the internal Oracle/MySQL squabbles and present > consistent behaviour to the user. Yes... :( . Ever heard a german saying "innerer Schweinehund überwinden"? That means convincing yourself to take the battle, to go the stony road, to motivate yourself. It pays out on the long run. In other words: our fault. Ulf