6

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

enter image description here

6 Answers 6

10

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);
4
  • It's working perfectly in Magento 2.4.2
    – Nikul
    Commented Dec 6, 2021 at 10:24
  • It doesn't work on magento2.4.4 . Very frustrating. Any other ideas? Commented Jun 25, 2022 at 12:35
  • This is not a perfect solution, as it slows down reindexes heavily. Please check my answer for more details: magento.stackexchange.com/a/359227/18731 Commented Aug 25, 2022 at 9:26
  • For production this is indeed not the best idea, but when running a development instance, this could simply work for the time being. Commented Mar 3, 2023 at 9:46
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);
     }

1

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
1
  • Didn't work with Magento 2.4.5-p1 and Mysql 8 Commented Apr 27, 2023 at 11:29
1

Need to change innodb_file_per_table | ON enter image description here

0

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.

2
  • No luck yet. This is very frustrating
    – Ashish
    Commented Feb 13, 2021 at 10:41
  • I m getting the same on M2.4.4. Have you manage to fix it ? Commented Jun 25, 2022 at 12:37
0

ALTER TABLE catalog_category_product_index_store1 TABLESPACE = innodb_file_per_table;

This might help to resolve the issue

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.