MySQL: INSERT error "Cannot be NULL" -- DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

Question

In fact there are so many similar questions, I could not solve this problem. I am using codeigniter framework. When I call insert method of ActiveRecord by passing a php object, it send all properties either by its value or as null. It causes cannot be null. error.

Table Structure:

CREATE TABLE `scheduledTasks` (
  `id` int(11) NOT NULL,
  `type` varchar(255) COLLATE utf8_bin NOT NULL,
  `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `executionTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ownerUserId` int(11) DEFAULT NULL,
  `subjectUserId` text COLLATE utf8_bin,
  `detail` text COLLATE utf8_bin,
  `isExecuted` int(1) DEFAULT '0'
);
ALTER TABLE `scheduledTasks`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `scheduledTasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Query I tried:

INSERT INTO `scheduledTasks` (`id`, `type`, `createTime`, `executionTime`, `ownerUserId`, `detail`, `isExecuted`)
VALUES (NULL, 'QuoteRequest', NULL, NULL, '926', NULL, NULL)

Mysql Version:

+-------------------------+
| VERSION()               |
+-------------------------+
| 5.7.17-0ubuntu0.16.04.1 |
+-------------------------+

I have

explicit_defaults_for_timestamp | OFF

and sql mode is

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

If NULL can trigger the DEFAULT_TIMESTAMP, then NOT NULL would be your problem. Otherwise, try inserting nothing for createTime and executionTime and see if that will trigger DEFAULT_TIMESTAMP. The manual is tricky. dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html — Anthony Rutledge
– Anthony Rutledge, Commented Mar 5, 2017 at 22:13
The manual says that you can use NULL to set a TIMESTAMP, but it does not say the same about setting DATETIME. — Anthony Rutledge
– Anthony Rutledge, Commented Mar 5, 2017 at 22:18

Anthony Rutledge · Accepted Answer · 2017-03-07 14:59:58Z

5

Discussion

The MySQL 5.7 manual states that...

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

The manual does not say you can do this for DATETIME fields. The best thing to do would be to supply no values in your INSERT query for the createTime and executionTime fields. That should give you the DEFAULT_TIMESTAMP. If that fails, well, I tried.

INSERT INTO `scheduledTasks` (`id`, `type`, `createTime`, `executionTime`, `ownerUserId`, `detail`, `isExecuted`)
VALUES (NULL, 'QuoteRequest', , , '926', NULL, NULL)

Also, keep this in mind about MySQL default values, even though your DATETIME columns do have an explicit DEFAULT clause.

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

Finally, about strict mode in the MySQL Manual:

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 12.7, “Data Type Default Values”.

Conclusion

In summary, if you are in strict mode (which you are, STRICT_TRANS_TABLES) and have DATETIME columns set to NOT NULL DEFAULT CURRENT_TIMESTAMP, and then you supply NULL values during an INSERT, and then subsequently you get a "cannot be NULL" error, ... next time around do not supply values to the DATETIME fields.

If your framework cannot be setup to omit values during INSERT, and changing the SQL mode does not work, then altering the table to use (gasp) TIMESTAMP may be your only option to use NULL in strict mode and have a DEFAULT TIMESTAMP appear.

edited Mar 7, 2017 at 14:59

answered Mar 5, 2017 at 22:24

Anthony Rutledge

7,7742 gold badges42 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1265083 Over a year ago

Thanks for your reply. I've changed the type to TIMESTAMP and it works as I wanted to be.

Anthony Rutledge Over a year ago

Just be careful about the range of dates you need to use. I believe TIMESTAMP starts in 1970 and is only good until 2038.

user1265083 Over a year ago

Thanks. This table is for scheduled tasks, so, I think 2038 will be enough for now. :D

Collectives™ on Stack Overflow

MySQL: INSERT error "Cannot be NULL" -- DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

1 Answer 1

Discussion

Conclusion

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Discussion

Conclusion

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related