“noticed that I have a not null contraint on one of the columns that was missing from the statement in question”
Ok .. as you had a NOT NULL in the definition what was than inserted into actual column? An empty string? A '0' (zero) ?
What is the MySQL version? It looks like an issue with MySQL 5 and strict_mode. Read about SQL_modes:
http://www.webyog.com/faq/28_72_en.html.
An example:
with this definition:
Code:
CREATE TABLE `TableName1` (
`id` bigint(20) NOT NULL auto_increment,
`txt1` varchar(20) NOT NULL,
`txt2` varchar(20) default NULL,
`num1` bigint(20) NOT NULL,
`num2` bigint(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
this SQL
Code:
insert into TableName1 (txt2, num2) values ('blabla',47);
select * from TableName1;
raises no error and returns
Code:
id txt1 txt2 num1 num2
—— —— —— —— ——
1 blabla 0 47
This is the SERVER behaviour in 'non-strict' mode.
Every GUI tools sets the sql_mode = ''.
It would raise too many problems with a GUI if they did not!
and BTW: before MySQL 5.0 the server behaviour was ALWAYS 'non-strict'!
