Incorrect Integer (2147483647) is inserted into MySQL?

Question

Alright, so I've been toying around with the Steam Web API, I have one of the values stored in a variable called $steam64. When I use this code snipper to INSERT it into a mysql database it insert a completley different integer than what is stored in the variable.

$sql_query = "INSERT INTO users_info (steam64) VALUES ('$steam64')";

var_dump($steam64); returns the real int, so does echoing it. Not too sure what is going on here, any help is appreciated.

Selfsustaining

What is inserted (and what was dumped) and what is the column type? It's likely not a "bug" in MySQL, which means the problem lies elsewhere... – Morphosis

shouldn't need the single quotes on an integer. it may be trying to insert the string '$steam64' as an integer, converting it in the process. – Sidereal

@pst 2147483647 is being inserted, 76561197989628470 is being dumped, column type is int(255). – Selfsustaining

@Sidereal just having it VALUES ($steam64) still inserts the wrong int. – Selfsustaining

dev.mysql.com/doc/refman/5.5/en/integer-types.html – Bollen

Answer 1

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.

Braga

Posted update. The number you are trying to insert is too big. – Braga

Thanks :) Is it PHP or MySQL that has the limit? – Selfsustaining

It's a system limit, so any app trying to access a number that big will result unexpected behavior. – Braga

Alright quick question with that, if i wanted to have a for loop with a variable that is something like 76561197980000000, would that even be possible? – Selfsustaining

Yes, as long as it's not an int. – Braga

@Pst: Thanks for the correction, I should have known when pasting the values in. – Braga

Answer 2

2147483647 is the largest int value for mysql. Just change the type from int to bigint.

Audit

It's worth noting that BIGINT has a limit of -9223372036854775808 to 9223372036854775807 (signed) or 0 to 18446744073709551615 (unsigned). if you want the larget POSITIVE ONLY integer then use something like COLUMNNAME BIGINT(32) UNSIGNED DEFAULT 0 – Headgear

Answer 3

While I was playing with SQL and MySQL had the same problem MySQL int data type. Modifying data type from int to bigint fixed issue.

MySQL Integer Types http://dev.mysql.com/doc/refman/5.7/en/integer-types.html

ALTER TABLE tablename MODIFY columnname BIGINT; 

Diastole

Answer 4

Simply Change the data type from INT to BIGINT

Grazia

Answer 5

The largest value for data type INT is 2147483647. If the number you're inserting is bigger than 2147483647, then it will cause the problem. For solution, change the data type from INT to BIGINT as BIGINT has a maximum value of 9223372036854775807, it might solve your problem. Have a look at this site: https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

Amadou

Answer 6

The integer type INT is 4Bytes storage, you get from -2^(4*8-1)=-2147483648 to 2^(4*8-1)-1=2147483647, when you have "signed" flags, if you change the flags to unsigned you will have a range from 0 to 2^(4*8)-1 . MySQL support BIGINT being 8Bytes storage. If you try save a value greater, you will save the max value of the range

Translunar

Answer 7

Go to operations-> table options -> change increment values to minimum or whatever you want to increment..

the big problem of autoincrement is it's start from last entry by mistake if its very large value then start problem in insert value.. with our predefined datatype

Tertia

Answer 8

Agree with the datatype change to BIGINT from INTEGER. Currently building a web app with node.js/sequelize the below refactor solved the phone number post from react-redux form manipulated to '2147483647':

clientPhone: {
    type: DataTypes.BIGINT,
    allowNull: true
},

Gasholder

Answer 9

I had the same problem but not with Varchar. The problem I had was that I was performing an INSERT INTO with a bad order of columns. For anyone who see this maybe is the order of cols that u are using in a subquery

Stanleystanly

Answer 10

CREATE TABLE `dvouchers` (
  `2147483647` int(3) NOT NULL auto_increment,
  `code` varchar(12) NOT NULL default '1',
  `type` char(1) NOT NULL default '$',
  `count` int(3) unsigned NOT NULL default '0',
  `amount` int(3) unsigned default '0',
  `notes` text,
  `expiryDate` date default NULL,
  `fkUserAdminId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`2147483647`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Bryce

hmm ... and this answers the question because ... ? – Aronson

This makes no sense, do not try to infer any help from this answer if you're here with this issue. – Headgear

Answer 11

Easiest way is change in MySQL "int" to "varchar".

Twit

Do not do this. Never do this. VARCHAR is a string representation and not a numeric one. It would not be possible to run mathematical operations on a string. – Erelia