Results 1 to 8 of 8
Thread: mySQL mystery....
-
04-09-2003, 12:25 AM #1Web Hosting Guru
- Join Date
- Dec 2002
- Posts
- 316
mySQL mystery....
Every few days late at night... between 12AM and 2AM mySQL will stop working. It cant be restarted untill I kill the last one running but nothing can be read from the databse and the non wokring mySQL process shows 0% CPU usage. I checked all the crons and theres nothing out of the ordinary at those times.
It appears there are porcesses hanging... but that souldnt totally halt mySQL... This only happens every few days wich is more annoying becasue I dont know when to watch for it. Anybody think they can help?
(RH 8.0 + mySQL 3.23.52)
Heres what the logs say.
Code:Number of processes running now: 1 mysqld process hanging, pid 9666 - killed 030409 00:05:57 mysqld restarted Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html 030409 0:05:57 Found an entry in the 'db' table with empty database name; Skipped /usr/libexec/mysqld: ready for connections Number of processes running now: 1 mysqld process hanging, pid 5529 - killed 030409 00:05:58 mysqld restarted Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html 030409 0:05:58 Found an entry in the 'db' table with empty database name; Skipped /usr/libexec/mysqld: ready for connections Number of processes running now: 1 mysqld process hanging, pid 5554 - killed 030409 00:06:07 mysqld restarted Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html 030409 0:06:08 Found an entry in the 'db' table with empty database name; Skipped /usr/libexec/mysqld: ready for connections ##-----THIS IS THE POINT WHERE MYSQL STOPPS WORKING-----## Number of processes running now: 2 mysqld process hanging, pid 5583 - killed mysqld process hanging, pid 5583 - killed 030409 00:06:08 mysqld restarted 030409 0:06:08 Can't start server: Bind on TCP/IP port: Address already in use 030409 0:06:08 Do you already have another mysqld server running on port: 3306 ? 030409 0:06:08 Aborting 030409 0:06:08 /usr/libexec/mysqld: Shutdown Complete 030409 00:06:08 mysqld ended 030409 00:08:24 mysqld started 030409 0:08:24 Can't start server: Bind on TCP/IP port: Address already in use 030409 0:08:24 Do you already have another mysqld server running on port: 3306 ? 030409 0:08:24 Aborting 030409 0:08:24 /usr/libexec/mysqld: Shutdown Complete
-
04-09-2003, 09:32 AM #2Disabled
- Join Date
- Feb 2003
- Posts
- 344
Perhaps you should pay attention to the errors before MySQL stopped functioning. Correct the InnoDB issue and things may start to work correctly.
-
04-09-2003, 08:51 PM #3Web Hosting Guru
- Join Date
- Dec 2002
- Posts
- 316
Those errors are for InnoDB wich I don't use.... SOoI don't have it set up. I really don't think it has anything to do with the problem, But I just set InnoDB up anyway to see if it helps....
BTW is there an easy way to convert My databases to InnoDB?
-
04-09-2003, 08:55 PM #4Disabled
- Join Date
- Feb 2003
- Posts
- 344
-
04-09-2003, 08:58 PM #5Web Hosting Guru
- Join Date
- Dec 2002
- Posts
- 316
Hmmm.... Looks familiar.
http://www.innodb.com/ibman.html#Altering_InnoDB_tables
To me thats not an easy way.
I've heard that InnoDB is actually slower and eats more memory. So I will only have to try this if the problem continues.
P.S. Could this problem be casued by somone running a crappy script?
-
11-10-2004, 11:23 PM #6Newbie
- Join Date
- Nov 2004
- Posts
- 12
Monkey,
Did you ever figure this problem out? I've been having the same problem as you. It's been going on for months but I'm really getting fed up with it. Every 3 - 5 days, between 12 AM and 2 AM, MySQL stops responding to certain queries. Restarting MySQL from WHM doesn't help and I find that only fully rebooting the machine fixes the problem. It really sucks becuase I never know when it will go down. I can't stay up that late, so every morning when I get up I check to verify that all is ok and if not, I reboot the machine. This is pretty ridiculous, considering that I've had machines in the past (with much lesser hardware) running MySQL and they've gone over 200 days without a reboot.
Strange thing is that MySQL does respond to SOME queries, but not all. For example, I host a phpBB forum and when MySQL starts to be stubborn you can view posts, but you can't use the search feature. The search just keeps working, with no errors reported. On other sites I host, you can view data from one table, but not from others. And phpMyAdmin won't let you view any tables at all.
What's also strange is that the resources being used are usually very low when I check in the morning and find MySQL not working properly. CPU is below 0.20 and and memory usage is below 50%, if not much lower.
My server specs are:
- Dual Xeon 2.4 P4s
- 1 GB RAM
- Redhat Enterprise
- MySQL 4.0.22
- Apache 1.3.33 / PHP 4.3.9
- Cpanel/WHM 9.9.7, cpanel 9.9.8-R5
I too suspect that one of my client's sites is causing the problem. But I have yet to trying moving this client to another server.
The server works great when MySQL isn't misbehaving. I wish it could work great all the time. I need a life outside of babysitting it.
I would appreciate any help you can give me!
Thanks!
-
11-10-2004, 11:32 PM #7Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
This is pretty ridiculous, considering that I've had machines in the past (with much lesser hardware) running MySQL and they've gone over 200 days without a reboot.Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
11-10-2004, 11:39 PM #8Newbie
- Join Date
- Nov 2004
- Posts
- 12
Hey, it's true, I just checked an old server of mine the other day and it was at 196 days. I haven't checked it since then, but I know that one hasn't needed any rebooting.
This new machine can't run more than 5 days without MySQL throwing a fit. What up with that?
Could a poor PHP/MySQL scripting cause this problem?
Like Monkey, I don't see any cronjobs out of the ordinary.
Also, I've noticed that there tends to be a large number of sleeping MySQL processes running from one user -- the one that I suspect could be the problem -- when I wake up in the morning and find MySQL not working.
Any other ideas besides comments on my old server's uptime?Last edited by mfos239; 11-10-2004 at 11:44 PM.