Hi,
have you finished your summer vacation? 😄
I have a question to ask you all.

Given that I had already written a post about it, a more general request to check the consistency of the db when I used the 1.9.x.

Now, switched to 1.10.x, I have a more contained target, I would like to clean up the threads tables that I think have drifted... ... what can be the right sequence of queries to identify all the orphaned threads??? The system has less than 100,000 tickets in the db, but it certainly has more that one million of extra threads...

...they take me 1GB, it's not much considering the weight of the attachments (65GB), but sooner or later if I don't shrink the tables I think I'll have some management problems!!!

Some idea?
Thanks for your attention!

73s,
Arturo.

Summer vacation? Oh, I knew I forgot to do something.

I'm curious why you think that you would have orphaned threads.
Have you been deleting tickets not using the osTicket ui?

Hi @ntozier,

Have you been deleting tickets not using the osTicket ui?

yes, actually I needed to schedule an automatic cleaning, then I put in a crontab script that, based on some criteria, deletes tickets and users!

Stuff already seen around: a select that returns me a list of id according to my criterion, list of id I use to invoke the function:

function delete_ticket($id) {
        $t = new Ticket ( $id );

        log_data("Deleting old ticket $id:" . $t->getSubject () . "\n");
        return $t->delete();

        return true;
}

... but this seem has caused some problems on threads :-(

so besides the fact that, more generally, I think a db check procedure is useful; now I wondered how to do two things:

  1. get a query that can highlight all orphan threads, so as to delete them one-time
  2. What do I need to get a complete removal of the old ticket

I would like to keep the archive under 100000 tickets and clean my db. For more I would like to carry out the operation in two phases: a first one in which I pass (eg after 3 months) the ticket in 'Archive' mode (ie not accessible to the end user) and a second one in which I permanently delete it (eg after 12 months). I think it's a bit of something for everyone, this to respect the laws (data retention) and keep the db more compact!

73s,
Arturo.

    Sorry for the delay in the response. I wanted to make sure I had time to write the length response that this was going to require.

    Deleting a ticket and/or user at the sql level is a lot more involved than just deleting all tickets with a specific user id.
    The problem is that there are a number of other tables that have entries based on the existence of that ticket or user.
    the ost_form_entry_values might have data. the ost_thread, ost_thread_collaborator, ost_thread_entry, ost_thread_entry_email, ost_thread_event, and ost_thread_referral tables might also have entries. ost_user, ost_user__cdata, ost_user_account, and ost_user_email. All of those things would have to be purged also.

    You can get the user id from ost_user. Then you can use that to delete information from:
    ost_user__cdata (you could always just drop this table, it will be recreated)
    ost_user_account
    ost_user_email

    With the ticket_id you then delete entries from the following:
    ost_ticket
    ost_thread,
    ost_thread_collaborator,
    ost_thread_entry,
    ost_thread_entry_email,
    ost_thread_event,
    ost_thread_referral

    summarize:
    Use the id from ost_user to identify and deleted from ost_user__cdata (you could always just drop this table, it will be recreated), ost_user_account, ost_user_email
    Use the ticket_id against the object_id and where object_type is equal to "T" from ost_thread to find the [thread] id, then delete the record from ost_thread
    Then delete all entries in ost_thread_collaborator, ost_thread_entry, ost_thread_entry_email, ost_thread_event, and ost_thread_referral where the thread_id is equal to the ost_thread.id

    Hi @ntozier,
    later I read again to see if I understood wholly the answer, but let me give you a first answer on the fly.

    I do not try to delete via sql, I use the sql only to have a list of tickets that satisfies my conditions, after I use the objects of the software to delete it:

    function delete_ticket($id) {
        $t = new Ticket ( $id );

        log_data("Deleting old ticket $id:" . $t->getSubject () . "\n");
        
        return $t->delete();
}

    and I expected the code to delete several things but (maybe in the past) something must not have worked because I see that some tables are too big (at least I think) and based on what you tell me, the suspicions are more concrete:

    these are the results of the following query:

    select YEAR(timestamp) as Year, COUNT(YEAR(timestamp)) as Events 
FROM thread_event A LEFT JOIN thread B ON thread_id = B.id
WHERE B.id IS NULL GROUP BY YEAR(timestamp)

    am I wrong to say that these are orphans and that ticket->delete() does not delete (in the past!?) this information but that it should be removed?

    I think someone decided to introduce and use the foreign keys, this db fails to be converted without first mega cleaning ... I never made an insert or a delete directly in the db but I realize that something is wrong! With almost 93,000 tickets in 2019, it seems strange to me, for example, to have a table thread_entry_email with 2 million records (over 1GB of data).

    73s,
    Arturo.

    So if...

    SELECT COUNT(A.id) AS 'e-mail Entry' 
FROM thread_entry_email A LEFT JOIN thread_entry B ON thread_entry_id = B.id
WHERE B.id IS NULL

    shows the number 1.742.560; then I can proceed to a massive deletion of 1,7 milion of row in thread_entry_email ???

    if you wonder why,
    because in a previous life I was a janitor :-D 😁 😁 and I don't like seeing the dust around!

    However, jokes aside, a db verification procedure would be really useful and preparatory to the introduction of some external key that can reduce and better highlight the problems of data consistency.

    73s,
    Arturo.

    @rinux

    It’s not complete yet, we are still working it out but will be addressed in the next release. We are also adding a cleaning method for deleted ticket’s thread entries.

    Cheers.

    @KevinTheJedi & @ntozier, great guys, good job !!!

    I'm trying to understand, let's say that the first useful step is to clean up and lighten the db. I await developments, I hope that in some way the improvements can be carried back even on 1.10.x. Among other things, I was thinking that many of the problems are perhaps also related to the fact that this system has been around for so long with old versions! So from this was born my hope to have a procedure that performs a deep check on the db and highlights any anomalies; for example, I wonder if having tens of thousands of drafts can be considered normal (I haven't checked yet, but the number of records seems high to me).

    Well,
    have a nice weekend...

    73s,
    Arturo.

    Well!

    I was able to delete 22000 on 22390 draft that are empty: 😅

    DELETE FROM vh_draft WHERE namespace LIKE 'ticket.%' AND LENGTH(body) = 0

    perhaps it could be the case, if it has not already been thought of, to activate the saving only when more than a dozen characters have been entered. ;-)

    73s,
    Arturo.

    My drafts table has 5 entries in it. I rarely ever see it more then ten. None of them are older than 8/14/2019. The clean up routines clean them up fine on my system.

    Hi!

    @ntozier I have no doubt ...

    Now even the installation in question has only 3 records; but before it had over 20 thousand of them stuck; maybe they came from a previous bad upgrade (!?!?), with the fields used for cleaning not set as expected (NULL !?).

    Now I have inserted the call to "api/cron.php" in crontab and it's much better but the bulk was only possible with that query:

    DELETE FROM draft WHERE namespace LIKE 'ticket.%' AND LENGTH(body) = 0 ;-)

    PS: maybe in the 1.9.X or previous the created field were not present so after I install the 1.10.X has become NULL?

    73s,
    Arturo.

    Write a Reply...