5

This is the error, can anyone help me with a solution to this?

There has been an error processing your request

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'f4p_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`, `f4p_sales_bestsellers_aggregated_yearly`.`product_id`, MAX(product_name) AS `product_name`, MAX(product_price) AS `product_price`, `f4p_sales_bestsellers_aggregated_yearly`.`product_type_id` FROM `f4p_sales_bestsellers_aggregated_yearly` WHERE (EXISTS (SELECT 1 FROM `f4p_catalog_product_entity` AS `existed_products` WHERE (f4p_sales_bestsellers_aggregated_yearly.product_id = existed_products.entity_id))) AND (store_id IN(0)) GROUP BY `product_id` LIMIT 5) AS `t`
6

Check this table f4p_sales_bestsellers_aggregated_yearly have 'product_type_id' columns in it.

If 'product_type_id' column is in table then re-index and refresh cache.

If 'product_type_id' column is not in table then you need to create (upload backup table) table again.

2

Database should look like this. Database correct view

1

This happens if product_type_id was not added by setup update script app/code/core/Mage/Sales/sql/sales_setup/upgrade-1.6.0.9-1.6.0.10.php:

$installer->startSetup();

$bestsellersTables = array($installer->getTable('sales/bestsellers_aggregated_daily'),
    $installer->getTable('sales/bestsellers_aggregated_monthly'),
    $installer->getTable('sales/bestsellers_aggregated_yearly'));

foreach ($bestsellersTables as $table) {
    $installer->getConnection()->addColumn(
        $table,
        'product_type_id',
        array(
            'type'     => Varien_Db_Ddl_Table::TYPE_TEXT,
            'length'   => 32,
            'default'  => Mage_Catalog_Model_Product_Type::TYPE_SIMPLE,
            'nullable' => false,
            'after'    => 'product_id',
            'comment'  => 'Product Type Id'
        )
    );
}

$installer->endSetup();

Often its just that Cache was not cleaned after pulling/adding Magento modifications. If this not the case I would recommend to set the value of "sales_setup" from 1.6.0.10 back to 1.6.0.9 in table core_resource

1

Check this table sales_bestsellers_aggregated_yearly have 'product_type_id' columns in it.

If 'product_type_id' column is not in table then you need to create (upload backup table) table again.

enter image description here

0

Rename your file app/etc/local.xml to app/etc/local.xmlBKP and run the install process using the data in this one.

Then remove local.xml that was automatically generated and rename the file app/etc/local.xmlBKP to app/etc/local.xml again.

0

Just to clarify: the reason is self explanatory, a column is missing in the database structure.

First thing to do is to check is whether or not the sales upgrade script ran, to do so, you need to run the following on your database:

select version from core_resource where code = "sales_setup";

First case:

If the result is 1.6.0.9, that means the script didnt run.

To make it run, as explained above, you need to flush the cache but its very unlikely you'll have access to your admin at this point, so it's better to jump on the server and run:

rm -Rf </home/to/your/magento/install>/var/cache/*

Obviously you'll have to replace </home/to/your/magento/install> with the path to your magento installation.

Second case:

If the result is 1.6.0.10, that means the script ran but, for some reasons, it didn't update the table schema.

Instead of having to manually tweak the database by trying to insert the new column, you can run the following :

update core_resource set version = "1.6.0.9" where code = "sales_setup";

Then flush the cache:

rm -Rf </home/to/your/magento/install>/var/cache/*

Hit your Magento install, the upgrade should now proceed correctly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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