Beware - MySQL integer overflow on both 32 and 64-bit systems

I traced a defect in my application to the use of PHP’s intval() function used by xPDO when returning MySQL integer values from database - my BIGINT values were coming back as 2,147,483,647 (the maximum 32-bit signed integer) which was the giveaway.

The maximum value returnable by intval() on a 32-bit platform is 2,147,483,647 (2^31-1) or double that for unsigned types, so if you are storing any numbers larger than that (in columns with type BIGINT(10+) or INT(10+)) you will have issues.

On 64-bit systems you may still not be safe, as the maximum signed integer supported is 9,223,372,036,854,775,807 or double for unsigned. Hence some numbers in BIGINT(19) and BIGINT(20) may be affected. Anything smaller will be fine.

Luckily in this particular case, I don’t actually perform any arithmetic on the numbers I’m storing (they’re identifiers only) so I’m updating the affected columns from BIGINT to VARCHAR type.. I can still sort and compare them alphanumerically so that’s all I’m worried about.

If you absolutely must work with very large integers, there are PHP libraries providing large number support. To overcome the storage and retrieval issue in xPDO, you’d have to:

a) Wait for a newer version of PHP that natively supports large numbers in intval() or
b) Modify xPDO source to replace or wrap the use of intval() to work with large numbers as strings or
c) Store your large numbers as strings in the first place, and then you can use your favourite PHP large number library in your app logic or
d) Do nothing but ensure you will never overflow the maximum signed or unsigned integer value you’re storing in INT or BIGINT MySQL type

Cheers

SD:

Thanks for bringing this up. We’ll look into this.

All you need to do is define BIGINT columns in your xPDO schema with phptype="string" instead of phptype="integer" and xPDO will treat your values as strings, completely avoiding the problem.

Quote from: OpenGeek at Sep 16, 2009, 04:52 AM

All you need to do is define BIGINT columns in your xPDO schema with phptype="string" instead of phptype="integer" and xPDO will treat your values as strings, completely avoiding the problem.

Nice workaround, didn’t think of that. However I don’t mind updating my DB schema as it’s conceptually cleaner that way.. and saves me extra effort as well.

For those people who are performing calculations which use or produce large integers, and expect their integers to come back with the same value as stored in their INT or BIGINT fields (without being aware of the 32-bit and 64-bit limits of PHP intval() function), then this won’t necessarily solve their problem.. I guess it all depends on the intended use and what people are expecting when using xPDO. Armed with the knowledge however, this should at least give people a chance to choose the best workaround for them.

Not suggesting that xPDO needs fixing either as in my view, it’s not broken.. there’s just no way of knowing, out of the box, whether someone wants/needs their PHP type to be returned as integer or string from INT or BIGINT columns so you can’t really make a blanket decision without immediately affecting some section of the user base. My question would be therefore, is there, or should there be, any responsibility on the part of xPDO to inform the user as to the potential issue if during schema generation, it is noticed that BIGINT dbtypes are used?

Quote from: SD at Sep 16, 2009, 06:07 AM

My question would be therefore, is there, or should there be, any responsibility on the part of xPDO to inform the user as to the potential issue if during schema generation, it is noticed that BIGINT dbtypes are used?

I could definitely see that as a useful warning during schema generation. Thanks very much for the great feedback!