Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:55228
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: error (pb1.pair.com: domain oracle.com from 141.146.126.227 cause and error)
Message-ID: <4E64E25E.3080102@oracle.com>
Date: Mon, 05 Sep 2011 16:53:18 +0200
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.1) Gecko/20110830 Thunderbird/6.0.1
MIME-Version: 1.0
To: internals@lists.php.net
References: <4E611027.6000103@sugarcrm.com>
In-Reply-To: <4E611027.6000103@sugarcrm.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: [PHP-DEV] mysqli tests breaking
From: ulf.wendel@oracle.com (Ulf Wendel)

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