digitalmars.D - MySQL daemon usage example
- KUV (7/7) Jan 09 2006 Has anyone some example, where your daemon-like program is using single ...
- Chris Lajoie (15/26) Jan 09 2006 At work we have had problems using MySQL (from a C# program) from
- KUV (12/26) Jan 10 2006 This bug appears only on program start, and so it is not caused by any t...
- Unknown W. Brackets (20/31) Jan 09 2006 I would suggest keeping a lifetime for any connection to MySQL. There
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
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.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
KUV <KUV_member pathlink.com> 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...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
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