Uncaught exception when deleting all spam at once

[rickc]

Environment: Using a Sqlite database, r1305, Apache 2.2.4, PHP 5.2.3, Sqlite 3.3.17.

What I did: I selected Manage->Spam. On the spam screen I tried to delete all spam by checking the Delete 'em all checkbox, then clicking the Moderate button. I expected all spam to be deleted.

What I expected: All spam in the database to be deleted.

What I got: Uncaught Exception: Exception: PDO::prepare() [function.PDO-prepare]: SQLSTATE[HY000]: General error: 1 near "comments": syntax error

Note: Clicking the text on the next line of the screen to Mark All For Deletion, then clicking the Moderate button works properly to delete the spam shown on the current screen.

[skippy]

I'm not sure I understand the comment in your 'Note:' section. Can you elaborate, please?

[rickc]

Replying to skippy:

I'm not sure I understand the comment in your 'Note:' section. Can you elaborate, please?

On the Manage Spam page, underneath the line that has the Moderate button and the Delete 'em all check box, there is a series of links to mark all comments on the page for Approval/Deletion/Spam/Unapproval. If I click on the Deletion link, all the comments on the page have their Delete radio button selected. If I then click the Moderate button, all those comments are deleted, but only those comments. The page refreshes, and the next series of spam comments is presented. Repeating the process for each page of spam, all the spam can be deleted from a database, but only page by page, rather than all at once at they would be if the Delete 'em all checkbox worked.

[rickc]

After posting the above reply, I looked in adminhandler.php . On line 475, which is in the post_moderate() function, there is a query, OPTIMIZE TABLE, I presume to clean up the table after the mass delete of spam. OPTIMIZE TABLE isn't called when deleting spam using the Mark all for Deletion link.

This is probably the problem since Sqlite doesn't have that command. The nearest equivalent is the VACUUM command, which compacts and defragments the entire database file. A translation in Sqlite's _tsql() function would probably fix it. I'm sorry I can't offer a patch. I know little about PHP and less about regular expressions.

[rickc]

Sorry. I meant sql_t(), not _tsql().

[skippy]

Thanks for the explanation. I added the OPTIMIZE call without realizing that SQLite didn't support it. I'll remove that from adminhandler.php.

That will resolve this issue.

[rickc]

That would resolve the problem with the OPTIMIZE call, though I would prefer a replace ment in sql_t() for it.

Over the weekend I explored the issue some more. When a mass delete of spam is requested, Comments::delete_by_status() is the function called to do the work. There's another bit of SQL there that works for MySQL but not SQLite can't handle. As best I can see, SQLite can only delete from one table at a time. The attached file has a replacement for Comments::delete_by_status() that should work for both database engines.

[rickc]

Patch for delete_by_status()

[rickc]

Here's a more civilized patch. Please ignore the text file.

[skippy]

alternate delete_by_status() implementation

[skippy]

118.diff is an alternate implementation of delete_by_status() which better follows our SQL coding conventions, and should support multiple DB engines.

[miklb]

with 30 spams in my test install (don't ask), using the 118 patch only one spam was deleted at a time when doing delete them all. Reverting, I was able to delete the other 28.

[skippy]

applied rickc's second patch in r1356. Thanks!