Category: Programming

Days of the week array

This snippet will give you the days of the week but human-readable in your own language.

today=$(date +%u)
yesterday=$((today-1))

dagen=(
        'zondag'
        'maandag'
        'dinsdag'
        'woensdag'
        'donderdag'
        'vrijdag'
        'zaterdag'
        'zondag'
      )

today=${dagen[$vandaag]}
yesterday=${dagen[$gisteren]}

Now you can use $today and $yesterday as variables in folder names, usefull for back-ups.

MySQL automatic restore script

We had a .sql export from a MySQL databaseserver from all the databases. In our case the filenames were made up like:

mysqldump-hostname-user_database-D5-22H.sql

So I wrote a little bash script to recreate all the databases and import it’s data:

#!/bin/bash

folder=/var/sqlbackup/

for file in `ls $folder/mysqldump-*.sql`
do
echo $file
IFS='-' read -r -a array <<< "$file"
echo ${array[2]}
echo "create database ${array[2]}" | mysql -u da_admin -ppassword
mysql -u da_admin -pnAcQKhIK ${array[2]} < $file

done

After a little while all databases were full with content again 🙂

Nginx configuration for WordPress websites

If you are using the WP Super Cache plugin by Automattic, this configuration can help you settings up your Nginx caching proxy:

[code]proxy_cache_path /data/nginx/cache keys_zone=one:10m inactive=24h max_size=1G;
proxy_cache_key “$host$request_uri$cookie_user”;
proxy_cache one;

server {
server_name ~^(static\.)?(?.+)$;
access_log /var/log/nginx/domains/static.$domain.log;

location ~ ^/(wp-content|wp-includes)/ {
resolver 8.8.8.8;
expires 30d;
add_header Pragma public;
add_header Cache-Control “public”;
proxy_cache_valid 200 10m;
proxy_pass http://www.$domain;
add_header X-Cache-Status $upstream_cache_status;
}
}[/code]

Change the static.domain.com to the hostname the nginx server is listening to. Change domain.com (twice!) to the main domain where the content is pulled from.

Make a DNS record (either cname or a) pointing static.domain.com to the IP the nginx server is running at.

Be sure to chmod the /data/nginx/cache folder to 700 and change the ownership to the same user as nginx is running (default nginx).

Check if the cache is working by using this command:

[code]du -h –max-depth=1 /data/nginx/cache[/code]

Easy as that!

Removing bbPress forum comments

In addition to my previous post about removing WooCommerce products, I just used the same snippet to remove 400k bbPress replies.

With this query you can remove all the replies:

DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='reply');

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'reply');
DELETE FROM wp_posts WHERE post_type = 'reply';

And use this to empty all bbPress topics:

DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='topic');

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'topic');
DELETE FROM wp_posts WHERE post_type = 'topic';

Worked like a charm! Now all bbPress topics and replies are deleted.

Removing WooCommerce products in WordPress

I found this handy SQL query to easily remove all the products and related stuff from a WordPress WooCommerce.

DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='product');

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'product');
DELETE FROM wp_posts WHERE post_type = 'product';

Source

WordPress update error: download failed

Sometimes WordPress can’t update itself or the plug-ins. This often happens after the installation has been moved or the username has been changed.

Downloading update from https://downloads.wordpress.org/release/wordpress-4.3.1-no-content.zip…

Download failed.: Destination directory for file streaming does not exist or is not writable.

Installation Failed

The solution is to fix the WP_TEMP_DIR in the wp-config.php. The full path looks like this on a DirectAdmin server:

define('WP_TEMP_DIR', '/home/username/domains/example.com/public_html/wp-content/uploads');

If the folder exists, and is writable for your user, WordPress will be able to update itself again.