Jump to:

10848 Posts in 2516 Topics by 1792 members

All other Modules

SilverStripe Forums » All other Modules » Sapphire SQL Syntax error when installing Subsites Module

Discuss all other Modules here.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 1457 Views
  • zalzadore
    Avatar
    Community Member
    20 Posts

    Sapphire SQL Syntax error when installing Subsites Module Link to this post

    Hi,
    With both the tar'd 0.1 and trunk versions of the Subsites module on Silverstripe 2.3.6 I get an SQL syntax error when I try to rebuild the database. The following is one of the errors from db/build?flush=1 (and any subsequent access to the silverstripe site):

    [User Error] Couldn't run query: SELECT `SiteTree`.*, `GhostPage`.*, `ErrorPage`.*, `RedirectorPage`.*, `VirtualPage`.*, `SubsitesVirtualPage`.*, `SiteTree`.ID, if(`SiteTree`.ClassName,`SiteTree`.ClassName,'SiteTree') AS RecordClassName FROM `SiteTree` LEFT JOIN `GhostPage` ON `GhostPage`.ID = `SiteTree`.ID LEFT JOIN `ErrorPage` ON `ErrorPage`.ID = `SiteTree`.ID LEFT JOIN `RedirectorPage` ON `RedirectorPage`.ID = `SiteTree`.ID LEFT JOIN `VirtualPage` ON `VirtualPage`.ID = `SiteTree`.ID LEFT JOIN `SubsitesVirtualPage` ON `SubsitesVirtualPage`.ID = `SiteTree`.ID WHERE (HomepageForDomain REGEXP '(, ^) *localhost *(,|$)') AND ("SiteTree"."SubsiteID" IN (0)) ORDER BY Sort LIMIT 1 | 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 '."SubsiteID" IN (0)) ORDER BY Sort LIMIT 1' at line 1
    GET /bean/

    Line 401 in /home/adam/public_html/bean/sapphire/core/model/MySQLDatabase.php

    If I execute the query manually and remove the quotes from around "SiteTree"."SubsiteID" (i.e. make it simply SiteTree.SubsiteID) it executes without an error. Unfortunately I'm not familiar enough with Sapphire to take this any further.

    Am I missing something obvious?

  • zalzadore
    Avatar
    Community Member
    20 Posts

    Re: Sapphire SQL Syntax error when installing Subsites Module Link to this post

    In having a squiz at the Subsites PHP code I found double quotes around column and table names causing the problem:

    SiteConfigSubsites.php: $query->where[] = "\"$tableName\".\"SubsiteID\" IN ($subsiteID)";
    SiteTreeSubsites.php: $query->where[] = "\"$tableName\".\"SubsiteID\" IN ($subsiteID)";
    SiteTreeSubsites.php: $candidatePage = DataObject::get_one("SiteTree", "\"URLSegment\" = '" . urldecode( $rest). "' AND \"SubsiteID\" = " . $subsiteID, false);

    Furthermore the code in the current Subsites module is liberally sprinkled with double quotes and, at least in my limited understanding of SQL syntax I think it's pretty broken (at least on MySQL 5.1 on Linux):

    GroupSubsites.php: DB::query('INSERT INTO "Group_Subsites" ("GroupID", "SubsiteID") SELECT "ID", "SubsiteID" FROM "Group" WHERE "SubsiteID" > 0');

    Wouldn't the above code just insert a number of rows with the strings 'ID' and 'SubsiteID' into the table Group_Subsites? (assuming a table with the name 'Group' was allowed)

    Am I missing something?

  • spronkey
    Avatar
    Community Member
    1 Post

    Re: Sapphire SQL Syntax error when installing Subsites Module Link to this post

    Well, I'm getting the same errors as you, and to my eyes it looks pretty dodgy.

    MySQL supports double quotes for object identifiers ONLY when ANSI_QUOTES mode is on.

    I'm also not familiar with the internals, but surely this should be using some kind of configured constant for the correct database type...?

    1457 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.