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.

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... — , Apr 21 '12 at 02:44
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. — Lazerblade, Apr 21 '12 at 02:47
@pst `2147483647` is being inserted, `76561197989628470` is being dumped, column type is int(255). — Archey, Apr 21 '12 at 02:55
@Lazerblade just having it `VALUES ($steam64)` still inserts the wrong int. — Archey, Apr 21 '12 at 02:56

score 71 · Answer 1 · answered Jul 22 '13 at 08:49

71

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

answered Jul 22 '13 at 08:49

George

6,006
6
48
68

1

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 – Lewis May 17 '17 at 18:14

score 42 · Accepted Answer · edited Apr 29 '21 at 20:06

42

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.

edited Apr 29 '21 at 20:06

miken32

42,008
16
111
154

answered Apr 21 '12 at 02:52

Mike Purcell

19,847
10
52
89

3

Posted update. The number you are trying to insert is too big. – Mike Purcell Apr 21 '12 at 03:01
Thanks :) Is it PHP or MySQL that has the limit? – Archey Apr 21 '12 at 03:06
It's a system limit, so any app trying to access a number that big will result unexpected behavior. – Mike Purcell Apr 21 '12 at 03:07
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? – Archey Apr 21 '12 at 03:22
Yes, as long as it's not an int. – Mike Purcell Apr 21 '12 at 03:43
@Pst: Thanks for the correction, I should have known when pasting the values in. – Mike Purcell Apr 21 '12 at 04:44

score 11 · Answer 3 · edited Aug 03 '16 at 20:07

11

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;

edited Aug 03 '16 at 20:07

Abdulla Nilam

36,589
17
64
85

answered Aug 03 '16 at 19:10

Lyserty

335
4
14

score 9 · Answer 4 · answered Mar 29 '18 at 04:54

9

Simply Change the data type from INT to BIGINT

answered Mar 29 '18 at 04:54

Ron Varghese

136
1
4

score 7 · Answer 5 · answered Jun 30 '17 at 02:13

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

score 5 · Answer 6 · answered Aug 25 '15 at 18:57

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

score 1 · Answer 7 · answered Aug 04 '15 at 13:10

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

score 1 · Answer 8 · edited Jun 07 '17 at 00:13

1

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
},

edited Jun 07 '17 at 00:13

HDJEMAI

9,436
46
67
93

answered Jun 06 '17 at 23:54

Peter Tountas

51
5

score 0 · Answer 9 · answered Jul 12 '22 at 12:23

0

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

answered Jul 12 '22 at 12:23

Martin Muñoz

436
4
6

score -5 · Answer 10 · edited Aug 10 '13 at 07:01

-5

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;

edited Aug 10 '13 at 07:01

kleopatra

51,061
28
99
211

answered Aug 10 '13 at 06:55

Firmansyah

5

2

hmm ... and this answers the question because ... ? – kleopatra Aug 10 '13 at 07:02
This makes no sense, do not try to infer any help from this answer if you're here with this issue. – Lewis May 17 '17 at 18:11

score -5 · Answer 11 · answered Feb 19 '16 at 16:58

-5

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

answered Feb 19 '16 at 16:58

MatiiTv

5

1

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. – mwieczorek Dec 10 '17 at 08:25

Incorrect Integer (2147483647) is inserted into MySQL?

11 Answers11

Linked

Related