How many unprocessed entities by indexer

helpers

export DB_NAME=$(grep [\']db[\'] -A 20 app/etc/env.php | grep dbname | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export MYSQL_HOST=$(grep [\']db[\'] -A 20 app/etc/env.php | grep host | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export DB_USER=$(grep [\']db[\'] -A 20 app/etc/env.php | grep username | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export MYSQL_PWD=$(grep [\']db[\'] -A 20 app/etc/env.php | grep password | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/[']$//" | sed "s/['][,]//");
mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -U
pager less -S;


#URLs
mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT * FROM core_config_data WHERE path = 'web/unsecure/base_url' OR path = 'web/secure/base_url'"

#DEBUG SQL
SET GLOBAL general_log = 1;
SET GLOBAL log_output = 'table';
SELECT * FROM mysql.general_log;
SET GLOBAL general_log = 0;
TRUNCATE mysql.general_log;

# Unproccessed entities


SELECT COUNT(*) AS version_count FROM catalog_product_category_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_category'
);
SELECT COUNT(*) AS version_count FROM catalog_category_flat_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalog_category_flat'
);
SELECT COUNT(*) AS version_count FROM catalog_product_flat_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_flat'
);
SELECT COUNT(*) AS version_count FROM catalog_category_product_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalog_category_product'
);
SELECT COUNT(*) AS version_count FROM catalog_product_category_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_category'
);
SELECT COUNT(*) AS version_count FROM catalog_product_price_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_price'
);
SELECT COUNT(*) AS version_count FROM catalog_product_attribute_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_attribute'
);
SELECT COUNT(*) AS version_count FROM catalogrule_rule_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalogrule_rule'
);
SELECT COUNT(*) AS version_count FROM catalogrule_product_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalogrule_product'
);
SELECT COUNT(*) AS version_count FROM targetrule_product_rule_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'targetrule_product_rule'
);
SELECT COUNT(*) AS version_count FROM targetrule_rule_product_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'targetrule_rule_product'
);
SELECT COUNT(*) AS version_count FROM salesrule_rule_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'salesrule_rule'
);
SELECT COUNT(*) AS version_count FROM cataloginventory_stock_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'cataloginventory_stock'
);
SELECT COUNT(*) AS version_count FROM catalogsearch_fulltext_cl WHERE version_id > ( 
	SELECT version_id FROM mview_state WHERE view_id = 'catalogsearch_fulltext'
);

# product with attribute
SELECT * FROM catalog_product_entity AS e
LEFT JOIN catalog_product_entity_varchar AS v ON v.row_id = e.row_id
LEFT JOIN eav_attribute AS eav ON eav.attribute_id = v.attribute_id
WHERE eav.attribute_code = 'url_key' AND eav.entity_type_id = 4;

# Finds conflicts product url keys with custom url rewrites

SELECT * FROM url_rewrite AS ur
INNER JOIN catalog_product_entity_varchar AS v ON v.value = LEFT (ur.request_path, LENGTH(ur.request_path)-5)
WHERE ur.entity_type = 'custom';

SELECT * FROM catalog_product_entity_varchar AS v
INNER JOIN catalog_product_entity_varchar AS v2 ON v.value_id <> v2.value_id AND v.value = TRIM(TRAILING '-' FROM v2.value )
LEFT JOIN catalog_product_entity AS e ON e.row_id = v2.row_id
WHERE v.attribute_id = 124 AND v2.attribute_id = 124;

# Cron Schedule
 SELECT job_code, status, created_at, scheduled_at, executed_at, finished_at, TIMEDIFF(finished_at, executed_at) AS duration 
 FROM cron_schedule ORDER BY schedule_id DESC;
 
  SELECT job_code, status, created_at, scheduled_at, executed_at, finished_at, TIMEDIFF(finished_at, executed_at) AS duration 
 FROM cron_schedule WHERE job_code LIKE "%index%" ORDER BY schedule_id DESC;
 
 SELECT job_code, status, COUNT(*) as count_status FROM cron_schedule GROUP BY job_code, status ORDER BY count_status DESC;
 
 for I in 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23; do echo -n "2019-06-17 $I - "; grep "\[2019-06-17 $I" /var/log/cron.log | wc -l; done
 
 #EVENTS
SELECT event_code, time, action FROM magento_logging_event WHERE event_code LIKE "%backup%";
 
 strace -t -o var/strace.log -s 2048 -f php pub/index.php
 php bin/magento cron:run --group=index --bootstrap=standaloneProcessStarted=1
 
 curl -O https://gist.githubusercontent.com/yvoronoy/bdc31ca9bb42898189b61ff668b7537a/raw/4021c250b1d56a093f292ee9a1b5928f3047ce8a/MAGENTO_ENABLE_MYSQL_DEBUG.patch
 patch -p0 < MAGENTO_ENABLE_MYSQL_DEBUG.patch
 
 #Elastic
 curl -XGET localhost:9200/_cat/indices
 curl -XGET localhost:9200/<index-name>/document/<id>
 curl -XGET localhost:9200/<index-name>/_search -d '
{
  "fields": ["_ids"],
  "size": 1000
}
' | json_pp | grep _id
curl -XGET localhost:9200/<index-name>/_search -d '
{
  "size": 1000
}
' | json_pp | less -S

# Get document by product ID
curl localhost:9200/magento2_product_7/_search -d '
{
  "query": {
    "match": {
      "_id": 753
    }
  }
}'



# Get all images for configurable product
SET @entity_id = 236279; #Configurable Id
SET @row_id = (SELECT row_id FROM catalog_product_entity WHERE entity_id = @entity_id);
SELECT DISTINCT g.value FROM catalog_product_entity_media_gallery_value gv
LEFT JOIN catalog_product_entity e ON e.row_id = gv.row_id
LEFT JOIN catalog_product_entity_media_gallery g ON g.value_id = gv.value_id
WHERE e.entity_id = @entity_id OR e.entity_id IN (SELECT child_id FROM catalog_product_relation WHERE parent_id = @row_id);


#debug
$data = array(
    __CLASS__ => time(),
);
ob_start();
var_dump($data);
$data = ob_get_clean();
$backtrace = \Magento\Framework\Debug::backtrace(true, false, true);
$message = sprintf("Data: %s %s", $data, $backtrace);
file_put_contents('/tmp/magento.support.debug.data.log', $message, FILE_APPEND);
#Debug end


###################
# Debug and catch values
###################
# vendor/magento/framework/Model/AbstractModel.php
# vendor/magento/framework/DataObject.php
# Method: setData()
if (is_scalar($key)) {
  $breakpoint = null; // is_scalar($value) && stripos($value, 'xxx') !== false
}
if (is_array($key)) {
  foreach ($key as $k => $v) {
    $breakpoint = null; // is_scalar($v) && stripos($v, 'xxx') !== false
  }
}

#######################
# Debug and catch SQL
#######################
vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php::query()
// stripos($sql, 'table-name') !== false




#Media
TMP_MEDIA_FILE=pub/media/catalog/product/p/d/yourmedia.jpg
magento@b62318b17328:/var/www/html/c$ find vendor/ -name "*.${TMP_MEDIA_FILE##*.}" | head -n 1 | xargs -t -I {} bash -c "mkdir -p `dirname $TMP_MEDIA_FILE`; cp {} $TMP_MEDIA_FILE"


#URL REWRITE
SET @default_store_id = (
	SELECT store.store_id as default_store_id FROM store_website 
	LEFT JOIN store_group ON store_group.website_id = store_website.website_id
	LEFT JOIN store ON store.store_id = store_group.default_store_id
	WHERE is_default = 1
);
SELECT @default_store_id;

SELECT v.store_id, v.entity_id, v.value, url.* FROM catalog_product_entity_varchar v
 INNER JOIN catalog_product_entity p ON p.entity_id = v.entity_id
 INNER JOIN eav_attribute eav ON eav.attribute_id = v.attribute_id AND eav.attribute_code = 'url_key'
 INNER JOIN eav_entity_type eav_type ON eav_type.entity_type_id = eav.entity_type_id AND eav_type.entity_type_code = 'catalog_product'
 INNER JOIN store ON store.store_id = v.store_id AND store.store_id = @default_store_id
 INNER JOIN store_website website ON website.website_id = store.website_id
 INNER JOIN catalog_product_website ON catalog_product_website.website_id = website.website_id AND v.entity_id = catalog_product_website.product_id
 LEFT JOIN url_rewrite url ON url.entity_id = v.entity_id
 WHERE url.request_path IS NULL
 ;
 
 
 #rdb redis report
 cat c/memory.csv | awk -F ',' '{print $3}' | sort -n | awk -F '_' '{print $2}' | sort -n | uniq -c | sort -k1 | tail
    206 SERVICEINTERFACEMETHODSMAP
    264 SERVICE
   1915 CONFIGURABLE
   2819 STRUCTURE
   2834 LAYOUT
   4109 COLLECTION
   5477 CATALOG
  15457 CAT
  16288 IMG
  36960 BLOCK