Re: Issues with multi-queries - Paul Martin - com.mysql.lists.plusplus

13 messages in com.mysql.lists.plusplusRe: Issues with multi-queries

From	Sent On	Attachments
Paul Martin	07 Nov 2007 09:30
Warren Young	07 Nov 2007 12:56
Paul Martin	07 Nov 2007 13:05
Warren Young	07 Nov 2007 13:38
Paul Martin	07 Nov 2007 15:06
Warren Young	07 Nov 2007 16:07
Maarten Schrijvers	08 Nov 2007 03:22
Paul Martin	08 Nov 2007 14:33
Warren Young	09 Nov 2007 15:02
Paul Martin	09 Nov 2007 18:31
Warren Young	09 Nov 2007 19:10
Ian Daysh	12 Nov 2007 01:10
Warren Young	12 Nov 2007 22:12

Subject:	Re: Issues with multi-queries
From:	Paul Martin (pmar...@nltinc.com)
Date:	11/07/2007 03:06:46 PM
List:	com.mysql.lists.plusplus

My point is not to debug my own code, but figure out why the server keeps dropping the connection. I have been looking at the user manual and will adopt the templates for some of my calls in the near future. I can also make use of the transaction class, sure, change the getch (only a simple way to exit the program). The con object is made global and the local one I removed. The endl's are used because the original multiquery example used them.

I've just modified a simple example to show it break. This is the type of table etc. that I am using... if the structure is wrong please point it out. I suppose I could use the sample db but that might not behave the same.

What I NEED to know is why when I send a bunch of queries using multiquery the connection gets dropped and needs to be re-initialized. I have a feeling the engine isn't ready for another call for a short time, and re-sending another one so quick catches it in a bad state. That is what the Sleep calls try to show. Without them the connection is dropped almost every time. So I believe there is a problem either in the engine or the C++ wrapper.

It looks like there may be a memory leak issue as well, but I'd like to figure out the first problem first.

Paul

Paul Martin wrote:

Sleep function...

It's a bad sign when your program needs to call Sleep() to function correctly. Race conditions galore lie down that path.

note that you must add a 'dbtest' database and a user called 'TestApp' using 'password' with rights to that db.

Why make us create a special database and user for you, when you can modify your example to do its work in the MySQL++ example database? If the problem goes away or changes when making that conversion, you've learned something that may point you to a fix.

cout << "Connection fried... "; Sleep(10);

Tiny little delays like this are particularly suspect.

try { // Execute query and print all result sets Result res = query.store(); res.purge();

You don't need to call purge() yourself. It's a cleanup function within ResUse and Result. It's not going to be public any more in v3.

I'm also concerned about whether you've elided code in this area: is there actual work done between the store() and purge() calls? Also, if you're using res after the purge call, Bad Things (TM) will happen.

query << "BEGIN WORK;" << endl;

All these newlines are garbage to MySQL's query parser. Maybe it happens to ignore them in all cases that matter to you, or maybe they're part of the problem.

Also, you should be using MySQL++'s Transaction class here for exception safety instead of rolling your own SQL. See section 3.9 in the user manual.

query << "SET FOREIGN_KEY_CHECKS=0;" << endl; query << "DROP DATABASE IF EXISTS dbtest;" << endl;

You do know you don't have to do these insertions in separate C++ statements, right? This works fine:

query << "BEGIN WORK;" << "SET FOREIGN_KEY_CHECKS=0;" << "DROP DATABASE IF EXISTS dbtest;" << ... ;

query << "INSERT INTO Status VALUES(1,29,2,1,'20071106111629','20071106111629',DEFAULT,180,0);" << endl;

Any reason you're not using either template queries or SSQLS here? MySQL++ will generate a lot of the most repetitive sorts of SQL for you, if you let it.

for(;;) { Query query = con.query();

This is hiding a variable of the same name outside the loop. You could just be reset()ting the query object at the top of the loop instead.

// 105 more UPDATEs with similar data

Why so many? I realize there's an overhead to each transaction, so the more transactions you batch up in each query execution the better, but the point of diminishing returns has to be well before this.

if(kbhit()) { cout << "Program halted by user\n";

while(kbhit()) char a=getch(); exit(0); } }

Instead of this unportable DOS-era crappery, please consider this instead:

string s; cin >> s;

It's not "Press any key", but it suffices. I say this not just because of style reasons, but also because you're cutting off a big chunk of your testing audience if you stick to these Windowsisms.