Problem with insert ... select in JDBC?

[Nick Scholtz]

I'm having a problem that is confusing me to no end and I'm curious if it's familiar to
anyone.

I have written a number of web applications using a java-based template system to connect
to a mysql back end (dxp.plands.com has details of the template language that I use).

I've encountered a situation in which I am getting different results running the same
query through the application or from the command line.

I am trying to use the insert . . . select command (details at the end of the message) and
when I run the query on the command line it works properly but run through java it will,
in some
circumstances only insert 1 line of data rather than then entire dataset.

I am using JDBC 2.0.4. I intend to upgrade to the new driver but I'd still like to know
anyone has seen this problem.

Here is a sample query. The context is this. I want to track bandwidth usage for various
ports. There is a database that stores a running counter for each port on the switch. To
determine usage one of
the things I need to first find out the base count at the start of the time period. To do
this I first find the earliest record for the time period I'm interested in and then find
the value associated
with that record (in a second query) here is the query to select the earliest times into a
temp table.


insert into monitor_temp.dv_notetime(ds_id,value_time,value) 
select ds_id,min(value_time),1 from monitor.dv where value_time > [date] group by ds_id

There are 28 distinct ds_id values so this should return 28 rows. When I run this query on
the command
line it does, in fact load 28 rows into the temp table. When I run it through the
application I
am getting only 1 row inserted into the temp table.

Part of what strikes me as odd about this is I'm not sure what sort of mangling it would
take to
generate that result (other than adding a "limit 1" to the end of the select). As I
understand
it the insert . . . select should be entirely internal to mysql. It shouldn't try to load
the
dataset into java memory and then insert it back into MySQL.

Any suggestions to help me isolate this error would be greatly appreciated.

Thanks,
-- 
Nick Scholtz
Planetary Data Systems