MySQL Bugs: #9098: Not possible for clients to distinguish between string default values and other

Bug #9098	Not possible for clients to distinguish between string default values and other
Submitted:	10 Mar 2005 18:39	Modified:	1 Nov 2008 22:30
Reporter:	Mark Matthews	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	affects_connectors

Description:
Kevin Lloyd wrote:
> I'm having a problem with 'data truncation' and
> timestamp fields.
> 
> The fields in question have a default value of
> CURRENT_TIMESTAMP. It appears the drivers are
> attempting to insert this value into the database
> when I use the ResultSet.insertRow() method (and
> the field isn't actually updated).  When they do,
> I get the (new) data truncation
> warning/exception.
> 
> I've managed to replicate the issue by calling
> updateString("CURRENT_TIMESTAMP") on the field.
> 
> Am I missing some driver or database option here?
> 
> Kevin Lloyd
> 
> MySQL 4.1.10
> MySQL-AB JDBC Driver mysql-connector-java-3.1.7

It seems the issue is that the driver can't distinguish from the information given by the server the difference between CURRENT_TIMESTAMP and 'CURRENT_TIMESTAMP', so it sends 'CURRENT_TIMESTAMP'...We'll have to add a fix to look if the default value is CURRENT_TIMESTAMP and the column is a timestamp type, and if so, not send it as a string:

mysql> create table tsFoo(field1 TIMESTAMP NOT NULL, field2 VARCHAR(32) DEFAULT 'abcd');
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from tsFoo; 
+--------+-------------+------+-----+-------------------+-------+
| Field  | Type        | Null | Key | Default           | Extra |
+--------+-------------+------+-----+-------------------+-------+
| field1 | timestamp   | YES  |     | CURRENT_TIMESTAMP |       |
| field2 | varchar(32) | YES  |     | abcd              |       |
+--------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql>

How to repeat:
mysql> create table tsFoo(field1 TIMESTAMP NOT NULL, field2 VARCHAR(32) DEFAULT 'abcd');
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from tsFoo; 
+--------+-------------+------+-----+-------------------+-------+
| Field  | Type        | Null | Key | Default           | Extra |
+--------+-------------+------+-----+-------------------+-------+
| field1 | timestamp   | YES  |     | CURRENT_TIMESTAMP |       |
| field2 | varchar(32) | YES  |     | abcd              |       |
+--------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24290

To be fixed in 5.1 becasue changing this in earlier version could potentially break a lot of clients

Mark, what is there that needs to be fixed in 5.1?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".