0002609: [MySQL] assigning empty value to datetime column silently fails

Viewing Issue Advanced Details [ Jump to Notes ]

[ View Simple ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0002609

[Quercus]

minor

always

04-16-08 14:31

05-14-08 15:11

Reporter

sgraf

View Status

public

Assigned To

ferg

Priority

normal

Resolution

fixed

Platform

Status

closed

Projection

none

OS Version

ETA

none

Fixed in Version

3.2.0

Product Version

3.1.5

Product Build

Summary

0002609: [MySQL] assigning empty value to datetime column silently fails

Description

Assigning a empty value '' to a datetime colum will silently ignore the query.

To reproduce:
mysqli_query($db_link, "CREATE TABLE a (id int(10), dt datetime default NULL)");
mysqli_query($db_link, "INSERT INTO a VALUES(1, now()),(2, now()), (3, now())");

mysqli_query($db_link, "UPDATE a SET dt = '' WHERE id = 1"); // Query will be ignored, dt will remain at its original value.
mysqli_query($db_link, "UPDATE a SET dt = NULL WHERE id = 2"); // This works as expected
mysqli_query($db_link, "UPDATE a SET dt = '0000-00-00 00:00:00' WHERE id = 3"); // This works as excpected

Expected result for the query "UPDATE a SET dt = ''"

Updating with an empty value should set the datetime column to 0000-00-00 00:00:00.

Steps To Reproduce

Additional Information

Attached Files

Relationships

Notes
(0003010) nam 04-24-08 16:53	MySQL JDBC Drivers: 3.1.14, 5.1.6 MySQL Server: 5.0.51a Community WinXP Issue not seen. The problem most likely is not from Resin/Quercus because Resin/Quercus just passes them along to your JDBC driver/database. The JDBC driver and/or database is ultimately responsible for the resulting query behavior.

(0003068) ferg 05-14-08 15:11	php/1f0a There is an issue with the JDBC driver and the value "0000-00-00 00:00:00" (throws exception)

Issue History
Date Modified	Username	Field	Change
04-16-08 14:31	sgraf	New Issue
04-24-08 16:53	nam	Note Added: 0003010
04-24-08 16:53	nam	version	3.1.6 => 3.1.5
05-14-08 15:11	ferg	Note Added: 0003068
05-14-08 15:11	ferg	Assigned To	=> ferg
05-14-08 15:11	ferg	Status	new => closed
05-14-08 15:11	ferg	Resolution	open => fixed
05-14-08 15:11	ferg	Fixed in Version	=> 3.2.0

Mantis 1.0.0rc3[^]

33 total queries executed.
29 unique queries executed.