List:MySQL++« Previous MessageNext Message »
From:Shigeya Suzuki Date:August 31 2006 10:54pm
Subject:Re: a new query inside a result loop and exception
View as plain text  
Thank you very much for comment.

Warren Young wrote:
> Shigeya Suzuki wrote:
>> Now, when the inner query (actually, executing a stored procedure to add
>> a record to two tables) fail and raise an exception, while trying to
>> unwind stack frame, program stuck at following read().. following is the
>> stack trace of the moment.
> 
> It's crashing down in the MySQL C library.  I can't really help you
> there.  If you cannot avoid the problem, but must fix it, you'll need to
> take this up on the main MySQL mailing list, where this is on topic.

I see.

>> 1) Why this is happening?
> 
> Most likely it's because the MySQL network protocol is not made to allow
> overlapping queries on a single connection, and there's some fault
> caused when you try to get around this limitation by using two
> connections.  Because of this limitation, most people write their code
> so they don't need overlapping queries, so the problem you're running
> into rarely manifests itself.

Actually, I'm using two connections as I wrote (I believed that it is
impossible to handle two queries on a single connection - as typical on
other database implementation).

>> 2) Is there any way to exec a stored procedure, while reading result
>> using fetch_row() other than using two connections like this?
>
> Is it absolutely required that the two queries be executed concurrently?
>  Could you instead build up a list in memory from the first query and
> then iterate over that list to build the inner loop queries?

Why I need:

- I have relatively large data set which does not fit into memory, and
I'd like to store as much as 2000 times more data on the table (or more)
currently, the table has 8 Million records.

- I have to process output of outer query's result to create second query.

Thus, reading all into memory and process is not an option...

Of course, I can limit amount of data to process and create one more
outer loop, it is possible to do, but it's messy and that's what I don't
want to do.

To be honest, I prefer to switch database product if I can't do this
with MySQL, since I know other product can do this sort of technique
without problem.

And note that, at least at this moment, by avoiding the situation which
cause exception, the program runs smoothly without problem.


>> I'm currently using MySQL 5.0.24 on MacOS X, but by some reason, I built
>> binary by myself.
> 
> If you decide to take this problem up with the MySQL C library people on
> the main MySQL list, they'll ask you to try the native binary.  I would
> also try using the Fink package.

I see. I will prepare well before I start discussing with them.

I will probably test against MacOS X binary package first but later.
(I had unhappy time with binary package - that's the reason why I'm
using pkgsrc based from-source build)

And making my program work is my current priority and it's currently
working, so I will investigate this later. Since this program is just
for research thus I can ignore some error case at this moment.

>> I will try on NetBSD too, but I doubt OS is the source of problem..
> 
> I wouldn't be so quick to discount that possibility.  OS X has a number
> of differences from the other BSDs.

Yes, I agree on that.

Actually, This is different topic but anyway, yesterday I found memory
mapping of MacOS X and the way MySQL use memory map cause very bad
swapping behavior, which does not happen on NetBSD (at least). I believe
this is caused by memory allocation policy of MacOS X kernel.

This behavior is very nasty, and memory used for memory map does not
appear on "top" or activity monitor. I could't figure out what's
happening for while.

shigeya
Thread
a new query inside a result loop and exceptionShigeya Suzuki31 Aug
  • Re: a new query inside a result loop and exceptionWarren Young31 Aug
    • Re: a new query inside a result loop and exceptionShigeya Suzuki1 Sep