Cannot Connect: Can't create a new thread (errno 11)
Folks, I'm wondering is someone could help us in trying to resolve the above error, we have read through what threads we could find and tried all the suggestions, but have had no luck. There are no memory problems, in fact when this happens if there is still a localhost client connection to the server you can do whatever you want, everything is running normal. It just refuses all new connections. When it happens there is plenty of memory on the machine and no load at all (everything is running just as it should). Once things fall below 90, everything comes back. The problem happens when the process list hits the 91st process every time. 90 processes, no problems, the 91st process/connection gets the above error and any after that simple gets "Can't connect to MySQL server". We've tried all the obvious things, messing with the number of connections, open files etc. Nothing seems to work. We are running MySQL 5 on Centos 5 (flavor of RedHat). The machine is dedicated to MySQL. Below is a link to a text file that shows the OS limits for the mysql account on the machine and the output from show variables (including it made the message to large for the list). http://208.3.90.212/wtresults.txt If anyone has any suggestions, it would be appreciated. Thanks!
Re: Getting remote connected machine as value in a select.
Thanks Mark - I missed this message the first time around, glad I came across it as that saves a step for sure! -Chris - Original Message - From: "Mark V" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, October 20, 2003 11:37 AM Subject: Re: Getting remote connected machine as value in a select. > FYI, > If you only want the hostname, and not the user, > combine the USER() with a SUBSTRING_INDEX(): > > SUBSTRING_INDEX(USER(),'@',-1) --> returns host only > SUBSTRING_INDEX(USER(),'@',1) --> returns username > only > > USER(), SESSION_USER(), and SYSTEM_USER() are all > synonymous > > CURRENT_USER() however is different in that it returns > the username and hostname that the current session was > authenticated as. > > I too am unaware of how to prevent the truncation of > string. If anyone knows, I myself would be very > interested. > > Thanks, > -Mark > > --- Chris Faust <[EMAIL PROTECTED]> wrote: > > That is good to know Emery.. > > > > I think I'll be ok in that respects, I did the > > permissions so the granted > > host is by IP (ex 192.168.0.%), so I'm getting IP > > back and I'm using the > > same username for all and its not truncated - so I > > think I'm good to go!! > > > > I just my first test on my dev server and everything > > worked exactly as I > > hoped! > > > > Thanks > > -Chris > > > > - Original Message - > > From: "Director General: NEFACOMP" <[EMAIL PROTECTED]> > > To: "Chris Faust" <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Monday, October 20, 2003 9:48 AM > > Subject: Re: Getting remote connected machine as > > value in a select. > > > > > > > You are welcome. > > > But remember that sometimes it truncates the name. > > > For example I sometimes connect from my laptop > > using nzeyimana as username > > > and it just returns [EMAIL PROTECTED] instead of > > returning > > > [EMAIL PROTECTED] > > > (it just removes the last P). > > > > > > I don't know why. Maybe someone on the list may > > help on this issue. > > > Also as I told you, I don't know the difference > > between USER() and > > > SESSION_USER() > > > > > > > > > Thanks > > > Emery > > > - Original Message - > > > From: "Chris Faust" <[EMAIL PROTECTED]> > > > To: "Director General: NEFACOMP" > > <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > > Sent: Monday, October 20, 2003 15:27 > > > Subject: Re: Getting remote connected machine as > > value in a select. > > > > > > > > > > Thanks Emery, > > > > > > > > That is exactly what I needed, it gives me back > > the IP of the machine > > I'm > > > > connecting from. > > > > This is going to allow me to remove so much > > machine specific code that > > we > > > > could put up a new server within mins if needed. > > > > > > > > Thanks Again > > > > -Chris > > > > > > > > - Original Message - > > > > From: "Director General: NEFACOMP" > > <[EMAIL PROTECTED]> > > > > To: "Chris Faust" <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > > > Sent: Monday, October 20, 2003 4:38 AM > > > > Subject: Re: Getting remote connected machine as > > value in a select. > > > > > > > > > > > > > I don't know I well understand your problem. > > > > > There is a function SESSION_USER() in MySQL > > that returns the currently > > > > > connected user. The user string also returns > > the host from which he > > > > > connected in the form [EMAIL PROTECTED] > > > > > > > > > > So, by issuing the query: > > > > > SELECT SESSION_USER(), Table.* from Table > > > > > you will get the needed data. > > > > > > > > > > But the problem is that you will need to get > > the host name from that > > > > > username. Also it truncates the string when it > > is long > > > > > There is also USER() that returns the same > > [EMAIL PROTECTED] . I really don't > > > know > > > > > the real difference between the two functions. > >
Re: Getting remote connected machine as value in a select.
That is good to know Emery.. I think I'll be ok in that respects, I did the permissions so the granted host is by IP (ex 192.168.0.%), so I'm getting IP back and I'm using the same username for all and its not truncated - so I think I'm good to go!! I just my first test on my dev server and everything worked exactly as I hoped! Thanks -Chris - Original Message - From: "Director General: NEFACOMP" <[EMAIL PROTECTED]> To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, October 20, 2003 9:48 AM Subject: Re: Getting remote connected machine as value in a select. > You are welcome. > But remember that sometimes it truncates the name. > For example I sometimes connect from my laptop using nzeyimana as username > and it just returns [EMAIL PROTECTED] instead of returning > [EMAIL PROTECTED] > (it just removes the last P). > > I don't know why. Maybe someone on the list may help on this issue. > Also as I told you, I don't know the difference between USER() and > SESSION_USER() > > > Thanks > Emery > - Original Message - > From: "Chris Faust" <[EMAIL PROTECTED]> > To: "Director General: NEFACOMP" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, October 20, 2003 15:27 > Subject: Re: Getting remote connected machine as value in a select. > > > > Thanks Emery, > > > > That is exactly what I needed, it gives me back the IP of the machine I'm > > connecting from. > > This is going to allow me to remove so much machine specific code that we > > could put up a new server within mins if needed. > > > > Thanks Again > > -Chris > > > > - Original Message - > > From: "Director General: NEFACOMP" <[EMAIL PROTECTED]> > > To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Monday, October 20, 2003 4:38 AM > > Subject: Re: Getting remote connected machine as value in a select. > > > > > > > I don't know I well understand your problem. > > > There is a function SESSION_USER() in MySQL that returns the currently > > > connected user. The user string also returns the host from which he > > > connected in the form [EMAIL PROTECTED] > > > > > > So, by issuing the query: > > > SELECT SESSION_USER(), Table.* from Table > > > you will get the needed data. > > > > > > But the problem is that you will need to get the host name from that > > > username. Also it truncates the string when it is long > > > There is also USER() that returns the same [EMAIL PROTECTED] . I really don't > know > > > the real difference between the two functions. > > > > > > > > > Hope this helps > > > > > > > > > Thanks > > > Emery > > > - Original Message - > > > From: "Chris Faust" <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]> > > > Sent: Sunday, October 19, 2003 23:55 > > > Subject: Getting remote connected machine as value in a select. > > > > > > > > > Hello, > > > > > > I'm curious as to if something is possible - I don't know if its > possible > > to > > > explain it right. > > > > > > I have a table which contains a bunch machine specific information, file > > > paths, names etc. > > > > > > What I would like to try and accomplish is when I query this table for > the > > > information I need, I also receive back a value (that I can define) that > > is > > > determined by the identity of the machine making the query - either by > IP > > > address or whatever. > > > > > > For an example say I have 3 machines total. > > > 1 mySQL DB Server > > > 2 web servers > > > > > > When I connect to mySQL from either of the 2 web servers and say > something > > > like "Select * from table", one of the values returned would be > something > > > like "web1" as that is what I defined to be the value for when machine > > > 192.168.0.4 connected to it. > > > It doesn't have to be IP, I'm just using that for a example. > > > > > > Any ideas on how to accomplish something like this? > > > mySql 4.0 > > > RH 9 > > > > > > Thanks > > > -Chris > > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting remote connected machine as value in a select.
Thanks Emery, That is exactly what I needed, it gives me back the IP of the machine I'm connecting from. This is going to allow me to remove so much machine specific code that we could put up a new server within mins if needed. Thanks Again -Chris - Original Message - From: "Director General: NEFACOMP" <[EMAIL PROTECTED]> To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, October 20, 2003 4:38 AM Subject: Re: Getting remote connected machine as value in a select. > I don't know I well understand your problem. > There is a function SESSION_USER() in MySQL that returns the currently > connected user. The user string also returns the host from which he > connected in the form [EMAIL PROTECTED] > > So, by issuing the query: > SELECT SESSION_USER(), Table.* from Table > you will get the needed data. > > But the problem is that you will need to get the host name from that > username. Also it truncates the string when it is long > There is also USER() that returns the same [EMAIL PROTECTED] . I really don't know > the real difference between the two functions. > > > Hope this helps > > > Thanks > Emery > - Original Message - > From: "Chris Faust" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, October 19, 2003 23:55 > Subject: Getting remote connected machine as value in a select. > > > Hello, > > I'm curious as to if something is possible - I don't know if its possible to > explain it right. > > I have a table which contains a bunch machine specific information, file > paths, names etc. > > What I would like to try and accomplish is when I query this table for the > information I need, I also receive back a value (that I can define) that is > determined by the identity of the machine making the query - either by IP > address or whatever. > > For an example say I have 3 machines total. > 1 mySQL DB Server > 2 web servers > > When I connect to mySQL from either of the 2 web servers and say something > like "Select * from table", one of the values returned would be something > like "web1" as that is what I defined to be the value for when machine > 192.168.0.4 connected to it. > It doesn't have to be IP, I'm just using that for a example. > > Any ideas on how to accomplish something like this? > mySql 4.0 > RH 9 > > Thanks > -Chris > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting remote connected machine as value in a select.
Hello, I'm curious as to if something is possible - I don't know if its possible to explain it right. I have a table which contains a bunch machine specific information, file paths, names etc. What I would like to try and accomplish is when I query this table for the information I need, I also receive back a value (that I can define) that is determined by the identity of the machine making the query - either by IP address or whatever. For an example say I have 3 machines total. 1 mySQL DB Server 2 web servers When I connect to mySQL from either of the 2 web servers and say something like "Select * from table", one of the values returned would be something like "web1" as that is what I defined to be the value for when machine 192.168.0.4 connected to it. It doesn't have to be IP, I'm just using that for a example. Any ideas on how to accomplish something like this? mySql 4.0 RH 9 Thanks -Chris
RE: Archive on remote Machine.
You could just ftp them wherever you want for storage. I made a simple script where I took the provided "mysqlhotcopy" script, added it and used it every day as a cron to make a copy of all my databases, then run through a check of each one then tar them all up and ftp them to a remote server. Its been running without a flaw for about 6 months now so personally I think its a good solution to archiving. -Chris >> -Original Message- >> From: Ahmed S K Anis [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, February 18, 2003 5:52 AM >> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; >> [EMAIL PROTECTED] >> Subject: Archive on remote Machine. >> >> >> Hi, >> I use Select * into outfile "filepath" from table to archive the >> database. >> Is there any way to archive this into a different machine. (Not current >> machine) >> >> Yes Mapping to new machine and then giving path is an option. Any other >> opton.? >> Anis >> >> >> >> - >> Before posting, please check: >>http://www.mysql.com/manual.php (the manual) >>http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail >> <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> >> >> >> - >> Before posting, please check: >>http://www.mysql.com/manual.php (the manual) >>http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail >> <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Whats between a varchar text ??
If you want to limit the amount of text coming in or going out, wouldn't it be easier to it either in your php script or constrain the html textarea? Just a thought. -Chris >> -Original Message- >> From: Craig melia [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, January 21, 2003 12:28 PM >> To: [EMAIL PROTECTED] >> Subject: Whats between a varchar text ?? >> >> >> Hi >> >> I am building my 1st app in php with Mysql and I hope you can help. >> >> I want to have users give a description in a form and >> varchar(255) wont give >> me enough space so I thought I would use text(650) but it seems >> the 'text' >> type wont take the limit of (650), it simply offers the user (65535): way >> too much. >> >> Is there anything I can do to set a column type to recieve just 650 >> characters of text, or something similar? >> >> Craig >> >> >> - >> Before posting, please check: >>http://www.mysql.com/manual.php (the manual) >>http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail >> <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Install 2 Servers of different versions
Hey All, I want to install the mySql 4.0.9 to try out some features but I want to make sure my 3.23.54 release stays 100% as is. I'd like to have both servers running, using different data dirs etc. etc. In short I don't want anything at all shared between the two. I've read a bunch of docs on the site as well as the multi install section the MySql book and what I've come up with so far is. Download the source install and configure with the following options. --prefix=/usr/mysql_409 (new dir for the 4.0 install) --with-unix-socket-path=/usr/local/mysql4/mysql.sock --localstatedir=/usr/mysql_409/db --with-tcp-port=4006 Now I'm at the point where I have a bunch of questions I can't seem to find a positive answer for.. 1. Are those the only options needed? Will this make sure that /etc/my.cf is only used for the 3.x install and the 4.x will allow me to have a my.cnf in the /usr/mysql_409 dir? 2. This is dumb, but do I pass each option one at a time into ./configure? Meaning I tried to do it all at once, for example "./configure --prefix=/usr/mysql_409 \ --with-unix ...)", but I got an error of it not seeing the second param as a valid setting, yet if I do each one, one at a time it works fine. 3. Is port 4406 OK for second install? 4. How do I get a local mysql prompt, do I just start using "-P 4406" whenever I want to connect on the command line? Its only my dev machine so I'm not worried about too much, I just really want to make sure I don't mess with the 3.x install. Thanks in Advance!! -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL & RH 8.0
I can be no help to you at all, but I can tell you that I getting an error
message that relates to the GLibc problem that is document everyplace
although there doesn't seem to be any fix for me.. Maybe what I've gone
through to date can help you make some choices (I think my next one can only
be freeBSD).
I get the wonderful
"DBI connect('...',...) failed: Lost connection to MySQL server during
query"
But ONLY when there is a high load on the machines, any other time it runs
fine..
I started with a brand new RH 8, mySql 3.23.54 and Apache 2.0 on a good P4
with 1 gig of ram.
Totally removed mySql, downloaded same version from mysql.com, installed it
had the same problem.
Downgraded GLibc, had the same problem.
Downgraded DBI
Ran mysld with the skip-name-resolve option, same problem.
Used a different Nic in the machine, used a different switch, different
cable etc. - same problem.
(BTW, there is low IO usage and no network errors or collisions or anything
like that).
In short we did everything we could do except OS, Apache and hardware.. So
be built a brand new machine and this time around..
mySql 3.23.54 from mysql.com, RH 7.3 and Apache 1.3
All the same things happen, no matter what we do... We still can't find any
reason for it.. Right now our fix is lousy round robin using both machines
to keep load low enough so the error doesn't happen.
-Chris
>> -Original Message-
>> From: Konstantin Sorokin [mailto:[EMAIL PROTECTED]]
>> Sent: Tuesday, January 07, 2003 6:10 AM
>> To: mysql users
>> Subject: MySQL & RH 8.0
>>
>>
>> Hi!
>>
>> https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=77467
>>
>> Has anybody working and tested solution for this kind of
>> problem with MySQL ?
>>
>> 1) upgrade to glibc 2.3.1 from phoebe RH beta seems to be fix
>> problem, but I conside it to be too dangeros on prodaction machine...
>> Has anybody success story with that upgrade ? :)
>>
>> 2) upgrade to newer version of MySQL. If it is true, what exactly
>> version should I use ?
>>
>> --
>> Konstantin Sorokin . * * . * .* .
>> . * . .*
>> Mail.Ru, Moscow, Russia * . . /\ ( . . *
>> Work:8-095-725-63-57 . . / .\ . * .
>> .*. / * \ . .
>> . /* o \ .
>> Happy New Year! * '''||''' .
>> **
>>
>>
>> -
>> Before posting, please check:
>>http://www.mysql.com/manual.php (the manual)
>>http://lists.mysql.com/ (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
>> <[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mod_perl and mySql
>> Glad to hear that about MPW; thanks. Its the truth, those 2 books have really saved my butt more then once - so it should be me thanking you!!. >> Regarding use of persistent connections, I wonder if you really need >> them. To give an example from another language, PHP offers >> mysql_connect() >> and mysql_pconnect() connections, which set up regular or persistent >> connections. But it turns out that the difference in overhead between >> the two call is minimal because MySQL's client/server protocol is so >> efficient for connection establishment. >> >> Also, persistent connections can have certain negative effects. For one >> thing, because a connection remains open for a longer time, even when no >> script is actively using it, you can more easily end up bumping >> up against >> your server's max-simultaneous-connection limit. With non-persistent >> connections, this is less of an issue. >> Thanks Paul, that tells me to stop going crazy over it at least :).. The difference in performance between now and before mod_perl is just so amazing I was trying to squeeze everything I could out of it. -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mod_perl and mySql
You mean overall? The move to mod_perl is more because the site is so perl heavy, its perl that is driving the change more then anything else. I just figure If I'm going to do it, I want to do as much of it as I can :).. Thanks -Chris >> -Original Message- >> From: Michael Bacarella [mailto:[EMAIL PROTECTED]] >> Sent: Sunday, January 05, 2003 11:40 AM >> To: Chris Faust >> Cc: MySql Mailing List >> Subject: Re: mod_perl and mySql >> >> >> Are you sure it would help? MySQL is damn fast at taking connections. >> >> When we converted to mod_perl I made a mental note to switch >> to persistent connections. It turns out that it still >> ran fast enough even though every hit results in an RDBMS >> connection. And at this point we're doing 5M of them per day. >> >> It has never been a bottleneck for us. >> >> -M >> >> > Due to ever increasing traffic I'm converting my site into mod_perl and >> > quite honestly I'm confused about persistent DB connection and >> DB connection >> > pooling.. I know there is tons of info out there on the goggle >> groups and on >> > perl.apache.com, I also picked up "Mod_Perl Developers >> cookbook" and I also >> > have the awesome "Mysql" and "MySql and Perl for the web". >> (BTW so far the >> > little mod_perl section in "MySql and Perl for the web" has proven more >> > useful then anything I've found in the whole Mod_Perl dev cookbook). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mod_perl and mySql
Hello Group,
Due to ever increasing traffic I'm converting my site into mod_perl and
quite honestly I'm confused about persistent DB connection and DB connection
pooling.. I know there is tons of info out there on the goggle groups and on
perl.apache.com, I also picked up "Mod_Perl Developers cookbook" and I also
have the awesome "Mysql" and "MySql and Perl for the web". (BTW so far the
little mod_perl section in "MySql and Perl for the web" has proven more
useful then anything I've found in the whole Mod_Perl dev cookbook).
I've seen so many example and different ways to do it, that I have no idea
if I'm doing things the safest and most optimized way. For example I've read
a bunch of posts where people said to do pooling its only a matter of doing
your connect like "$db || DBI->CONNECT", which just gives me a syntax error.
I also hear in one place that "Apache::DBI" takes care of making sure the
connection stays alive but then I hear in another that is something that I
will need to take care of.
Below is how I've set things up, hopefully someone out there that really
knows can tell me if I'm doing something wrong or if it could be done
better!!
Right now as it stands everything is running perfect under mod_perl, again
I'm just not sure I'm doing everything the correct way.
Thanks in Advance!
-Chris
***
Relevant httpd.conf:
PerlRequire /home/httpd/perl/startup.pl
PerlModule Apache::DBI
SetHandler perl-script
PerlHandler Apache::Registry
Options ExecCGI
allow from all
PerlSendHeader On
Startup.pl
use strict;
use lib qw(/websites/perl/modules);
use DBI ();
use DBD::mysql ();
use CGI ();
use CGI::Cookie ();
CGI->compile (':all');
use Data::Pageset ();
use HTML::Template ();
use Mail::Sendmail ();
use File::Find ();
# Cache all of our HTML::Templates
my @tmpl_dir = qw(/websites/html/templates/);
print STDERR "Pre-loading HTML Templates...\n";
File::Find::find (
sub {
return unless /\.tmpl$/;
HTML::Template->new(
filename => "$File::Find::dir/$_",
cache => 1,
);
},
@tmpl_dir
);
1;
***
Database Connection Package:
(This .pm file lives in the "use lib" dir from above and is what I use in
all my
scripts to make a connection to the DB).
package connect_db;
require Exporter;
our @ISA = qw(Exporter);
our @EXPORT = qw(&connect_to_db $db);
our $VERSION = 1.00;
#
sub connect_to_db {
# DBI->trace(3, "/websites/web_files/display.log");
my $dbpassfile = '/websites/web_files/dbroot.pass';
my ($db_username,$db_password,$db_location,$db_name,$db);
open (DBPASSFILE, "$dbpassfile") or die "Couldn't open $dbpassfile: $!\n";
while () {
($db_username,$db_password,$db_location,$db_name) = split;
}
my $database = "DBI:mysql:database=$db_name:host=$db_location";
$db = DBI->connect($database,$db_username,$db_password) or die "Cannot
Connect: " . $db->errstr();
close DBPASSFILE;
return $db;
}
1;
***
In My Scripts:
I should point out that I do no forking or anything of that nature, for
example before mod_perl the DB connect package was just something that was
in "MAIN" in all my scripts, all subs and everything made use of that single
connection ($db) for all DB connections.
My mod_perl scripts go something like this:
use connect_db qw(&connect_to_db $db);
use vars qw($CGI $db);
##
# Main
#
# Setup a working environment
init_global_vals();
display_page()
#done
#
sub init_global_vals {
# Global Vars
$CGI = new CGI();
$db = connect_db->connect_to_db;
.
} # end of sub
##
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Desperate - failed: Lost connection to MySQL server during query
Thanks for the help John, I really don't know all the specifics of the hardware setup as it is all out at our ISP, I know its a rack mounted P4 and its going into a Cisco switch but that's about it. We have made some progress though and as strange as it sounds its really looking like its something with RH 8, in short we downgraded perl 5.8 to 5.6.1 with no results, we then went from Apache 2 down to 1.3 with no results, then that night backup exec came through and the machine spiked to 100% - noticed it by luck of being on the machine at the same time, this pretty much took Apache, Perl and my code out of the question. Did up a new machine (same specs), put RH 7.3 on it, copied everything over and made it live.. Right before we switched over to the new machine we stopped throttling the server (it was the only way to stop those errors) and instantly those connection failures started happening like crazy. As soon as we made the switch, they all went away, load was cut in half and CPU idle went up around 30 to 40% and things were just screaming, 110% improvement in all areas. So you'd think everything was wonderful now, wouldn't you?.. Well check this out.. As soon as the new web server was brought up I started getting (and still get) tons of "Aborted connection ... (Got an error reading communication packets)" - and mySql has run without a flaw since day one, also the scripts and DBI and everything was exactly the same on the new web server from the old web server (mysql would have no knowledge of the switch). I can say I'm about 98% sure that everything mySql shows for this problem at: http://www.mysql.com/doc/en/Communication_errors.html Isn't a issue. As RH 8 is also running on the dedicated mySql machine, I'm starting to believe its the root of all my problems. Its been a wild week!! Thanks -Chris >> -Original Message- >> From: John P [mailto:[EMAIL PROTECTED]] >> Sent: Saturday, December 28, 2002 1:45 PM >> To: Chris Faust; [EMAIL PROTECTED] >> Subject: Re: Desperate - failed: Lost connection to MySQL server during >> query >> >> >> > If you are running close to the same setup with no problems >> then I feel a >> > little better about it not being a issue of pure traffic. >> >> Yes, we operate in a very similar way to you. (BTW, we have had very good >> results with a PHP based cache facility that simply stores the db driven >> pages over a selectable time period; good if your pages don't change that >> much! Capacity increased 4000% or so; would imagine something >> similar exists >> for perl) >> >> > I don't think the link between the machines is a issue, both >> machines are >> > dedicated and at the same location, I've tried using both the >> external and >> > internal (10.0.x.x) IP to connect to the DB with the same >> results and in >> all >> > cases both machines have had entries in their hosts file for >> one another. >> > >> > As for simultaneous connections, that is something that I >> still need to go >> > through the logs and come up with a real number for those time >> frames.. I >> > don't know anyway to see a real number of actual users using Apache via >> the >> > command line (is there such a thing?). >> >> I've always done ps -ef | grep httpd | wc -l a few times which shows the >> processes, you can get a good idea of whether the site is busy or not. I >> don't know how to actually find the exact number of users, I >> suppose netstat >> ? >> >> > One thing I wonder that I forgot to ask - does using IP address as the >> host >> > for a user in mySql matter? Meaning before DNS switched over I >> wanted to >> get >> > everything up and running so I created all the mysql user >> accounts based >> on >> > the IP address of the web server (and not name). >> > I would think if this were a issue at all I would be getting "denied" >> > messages and it wouldn't work 100% of the time, but I thought I would >> throw >> > it out there. >> >> I'd do everything by IP, especially for the "internal" stuff. >> >> It might be worth trying a dedicated cable+two separate network cards in >> each server to handle MySQL traffic. Just a thought, but could packets be >> getting lost between the two servers (network overloaded or out >> of ports on >> eth0 or ..?) >> >> Post your exact network setup (machines, switches/hubs etc) and >> that might >> give some more clues? >> >> Good luck >> John >> - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Desperate - failed: Lost connection to MySQL server during query
Yes, I do connect to the DB server over IP - in short this is what I do
within MAIN of all my scripts (and $db is what is used anyplace a DB
connection is needed)
##
# Database Setup
# -
#
my $dbpassfile = '/websites/web_files/dbadmin.pass';
my ($db_username,$db_password,$db_location,$db_name,$db);
open (DBPASSFILE, "$dbpassfile") or die "Couldn't open $dbpassfile: $!\n";
while () {
($db_username,$db_password,$db_location,$db_name) = split;
}
my $database = "DBI:mysql:database=$db_name:host=$db_location";
$db = DBI->connect($database,$db_username,$db_password) or die "Cannot
Connect: " . $db->errstr();
close DBPASSFILE;
I use a text file to store the username,password,table name and IP to add
just a little more security.
I have check to make sure that open files on the linux machine wasn't a
issue (and it wasn't) when opening up that file each time.
As far as the connection_timeout, I don't have anything in my.cnf but if I
do a mysqladmin variables it shows me its set to 5.
I think I'm going back to the drawing board :)
Thanks
-Chris
>>
>> This DOES seem to indicate you are connecting using TCP/IP. Are
>> you? If so,
>> a whole new sleuth of options appears.
>>
>> > I'm leaning towards either Redhat 8, or Apache 2 or the combination of
>> > the two of them - I just can't figure out what else to try or what to
>> > look for.
>>
>> Try connecting over the domain socket; or / and add something
>> like this to
>> the [mysql] section of my.cnf,
>>
>> set-variable = connect_timeout=4
>>
>> Just to see whether it is a timing problem.
>>
>> - Mark
>>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Desperate - failed: Lost connection to MySQL server during query
If you are running close to the same setup with no problems then I feel a little better about it not being a issue of pure traffic. As for the scripts, its a heavy reliance on the database for information - pretty much the whole site is like a classifieds ads system. Other then the advance searches (which are not used all that much) 90% of everything is just a single select to the DB at which point the results are normally parsed out in a single loop and displayed to the user (I also use HTML::Template to deal with serving results). If it means anything, when things are running at a normal load I can sit and watch people hitting the scripts (just using top or ps -eaf) and see that they use up a little CPU and then they are gone (meaning everything did its thing and it was over), I almost never see a perl process that is running one of the webscipts last any more then a second or sometimes two.. I don't think the link between the machines is a issue, both machines are dedicated and at the same location, I've tried using both the external and internal (10.0.x.x) IP to connect to the DB with the same results and in all cases both machines have had entries in their hosts file for one another. As for simultaneous connections, that is something that I still need to go through the logs and come up with a real number for those time frames.. I don't know anyway to see a real number of actual users using Apache via the command line (is there such a thing?). One thing I wonder that I forgot to ask - does using IP address as the host for a user in mySql matter? Meaning before DNS switched over I wanted to get everything up and running so I created all the mysql user accounts based on the IP address of the web server (and not name). I would think if this were a issue at all I would be getting "denied" messages and it wouldn't work 100% of the time, but I thought I would throw it out there. Thanks -Chris >> >> Chris, we are running a similar www/db box setup to you, with similar >> traffic and data transfer figures. I use Apache 1.3/PHP though. >> I never see >> the uptime over 1, let alone 6. >> >> Do all your scripts have quite extensive reliance on the DB and >> calculations >> etc? I would have thought that the WWW box has very little to do! >> >> I know each situation is different, but have you tested the link >> between the >> two computers (is it dedicated or over the same net as the WWW?) to check >> that there isn't a problem there? What sort of simultaneous connection >> figures does this problem start at? I wonder about a hardware / network >> problem too.. >> >> >> Cheers, >> John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Desperate - failed: Lost connection to MySQL server during query
Wow, I don't know if that's a good bet as doesn't a donut cost around a dollar these days :). Seriously, It very well could be something hardware related but everything is brand-new and I'm not seeing any issues logged anyplace with anything on the machine (except this specific problem at high load times and there is the error being reported). Something else that may be an issue is its just traffic and how Redhat 8 or Apache 2 deals with it, the site isn't over the top but the high daily average is 10,000 unique users, 250,000 page views and about 6 gig of bandwidth a day.. Pipe is no issue at all and I would think that the P4 could handle that amount of traffic - I only see CPU go off the chart when all the disconnects start happening so its hard to tell if one is the result of the other.. Hardware is my last resort, but its moving up the list fast!! Thanks -Chris >> -Original Message- >> From: Doug Thompson [mailto:[EMAIL PROTECTED]] >> Sent: Saturday, December 28, 2002 7:33 AM >> To: Chris Faust; Mark; [EMAIL PROTECTED] >> Subject: RE: Desperate - failed: Lost connection to MySQL server during >> query >> >> >> Chris: >> >> I bet a dollar to a donut that you have a hardware problem. The >> essential proof is in the first quoted paragraph below. Believe >> your data, save your hair. >> >> Regards, >> Doug >> >> Filter That! SQL MySQL Query >> >> On Sat, 28 Dec 2002 07:18:07 -0500, Chris Faust wrote: >> >> >One other test I did was put part of the site that was getting >> as many lost >> >connection errors as anything else, on another machine (the >> administration >> >side of it) and once there it ran perfectly using the same >> mySql server (and >> >its even IIS). >> > >> >I'm leaning towards either Redhat 8, or Apache 2 or the >> combination of the >> >two of them - I just can't figure out what else to try or what >> to look for. >> >I hate the thought all the work involved in bring up a new machine and >> >bringing everything over when I at least have a error message >> to work with >> >:). >> >> >> >> - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Desperate - failed: Lost connection to MySQL server during query
Hello Mark, Thanks for the reply.. I would need to wait until load gets up there to see the state of apache but even now with almost no load on the machine I'm seeing at least a dozen connections in the "TIME_WAIT" state. On the flip site of things I have not been seeing any other connection related issues from apache (no connection reset by peer messages or anything like that) I really don't think its any of the scripts either, everything was running fine at another location (none of these errors ever came up) and the scripts do run fine via the command line. To give you a little more on the scripts.. I create a connection to the DB as the first thing in all scripts. That connection is used anyplace a call to the DB is needed, (in other words I don't fork anything or create any children, establish a second connection etc.) there is never anymore then 1 connection made to the DB for any 1 script that is compiled at run-time. For timeout, Apache has: Timeout 300 KeepAlive On MaxKeepAliveRequests 0 KeepAliveTimeout 15 One other test I did was put part of the site that was getting as many lost connection errors as anything else, on another machine (the administration side of it) and once there it ran perfectly using the same mySql server (and its even IIS). I'm leaning towards either Redhat 8, or Apache 2 or the combination of the two of them - I just can't figure out what else to try or what to look for. I hate the thought all the work involved in bring up a new machine and bringing everything over when I at least have a error message to work with :). Thanks -Chris >> >> >> > The problem is this, everytime the web server starts to work a little >> > (load via a uptime showing about 6), 1 out of 5 scripts bomb out with >> > the message of: >> > >> > "failed: Lost connection to MySQL server during query" >> > >> > As load gets higher then the rate of failure gets higher. >> >> >> I lack the necessary info on your scripts, of course; but you might be >> looking at it from the wrong angle. I would ere expect a >> time-out issue with >> the Apache server (that is, with its spawned children). Try a >> "netstat -a"; >> are many http connection in a TIME_WAIT state? The "Timeout" value in >> httpd.conf also leaps to mind. A CPU load of 6, though not itself >> excessively high, may be an indication that the Apache server >> gets hits with >> many, many requests, forcing many to remain in a TIME_WAIT state for a >> while -- long enough to cause a potential problem for the MySQL >> connection, >> at least. >> >> A solid test, I would say, is to try and run your Perl scripts >> directly, and >> not via a web-page. I have a nagging suspicion the errors will be gone. >> >> - Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Desperate - failed: Lost connection to MySQL server during query
Hello All, This mail list is about my last hope as I tried everything I could possibly find online related to this problem and I've had no luck (it doesn't appear to be the GLib problem that everyone keeps talking about). Machine Specifics: Web Server (dedicated): P4 2gHz with 2gig memory RedHat 8.0 Apache 2.0 Perl 5.8 and latest DBI glibc-2.2.93-5 DB Server (dedicated): P4 2gHz with 2gig memory mySql 3.23.54 The problem is this, everytime the web server starts to work a little (load via a uptime showing about 6), 1 out of 5 scripts bomb out with the message of: "failed: Lost connection to MySQL server during query" As load gets higher then the rate of failure gets higher. There is NOTHING being reported by mysql on the DB server, even with warnings turned on - no DB restarts or nothing like that and at no time is load of any concern on the DB server nor do we have any problems with max_connections or anything of that nature. I have 3 other mysql boxes up and running (that work fine) and I went through all the default values given via the mysqladmin variables and everything is the same across multiple machines. I've found a ton of messages that show this same problem and they all relate to the RedHat version of Glib which is prior to mine. Also the reports I've seen all say this happens 100% of the time and in my case it only happens when load is high. Finally the reports say it causes mysqld to crash and restart which isn't happening (mysql isn't complaining about a thing). Does anyone out there have any idea? I'm at a total loss, everything is fine until load gets up there a little (and what little there is isn't overloading anything). Thanks In Advance! -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
