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.

Archive /

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

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

Failure in E-Commerce Module


Go to End
Reply


30 Posts   65941 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, 685 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