Bug #4841 possible table corruption in mysql server version: 4.0.18
Submitted: 31 Jul 2004 18:31 Modified: 6 Sep 2004 20:23
Reporter: ervin ruci Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.18 OS:Linux (debian linux (knoppix 3.4))
Assigned to: Matthew Lord CPU Architecture:Any

[31 Jul 2004 18:31] ervin ruci
Description:
once the table gets past a certain size, the max() function no longer reports properly:
eg:

1. table store, storeid is unique:
mysql> desc store;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| storeid  | int(11)      | YES  | MUL | NULL    |       |
+----------+--------------+------+-----+---------+-------+

2. select the max of the storeid:
mysql> select max(storeid) from store;
+--------------+
| max(storeid) |
+--------------+
|         9667 |
+--------------+
that is incorrect!!! because storeid = 9668 exists!!
mysql> select storeid from store where storeid = 9668;
+---------+
| storeid |
+---------+
|    9668 |
+---------+
mysql> select storeid from store where storeid > 9667;
+---------+
| storeid |
+---------+
|    9668 |
+---------+
1 row in set (0.01 sec)

How to repeat:
create a table.
create a primary key unique field (integer)
insert into table like this:
my $max = select max(id) from tabl;
insert into tabl values ($max+1)

once the table size gets past a certain point this will crash because the max function will no longer be reporting the maximum size integer.
[3 Aug 2004 1:32] Matthew Lord
Hi,

Thank you for your bug report!  Could you provide a dump of this table on our ftp server?
You can use the anonymous account to put it on ftp.mysql.com.

If that's not possible could your provide me with the table type, the size of the table in bytes, and 
the number of rows (show table status)?  The table structure would also be helpful (show create 
table).
[3 Aug 2004 5:48] ervin ruci
bizarre... this problems seems to be intermittent. i truncated my table then reloaded it several times over and was unable to reproduce the error. (i actually first got around it by this little hack:
    my $max = $dbh->selectrow_array("select max(storeid) from store ");
    my $sid = $max + 1; #we will insert the next record with this id
    my $isid = 1;
    while ($isid) { #workaround for a (possible) bug in mysql
	$isid = $dbh->selectrow_array("select storeid from store where storeid = $sid");
	if ($isid) {
	    print "max mess $isid\n";
	    $sid++;
	}
    }
    #ok. now we are sure $sid does not exist. let's insert..

i am afraid you might not be able to reproduce it either.

in any case this is the information you required:
mysql> show table status;
| store      | MyISAM | Dynamic    | 14126 |            131 |     1851552 |      4294967295 |       133120 |         0 |           NULL | 2004-08-02 14:22:25 | 2004-08-02 16:10:42 | NULL                |                |         |

mysql> check table store;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| groc.store | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.36 sec)

mysql> desc store;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| storeid  | int(11)      | YES  | MUL | NULL    |       |
| user     | varchar(200) | YES  |     | NULL    |       |
| pass     | varchar(200) | YES  |     | NULL    |       |
| addresst | varchar(200) | YES  |     | NULL    |       |
| city     | varchar(200) | YES  |     | NULL    |       |
| prov     | varchar(20)  | YES  |     | NULL    |       |
| postal   | varchar(200) | YES  |     | NULL    |       |
| phone    | varchar(200) | YES  |     | NULL    |       |
| name     | varchar(200) | YES  |     | NULL    |       |
| email    | varchar(240) | YES  |     | NULL    |       |
| manager  | varchar(240) | YES  |     | NULL    |       |
| type     | varchar(240) | YES  |     | NULL    |       |
| status   | char(3)      | YES  |     | NULL    |       |
| master   | int(11)      | YES  |     | NULL    |       |
| stnumber | int(11)      | YES  |     | NULL    |       |
| said     | int(11)      | YES  |     | NULL    |       |
| url      | varchar(200) | YES  |     | NULL    |       |
| promo    | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)

since the table contains several sensitive informations i am unable to provide you with a complete table dump. 
good luck & thanks for your quick response.

i really like your database server and currently i am in the process of evaluating it for application in enterprise level applications at the company i work for. (maybe the day we'll be rid of oracle isn't that far).

cheers,
ervin.