1

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?

| improve this question | | | | |
  • 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
0

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

| improve this answer | | | | |
  • 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...

| improve this answer | | | | |
0

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

| improve this answer | | | | |
0

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;
| improve this answer | | | | |
0

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

| improve this answer | | | | |

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.