List:General Discussion« Previous MessageNext Message »
From:Peter He Date:February 4 2011 11:11pm
Subject:RE: Question about database value checking
View as plain text  
Are you using the strict SQL mode?  Check your my.cnf file.
 
Peter
 
> Date: Fri, 4 Feb 2011 14:08:01 -0800
> From: awallace@stripped
> To: mysql@stripped
> 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.
> awallace@stripped
> (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=1
> 
 		 	   		  
Thread
Question about database value checkingAndy Wallace4 Feb
  • RE: Question about database value checkingPeter He5 Feb
    • Re: Question about database value checkingAndy Wallace5 Feb