deleting a category

sunnysha

when i delete a category and uncheck the box to move discussions, i get this message


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c from GDN_Comment c join GDN_Discussion d on c.DiscussionID = d.DiscussionID wh' at line 1|Gdn_Database|Query|delete GDN_Comment c from GDN_Comment c join GDN_Discussion d on c.DiscussionID = d.DiscussionID where d.CategoryID = :dCategoryID


am i doing something wrong or is it a bug?
i did it on localhost

luc

Which vanilla 2 version?

sunnysha

VANILLA 2.0.18BETA

dareiff

Same here — still trying to work out if it's a me problem or a Garden problem.

alsarg72

I have this problem with 2.0.18.4 with MySQL 5.5.25.

alsarg72

The problem with the statement is covered in "http://dev.mysql.com/doc/refman/5.5/en/delete.html"...

...where it says "Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement." and the examples immediately below it.

This is what vanilla is doing.

mysql> delete GDN_Comment c from GDN_Comment c join GDN_Discussion d on c.DiscussionID = d.DiscussionID where d.CategoryID =
"Test";

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'c from GDN_Comment c join GDN_Discussion d on c.DiscussionID = d.DiscussionID wh' at line 1

This is what it needs to do. The difference is that the table name is not mentioned immediately after delete, only the alias.

mysql> delete c from GDN_Comment c join GDN_Discussion d on c.DiscussionID = d.DiscussionID where d.CategoryID = "Test";

Query OK, 0 rows affected (0.00 sec)

422

Looks good, one for the senior devs and the wiki

alsarg72

In class.mysqldriver.php, GetDelete, at line 238, I've put in this workaround.

//return "delete ".$TableName." from ".$TableName.$Joins.$Conditions;
$AliasPos = strrpos($TableName, " ");
if ($AliasPos !== false)
{
   $AliasName = substr($TableName, $AliasPos + 1);
   return "delete ".$AliasName." from ".$TableName.$Joins.$Conditions;
}
else
{
   return "delete ".$TableName." from ".$TableName.$Joins.$Conditions;
}

Khrista

Could you specify it a litle bit more where to copy this code?

I do not have GetDelete, at line 238 in class.mysqldriver.php.
I have the latest version downloaded two days ago.

Thanks for that!