We have about 300 megabytes (in plain text) of content in comments table. We use OJS 2.4.8-5 and comments are actually disabled in all journals. I found the way to merge a lot of spam users, but how can i clean all comments? Or may be sort (by stop phrase or url) them and clean?
I can make something like this:
mysql> select comment_id from ojs.comments WHERE body like '%iframe%';
10740 rows in set (12.65 sec)
But how can I delete this comments?
I already merge a lot of users, may be it is possible to make a query to delete comments with non existing author user_id?
That’s outside the scope of this forum – I’d suggest looking at some general SQL resources, or using a UI like phpMyAdmin to help work with the content. But if you take your SQL query above:
select comment_id from ojs.comments WHERE body like '%iframe%';
…and change it from a SELECT command to a DELETE command…
DELETE FROM ojs.comments WHERE body like '%iframe%';
…that command will remove all entries from the comments table where the body of the comment contains iframe.
Note that SQL DELETE statements might remove a lot of content at once! Take a backup of your entire database first, in case you make a mistake.
Regards,
Alec Smecher
Public Knowledge Project Team
We are actually have no useful comments and comments are disabled in all readers tools fo all our journals. I found no any high level tool to moderate/read/approve|delete comments… and now i will try to check it one more time by exporting from sql, and… may be delete it all. and 1GB database becomes 350-400MB database (wo fake users and comments).
Our database was about 1050MB.
About 560 000 of users was deleted.
About 95 000 comments was deleted (just all comments).
Now we have about 1500 users and 290MB database (14 journals with from one to six years archive). Users was about 350MB, comments - more than 400MB.
Now comments are disabled in the readers tools, google recapcha is active and we get about 5-10 new spam users per day… service should to be cleaned before upgrading to ojs3.