Made of Everything You're Not

Here’s another random MySQL issue I run up against from time to time; an obscure duplicate key error.

#1062 - Duplicate entry '2147483647' for key 1

The reason for the issue is because the script is trying to use an INT column type with a number larger than 2147483647 and 2147483647 is the highest number you can have on a 32bit system.

2147483647

The number 2,147,483,647 is also the maximum value for a 32-bit signed integer in computing. It is therefore the maximum value for variables declared as int in many programming languages running on popular CPUs, and the maximum possible score for many video games. The appearance of the number often reflects an error, overflow condition, or missing value.

The data type time_t, used on operating systems such as Unix, is a 32-bit signed integer counting the number of seconds since the start of the Unix epoch (midnight UTC of January 1, 1970). The latest time that can be represented this way is 03:14:07 UTC on Tuesday, 19 January 2038 (corresponding to 2,147,483,647 seconds since the start of the epoch), so that systems using a 32-bit time_t type are susceptible to the Year 2038 problem.

Online microblogging service Twitter faced a similar problem (called a “Twitpocalypse”) at 2009-06-12 23:52:04 GMT, when the unique identifier associated with each tweet exceeded 2,147,483,647. While Twitter itself was not affected, some third-party clients were, and had to be patched.

Ironically, the issue that precipitated this post was for a Twitter app. A little advise; if you’re storing twitter post ids don’t use INT for the column type

It’s a pretty common error actually; definitely not one to be too embarrassed about (you should be a little embarrassed though). In fact, if you’ve ever played a video game you’ve probably already seen it.

A number which is commonly found in hacked games and the score will be 2147483647. It is also the highest score you can get in a 32 bit game. The maximum score is 2147483647 because most games are written in 32 bit and it has to represent both negative and positive integers so 2^31 – 1 would be 2147483647. It is also commonly known as Mersenne Prime which a prime number that is one less power of two.
1. h4x0r – 2147483647
2. h4x0r – 2147483647
3. h4x0r – 2147483647
…

noob: OMG… someone has hacked the game and got 2147483647
h4x0r: beat that score noob

Perhaps the coolest effect, or scariest if you’re a pussy, issue with 2147483647 is, as shown above, what’s being called the Year 2038 problem.

The Year 2000 problem is understood by most people these days because of the large amount of media attention it received.

Most programs written in the C programming language are relatively immune to the Y2K problem, but suffer instead from the Year 2038 problem. This problem arises because most C programs use a library of routines called the standard time library . This library establishes a standard 4-byte format for the storage of time values, and also provides a number of functions for converting, displaying and calculating time values.

The standard 4-byte format assumes that the beginning of time is January 1, 1970, at 12:00:00 a.m. This value is 0. Any time/date value is expressed as the number of seconds following that zero value. So the value 919642718 is 919,642,718 seconds past 12:00:00 a.m. on January 1, 1970, which is Sunday, February 21, 1999, at 16:18:38 Pacific time (U.S.). This is a convenient format because if you subtract any two values, what you get is a number of seconds that is the time difference between them. Then you can use other functions in the library to determine how many minutes/hours/days/months/years have passed between the two times.

All this from one number.

This entry was written by Eric Lamb and posted on Monday, July 6th, 2009 at 5:00 am and is filed under IT, Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.