magento2 – How to delete specific product images from Magento 2 database and physical images from folder?

Deleting selected product / SKU images from Magento 2 database and the physical images files from the media product catalog folders which are dynamically created by Magento during image upload.

  1. Locate the image and image physical path. In the database, the images and the physical path reference of the images are stores in the below 3 tables
  • catalog_product_entity_media_gallery
    //This is where the physical
    image path is stored
  • catalog_product_entity_media_gallery_value
    //This table has the storeid, valueid, product entity id and the
    position of the images
  • catalog_product_entity_media_gallery_value_to_entity //This table is
    where the mapping between the product entity id and valueid mapping
    done.
  • And the images are stored physically under magento2rootpath/pub/media/catalog/product/x/x/x
  1. First step is to identify the physical path of the server and delete from the media –> catalog –> product path, I used the below query to create a file with remove command and the image physical path on the server.

This example was executed on Ubuntu 16.04, the below commands and sql querie. Will run on any Linux distro and Mysql.

Basically create a file with the below command to delete the images,

run the below query on the command prompt.

 - mysql -u username -p databasename -e "select concat('rm -f
   /home/magento2rootpath/pub/media/catalog/product',value) from
   catalog_product_entity_media_gallery  where value_id in (select
   value_id from catalog_product_entity_media_gallery_value_to_entity
   where entity_id in (select entity_id from catalog_product_entity
   where sku in ('SKU1','SKU2','SKU3'))) and attribute_id=90;" >
   imagefilestodelete.sh

In my case, Magento is installed in folder /home/magento2rootpath – replace with your actual magento root path and replace SKU1, SKU2, SKU3 with your actual product SKU

Output of the generated file imagefilestodelete.sh

rm -f /home/magento2rootpath/pub/media/catalog/product/4/m/4m6a6330.jpg
rm -f /home/magento2rootpath/pub/media/catalog/product/4/m/4m6a6332.jpg
rm -f /home/magento2rootpath/pub/media/catalog/product/4/m/4m6a6333.jpg
rm -f /home/magento2rootpath/pub/media/catalog/product/4/m/4m6a6335.jpg

From the CLI run the below command

sh imagefilestodelete.sh

Now that we have deleted the physical image files from the media catalog respective image path folder, we will now need to remove the same from database, I used the below sql queries in the same order to remove the file references from database.

  1. Remove the image references from the respective tables in Magento

Login to your mysql with user having privilege to execute delete
queries

 - delete from catalog_product_entity_media_gallery where value_id in
   (select value_id from catalog_product_entity_media_gallery_value_to_entity where entity_id
   in (select entity_id from catalog_product_entity where sku in
   ('SKU1','SKU2','SKU3'))) and attribute_id=90;

 - delete from catalog_product_entity_media_gallery_value_to_entity
   where entity_id in (select entity_id from catalog_product_entity
   where sku in ('SKU1','SKU2','SKU3'));

 - delete from catalog_product_entity_media_gallery_value where
   entity_id in (select entity_id from catalog_product_entity where sku
   in ('SKU1','SKU2','SKU3'));

NOTE: Before you execute the above delete queries, run SELECT to check the entries. To do that replace delete from above queries to SELECT *

This way it was easier for me to delete images for specific products on production and not truncate the entire tables.

sharepoint online – Index all files in specific folders

In SharePoint online, I have a document library that has one folder for each brand and a number of sub-folders under each of the brand folder. How do I index all files in all the sub-folders of a particular brand folder?

Example:
I have a column [Product status], Can I index everything in Brand A ‘s sub-folders as “launched” while leaving Brand B’s as “developing“? I want to do it without the need to go to every single sub-folders to make this change. I tried the “no folder view”, but that would give me files for both Brand A and Brand B.
Libary folder hierarchy

Appreciate your help!!

webapp rec – How to create an application that runs 24/7, and do some tasks at some specific times?

I want to create a (web)application that always runs in the background. At certain specific times, the application has to perform tasks.

Example: An application that displays a number and is set to 0 every first day of the month. The number increases by 1 every day at 8:00 PM. Furthermore, the number will be increased by 10 on the 7th, 14th, 21st, and 28th day of the month at 3:00 AM.

The app must therefore always be on (as in: the app must always ‘wait / listen’ for times to perform tasks, and execute them when the time comes). These tasks are on specific days of the month, at specific times of the day. In the actual application, the tasks will use a MySQL or SQLite database.

My question: Is this realistic? And if so: Which software / programming language can I use best for this? My preference would be in Anroid Studio, with the programming language Java.

azure – Sentiment analysis for speech in MS Teams meetings (classrooms to be more specific)

I’m looking for a solution that could be used with MS Teams for Education that could be used for sentiment analysis in real time during classroom discussions in synchronous on-line learning. I assume Azure’s Speech to Text could capture what is said but not sure what tools are available for the analysis.
Comments by individual students are not as important as gauging the overall sentiment in the classroom and ideally it would go past simply positive/negative to detecting mood, words or phrases that are being used frequently, lapses between questions and answers, etc.
Ideally the data could then be fed into Power BI for providing real-time indicators of what is happening in the class.

Any suggestions?

list manipulation – Finding points located on a specific locations of a 3D objects

I have the following list of points in 3D and would like to find the points which are located on the lower edge of the object and on (or closest distance to) the X-Y or Y-Z plane of the symmetry. (Please see yellow points in fig 1). What I did was generating a point which has the X average with Y and Z min and then finding a point of the list which has the closest distance to the generated point. However, it was not correct as the generated point was not a member of the list point so the closest point was sometimes in the front and sometimes on the back of the object for different lists of points.

https://pastebin.com/L12n0Gfr

fig. 1

How to send BTC amount from one specific address in Bitcoin Core

I canceled one transaction that was in the memory pool, now I would like to make sure that this transaction will not be sent anymore sending all BTC amount of the original address of this transaction to another address, but when I go to send BTC in Bitcoin Core, I can’t choose one specific address to send the amount. So what Can I do to “empty” the bitcoins of this address using Bitcoin Core?

Deactivate a list of SKUs for a two specific websites?

How can I quickly deactivate a list of SKUs for a specific website?

Can’t block specific IP address with iptables, Ubuntu 16.04

I’m trying to troubleshoot Fail2ban recognizing our http-get-dos trigger, but not actually banning the offending host. I can see it adding entries to iptables rules, but they don’t have any affect.

So I tried to manually ban a host, and I cant get that to work either.

I’m trying to drop all traffic from a specific host. I inserted a DROP rule, and appended a DROP rule, so they bookend everything. I’ve X’ed out the ip address of the host I’m trying to ban. This host can still connect to my web server, and get web pages.

I must be missing something stupid. Here’s the iptables -nvL output:

sudo iptables -nvL

Chain INPUT (policy ACCEPT 6226 packets, 31M bytes)
 pkts bytes target     prot opt in     out     source               destination
    0     0 DROP       all  --  *      *       XX.XX.XX.XX         0.0.0.0/0
85222 7764K fail2ban-xmlrpc  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:80
85222 7764K fail2ban-HTTP  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:80
85222 7764K fail2ban-HTTP  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:80
 137K   16M fail2ban-BadBots  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            multiport dports 80,443
   82  7136 fail2ban-sasl  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            multiport dports 25,465,587,143,220,993,110,995
   82  7136 fail2ban-postfix  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            multiport dports 25,465,587
 137K   16M fail2ban-apache-overflows  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            multiport dports 80,443
 137K   16M fail2ban-apache-noscript  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            multiport dports 80,443
16620 1181K fail2ban-ssh-ddos  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            multiport dports 22
16621 1181K fail2ban-ssh  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            multiport dports 22
    0     0 DROP       all  --  *      *       XX.XX.XX.XX         0.0.0.0/0

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination

Chain OUTPUT (policy ACCEPT 6649 packets, 5149K bytes)
 pkts bytes target     prot opt in     out     source               destination

Chain fail2ban-BadBots (1 references)
 pkts bytes target     prot opt in     out     source               destination
 137K   16M RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-HTTP (2 references)
 pkts bytes target     prot opt in     out     source               destination
 170K   16M RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0
    0     0 RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-apache-noscript (1 references)
 pkts bytes target     prot opt in     out     source               destination
 137K   16M RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-apache-overflows (1 references)
 pkts bytes target     prot opt in     out     source               destination
 137K   16M RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-postfix (1 references)
 pkts bytes target     prot opt in     out     source               destination
   82  7136 RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-sasl (1 references)
 pkts bytes target     prot opt in     out     source               destination
   82  7136 RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-ssh (1 references)
 pkts bytes target     prot opt in     out     source               destination
16621 1181K RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-ssh-ddos (1 references)
 pkts bytes target     prot opt in     out     source               destination
16620 1181K RETURN     all  --  *      *       0.0.0.0/0            0.0.0.0/0

Chain fail2ban-xmlrpc (1 references)
 pkts bytes target     prot opt in     out     source               destination

and here’s the iptables -S output:

sudo iptables -S

-P INPUT ACCEPT
-P FORWARD ACCEPT
-P OUTPUT ACCEPT
-N fail2ban-BadBots
-N fail2ban-HTTP
-N fail2ban-apache-noscript
-N fail2ban-apache-overflows
-N fail2ban-postfix
-N fail2ban-sasl
-N fail2ban-ssh
-N fail2ban-ssh-ddos
-N fail2ban-xmlrpc
-A INPUT -s XX.XX.XX.XX/32 -j DROP
-A INPUT -p tcp -m tcp --dport 80 -j fail2ban-xmlrpc
-A INPUT -p tcp -m tcp --dport 80 -j fail2ban-HTTP
-A INPUT -p tcp -m tcp --dport 80 -j fail2ban-HTTP
-A INPUT -p tcp -m multiport --dports 80,443 -j fail2ban-BadBots
-A INPUT -p tcp -m multiport --dports 25,465,587,143,220,993,110,995 -j fail2ban-sasl
-A INPUT -p tcp -m multiport --dports 25,465,587 -j fail2ban-postfix
-A INPUT -p tcp -m multiport --dports 80,443 -j fail2ban-apache-overflows
-A INPUT -p tcp -m multiport --dports 80,443 -j fail2ban-apache-noscript
-A INPUT -p tcp -m multiport --dports 22 -j fail2ban-ssh-ddos
-A INPUT -p tcp -m multiport --dports 22 -j fail2ban-ssh
-A INPUT -s XX.XX.XX.XX/32 -j DROP
-A fail2ban-BadBots -j RETURN
-A fail2ban-HTTP -j RETURN
-A fail2ban-HTTP -j RETURN
-A fail2ban-apache-noscript -j RETURN
-A fail2ban-apache-overflows -j RETURN
-A fail2ban-postfix -j RETURN
-A fail2ban-sasl -j RETURN
-A fail2ban-ssh -j RETURN
-A fail2ban-ssh-ddos -j RETURN
-A fail2ban-xmlrpc -j RETURN

api – How can i implement user specific calendars in a webapp?

I’m creating a webapp for the management of sportevents and sportclubs in angular.
One of the features is a calendar with events, todos and working shifts. Let’s call them events.
Users have their own events and events assigned to them via groups. Every user could be in a group that has group-level events.

I want the users to be able to see those events in a calendar on the webapp and also to subscribe as internet calendar on their desired calendar app (e.g. Outlook, Spark, etc…).

I’ve been searching for calendar APIs, calendar services, functionalities of the .ical format and I’ve been looking in to the google calendar and google calendar API. But none of them are covering my needs.

How can I implement my calendar? Are there tools for that? Is it possible to develop this thing on my own?