The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

skip-locking and log_slow_queries deprecated errors and what to do

Discussion in 'Database Discussions' started by Metro2, Apr 27, 2012.

  1. Metro2

    Metro2 Well-Known Member

    Joined:
    May 24, 2006
    Messages:
    376
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    USA
    cPanel Access Level:
    Root Administrator
    Has anyone else recently noticed the follwowing showing up in their /usr/local/cpanel/logs/error_log:

    Anyone seeing this? Anyone know what exact action to take?

    I've just dont some searches here on the forums and google as well and I can't seem to find documentation on where / how to fix this.
     
  2. Astral God

    Astral God Well-Known Member

    Joined:
    Sep 27, 2010
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    Just edit /etc/my.cnf and add or replace existing configuration with those on the messages.

    Ex.: if 'skip-locking' is present, replace with 'skip-external-locking'. If not, just add it in one single line.

    Restart MySQL server (# service mysql restart).
     
  3. Metro2

    Metro2 Well-Known Member

    Joined:
    May 24, 2006
    Messages:
    376
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    USA
    cPanel Access Level:
    Root Administrator
    Thanks Astral God, I did just find that much doing a googling a little while ago, so that takes care of the skip-locking part.

    Now in the process of checking out the log_slow_queries part because in my my.cnf files there are hyphens (log-slow-queries), not underscores (log_slow_queries) as shown in the cpanel error log. Strange...
     
    #3 Metro2, Apr 27, 2012
    Last edited: Apr 27, 2012
  4. Astral God

    Astral God Well-Known Member

    Joined:
    Sep 27, 2010
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    Check https://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

    :)
     
  5. Metro2

    Metro2 Well-Known Member

    Joined:
    May 24, 2006
    Messages:
    376
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    USA
    cPanel Access Level:
    Root Administrator
    Right, and that's what I found with initial Google searches on the topic as well.

    But here's the thing - apparently depending on who you are and which kind of server you're using, the underscores (_) that appear in the error log could also be represented by hyphens (-)

    So in my particular case, in /etc/my.cnf I previously had this:

    And I replaced it with this:

    And now that outputs the logfile for it to:

    Whereas on some people's servers the path to that slow.log will be slightly different, maybe more like the page you discovered in your google search.

    Now that I've narrowed down those details I'm off to try to find a way to make /var/lib/mysql/(hostname)-slow.log rotate according to my settings so that the log file does not become huge (which can easily happen, especially if your customers are running scripts that are not coded well).

    So far I cannot find a way to add /var/lib/mysql/(hostname)-slow.log to the log rotations, neither in WHM >> Service Configuration >> Apache Configuration >> Log Rotation nor in WHM >> Service Configuration >> cPanel Log Rotation Configuration

    So that leaves the /var/lib/mysql/(hostname)-slow.log to grow huge unless manually maintained.
     
    #5 Metro2, May 1, 2012
    Last edited: May 1, 2012
  6. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    322
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    you're not meant to leave slow query logging enabled 24/7. Use it for diagnostic purposes if you have slow query issues then after you solve those slow queries, disable slow query logging.
     
  7. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Also, skip-external-locking is the default, so there's no reason for it to be enabled in /etc/my.cnf

    To check if a setting is or is not enabled, you can check it using mysqladmin:

    Code:
    mysqladmin var | grep external
    You'll see the following:

    Code:
    # mysqladmin var | grep external
    | skip_external_locking                   | ON
    As for the slow query log, you might try adding it to logrotate's /etc/logrotate.conf file to rotate it if you want. I don't see why you'd enable and disable it periodically personally. The following may work:

    Code:
    /var/lib/mysql/hostname-slow.log {
        missingok
        weekly
        minsize 1M
        create 0660 mysql mysql
        rotate 4
    }
    The only issue here is that I'm not sure if MySQL needs restarted when it rotates. If it does, then instead you'd want to put that configuration into /etc/logrotate.d/ folder as slowlog and then have MySQL restarted for it in that file you create. Compare it to the exim one as an example.
     
  8. Metro2

    Metro2 Well-Known Member

    Joined:
    May 24, 2006
    Messages:
    376
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    USA
    cPanel Access Level:
    Root Administrator
    Thanks VERY much for the info guys.

    So if I understand you all correctly - the default /etc/my.cnf file on my cPanel servers as provisioned by my data center is including two lines/commands that shouldn't even be there, so as mentioned these two lines:

    log-slow-queries

    skip-external-locking

    Should not even be in /etc/my.cnf at all unless I enable them to debug / look for specific problems.

    Correct?
     
  9. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The slow query log is useful to have in /etc/my.cnf file. I don't agree with the prior user who stated it should only be there for debugging. It should always be there. That log rarely fills up. And, if you don't have it enabled first off and do after the fact, you won't have the log already when you actually need it. The general log shouldn't be default enabled. The slow query log most definitely should be.

    As for skip-external-locking, it isn't a debugging tool. It's the default already as previously stated. There's no point having it in your /etc/my.cnf unless you like putting in already default variables. It's a waste of space in /etc/my.cnf
     
  10. Metro2

    Metro2 Well-Known Member

    Joined:
    May 24, 2006
    Messages:
    376
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    USA
    cPanel Access Level:
    Root Administrator
    This has been extremely helpful, thank you guys for all the feedback!!
     
  11. Metro2

    Metro2 Well-Known Member

    Joined:
    May 24, 2006
    Messages:
    376
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    USA
    cPanel Access Level:
    Root Administrator
    PS - Tristan, I'm assuming that when you suggested the possibility of adding this code:

    Code:
    /var/lib/mysql/hostname-slow.log {
        missingok
        weekly
        minsize 1M
        create 0660 mysql mysql
        rotate 4
    }
    
    to the logrotate.conf , that the word "hostname" should be replaced with the hostname of the actual server.

    I know, kind of dumb question, but just making sure.
     
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Yes, you'd use the actual file's name.
     
Loading...

Share This Page