SQLSTATE[HY000]: General error: 1478 when trying to reindex or saving new products

[mrmancomed1cf3]

Greetings.

I am having General error: 1478 when trying to reindex magento 2. I am using mysql and when running the ssh command, this comes out:

Design Config Grid index has been rebuilt successfully in 00:00:00
Customer Grid index has been rebuilt successfully in 00:00:00
Category Products index has been rebuilt successfully in 00:00:00
Product Categories index has been rebuilt successfully in 00:00:00
Catalog Rule Product index has been rebuilt successfully in 00:00:00
SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `kabulo1_ei19` cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `catalog_product_index_eav_temp` LIKE `catalog_product_index_eav_tmp`
Stock index has been rebuilt successfully in 00:00:00
Inventory index has been rebuilt successfully in 00:00:00
Catalog Product Rule index has been rebuilt successfully in 00:00:00
SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `kabulo1_ei19` cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `catalog_product_index_price_temp` LIKE `catalog_product_index_price_tmp`
Google Product Removal Feed index has been rebuilt successfully in 00:00:00
Google Product Feed index has been rebuilt successfully in 00:00:00
Catalog Search index has been rebuilt successfully in 00:00:00

Please assist.

I am having an issue with those temporary tables

[Manish Mittal]

@mrmancomed1cf3 

It seems memory issue:

tmp_table_size = 4G
max_heap_table_size = 4G

seems to help in my.cnf

or remove : $table->setOption('type', 'memory'); from
magento2/lib/internal/Magento/Framework/Search/Adapter/Mysql/TemporaryStorage.php

to disable use of memory type tmp tables.

I'd do that and recommend using tmpfs for /tmp and setting my.cnf temp to use /tmp if you have enough ram!

tmpfs 60350944 176 60350768 1% /tmp

Take reference from here:

https://github.com/magento/magento2/issues/6415

[mrmancomed1cf3]

I have tried this solution but it did nit work for me. I still get the same error : SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `kabulo1_ei19` cannot contain TEMPORARY tables. "kabulo1_ei19" being my mysql database.

Please assist guys,

I think the issue here is not the memory.

[The Big G Man]

Hi there,

We are also having this issue after upgrade from 2.4.1 to 2.4.2

SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `innodb_system` cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `catalog_category_product_index_store1_tmp` LIKE `catalog_category_product_index_store1`

Did anyone find a fix after?

Thanks!