10979 Posts in 2722 Topics by 1815 members
|Go to End|
7 March 2010 at 4:10am
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
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
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
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...?
|Go to Top|