MySQL Bugs: #5951: TIMESTAMP can't be set to NULL

Bug #5951	TIMESTAMP can't be set to NULL
Submitted:	7 Oct 2004 9:19	Modified:	7 Oct 2004 13:40
Reporter:	Scott Douglass	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	4.1.5	OS:	Linux (Linux x86)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
EJB/CMP application works with MySQL 4.0, but can't update/insert records in MySQL 4.1 because TIMESTAMP columns can't be set to NULL. 

[2004-10-07 01:23:12.011] Caused by: com.caucho.ejb.CreateExceptionWrapper: java.sql.SQLException: Column 'last_modified' cannot be null

Using MySQL Connector/Java 3.1.4 and InnoDB tables with UTF-8 encoding. Not using MaxDB mode.

How to repeat:
Fire up your EJB container, create a CMP Entity Bean, well there's really a dozen steps here some of which will depend on your container. Try to insert of update your Bean. I'll have to make a sample and upload the source, it's too hard to explain.

Suggested fix:
Make TIMESTAMP work like it did in 4.0. I need Unicode support, but I don't need a new flavor of TIMESTAMP ;-)

I could reproduce this problem with following test case:

create table a(id int not null, time timestamp, primary key (id)) default charset utf8;
insert into a values(1,null);
commit;

Now if I do 
select * from a;

result is

+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2004-10-07 12:11:00 |
+----+---------------------+
1 row in set (0.00 sec)

I should have looked from the manual. The manual section 12.3.1.2 TIMESTAMP Properties as of MySQL clearly says:

If a DEFAULT value is specified for the first TIMESTAMP column in a table, it is not ignored. The default can be CURRENT_TIMESTAMP or a constant date and time value.

DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP column. For any other TIMESTAMP column, DEFAULT NULL is treated as DEFAULT 0.

Any single TIMESTAMP column in a table can be set to be the one that is initialized to the current timestamp and/or updated automatically.

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

* With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
* With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
* With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
* With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
* With a constant DEFAULT value and with ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default and is automatically updated.

In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify ON UPDATE to get auto-update without also having the column auto-initialized.)

I read the manual too. What it boils down to is that the default behavior for TIMESTAMP when NOT using the MaxDB option is supposed to be the same as the behavior in 4.0. That doesn't seem to be the case however, otherwise I would not be getting an SQL exception from the JDBC driver.
------------------------------------------------------------------------------
http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html

Example. These statements are equivalent:

CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                             DEFAULT CURRENT_TIMESTAMP);

---------------------------------------------------------------------------
The only way I've been able to work around this is to add code to set my last_modified to a java.util.Date object before I do the insert or update, which is something I never had to do before trying to use 4.1.x

The funny thing is, I can insert and update using the mysql client (mysql) without this problem. It's only Java code that's having the problem. So it could be a MySQL Connector problem.  I'll change the category of the bug to MySQL Connector/J.

As of MySQL/Innodb 4.1.6 version TIMESTAMP columns can store NULL values. To create such a column, you must explicitly specify the NULL attribute in the column specification. (Unlike all other column types, TIMESTAMP columns are NOT NULL by default.)

e.g. create table c(id int not null,time timestamp null);

This sounds very similar (and most likely is) a duplicate of BUG#5510. If you add 'useServerPrepStmts=false' to your JDBC url, does the problem go away? If so, then it is a duplicate of BUG#5510 (which is fixed in MySQL-4.1.6, related _only_ to server-side prepared statements). MySQL-4.1.6 will be released imminently.

'useServerPrepStmts=false' solved the problem

I look forward to 4.1.6! Thanks!