4

I'm attempting to use LAST_INSERT_ID on an auto incremented index that has moved past the signed int value 2147483647. This column is an unsigned int. However, LAST_INSERT_ID() is returning an invalid negative value. Researching this, I've found a couple comments indicating this is the nature of this function. But I cannot find it officially documented. There are some bug reports dating back several years that are still open.

So I need to know if this is in fact a known bug and/or if there's something I can do about this. Do I need to upgrade mySQL to a newer version?

EricLeslie
  • 67
  • 4
  • I'm actually assuming my apps are using last_insert_id(). I'm using Kohana's ORM module. Running the query in phpmyadmin returns the same invalid value. I just checked the ORM module and it appears to be using mysql_insert_id() and I've read problems there. Is this a matter of changing the PHP_INT_MAX to fix this or stop using mysql_insert_id? – EricLeslie Aug 05 '10 at 19:52
  • 1
    You can't actually change PHP_INT_MAX (which is a constant) without upgrading to 64-bit PHP – Mark Baker Aug 05 '10 at 20:02
  • Ok, makes sense, this is turning into a big hassle. The previous dev on this project used timestamps originally to create unique ids which wastes gads of number space. At some point it was changed to an autoincrement, but I'm stuck with the index over the 32bit limit. So I either need to upgrade our server or develop a script to rebuild all the IDs across several tables. – EricLeslie Aug 05 '10 at 20:26
  • Unless you need each sequence to be higher that the previous for any reason, then an option might be to identify the gaps in the id sequences (number space) and use those values rather than the autoincrement id – Mark Baker Aug 05 '10 at 20:56
  • @EricLeslie i was facing the same issue now and find out that there is a workaround only, $this->db->insert_id()+2147483647+2147483649 this will give you the correct value, but if you found a good way to do this please let me know ? – sandeepKumar Aug 13 '13 at 11:20

2 Answers2

0

I'm not 100% sure on this, but since you just need the ID and not to do any mathematical operations on it, could you perhaps process it as a string and have your select statement convert the number to a string?

Shadow Radiance
  • 1,349
  • 13
  • 20
0

I'm assuming you are using PHP from the comments on the question. (is not currently tagged as "php" related). I'll just add my 2 cents here

I'm attempting to use LAST_INSERT_ID on an auto incremented index that has moved past the signed int value 2147483647. This column is an unsigned int. However, LAST_INSERT_ID() is returning an invalid negative value. Researching this, I've found a couple comments indicating this is the nature of this function. But I cannot find it officially documented. There are some bug reports dating back several years that are still open.

mySQL's LAST_INSERT_ID function will return the ID from the latest INSERT on an AUTO_INCREMENT column (on the current connection) so, if your column is an INTEGER UNSIGNED like you said, it could return values greater than 2147483647. This is the correct behavior.

So I need to know if this is in fact a known bug and/or if there's something I can do about this. Do I need to upgrade mySQL to a newer version?

The mySQL data type has little to do with the representation you chose to use on PHP so the problema is not in the mySQL server. If your PHP installation can't handle numbers so BIG, then you can treat it as a string (Shadow Radiance said).

dcestari
  • 442
  • 3
  • 7