2

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?

2
  • Did you find any solution?
    – Raül
    Jun 17 '19 at 15:26
  • 1
    I have yet to see anyone respond with an answer :(
    – Jayreis
    Jun 18 '19 at 15:57
2

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

1
  • Thank you, I used the workaround and its also working for me. Oct 16 '20 at 18:54
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;
1
0

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

2
  • Does anyone know why this happens??
    – Jayreis
    Feb 14 '19 at 15:04
  • I have not found out why these tables or created or what there purpose is as of yet.
    – Jayreis
    Mar 20 '19 at 2:58
0

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...

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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