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.

sql errors help

Discussion in 'Database Discussions' started by ukshane, Mar 29, 2012.

  1. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    this is causing cpu to spike around 50% every so often

    /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --log-error/etc/httpd/logs/mysqld.err --pid-file/var/lib/mysql/srv1.worldwidexs.com.au.pid

    and i looked in the mysqld.err this what it shows

    Code:
    120328  9:56:07 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:56:11 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:56:16 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:57:37 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:57:48 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:58:03 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:58:26 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:59:00 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:59:06 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328  9:59:53 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:00:08 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:00:13 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:00:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:02:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:03:57 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_1.MYI'; try to repair it
    120328 10:03:57 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:04:11 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_1.MYI'; try to repair it
    120328 10:04:11 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:04:15 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_1.MYI'; try to repair it
    120328 10:04:15 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:04:20 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:05:25 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:06:40 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:07:23 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:08:13 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:08:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:08:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_2.MYI'; try to repair it
    120328 10:08:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:08:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_1.MYI'; try to repair it
    120328 10:08:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:09:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_1.MYI'; try to repair it
    120328 10:09:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:10:27 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:10:52 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:11:55 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_1.MYI'; try to repair it
    120328 10:12:03 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:12:09 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:12:13 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:12:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:12:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:12:47 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:13:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_1.MYI'; try to repair it
    120328 10:13:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:13:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:14:08 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
    120328 10:15:41 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1a4f_0.MYI'; try to repair it
     
    #1 ukshane, Mar 29, 2012
    Last edited: Mar 29, 2012
  2. 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
  3. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    would this cause any problems to people sql database if i did fix it
     
  4. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    i got this mysqladmin: unknown variable 'tmpdir=/mysqltmp'
     
  5. 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
    I am not understanding the error. The tmpdir=/mysqltmp line would be placed into /etc/my.cnf file after you've created that folder /mysqltmp and given it the right permissions. You'd also have to restart MySQL. Did you follow the directions in my post on how to create the directory and change to use it exactly as noted?

    As for causing problems, right now what is happening is causing major problems so it needs to be fixed and I've detailed how to fix it in that post I linked to. If /tmp is filling up by temporary tables from MySQL, MySQL will hang and all sites using it won't work. I'd call that pretty major.
     
  6. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    ye i did that but i got that mysqladmin error i did exactly everything it says to fix it
     
  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
    Please post what you did precisely step-by-step so we can see what hasn't been done properly.
     
  8. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    ok i put this in my.cnf
    #[mysqld]
    tmpdir=/mysqltmp

    then

    mkdir /mysqltmp
    chmod 1777 /mysqltmp

    then

    /etc/init.d/mysql restart
    mysqladmin var | grep tmpdir

    then it fails after that
     
  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
    Do you actually have this # sign in front of [mysqld] in /etc/my.cnf file?

    #[mysqld]

    Because that would comment out the line indicating that tmpdir=/mysqltmp would be under the mysqld service. You wouldn't ever put a # in front of that. It should be:

    Code:
    [mysqld]
    tmpdir=/mysqltmp
     
  10. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    ye that worked the # was already there went i looked at the file

    so how will i no if the tmp as now defo worked it says 99%full on my status2k
     
  11. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    also would there be a chance the reason be there is no tmpdir as the ones i see is tmp
     
  12. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    this is my cnf i got

    [mysqld]
    max_allowed_packet=100M
    max_connections=1000
    #safe-show-database
    # Connection timeout
    connect_timeout = 20

    # Timeout for inactive connections
    wait_timeout = 20

    # Network buffer length
    net_buffer_length = 16K
    log-error = /etc/httpd/logs/mysqld.err
    binlog_cache_size = 2M
    table_cache = 6588
    query_cache_size = 8M
    join_buffer_size = 320M
    thread_cache_size = 6
    max_heap_table_size = 320M
    #query_cache_size = 312M
    key_buffer_size = 128M
    [client]
    socket=/home/mysql/mysql.sock
    [mysqld]
    tmpdir=/mysqltmp
    #set-variable = max_connections=500
    #log-slow-queries
    #safe-show-database
     
  13. 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
    Hello,

    Unfortunately, the existing /etc/my.cnf still isn't correct. [mysqld] shouldn't be repeated twice in that file. The tmpdir setting should be under the first instance of [mysqld] rather than putting it again there. This would be a better way to do it:

    Code:
    [mysqld]
    max_allowed_packet=100M
    max_connections=1000
    connect_timeout = 20
    wait_timeout = 20
    net_buffer_length = 16K
    log-error = /etc/httpd/logs/mysqld.err
    binlog_cache_size = 2M
    table_cache = 6588
    query_cache_size = 8M
    join_buffer_size = 320M
    thread_cache_size = 6
    max_heap_table_size = 320M
    key_buffer_size = 128M
    tmpdir=/mysqltmp
    
    [client]
    socket=/home/mysql/mysql.sock
    As for tmpdir, there isn't a directory by that name as that's the directive name for it in MySQL. You've now set it to /mysqltmp instead. If mysqladmin var | grep tmpdir shows /mysqltmp then it worked to change the location, and now MySQL will write to /mysqltmp rather than /tmp location.

    If your /tmp is still full, then you could resize it and review one of the threads on how to resize /tmp partition. I've linked a thread earlier that also talks about doing that.

    Thanks!
     
  14. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    ok i try it now cheers would i need to reboot to make it work
     
  15. 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
    A reboot is not needed for the directory to change for MySQL's tmpdir setting. Is mysqladmin var | grep tmpdir showing the new location of /mysqltmp? If so, then I'm not certain what still isn't working. If it is /tmp size itself, then I already indicated in my last post that you can resize /tmp itself if other files are there causing it to be filled. None of this requires a reboot.
     
  16. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    ye it showed mysqltmp but still high on tmp

    i give it a try what you said
     
  17. ukshane

    ukshane Member

    Joined:
    Mar 29, 2012
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    i just did the resize and it still shows near full
     
  18. 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
    Restart Apache then possibly or actually look at the files in /tmp to see what is filling it up.
     
Loading...

Share This Page