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:
Category:Connector / J Severity:S1 (Critical)
Version:4.1.5 OS:Linux (Linux x86)
Assigned to: Mark Matthews CPU Architecture:Any

[7 Oct 2004 9:19] Scott Douglass
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 ;-)
[7 Oct 2004 9:39] Jan Lindström
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);

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)
[7 Oct 2004 10:24] Jan Lindström
I should have looked from the manual. The manual section 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 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.)
[7 Oct 2004 11:20] Scott Douglass
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.

Example. These statements are equivalent:

                             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.
[7 Oct 2004 12:57] Jan Lindström
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);
[7 Oct 2004 13:40] Mark Matthews
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.
[7 Oct 2004 14:26] Scott Douglass
'useServerPrepStmts=false' solved the problem

I look forward to 4.1.6! Thanks!