GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Integer overflow with UNSIGNED INT columns

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Integer overflow with UNSIGNED INT columns

    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.

    Comment


    • #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

      Comment


      • #4
        Finally I've found the solution:

        SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';

        Cheers,
        Chris

        Comment

        Working...
        X