Results 1 to 8 of 8
  1. #1
    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

  2. #2
    Perhaps you should pay attention to the errors before MySQL stopped functioning. Correct the InnoDB issue and things may start to work correctly.

  3. #3
    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?

  4. #4

  5. #5
    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?

  6. #6
    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!

  7. #7
    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.
    OH do i hate when people brag about uptimes...
    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

  8. #8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •