MySQL Bugs: #32310: select max gives different results

Bug #32310	select max gives different results
Submitted:	13 Nov 2007 11:09	Modified:	29 Feb 2008 17:29
Reporter:	Markus Senoner	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	Ver 5.0.45	OS:	Linux (Debian 4.0)
Assigned to:		CPU Architecture:	Any
Tags:	query max

Description:
Following table "shopcart":
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| sid       | int(11)  |      | PRI | NULL    | auto_increment |
...

When I insert "select max(sid) as last from shopcart;" on the mysql-command-line-interface, it gives 59218 (which is correct)

When I make the query in a PHP-Script:
  $query = "select max(sid) as last from shopcart";
  $getit = mysql_query($query, $dbh);
  $row = mysql_fetch_array($getit);
  $num = $row["last"];
$num will be 59217, which is wrong!

It worked correct till "sid" became 59218 - I didn't change anything in the software!

How to repeat:
Always repeatable on my Server

Suggested fix:
whena I change the query like follows:
  $query = "select max(sid)+1 as nextsid from shopcart";
  $getit = mysql_query($query, $dbh);
  $row = mysql_fetch_array($getit);
  $num = $row["nextsid"];
$num will be 59219, which is correct!

Thank you for a problem report. Please, send the results of:

SHOW CREATE TABLE shopcart;

and complete C API code you use.

Here the output of "SHOW CREATE TABLE shopcart":

CREATE TABLE `shopcart` (
  `sid` int(11) NOT NULL auto_increment,
  `sessionid` char(40) default NULL,
  `idprod` int(11) default NULL,
  `prodcode` char(40) default NULL,
  `squant` int(11) default NULL,
  `date` char(8) default NULL,
  `prodaid1` int(11) default NULL,
  `prodaid2` int(11) default NULL,
  PRIMARY KEY  (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I have no details about the C-API-Code.  I use the functions as given by PHP 4.3.11: mysql_connect(), mysql_select_db(), mysql_query() and mysql_fetch_array()

Strange is:
"select max(sid) from shopcart" gives 59217 while
"select max(sid)+1 from shopcart" gives 59219

Just to rule out the obvious: you are trying the "max()" and "max()+1" queries from the same session so that they are not producing different results due to 
an uncommited transaction?

What is also strange is that the SHOW CREATE output doesn't list the current auto_increment counter setting like here:

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Did you do the SHOW CREATE on the life system or on a system with an empty table?

It's an online shop, which worked well for several months. All of a sudden people complained, they cannot put products in the shopcart.  I went to see, and the error-message was: "Duplicate entry '59218' for key 1", which was produced by an INSERT statement:
  $query = "select max(sid) as lastfrom shopcart";
  $getit = mysql_query($query, $dbh);
  $row = mysql_fetch_array($getit);
  $num = $row["last"] + 1;
  $query = "insert into shopcart values($num,'$sessionid',$p_id, [...])";
  $res = mysql_query($query, $dbh);

There already existed a record with sid=59218.
The value of max(sid) should therefore have been 59218, but (In dunno why) is was stuck to 59217.

So I opened mysql from command line and executed the same SQL-command, as in PHP, and the result was correct: 59218

Now I have changed the above lines to:
  $query = "select max(sid)+1 as nextsid from shopcart";
  $getit = mysql_query($query, $dbh);
  $row = mysql_fetch_array($getit);
  $num = $row["nextsid"];
  $query = "insert into shopcart values($num,'$sessionid',$p_id, [...])";

and it works.

Yes, I did SHOW CREATE on the life system - unfortunately its a production system, so this error is crucial!

SHOW CREATE doesn't list the current counter setting on any of the tables that use auto_increment.  Is this maybe dependent on the fact that I use an older version of MySQL (4.1.12)?

Thank you for the feedback.

> Is this maybe dependent on the fact that I use an older version of MySQL
(4.1.12)?

Yes, current 4.1 version is 4.1.23 which contains many bug fixes. Please upgrade and if you still can repeat the problem please turn on general query log and send us corresponding part of it to we can see which statements and in which order were really issued.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

The same error happened on another Linux-system, where I use mysqld Ver 5.0.45 for pc-linux-gnu on i686.

This time the table was like:
CREATE TABLE `orders` (
  `num` int(11) NOT NULL auto_increment,
  `sessionid` varchar(40) default NULL,
  `lang` char(2) default NULL,
  `clientid` varchar(30) default NULL,
  ...
  `fattura` int(11) default NULL,
  ...
  `creditnote` int(11) default NULL,
  ...
  PRIMARY KEY  (`num`)
) ENGINE=MyISAM AUTO_INCREMENT=108622 DEFAULT CHARSET=latin1

SELECT max(fattura) as maxf,max(creditnote) as maxc FROM orders
(executing sql-statement with mysql_query() and fetching values with mysql_fetch_assoc()...)
$creditnote = max($maxf,$maxc) + 1;

gave me wrong results, while

select max(fattura)+1 as nextf,max(creditnote)+1 as nextc from orders
and then calculating:
$creditnote = max($nextf,$nextc);

gives me now correct value for $creditnote

there's no auto_increment on the columns fattura and creditnote.
The select-max function seems to give wrong values.  There was no heavy load on the system.  Uptime was 75 days.  Maybe some buffer/cache overflow?

Please do CHECK TABLE and REPAIR TABLE.

When this didn't help try myisamchk.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".