*

oscman

  • ****
  • 227 posts
Maintenace deletes only threads, no messages
« on: May 07, 2020, 10:45:17 AM »
Is there a specific reason that the maintenance removes only threads and not old messages? why would you keep messages that are not visible anywhere? It slows down the database by ALOT

*

MB Themes

Re: Maintenace deletes only threads, no messages
« Reply #1 on: May 07, 2020, 12:23:08 PM »
@oscman
That's strange,... foreign key was not added for fluent installation, easiest solution for you is to add foreign key on t_messages table on key t_messages.fk_i_thread_id -> t_threads.i_thread_id

Also update in model function removeThreadsByDate as following:
Code: [Select]
public function removeThreadsByDate( $datetime ) {
  $this->dao->query('DELETE FROM '. $this->getTable_messages() . ' WHERE fk_i_thread_id in (SELECT i_thread_id FROM '. $this->getTable_threads() . ' WHERE d_datetime <= "' . $datetime . '")' . $thread_id );
  $this->dao->query('DELETE FROM '. $this->getTable_threads() . ' WHERE d_datetime <= "' . $datetime . '"' );
}
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

*

oscman

  • ****
  • 227 posts
Re: Maintenace deletes only threads, no messages
« Reply #2 on: May 08, 2020, 12:44:53 PM »
Thanks for you reply.

There are no records of adding this foreign keys in plugin db install.

Can you tell me how to add the foreign keys? i am getting an error when trying this:

Code: [Select]
ALTER TABLE oc_t_im_messages
ADD FOREIGN KEY (fk_i_thread_id)
REFERENCES oc_t_im_threads (i_thread_id)

#1452 - Cannot add or update a child row: a foreign key constraint fails (db.#sql-104_965, CONSTRAINT #sql-104_965_ibfk_1 FOREIGN KEY (fk_i_thread_id) REFERENCES oc_t_im_threads (i_thread_id))

i don't want to delete all existing messages if that's the reason of the error

*

MB Themes

Re: Maintenace deletes only threads, no messages
« Reply #3 on: May 08, 2020, 08:25:09 PM »
Your key is correct, but probably thread ids in messages table already reference to non existing threads
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots