magento2 creating a lot of catalogrule_product_temp tables

Question

Magento2.2.5 based multisite (multi store view)seems to be creating a lot of temporary tables such as

catalogrule_product__temp9a310576
catalogrule_product__temp998786f4
etc...

However I am not sure why these tables are being created they all seem to be empty?

Answer 1

We have been seeing this issue as well. Until a solution is found I am using the following to clear the tables - we've not yet had any issues by removing them.

Add the following to bin/remove-temp-tables.php

<?php
$settings = require(__DIR__.'/../app/etc/env.php');
$database = $settings['db']['connection']['default'];

$conn = new mysqli($database['host'], $database['username'], $database['password'], $database['dbname']);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$tempName = 'catalogrule_product__temp';

$query = $conn->query('SHOW TABLES FROM `'.$database['dbname'].'`');
$tablesToDelete = [];

while ($row = $query->fetch_assoc()) {
    $tableName = array_shift($row);
    if (substr($tableName, 0, strlen($tempName)) === $tempName)
        $tablesToDelete[] = $tableName;
}

foreach($tablesToDelete AS $tableName) {
    $conn->query('DROP TABLE `'.$tableName.'`;');
}

echo 'Completed.'.PHP_EOL;

Run with php bin/remove-temp-tables.php

Answer 2

These tables get created by the Catalog Rule indexer. When this indexer fails (=throws an exception) the created table doesn't get cleaned up. One can use following mysql query to perform a manual cleanup: (make sure to replace %DATABASENAME% by your db name)

-- Set up variable to delete ALL tables starting with 'catalogrule_product__temp'
SET GROUP_CONCAT_MAX_LEN=1000000;
SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME)
             FROM information_schema.TABLES
             WHERE TABLE_SCHEMA = '%DATABASENAME%'
               AND TABLE_NAME LIKE 'catalogrule_product__temp%');
SET @delStmt = CONCAT('DROP TABLE ',  @tbls);
-- SELECT @delStmt;
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 3

even i am also facing the same issue. Mostly happening during re-indexing.

Answer 4

I have the same issues, did you find a solution? Is it safe to just delete those tables? My database is getting huge and it's getting more and more difficult to make a backup...

Answer 5

This issue will be fixed in version 2.4 https://github.com/magento/magento2/pull/27012

Answer 6

I faced this issue after migrating from Magento 1.9 to 2.3.5 and managed to solve it.

The issue is caused by the reindexing process not being able to finish it's task. So basically when ever you run php bin/magento cron:run or php bin/magento indexer:reindex you will get an additional temp table which is not being cleaned up.

In my case, the issues was caused by the CLI php version. I used php 7.3 when calling cron, but this raised some errors during the reindex process, so I switched to using php 7.1 when calling cron:run or indexer:reindex.

For Ubuntu, you can try using /usr/bin/php7.1 bin/magento cron:run.

The correct php version will vary depending on your Magento 2 version (older Magento versions will require older php versions for running cron jobs).

If you don't have access to your hosting server via SSH, you will need to ask your hosting provider to set this up.