Incorrect Integer (2147483647) Is Inserted into MySQL

Incorrect Integer (2147483647) is inserted into MySQL?

Based on your comment of "value being dumped"; the number you are trying to insert is too large for 32-bit systems. The max for 32-bit is 4,294,967,295, and the max for 64-bit is 18,446,744,073,709,551,615. I'd recommend converting your column into a varchar(100) hash rather than an int, or switch to a 64 bit system. Great article about max ints here, and here.

---

Also, before I get flamed, be sure to read up on SQL injection in case you are not sanitizing variables being posted directly into sql statements.

Value being inserted as 2147483647 into database

If you can, convert the phone number to a VARCHAR field, don't store it as a signed (or unsigned) numeric value (int, bigint, double, etc...).

In this case, the limit for signed INT in MySQL of 2147483647 is what is causing your issue. Anything larger inserted into this field will be capped at that maximum value.

For example the phone number 555-555-5555 if bigger than that limit (5555555555 >2147483647), as such storing it would result in the max value 2147483647.

I also recommend not storing it as a BIG INT or any other numeric type. How will you handle extension or special encoded characters like:

   +02 112020 10020  
   1-333-232-9393 x203

BTW: don't know if the first is real non-US number format, but you get the idea

Also, phone numbers with relevant leading 0's would be have some of it lost no mater how large the number:

 021-392-9293

Would be the number 213929293

2147483647 getting inserted into DB field even though the datatype is VARCHAR

Analysis

You got 2 major clues here.

First Clue: 2147483647

What do we know about 2147483647?

From Wikipedia (http://en.wikipedia.org/wiki/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 computers.

How can we get this number created by the database?

In several ways, all related to INT, but since your "Mobile" field data type is VARCHAR and not INT, as you clearly emphasized, we know that the problem is not in the database itself. If the field was INT, we could have continued exploring the database itself for possible issues. So we eliminate this possibility. This leaves us with possible code issues.

Second Clue: The log

this is getting logged, and shows that the data was correctly entered. But as soon as we go and check in the database very randomly data is incorrect.

Every time the select query is running for the first time after the insert was made and being logged - it shows (in the log) that the data entered was correct and then when you check the db - sometimes it's correct and sometimes it's 2147483647.

The (usual) Suspect

Based on this information, it would be safe to assume that PHP is converting the string to integer somewhere and then put/update this value in the db, after it has already been correctly inserted.

Debugging

There are several good ways to debug this:

1) Search if there is another UPDATE query that enters data to the Mobile field and overwrites the correct data that was inserted. A full code search of all your files for the term "Mobile" can be a good start.

2) Search for any occurrence of intval in your code, as this function (or other similar functions/methods) can convert a string to 2147483647: Converting a String to an Integer returns 2147483647

3) Search for any other occurrences of 2147483647 in your MySQL database, just to determine if this is an isolated problem or if anything else get this value and might have this value copied to the Mobile field via some query or another method.

Hope this helps.

Mobile No getting saved in database and upon retrieval generating 2147483647

The largest value for mysql is 2147483647. Goto your specific table (one storing the mobile number) and change the column's type from int to bigint.

Mysql - UPDATE JOIN sets an unknown value 2147483647 to every entry

Finally solved it myself..

I'm trying to set a too big integer into an INT(8) field. The 2147483647 value means Incorrect Integer, that's why the value isn't matching with one of my records.

Hope it will help some people.

When I try to insert ip2long Integer, the wrong Integer gets inserted into table

I assume you have used INT as datatype in your database. To make this story short just change datatype of ip column to BIGINT and problem will be solved.

INT range (-2147483648, 2147483647)
BIGINT range (-9223372036854775808, 9223372036854775807)

2953470927 goes bit over the range of signed INT.

Refference: MySQL: Integer types

CodeIgniter keeps inserting 2147483647 for int values

The problem is with MySQL based on the id type you have defined. The largest int value is 2147483647. Check out their docs: https://dev.mysql.com/doc/refman/5.5/en/integer-types.html

Other options:

• Signed BIGINT can get you to 9223372036854775807
• Unsigned BIGINT can get you to 18446744073709551615
• The maximum number of digits for DECIMAL is 65

(see https://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html for more details).

Integer inserted into mysql is not the same when selected from db

Because your height is always integer value I think it's better to use:

$height = (int) $_POST['height'];

---

Related Topics