[RESOLVED] mysql default value bug

mark_kccs

I have upgraded mysql to v5.0.51a and since then I get an error when using this query

query: INSERT INTO login(user) VALUES ('User')

error: #1364 - Field 'comments' doesn't have a default value

query: INSERT INTO login(comments) VALUES ('comments')

error: #1364 - Field 'user' doesn't have a default value

This did not happen with the previous version on the same table as follows:

CREATE TABLE IF NOT EXISTS login (
id int(10) unsigned NOT NULL auto_increment,
user varchar(100) NOT NULL,
comments text NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Many thanks

laserlight

In other words, your SQL statement should be:

INSERT INTO login (user, comments) VALUES ('User', 'comments')

You can leave out the id column since it will be provided by the auto increment. If you really want to leave out the comments column, then you either remove the NOT NULL requirement, or make say, '' (empty string) or some other suitable text as the default. Providing an empty string default is probably best, though with the given context I would argue that you should validate that the user has actually made a comment.

mark_kccs

Hi laserlight

Thanks for the reply.

I believe I have solved this issue as follows:

Version 5 my.ini contains the the option

Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

which was not in my previous version. Hence removing the first statement STRICT_TRANS_TABLES has worked.

Many thanks