Simon Haldon wrote:
> This is a correction/distilled post of a problem I
> raised under the 'zero dates not working' thread
> The following query (copied from the mysql log
> file) kills the MySql server 4.1 NT
> (mysqld-nt.exe), but ONLY when using Connector
> J/3.1.x. Under Connector J/3.0.x it works fine. It
> happens on both jdk1.4.2 and jdk1.5.
> I'm using PreparedStatement setInt() to set the
> parameters represented by the ? in the query.
> select threadtitle, a.threadid, postedby,
> posteddate, textpart1 from threadheader a,
> threaddetail b where a.forumid=b.forumid and
> and a.forumid=? and a.deleteddate='1900-01-01
> 00:00:00' and b.deleteddate='1900-01-01 00:00:00'
> and posteddate=
> (select max(posteddate) from threaddetail where
> forumid=? and deleteddate='1900-01-01 00:00:00')
> This is getting the latest post from a forum in
> case you're wondering. The posteddate column
> contains non-null datetime data.
> Using MySql query browser or command line, the
> query runs with no problems in 0.015s.
> So this appears to be an issue with Connector/J
> This issue is currently preventing me upgrading so
> any help would be appreciated. I don't want to
> start rewriting subqueries as two separate
> I tried qualifying even the unique column names
> (e.g. using b.posteddate instead of just postedate
> outside of the subquery) but that isn't the
Not an issue with Connector/J, most likely an issue with the server's implementation of prepared statements.
Does the server crash if you do the following in the mysql client?
PREPARE foo FROM select threadtitle, a.threadid, postedby,
posteddate, textpart1 from threadheader a,
threaddetail b where a.forumid=b.forumid and
and a.forumid=? and a.deleteddate='1900-01-01
00:00:00' and b.deleteddate='1900-01-01 00:00:00'
(select max(posteddate) from threaddetail where
forumid=? and deleteddate='1900-01-01 00:00:00');
EXECUTE foo USING @var
I'm still confused as to why you think you need 'sentinal' values for dates though, if you want them to be NULL, then use NULL. 'Magic' values are problematic in most programs, especially for maintenance reasons.
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.