Question about database value checking

2011-02-04 Thread Andy Wallace 

So, a problem popped up today that has caused us no end of hair-pulling, and
it brought to mind a similar issue that I found very, well, wrong.

If you have a table defined:

CREATE TABLE `tester_table` (
  `acnt`varchar(20) NOT NULL DEFAULT '',
  `method`  varchar(10) NOT NULL DEFAULT '',
  `card_num`varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`acnt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And try this:

INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', NULL);

That fails. and gives a nice error. But:

INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', 'A12345');

UPDATE tester_table set card_num = NULL WHERE acnt = 'test1';

That succeeds, but it puts an empty string into the card_num column. I
would have thought (hoped) that an error would be thrown in that case as
well.  On a similar note, the following table:

CREATE TABLE `tester_table2` (
  `acnt` varchar(20) NOT NULL,
  `required` enum('NO','DETAILS','RESULTS') NOT NULL,
  PRIMARY KEY (`acnt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Lets you insert:

INSERT INTO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT 
REAL');

Though it just puts an empty string into the required column.

Is there a setting for mysql to return errors in these cases? It seems silly to 
set
up an enum column, or a not null column, and not have the possible values 
enforced?

thanks,
andy


--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Question about database value checking

2011-02-04 Thread Peter He 

Are you using the strict SQL mode?  Check your my.cnf file.
 
Peter
 
 Date: Fri, 4 Feb 2011 14:08:01 -0800
 From: awall...@ihouseweb.com
 To: mysql@lists.mysql.com
 Subject: Question about database value checking
 
 So, a problem popped up today that has caused us no end of hair-pulling, and
 it brought to mind a similar issue that I found very, well, wrong.
 
 If you have a table defined:
 
 CREATE TABLE `tester_table` (
 `acnt` varchar(20) NOT NULL DEFAULT '',
 `method` varchar(10) NOT NULL DEFAULT '',
 `card_num` varchar(100) NOT NULL DEFAULT '',
 PRIMARY KEY (`acnt`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
 And try this:
 
 INSERT INTO tester_table (acnt, method, card_num)
 VALUES ('test1', 'push', NULL);
 
 That fails. and gives a nice error. But:
 
 INSERT INTO tester_table (acnt, method, card_num)
 VALUES ('test1', 'push', 'A12345');
 
 UPDATE tester_table set card_num = NULL WHERE acnt = 'test1';
 
 That succeeds, but it puts an empty string into the card_num column. I
 would have thought (hoped) that an error would be thrown in that case as
 well. On a similar note, the following table:
 
 CREATE TABLE `tester_table2` (
 `acnt` varchar(20) NOT NULL,
 `required` enum('NO','DETAILS','RESULTS') NOT NULL,
 PRIMARY KEY (`acnt`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 Lets you insert:
 
 INSERT INTO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT 
 REAL');
 
 Though it just puts an empty string into the required column.
 
 Is there a setting for mysql to return errors in these cases? It seems silly 
 to set
 up an enum column, or a not null column, and not have the possible values 
 enforced?
 
 thanks,
 andy
 
 
 -- 
 Andy Wallace
 iHOUSEweb, Inc.
 awall...@ihouseweb.com
 (866) 645-7700 ext 219
 --
 There are two ways to build software:
 Make it so simple that there are obviously no bugs,
 or make it so complex that there are no obvious bugs.
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=phe1...@hotmail.com

Re: Question about database value checking

2011-02-04 Thread Andy Wallace 

Thanks Peter, exactly what I was hoping for!
andy

On 2/4/11 3:11 PM, Peter He wrote:


Are you using the strict SQL mode?  Check your my.cnf file.

Peter


Date: Fri, 4 Feb 2011 14:08:01 -0800
From: awall...@ihouseweb.com
To: mysql@lists.mysql.com
Subject: Question about database value checking

So, a problem popped up today that has caused us no end of hair-pulling, and
it brought to mind a similar issue that I found very, well, wrong.

If you have a table defined:

CREATE TABLE `tester_table` (
`acnt` varchar(20) NOT NULL DEFAULT '',
`method` varchar(10) NOT NULL DEFAULT '',
`card_num` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`acnt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And try this:

INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', NULL);

That fails. and gives a nice error. But:

INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', 'A12345');

UPDATE tester_table set card_num = NULL WHERE acnt = 'test1';

That succeeds, but it puts an empty string into the card_num column. I
would have thought (hoped) that an error would be thrown in that case as
well. On a similar note, the following table:

CREATE TABLE `tester_table2` (
`acnt` varchar(20) NOT NULL,
`required` enum('NO','DETAILS','RESULTS') NOT NULL,
PRIMARY KEY (`acnt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Lets you insert:

INSERT INTO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT 
REAL');

Though it just puts an empty string into the required column.

Is there a setting for mysql to return errors in these cases? It seems silly to 
set
up an enum column, or a not null column, and not have the possible values 
enforced?

thanks,
andy


--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=phe1...@hotmail.com





--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org