Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

All other Modules

Discuss all other Modules here.

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

Sapphire SQL Syntax error when installing Subsites Module


3 Posts   1563 Views


7 March 2010 at 4:10am Community Member, 20 Posts

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?


7 March 2010 at 5:06am (Last edited: 7 March 2010 5:10am), Community Member, 20 Posts

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?


11 March 2010 at 12:36pm Community Member, 1 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...?