mySQL Hangs with Status 'D'
I have been having repeated problems with mySQL 3.23.36 on Linux (Red Hat 6.0, 2.4.3 kernel). I think I've got traced the problem but I don't know how to fix it... At some point after being started one or more of the mySQL threads hang with the ps status of 'D' (my research suggests this can mean something disk related or not). At that point mySQL is totally hung. It won't respond to new connections, thus basically hanging all my Apache sites that use it. Trying to launch 'mysqladmin' just hangs forever trying to connect. The only solution is a restart. Processes with status 'D' cannot be killed. What can I do about this? I've had to restart this server twice today and it's a production Web server. Not fun. Any help is GREATLY appreciated. Hunter - 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
Tracking Down What's Locking the Tables
Here is the part of the processlist from when the server was full... | Id | User | Host | db | Command | Time | State| Info | +--+---+---+--+-+--+--+- -+ | 751 | dummy | localhost | fatfreeradio | Query | 3902 | Sending data | select * from song_files where uri ='Good_Riddance-Trophy-64.mp3 ' | | 807 | dummy | localhost | fatfreeradio | Query | 3888 | Locked | update song_files set current_score = '64', total_votes = '11' where uri = 'Good_Riddance-Trophy-128 | | 809 | dummy | localhost | fatfreeradio | Query | 3875 | Locked | select * from song_files where rec_num = '77' | | 811 | dummy | localhost | fatfreeradio | Query | 3870 | Locked | select * from song_files where uri ='Enemy_You-Lock_out-24.mp3 The rest of the list is just SELECTs. So, does that mean the first select got a lock, somehow got hung up talking to the server and never released the lock? What can I do to further diagnose this problem? Hunter - 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 3.23.36 STILL Having Major Problems - New Details
Okay... Since I modified the process_priv() stuff I was able to get in and see what was going on when the server ran out of connections. It had 257 threads, all executing select statements that were in a Locked state. There was one Update at the top of the list. I'm going to go back and read all the table locking stuff in the manual... Do updates/inserts lock the table so that others can't read it? If so, is there a way to make an update/insert fail if it just takes too long? Hunter From: Sander Pilon [EMAIL PROTECTED] Date: Wed, 25 Apr 2001 22:51:06 +0200 To: 'Hunter Hillegas' [EMAIL PROTECTED], 'mySQL List' [EMAIL PROTECTED] Subject: RE: MySQL 3.23.36 STILL Having Major Problems OS: Red Hat Linux 6.0 / Intel 1GB RAM MySQL 3.23.36 PHP4.0.4PL1 Linux kernel 2.4.3 Using the my.cnf for medium sized sites mySQL starts with safe_mysqld and there are three threads running. Queries come in and out and it's quite fast. Using mysqladmin proc status shows no queries running. After about 12 hours, the number of mySQL threads visible in ps have shot to about 75. At this point the server reports No More Connections, both through PHP and from mysqladmin. What's going on here? I can't figure it out. I can't even get in to diagnose the problem, since the server is out of connections. 99% of my queries are SELECT's. It sounds like something is hanging on to connections and not returning them... I'm using mysql_connect() in the PHP scripts. - 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 3.23.36 STILL Having Major Problems
Running mysqladmin -p proc reports: too many connections... That's why I can't diagnose it. I'm logging in as root. How else would I get process_priv? I haven't changed any parameters so I should be able to get in but I cannot. What am I doing wrong? Hunter From: Andrew Schmidt [EMAIL PROTECTED] Date: Wed, 25 Apr 2001 14:46:48 -0400 To: Hunter Hillegas [EMAIL PROTECTED], mySQL List [EMAIL PROTECTED] Subject: Re: MySQL 3.23.36 STILL Having Major Problems taken from the manual: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SHO W_PROCESSLIST SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the process privilege, you can see all threads. Otherwise, you can see only your own threads. See section 7.27 KILL Syntax. If you don't use the FULL option, then only the first 100 characters of each query will be shown. This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the Process_priv privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users). I believe you will want to do the second paragraph. regards, -- Andrew - Original Message - From: Hunter Hillegas [EMAIL PROTECTED] To: mySQL List [EMAIL PROTECTED] Sent: Wednesday, April 25, 2001 11:21 AM Subject: MySQL 3.23.36 STILL Having Major Problems OS: Red Hat Linux 6.0 / Intel 1GB RAM MySQL 3.23.36 PHP4.0.4PL1 Linux kernel 2.4.3 Using the my.cnf for medium sized sites mySQL starts with safe_mysqld and there are three threads running. Queries come in and out and it's quite fast. Using mysqladmin proc status shows no queries running. After about 12 hours, the number of mySQL threads visible in ps have shot to about 75. At this point the server reports No More Connections, both through PHP and from mysqladmin. What's going on here? I can't figure it out. I can't even get in to diagnose the problem, since the server is out of connections. 99% of my queries are SELECT's. It sounds like something is hanging on to connections and not returning them... I'm using mysql_connect() in the PHP scripts. Any ideas? This has taken my Web site out about 5 or 6 times in the last two weeks. It's no fun. Anything anyone could do to help would be great. Hunter - 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: MySQL 3.23.36 STILL Having Major Problems
I'm running PHP4.0.4pl1 - the latest version. Web users are logging in as a user called dummy. I guess I'll have to look in the manual to see how to remove the process_priv(). I don't think they need it... Thanks, Hunter From: Andrew Schmidt [EMAIL PROTECTED] Date: Wed, 25 Apr 2001 15:13:24 -0400 To: Hunter Hillegas [EMAIL PROTECTED], mySQL List [EMAIL PROTECTED] Subject: Re: MySQL 3.23.36 STILL Having Major Problems Who are web users logging in as? Take away their process_priv if they have it (unless they need it). Also, what version of php are you running? As old versions contained leaks in connections In the change log for php 4.0.4 cut Fixed mysql_close(), pg_close(), msql_close() and sybase_close() - they weren't properly closing the SQL connections (Zeev) cut regards, -- Andrew - Original Message - From: Hunter Hillegas [EMAIL PROTECTED] To: Andrew Schmidt [EMAIL PROTECTED]; mySQL List [EMAIL PROTECTED] Sent: Wednesday, April 25, 2001 3:04 PM Subject: Re: MySQL 3.23.36 STILL Having Major Problems Running mysqladmin -p proc reports: too many connections... That's why I can't diagnose it. I'm logging in as root. How else would I get process_priv? I haven't changed any parameters so I should be able to get in but I cannot. What am I doing wrong? Hunter From: Andrew Schmidt [EMAIL PROTECTED] Date: Wed, 25 Apr 2001 14:46:48 -0400 To: Hunter Hillegas [EMAIL PROTECTED], mySQL List [EMAIL PROTECTED] Subject: Re: MySQL 3.23.36 STILL Having Major Problems taken from the manual: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SHO W_PROCESSLIST SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the process privilege, you can see all threads. Otherwise, you can see only your own threads. See section 7.27 KILL Syntax. If you don't use the FULL option, then only the first 100 characters of each query will be shown. This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the Process_priv privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users). I believe you will want to do the second paragraph. regards, -- Andrew - Original Message - From: Hunter Hillegas [EMAIL PROTECTED] To: mySQL List [EMAIL PROTECTED] Sent: Wednesday, April 25, 2001 11:21 AM Subject: MySQL 3.23.36 STILL Having Major Problems OS: Red Hat Linux 6.0 / Intel 1GB RAM MySQL 3.23.36 PHP4.0.4PL1 Linux kernel 2.4.3 Using the my.cnf for medium sized sites mySQL starts with safe_mysqld and there are three threads running. Queries come in and out and it's quite fast. Using mysqladmin proc status shows no queries running. After about 12 hours, the number of mySQL threads visible in ps have shot to about 75. At this point the server reports No More Connections, both through PHP and from mysqladmin. What's going on here? I can't figure it out. I can't even get in to diagnose the problem, since the server is out of connections. 99% of my queries are SELECT's. It sounds like something is hanging on to connections and not returning them... I'm using mysql_connect() in the PHP scripts. Any ideas? This has taken my Web site out about 5 or 6 times in the last two weeks. It's no fun. Anything anyone could do to help would be great. Hunter - 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
From Source to RPMs...
As I'm having a lot of problems with the source distro, I'm considering moving to your RPMs. The manual suggests that there are a lot of problems with Linux and threads, etc... and to use the prebuilt binaries if you can... I've been using a source distro for awhile. What do I have to do to install the RPMs? Any kind of prep? Hunter database - 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
mysqld sits with 'D' status
I have several mysqld processes that have 'D' status as shown by ps aux. From my limited understanding, that means they are waiting for disk and are uninterruptible? How do they get this way and what can I do to fix this? Hunter - 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
Got an error reading communication packets...
I am using 3.23.36 in conjunction with PHP 4.0.4pl1. When I compiled PHP, I used --with-mysql and let it use it's own mySQL library, not compiling against anything build under 3.23.36 (I tried the other way but the configure failed, never finding mysqlclient libraries, even though they were right there)... Anyway, I'm getting the Got an error reading communication packets in my log files... What can I do to solve this? Hunter - 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
Problems with Too Many Connections
Hello. I wrote a few days ago when I was having trouble with too many connections to my server. Since then I upgraded to 3.23.36 to see if that would help... Basically I have a PHP (4.0.4pl1) application that does both SELECTS and INSERTS against mySQL and is very active. It's my understanding that PHP closes the connection after it delivers the page to the user. When the server gets into the "too many connections" state I can't even login (though the manual seems to claim I should be able to since it does max_connections+1, I still cannot) so I can't see what's in the processlist. I'm trying to discover the best way to troubleshoot this problem. I tried upping the user max_connections variable but that didn't help... It's hard to troubleshoot since I don't know what's going on. Any help would be appreciated. Hunter - 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
Hanging w/ Too Many Connections
I have mySQL threads that are sleeping with LONG times from a PHP app. What kind of PHP call could sleep that long w/o dying? Hunter - 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
MySQL Reports No More Connections - How to Troubleshoot?
I have a Web app that uses PHP4 to connect to a mySQL server (3.22.26) on Red Hat 6.0 (Dual Pentium III). The site is very popular and every once and a while PHP can't connect to mySQL because of "Too Many Connections". I log in to the server and I am unable to execute a mysqladmin shutdown because it claims it can't find the mysql.sock file... Trying to login via mysql reports no more connections as well. I'm trying to figure out the best way to troubleshoot this. I increased the max connections to 150 but that didn't really seem to help. I assume there is a deadlock somewhere, probably with an INSERT? What's the best way to figure this out if I can't get into the server at all when it locks up? Hunter - 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
Query Question
I have a query question I can't figure out on my own. Any help is appreciated... I have three tables: create table courses ( rec_num int(5) not null default '0' auto_increment, name varchar(255), description varchar(255), class_time varchar(255), professor_id int(5), units float(4,2), location varchar(255), quarter varchar(255), department_id int (5), primary key (rec_num) ); create table enroll_course ( rec_num int(5) not null default '0' auto_increment, course_id int(5), student_id int(5), grade varchar(10), primary key(rec_num) ); create table course_prereq ( rec_num int(5) not null default '0' auto_increment, source_course_id int(5), pre_req_course_id int(5), primary key (rec_num) ); Basically I want a query that looks at courses, sees if there are any pre-reqs, if so, checks enroll_course to see if they've been met, and if not, return a list of the courses that have not been met. Is this even possible with one query? Hunter - 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
