Known Bug? Not Null Not Being Enforced Under Query Window

[pencil]

Hi everyone,

First of all, I have to thank Webyog for all the work on SQLyog. You guys did quite an impressive job.

I just ran into some problem and I suspect it being a bug. One of my MySQL statements in PHP failed to insert while I was trying to populate a table. I copied the statement with in question, executed in in a SQLyog query window and it worked. Then I examined my table definition, noticed that I have a not null contraint on one of the columns that was missing from the statement in question. I left out another column which has not null constraint as well this time, executed it in the query window. It worked as well. The same statement failed as I expected in PHP.

I am using SQLyog MySQL GUI 5.16. I did a search on the forum with no hit. Maybe this hasn't been brought up yet.

Thanks for your time!

Cheers

[peterlaursen]

“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'!

[pencil]

peterlaursen wrote on Sep 6 2006, 08:32 PM:

“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'!

Thanks for replying! I need to read a bit more on the 'non-strict' mode. Thanks for pointing that out!

[Author]

Posts