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);
}