2

I have an issue in Magento 2.2.7 where my site has the message "One or more indexers are invalid." and looking at the indexers I see the following:

enter image description here

However when I try to run php bin/magento indexer:reindex in the command line I get the following error:

Catalog Search indexer process unknown error:
SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_41e5_3.MYI'; try to repair it, query was: SELECT DISTINCT  `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_int` AS `t`
 LEFT JOIN `catalog_product_entity_int` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = 1
 LEFT JOIN `catalog_product_entity_int` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (93, 151, 180, 222, 128, 83, 226, 224, 256, 227, 223, 219, 97, 99)) AND (t.entity_id IN (59, 60, 61, 64, 65, 69, 71, 72, 73, 74, 75, 77, 78, 79, 80, 81, 83, 84, 85, 86, 87, 88, 91, 92, 94, 97, 100, 104, 106, 107, 109, 113, 114, 115, 116, 119, 120, 121, 122, 123, 124, 125, 126, 130, 131, 133, 134, 135, 136, 137, 138, 139, 141, 145, 146, 149, 151, 154, 158, 161, 162, 163, 164, 165, 166, 168, 169, 170, 171, 172, 174, 176, 177, 182, 189, 190, 206, 207, 211, 212, 213, 214, 215, 216, 219, 220, 228, 229, 230, 232, 235, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 252, 256, 258, 259, 260, 264, 265, 270, 271, 272, 273, 275, 283, 284, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 313, 314, 315, 316, 317, 324, 326, 327, 335, 336, 337, 338, 339, 342, 343, 344, 345, 346, 348, 350, 354, 355, 356, 358, 359, 360, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 378, 381, 384, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 412, 413, 414, 419, 420, 421, 422, 423, 425, 426, 427, 428, 429, 430, 431, 434, 435, 437, 441, 442, 443, 444, 448, 449, 454, 456, 457, 458, 464, 467, 468, 469, 472, 473, 474, 475, 476, 477, 478, 482, 483, 488, 489, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500, 501, 510, 834, 835, 1061, 1062, 1264, 1265, 1266, 1267, 1506, 1507, 1508, 1509, 1510, 1511, 1569, 2014, 2015, 2017)) UNION ALL SELECT DISTINCT  `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_varchar` AS `t`
 LEFT JOIN `catalog_product_entity_varchar` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = 1
 LEFT JOIN `catalog_product_entity_varchar` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (154, 109, 130, 73, 129, 110, 111)) AND (t.entity_id IN (59, 60, 61, 64, 65, 69, 71, 72, 73, 74, 75, 77, 78, 79, 80, 81, 83, 84, 85, 86, 87, 88, 91, 92, 94, 97, 100, 104, 106, 107, 109, 113, 114, 115, 116, 119, 120, 121, 122, 123, 124, 125, 126, 130, 131, 133, 134, 135, 136, 137, 138, 139, 141, 145, 146, 149, 151, 154, 158, 161, 162, 163, 164, 165, 166, 168, 169, 170, 171, 172, 174, 176, 177, 182, 189, 190, 206, 207, 211, 212, 213, 214, 215, 216, 219, 220, 228, 229, 230, 232, 235, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 252, 256, 258, 259, 260, 264, 265, 270, 271, 272, 273, 275, 283, 284, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 313, 314, 315, 316, 317, 324, 326, 327, 335, 336, 337, 338, 339, 342, 343, 344, 345, 346, 348, 350, 354, 355, 356, 358, 359, 360, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 378, 381, 384, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 412, 413, 414, 419, 420, 421, 422, 423, 425, 426, 427, 428, 429, 430, 431, 434, 435, 437, 441, 442, 443, 444, 448, 449, 454, 456, 457, 458, 464, 467, 468, 469, 472, 473, 474, 475, 476, 477, 478, 482, 483, 488, 489, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500, 501, 510, 834, 835, 1061, 1062, 1264, 1265, 1266, 1267, 1506, 1507, 1508, 1509, 1510, 1511, 1569, 2014, 2015, 2017)) UNION ALL SELECT DISTINCT  `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_text` AS `t`
 LEFT JOIN `catalog_product_entity_text` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = 1
 LEFT JOIN `catalog_product_entity_text` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (152, 75, 153, 225, 220, 85, 221, 76)) AND (t.entity_id IN (59, 60, 61, 64, 65, 69, 71, 72, 73, 74, 75, 77, 78, 79, 80, 81, 83, 84, 85, 86, 87, 88, 91, 92, 94, 97, 100, 104, 106, 107, 109, 113, 114, 115, 116, 119, 120, 121, 122, 123, 124, 125, 126, 130, 131, 133, 134, 135, 136, 137, 138, 139, 141, 145, 146, 149, 151, 154, 158, 161, 162, 163, 164, 165, 166, 168, 169, 170, 171, 172, 174, 176, 177, 182, 189, 190, 206, 207, 211, 212, 213, 214, 215, 216, 219, 220, 228, 229, 230, 232, 235, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 252, 256, 258, 259, 260, 264, 265, 270, 271, 272, 273, 275, 283, 284, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 313, 314, 315, 316, 317, 324, 326, 327, 335, 336, 337, 338, 339, 342, 343, 344, 345, 346, 348, 350, 354, 355, 356, 358, 359, 360, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 378, 381, 384, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 412, 413, 414, 419, 420, 421, 422, 423, 425, 426, 427, 428, 429, 430, 431, 434, 435, 437, 441, 442, 443, 444, 448, 449, 454, 456, 457, 458, 464, 467, 468, 469, 472, 473, 474, 475, 476, 477, 478, 482, 483, 488, 489, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500, 501, 510, 834, 835, 1061, 1062, 1264, 1265, 1266, 1267, 1506, 1507, 1508, 1509, 1510, 1511, 1569, 2014, 2015, 2017)) UNION ALL SELECT DISTINCT  `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_decimal` AS `t`
 LEFT JOIN `catalog_product_entity_decimal` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = 1
 LEFT JOIN `catalog_product_entity_decimal` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (77, 233)) AND (t.entity_id IN (59, 60, 61, 64, 65, 69, 71, 72, 73, 74, 75, 77, 78, 79, 80, 81, 83, 84, 85, 86, 87, 88, 91, 92, 94, 97, 100, 104, 106, 107, 109, 113, 114, 115, 116, 119, 120, 121, 122, 123, 124, 125, 126, 130, 131, 133, 134, 135, 136, 137, 138, 139, 141, 145, 146, 149, 151, 154, 158, 161, 162, 163, 164, 165, 166, 168, 169, 170, 171, 172, 174, 176, 177, 182, 189, 190, 206, 207, 211, 212, 213, 214, 215, 216, 219, 220, 228, 229, 230, 232, 235, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 252, 256, 258, 259, 260, 264, 265, 270, 271, 272, 273, 275, 283, 284, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 313, 314, 315, 316, 317, 324, 326, 327, 335, 336, 337, 338, 339, 342, 343, 344, 345, 346, 348, 350, 354, 355, 356, 358, 359, 360, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 378, 381, 384, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 412, 413, 414, 419, 420, 421, 422, 423, 425, 426, 427, 428, 429, 430, 431, 434, 435, 437, 441, 442, 443, 444, 448, 449, 454, 456, 457, 458, 464, 467, 468, 469, 472, 473, 474, 475, 476, 477, 478, 482, 483, 488, 489, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500, 501, 510, 834, 835, 1061, 1062, 1264, 1265, 1266, 1267, 1506, 1507, 1508, 1509, 1510, 1511, 1569, 2014, 2015, 2017))

Looking at the error it would seem that one of my tables is corrupt/broken but I'm unsure how to resolve the issue. If someone could suggest a way to resolve this I would appreciate it.

  • 1
  • 1
    should follow the link above – fmsthird Mar 13 at 12:52
  • Thank you. As I'm on using a hosted server can I perform the steps mentioned in the stackoverflow post or do I need to get my hosting provider to perform them for me? – CJNotts Mar 13 at 12:55
  • 1
    you can ask your hosting provider to do it – fmsthird Mar 13 at 12:58
  • 1
    Much obliged. I'll do that now. – CJNotts Mar 13 at 13:00
1

Try resetting indexers and then reindexing again ;

php bin/magento indexer:reset
php bin/magento indexer:reindex
  • Hi. Thank you but I'm afraid I tried that already. Just got the same error unfortunately. – CJNotts Mar 13 at 13:14
  • 1
    Please do check that you're not out of storage in your hosting. – Vivek Kumar Mar 13 at 13:22
  • Thanks, I'll check with my hosting provider. We have been having some storage space issues lately so it could be related. – CJNotts Mar 13 at 13:23
0

Many thanks to Vivek Kumar and magefms for their advice. As it turns out this was indeed a storage issue on my hosting server.

I have contacted my hosting provider and we have transferred to a new server with greater resources. The issue now seems to be resolved.

Running the commands

php bin/magento indexer:reset
php bin/magento indexer:reindex

Reindexes the site as usual

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.