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

[10 Mar 2005 18:39] Mark Matthews
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>
[25 Apr 2005 20:36] Bugs System
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
[6 May 2005 8:59] Michael Widenius
To be fixed in 5.1 becasue changing this in earlier version could potentially break a lot of clients
[29 Sep 2008 21:34] Konstantin Osipov
Mark, what is there that needs to be fixed in 5.1?
[2 Nov 2008 0:00] Bugs System
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".