Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

deleting a category

edited August 2011 in Vanilla 2.0 - 2.8
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

Answers

  • lucluc ✭✭
    Which vanilla 2 version?
  • VANILLA 2.0.18BETA
  • Same here — still trying to work out if it's a me problem or a Garden problem.
  • alsarg72alsarg72 New
    edited June 2012

    I have this problem with 2.0.18.4 with MySQL 5.5.25.

  • 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)

  • 422422 Developer MVP

    Looks good, one for the senior devs and the wiki

    There was an error rendering this rich post.

  • alsarg72alsarg72 New
    edited June 2012

    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;
    }
    
  • 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!

Sign In or Register to comment.