3 messages in com.mysql.lists.plusplusRe: a new query inside a result loop ...| From | Sent On | Attachments |
|---|---|---|
| Shigeya Suzuki | 31 Aug 2006 11:30 | |
| Warren Young | 31 Aug 2006 12:41 | |
| Shigeya Suzuki | 31 Aug 2006 15:54 |
| Subject: | Re: a new query inside a result loop and exception |
|---|---|
| From: | Shigeya Suzuki (shig...@wide.ad.jp) |
| Date: | 08/31/2006 03:54:25 PM |
| List: | com.mysql.lists.plusplus |
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