Product EAV index exception: SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace `innodb_system` cannot contain TEMPORARY tables.,

Question

When we are rebuilding the index we are getting the following error.

Product EAV index exception: SQLSTATE[HY000]: General error: 1478 InnoDB: Tablespace innodb_system cannot contain TEMPORARY tables., query was: CREATE TEMPORARY TABLE IF NOT EXISTS catalog_product_index_eav_temp LIKE catalog_product_index_eav_tmp

Answer 1

If it is Magento 2.4 and you are using MySQL 8 I think you need to create a patch:

Edit vendor/magento/framework/DB/Adapter/Pdo/Mysql.php file and change:

$sql = sprintf('CREATE TEMPORARY TABLE %s %s LIKE %s', $ifNotExistsSql, $temporaryTable, $originTable);

to:

$sql = sprintf('CREATE TEMPORARY TABLE %s %s SELECT * FROM %s', $ifNotExistsSql, $temporaryTable, $originTable);

Answer 2

Magento's MySQL adapter uses CREATE TEMPORARY TABLE ... LIKE command in \Magento\Framework\DB\Adapter\Pdo\Mysql::createTemporaryTableLike.

Newer versions of MySQL (8+) have problems with CREATE TEMPORARY TABLE ... LIKE, as you can read in the official documentation (https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html):

You cannot use CREATE TEMPORARY TABLE ... LIKE to create an empty table based on the definition of a table that resides in the mysql tablespace, InnoDB system tablespace (innodb_system), or a general tablespace.

The documentation suggests to use CREATE TEMPORARY TABLE ... SELECT * FROM ... LIMIT 0 instead, but such command IS NOT an exact equivalent. It will create temporary table with the same fields, but will not create indexes. Without indexes performance of reindexing big amount of data is absurdly slow.

Below you can find my code that is using different approach. I firstly use SHOW CREATE TABLE to get query for creation of source table (including indexes creation), and then I modify it with PHP: I change CREATE TABLE to CREATE TEMPORARY TABLE, I add IF NOT EXISTS if necessary, I remove TABLESPACE if it exists, and I replace source table name with temporary table name.

Code:

    public function createTemporaryTableLike($temporaryTableName, $originTableName, $ifNotExists = false)
    {
        $ifNotExistsSql = ($ifNotExists ? ' IF NOT EXISTS' : '');
        $temporaryTable = $this->quoteIdentifier($this->_getTableName($temporaryTableName));
        $originTable = $this->quoteIdentifier($this->_getTableName($originTableName));
        $originCreate = $this->fetchPairs("SHOW CREATE TABLE {$originTable}");
        $sql = reset($originCreate);
        $sql = preg_replace('/\/\*!50100 TABLESPACE [^\s]+ \*\//', '', $sql);
        $sql = str_replace('CREATE TABLE', 'CREATE TEMPORARY TABLE' . $ifNotExistsSql, $sql);
        $sql = str_replace($originTable, $temporaryTable, $sql);

        return $this->query($sql);
    }

Patch for Magento 2.4.4-p1:

Index: vendor/magento/framework/DB/Adapter/Pdo/Mysql.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php b/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php
--- a/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php 
+++ b/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php (date 1661519230948)
@@ -2229,10 +2229,14 @@
      */
     public function createTemporaryTableLike($temporaryTableName, $originTableName, $ifNotExists = false)
     {
-        $ifNotExistsSql = ($ifNotExists ? 'IF NOT EXISTS' : '');
+        $ifNotExistsSql = ($ifNotExists ? ' IF NOT EXISTS' : '');
         $temporaryTable = $this->quoteIdentifier($this->_getTableName($temporaryTableName));
         $originTable = $this->quoteIdentifier($this->_getTableName($originTableName));
-        $sql = sprintf('CREATE TEMPORARY TABLE %s %s LIKE %s', $ifNotExistsSql, $temporaryTable, $originTable);
+        $originCreate = $this->fetchPairs("SHOW CREATE TABLE {$originTable}");
+        $sql = reset($originCreate);
+        $sql = preg_replace('/\/\*!50100 TABLESPACE [^\s]+ \*\//', '', $sql);
+        $sql = str_replace('CREATE TABLE', 'CREATE TEMPORARY TABLE' . $ifNotExistsSql, $sql);
+        $sql = str_replace($originTable, $temporaryTable, $sql);

         return $this->query($sql);
     }

Answer 3

Input:

show create table catalog_category_product_index_store2\G

*************************** 1. row ***************************
       Table: catalog_category_product_index_store2
Create Table: CREATE TABLE `catalog_category_product_index_store2` (
  `category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Category Id',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product Id',
  `position` int(11) DEFAULT NULL COMMENT 'Position',
  `is_parent` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Is Parent',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id',
  `visibility` smallint(5) unsigned NOT NULL COMMENT 'Visibility',
  PRIMARY KEY (`category_id`,`product_id`,`store_id`),
  KEY `CAT_CTGR_PRD_IDX_STORE2_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY` (`product_id`,`store_id`,`category_id`,`visibility`),
  KEY `IDX_2AB6FE58F086547FA7E4590837296849` (`store_id`,`category_id`,`visibility`,`is_parent`,`position`)
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Category Product Index Store2'

Take a closer look at: /*!50100 TABLESPACE innodb_system */

• FIX: Upon running mysqldump add --no-tablespaces option. That should resolve the problem
• Alternative: Use ALTER on this table

Answer 4

Need to change innodb_file_per_table | ON

Answer 5

I am also having this issue

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 you manage to fix it?

Best Wishes.

Answer 6

ALTER TABLE catalog_category_product_index_store1 TABLESPACE = innodb_file_per_table;

This might help to resolve the issue