Error after server restart

[james_flynn2]

Hi All,

I am running magento 2.2.5 on a VPS, Ubuntu 16.04 Apache, redis and varnish all enabled using maria db.

All working fine then yesterday the server restarted and now none of the products are showing on category pages.

Looking through the logs I have receiving this error.

Any help would be appreciated as i am going mad trying to fix this.

main.CRITICAL: SQLSTATE[HY000]: General error: 1030 Got error 1 "Operation not permitted" from storage engine InnoDB, query was: SELECT `main_table`.*, count(main_table.value) AS `count` FROM (SELECT `main_table`.`category_id` AS `value` FROM `catalog_category_product_index_store1` AS `main_table`
INNER JOIN `search_tmp_5c1b66b83503a8_05389005` AS `entities` ON main_table.product_id = entities.entity_id
INNER JOIN `catalog_category_entity` AS `category` ON main_table.category_id = category.entity_id WHERE (main_table.store_id = '1') AND (`category`.`path` LIKE '1/2/19/45%') AND (`category`.`level` > '3')) AS `main_table` GROUP BY `value` [] []

[james_flynn2 · Accepted solution]

In case anybody googles this or has a similar issue in the future I have managed to sort this out.

Essentially when the server restarted in looks like it had corrupt two tables in my magento instance. The tables were, catalog_category_product_index_store1 and catalog_category_product_index_store1_replica. I found out it was these tables by clicking into every table throuhg phpmyadmin and also bu trying to do a msqldump from the shell.

I restored the server to a working version from three days ago, backed up the working tables. I then restored the server back to broken instance, dropped the corrupt tables and uploaded the working tables and everything is now fixed.

Hope this helps someone.

[EDENCO]

The tables `catalog_category_product_index_store *` were broken.
The solution was to optimize the tables in all stores and replicas of these via phpMyAdmin.

Regards, Edwin

[mahade hassan_khan]

I might be face same problem, But can't get it. Could you please help me ?

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[HY000]: General error: 1030 Got error 1 "Operation not permitted" from storage engine InnoDB, query was: SELECT `e`.*, `at_position`.`position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product` AS `at_position` ON (at_position.`product_id`=e.entity_id) AND (category_id=99)
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2
 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id LIMIT 8
Exception #1 (PDOException): SQLSTATE[HY000]: General error: 1030 Got error 1 "Operation not permitted" from storage engine InnoDB

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[HY000]: General error: 1030 Got error 1 "Operation not permitted" from storage engine InnoDB, query was: SELECT `e`.*, `at_position`.`position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product` AS `at_position` ON (at_position.`product_id`=e.entity_id) AND (category_id=99)
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0

Dolphin.com.bd  

[avstudnitz]

@mahade hassan_khanTry to delete delete the database tables

catalog_category_product_index_store1

and

catalog_category_product_index_store1_replica

. Also those with other store IDs if you have them. Then call

bin/magento setup:upgrade

and

bin/magento indexer:reindex

in order to re-generate and re-fill the tables. That worked for me.