obukhow’s quick tips

Run partial reindex process (mview)

Denis Obukhov — Thu, 30 Jun 2022 09:14:46 +0300

If you want to force run a partial reindex process, also called mview or materialized view to update only entries collected in a change log table (suffixed with _cl) you can use following command. First you need to find out desired process code with

bin/magento indexer:info

command. And use it for the

$processCode

variable value.

$processCode = 'visual_merchandiser_category';
$collection = $objectManaget->get(\Magento\Framework\Mview\View\Collection::class);
$item = $collection->getItemsByColumnValue('view_id', $processCode)[0];
$item->update();

Mark index as invalid or invalidate index in Magento 2

Denis Obukhov — Thu, 12 Aug 2021 14:58:42 +0300

To programmatically mark on of the indexes as invalid you need help of

\Magento\Framework\Indexer\IndexerRegistry

. After receiving the indexer instance, you can call

invalidate()

method.

Example

$this->indexerRegistry->get(\Magento\CatalogSearch\Model\Indexer\Fulltext::INDEXER_ID)->invalidate();

Undo Order Cancel in Magento

Denis Obukhov — Tue, 20 Aug 2019 09:33:42 +0300

If you want to restore canceled order, rollback order cancellation and undo order cancel action in Magento and Magento 2 use the following SQL query. Order will be restored to “Pending” status.

SET @ORDERID = "41970";
UPDATE `sales_order`
SET `state` = 'new',
`status` = 'pending',
`base_discount_canceled` = '0',
`base_shipping_canceled` = '0',
`base_subtotal_canceled` = '0',
`base_tax_canceled` = '0',
`base_total_canceled` = '0',
`discount_canceled` = '0',
`shipping_canceled` = '0',
`subtotal_canceled` = '0',
`tax_canceled` = '0',
`total_canceled` = '0'
WHERE `entity_id` = @ORDERID;
UPDATE `sales_order_item`
SET `qty_canceled` = '0',
`tax_canceled` = '0',
`discount_tax_compensation_canceled` = '0'
WHERE (`order_id` = @ORDERID);
DELETE FROM `sales_payment_transaction`
WHERE (`order_id` = @ORDERID) and `txn_type` = 'void';
UPDATE `sales_payment_transaction`
SET `is_closed` = 0
WHERE (`order_id` = @ORDERID) and `txn_type` = 'authorization';

Find file by name in Linux, macOS in command line

Denis Obukhov — Fri, 02 Aug 2019 10:20:36 +0300

To find a file by name in Linux (Ubuntu, Centos) or macOS in command line use the following command

find /home/username/ -name "*.inc"

Find Products Without Images

Denis Obukhov — Fri, 26 Apr 2019 13:56:15 +0300

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

Find file containing specific text on Linux, Mac OS X using command line

Denis Obukhov — Wed, 13 Feb 2019 12:26:59 +0300

grep -Ril "text-to-find-here" /

Clear redis cache

Denis Obukhov — Mon, 04 Feb 2019 15:37:54 +0300

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

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

Generate Self-signed SSL Certificate for nginx

Denis Obukhov — Fri, 18 Jan 2019 16:12:15 +0300

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

Now you can change your nginx config to use these certificates. Add the following section

server {
    listen               443 ssl;
    ssl_certificate      /etc/ssl/certs/mylocal.crt;
    ssl_certificate_key  /etc/ssl/private/mylocal.key;
    server_name mylocal.local *.mylocal.local;
    set $MAGE_ROOT /projects/mylocal;
    include /projects/mylocal/nginx.conf.sample;
}

Get locked transactions in MySQL

Denis Obukhov — Fri, 07 Dec 2018 12:58:17 +0300

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;

Mac OS email sending log

Denis Obukhov — Fri, 07 Dec 2018 12:56:47 +0300

log stream --predicate  '(process == "smtpd") || (process == "smtp")' --info

Log Debug Backtrace in Magento 2

Denis Obukhov — Fri, 02 Nov 2018 09:58:05 +0300

$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(true, false, true), true));

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

Delete product duplicate images

Denis Obukhov — Thu, 25 Feb 2016 15:14:20 +0300

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.

Hide all categories without products and show categories with products

Denis Obukhov — Sat, 20 Feb 2016 14:23:48 +0300

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

Check Product Is New

Denis Obukhov — Sun, 20 Sep 2015 21:29:20 +0300

Magento has two attributes “Set Product As New From Date” and “Set Product As New To Date” to set a product as new. If both fields are empty the product is not new. If only “Set Product As New To Date” is set, the product will be marked as “new” from current time until “Set Product As New To Date”. And If you set only “Set Product As New From Date” attribute the product will be marked as new from that date until forever. But how to check is product “new” currently?

Unfortunately Magento doesn’t have special method for this. But in Mage_Core_Model_Locale model there is a method called isStoreDateInInterval($store, $dateFrom, $dateTo) which checks is current date in two passed dates interval. But we need additional validation two dates are not empty, otherwise that method will return true. So we can create such method in some helper class:

<?php
public function isProductNew(Mage_Catalog_Model_Product $product)
{
    $newsFromDate = $product->getNewsFromDate();
    $newsToDate   = $product->getNewsToDate();
    if (!$newsFromDate && !$newsToDate) {
        return false;
    }
    return Mage::app()->getLocale()
            ->isStoreDateInInterval($product->getStoreId(), $newsFromDate, $newsToDate);
}

Add Admin User Via MySQL

Denis Obukhov — Sun, 20 Sep 2015 16:34:51 +0300

If you don’t have access to store’s admin panel, but you have access to a database, you can easily add new Magento admin panel user via MySQL script.

Before you launch the next script, please, be sure your password is 8 symbols length and has at least 1 digit.

LOCK TABLES `admin_role` WRITE , `admin_user` WRITE;
 
SET @SALT = "mr";
SET @PASS = CONCAT(MD5(CONCAT( @SALT , "password") ), CONCAT(":", @SALT ));
SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL;
 
INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) 
VALUES ('First name','Last name','email@example.com','username',@PASS,NOW(),0,0,1,@EXTRA,NOW());
 
INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name) 
VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'username'),'First name');
 
UNLOCK TABLES;

Make Simple Products of Configurable “Not Visible Individually”

Denis Obukhov — Fri, 18 Sep 2015 22:16:50 +0300

If you wan’t automatically make all simple products which are configurable options of configurable products to be hidden in catalog, you have to change their visibility to “Not Visible Individually”.

Next SQL-query will help you with that task.

UPDATE
`catalog_product_entity_int`
SET 
`value` = 1
WHERE
`attribute_id`=(SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'visibility')
AND
`entity_id` IN (SELECT `product_id` FROM `catalog_product_super_link`);

Change Customers Email Domains

Denis Obukhov — Fri, 18 Sep 2015 18:46:17 +0300

Sometimes you need to work with a production’s DB dump on a staging or on a dev server. To avoid emails be sent to real customers you should replace their email addresses with safe values. We will use Mailinator service which receives all emails and allows to access them if you know full email address.

Just execute this SQL-query in your DB

UPDATE `customer_entity`
SET `email` = CONCAT(
SUBSTRING(`email`, 1, locate('@', `email`)),
'mailinator.com'
);

If you need to left your organization domain email addresses unchanged, just add such condition:

UPDATE `customer_entity`
SET `email` = CONCAT(
SUBSTRING(`email`, 1, locate('@', `email`)),
'mailinator.com'
)
WHERE  SUBSTRING(`email`, locate('@', `email`) + 1) != 'my-organization.com';

Change Magento Product Images File Type

Denis Obukhov — Fri, 18 Sep 2015 14:06:01 +0300

For instance, we need to convert all .gif to .jpg

Execute next commands in terminal

> cd /vaw/www/media/catalog/product/
> for i in $(find $(pwd) -name \*.gif -print); do convert $i $(echo $i.jpg | sed s/.gif//g); done
> rm -r cache/

Then, run these SQL-statements in DB

UPDATE `catalog_product_entity_varchar`
SET `value` = REPLACE(`value`, '.gif', '.jpg') 
WHERE `attribute_id` IN 
    (SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('image', 'small_image', 'thumbnail') AND `entity_type_id` = 4
    )
; 
UPDATE `catalog_product_entity_media_gallery`
SET `value` = REPLACE(`value`, '.gif', '.jpg');