Posted on

Column not found: 1054 Unknown column ‘sales_bestsellers_aggregated_yearly.product_type_id’ in ‘field list

Column not found: 1054 Unknown column ‘sales_bestsellers_aggregated_yearly.product_type_id’ in ‘field list

You might receive this error message in a fresh Magento 1.9.3 installation with sample data when trying to log into the admin panel. I got it after installing https://github.com/andreaskoch/dockerized-magento and found a few reports online, but no solution.

I don’t know the root cause yet, but to be able to log in again, you can set a different admin startup page:

n98-magerun config:set admin/startup/page system/config
n98-magerun cache:clear config

If you don’t have magerun installed (you should!), change the value in the core_config_data table instead and delete the cache.

Now delete the adminhtml cookie in your browser and you can log in again. Just don’t try to open the dashboard.

Full Stack Trace

a:5:{i:0;s:698:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_bestsellers_aggregated_yearly.product_type_id' in 'field list', query was: SELECT COUNT(*) FROM (SELECT MAX(DATE_FORMAT(period, '%Y-%m-%d')) AS `period`, SUM(qty_ordered) AS `qty_ordered`, `sales_bestsellers_aggregated_yearly`.`product_id`, MAX(product_name) AS `product_name`, MAX(product_price) AS `product_price`, `sales_bestsellers_aggregated_yearly`.`product_type_id` FROM `sales_bestsellers_aggregated_yearly` WHERE (EXISTS (SELECT 1 FROM `catalog_product_entity` AS `existed_products` WHERE (sales_bestsellers_aggregated_yearly.product_id = existed_products.entity_id))) AND (store_id IN(0)) GROUP BY `product_id` LIMIT 5) AS `t`";i:1;s:4693:"#0 /var/www/html/web/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/html/web/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/html/web/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/html/web/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT COUNT(*)...', Array)
#4 /var/www/html/web/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT COUNT(*)...', Array)
#5 /var/www/html/web/lib/Zend/Db/Adapter/Abstract.php(828): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /var/www/html/web/lib/Varien/Data/Collection/Db.php(225): Zend_Db_Adapter_Abstract->fetchOne(Object(Varien_Db_Select), Array)
#7 /var/www/html/web/lib/Varien/Data/Collection.php(225): Varien_Data_Collection_Db->getSize()
#8 /var/www/html/web/lib/Varien/Data/Collection.php(211): Varien_Data_Collection->getLastPageNumber()
#9 /var/www/html/web/lib/Varien/Data/Collection/Db.php(522): Varien_Data_Collection->getCurPage()
#10 /var/www/html/web/lib/Varien/Data/Collection/Db.php(569): Varien_Data_Collection_Db->_renderLimit()
#11 /var/www/html/web/app/code/core/Mage/Reports/Model/Resource/Report/Collection/Abstract.php(285): Varien_Data_Collection_Db->load(false, false)
#12 /var/www/html/web/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(550): Mage_Reports_Model_Resource_Report_Collection_Abstract->load()
#13 /var/www/html/web/app/code/core/Mage/Adminhtml/Block/Dashboard/Tab/Products/Ordered.php(66): Mage_Adminhtml_Block_Widget_Grid->_prepareCollection()
#14 /var/www/html/web/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(643): Mage_Adminhtml_Block_Dashboard_Tab_Products_Ordered->_prepareCollection()
#15 /var/www/html/web/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(649): Mage_Adminhtml_Block_Widget_Grid->_prepareGrid()
#16 /var/www/html/web/app/code/core/Mage/Core/Block/Abstract.php(922): Mage_Adminhtml_Block_Widget_Grid->_beforeToHtml()
#17 /var/www/html/web/app/code/core/Mage/Adminhtml/Block/Dashboard/Grids.php(64): Mage_Core_Block_Abstract->toHtml()
#18 /var/www/html/web/app/code/core/Mage/Core/Block/Abstract.php(297): Mage_Adminhtml_Block_Dashboard_Grids->_prepareLayout()
#19 /var/www/html/web/app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#20 /var/www/html/web/app/code/core/Mage/Adminhtml/Block/Dashboard.php(75): Mage_Core_Model_Layout->createBlock('adminhtml/dashb...')
#21 /var/www/html/web/app/code/core/Mage/Core/Block/Abstract.php(297): Mage_Adminhtml_Block_Dashboard->_prepareLayout()
#22 /var/www/html/web/app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#23 /var/www/html/web/app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('adminhtml/dashb...', 'dashboard')
#24 /var/www/html/web/app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('adminhtml/dashb...', 'dashboard')
#25 /var/www/html/web/app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#26 /var/www/html/web/app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#27 /var/www/html/web/app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#28 /var/www/html/web/app/code/core/Mage/Core/Controller/Varien/Action.php(269): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#29 /var/www/html/web/app/code/core/Mage/Adminhtml/Controller/Action.php(275): Mage_Core_Controller_Varien_Action->loadLayout(NULL, true, true)
#30 /var/www/html/web/app/code/core/Mage/Adminhtml/controllers/DashboardController.php(40): Mage_Adminhtml_Controller_Action->loadLayout()
#31 /var/www/html/web/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_DashboardController->indexAction()
#32 /var/www/html/web/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('index')
#33 /var/www/html/web/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#34 /var/www/html/web/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#35 /var/www/html/web/app/Mage.php(692): Mage_Core_Model_App->run(Array)
#36 /var/www/html/web/index.php(83): Mage::run('', 'store')
#37 {main}";s:3:"url";s:70:"/index.php/admin/dashboard/index/key/8565326cb6d7ae31c17ba1e04f2631bc/";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:5:"admin";}
Posted on

Magento 2 Integration Tests: @magentoConfigFixture

I did not find a good documentation on how to use the @magentoConfigFixture annotation for configuration fixtures in Magento 2 integration tests, so here is my summary after inspecting the core code (Magento 2.1.0, Magento\TestFramework\Annotation\ConfigFixture)

How to use @magentoConfigFixture

Set default value 42 for configuration path x/y/z:

/**
 * @magentoConfigFixture default/x/y/z 42
 */

Set store specific value 42 for configuration path x/y/z in store with code store1

/**
 * @magentoConfigFixture store1_store x/y/z 42
 */

Set store specific value 42 for configuration path x/y/z in current store (i.e. default store)

/**
 * @magentoConfigFixture current_store x/y/z 42
 */

These are all possible formats. The first parameter must end with _store or be ommitted. And if it is omitted, the path must start with default/, otherwise it is ignored.

Implications

  • You cannot set configuration values on website level
  • Do not use “current” as a real store code, otherwise you cannot use config fixtures for that store
Posted on

5 Minute Tips: Magento Performance Tweaks

My “Week On StackExchange” Series takes a break at the moment because I don’t have not too much blog-worthy.

Instead, I’m starting something new again: Tips on a specific topic which you can read in 5 minutes maximum during coffee break. Mostly not by me, just found by me 🙂

I don’t try to make it a regular thing but I have a loose collection of useful stuff so why not use the blog to bring order into it and hopefully it’s also useful for others.

Let’s start with Magento Performance Tweaks, all with little effort and can be used without hesitation:

Continue reading “5 Minute Tips: Magento Performance Tweaks”

Posted on

Efficiently Increase/Decrease Magento Attributes

Magento.SE Screenshot

This question arose on Magento StackExchange:

I need to decrement a value with an atomic database operation, is it possible using Magento models?

It is in fact possible, with a lesser known technique using Zend_Db_Expr. I’ll share it here as well:

$object->setNumber(new Zend_Db_Expr('number-1'));

For reference:

The method Mage_Core_Model_Resource_Abstract::_prepareDataForSave() contains the following code:

if ($object->hasData($field)) {
    $fieldValue = $object->getData($field);
    if ($fieldValue instanceof Zend_Db_Expr) {
        $data[$field] = $fieldValue;
    } else {
        ... [normal value processing follows]

EAV Models:

Note that you only can reference the attribute by its name (“number” in the example) if it’s a real column of the main table, not an EAV attribute.

Although the abovementioned method is only used by models with flat tables, Zend_Db_Expr can be used for EAV attributes as well, the method that handles it is Varien_Db_Adapter_Pdo_Mysql::prepareColumnValue().

BUT you always have use the column name “value“:

$product->setNumber(new Zend_Db_Expr('value-1'));

You don’t need to specify a table alias because during save each attribute gets processed with its own query, so “value” is not ambiguous.

Posted on

Magento: Direct Link To Tab in Adminhtml Tab Widgets

For an extension I was recently working on, I wondered if there was a built-in way to link directly to a tab on a backend page. My research didn’t result in anything useful (read: my Google foo had failed me), so I dug into the core code to see where to start. I’ll share what I found out.

The Problem

In particular, I wanted to link to the “Manage Label / Options” tab on the “Edit Product Attribute Page”:

Screenshot

The Solution

Actually it’s possible with a URL parameter ?active_tab=$id.

How To Find The Right Tab ID

Find the responsible tab container class. This is a child class of Mage_Adminhtml_Block_Widget_Tabs, in my case, Mage_Adminhtml_Block_Catalog_Product_Attribute_Edit_Tabs.

You’ll find calls to $this->addTab(), usually in the methods _beforeToHtml(), or _construct(). The first parameter of addTab() is the tab id:

    $this->addTab('labels', array(
        'label'     => Mage::helper('catalog')->__('Manage Label / Options'),
        'title'     => Mage::helper('catalog')->__('Manage Label / Options'),
        'content'   => $this->getLayout()->createBlock('adminhtml/catalog_product_attribute_edit_tab_options')->toHtml(),
    ));

So, the URL is /admin/catalog_product_attribute/edit/attribute_id/123/?active_tab=labels, generated with this code (within an adminhtml block):

    $this->getUrl('adminhtml/catalog_product_attribute/edit',
        array('attribute_id' => 123, '_query' => array('active_tab' => 'labels'));

How it works

Let’s have a look at the responsible code:
Continue reading “Magento: Direct Link To Tab in Adminhtml Tab Widgets”

Posted on

Magento: How To use the Secure Base URL for Custom Controllers

This question came up on Magento.SE and more people should know, how dead simple it actually is.

If you look at core/Mage/Checkout/etc/config.xml you can see how Magento defines for the checkout to use the secure base URL, i.e. HTTPS:

<frontend>
    <secure_url>
        <checkout_onepage>/checkout/onepage</checkout_onepage>
        <checkout_multishipping>/checkout/multishipping</checkout_multishipping>
    </secure_url>
</frontend>

That’s all. You can configure your own controllers to use the secure URL in the same way and now Mage::getUrl() returns the secure URL for the configured routes and any unsecure request will be redirected.

Posted on

Allow SVG Images in Magento Uploader

If you want to change the allowed image extensions for product images in Magento you will find out that they are hard coded in Mage/Catalog/Model/Product/Attribute/Backend/Media.php and Mage/Adminhtml/controllers/Catalog/Product/GalleryController.php

However, I found an observer that allows you to change them along with other configuration of the uploader. This post explains how to create an extension to allow uploading of SVG files. Foundations of Magento extension develoment are presumed.

Continue reading “Allow SVG Images in Magento Uploader”

Posted on

PHP Fatal Error Handler

Code snipped inspired by this article:

fatalerrorhandler.php

<?php
// report all but fatal errors 
error_reporting((E_ALL | E_STRICT) ^ (E_ERROR | E_CORE_ERROR | E_COMPILE_ERROR));

// fatal error handler as shutdown function
register_shutdown_function('fatalErrorHandler'); 

function fatalErrorHandler() {
	$error = error_get_last();
	if ($error['type'] & (E_ERROR | E_CORE_ERROR | E_COMPILE_ERROR)) {
		echo '<h1>Fatal Error, shutting down...</h1>';
		echo '<pre>' . var_export($error,true) . '</pre>';
	} else {
		echo 'Regular Shutdown, no fatal errors.';
	}
}

test1.php

<?php
require 'fatalerrorhandler.php';

// Fatal Error (E_ERROR)
unknown_function_call();

test2.php

<?php
require 'fatalerrorhandler.php';

// E_USER_ERROR
trigger_error('...', E_USER_ERROR);

test3.php

<?php
require 'fatalerrorhandler.php';

// Notice (E_NOTICE)
echo $unknown_var;