Integer overflow with UNSIGNED INT columns

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’ve got a problem with UNSIGNED INT columns on different mysql versions:

(stylised queries)

MySql 4:

mysql> select col from tab;
mysql> 0
mysql> update tab set col=col-1;
mysql> select col from tab;
mysql> 0

MySql 5:

mysql> select col from tab;
mysql> 0
mysql> update tab set col=col-1;
mysql> select col from tab;
mysql> 4294967295

MySql 5 seems to have an integer overflow, which is VERY bad in my case. Is there a way to teach MySql 5 to act like MySql 4 to keep the zero (even if the actual value is lower)?

I hopy you can help! This prevents me from updating to a newer MySql Version …

Thanks and kind regards,
Chris

P.S. I don’t know the exact mysql-versions by heart but can hand it in later if it is important / required!

2

Actually, it’s not an under/over flow. Technically, an over/under flow is when there aren’t enough bits to represent the number.

In this case you’re entering -1. -1 is stored as 11111111111111111111111111111111 in signed format. Can you guess what that is in unsigned format? 4294967295!

You’re just converting an internal binary number from signed to unsigned and that’s exactly what the result is.

3

Thanks for your reply, you’re right )

But it actually doesn’t explain or even solve the problem … the unsigned value of “-1” should not appear.

Cheers,
Chris

4

Finally I’ve found the solution:

SET SQL_MODE=‘NO_UNSIGNED_SUBTRACTION’;

Cheers,
Chris