note 93622 modified in function.mysql-insert-id by danbrown

Apparently the value returned by mysql_insert_id() may be correct for BIGINT auto_increment keys below the value of INT, but it may wrap to negative when the BIGINT auto_increment passes the largest signed value of INT. A timebomb for very large tables... PHP 5.2.10, MySQL 5.0.81, assume the connection and selection... <?php // CREATE A TABLE AND ALTER IT TO A HIGH INDEX NUMBER $sql = "CREATE TEMPORARY TABLE noise ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=MEMORY"; if (!$res = mysql_query($sql)) die(mysql_error()); $sql = "ALTER TABLE noise AUTO_INCREMENT = 2147483646"; if (!$res = mysql_query($sql)) die(mysql_error()); // INSERT DATA TO ADD TO THE AUTO_INCREMENT INDEX $kount = 0; while ($kount < 3) { $sql = "INSERT INTO noise () VALUES ()"; if (!$res = mysql_query($sql)) die(mysql_error()); $nid = mysql_insert_id($dbcx); var_dump($nid); $kount++; } // PRODUCES THIS // int(2147483646) // int(2147483647) // int(-2147483648) ?> --was-- Apparently the value returned by mysql_insert_id() may be correct for BIGINT auto_increment keys below the value of INT, but it may wrap to negative when the BIGINT auto_increment passes the largest signed value of INT. A timebomb for very large tables... PHP 5.2.10, MySQL 5.0.81, assume the connection and selection... <?php // CREATE A TABLE AND ALTER IT TO A HIGH INDEX NUMBER $sql = "CREATE TEMPORARY TABLE noise ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=MEMORY"; if (!$res = mysql_query($sql)) die(mysql_error()); $sql = "ALTER TABLE noise AUTO_INCREMENT = 2147483646"; if (!$res = mysql_query($sql)) die(mysql_error()); // INSERT DATA TO ADD TO THE AUTO_INCREMENT INDEX $kount = 0; while ($kount < 3) { $sql = "INSERT INTO noise () VALUES ()"; if (!$res = mysql_query($sql)) die(mysql_error()); $nid = mysql_insert_id($dbcx); var_dump($nid); $kount++; } // PRODUCES THIS // int(2147483646) // int(2147483647) // int(-2147483648) http://php.net/manual/en/function.mysql-insert-id.php