obukhow’s quick tips

Some code recipes and useful scripts.

Aug 2   command line   find   linux   mac os

Find Products Without Images

SELECT `p`.`sku` FROM `catalog_product_entity` AS `p`
LEFT JOIN `catalog_product_entity_varchar` AS `attr_images`
	ON `attr_images`.`entity_id` = `p`.`entity_id`
	AND `attr_images`.`attribute_id` IN (SELECT `attribute_id` FROM `eav_attribute`
			WHERE `attribute_code` IN ('image', 'small_image', 'thumbnail') AND `entity_type_id` = 4
		)
LEFT JOIN `catalog_product_entity_media_gallery` AS `gallery`
	ON `gallery`.`entity_id` = `p`.`entity_id`

WHERE (`attr_images`.`value` IS NULL OR `attr_images`.`value` = 'no_selection') AND (`gallery`.`value` IS NULL);
2019   gallery   images   product   sql

Clear redis cache

redis-cli -h <host> -p <port> flushall

The command will remove all data from client connected (with host and port).

2019   cache   magento 2   redis

Generate Self-signed SSL Certificate

You can easily generate self-signed SSL certificate for your local machine using openssl this command line tool

openssl req -x509 -nodes -days 365 -newkey rsa:1024 \
    -keyout /etc/ssl/private/mylocal.key \
    -out /etc/ssl/certs/mylocal.crt
2019   certificate   ssl

Get locked transactions in MySQL

SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';
SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;
2018   mysql
2018   mac os   terminal

Log Debug Backtrace in Magento 2

$obj = \Magento\Framework\App\ObjectManager::getInstance();
$fileHandler = $obj->create(\Magento\Framework\Logger\Handler\Base::class, ['fileName' => 'var/log/debug.log']);
$logger = $obj->create(\Monolog\Logger::class, [
                'name' => 'save',
                'handlers' => ['file' => $fileHandler],
                'processors' => ['process_id' => $obj->get(\Monolog\Processor\ProcessIdProcessor::class)]
            ]);
$logger->info(var_export(\Magento\Framework\Debug::backtrace(1, 0, 1), 1));

Result could be found in var/log/debug.log

2018   debug   log   magento2

Delete product duplicate images

The following solutions helps to find duplicate image file for each product and to remove it.
Create file delete.php in Magento base directory, put the following code into the file.

<?php
include('app/Mage.php');
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
ob_implicit_flush(1);

$mediaApi  = Mage::getModel("catalog/product_attribute_media_api");
$_products = Mage::getModel('catalog/product')->getCollection();
$i         = 0;
$total     = count($_products);
$count     = 0;
foreach ($_products as $_prod) {
    $_product   = Mage::getModel('catalog/product')->load($_prod->getId());
    $_md5Values = array();

    //protected base image
    $baseImage = $_product->getImage();
    if ($baseImage != 'no_selection') {
        $filePath = Mage::getBaseDir('media') . '/catalog/product' . $baseImage;
        if (file_exists($filePath)) {
            $_md5Values[] = md5(file_get_contents($filePath));
        }
    }

    $i++;
    echo "processing product $i of $total " . PHP_EOL;

    // Loop through product images
    $_images = $_product->getMediaGalleryImages();
    if ($_images) {
        foreach ($_images as $_image) {
            //protected base image
            if ($_image->getFile() == $baseImage) {
                continue;
            }

            $filePath = Mage::getBaseDir('media') . '/catalog/product' . $_image->getFile();
            if (!file_exists($filePath)) {
                continue;
            }
            $md5 = md5(file_get_contents($filePath));
            if (in_array($md5, $_md5Values)) {
                $mediaApi->remove($_product->getId(), $_image->getFile());
                echo 'removed duplicate image from ' . $_product->getSku() . PHP_EOL;
                $count++;
            } else {
                $_md5Values[] = $md5;
            }
        }
    }
}

To launch the script just launch in terminal

>php delete.php

Or visit

http://YourWebsiteURL/delete.php

from your browser.

Thanks to Aadil for a solution.

2016   gallery   images   product

Hide all categories without products and show categories with products

Reindex category products index at first and then execute following sql-script.

UPDATE `catalog_category_entity_int` AS `status`
INNER JOIN `eav_attribute` AS `attr` ON `attr`.`attribute_code` = 'is_active'
AND `attr`.`entity_type_id` = 3
AND `status`.`attribute_id` = `attr`.`attribute_id`
SET `status`.`value` = IF((SELECT COUNT(`index`.`product_id`)
	FROM `catalog_category_product_index` AS `index`
	WHERE `index`.`category_id` = `status`.`entity_id` GROUP BY `index`.`category_id`) > 0, 1, 0)
WHERE `status`.`store_id` = 0
2016   category   product   sql
Earlier Ctrl + ↓