Posted in Database

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

1
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

1
launchctl limit

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?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:

1
2
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 »

Posted (Updated ) in Database, Linux, PHP

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:

1
/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.

1
2
3
4
# 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:

1
2
max_allowed_packet = 2G
innodb_file_per_table = 1

Make MySQL start when you log in:

1
brew services start mariadb

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

1
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 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 = nonRepudiation, digitalSignature, keyEncipherment
  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 server.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.

  • 1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    
    # Install PHP 7.3
    brew install php@7.3
    brew link --overwrite --force php@7.3
    # 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
  • 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):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 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"

Now configure the default SSL options:

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

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<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>

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:

1
sudo apachectl restart

 

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

1
sudo apachectl restart

 

Resources

Read More »

Posted (Updated ) in Database

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

1
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MYSQL_USER=rootMYSQL_PASS=rootpasswordMYSQL_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 »

Posted (Updated ) in Database

When doing a

1
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

1
2
3
4
5
6
7
8
9
10
11
12
13
$ 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 »

Posted in Database

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

1
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 »

Posted in Database, PHP

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:

1
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:

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

and for each table, perform the following query:

1
2
3
4
#_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 »

Posted (Updated ) in Database, Linux, PHP

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:

1
2
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:

1
2
3
4
5
6
7
#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:

1
2
#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 »

Posted in Database

MySQL’s built in FROM_UNIXTIME() function only handles positive numbers however we can still convert negative integers to dates using its handy DATE_ADD() function like so:

1
SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL -13391999 SECOND)

which gives:

1
1969-07-30 10:00:01

Note that this also works with positive numbers, so if your database contains a mixture of both it’s still safe to use.

Credit to user fat_kid for his tutorial here.

Read More »

Posted in Database

Without any adieu what-so-ever, below is a MySQL implementation of PHP’s ucfirst function which capitalizes the first letter of each word in a string.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DELIMITER $$
CREATE FUNCTION CAP_FIRST (INPUT VARCHAR(255))
 
RETURNS VARCHAR(255)
 
DETERMINISTIC
 
BEGIN
    DECLARE len INT;
    DECLARE i INT;
 
    SET len   = CHAR_LENGTH(INPUT);
    SET INPUT = LOWER(INPUT);
    SET i = 0;
 
    WHILE (i < len) DO
        IF (MID(INPUT,i,1) = ' ' OR i = 0) THEN
            IF (i < len) THEN
                SET INPUT = CONCAT(
                    LEFT(INPUT,i),
                    UPPER(MID(INPUT,i + 1,1)),
                    RIGHT(INPUT,len - i - 1)
                );
            END IF;
        END IF;
        SET i = i + 1;
    END WHILE;
 
    RETURN INPUT;
END$$
DELIMITER ;

Use it like so:

1
SELECT CAP_FIRST('my string of words');

and you should get:

My String Of Words

All credit and many thanks for this function to Joezack.

Read More »

Posted (Updated ) in Database, Linux

Update 2011-10-12: More storage options! Google Storage, Local HDD, FTP, experimental unlimited Google Docs conversion

Have you ever wanted to ensure that even if your server dies in the most spectacular of ways, your DB is safe? Meet Cloud Database Backup. Cloud Database Backup is a little script I’ve written that allows you to quickly and easily back up your MySQL DBs to the cloud.

Currently you can back your database up to:

  • Amazon S3
  • FTP
  • Google Docs
  • Google Storage
  • Local HDD

Download the script here.

Requirements:

  • MySQL with access to INFORMATION_SCHEMA database
  • php, php5-cli (for S3 backups)
  • python2, python-gdata (for Google Docs backups)

This is an updated version of my old Automatic MySQL Database Backup script that adds support for Google Docs (which now allows uploads of any file type for non-enterprise users), the recently opened Google Storage, as well as local HDD and FTP servers. It does a MySQL dump of each DB separately, zips them all and uploads the zip to your cloud service of choice.

I’ve also added in an experimental ‘db chunker’ which uses this script to split SQL dumps into convertible sizes for Google Docs, allowing you to upload unlimited backups for free; owever due to a reproducible bug in Google Docs, I wouldn’t recommend its use (and it’s disabled by default).

To get started simply update your details in the appropriate places at the top of backup.sh and type ./backup.sh. Presto!

I’ve also included a crontab example to allow automation of backups.

Read More »