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

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ 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

Status

closed

Product Version

3.1.5

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.

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.