MySQL - Cannot insert NULL value in column, but I have a default value specified?

I have a table in MySQL that have a few columns that have default values specified, but when I try to insert a row, (not specifying values for those default columns), it throws an error saying I cannot insert NULL values.

Here is the table example;

CREATE TABLE `users` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `UniqueName` varchar(120) NOT NULL,
  `Password` varchar(1000) NOT NULL,
  `PublicFlag` tinyint(1) NOT NULL,
  `NoTimesLoggedIn` int(10) unsigned NOT NULL DEFAULT '0',
  `DateTimeLastLogin` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00',
  `UserStatusTypeId` int(10) unsigned NOT NULL,
  `Private` tinyint(1) NOT NULL DEFAULT '0',
  `SiteName` varchar(255) NOT NULL,
  `CountryId` int(10) NOT NULL DEFAULT '0',
  `TimeZoneId` varchar(255) NOT NULL DEFAULT 'UTC',
  `CultureInfoId` int(10) unsigned NOT NULL DEFAULT '0',
  `DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UserCreated` int(10) unsigned NOT NULL,
  `LastUpdatedBy` int(10) unsigned NOT NULL,
  `DateLastUpdated` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `UniqueName_UNIQUE` (`UniqueName`),
  KEY `Index 3` (`SiteName`)
)

It complains about TimeZoneId, and when I populate TimeZoneId, it complains about CultureInforId.

I am using MySQL Version: 5.1.43-community

Here is the insert query I am trying to insert, grabbed from NHibernate Profiler:

INSERT INTO Users
           (UniqueName,
            Password,
            PublicFlag,
            NoTimesLoggedIn,
            DateTimeLastLogin,
            SiteName,
            TimeZoneId,
            DateCreated,
            DateLastUpdated,
            Private,
            CountryId,
            CultureInfoId,
            UserCreated,
            LastUpdatedBy,
            UserStatusTypeId)
VALUES     ('[email protected]','u1uhbQviLp89P9b3EnuN/Prvo3A4KVSiUa0=',1,
0,'1/01/1971 12:00:00 AM','V9O1T80Q6D',NULL,'2/08/2010 2:13:44 AM',
'2/08/2010 2:13:44 AM',0, NULL, NULL, 4, 4,31)

Use the DEFAULT keyword instead:

INSERT INTO users (TimeZoneId) VALUES (DEFAULT);

Do not insert NULL values. I'm assuming you were trying this syntax:

INSERT INTO users VALUES (null, 'Jones', 'yarg', 1, null, null, null);

Instead, use this syntax:

INSERT INTO users SET UniqueName='Jones', Password='yarg';

For more info, see the MySQL docs on INSERT.

You have "NOT NULL" set on fields that you are trying to INSERT NULL on.

eg. CountryId, CultureInfoId, TimeZoneId

execute the following:

ALTER TABLE `users` MODIFY `CountryId` int(10) DEFAULT '0' NULL;
ALTER TABLE `users` MODIFY `CultureInfoId` int(10) unsigned DEFAULT '0' NULL;
ALTER TABLE `users` MODIFY `TimeZoneId` varchar(255) DEFAULT 'UTC' NULL;

EDIT: Didn't realize he wanted the default value instead of NULL on "null" insert. Basically as already has been suggested use the DEFAULT keyword in place of NULL on the values.

OR leave the NULL fields and values out altogether and mysql will use the defined defaults eg.

INSERT INTO Users
       (UniqueName,
        Password,
        PublicFlag,
        NoTimesLoggedIn,
        DateTimeLastLogin,
        SiteName,
        DateCreated,
        DateLastUpdated,
        Private,
        UserCreated,
        LastUpdatedBy,
        UserStatusTypeId)
VALUES     ('[email protected]','u1uhbQviLp89P9b3EnuN/Prvo3A4KVSiUa0=',1,
0,'1/01/1971 12:00:00 AM','V9O1T80Q6D','2/08/2010 2:13:44 AM',
'2/08/2010 2:13:44 AM',0, 4, 4,31)

The documentation says that from version 5.6 you need to access fields by default.

I've read this post about this trouble I've fixed it this way:

mysql> show global variables like 'explicit_defaults_for_timestamp';

And if your field has ON value change to OFF

mysql> set global explicit_defaults_for_timestamp=0;

That's all.

As an alternative to using the DEFAULT keyword you can also just not specify values for the fields which you want to have default values. For instance if you just removed TimeZoneId, CountryId and CultureInfoId from your query entirely those columns will receive the default values automatically:

INSERT INTO Users
	(UniqueName,
	Password,
	PublicFlag,
	NoTimesLoggedIn,
	DateTimeLastLogin,
	SiteName,
	DateCreated,
	DateLastUpdated,
	Private,
	UserCreated,
	LastUpdatedBy,
	UserStatusTypeId)
VALUES
	('[email protected]','u1uhbQviLp89P9b3EnuN/Prvo3A4KVSiUa0=',1,0,
	'1/01/1971 12:00:00 AM','V9O1T80Q6D','2/08/2010 2:13:44 AM',
	'2/08/2010 2:13:44 AM',0,4,4,31)

I'm not sure how that would work in the context of NHibernate however as that part of the question wasn't quite as well explained.

TimeZoneIdvarchar(255) NOT NULL DEFAULT 'UTC', CultureInfoId` int(10) unsigned NOT NULL DEFAULT '0',

For this fields you have set constraints as "Not Null" and hence values inserted can't be null and hence either alter the table structure or just not specify values for the fields which you want to have default values.