SQL Error when I try to reindex Magento 2

Question

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.

• fmsthird: stackoverflow.com/questions/11805793/…
• fmsthird: should follow the link above
• CJNotts: 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?
• fmsthird: you can ask your hosting provider to do it
• CJNotts: Much obliged. I'll do that now.

Answer 1

Try resetting indexers and then reindexing again ;

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

• CJNotts: Hi. Thank you but I'm afraid I tried that already. Just got the same error unfortunately.
• Vivek Kumar: Please do check that you're not out of storage in your hosting.
• CJNotts: Thanks, I'll check with my hosting provider. We have been having some storage space issues lately so it could be related.

Answer 2

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