57

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     ('zma@zm.com','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)
Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Ryk
  • 3,072
  • 5
  • 27
  • 32
  • Is it just me or are there only 12 fields and yet you are trying to insert 15 values? – Ross Aug 02 '10 at 03:06

6 Answers6

43

Use the DEFAULT keyword instead:

INSERT INTO users (TimeZoneId) VALUES (DEFAULT);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks Bill, this works, but as for the answer, I am still investigating as to why it all of a sudden appeared today, and not before. SO it could be NHibernate, or MySQL not sure. – Ryk Aug 02 '10 at 03:59
  • 3
    If the behavior changed, then you must have changed something in your *environment*, your *code*, or your *data*. I can't guess which one. Or else the behavior did not change -- it worked this way all along and you never noticed. That happens to me sometimes. – Bill Karwin Aug 10 '10 at 15:53
  • when you are hard writing a query, not mentioning the column will make mysql use the default value. However, if you explicitly mention null as value, it will throw exception. This happens when you are using a programming library that inserts a bean value as is (null), in this case, specifying the default value to the bean variable will do the job: String name = "default"; – user666 Feb 27 '19 at 06:45
6

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.

Mark Eirich
  • 10,016
  • 2
  • 25
  • 27
  • This could be it, I am investigating now, since this was working before and just started complaining about NULL inserts. – Ryk Aug 02 '10 at 03:39
  • When you insert nulls on a multi-row insert, MySQL gives a warning instead of failing. Perhaps it was always doing a multi-row insert before, or something odd like that. – Mark Eirich Aug 02 '10 at 04:19
6

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     ('zma@zm.com','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)
User123342234
  • 1,455
  • 14
  • 17
  • Yes, but the moment I allow null's it actually insert nulls and not the default values. – Ryk Aug 02 '10 at 03:50
  • Ah sorry should of read the q better :D. The DEFAULT keyword should be used then OR you leave them out of the INSERT process altogether. eg. INSERT INTO `users` (all_fields_except_nulls) VALUES (all_values_except_nulls) – User123342234 Aug 02 '10 at 03:53
  • Thats what the previous 2 answers said. Thanks a lot though, appreciate the help. – Ryk Aug 02 '10 at 03:56
  • 1
    We have the same problem. Two databases have exactly the same schema with a field `updated_at timestamp DEFAULT '0000-00-00 00:00:00' NOT NULL`. The problem is that on one machine we can insert the values (and MySQL itself is even generating updated_at timestamp automatically), but on another machine MySQL is yelling that it cannot insert NULL into updated_at. So I guess, MySQL has changed something between versions. Or maybe there is some setting which differs between the servers. Or maybe this bug is at fault: https://bugs.mysql.com/bug.php?id=72628 – JustAMartin May 07 '15 at 06:55
2

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.

Chusya
  • 90
  • 2
  • 9
0

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
    ('zma@zm.com','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.

Ross
  • 511
  • 3
  • 12
0

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.

Ankur Mukherjee
  • 3,777
  • 5
  • 32
  • 39