Magento2: Column not found: 1054 Unknown column 'cat_index_position' in 'order clause'

[craigcarnell]

Install of Magento 2.0
Use data migration tool to import data
Upgrade to 2.1.
Clear Caches
Re-index everything

Issue 1: View Top Level Category:

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`created_at`, `e`.`updated_at`, `e`.`sku`, `product_entity`.`row_id`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_flat_3` AS `e`
 INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.entity_id = e.entity_id AND (product_entity.created_in <= 1 AND product_entity.updated_in > 1)
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 ORDER BY `cat_index_position` ASC, `e`.`entity_id` ASC
 LIMIT 102 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat_index_position' in 'order clause', query was: SELECT 1 AS `status`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`created_at`, `e`.`updated_at`, `e`.`sku`, `product_entity`.`row_id`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_flat_3` AS `e`
 INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.entity_id = e.entity_id AND (product_entity.created_in <= 1 AND product_entity.updated_in > 1)
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 ORDER BY `cat_index_position` ASC, `e`.`entity_id` ASC
 LIMIT 10
Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat_index_position' in 'order clause'

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat_index_position' in 'order clause', query was: SELECT 1 AS `status`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`created_at`, `e`.`updated_at`, `e`.`sku`, `product_entity`.`row_id`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_flat_3` AS `e`
 INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.entity_id = e.entity_id AND (product_entity.created_in <= 1 AND product_entity.updated_in > 1)
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 ORDER BY `cat_index_position` ASC, `e`.`entity_id` ASC
 LIMIT 10
#0 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#1 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT 1 AS `st...', Array)
#4 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(452): Zend_Db_Adapter_Pdo_Abstract->query('SELECT 1 AS `st...', Array)
#5 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(507): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT 1 AS `st...', Array)
#6 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(737): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select), Array)
#7 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Data/Collection/Db/FetchStrategy/Query.php(21): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\Framework\DB\Select), Array)
#8 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Data/Collection/AbstractDb.php(772): Magento\Framework\Data\Collection\Db\FetchStrategy\Query->fetchAll(Object(Magento\Framework\DB\Select), Array)
#9 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(1111): Magento\Framework\Data\Collection\AbstractDb->_fetchAll(Object(Magento\Framework\DB\Select))
#10 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Catalog/Model/ResourceModel/Product/Collection/Interceptor.php(1090): Magento\Eav\Model\Entity\Collection\AbstractCollection->_loadEntities(false, false)
#11 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(910): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->_loadEntities(false, false)
#12 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-catalog/Model/ResourceModel/Product/Collection.php(710): Magento\Eav\Model\Entity\Collection\AbstractCollection->load(false, false)
#13 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(146): Magento\Catalog\Model\ResourceModel\Product\Collection->load(false, false)
#14 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Catalog/Model/ResourceModel/Product/Collection/Interceptor.php(195): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->___callPlugins('load', Array, Array)
#15 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Data/Collection.php(820): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->load()
#16 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Catalog/Model/ResourceModel/Product/Collection/Interceptor.php(1727): Magento\Framework\Data\Collection->getIterator()
#17 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-catalog/Block/Product/ListProduct.php(321): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->getIterator()
#18 /Users/ccarnell/Sites/vax-uk2/var/generation/VaxLtd/Widget/Block/Category/Bestseller/Interceptor.php(219): Magento\Catalog\Block\Product\ListProduct->getIdentities()
#19 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-page-cache/Model/Layout/LayoutPlugin.php(71): VaxLtd\Widget\Block\Category\Bestseller\Interceptor->getIdentities()
#20 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(152): Magento\PageCache\Model\Layout\LayoutPlugin->afterGetOutput(Object(Magento\Framework\View\Layout\Interceptor), '    <script>\n  ...')
#21 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Framework/View/Layout/Interceptor.php(494): Magento\Framework\View\Layout\Interceptor->___callPlugins('getOutput', Array, Array)
#22 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/View/Result/Page.php(243): Magento\Framework\View\Layout\Interceptor->getOutput()
#23 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/View/Result/Layout.php(164): Magento\Framework\View\Result\Page->render(Object(Magento\Framework\App\Response\Http\Interceptor))
#24 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(74): Magento\Framework\View\Result\Layout->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#25 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\View\Result\Page\Interceptor->___callParent('renderResult', Array)
#26 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'renderResult', Object(Magento\Framework\View\Result\Page\Interceptor), Array, 'result-varnish-...')
#27 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-page-cache/Model/Controller/Result/VarnishPlugin.php(74): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Response\Http\Interceptor))
#28 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Chain/Chain.php(67): Magento\PageCache\Model\Controller\Result\VarnishPlugin->aroundRenderResult(Object(Magento\Framework\View\Result\Page\Interceptor), Object(Closure), Object(Magento\Framework\App\Response\Http\Interceptor))
#29 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'renderResult', Object(Magento\Framework\View\Result\Page\Interceptor), Array, 'result-builtin-...')
#30 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-page-cache/Model/Controller/Result/BuiltinPlugin.php(67): Magento\Framework\View\Result\Page\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Response\Http\Interceptor))
#31 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(142): Magento\PageCache\Model\Controller\Result\BuiltinPlugin->aroundRenderResult(Object(Magento\Framework\View\Result\Page\Interceptor), Object(Closure), Object(Magento\Framework\App\Response\Http\Interceptor))
#32 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Framework/View/Result/Page/Interceptor.php(130): Magento\Framework\View\Result\Page\Interceptor->___callPlugins('renderResult', Array, Array)
#33 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/App/Http.php(139): Magento\Framework\View\Result\Page\Interceptor->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#34 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#35 /Users/ccarnell/Sites/vax-uk2/pub/index.php(37): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#36 {main}

Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat_index_position' in 'order clause'
#0 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#2 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#3 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT 1 AS `st...', Array)
#5 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(452): Zend_Db_Adapter_Pdo_Abstract->query('SELECT 1 AS `st...', Array)
#6 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(507): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT 1 AS `st...', Array)
#7 /Users/ccarnell/Sites/vax-uk2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(737): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select), Array)
#8 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Data/Collection/Db/FetchStrategy/Query.php(21): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\Framework\DB\Select), Array)
#9 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Data/Collection/AbstractDb.php(772): Magento\Framework\Data\Collection\Db\FetchStrategy\Query->fetchAll(Object(Magento\Framework\DB\Select), Array)
#10 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(1111): Magento\Framework\Data\Collection\AbstractDb->_fetchAll(Object(Magento\Framework\DB\Select))
#11 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Catalog/Model/ResourceModel/Product/Collection/Interceptor.php(1090): Magento\Eav\Model\Entity\Collection\AbstractCollection->_loadEntities(false, false)
#12 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(910): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->_loadEntities(false, false)
#13 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-catalog/Model/ResourceModel/Product/Collection.php(710): Magento\Eav\Model\Entity\Collection\AbstractCollection->load(false, false)
#14 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(146): Magento\Catalog\Model\ResourceModel\Product\Collection->load(false, false)
#15 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Catalog/Model/ResourceModel/Product/Collection/Interceptor.php(195): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->___callPlugins('load', Array, Array)
#16 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Data/Collection.php(820): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->load()
#17 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Catalog/Model/ResourceModel/Product/Collection/Interceptor.php(1727): Magento\Framework\Data\Collection->getIterator()
#18 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-catalog/Block/Product/ListProduct.php(321): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->getIterator()
#19 /Users/ccarnell/Sites/vax-uk2/var/generation/VaxLtd/Widget/Block/Category/Bestseller/Interceptor.php(219): Magento\Catalog\Block\Product\ListProduct->getIdentities()
#20 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-page-cache/Model/Layout/LayoutPlugin.php(71): VaxLtd\Widget\Block\Category\Bestseller\Interceptor->getIdentities()
#21 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(152): Magento\PageCache\Model\Layout\LayoutPlugin->afterGetOutput(Object(Magento\Framework\View\Layout\Interceptor), '    <script>\n  ...')
#22 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Framework/View/Layout/Interceptor.php(494): Magento\Framework\View\Layout\Interceptor->___callPlugins('getOutput', Array, Array)
#23 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/View/Result/Page.php(243): Magento\Framework\View\Layout\Interceptor->getOutput()
#24 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/View/Result/Layout.php(164): Magento\Framework\View\Result\Page->render(Object(Magento\Framework\App\Response\Http\Interceptor))
#25 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(74): Magento\Framework\View\Result\Layout->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#26 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\View\Result\Page\Interceptor->___callParent('renderResult', Array)
#27 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'renderResult', Object(Magento\Framework\View\Result\Page\Interceptor), Array, 'result-varnish-...')
#28 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-page-cache/Model/Controller/Result/VarnishPlugin.php(74): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Response\Http\Interceptor))
#29 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Chain/Chain.php(67): Magento\PageCache\Model\Controller\Result\VarnishPlugin->aroundRenderResult(Object(Magento\Framework\View\Result\Page\Interceptor), Object(Closure), Object(Magento\Framework\App\Response\Http\Interceptor))
#30 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'renderResult', Object(Magento\Framework\View\Result\Page\Interceptor), Array, 'result-builtin-...')
#31 /Users/ccarnell/Sites/vax-uk2/vendor/magento/module-page-cache/Model/Controller/Result/BuiltinPlugin.php(67): Magento\Framework\View\Result\Page\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Response\Http\Interceptor))
#32 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/Interception/Interceptor.php(142): Magento\PageCache\Model\Controller\Result\BuiltinPlugin->aroundRenderResult(Object(Magento\Framework\View\Result\Page\Interceptor), Object(Closure), Object(Magento\Framework\App\Response\Http\Interceptor))
#33 /Users/ccarnell/Sites/vax-uk2/var/generation/Magento/Framework/View/Result/Page/Interceptor.php(130): Magento\Framework\View\Result\Page\Interceptor->___callPlugins('renderResult', Array, Array)
#34 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/App/Http.php(139): Magento\Framework\View\Result\Page\Interceptor->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#35 /Users/ccarnell/Sites/vax-uk2/vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#36 /Users/ccarnell/Sites/vax-uk2/pub/index.php(37): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#37 {main}

[craigcarnell]

Issue 2: Also in my sub-categories, no products are visible.

Turning off flat categories and flat products fixes the first issue

[kevincal]

I've seen this in 1.9.x as well. Have you found a resolution?

[nagrgk]

any solution for this in 1.9? we cannot turn off flat .

[josh-carter]

@kevincal @nagrgk I've wrote an extension to fix the error in 1.9x, its open source so feel free to use it Webtise Cat Index Position Fix.

[magento-engcom-team]

@craigcarnell, thank you for your report.
We were not able to reproduce this issue by following the steps you provided. If you'd like to update it, please reopen the issue.

[hish]

reindex