How to bulk delete comments?

teddink

Joined: 2006-06-22
Posts: 7

Posted: Fri, 2006-09-29 23:25

Gallery version = 2.1.2 core 1.1.0.2
PHP version = 4.4.4 cgi
Webserver = Apache/1.3.37 (Unix)
Database = mysql 4.0.27-standard-log, lock.system=flock
Toolkits = ArchiveUpload, Exif, Gd, Getid3, NetPBM, SquareThumb, Thumbnail
Acceleration = none, none
Operating system = Linux infong 2.4 #1 SMP Thu Jan 13 08:59:31 CET 2005 i686 unknown
Default theme = matrix
Locale = en_US
Browser = Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2)

Unfortunately I left the ability for guest to comment on the root album. I eneded up with 350 or so comments on the root album. Is there an easy way to bulk delete all of the comments for a particular album? Needless to say that deleting them one at a time will get very tiresome...

Thanks in advance.

valiant

Joined: 2003-01-04
Posts: 32509

Posted: Fri, 2006-09-29 23:31

there's no interface for that yet.

you can file a feature request for it.

if you decide to manually delete the comments in the database, don't forget the g2_Entity and g2_ChildEntity tables which hold a row for each row in g2_Comment.

teddink

Joined: 2006-06-22
Posts: 7

Posted: Sat, 2006-09-30 07:00

Done - thanks for the verification and the suggestion. I may try the direct DB deletion method in the meantime.

Murple

Joined: 2005-12-07
Posts: 14

Posted: Tue, 2006-10-03 21:37

That seems a pretty glaring lack.

eduo

Joined: 2003-09-10
Posts: 107

Posted: Mon, 2007-04-16 08:22

Has anything for this done yet in 2.2? I have looked around and can't find anything.

I see here (http://gallery.menalto.com/node/33894) that a hack existed once for this in Gallery 1, and also there has been lots of requests for some way to bulk delete comments (there was in G1). I may be missing this functionality if it has indeed been added since I last checked.

Eduo
---
www.eduo.info
www.eduo.info/gallery/
www.hamsterspit.com

austris

Joined: 2006-03-16
Posts: 17

Posted: Mon, 2007-04-23 15:22

haven't found anything for 2.2.
though haven't looked too hard since I saved the magic SQLs that deletes them.

1) browse the g2_comment table to find range of g_id's that you want to delete (in my case there are ~15 spam comments a day from various IPs despite the fact that I have captcha module enabled for anonymous comments). Take a not of min/max values of the IDs - ID_MIN, ID_MAX
2) replace the ID_MIN/ID_MAX with actual values and execute the following SQLs:
delete from g2_Comment where g_id>=ID_MIN and g_id<=ID_MAX
delete from g2_Entity where g_id>=ID_MIN and g_id<=ID_MAX
delete from g2_ChildEntity where g_id>=ID_MIN and g_id<=ID_MAX

for good or for bad, but legal commenters are rather inactive in my galleries, so it's usually just a single range that needs to be deleted to get rid of the spam comments.

cheers,
Austris

valiant

Joined: 2003-01-04
Posts: 32509

Posted: Mon, 2007-04-23 17:28

that's a very dangerous and error-prone way to do it. please don't do it this way.
you could delete new items and other stuff this way by accident.

--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage

yottabit

Joined: 2004-02-25
Posts: 36

Posted: Mon, 2007-05-14 20:03

okay, so deleted the Comment rows without deleting the entries from the Entity and ChildEntity tables... Any advice? :o)

austris

Joined: 2006-03-16
Posts: 17

Posted: Mon, 2007-05-14 20:36

that's why valiant asked you to not do it this way ;)

I'm not SQL expert, so it might be not most effective way, but I would fix it as follows:
run the following SQL to get id's of comments you deleted from Comment table and did not delete from Entity and child Entity (this is just informative step so you can get the feeling whether number of id's is ok or not):
select g_id from g2_Entity where g_entityType='GalleryComment' and g_id not in (select g_id from g2_Comment)

delete the id's from both tables. I used following SQL:
delete g2_ChildEntity, g2_Entity from g2_ChildEntity, g2_Entity where g2_ChildEntity.g_id=g2_Entity.g_id and g2_Entity.g_entityType='GalleryComment' and g2_Entity.g_id not in (select g_id from g2_Comment)

austris

Joined: 2006-03-16
Posts: 17

Posted: Wed, 2007-05-16 21:34

ok, I got the same problem in the different instance of gallery, so I came up with something better.
I hope, valiant will admit this as more secure option

to delete by IP:
delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_host='195.225.177.20' or co.g_host='195.225.177.40')

to delete by content:
delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_comment like '%[url=http://%')

to delete by author:
delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_author like '%ialis%' or co.g_author like '%iagra%')

of course, you can combine IP with content and/or author if you wish.

and this will delete it in a single sql, so yottabit won't have the same problem next time

cheers,
Austris

sleze

Joined: 2004-05-08
Posts: 15

Posted: Fri, 2007-10-19 16:38

I went into MyPHP and found all the records of the few hundred comment spams and deleted them. Now when I attempt to view latest comments of certain folders, I get a SQL error:

Error (ERROR_MISSING_OBJECT) : Missing object for 12 
in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 1887 (GalleryCoreApi::error) 
in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 98 (GalleryStorageExtras::_identifyEntities) 
in modules/core/classes/GalleryStorage.class at line 298 (GalleryStorageExtras::loadEntities) 
in modules/core/classes/helpers/GalleryEntityHelper_simple.class at line 71 (GalleryStorage::loadEntities) 
in modules/core/classes/GalleryCoreApi.class at line 2251 (GalleryEntityHelper_simple::loadEntitiesById) 
in modules/comment/ShowAllComments.inc at line 73 (GalleryCoreApi::loadEntitiesById) 
in modules/core/classes/GalleryView.class at line 300 (ShowAllCommentsView::loadTemplate) 
in main.php at line 441 (GalleryView::doLoadTemplate) 
in main.php at line 94
in main.php at line 83

I think for some reason, Gallery is still looking for those comments. You can tell by going to my gallery homepage and clicking "Latest Comments." The first page is fine but when you click next, you will see "not found." Admins see the above detail. I have tried to delete the database cache and I got the following results:

Unable to delete directory: ...gallery/g2data/cache/entity 
Unable to delete directory: ...gallery/g2data/cache/theme 
Unable to delete directory: ...gallery/g2data/cache/module

When I tried to delete the template cache, I get THIS error:

Unable to delete directory: ...gallery/g2data/smarty/templates_c/

Anyone have any ideas as to how to fix it?

sleze

Joined: 2004-05-08
Posts: 15

Posted: Fri, 2007-10-19 17:18

Fixed the delete problem by playing with permissions but I still have the not-found issue with viewing latest comments.

yhager

Joined: 2007-12-09
Posts: 1

Posted: Sun, 2007-12-09 12:54

I had quite a success using CommentBlaster, after failing one of the SQL queries above.

spurrymoses

Joined: 2004-02-22
Posts: 12

Posted: Sun, 2008-06-01 02:39

uhuru53

Joined: 2006-12-02
Posts: 94

Posted: Mon, 2008-08-25 10:35

yhager wrote:

I had quite a success using CommentBlaster, after failing one of the SQL queries above.

Thanks for suggesting this script!
I had more than a thousand spam comments!

nnnortonian

Joined: 2010-01-13
Posts: 1

Posted: Wed, 2010-01-13 18:39

Script works great. Thanks!

teddink Joined: 2006-06-22 Posts: 7	Posted: Fri, 2006-09-29 23:25
	Gallery version = 2.1.2 core 1.1.0.2 PHP version = 4.4.4 cgi Webserver = Apache/1.3.37 (Unix) Database = mysql 4.0.27-standard-log, lock.system=flock Toolkits = ArchiveUpload, Exif, Gd, Getid3, NetPBM, SquareThumb, Thumbnail Acceleration = none, none Operating system = Linux infong 2.4 #1 SMP Thu Jan 13 08:59:31 CET 2005 i686 unknown Default theme = matrix Locale = en_US Browser = Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2) Unfortunately I left the ability for guest to comment on the root album. I eneded up with 350 or so comments on the root album. Is there an easy way to bulk delete all of the comments for a particular album? Needless to say that deleting them one at a time will get very tiresome... Thanks in advance.
	XUser login Username: * Password: * Create new account Request new password
valiant Joined: 2003-01-04 Posts: 32509	Posted: Fri, 2006-09-29 23:31
	there's no interface for that yet. you can file a feature request for it. if you decide to manually delete the comments in the database, don't forget the g2_Entity and g2_ChildEntity tables which hold a row for each row in g2_Comment.

teddink Joined: 2006-06-22 Posts: 7	Posted: Sat, 2006-09-30 07:00
	Done - thanks for the verification and the suggestion. I may try the direct DB deletion method in the meantime.

Murple Joined: 2005-12-07 Posts: 14	Posted: Tue, 2006-10-03 21:37
	That seems a pretty glaring lack.

eduo Joined: 2003-09-10 Posts: 107	Posted: Mon, 2007-04-16 08:22
	Has anything for this done yet in 2.2? I have looked around and can't find anything. I see here (http://gallery.menalto.com/node/33894) that a hack existed once for this in Gallery 1, and also there has been lots of requests for some way to bulk delete comments (there was in G1). I may be missing this functionality if it has indeed been added since I last checked. Eduo --- www.eduo.info www.eduo.info/gallery/ www.hamsterspit.com

austris Joined: 2006-03-16 Posts: 17	Posted: Mon, 2007-04-23 15:22
	haven't found anything for 2.2. though haven't looked too hard since I saved the magic SQLs that deletes them. 1) browse the g2_comment table to find range of g_id's that you want to delete (in my case there are ~15 spam comments a day from various IPs despite the fact that I have captcha module enabled for anonymous comments). Take a not of min/max values of the IDs - ID_MIN, ID_MAX 2) replace the ID_MIN/ID_MAX with actual values and execute the following SQLs: delete from g2_Comment where g_id>=ID_MIN and g_id<=ID_MAX delete from g2_Entity where g_id>=ID_MIN and g_id<=ID_MAX delete from g2_ChildEntity where g_id>=ID_MIN and g_id<=ID_MAX for good or for bad, but legal commenters are rather inactive in my galleries, so it's usually just a single range that needs to be deleted to get rid of the spam comments. cheers, Austris

valiant Joined: 2003-01-04 Posts: 32509	Posted: Mon, 2007-04-23 17:28
	that's a very dangerous and error-prone way to do it. please don't do it this way. you could delete new items and other stuff this way by accident. -------------- Documentation: Support / Troubleshooting \| Installation, Upgrade, Configuration and Usage

yottabit Joined: 2004-02-25 Posts: 36	Posted: Mon, 2007-05-14 20:03
	okay, so deleted the Comment rows without deleting the entries from the Entity and ChildEntity tables... Any advice? :o)

austris Joined: 2006-03-16 Posts: 17	Posted: Mon, 2007-05-14 20:36
	that's why valiant asked you to not do it this way ;) I'm not SQL expert, so it might be not most effective way, but I would fix it as follows: run the following SQL to get id's of comments you deleted from Comment table and did not delete from Entity and child Entity (this is just informative step so you can get the feeling whether number of id's is ok or not): select g_id from g2_Entity where g_entityType='GalleryComment' and g_id not in (select g_id from g2_Comment) delete the id's from both tables. I used following SQL: delete g2_ChildEntity, g2_Entity from g2_ChildEntity, g2_Entity where g2_ChildEntity.g_id=g2_Entity.g_id and g2_Entity.g_entityType='GalleryComment' and g2_Entity.g_id not in (select g_id from g2_Comment)

austris Joined: 2006-03-16 Posts: 17	Posted: Wed, 2007-05-16 21:34
	ok, I got the same problem in the different instance of gallery, so I came up with something better. I hope, valiant will admit this as more secure option to delete by IP: delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_host='195.225.177.20' or co.g_host='195.225.177.40') to delete by content: delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_comment like '%[url=http://%') to delete by author: delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_author like '%ialis%' or co.g_author like '%iagra%') of course, you can combine IP with content and/or author if you wish. and this will delete it in a single sql, so yottabit won't have the same problem next time cheers, Austris

sleze Joined: 2004-05-08 Posts: 15	Posted: Fri, 2007-10-19 16:38
	I went into MyPHP and found all the records of the few hundred comment spams and deleted them. Now when I attempt to view latest comments of certain folders, I get a SQL error: Error (ERROR_MISSING_OBJECT) : Missing object for 12 in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 1887 (GalleryCoreApi::error) in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 98 (GalleryStorageExtras::_identifyEntities) in modules/core/classes/GalleryStorage.class at line 298 (GalleryStorageExtras::loadEntities) in modules/core/classes/helpers/GalleryEntityHelper_simple.class at line 71 (GalleryStorage::loadEntities) in modules/core/classes/GalleryCoreApi.class at line 2251 (GalleryEntityHelper_simple::loadEntitiesById) in modules/comment/ShowAllComments.inc at line 73 (GalleryCoreApi::loadEntitiesById) in modules/core/classes/GalleryView.class at line 300 (ShowAllCommentsView::loadTemplate) in main.php at line 441 (GalleryView::doLoadTemplate) in main.php at line 94 in main.php at line 83 I think for some reason, Gallery is still looking for those comments. You can tell by going to my gallery homepage and clicking "Latest Comments." The first page is fine but when you click next, you will see "not found." Admins see the above detail. I have tried to delete the database cache and I got the following results: Unable to delete directory: ...gallery/g2data/cache/entity Unable to delete directory: ...gallery/g2data/cache/theme Unable to delete directory: ...gallery/g2data/cache/module When I tried to delete the template cache, I get THIS error: Unable to delete directory: ...gallery/g2data/smarty/templates_c/ Anyone have any ideas as to how to fix it?

sleze Joined: 2004-05-08 Posts: 15	Posted: Fri, 2007-10-19 17:18
	Fixed the delete problem by playing with permissions but I still have the not-found issue with viewing latest comments.

yhager Joined: 2007-12-09 Posts: 1	Posted: Sun, 2007-12-09 12:54
	I had quite a success using CommentBlaster, after failing one of the SQL queries above.

spurrymoses Joined: 2004-02-22 Posts: 12	Posted: Sun, 2008-06-01 02:39


uhuru53 Joined: 2006-12-02 Posts: 94	Posted: Mon, 2008-08-25 10:35
	yhager wrote: I had quite a success using CommentBlaster, after failing one of the SQL queries above. Thanks for suggesting this script! I had more than a thousand spam comments!

nnnortonian Joined: 2010-01-13 Posts: 1	Posted: Wed, 2010-01-13 18:39
	Script works great. Thanks!

How to bulk delete comments?

XUser login