Skip to content

Instantly share code, notes, and snippets.

Joost van Veen joostvanveen

Block or report user

Report or block joostvanveen

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@joostvanveen
joostvanveen / mysql_alter_charset_and_collation_to_utf8.sql
Created May 29, 2019
Alter the charset and collation to UTF8 for a database and a table. Also inspects the default charset and collation for the entire database.
View mysql_alter_charset_and_collation_to_utf8.sql
-- Inspect the default charset and collation for your database
SELECT default_character_set_name FROM information_schema.schemata WHERE schema_name = 'MYDATABASE_NAME';
-- Alter the default charset and collation for your database
-- CAREFUL: when you change the charset for your database, make sure the data in your tables matches that character set.
-- If it is not, follow these steps: http://www.alphadevx.com/a/420-Converting-a-MySQL-database-from-latin1-to-utf8
ALTER DATABASE MYDATABASE_NAME CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Alter the default charset and collation for your database
ALTER TABLE MYTABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
@joostvanveen
joostvanveen / top-20-user-agents-and-ips-from-directadmin-logs.sh
Created Feb 17, 2019
Loop though all rotated access logs and get the top 20 list of User AgentsThis can be used to identify bots
View top-20-user-agents-and-ips-from-directadmin-logs.sh
# Loop though all rotated Directadmin access logs and get the top 20 list of User Agents
# This can be used to identify bots
zcat /home/USER/domains/DOMAIN/logs/LOGNAME.tar.gz* | awk -F\" '{print $6}' | sort | uniq -c | sort -nr | head -20
# Get top 20 IP addresses
zcat /home/USER/domains/DOMAIN/logs/Feb-2019.tar.gz | awk -F\" '{print $1}' | sort | uniq -c | sort -nr | head -20
# Get top 20 most visited URLs
zcat /home/USER/domains/DOMAIN/logs/Feb-2019.tar.gz | awk -F\" '{print $4}' | sort | uniq -c | sort -nr | head -20
@joostvanveen
joostvanveen / delete_files_continaing_a_string.sh
Last active Jun 26, 2018
Delete log files containing a certain string. Handy to clean up Magento reports after committing a fix.
View delete_files_continaing_a_string.sh
# Delete log files containing a certain string.
# Handy to clean up Magento reports after committing a fix.
find var/report/* -exec grep -q 'A non-numeric value encountered' '{}' \; -delete
# If you ant to chcek first:
find var/report/* -exec grep -q 'A non-numeric value encountered' '{}' \; -find
@joostvanveen
joostvanveen / clean_magento_product_images.sh
Created Apr 25, 2018
Clean old product images cache and orphaned product images for Magento 1
View clean_magento_product_images.sh
# Delete all cached images older than 365 days
find ~/public/media/catalog/product/cache/* -type f -atime +365 -exec rm {} \;
# Remove product images that are not present in the database
magerun media:images:removeorphans
@joostvanveen
joostvanveen / count_visitors_by_ip.sh
Last active Mar 22, 2018
Display the top X visitors by IP address from a single or all nginx access log files
View count_visitors_by_ip.sh
# Display the top 20 visitors by IP address from a single log file
awk '{print $2}' /var/log/nginx/access.log | sort | uniq -c | sort -nr | head -20
# Display the top 40 visitors by IP address from all rotated gzipped log files
zcat /var/log/nginx/access.log* | awk '{print $2}' | sort | uniq -c | sort -nr | head -40
# Find IP adrdresses for all users that created a custoter account for Magento
zcat /var/log/nginx/access.log* | awk -v OFS='\t' '/\/customer\/account\/createpost?/ {print $2, $21}' | sort | uniq -c | sort -nr | head -40
@joostvanveen
joostvanveen / csplit.sh
Created Mar 9, 2018
Split large SQL files into separate files for each table
View csplit.sh
## Split large SQL files into separate files for each table if every tabel starts with a 'DROP TABLE IF EXISTS' statement
csplit -k $PWD/filename.sql '/^DROP TABLE IF EXISTS .*/' '{900}'
## Split large SQL files into separate files for each table if every tabel starts with a 'CREATE TABLE' statement
csplit -k $PWD/filename.sql '/^CREATE TABLE .*/' '{900}'
View remove-table-prefixes.sql
-- STEP 1: Set @database, @old_prefix and @new_prefix
-- STEP 2: Execute the query
-- STEP 3: Copy the string that was genereated by the query
-- STEP 4: Run the generated query to rename all tables
-- STEP 5: Use you Party Gun to make it rain!
SET SESSION group_concat_max_len = 999999999;
SET @database = "databasename";
SET @old_prefix = "ma2_";
SET @new_prefix = "";
@joostvanveen
joostvanveen / linux_find_size_modified_date_and_remove_files.sh
Last active Oct 21, 2019
Linux commands to find all files modified older than a certain date or time, remove, find largest files or folder in a human readable way.
View linux_find_size_modified_date_and_remove_files.sh
## NOTE: when finding file by time, use one of the following flags:
# ctime : the time a file was changed, in hours. On most systems this timestamp cannot be altered by any user, so it is fairly reliable
# atime : the time a file was last accessed, in hours. This is also set by the system.
# mtime : the modified time of a file, in hours. This can be set by any user. So if you are looking for mailcious files, never use this flag.
# cmin : same as mtime, but in minutes.
# amin : same as atime, but in minutes.
# mmin : same as mtime, but in minutes.
# Find all files in current directory changed more than 8 hours ago (480 minutes)
find $PWD -mindepth 1 -type f -cmin +480
@joostvanveen
joostvanveen / rsync.sh
Created Dec 13, 2017
Crude bash script to Rsync source folder to destination folder, only certain folders and files
View rsync.sh
#!/usr/bin/env bash
# Magento 2 Deployment Script
# Author: Joost van Veen
usage="$(basename "$0") [-h] source-directory destination-directory folders-to-sync files-to-sync
Rsync folders and files from one folder to another
where:
-h show this help text
@joostvanveen
joostvanveen / magento_2_unrequire_firstname_lastname_etc.sql
Created Nov 3, 2017
Set firstname, lastname, email, telephone as unrequired for Magento 2
View magento_2_unrequire_firstname_lastname_etc.sql
-- DEFAULT MAGENTO: REQUIRE FIRSTNAME, LASTNAME, EMAIL, TELEPHONE
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'firstname';
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'lastname';
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'email';
UPDATE `ma2_eav_attribute` SET is_required = 1 WHERE attribute_code = 'telephone';
-- DO NOT REQUIRE FIRSTNAME, LASTNAME, EMAIL, TELEPHONE
UPDATE `ma2_eav_attribute` SET is_required = 0 WHERE attribute_code = 'firstname';
UPDATE `ma2_eav_attribute` SET is_required = 0 WHERE attribute_code = 'lastname';
UPDATE `ma2_eav_attribute` SET is_required = 0 WHERE attribute_code = 'email';
You can’t perform that action at this time.