Error aggregating data tags.

Question

Error aggregating data tags.

I seem to have a problem with indexing on Magento. All available indexes are indexed with a penalty, except for one:

Tag Aggregation Data

When indexing with the following error, but I cannot figure out what the problem is.

Tag Aggregation Data index process unknown error: exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp.base_popularity, 0))' in 'field list'' in /mywebsite-domain/httpd.www/lib/Zend/Db/Statement/Pdo.php:228 Stack trace: #0 /mywebsite-domain/httpd.www/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array) #1 /mywebsite-domain/httpd.www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #2 /mywebsite-domain/httpd.www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #3 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) #4 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ma...', Array) #5 /mywebsite-domain/httpd.www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ma...', Array) #6 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(233): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ma...') #7 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(135): Mage_Tag_Model_Resource_Indexer_Summary->aggregate() #8 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Tag_Model_Resource_Indexer_Summary->reindexAll() #9 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll() #10 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll() #11 /mywebsite-domain/httpd.www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything() #12 /mywebsite-domain/httpd.www/shell/indexer.php(216): Mage_Shell_Compiler->run() #13 {main} Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp.base_popularity, 0))' in 'field list', query was: INSERT INTO `magentotag_summary` (`tag_id`, `store_id`, `customers`, `products`, `popularity`, `uses`, `historical_uses`, `base_popularity`) SELECT `tr`.`tag_id`, `tr`.`store_id`, COUNT(DISTINCT tr.customer_id) AS `customers`, COUNT(DISTINCT tr.product_id) AS `products`, `COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp`.`base_popularity, 0))` AS `popularity`, 0 AS `uses`, 0 AS `historical_uses`, 0 AS `base_popularity` FROM `magentotag_relation` AS `tr` INNER JOIN `magentocore_store` AS `cs` ON cs.store_id = tr.store_id INNER JOIN `magentocatalog_product_website` AS `pw` ON cs.website_id = pw.website_id AND tr.product_id = pw.product_id INNER JOIN `magentocatalog_product_entity` AS `e` ON tr.product_id = e.entity_id LEFT JOIN `magentotag_properties` AS `tp` ON tp.tag_id = tr.tag_id AND tp.store_id = tr.store_id INNER JOIN `magentocatalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 273 AND tad_status.store_id = 0 LEFT JOIN `magentocatalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 273 AND tas_status.store_id = cs.store_id INNER JOIN `magentocatalog_product_entity_int` AS `tad_visibility` ON tad_visibility.entity_id = e.entity_id AND tad_visibility.attribute_id = 526 AND tad_visibility.store_id = 0 LEFT JOIN `magentocatalog_product_entity_int` AS `tas_visibility` ON tas_visibility.entity_id = e.entity_id AND tas_visibility.attribute_id = 526 AND tas_visibility.store_id = cs.store_id INNER JOIN `magentocataloginventory_stock_status` AS `ciss` ON ciss.product_id = e.entity_id AND ciss.website_id = cs.website_id WHERE (tr.active = 1) AND (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1) AND (IF(IFNULL(tas_visibility.value_id, -1) > 0, tas_visibility.value, tad_visibility.value)!=1) AND (ciss.stock_status = 1) GROUP BY `tr`.`tag_id`, `tr`.`store_id` ON DUPLICATE KEY UPDATE `tag_id` = VALUES(`tag_id`), `store_id` = VALUES(`store_id`), `customers` = VALUES(`customers`), `products` = VALUES(`products`), `popularity` = VALUES(`popularity`), `uses` = VALUES(`uses`), `historical_uses` = VALUES(`historical_uses`), `base_popularity` = VALUES(`base_popularity`)' in /mywebsite-domain/httpd.www/lib/Zend/Db/Statement/Pdo.php:235 Stack trace: #0 /mywebsite-domain/httpd.www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #1 /mywebsite-domain/httpd.www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #2 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) #3 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ma...', Array) #4 /mywebsite-domain/httpd.www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ma...', Array) #5 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(233): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ma...') #6 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(135): Mage_Tag_Model_Resource_Indexer_Summary->aggregate() #7 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Tag_Model_Resource_Indexer_Summary->reindexAll() #8 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll() #9 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll() #10 /mywebsite-domain/httpd.www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything() #11 /mywebsite-domain/httpd.www/shell/indexer.php(216): Mage_Shell_Compiler->run() #12 {main}

Any clues?

Thanks.

+5

php mysql pdo magento-1.9

Yorck Jan 27 '16 at 22:59

source share

4 answers

Anse · Answer 1 · 2016-05-10T15:49:01+0000

Erroneous backlinks are inserted because Zend_Db_Select → _ tableCols () cannot detect COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp.base_popularity, 0)) as some SQL function, which then must pass her on Zend_Db_Expr() . Instead, it is defined as [currentCorrelationName]. [Col], and then treated as a regular string.

The fix for this problem is to weaken the regular expression to detect SQL functions:

Open lib/Zend/Db/Select.php , find the following line and remove the line ending character ($):

 const REGEX_COLUMN_EXPR = '/^([\w]*\(([^\(\)]|(?1))*\))$/';

... so it looks like this:

 const REGEX_COLUMN_EXPR = '/^([\w]*\(([^\(\)]|(?1))*\))/';

Now Zend_Db_Select->_tableCols() should pass the entire expression to Zend_Db_Expr() , and the SQL error should disappear.

Shadow · Answer 2 · 2016-01-28T01:47:31+0000

I do not think this is an indexing error. If you carefully check the selected part of the insert ... select ... statement, you will see that the expression COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp . base_popularity, 0)) is reversed (`), which means mysql is trying to interpret this as a column name and not as an expression. Obviously there is no such column, so an error message. Remove the inverse elements around this expression and the error message will disappear.

Evan hamlet · Answer 3 · 2016-02-22T16:29:16+0000

I have exactly the same problem. @Shadow There is no backquote in Back.Query

 /** * Aggregate tags by specified ids * * @param null|int|array $tagIds * @return Mage_Tag_Model_Resource_Indexer_Summary */ public function aggregate($tagIds = null) { $writeAdapter = $this->_getWriteAdapter(); $this->beginTransaction(); try { if (!empty($tagIds)) { $writeAdapter->delete( $this->getTable('tag/summary'), array('tag_id IN(?)' => $tagIds) ); } else { $writeAdapter->delete($this->getTable('tag/summary')); } $select = $writeAdapter->select() ->from( array('tr' => $this->getTable('tag/relation')), array( 'tr.tag_id', 'tr.store_id', 'customers' => 'COUNT(DISTINCT tr.customer_id)', 'products' => 'COUNT(DISTINCT tr.product_id)', 'popularity' => 'COUNT(tr.customer_id) + MIN(' . $writeAdapter->getCheckSql( 'tp.base_popularity IS NOT NULL', 'tp.base_popularity', '0' ) . ')', 'uses' => new Zend_Db_Expr(0), // deprecated since 1.4.0.1 'historical_uses' => new Zend_Db_Expr(0), // deprecated since 1.4.0.1 'base_popularity' => new Zend_Db_Expr(0) // deprecated since 1.4.0.1 ) )

Hope someone has a solution. I tried almost everything.

proxiblue · Answer 4 · 2017-03-03T17:02:30+0000

Recently, I got this on the customers site, a completely new installation, basically install base 1.9.3.2

here is what i found:

The site uses the composer (of course) to install some basic packages.

One of them:

colinmollenhour/magento-redis-session": "^2.0"

This in turn affects dependencies.

- Installing magento/zendframework1 (1.12.16-patch3) Downloading: 100% Extracting archive REASON: colinmollenhour/php-redis-session-abstract v1.3.1 requires magento/zendframework1 ~1.12.0 -> satisfiable by magento/zendframework1[1.12.0, 1.12.1, 1.12.10, 1.12.13, 1.12.16, 1.12.16-patch1, 1.12.16-patch2, 1.12.16-patch3, 1.12.2, 1.12.3, 1.12.4, 1.12.5, 1.12.6, 1.12.7, 1.12.8, 1.12.9, 1.12.9-patch1]. Thus, the update for magento / zendframework1 is installed in the vendor. At this time, "magento / zendframework1: 1.12.16-patch3" is installed

As soon as I removed the redis-session composition packages that removed the zend update, everything became normal.

Removals: colinmollenhour/magento-redis-session, colinmollenhour/php-redis-session-abstract, magento/zendframework1, colinmollenhour/credis - Removing colinmollenhour/magento-redis-session (2.0.2) - Removing colinmollenhour/php-redis-session-abstract (v1.3.1) - Removing magento/zendframework1 (1.12.16-patch3) - Removing colinmollenhour/credis (1.7)

Version 2.0 was installed in the composer's record for installing the package> Looking at package releases ( https://github.com/colinmollenhour/Cm_RedisSession/releases )

v1.12 has been marked as: The latest version before switching to a Magento-compatible version.

Composer lock for this version: "colinmollenhour/magento-redis-session": "1.12"

solves the problem and saves the redis-session update in the purple codebase.

So basically the problem is that redis-session> 1.12 is not compatible with m1.

Error aggregating data tags.

More articles: