Is it safe to clean scheduler_stats?


#1

Hi,

I’m investigating my sql dump because it took 15mb with only 3 post. I figure out that there is a table which is named “scheduler_stats” and it have about 40k lines of data. I just want to learn is it safe to clean this table with some sql query like “delete from scheduler_stats where id < 500;” ? Or may be there is a feature which is come with discourse by default for cleaning that table?

Also I found that it is possible to completely remove posts which is already “deleted” by :

DELETE FROM posts WHERE deleted_at IS NOT NULL;
DELETE FROM topics WHERE deleted_at IS NOT NULL;

But I don’t sure about if this is safe or not…
Does anyone have any idea?


(Jeff Atwood) #2

Not familiar with this table any ideas @sam?


(Sam Saffron) #3

Sure, you can delete it


(Mittineague) #4

The fact that you ask if it’s safe, I’d have to answer “probably not”.

A good number of other tables “foreign key” to the posts and topics tables.

I’m guessing that SQL queries have been written on the assumption that rows have not been deleted. That is, unless a query has been written to take into account that a row has been deleted there is a good chance there will be errors.

Another thing to consider is routing. eg. any link that references the deleted topic / post may no longer work as expected (eg. “internal server error” instead of “not found”)


#5

Thank you. I’m gonna investigate it tonight. I’m planning to use cron jobs for clean these data regularly. (I mean “deleted” posts, and scheduler_stats.)


(Sam Saffron) #6

It is safe to nuke scheduler stats, nuking posts like that is going to leave corruption behind


#7

Thank you @sam. Do you have any idea about schema_migration_details & schema_migrations? Are they safe to nuke too?


(Sam Saffron) #8

No it is not safe, and this entire endeavor is very likely to leave you with an unsupportable broken install


#9

Ah, thank you. I’m testing these changes on my local server, so no problem. I just want to figure out database scheme, what can be deleted what is not… and if I can, make something for deleting “deleted” posts forever. Thank you again for your kindness and patience.


(Felix Freiberger) #10

Good luck! Not every way to corrupt the database will immediately show obvious symptoms…

I highly recommend you trust @sam’s opinion:


(cpradio) #11

You don’t need to do this.

There is already a .destroy() on the Post object. If you go into Rails console and do a p = Post.find_by_id(...), and then do p.destroy(), it should destroy it entirely (fairly certain)

You should be focusing on how to expose that via a Plugin in the form of a button to the UI

Similarly, you should look at which handles old hidden posts (granted, this may still leave remanents behind that calling .destroy() on the post object wouldn’t).


#12

Oh thank you @cpradio! Probably this is what I’m looking for! Also .destroy is very good trick too! Thank you very much for these useful informations… :thumbsup:

I agree with you, I should do this by plugin but I don’t have ruby skills. That’s why I’m using low-level stuff.


(cpradio) #13

well, then for the time being, if you don’t think you’ll do this often, simply remote into the server, enter the app, start the rails console, and use the destroy command.

One thing that should be made known here is that the reason this doesn’t exist in a public way is that the risks outweigh the benefits. The size of a database record is incredibly small and likely not an issue with your storage space. If you were to permanently delete the wrong post, or if it goes wrong, you could be left in a worst position than what you would be by leaving it.

Either way, if you know what you are doing, .destroy should help you out with your goal.