1

When trying to move an order from archive back to the sales I get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''2014-01-09 10:24:01') SELECT `enterprise_sales_order_grid_archive`.`entity_id`,' at line 1 [queryString=INSERT INTO `sales_flat_order_grid` (`entity_id`, `status`, `store_id`, `store_name`, `customer_id`, `base_grand_total`, `base_total_paid`, `grand_total`, `total_paid`, `increment_id`, `base_currency_code`, `order_currency_code`, `shipping_name`, `billing_name`, `created_at`,  :_mage_bind_var_1) SELECT `enterprise_sales_order_grid_archive`.`entity_id`, `enterprise_sales_order_grid_archive`.`status`, `enterprise_sales_order_grid_archive`.`store_id`, `enterprise_sales_order_grid_archive`.`store_name`, `enterprise_sales_order_grid_archive`.`customer_id`, `enterprise_sales_order_grid_archive`.`base_grand_total`, `enterprise_sales_order_grid_archive`.`base_total_paid`, `enterprise_sales_order_grid_archive`.`grand_total`, `enterprise_sales_order_grid_archive`.`total_paid`, `enterprise_sales_order_grid_archive`.`increment_id`, `enterprise_sales_order_grid_archive`.`base_currency_code`, `enterprise_sales_order_grid_archive`.`order_currency_code`, `enterprise_sales_order_grid_archive`.`shipping_name`, `enterprise_sales_order_grid_archive`.`billing_name`, `enterprise_sales_order_grid_archive`.`created_at`,  :_mage_bind_var_2 AS `updated_at` FROM `enterprise_sales_order_grid_archive` WHERE (`entity_id` IN('23037')) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `status` = VALUES(`status`), `store_id` = VALUES(`store_id`), `store_name` = VALUES(`store_name`), `customer_id` = VALUES(`customer_id`), `base_grand_total` = VALUES(`base_grand_total`), `base_total_paid` = VALUES(`base_total_paid`), `grand_total` = VALUES(`grand_total`), `total_paid` = VALUES(`total_paid`), `increment_id` = VALUES(`increment_id`), `base_currency_code` = VALUES(`base_currency_code`), `order_currency_code` = VALUES(`order_currency_code`), `shipping_name` = VALUES(`shipping_name`), `billing_name` = VALUES(`billing_name`), `created_at` = VALUES(`created_at`),  :_mage_bind_var_3 = VALUES( :_mage_bind_var_4), params=2014-01-09 10:24:01, 2014-01-09 10:24:01, 2014-01-09 10:24:01, 2014-01-09 10:24:01]

#0 magento/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 magento/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 magento/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `sa...', Array)
#4 magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sa...', Array)
#5 magento/app/code/core/Enterprise/SalesArchive/Model/Resource/Archive.php(301): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `sa...')
#6 magento/app/code/core/Enterprise/SalesArchive/Model/Archive.php(216): Enterprise_SalesArchive_Model_Resource_Archive->removeFromArchive(Object(Enterprise_SalesArchive_Model_Archive), 'order', 'entity_id', Array)
#7 magento/app/code/core/Enterprise/SalesArchive/controllers/Adminhtml/Sales/ArchiveController.php(199): Enterprise_SalesArchive_Model_Archive->removeOrdersFromArchiveById(Array)
#8 magento/app/code/core/Mage/Core/Controller/Varien/Action.php(420): Enterprise_SalesArchive_Adminhtml_Sales_ArchiveController->massRemoveAction()
#9 magento/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('massRemove')
#10 magento/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#11 magento/app/code/core/Mage/Core/Model/App.php(347): Mage_Core_Controller_Varien_Front->dispatch()
#12 magento/app/Mage.php(640): Mage_Core_Model_App->run(Array)
#13 magento/index.php(80): Mage::run('', 'store')
#14 {main}

I found out that this happens because of the following code (marked with >>> and <<< on line 288 to line 292 in app/code/core/Enterprise/SalesArchive/Model/Resource/Archive.php):

$updatedAtIndex = array_search('updated_at', $insertFields);
if ($updatedAtIndex !== false) {
    unset($insertFields[$updatedAtIndex]);
    >>> $insertFields['updated_at'] = new Zend_Db_Expr("'".$this->formatDate(true)."'"); <<<
}

All other fields are directly referenced and without a value in the insertFields-array, so the correct code would be:

//$insertFields['updated_at'] = new Zend_Db_Expr("'".$this->formatDate(true)."'");
$insertFields[] = 'updated_at';

Now, is this a bug or can someone please explain me why all other fields are directly referenced and for update_at there is a special case?

Improve this question

2 Answers 2

Reset to default
1

The problem was definitely a bug in magento 1.11.0.2 After using the correct code of magento 1.12.0.2 everything worked fine.

Old code:

Magento 1.11.0.2
================
    $updatedAtIndex = array_search('updated_at', $insertFields);
    if ($updatedAtIndex !== false) {
        unset($insertFields[$updatedAtIndex]);
        $insertFields['updated_at'] = new Zend_Db_Expr($adapter->quoteInto('?', $this->formatDate(true)));
    }

    $select = $adapter->select()
        ->from($sourceTable, $insertFields);

    if (!empty($conditionField)) {
        $select->where($adapter->quoteIdentifier($conditionField) . ' IN(?)', $conditionValue);
    }

    $adapter->query($select->insertFromSelect($targetTable, $insertFields, true));

New code:

Magento 1.12.0.2
================
    $selectFields = $insertFields;
    $updatedAtIndex = array_search('updated_at', $selectFields);
    if ($updatedAtIndex !== false) {
        unset($selectFields[$updatedAtIndex]);
        $selectFields['updated_at'] = new Zend_Db_Expr($adapter->quoteInto('?', $this->formatDate(true)));
    }

    $select = $adapter->select()
        ->from($sourceTable, $selectFields);

    if (!empty($conditionField)) {
        $select->where($adapter->quoteIdentifier($conditionField) . ' IN(?)', $conditionValue);
    }

    $adapter->query($select->insertFromSelect($targetTable, $insertFields, true));

As seen the variable was copied and it was worked with the copy except for the insertFromSelect.

Improve this answer
0

The updated_at field keeps the last time of any modification done to a record.
You are calling removeFromArchive that basically does some modification to the order (removing it from the archive). This action needs to modify the updated_at field or the field loses its purpose.
So updated_at gets the value of the current time.

Improve this answer
1
  • Yeah it does that if I use the corrected code. If I use the given code from Magento I got the error because the date is not a field (you can see that in the resulting sql before the SELECT => , "created_at, :_mage_bind_var_1)")
    – Chuvisco
    Jan 9, 2014 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.