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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Archive /

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo

Failure in E-Commerce Module


Go to End


30 Posts   69695 Views

Avatar
RichardVowles

Community Member, 5 Posts

4 December 2007 at 1:15pm

I am running the current version of both Silverstripe (2.2) and the E-Commerce module (0.5.1)? And using the PayPalPayment although I don't think this relates to that.

There appears to be an error in the way the SQL is generated, notice the first SELECT is trying to request the data from Product_Live (which does exist) but it isn't listed in the FROM anywhere (not joined to).

FATAL ERROR: DATABASE ERROR: Couldn't run query: SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort | Unknown table 'Product_Live'
At line 397 in C:\lighttpd\htdocs\sapphire\core\model\Database.php

user_error(DATABASE ERROR: Couldn't run query: SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort | Unknown table 'Product_Live',256)
line 397 of Database.php

Database->databaseError(Couldn't run query: SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort | Unknown table 'Product_Live',256)
line 106 of MySQLDatabase.php

MySQLDatabase->query(SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort,256)
line 79 of DB.php

....

SQLQuery->execute()
line 642 of Versioned.php

Versioned::get_version(Product,7,1)
line 1005 of Order.php

Order_Item->__construct(Array)
line 1472 of DataObject.php

DataObject->buildDataObjectSet(MySQLQuery,DataObjectSet,SQLQuery,Order_Item)
line 1452 of DataObject.php

DataObject->instance_get(OrderID = 11,,,,DataObjectSet,)
line 1432 of DataObject.php

DataObject::get(Order_Item,OrderID = 11)
line 356 of Order.php

Order->itemsFromDatabase()
line 373 of Order.php

Order->Items()

call_user_func_array(Array,Array)
line 524 of ViewableData.php

ViewableData->cachedCall(Items,,)
line 565 of ViewableData.php

ViewableData->hasValue(Items)
line 54 of .cacheC..lighttpd.htdocs.ecommerce.templates.Layout.CheckoutPage.ss

include(C:\Documents and Settings\ddeveloper\Local Settings\Temp\silverstripe-cacheC--lighttpd-htdocs\.cacheC..lighttpd.htdocs.ecommerce.templates.Layout.CheckoutPage.ss)
line 190 of SSViewer.php

SSViewer->process(CheckoutPage_Controller)
line 182 of SSViewer.php

SSViewer->process(CheckoutPage_Controller)
line 247 of Controller.php

Controller->defaultAction(11,Array)
line 218 of Controller.php

Controller->run(Array)
line 15 of ModelAsController.php

ModelAsController->run(Array)
line 76 of Director.php

Director::direct(/checkout/11)
line 104 of main.php

Avatar
RichardVowles

Community Member, 5 Posts

4 December 2007 at 8:39pm

Okay, this I have finally tracked down to Versioned.php, the following code being seriously broken because of earlier replacements:

/**
* Build a SQL query to get data from the _version table.
* This function is similar in style to {@link DataObject::buildSQL}
*/
function buildVersionSQL($filter = "", $sort = "") {
$query = $this->owner->extendedSQL($filter,$sort);
foreach($query->from as $table => $join) {
if($join[0] == '`') $baseTable = str_replace('`','',$join);
else $query->from[$table] = "LEFT JOIN `$table` ON `$table`.RecordID = `{$baseTable}_versions`.RecordID AND `$table`.Version = `{$baseTable}_versions`.Version";
$query->renameTable($table, $table . '_versions');
}
$query->select[] = "`{$baseTable}_versions`.AuthorID, `{$baseTable}_versions`.Version, `{$baseTable}_versions`.RecordID AS ID";
return $query;
}

1) The original code builds the query using SiteTree and Product as the table names.
2) The _Live code then goes and finds the array (using Product) and replaces Product/SiteTree with Product_Live and SiteTree_Live.
3) The code above then does another check, and sees that SiteTree uses a simple FROM element (where the name of the table starts with a quote) and assumes the name of the table is still SiteTree, replaces the FROM with SiteTree_Live_Versions!
4) The code above sees that the Product $table in the from hash uses a join, so it replaces it with a Product_Versions, but never changes the select statement.

So yeah - not sure how this code made it into the wild? :-)

Richard
(using Delphi for PHP & the PHP debugger, and about 4 hours)

Avatar
RichardVowles

Community Member, 5 Posts

4 December 2007 at 9:27pm

And I'm gonna answer my own implicit question (how do you fix it). My proposal is this, in SQLQuery.php:

function replaceText($old, $new) {
if($this->select) foreach($this->select as $i => $item)
$this->select[$i] = str_replace($old, $new, $item);

if($this->from) {
foreach($this->from as $i => $item) {
if ( "`$i`" == $old )
{ // if this is the FROM related to the table we are replacing, then remove it from the array & replace with the new one
unset( $this->from[$i] );
$this->from[$new] = str_replace($old, $new, $item);
}
else
$this->from[$i] = str_replace($old, $new, $item);
}
}

But alas, my PHP is far too poor to actually have this work. But the intent is to make sure the From $table name in the associative array *always* reflect sthe actual name of the table in the query. There may be implications here I don't understand.

Avatar
RichardVowles

Community Member, 5 Posts

4 December 2007 at 10:36pm

No, that won't work either given the previous code (posting 2 expects to find the base table and add _versions to it given the name of the table in the associative array). Somehow the original table name & the changed table name need to both be stored...

Avatar
Sam

Administrator, 690 Posts

5 December 2007 at 10:55am

Thanks for work investigating this, Richard. The crux of the issue is that, if buildVersionSQL is being used, Versioned's rewriting of the table name (from SiteTree to SiteTree_Live) needs to be disabled.

The simplest solution is to temporarily clear Versioned::$reading_stage, before $this->owner->buildSQL() is called. That way, the '_Live' suffix won't get added.

See Versioned::writeToStage() for an example of how this is done.

Avatar
quicked

Community Member, 32 Posts

15 January 2008 at 8:28am

so that's it? No patches or fixes? So to use the ecommerce module you have to temporarily clear Versioned::$reading_stage, before $this->owner->buildSQL() is called?

Avatar
quicked

Community Member, 32 Posts

15 January 2008 at 8:31am

Where is Versioned::writeToStage() found?

Avatar
Sigurd

Forum Moderator, 628 Posts

16 January 2008 at 10:52am

Just to make it clear, the above error occurs when you are visiting the "checkout" page, where you wish to enter your name, payment info, etc.

Go to Top