www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - MySQL daemon usage example

reply KUV <KUV_member pathlink.com> writes:
Has anyone some example, where your daemon-like program is using single MySQL
connection? And, MUCH MORE likely, in several threads and with connection
restoring.

My own program do this, but sometimes something is wrong after start, and all
mysql_store_result calls return null for valid query, all mysql_ping calls
return not 0.

P.S. do not advice me to call mysql_thread_init, I do.
Jan 09 2006
next sibling parent reply Chris Lajoie <ctlajoie___remove___this___ ___gmail.com> writes:
KUV wrote:
 Has anyone some example, where your daemon-like program is using single MySQL
 connection? And, MUCH MORE likely, in several threads and with connection
 restoring.
 
 My own program do this, but sometimes something is wrong after start, and all
 mysql_store_result calls return null for valid query, all mysql_ping calls
 return not 0.
 
 P.S. do not advice me to call mysql_thread_init, I do.
 
 

At work we have had problems using MySQL (from a C# program) from seperate threads (we also call mysql_thread_init). In our case, the error was something similar to "MySQL requires a connection that is open", but when we check the state of the connection it *is* always Open. this problem would go for perhaps several days without appearing once, but then all of the sudden would start to happen frequently, then again disappear for a day or more. after months of trying to figure this one out, we accepted that this was probably related to using non-managed API from a .NET program, and went to a model of funneling all DB queries through a single global connection using locks. While quite less than ideal, it seems to work. Please let us know if you figure out what is wrong, I would be interrested in finding out, even if it turns out your problem is totally different than ours was. Chris
Jan 09 2006
parent KUV <KUV_member pathlink.com> writes:
This bug appears only on program start, and so it is not caused by any timeouts.
If it is appeared - than I cant use any mysql functions, but if it is not, than
everything is good for a long time (a have tested about ~hour session).
When I add sleep(1) after mysql_real_connect, bug appears much more rare. So I
can apologise that it is caused namely by threads (several ones try to use this
connection at almost same time).

Currently I am going to use new connection for each thread, because it is
possible in my program. I am writing local network file scanner and searcher (by
filename), and scan many resources at same time. I have already implemented very
good index that can search in about four million filenames by substring (not
subword), so the only problem is to scan.

In article <dpu9rb$2bo$1 digitaldaemon.com>, Chris Lajoie says...
At work we have had problems using MySQL (from a C# program) from 
seperate threads (we also call mysql_thread_init). In our case, the 
error was something similar to "MySQL requires a connection that is 
open", but when we check the state of the connection it *is* always 
Open. this problem would go for perhaps several days without appearing 
once, but then all of the sudden would start to happen frequently, then 
again disappear for a day or more. after months of trying to figure this 
one out, we accepted that this was probably related to using non-managed 
API from a .NET program, and went to a model of funneling all DB queries 
through a single global connection using locks. While quite less than 
ideal, it seems to work. Please let us know if you figure out what is 
wrong, I would be interrested in finding out, even if it turns out your 
problem is totally different than ours was.

Chris

Jan 10 2006
prev sibling parent "Unknown W. Brackets" <unknown simplemachines.org> writes:
I would suggest keeping a lifetime for any connection to MySQL.  There 
are various things that can happen, or your problems may just be caused 
by the connection outliving wait_timeout or interactive_timeout at some 
point.

If you're not killing a connection that's open for longer than 24 hours, 
you should do so.  Quite possibly, shorter than that is better.  I don't 
believe MySQL was even designed for having connections open terribly 
long, with or without running queries.

What version of MySQL is it?

If you're using a multi-threaded program, you'll also want to check for 
deadlocks and use InnoDB for any tables being modified often.

PS: Most popular PHP/MySQL softwares qualify for your description; while 
the script itself is not long-running, PHP is and it keeps connections 
open in the background until they're needed.  While this is often a good 
thing, it has little performance improvement, and can cause detriments.

I say this only to illustrate that even if you perfect your program to 
have no problems with MySQL, it may not even be worth the time.  MySQL 
doesn't have a high penalty for new connections, especially when 
configured properly (thread_cache_size is important here.)

-[Unknown]

 Has anyone some example, where your daemon-like program is using single MySQL
 connection? And, MUCH MORE likely, in several threads and with connection
 restoring.
 
 My own program do this, but sometimes something is wrong after start, and all
 mysql_store_result calls return null for valid query, all mysql_ping calls
 return not 0.
 
 P.S. do not advice me to call mysql_thread_init, I do.
 
 

Jan 09 2006