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.

Purging old data (tickets)

Discussion in 'Technical chat' started by Dmitry Shirokov, Aug 2, 2012.

  1. Dmitry Shirokov

    Dmitry Shirokov New Member

    Hello all,

    Our company is facing an issue with very large kayako db size. Currently we have 200000+ tickets, and it's number growing rapidly, say 1500 per day. I tried to find an advice using email support, support@kayako.com, though answers were absolutely non-competent.

    1. The first suggestion was to remove old closed tickets by hands using staff interface. Wtf, do I have to do the same action 4000 times if I have 50 tickets per page? Even if I increase this value it's still unbearable amount of routine work.

    2. The second. Don't know how to comment it. They suggested this way:
    DELETE FROM swtickets WHERE dateline < 1307730600;
    DELETE FROM swticketposts WHERE dateline < 1307730600;
    What about other related to the swtickets tables? There are 17 (!!!) other tables that have relations with swtickets. And the developer that suggested me this solution really works in your company?

    3. My own solution - REST API.
    I've created a script that fetches all tickets from the given department and then delete the old ones. Guys, your API is a crap. There is no way to limit the output, can you imagine a time that needs for fetch, parse and build xml response for 100K tickets? Alright, I've changed your code and set a hard limit. There is no way to remove several tickets at once. And deleting tickets via API is SLOOOW and our db/server hangs up again.

    --------------------------------------------

    So my final question is - what is the best way to purge old tickets? I've written sql that remove data from the linked tables first, and then from swtickets ('SELECT' query for the test purpose).

    SELECT A.* FROM_UNIXTIME(A.dateline) FROM swticketposts A
    LEFT JOIN swtickets B USING(ticketid)
    WHERE (B.ticketstatusid = 3) AND (B.dateline + (86400 * 90) < UNIX_TIMESTAMP(NOW()))
    ORDER BY B.dateline
    LIMIT 10;

    SELECT * FROM swtickets WHERE (ticketstatusid = 3) AND (dateline + (86400 * 90) < UNIX_TIMESTAMP(NOW()))

    Any ideas?
     
    Jony Kendenay likes this.
  2. Gary McGrath

    Gary McGrath Staff Member

    Hi there,

    Before working for Kayako, I did actually create a script which would clean out tickets older than xx date, along with all assocaited data in all other tables. It worked well for me, I can have a root around and find it if that would help, although I would need to amend it a bit to put in some error checking and safe guards etc..

    But the basic principle of what you need to do is this:

    select swtickets where dateline < 'date' ( grab a list of the affected ticketid's )

    then run deletes on these tables using the ticketid's

    swticketauditlogs -> ticketid
    swticketlinkedtables - > ticketid
    swticketlinks -> ticketid
    swticketmergelog -> ticketid
    swticketmessageids -> ticketid
    swticketnotes -> linktypeid ( = ticketid )
    swticketposts -> ticketid
    swticketrecipients -> ticketid
    swtickettimetracks -> ticket id ( grab tickettimetrackid )
    swtickettimetracknotes - > tickettimetrackid
    swticketwatchers -> ticketid
    swcustomfieldvalues -> typeid ( = ticketid )

    swattachments -> ticketid ( need to purge the file too ideally )

    then finally delete the swtickets on ticketid's

    But before you run any kind of SQL or scripts, ensure you have plenty of backups, as its a one way trip and one mistake could turn your kayako install into a doorstop.

    Gary
     
    Jony Kendenay likes this.
  3. Dmitry Shirokov

    Dmitry Shirokov New Member

    Hi Gary,

    Thanks for reply. Looks like that you have a solution similar to my. Could you share your script please?

    By the way there are more tables than you listed in your post:

    swattachments​
    swescalationpaths​
    swticketauditlogs​
    swticketdrafts​
    swticketfollowups​
    swticketlinkchains​
    swticketlinkedtables​
    swticketlinks​
    swticketlocks​
    swticketmergelog​
    swticketmessageids​
    swticketpostlocks​
    swticketposts​
    swticketrecipients​
    swticketrecurrences​
    swtickettimetracks​
    swticketwatchers​


    So we have to remove old data from related tables (listed above):

    DELETE FROM swticketposts A
    LEFT JOIN swtickets B USING(ticketid)
    WHERE (B.ticketstatusid = 3) AND (B.dateline + (86400 * 90) < UNIX_TIMESTAMP(NOW()));

    And then delete from the master table:

    DELETE FROM swtickets WHERE (ticketstatusid = 3) AND (dateline + (86400 * 90) < UNIX_TIMESTAMP(NOW()))

    I still feel that this solution is somewhat workaround and there should be better method.
     
    Jony Kendenay likes this.
  4. Gary McGrath

    Gary McGrath Staff Member

    Hi there,

    Ok, I have attached the script I have used before. You will need to edit it, and fill in the server name, database name, username and password for your MySQL server.

    You then need to edit the date with a relevant date ( right now it deletes all tickets from last year and older )

    Make sure you have a sufficient "max execution time" set in your php.ini to allow the script to run, it can take awhile to process all the tickets if you have many to delete.

    Also note this script is provided as is and it is not authorised by Kayako and should only be run by people that understand mysql and php to ensure mistakes are not made.

    MAKE SURE YOU HAVE A VALID BACKUP OF YOUR DATABASE BEFORE RUNNING THIS SCRIPT!
    Gary
     

    Attached Files:

  5. Dmitry Shirokov

    Dmitry Shirokov New Member

    Thank you for sharing this script, i'll try it today later.
     
  6. Dmitry Shirokov

    Dmitry Shirokov New Member

  7. Yosemite

    Yosemite Member

    Dmitry,
    Thanks for sharing it. I went to the site you mentioned and I am using the code contained in the cleaner2.sql
    However in PHPMy admin when I run the query under SQL
    it returns me the following error:

    MySQL said: #1054 - Unknown column 'dateline' in 'where clause'

    Do you have an idea how to fix this?
    Do I need to change the 'dateline' value in the query with my current date?
    Thank you
     
  8. Dmitry Shirokov

    Dmitry Shirokov New Member

    Yosemite,

    I hope that you have a full backup of your db before you have tried this script :)

    1) If your kayako db is a quite large, it is better not to use the PHPAdming because it'll return an error after 30-60 seconds of waiting
    2) What is the version of the kayako that you use?
    3) Try to run these queries one by one using cli interface
     
  9. Yosemite

    Yosemite Member

    Dmitry,
    Thank you for your reply.
    1. I guess its not. Db is about 600 MB. I am not an expert so I don't know any alternative ways rather than the SQL queries on php my admin
    2. 3.70.
    3. Unfortunately I am not sure what you refer to. Where cli interface?

    Thank you
     
  10. Dmitry Shirokov

    Dmitry Shirokov New Member

    Oh. I've tested these queries on 4.x.

    Try to ask your hosting company how you can access the mysql via command line. Hope they'll help.
     
  11. Amazing, It worked to me but the tag cloud in the staff section doesn´t update to zero tickets, how do I deleted tags or get the "escalated" tags to zero?

    Thanks :)
     
  12. crazee

    crazee Member

    Are any of these scripts still valid? Do they apply to the latest stable release? Are there any new tables that need to be addressed since these were posted?

    Thanks for the scripts and any information you can provide.
     
  13. Dmitry Shirokov

    Dmitry Shirokov New Member

    Hi crazee.

    We still use this script on our helpdesk instances, ver 4.x.

    Cheers.
     
  14. crazee

    crazee Member

    Thanks Dmitry! This is going to save me a lot of time.
     
  15. crazee

    crazee Member

    I have been testing this out and here are a couple additional tables that you may want to consider clearing as well.

    Table: swsearchindex | Related Field: objid
    Table: swticketrecurrences | Related Field: ticketid
     
  16. DonLapeno

    DonLapeno Established Member

    I think they need to include this in kayako, no reason not to, people should not have to run scripts direct on the DB to clean out their Kayako systems.

    Any thoughts on if this works with recent Kayako versions?
     
  17. jcossota

    jcossota Established Member

    Question, what advantage does running one of these "cleaner scripts" have over say purging old attachments for the effect of saving space?
     
  18. Drew Keller

    Drew Keller Just one person in a world of millions.

    These scripts are specifically focused on cleanup of some database tables. I would expect attachments to save more space, however these scripts may improve performance .
     
  19. Alan 6k8

    Alan 6k8 New Member

    Hi,

    I have found the solution offered here incomplete and vulnerable. List of tables may change in time across versions thus SQL scripts would have to be constantly checked and updated. Also attachments are not handled at all. I've chosen a slightly different approach - use SQL query (quite fast and simple to use) to search for ticket ids only and then use REST API to delete ticket properly.
    My use case was to remove tickets from trash where last activity is beyond specified threshold (in days) and that is what cleaner.php does. You may easily update it to suit you needs, just play around with the query.

    Cheers.
     

    Attached Files:

    Last edited: Mar 10, 2015
    factor likes this.
  20. jnet

    jnet Reputed Member

    Has this problem resolved . I do not want to use any kind of script I need a solution to archive old kayako tickets some where I and other staff can easy search thing sin it while it remains separate from main help desk.
     

Share This Page