Re: Very Strange data corruption

[David Griffiths]

Michael Stassen wrote:
This comes up frequently. MySQL's behavior is explained in the manual 
<http://dev.mysql.com/doc/mysql/en/constraint_NOT_NULL.html>.  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]