array_diff in excel – finding missing values from column B that exist in column A

Fill column A with bigger sample space, aka the longer array.

Fill column B with smaller array, that has few or none of the elements from column A

Now, you need to find out values that are in column A but not in column B, here is what you do.
Create a separate column C and put this formula in cell C1 or $C$1

=IF(ISERROR(MATCH(A1, $B$1:$C$<LAST_ROWNUMBER_THAT_EXISTS_IN_COLUMN_B>, 0)), A1, "")

Ultimately your formula becomes match function dependable for whole column A. You just have to drag the cell C1 downwards.

How to temporarily bypass content security policy CSP headers

Browser: Firefox
Extension: Mod Headers

https://addons.mozilla.org/en-US/firefox/addon/modheader-firefox/

Add A CSP policy like this:
Use “parse” option and paste this:

default-src *; script-src 'unsafe-inline' 'unsafe-eval' *; style-src 'unsafe-inline' *; img-src * blob: data:; font-src * data:;

To create a Content Security Policy (CSP) header that allows everything, you would use the 'unsafe-inline' and 'unsafe-eval' sources for scripts and styles, and the * wildcard to allow all other sources. This setup is generally not recommended for production environments because it exposes your site to potential security vulnerabilities. However, it can be useful for development or testing purposes. Here’s an example of what this CSP header might look like:

Content-Security-Policy: default-src *; script-src 'unsafe-inline' 'unsafe-eval' *; style-src 'unsafe-inline' *; img-src * blob: data:; font-src * data:;

This header includes:

  • default-src *;: Allows all sources for all types of content except those that have been explicitly specified by other directives.
  • script-src 'unsafe-inline' 'unsafe-eval' *;: Allows all scripts, inline scripts, and usage of eval().
  • style-src 'unsafe-inline' *;: Allows all styles and inline styles.
  • img-src * data:;: Allows all image sources and data URIs.
  • font-src * data:;: Allows all font sources and data URIs.

Remember, using this policy removes a significant part of the CSP’s protection. It is like not having a CSP at all because it allows resources to be loaded from anywhere, inline scripts and styles to be executed, and eval() to be used, which can lead to security vulnerabilities such as Cross-Site Scripting (XSS) attacks. Always aim to restrict your CSP to the minimum necessary sources for your application to function.

How to speed up slow Magento indexer process? Maria DB 10.4

Category Products index taking too much time?

bin/magento index:reset

bin/magento index:reindex

Stuck at category products index …

Here is the solution if you are using MariaDB 10.4

You need to update options/parameters for your db. If using RDS, edit “DB instance parameter group” that you are using and update these 2 values:

optimizer_switch='rowid_filter=off'
optimizer_use_condition_selectivity = 1



After these changes, restart your database. In aws, RDS automatically updates these parameters without any downtime or requirement for restarting. You will see noticeable difference in indexer time.

Reference:

https://devdocs.magento.com/guides/v2.4/performance-best-practices/configuration.html

https://devdocs.magento.com/guides/v2.4/extension-dev-guide/indexer-batch.html

https://magento.stackexchange.com/a/336816/32283

Windows 10 WSL Ubuntu Common Commands and hacks

In C:\Windows\System32\drivers\etc\hosts

127.0.0.1 test.tld
::1 test.tld
#::1 is really important sometimes
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WinSock2\Parameters\AppId_Catalog08F7A3]
"AppFullPath"="C:\\Windows\\System32\\wsl.exe"
"PermittedLspCategories"=dword:80000000

Save the above as a .reg file and run it to update registry

Powershell script=> save as .ps1 file

If (-NOT ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) {   
  $arguments = "& '" + $myinvocation.mycommand.definition + "'"
  Start-Process powershell -Verb runAs -ArgumentList $arguments
  Break
}

$remoteport = bash.exe -c "ifconfig eth0 | grep 'inet '"
$found = $remoteport -match '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}';

if( $found ){
  $remoteport = $matches[0];
} else{
  echo "The Script Exited, the ip address of WSL 2 cannot be found";
  exit;
}

#[Ports]

#All the ports you want to forward separated by coma
$ports=@(80,443,3306);


#[Static ip]
#You can change the addr to your ip config to listen to a specific address
$addr='0.0.0.0';
$ports_a = $ports -join ",";


#Remove Firewall Exception Rules
iex "Remove-NetFireWallRule -DisplayName 'WSL 2 Firewall Unlock' ";

#adding Exception Rules for inbound and outbound Rules
iex "New-NetFireWallRule -DisplayName 'WSL 2 Firewall Unlock' -Direction Outbound -LocalPort $ports_a -Action Allow -Protocol TCP";
iex "New-NetFireWallRule -DisplayName 'WSL 2 Firewall Unlock' -Direction Inbound -LocalPort $ports_a -Action Allow -Protocol TCP";

for( $i = 0; $i -lt $ports.length; $i++ ){
  $port = $ports[$i];
  iex "netsh interface portproxy delete v4tov4 listenport=$port listenaddress=$addr";
  iex "netsh interface portproxy add v4tov4 listenport=$port listenaddress=$addr connectport=$port connectaddress=$remoteport";
}


netsh interface portproxy show v4tov4

check if your proxy is working

Most of the times you may not even need a proxy for apache2. However mysql inside WSL is extremely tricky for some reason. I have ALWAYS had issues until I changed the default port. Hence try this in your file:

sudo nano /etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
explicit_defaults_for_timestamp=true
bind-address = 0.0.0.0
user=root
pid-file     = /var/run/mysqld/mysqld.pid
socket       = /var/run/mysqld/mysqld.sock
port         = 3334

Now you should be able to connect to this mysql instance from some gui app on windows too like dbeaver with localhost on port 3334.

If you are victim of unpredictive WSL behavior, do leave a comment

Condition types for addAttributeToFilter in Magento2 Collections

'eq'            => "{{column}} = ?",
'finset'        => "FIND_IN_SET(?, {{column}})",
'from'          => "{{column}} >= ?",
'gt'            => "{{column}} > ?",
'gteq'          => "{{column}} >= ?",
'in'            => "{{column}} IN(?)",
'is'            => "{{column}} IS ?",
'like'          => "{{column}} LIKE ?",
'lt'            => "{{column}} < ?",
'lteq'          => "{{column}} <= ?",
'neq'           => "{{column}} != ?",
'nin'           => "{{column}} NOT IN(?)",
'nlike'         => "{{column}} NOT LIKE ?",
'notnull'       => "{{column}} IS NOT NULL",
'ntoa'          => "INET_NTOA({{column}}) LIKE ?",
'null'          => "{{column}} IS NULL",
'regexp'        => "{{column}} REGEXP ?",
'seq'           => null,
'sneq'          => null,
'to'            => "{{column}} <= ?"

How to fix CORS error for amazon s3 files and resources?

Go to bucket

In the bucket permissions tab

There is a section for CORS, put the following in it and save.

[
    {
        "AllowedOrigins": [
            "*"
        ],
        "AllowedMethods": [
            "GET"
        ],
        "MaxAgeSeconds": 3000,
        "ExposeHeaders": [
             "Content-Range",
             "Content-Length",
             "ETag"
        ],
        "AllowedHeaders": [
            "Authorization",
            "Content-Range",
            "Accept",
            "Content-Type",
            "Origin",
            "Range"
        ]
    }
]

centos web panel – vhost templates for proxying websocket traffic

http://wiki.centos-webpanel.com/webservers-vhost-templates

Templates location: /usr/local/cwpsrv/htdocs/resources/conf/web_servers/
– Folder main contains main configuration for WebServers
– Folder vhosts contains vhosts configuration for domains (this is what you need)

How to create a template file
The easiest way to do that is to simply copy an existing file and then edit a new file you have created.
Note that you need to have .tpl and .stpl files (tpl is for http and stpl is for https version)

Let’s do one example for apache vhost with nginx

cd /usr/local/cwpsrv/htdocs/resources/conf/web_servers/vhosts/nginx/
cp default.tpl my-template.tpl
cp default.stpl my-template.stpl

Now you can edit these newly created template files.

Template folders explained
List from folder: /usr/local/cwpsrv/htdocs/resources/conf/web_servers/vhosts/

httpd = apache templates
nginx = nginx templates
varnish = varnish templates
php-fpm = php-fpm service templates (used for all php-fpm versions)

add a block

map $http_upgrade $connection_upgrade {
	default upgrade;
	'' close;
}

also add these inside “location /” block

		proxy_http_version 1.1;
		proxy_set_header Upgrade $http_upgrade;
		proxy_set_header Connection $connection_upgrade;
		proxy_set_header Host $host;

basic cwp with jenkins and nodejs

hostname srv1.example.com
yum -y install wget
yum -y update
reboot


cd /usr/local/src
wget http://centos-webpanel.com/cwp-el7-latest
sh cwp-el7-latest -r no --phpfpm 7.3 --softaculous no



reboot



sudo wget -O /etc/yum.repos.d/jenkins.repo \
    https://pkg.jenkins.io/redhat-stable/jenkins.repo
sudo rpm --import https://pkg.jenkins.io/redhat-stable/jenkins.io.key
sudo yum upgrade
sudo yum install epel-release java-11-openjdk-devel
sudo yum install jenkins
sudo systemctl daemon-reload
curl -sL https://rpm.nodesource.com/setup_12.x | bash -
sudo yum install nodejs npm -y
sudo npm install pm2@latest -g