Re: Subquery using SELECT MAX() on DATETIME field kills Mysql NT server on latest Connector/J
Simon Haldon wrote:
> Hi,
>
> This is a correction/distilled post of a problem I
> raised under the 'zero dates not working' thread
> below.
>
> 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
> a.threadid=b.threadid
> 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
> 3.1.x.
>
> This issue is currently preventing me upgrading so
> any help would be appreciated. I don't want to
> start rewriting subqueries as two separate
> queries.
>
> 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
> problem.
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
a.threadid=b.threadid
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');
SET @var=your-int-val
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.
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html