1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

MySQL locking up with "Waiting for Table Level Lock"

Discussion in 'Technical chat' started by Donald Merriman, Sep 22, 2016.

  1. For the last 2 days the MySQL Database has been locking up with queries that have been holding up the processes queue.

    Waiting queries are reporting "Waiting for Table Level Lock" State in the Client connections.

    Anyone have any suggestions to alleviate this?
     
  2. Gary McGrath

    Gary McGrath Staff Member

    Hi Donald,

    Did your checks highlight which table was locked?

    Can you check the engine type of the table swsearchindex?

    Gary
     
  3. Hi Gary,

    Thank you for the reply - The queries that are causing the locking seem to be random - On the next lock up I'll get examples. Below is the table type for the swsearchindex:

    upload_2016-9-23_8-58-58.png

    Will converting the engine to InnoDB for the table cause any issues with Kayako?
     
  4. Gary McGrath

    Gary McGrath Staff Member

    What version is your MySQL DB? it needs to be 5.6.4 or above, if so, then yes convert it to InnoDB and that will hopefully stop the locks

    Gary
     
  5. Hi Gary,

    The MySQL version is 5.7.15 - I'll convert it tonight and report back on the outcome. Thank you again for your assistance.
     
  6. Just to update the ticket with information - The query seems to want to run twice, below is one query that locked up the DB - I had to kill the queries to free the lock.

    SELECT tickets.* FROM swtickets AS tickets
    LEFT JOIN swticketposts AS ticketposts ON (tickets.ticketid = ticketposts.ticketid)
    LEFT JOIN swticketlocks AS ticketlocks ON (tickets.ticketid = ticketlocks.ticketid)
    LEFT JOIN swticketnotes AS ticketnotes ON (tickets.ticketid = ticketnotes.linktypeid)
    LEFT JOIN swticketwatchers AS ticketwatchers ON (tickets.ticketid = ticketwatchers.ticketid AND ticketwatchers.staffid = '260')
    LEFT JOIN swusers AS users ON (tickets.userid = users.userid)
    LEFT JOIN swusergroups AS usergroups ON (users.usergroupid = usergroups.usergroupid)
    LEFT JOIN swuserorganizations AS userorganizations ON (users.userorganizationid = userorganizations.userorganizationid)
    LEFT JOIN swticketpriorities AS ticketpriorities ON (tickets.priorityid = ticketpriorities.priorityid)
    LEFT JOIN swticketstatus AS ticketstatus ON (tickets.ticketstatusid = ticketstatus.ticketstatusid)
    WHERE ((tickets.subject LIKE '%NXH-800-54389%') OR (ticketposts.contents LIKE '%NXH-800-54389%')
    OR (ticketnotes.note LIKE '%NXH-800-54389%'))
    AND tickets.departmentid IN ('29','0') AND tickets.ticketstatusid != '-1' AND tickets.ownerstaffid != '-1' ORDER BY tickets.lastactivity desc LIMIT 0,400
     
  7. Update

    Converting the swsearchindex from MyISAM to InnoDB resolved the issue with the queries being stuck in "Waiting for Table Level Lock".

    Thank you for your assistance.
     
  8. Gary McGrath

    Gary McGrath Staff Member

    awesome, glad I could help :D

    Gary
     

Share This Page