obukhow’s quick tips

Some code recipes and useful scripts.

Run partial reindex process (mview)

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

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

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 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);
 No comments   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).

Generate Self-signed SSL Certificate for nginx

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

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;
 No comments   2018   mysql
Earlier Ctrl + ↓