While trying to import a database dump on the homebrew version of MariaDB on OSX, I was getting the error

SQL ERROR [mysql4] Out of resources when opening file `./mydatabase/table.MYD´ (Errcode: 24 – Too many open files)

This is caused by the open_files_limit setting being too low. To check your open_files_limit in MariaDB run

SHOW GLOBAL VARIABLES LIKE 'open_files_limit';

For me this value was 256 – it should be much higher. Increasing the value isn’t as simple as it would seem, however, as the limit is actually coming from OSX’s maxfiles value. You can see this value by running

launchctl limit

cpu         unlimited      unlimited
filesize    unlimited      unlimited
data        unlimited      unlimited
stack       8388608        67104768
core        0              unlimited
rss         unlimited      unlimited
memlock     unlimited      unlimited
maxproc     2784           4176
maxfiles    256            unlimited

Thankfully increasing maxfiles is relatively simple:

Adjusting Open File Limits in Yosemite

To adjust open files limits on a system-wide basis in Mac OS X Yosemite, you must create two configuration files. The first is a property list (aka plist) file in /Library/LaunchDaemons/limit.maxfiles.plist that contains the following XML configuration:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
  <plist version="1.0">
    <dict>
      <key>Label</key>
        <string>limit.maxfiles</string>
      <key>ProgramArguments</key>
        <array>
          <string>launchctl</string>
          <string>limit</string>
          <string>maxfiles</string>
          <string>200000</string>
          <string>200000</string>
        </array>
      <key>RunAtLoad</key>
        <true/>
      <key>ServiceIPC</key>
        <false/>
    </dict>

This will set the open files limit to 200000. The second plist configuration file should be stored in /Library/LaunchDaemons/limit.maxproc.plist with the following contents:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple/DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
  <plist version="1.0">
    <dict>
      <key>Label</key>
        <string>limit.maxproc</string>
      <key>ProgramArguments</key>
        <array>
          <string>launchctl</string>
          <string>limit</string>
          <string>maxproc</string>
          <string>2048</string>
          <string>2048</string>
        </array>
      <key>RunAtLoad</key>
        <true />
      <key>ServiceIPC</key>
        <false />
    </dict>
  </plist>

Both plist files must be owned by root:wheel and have permissions -rw-r–r–. This permissions should be in place by default, but you can ensure that they are in place by running sudo chmod 644 . While the steps explained above will cause system-wide open file limits to be correctly set upon restart, you can apply them manually by running launchctl limit.

In addition to setting these limits at the system level, we recommend setting the at the session level as well by appending the following lines to your bashrc, bashprofile, or analogous file:

ulimit -n 200000
ulimit -u 2048

At this point, you can restart your computer and enter ulimit -n into your terminal. If your system is configured correctly, you should see that maxfiles has been set to 200000.

Thanks to tombigel for his very informative gist.

Read More »

After suffering some pretty bad issues with MAMP, I decided to set everything up with homebrew instead. The result was surprisingly a much faster and (in my opinion) easier to configure setup.

As a tl;dr, we’ll be setting up Homebrew MySQL and PHP and using OSX’s built in Apache.

In this tutorial I’m using the subl command which will open a file for editing in Sublime Text. If you don’t use Sublime Text, replace subl with nano or vi or any other app you use to edit text/config files.

Homebrew Setup

Homebrew is a package manager for OSX. It makes installation of a wide variety of useful apps super easy.

Installation instructions are on the homebrew homepage but you can also just run the following:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

MySQL

I lied! We’re installing MariaDB instead! At the time of writing MySQL version 8.0.11 has just changed its default authentication method to caching_sha2_password which isn’t supported in PHP. It’s a huge hassle so we’ll just use the drop-in replacement MariaDB instead.

Install and configure MariaDB.

# Install MariaDB
brew install mariadb
# Open my.cnf config file for editing
subl /usr/local/etc/my.cnf

Add the following to the end of the file to add support for large imports:

max_allowed_packet = 2G
innodb_file_per_table = 1

Make MySQL start when you log in:

brew services start mariadb

The default installation comes with a passwordless root user. So secure it with:

mysql_secure_installation

SSL

Like all developers I like working on a custom subdomain – in this case localhost.com. We need to create a self-signed wildcard SSL certificate and get Chrome accepting it.

Create a folder /Users/your_username/Sites/certs and inside it run the following:

# Generate a temporary OpenSSL config file
cat > openssl.cnf <<-EOF
  [req]
  distinguished_name = req_distinguished_name
  x509_extensions = v3_req
  prompt = no
  [req_distinguished_name]
  CN = *.localhost.com
  [v3_req]
  keyUsage = keyEncipherment, dataEncipherment
  extendedKeyUsage = serverAuth
  subjectAltName = @alt_names
  [alt_names]
  DNS.1 = *.localhost.com
  DNS.2 = localhost.com
EOF

# Generate the certificates
openssl req \
  -new \
  -newkey rsa:2048 \
  -sha1 \
  -days 3650 \
  -nodes \
  -x509 \
  -keyout ssl.key \
  -out server.crt \
  -config openssl.cnf

# Delete the temporary config file
rm openssl.cnf

This should have created two files – server.crt and server.key which will be used in the apache config below to get HTTPS up and running.

But first, because this certificate is self-signed, it’ll result in a This site’s security certificate is not trusted! error in Chrome. That can be fixed through adding the cert to OSX’s keychain app.

• open /Applications/Utilities/Keychain\ Access.app /Users/your_username/Sites/certs/server.crt

• Set the Keychain dropdown to System and click Add
• Now in the Certificates section of Keychain find your newly added cert, double click it, expand the Trust section and set everything to Always Trust
• These changes will only take effect after a browser restart.

Apache and PHP

OSX 10.13 High Sierra comes (at the time of writing) with Apache 2.4.33.

To configure apache (with SSL):

# Install PHP 7.1
brew install php@7.1
brew link --overwrite --force php@7.1
# Open httpd.conf for editing
subl /etc/apache2/httpd.conf

# Enable the PHP and SSL modules by removing the # at the start of the line
LoadModule socache_shmcb_module libexec/apache2/mod_socache_shmcb.so
LoadModule ssl_module libexec/apache2/mod_ssl.so
LoadModule php7_module /usr/local/opt/php@7.1/lib/httpd/modules/libphp7.so
# A few extras I like to have enabled
LoadModule deflate_module libexec/apache2/mod_deflate.so
LoadModule expires_module libexec/apache2/mod_expires.so
LoadModule headers_module libexec/apache2/mod_headers.so
LoadModule rewrite_module libexec/apache2/mod_rewrite.so

# Point the document root to a htdocs folder in your home directory and enable .htaccess
# I've removed all the comments for succinctness but feel free to leave them in
DocumentRoot "/Users/your_username/htdocs"
<Directory "/Users/your_username/htdocs">
    Options FollowSymLinks Multiviews
    MultiviewsMatch Any

    AllowOverride All

    Require all granted
</Directory>

# Add PHP to your default file list
<IfModule dir_module>
    DirectoryIndex index.html index.php
</IfModule>

# And make it work
<FilesMatch \.php$>
    SetHandler application/x-httpd-php
</FilesMatch>

# As with content, we want to load all site definitions from a Sites folder in our 
# home directory. At the bottom replace the following:
# Include /private/etc/apache2/other/*.conf
IncludeOptional /Users/your_username/Sites/*.conf

# The error and custom logs too
CustomLog "/Users/your_username/Sites/logs/apache2/access_log" common
ErrorLog "/Users/your_username/Sites/logs/apache2/error_log"

# Uncomment to load the SSL config
Include /private/etc/apache2/extra/httpd-ssl.conf

Now configure the default SSL options:

# Open httpd.conf for editing
subl /etc/apache2/extra/httpd-ssl.conf

# Point to our same document root as before
DocumentRoot "/Users/your_username/htdocs"

# Update log file locations
ErrorLog "/Users/your_username/Sites/logs/apache2/error_log"
TransferLog "/Users/your_username/Sites/logs/apache2/access_log"
CustomLog "/Users/your_username/Sites/logs/apache2/ssl_request_log" \
          "%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"

# Point to the certs we created
SSLCertificateFile "/Users/your_username/Sites/certs/server.crt"
SSLCertificateKeyFile "/Users/your_username/Sites/certs/server.key"

Since this is a development machine, you’ll probably also want to enable the ever popular xdebug which luckily for us comes pre-compiled with OSX. What OSX doesn’t come with, however, is a default php.ini though it does have a sample file. We can use that:

sudo cp /etc/php.ini.default /etc/php.ini

Then simply add extension=xdebug.so below all the extension= lines in your new /etc/php.ini file.

VirtualHosts

I like to split virtualhosts up into one for each site and store them all in /Users/your_username/Sites/ folder.

Create a file /Users/your_username/Sites/mysite.localhost.com.conf and add the following:

<VirtualHost *:80>
  ServerAdmin webmaster@localhost
  ServerName mysite.localhost.com
  ServerAlias mysite.localhost.com
  DocumentRoot /Users/your_username/htdocs/mysite.com

  ErrorLog /Users/your_username/Sites/logs/mysite.com.error.log
  LogLevel warn
  CustomLog /Users/your_username/Sites/logs/mysite.com.access.log varnishcombined

  <Directory /Users/your_username/htdocs/mysite.com/>
    Options Indexes FollowSymLinks
    AllowOverride All
    Require all granted
  </Directory>
</VirtualHost>

<IfModule ssl_module>
  <VirtualHost *:443>
    ServerAdmin webmaster@localhost
    ServerName mysite.localhost.com
    ServerAlias mysite.localhost.com
    DocumentRoot /Users/your_username/htdocs/mysite.com

    ErrorLog /Users/your_username/Sites/logs/mysite.com.error.log
    LogLevel warn
    CustomLog /Users/your_username/Sites/logs/mysite.com.access.log varnishcombined

    <Directory /Users/flynsarmy/htdocs/work/qpsmedia/qpsstats/>
      Options Indexes FollowSymLinks
      AllowOverride All
      Require all granted
    </Directory>

    SSLEngine on
    SSLCertificateFile    /Users/your_username/Sites/certs/server.crt
    SSLCertificateKeyFile /Users/your_username/Sites/certs/server.key

    <FilesMatch "\.(cgi|shtml|phtml|php)$">
      SSLOptions +StdEnvVars
    </FilesMatch>
  </VirtualHost>
</IfModule>

Finally, restart apache and you should be good to go!

sudo apachectl restart

Resources

• Much of the content of this article came from David Marcus’s really great post Set up localhost on macOS High Sierra (Apache, MySQL, and PHP 7) with SSL/HTTPS.
• To get SSL certs working correctly in the browser I followed the instructions on Jed Schmidt’s gist How to set up stress-free SSL on an OS X development machine.
• Because I’m positive I’ve missed or incorrectly written at least some of this article I’ve uploaded copies of all relevant files. httpd.conf httpd-ssl.conf mysite.localhost.com.conf

Read More »

If you want to back up your MySQL databases you’ll be familiar with

mysqldump -uroot -p --all-databases > dump.sql

However this includes the information_schema, mysql and performance_schema databases. These are often not only unwanted in the dump, but can potentially cause issues on import.

To exclude these databases from your dump use the following script courtesy of user RolandoMySQLDBA on the StackOverflow forums.

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql

Read More »

When doing a

mysqldump --all-databases -uUSER -p > dump.sql

I was getting the error

mysqldump: Got error: 1017: Can’t find file: ‘./dbname/tablename.frm’ (errno: 13 – Permission denied) when using LOCK TABLES

The error was caused by the table files in my MySQL data directory being owned by the wrong user so MySQL couldn’t properly read them. The solution is simple.

Find your MySQL Data Directory, Fix Permissions

$ mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name="datadir"'

+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+

# Check owner of each file
$ ls -lh /usr/local/var/mysql/

# Update to correct owner
$ sudo chown -R _mysql:admin /usr/local/var/mysql/*

After fixing the permissions on all files in this directory, I was able to mysqldump correctly.

Read More »

This post will be a short and sweet one. Copying/cloning a database between mysql hosts in a single command can be done with:

mysqldump -h old_host -u old_user -p old_password old_db_name | mysql -h new_host -u new_user -p new_password new_db_name

Note that this will dump the entire database down to your machine before re-uploading it to the new machine.

Read More »

Sometimes in WordPress you want to include associated taxonomy terms with your get_posts() or WP_Query lookups. Doing so can have a noticeable impact on performance. Not to mention it’s much cleaner code-wise.

Here’s an example. I wanted to group image attachments into genre – action, adventure etc. and display that information on my sites frontend. Firstly I added my genre taxonomy to the attachment post type:

register_taxonomy('genre', 'attachment', array(
	'label' => 'Genres',
	'rewrite' => array( 'slug' => 'genre' ),
	'hierarchical' => true,
	'capabilities' => array(
		'assign_terms' => 'edit_posts',
		'edit_terms' => 'publish_posts'
	)
));

I now needed to display that information on the images associated post page (single.php) on the frontend.

The dumb way

On my first attempt I looped through the images, grabbing the associated genres and displaying them:

$images = get_posts(array(
	'post_parent' => get_the_ID(),
	'post_type' => 'attachment',
	'numberposts' => -1,
	'orderby'        => 'title',
	'order'           => 'ASC',
	'post_mime_type' => 'image',
));
foreach ( $images as $image )
	echo $image->post_title . ': ' . strip_tags(get_the_term_list($image->ID, 'genre', '', ', ', ''));

My image: Action, Adventure

This resulted in one unnecessary database call per image which could add up quickly. I needed a better way.

A smarter approach

WP_Query (which get_posts() uses to retrieve its results) supports a filter posts_clauses that lets you modify various parts of the SQL query it is about to perform. I used this to JOIN the taxonomy tables on and include the genre name(s) in the result array.

Firstly the filter (only works if you drop it in functions.php):

/**
 * Include 'size' name in image attachment lookups. This only applies if
 * INCLUDE_GENRES global variable flag is set - otherwise it will affect
 * the_loop
 *
 * @param array $pieces Includes where, groupby, join, orderby, distinct, fields, limits
 *
 * @return array $pieces
 */
add_filter( 'posts_clauses', function( $pieces )
{
	global $wpdb, $INCLUDE_SIZE;

	if ( empty($INCLUDE_GENRES) )
		return $pieces;

	$pieces['join'] .= " LEFT JOIN $wpdb->term_relationships iqctr ON iqctr.object_id=$wpdb->posts.ID
						 LEFT JOIN $wpdb->term_taxonomy iqctt ON iqctt.term_taxonomy_id=iqctr.term_taxonomy_id AND iqctt.taxonomy='genre'
						 LEFT JOIN $wpdb->terms iqct ON iqct.term_id=iqctt.term_id";
	$pieces['fields'] .= ",GROUP_CONCAT(iqct.name SEPARATOR ', ') AS genres";

	return $pieces;
}, 10, 1 );

You’ll notice the $INCLUDE_GENRES variable. This is required because without it the filter will apply to all the_loop and other queries. We only want it to apply for one specific query. Now how to use it:

$INCLUDE_GENRES = true;
$images = get_posts(array(
	'post_parent' => get_the_ID(),
	'post_type' => 'attachment',
	'numberposts' => -1,
	'orderby'        => 'title',
	'order'           => 'ASC',
	'post_mime_type' => 'image',
	'suppress_filters' => false,
));
$INCLUDE_GENRES = false;

foreach ( $images as $image )
	echo $image->post_title . ': ' . $image->genres;

My image: Action, Adventure

Perfect!

Read More »

Here is a super simple SQL query to determine the size of all tables in a given database:

SELECT table_name AS "Table",
	round(((data_length + index_length) / 1024 / 1024), 2) AS Size_in_MB
FROM information_schema.TABLES 
WHERE table_schema = 'my_db_name'
ORDER BY Size_in_MB DESC

This can be very useful when debugging for instance in figuring out which rogue plugin/module on your site is dumping hundreds of megs of data into your database.

Read More »

I recently came across a tutorial on sitting Redis infront of WordPress allowing for insanely fast page generation. I gave it a try and it really works, in fact I’m now using it on this very site! The best part however is the fact that the script requires absolutely no modification to your existing WordPress site save for 1 line of htaccess. Truly amazing.

Below I’ll detail my slightly modified version of Jim’s script along with some metrics.

Firstly, What is Redis and what will it do for me?

The Redis website describes Redis as

… an open source, BSD licensed, advanced key-value store. It is often referred to as a data structure server since keys can contain strings, hashes, lists, sets and sorted sets.

What does this mean? Essentially it’s Memcached but more useful. Redis stores key-value pairs in memory and spits them out when requested. Unlike Memcached it has built in persistence but what’s most important to us is that it’s fast – very fast.

We’ll be using Redis to speed up our site by loading cached pages from it directly without even booting up WordPress. This will save a large amount of page generation time and get out site infront of our users’ eyeballs faster.

Exactly how much faster are we talking?

In my very unscientific tests, loading www.flynsarmy.com a bunch of times resulted in the following:

As you can see from the table above this equates to a 20x to 50x speed increase and that was WITH W3 Total Cache installed! Results of course may vary but I think you get the picture.

Read More »

Here’s a very quick set of SQL snippets for updating a WPMU domain. This might be useful when building a site on a development domain before moving to a production one later on.

Firstly update the wp_blogs table:

UPDATE wp_blogs SET domain='newdomain.com';

There will be a bunch of wp_options and wp_posts tables – one per site. Find all the tables:

SHOW TABLES LIKE "%_options";
SHOW TABLES LIKE "%_posts";

and for each table, perform the following query:

#_options tables
UPDATE <tablename> SET option_value=REPLACE(option_value, 'http://olddomain.com', 'http://newdomain.com');
#_posts tables
UPDATE <tablename> SET post_content=REPLACE(post_content, 'http://olddomain.com', 'http://newdomain.com');

This was enough to get the sites working for me. Additional tweaks are probably required afterwards – if you find anything let me know in the comments below.

Read More »

Tonight I had to move my WPMU install from my local development machine to the live server – this meant a change in installation path which is always a hassle with WordPress. Below I’ll detail my issues and the corresponding fixes in the hopes it will make life easier for others experiencing the same problems.

Redirection to wp-signup.php

First thing I noticed was that when loading the site on a live domain, I’d get instantly redirected to mydomain.com/wp-signup.php?new=mydomain.com. With a bit of Googling I came across this forum thread which recommended adding the following to my wp-config.php:

define( 'NOBLOGREDIRECT', '' );
define( 'SUBDOMAIN_INSTALL', false );

Don’t do this! It will get the site closer to working order but it’s not going to help you in the long run – especially when you need the other domains working too. Instead here’s what you should be doing:

#Dump your DB
mysqldump - u<username> -p<password> -h<host> <dbname> > test.sql

#Update folder path to that of the new servers
sed -i "s/path\/to\/install/new\/path\/to\/install/g" test.sql 
#If you installed into localhost/foo/bar/mysite, change that to your live servers domain
sed -i "s/url\/path\/to\/site/www\.domain\.com/g" test.sql

This is pretty standard behavior for moving WordPress sites, however if you load the above dump up on your live domain you’ll probably be greeted with the dreaded Error establishing a database connection.

Error establishing a database connection

Heading to www.domain.com/wp-admin will shed a little more light on the situation – you need to update your wp_blogs table for the main site. WPMU is currently using your development servers URL from this table and ignoring what’s in wp-config.php. To play things safe we’ll update any occurrances of our test servers domain in test.sql:

#Update your live servers subdomain in wp_blogs to your live servers domain
sed -i  "s/yoursite\.localhost\.com/www\.yourdomain\.com/g" test.sql

This should do the trick. Load that bad boy into your live server and you should be good to go!

Read More »