MySQL Bugs: #24344: useServerPrepStmts impacts time zone calculations

Bug #24344	useServerPrepStmts impacts time zone calculations
Submitted:	15 Nov 2006 19:38	Modified:	22 Feb 2007 14:07
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.4	OS:
Assigned to:		CPU Architecture:	Any
Tags:	time zone, useJDBCCompliantTimezoneShift, useServerPrepStmts

Description:
Connector/J is shifting DATETIME fields in prepared statements by the timezone offset (to GMT) when the connection with useServerPrepStmts=true.  When the connection is established with userServerPrepStmts=false, this shifting does not occur.  This causes different results dependent on the setting of useServerPrepStmts.

How to repeat:
See attached test case.

1.  Create a Calendar object.
2.  Establish a Connection with useServerPrepStmts=true.
3.  Use a PreparedStatement to insert the Calendar object into a table.
4.  Establish a Connection with useServerPrepStmts=false.
5.  Use a PreparedStatement to insert the Calendar object into a table.
6.  Compare the values inserted in steps 3 and 5 - they will be different by the time zone offset.

Suggested fix:
Correct behavior of of PreparedStatements so they are independent of the setting of useServerPrepStmts.

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/commits/16553

A note has been added to the 5.0.5 changelog.