MySQL Strange Insert Error

Question

I am getting strange error while inserting data into mysql table column.

Details:

Create Table Statement:

     CREATE TABLE `product_offer_line` (
  `object_id` int(19) NOT NULL default '0',
  `snapshot_id` int(19) NOT NULL default '0',
  `domain` varchar(255) NOT NULL,
  `description` varchar(255) default NULL,
  `name` varchar(255) NOT NULL,
  `priority` int(10) default NULL,
  `parent_offer_line` varchar(255) default NULL,
  PRIMARY KEY  (`object_id`,`snapshot_id`,`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Note here object_id as int(19), and my insert statement is ("INSERT INTO product_offer_line VALUES('1000000000001','1','NL','Radio and TV','TV','0','NULL')");

Now when I run this insert and than

select * from product_offer_line;

+------------+-------------+--------+-----------------------+------+----------+-------------------+ | object_id | snapshot_id | domain | description | name | priority | parent_offer_line | +------------+-------------+--------+-----------------------+------+----------+-------------------+ | 2147483647 | 1 | NL | Standard radio and TV | CATV | 0 | NULL | +------------+-------------+--------+-----------------------+------+----------+-------------------+

My question here is that even if am entering 1000000000001 why does select statement show 2147483647 ?

I have tried creating object_id as int(64) also but even than I am having same issue.

I am not sure what is going wrong here and so would really appreciate if someone can share some insights on the issue.

Thanks.

As suggest I have used BIGINT instead of INT and it is storing fine. Thank you for your inputs. — Rachel
– Rachel, Commented Nov 10, 2009 at 19:43
it still amazes me how many questions here relate to mysql and its shortcomings, inconsistencies and bugs .. as if there wheren't viable alternatives, especially postgresql — pfote
– pfote, Commented Nov 10, 2009 at 20:06

Justin Lucas · Accepted Answer · 2009-11-10 19:41:02Z

3

A mysql int data type can only store a number up to 4294967295 (unsigned). If you try to store a number greater than that, such as 1000000000001, then mysql will store it as the max number possible (2147483647 if the column is signed). Change the column to a bigint data type and you should be good to go.

answered Nov 10, 2009 at 19:41

Justin Lucas

2,3111 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Justin Lucas Over a year ago

Quotes don't make a difference. Mysql will still enter it as a number if the column is a numeric data type

Greg · Accepted Answer · 2009-11-10 19:38:21Z

2

1000000000001 is beyond the range of an INT. You need to use BIGINT instead.

The number is brackets relates to how it is displayed, not the storage size.

answered Nov 10, 2009 at 19:38

Greg

323k55 gold badges378 silver badges338 bronze badges

Comments

Daniel Schneller · Accepted Answer · 2009-11-10 19:47:27Z

MySQL is very lenient on values without the valid range for any column. In your case you are exceeding the value range for an INT column. For an INT(64) column this does not mean you can store actual numbers with up to 64 digits, but only that MySQL should display them with that length.

You will have to define the column as a BIGINT or a VARCHAR, depending on whether the quoting in your INSERT was intentional. If for example you have to store numbers with leading zeroes, a VARCHAR might be a better choice.

You can control whether MySQL silently converts invalid values to the closest match it can hold, or fail you with an error. You can control this in some detail with the SQL Server Mode. See this MySQL 5.0 manual page for the possible values and how they affect MySQL's behavior.

For your particular case you should have a look at the STRICT modes which specify what will happen on invalid values in particular.

Myles · Accepted Answer · 2009-11-10 19:41:34Z

0

It's been awhile since I used mysql, but is it because you quoting your number?

answered Nov 10, 2009 at 19:41

Myles

21.6k4 gold badges31 silver badges38 bronze badges

1 Comment

Justin Johnson Over a year ago

No, quotes do not make a difference in this case and are optional for numeric types. The only time quotes matter is if you are working with the null value or mysql functions.

dnagirl · Accepted Answer · 2009-11-10 19:42:00Z

0

use BIGINT rather than INT(64) (ref). If your numbers are guaranteed to be positive, you can also increase your range by declaring the field UNSIGNED.

answered Nov 10, 2009 at 19:42

dnagirl

20.5k13 gold badges86 silver badges127 bronze badges

Collectives™ on Stack Overflow

MySQL Strange Insert Error

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related