Re: Very Strange data corruption

[David Griffiths]

Michael Stassen wrote:
This comes up frequently. MySQL's behavior is explained in the manual 
.  It begins:

That's interesting, and I guess one just has to accept it as part of the 
mysql philosphy. I don't agree, as I don't like the database trying to 
read my mind on what the best possible value. I agree that the 
client-code should do the same, but it's nice to have that last defense.

But the reason modern databases have foreign keys, primary keys, 
not-nulls, check constraints and data-metadata (char(5), INT, BIGINT, 
etc) is to prevent bad data from going in.. If no exception is thrown 

MyISAM tables do not have foreign key and check constraints.  I'm not 
sure what you mean by data-metadata.

Meta-data is information about the columns and tables. For example 
finding out that a column is an INT, not-null, etc. MySQL doesn't have a 
tonne of that stuff (some other databases litterally have hundreds of 
tables and views with information about what's in the database, and 
what's going on).

MySQL will only throw an exception if you try to explicitly insert a 
NULL into a NOT-NULL column in a single row insert.  It won't throw an 
exception in a multi-row insert or if you implicitly set a column to 
NULL by leaving it out.  This is in keeping with the need to support 
non-transactional tables.

Foreign keys are different.  They are only supported in transactional 
tables (InnoDB), so ROLLBACK is available.

I am not really familiar with MyISAM - we use only InnnoDB in our 
databases (other than the mysql datababase, of course) as we need the 
ACID-transactions and row-level locking.

There is a reason the MySQL developers do things the way they do.  
It's documented in the manual.  The driving principle is the need to 
support non-transactional tables, not some idea that no data validity 
checking should ever be done by the db.

When MyISAM gets transactions (in the next major version, I think), will 
this behaviour go away? In fact, I thought MyISAM had a basic 
begin-commit/rollback transaction already?

This is a philosophical matter. There have been excellent arguments 
for both
sides in previous threads on this topic. As it stands now, however, if 
you
require a db where the db itself can be set up to validate all data, then
MySQL is not the db for you. On the other hand, if you are willing to
validate your data, MySQL is fast, reliable, and cheap. Personally, I 
don't
We'll just have to code around it - it makes a strong case for adding 
unit tests to our development cycle.

find coding to validate input any more difficult than coding to handle
exceptions.
I find it exactly the opposite. An exception is a "try", two braces, a 
"catch", two more braces with a stack-trace in between. If one is 
thrown, it means there's a bug. And using InnoDB, I know that I can roll 
the whole transaction back.

David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[Michael Stassen]

This comes up frequently. MySQL's behavior is explained in the manual 
.  It begins:

| To be able to support easy handling of non-transactional tables, all
| columns in MySQL have default values.
|
| If you insert an ``incorrect'' value into a column, such as a NULL into a
| NOT NULL column or a too-large numerical value into a numerical column,
| MySQL sets the column to the ``best possible value'' instead of producing
| an error:
and concludes:
| This means that you should generally not use MySQL to check column
| content.  Instead, the application should ensure that it passes only legal
| values to MySQL...
David Griffiths wrote:
It's best practice to write unit tests for all your code, with 
calculated data to show what you expect. That data then gets compared to 
what is actually generated and if there is a discrepency, then you have 
a bug somewhere.

But the reason modern databases have foreign keys, primary keys, 
not-nulls, check constraints and data-metadata (char(5), INT, BIGINT, 
etc) is to prevent bad data from going in.. If no exception is thrown 
MyISAM tables do not have foreign key and check constraints.  I'm not sure 
what you mean by data-metadata.

because you are trying to put a BIGINT into an INT, then why throw one 
if you try to insert a NULL into a NOT-NULL column (assuming no DEFAULT 
is present)? Or what about foreign keys? Why not just quietly fail if a 
fk-constraint is violated?
MySQL will only throw an exception if you try to explicitly insert a NULL 
into a NOT-NULL column in a single row insert.  It won't throw an exception 
in a multi-row insert or if you implicitly set a column to NULL by leaving 
it out.  This is in keeping with the need to support non-transactional tables.

Foreign keys are different.  They are only supported in transactional tables 
(InnoDB), so ROLLBACK is available.

Go even farther. Say your SQL is just incorrect ("INSETR IN TO " instead 
of "INSERT INTO"). What if MySQL didn't throw an exception back to your 
PERL DBI or Java JDBC connection? After all, it's up to the developer to 
make sure their SQL syntax is correct.
Sure, MySQL could do that, but then no one would use it.  In any case, that 
hypothetical has no bearing on the discussion at hand.  The issue is what 
MySQL does, not what ridiculous thing it could do.

There is a reason the MySQL developers do things the way they do.  It's 
documented in the manual.  The driving principle is the need to support 
non-transactional tables, not some idea that no data validity checking 
should ever be done by the db.

The database has all sorts of constraints that can be applied to your 
data model. They should all have the same behaviour when violated.
The developers have explained why this is not so for MySQL.
This is a philosophical matter. There have been excellent arguments for both
sides in previous threads on this topic. As it stands now, however, if you
require a db where the db itself can be set up to validate all data, then
MySQL is not the db for you. On the other hand, if you are willing to
validate your data, MySQL is fast, reliable, and cheap. Personally, I don't
find coding to validate input any more difficult than coding to handle
exceptions.
David
Michael

Mike Johnson wrote:
From: David Griffiths [mailto:[EMAIL PROTECTED]
 

MySQL really should throw an exception/error rather than just quietly 
trim your data and accept it. When your data is critical, and your 
business depends on it, you can't have bad data quietly going into 
the database.
  

Someone correct me if I'm wrong, but isn't it considered best practice 
to validate data before it gets to the database?

I can't seem to find a source for this after a quick search on Google, 
though...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very Strange data corruption

[Mike Johnson]

From: Steve Meyers [mailto:[EMAIL PROTECTED]

> David Griffiths wrote:
> > I'm not sure what the sql standard says on the matter, but 
> Oracle, DB2 
> > and Postgres would through an exception. In fact, there is 
> a page on 
> > MySQL "gotachs" to document MySQL behaviour when it differs 
> > significnatly from other databases (like the first datetime 
> field in a 
> > table getting a value if none is provided during insert).
> 
> I assume you mean "timestamp", not "datetime".  Not trying to 
> be picky, I just don't want to confuse anyone out there.

While we're being picky, then, the first timestamp field is actually updated on any 
INSERT or UPDATE, not just given a value on the initial INSERT.   :)


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[Keith Ivey]

David Griffiths wrote:
But the reason modern databases have foreign keys, primary keys, 
not-nulls, check constraints and data-metadata (char(5), INT, BIGINT, 
etc) is to prevent bad data from going in.. If no exception is thrown 
because you are trying to put a BIGINT into an INT, then why throw one 
if you try to insert a NULL into a NOT-NULL column (assuming no 
DEFAULT is present)? Or what about foreign keys? Why not just quietly 
fail if a fk-constraint is violated?
But a column type isn't a constraint.  If integer types were about 
defining a range of acceptable data, then you'd be able to define one 
that held only integers between 1 and 100, for example.  Column types 
are about storage, and sorting, and maybe some other things, but they're 
not a good way to handle constraints, even in databases that throw 
exceptions.

Your general point is a reasonable way of looking at things, but it's 
not the MySQL philosophy, and that's one of the things that makes MySQL 
fast.

--
Keith Ivey <[EMAIL PROTECTED]>
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[Peter J Milanese]

This complicates the database. This discussion has come up hundreds of 
times on this list.

NULL/NOT-NULL is a basic check, and by saying NOTNULL, you're telling 
mysql to fail it.

Same thing with Syntax checks, they're just that.

The different INT fields are provided to make the database more efficient 
(and compact), as
the byte requirements for each are different. It is up to the developer to 
use them correctly.

The database (fields in general) is not expected to provide workflow, it 
is merely a repository for 
objects dictated by the workflow. The application front-ending the 
database should always handle
exceptions correctly. 

When you add all of this crap to a database, you get MSSQL. All that, and 
the glorious deadlocks
that make it the marvelous workhorse it is.


 







David Griffiths <[EMAIL PROTECTED]>
05/25/2004 04:04 PM
 
To: [EMAIL PROTECTED]
cc: 
Subject:    Re: Very Strange data corruption



It's best practice to write unit tests for all your code, with 
calculated data to show what you expect. That data then gets compared to 
what is actually generated and if there is a discrepency, then you have 
a bug somewhere.

But the reason modern databases have foreign keys, primary keys, 
not-nulls, check constraints and data-metadata (char(5), INT, BIGINT, 
etc) is to prevent bad data from going in.. If no exception is thrown 
because you are trying to put a BIGINT into an INT, then why throw one 
if you try to insert a NULL into a NOT-NULL column (assuming no DEFAULT 
is present)? Or what about foreign keys? Why not just quietly fail if a 
fk-constraint is violated?

Go even farther. Say your SQL is just incorrect ("INSETR IN TO " instead 
of "INSERT INTO"). What if MySQL didn't throw an exception back to your 
PERL DBI or Java JDBC connection? After all, it's up to the developer to 
make sure their SQL syntax is correct.

The database has all sorts of constraints that can be applied to your 
data model. They should all have the same behaviour when violated.

David


Mike Johnson wrote:

>From: David Griffiths [mailto:[EMAIL PROTECTED]
>
> 
>
>>MySQL really should throw an exception/error rather than just quietly 
>>trim your data and accept it. When your data is critical, and your 
>>business depends on it, you can't have bad data quietly going 
>>into the 
>>database.
>> 
>>
>
>Someone correct me if I'm wrong, but isn't it considered best practice to 
validate data before it gets to the database?
>
>I can't seem to find a source for this after a quick search on Google, 
though...
>
>
> 
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[Steve Meyers]

David Griffiths wrote:
I'm not sure what the sql standard says on the matter, but Oracle, DB2 
and Postgres would through an exception. In fact, there is a page on 
MySQL "gotachs" to document MySQL behaviour when it differs 
significnatly from other databases (like the first datetime field in a 
table getting a value if none is provided during insert).
I assume you mean "timestamp", not "datetime".  Not trying to be picky, 
I just don't want to confuse anyone out there.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[David Griffiths]

The client software ought to be range-checking the data before sending it to
the database.  If the client isn't even doing that kind of minimal-effort
check, how likely is it to be checking for exceptions?
 

Not sure what you code in, but in Java, you *HAVE* to catch 
SQLExceptions (or throw them up). Isn't it better to give the developer 
the option?

Also, most people who have replied seem to think that I/you are the 
author of the code.

If you use the MySQL client to do an IMPORT DATA (say as an export from 
another database), and you made a mistake in the DDL 
(data-definition-language) and used an INT when you should have used a 
BIGINT, you won't know.

David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[David Griffiths]

David Brodbeck wrote:
The client software ought to be range-checking the data before sending 
it to

the database.  If the client isn't even doing that kind of minimal-effort
check, how likely is it to be checking for exceptions?
That's not to say that an error or exception is a bad idea, but MySQL may be
constrained here by what the SQL standard says to do; I'm not sure.
 

I'm not sure what the sql standard says on the matter, but Oracle, DB2 
and Postgres would through an exception. In fact, there is a page on 
MySQL "gotachs" to document MySQL behaviour when it differs 
significnatly from other databases (like the first datetime field in a 
table getting a value if none is provided during insert).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[David Griffiths]

It's best practice to write unit tests for all your code, with 
calculated data to show what you expect. That data then gets compared to 
what is actually generated and if there is a discrepency, then you have 
a bug somewhere.

But the reason modern databases have foreign keys, primary keys, 
not-nulls, check constraints and data-metadata (char(5), INT, BIGINT, 
etc) is to prevent bad data from going in.. If no exception is thrown 
because you are trying to put a BIGINT into an INT, then why throw one 
if you try to insert a NULL into a NOT-NULL column (assuming no DEFAULT 
is present)? Or what about foreign keys? Why not just quietly fail if a 
fk-constraint is violated?

Go even farther. Say your SQL is just incorrect ("INSETR IN TO " instead 
of "INSERT INTO"). What if MySQL didn't throw an exception back to your 
PERL DBI or Java JDBC connection? After all, it's up to the developer to 
make sure their SQL syntax is correct.

The database has all sorts of constraints that can be applied to your 
data model. They should all have the same behaviour when violated.

David
Mike Johnson wrote:
From: David Griffiths [mailto:[EMAIL PROTECTED]
 

MySQL really should throw an exception/error rather than just quietly 
trim your data and accept it. When your data is critical, and your 
business depends on it, you can't have bad data quietly going 
into the 
database.
   

Someone correct me if I'm wrong, but isn't it considered best practice to validate 
data before it gets to the database?
I can't seem to find a source for this after a quick search on Google, though...
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

(U) RE: Very Strange data corruption

[Johnson, Michael]

CLASSIFICATION: UNCLASSIFIED

who knows

-Original Message-
From: Mike Johnson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 25, 2004 12:26 PM
To: David Griffiths; [EMAIL PROTECTED]
Subject: RE: Very Strange data corruption


From: David Griffiths [mailto:[EMAIL PROTECTED]

> MySQL really should throw an exception/error rather than just quietly 
> trim your data and accept it. When your data is critical, and your 
> business depends on it, you can't have bad data quietly going 
> into the 
> database.

Someone correct me if I'm wrong, but isn't it considered best practice to
validate data before it gets to the database?

I can't seem to find a source for this after a quick search on Google,
though...


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


Classification: UNCLASSIFIED


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[gerald_clark]

David Griffiths wrote:
MySQL really should throw an exception/error rather than just quietly 
trim your data and accept it. When your data is critical, and your 
business depends on it, you can't have bad data quietly going into the 
database.

David.
Mike Johnson wrote:
A value is not valid just because it fits into the defined field type.
All input values should be validated before inserting.
If you are getting sql errors on inserts and updates, you may have
a poorly designed user interface.
Duplicate key errors on inserts are probably the exception. They can
be valuable in eliminating lock-read-insert-unlock loops. An insert
error indicates an existing record.  A relative update can then be safely
made without locking or insert race conditions.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very Strange data corruption

[David Brodbeck]

> -Original Message-
> From: David Griffiths [mailto:[EMAIL PROTECTED]

> MySQL really should throw an exception/error rather than just quietly 
> trim your data and accept it. When your data is critical, and your 
> business depends on it, you can't have bad data quietly going 
> into the database.

The client software ought to be range-checking the data before sending it to
the database.  If the client isn't even doing that kind of minimal-effort
check, how likely is it to be checking for exceptions?

That's not to say that an error or exception is a bad idea, but MySQL may be
constrained here by what the SQL standard says to do; I'm not sure.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very Strange data corruption

[Mike Johnson]

From: David Griffiths [mailto:[EMAIL PROTECTED]

> MySQL really should throw an exception/error rather than just quietly 
> trim your data and accept it. When your data is critical, and your 
> business depends on it, you can't have bad data quietly going 
> into the 
> database.

Someone correct me if I'm wrong, but isn't it considered best practice to validate 
data before it gets to the database?

I can't seem to find a source for this after a quick search on Google, though...


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[David Griffiths]

MySQL really should throw an exception/error rather than just quietly 
trim your data and accept it. When your data is critical, and your 
business depends on it, you can't have bad data quietly going into the 
database.

David.
Mike Johnson wrote:
From: Jeff McKeon [mailto:[EMAIL PROTECTED]
 

Query: 

insert into
MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk
2,pin1,pin
2,TwoStage,Status,DateAssigned,DateDisabled,UserID) 
VALUES('NULL', '6889927707', '1', '8988169214000421398', 
'881621456175',
'', '', '881693156175', '62982149', '', '', '', '1307', '1',
'1085508771', 'NULL', 'jsm');

Always results in a ShipID field value of "2147483647" instead of
"6889927707"
Even if I just do a simple:
insert into MIS.simcard (ShipID) values ('6889927707');
It does the same darn thing.
ShipID is an Int(11) field
Version 4.0.15
If I change the first digit of the input from a 6 to any 
other digit, it
gets entered correctly.  Any idea what is going on here!?

Version 4.0.15
   

The max value of INT is 2147483647, lower than the value you're inserting (even 
when unsigned, which is 4294967295). That's why that's what's getting inserted.
Manual page is here:
http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html
Try converting the column to a BIGINT, the signed max alone is 9223372036854775807.
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very Strange data corruption

[Jeff McKeon]

Actually the Field type is fine.  It was user input error.  The person
who created the ship ID added an extra digit, normally they are only 9
digits long.  I didn't catch it until I looked at a list of all the
ShipID's together and then it stuck out like a sore thumb.

Thanks for all the explanations..

Jeff

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 25, 2004 2:41 PM
> To: Jeff McKeon
> Cc: [EMAIL PROTECTED]
> Subject: Re: Very Strange data corruption
> 
> 
> 
> Jeff,
> 
> You are trying to exceed the limits of the INTEGER column. 
> INTEGERs top out at 2GB-1 (or 2147483647). May I suggest you 
> change your table to use a larger integer type like BIGINT. 
> With BIGINT fields you can go all the way to 9223372036854775807.
> 
> MySQL will give you the nearest possible number in the event 
> of an overflow or an underflow. That's why you see the 
> "wrong" value for your column after the INSERT.
> 
> Respectfully,
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> 
> 
> 
> 
> 
> 
> 
>   
>
>   "Jeff McKeon"   
>
>   <[EMAIL PROTECTED]To:   
> <[EMAIL PROTECTED]>   
>   
>   .com>cc:
>
>Fax to:
>
>   05/25/2004 02:29 Subject:  Very 
> Strange data corruption
>   PM  
>
>   
>
>   
>
> 
> 
> 
> 
> Query:
> 
> insert into 
> MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk
> 2,pin1,pin
> 2,TwoStage,Status,DateAssigned,DateDisabled,UserID)
> VALUES('NULL', '6889927707', '1', '8988169214000421398', 
> '881621456175', '', '', '881693156175', '62982149', '', 
> '', '', '1307', '1', '1085508771', 'NULL', 'jsm');
> 
> Always results in a ShipID field value of "2147483647" 
> instead of "6889927707"
> 
> Even if I just do a simple:
> 
> insert into MIS.simcard (ShipID) values ('6889927707');
> 
> It does the same darn thing.
> 
> ShipID is an Int(11) field
> Version 4.0.15
> 
> If I change the first digit of the input from a 6 to any 
> other digit, it gets entered correctly.  Any idea what is 
> going on here!?
> 
> Version 4.0.15
> 
> Jeff
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 
> 
> 
> 
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[SGreen]

Jeff,

You are trying to exceed the limits of the INTEGER column. INTEGERs top out
at 2GB-1 (or 2147483647). May I suggest you change your table to use a
larger integer type like BIGINT. With BIGINT fields you can go all the way
to 9223372036854775807.

MySQL will give you the nearest possible number in the event of an overflow
or an underflow. That's why you see the "wrong" value for your column after
the INSERT.

Respectfully,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine








   
  
  "Jeff McKeon"
  
  <[EMAIL PROTECTED]To:   <[EMAIL PROTECTED]>  
   
  .com>cc: 
  
   Fax to: 
  
  05/25/2004 02:29 Subject:  Very Strange data corruption  
  
  PM   
  
   
  
   
  




Query:

insert into
MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk2,pin1,pin
2,TwoStage,Status,DateAssigned,DateDisabled,UserID)
VALUES('NULL', '6889927707', '1', '8988169214000421398', '881621456175',
'', '', '881693156175', '62982149', '', '', '', '1307', '1',
'1085508771', 'NULL', 'jsm');

Always results in a ShipID field value of "2147483647" instead of
"6889927707"

Even if I just do a simple:

insert into MIS.simcard (ShipID) values ('6889927707');

It does the same darn thing.

ShipID is an Int(11) field
Version 4.0.15

If I change the first digit of the input from a 6 to any other digit, it
gets entered correctly.  Any idea what is going on here!?

Version 4.0.15

Jeff

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very Strange data corruption

[Mike Johnson]

From: Jeff McKeon [mailto:[EMAIL PROTECTED]

> Query: 
> 
> insert into
> MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk
> 2,pin1,pin
> 2,TwoStage,Status,DateAssigned,DateDisabled,UserID) 
> VALUES('NULL', '6889927707', '1', '8988169214000421398', 
> '881621456175',
> '', '', '881693156175', '62982149', '', '', '', '1307', '1',
> '1085508771', 'NULL', 'jsm');
> 
> Always results in a ShipID field value of "2147483647" instead of
> "6889927707"
> 
> Even if I just do a simple:
> 
> insert into MIS.simcard (ShipID) values ('6889927707');
> 
> It does the same darn thing.
> 
> ShipID is an Int(11) field
> Version 4.0.15
> 
> If I change the first digit of the input from a 6 to any 
> other digit, it
> gets entered correctly.  Any idea what is going on here!?
> 
> Version 4.0.15

The max value of INT is 2147483647, lower than the value you're inserting (even when 
unsigned, which is 4294967295). That's why that's what's getting inserted.

Manual page is here:
http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html

Try converting the column to a BIGINT, the signed max alone is 9223372036854775807.


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very Strange data corruption

[Peter J Milanese]

Set the fieldtype to 'bigint'

It's the limit on int






"Jeff McKeon" <[EMAIL PROTECTED]>
05/25/2004 02:29 PM
 
To: <[EMAIL PROTECTED]>
cc: 
Subject:Very Strange data corruption


Query: 

insert into
MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk2,pin1,pin
2,TwoStage,Status,DateAssigned,DateDisabled,UserID) 
VALUES('NULL', '6889927707', '1', '8988169214000421398', '881621456175',
'', '', '881693156175', '62982149', '', '', '', '1307', '1',
'1085508771', 'NULL', 'jsm');

Always results in a ShipID field value of "2147483647" instead of
"6889927707"

Even if I just do a simple:

insert into MIS.simcard (ShipID) values ('6889927707');

It does the same darn thing.

ShipID is an Int(11) field
Version 4.0.15

If I change the first digit of the input from a 6 to any other digit, it
gets entered correctly.  Any idea what is going on here!?

Version 4.0.15

Jeff

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]