• Setting up MySQL 5.7.* on MAMP Pro (v4.4)

    Posted on 29th Mar 2018

    Out of the box MAMP PRO v4.4 comes with a flavour of MySQL 5.6 (at the time of writing it's 5.6.38 specifically). Why they don't with 5.7 is beyond me (it was first released in 2013!) but that's by the by.

    I wanted to live life on the edge and use the latest and greatest so here are my steps navigating some of the 'gotchas' and using some wizardry to get it playing nicely.

    DISCLAIMER. MAMP do document a method to setup a separate instance of MySQL 5.7 that runs in parallel to their packaged 5.6 version, but you have to manage multiple ports and the databases get stored in separate places etc etc. Meh. Lets' update the version of MySQL MAMP uses out of the box and be done with it.

    Firstly here was my setup: OS. Mac OS 10.12.6 MAMP PRO. 4.4.1

    Step 1. Download and Install MySQL 5.7.*

    Firstly we're going to need to get a version of MYSQL 5.7 that's compatible with our Operating System, backup our existing MAMP setup (you know, just in case) and migrate MAMP to use it.

    Here's the bash script I used to do all the above in one fell swoop. I just saved in a file named 'mamp-migrate.sh' to my Desktop.

    #!/bin/sh
     
    wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-macos10.12-x86_64.tar.gz
    tar xfvz mysql-5.7*
     
    echo "stopping mamp"
    sudo /Applications/MAMP/bin/stop.sh
    sudo killall httpd mysqld
     
    echo "creating backup"
    sudo rsync -arv --progress /Applications/MAMP ~/Desktop/MAMP-Backup
     
    echo "copy bin"
    sudo rsync -arv --progress mysql-5.7.*/bin/* /Applications/MAMP/Library/bin/ --exclude=mysqld_multi --exclude=mysqld_safe 
     
    echo "copy share"
    sudo rsync -arv --progress mysql-5.7.*/share/* /Applications/MAMP/Library/share/
     
    echo "fixing access (workaround)"
    sudo chmod -R o+rw  /Applications/MAMP/db/mysql/
    sudo chmod -R o+rw  /Applications/MAMP/tmp/mysql/
    sudo chmod -R o+rw  "/Library/Application Support/appsolute/MAMP PRO/db/mysql/"
     
    echo "starting mamp"
    sudo /Applications/MAMP/bin/start.sh
     
    echo "making symlink, enter sudo password"
    sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock
     
    echo "migrate to new version"
    /Applications/MAMP/Library/bin/mysql_upgrade -u root -p

    To run this script just open up the Terminal application, cd to your Desktop, e.g. cd ~/Desktop and then run 

    bash mamp-migrate.sh

    If you're not running MAC OS 10.12 or want a different version of MySQL (the above script installs v5.7.20) there are a few other flavours of this script here that claim to work for other OS and MySQL versions.

    Step 2. Check MAMP PRO is picking up MySQL 5.7

    All being well you should be able to open up MAMP PRO, start the MySQL server and navigate to the MySQL settings (click on MySQL in the left sidebar). At the very top of it should read 'Version: 5.7.20' or whichever version of MYSQL 5.7 you ended up installing.

    Step 3. my.cnf

    Under the hood one huge change that tends to catch people out upgrading to MySQL 5.7 is that it defaults to include some options within the sql_mode setting (in MySQL v5.6 this setting was empty). One such inclusion within this setting is the 'ONLY_FULL_GROUP_BY' flag. There's a strong chance that if you're developing on a CMS or platform released pre-2017/2018 that this alone is going to break some elements of the installation or in some cases take a whole site down. Thankfully this should be fairly straightforward to mitigate if it is causing you headaches.

    Within MAMP PRO open up File » Edit Template » MySQL (my.cnf) » 5.7.20 (or whichever version you installed). This will open up a text editor window. You should be able to see a [mysqld] line. Beneath that line add the following

    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

    This effectively retains all the default flags for the sql_mode setting except the ONLY_FULL_GROUP_BY flag. Hit CMD+s to save the file then close the text editor window. MAMP should restart MySQL to pickup the new settings. Try your website or application again to see if the new settings have been picked up.

    If they haven'tand this was the case for me, read on...

    Step 4. MAMP PRO my57.cnf template

    Behind the scenes whenever the MySQL server on MAMP PRO is started it takes a template .cnf file and generates a my.cnf file from it that the server can them parse and execute. Step 3 above in theory is editing that template file but sometimes it seems that isn't the case. We can do this manually though by finding the correct template file in our filesystem. For me the template file in question is named my57.cnf and can be found here: 

    /Applications/MAMP PRO/MAMP PRO.app/Contents/Resources/my57.cnf

    You can either open this file in a text editor or use vim or nano etc to edit it within the Terminal. I copied the contents from the file in Step 3 into this my57.cnf file (which was completely empty), saved the file and restarted the MySQL server in MAMP PRO. Voila! My site came back online.

    Here are the final contents of the my57.cnf file

    # Example MySQL config file for medium systems.
    #
    # This is for a system with little memory (32M - 64M) where MySQL plays
    # an important part, or systems up to 128M where MySQL is used together with
    # other programs (such as a web server)
    #
    # You can copy this file to
    # /etc/my.cnf to set global options,
    # mysql-data-dir/my.cnf to set server-specific options (in this
    # installation this directory is /Library/Application Support/appsolute/MAMP PRO/db/mysql57) or
    # ~/.my.cnf to set user-specific options.
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
    # The following options will be passed to all MySQL clients
    [client]
    #password = your_password
    #port = 9999
    socket = /Applications/MAMP/tmp/mysql/mysql.sock
    # Here follows entries for some specific programs
    # The MySQL server
    [mysqld]
    #port = 9999
    socket = /Applications/MAMP/tmp/mysql/mysql.sock
    key_buffer_size = 16M
    max_allowed_packet = 16M
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<!--
    explicit_defaults_for_timestamp = 1
    #table_open_cache = 64
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    MAMP_bind-address_MAMP
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    MAMP_skip-networking_MAMP
    # Replication Master Server (default)
    # binary logging is required for replication
    #log-bin=mysql-bin
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id = 1
    # Replication Slave (comment out master section to use this)
    #
    # To configure this host as a replication slave, you can choose between
    # two methods :
    #
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
    #    the syntax is:
    #
    #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
    #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
    #
    #    where you replace <host>, <user>, <password> by quoted strings and
    #    <port> by the master's port number (3306 by default).
    #
    #    Example:
    #
    #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
    #    MASTER_USER='joe', MASTER_PASSWORD='secret';
    #
    # OR
    #
    # 2) Set the variables below. However, in case you choose this method, then
    #    start replication for the first time (even unsuccessfully, for example
    #    if you mistyped the password in master-password and the slave fails to
    #    connect), the slave will create a master.info file, and any later
    #    change in this file to the variables' values below will be ignored and
    #    overridden by the content of the master.info file, unless you shutdown
    #    the slave server, delete master.info and restart the slaver server.
    #    For that reason, you may want to leave the lines below untouched
    #    (commented) and instead use CHANGE MASTER TO (see above)
    #
    # required unique id between 2 and 2^32 - 1
    # (and different from the master)
    # defaults to 2 if master-host is set
    # but will not function as a slave if omitted
    #server-id       = 2
    #
    # The replication master for this slave - required
    #master-host     =   <hostname>
    #
    # The username the slave will use for authentication when connecting
    # to the master - required
    #master-user     =   <username>
    #
    # The password the slave will authenticate with when connecting to
    # the master - required
    #master-password =   <password>
    #
    # The port the master is listening on.
    # optional - defaults to 3306
    #master-port     =  <port>
    #
    # binary logging - not required for slaves, but recommended
    #log-bin=mysql-bin
    # Point the following paths to different dedicated disks
    #tmpdir = /tmp/ 
    #log-update = /path-to-dedicated-directory/hostname
    # Uncomment the following if you are using BDB tables
    #bdb_cache_size = 4M
    #bdb_max_lock = 10000
    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = "/Library/Application Support/appsolute/MAMP PRO/db/mysql57"
    #innodb_data_file_path = ibdata1:10M:autoextend
    #innodb_log_group_home_dir = "/Library/Application Support/appsolute/MAMP PRO/db/mysql57"
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    #innodb_buffer_pool_size = 16M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 5M
    #innodb_log_buffer_size = 8M
    #innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 50
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    [isamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    [myisamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    [mysqlhotcopy]
    interactive-timeout
    # DONT REMOVE: MAMP PRO my57.cnf template compatibility version: 10
  • The Boxhead Launch Checklist

    Posted on 9th Nov 2015

    Thousands of sub-standard websites 'go live' every single day. At Boxhead we wanted to make sure we didn't launch any of those websites, that offered so much but didn't deliver a great experience. But how do you ensure you're consistently launching top-of-the-line websites for clients across the board with multiple designers and developers in the mix?

    Let me introduce you to our answer

    The Boxhead Launch Checklist

    I know, I know it's just a set of tick boxes I hear you say. While that is true and anybody could have thought of it, nobody else did to my recollection and certainly not comprehensively.

    OK you might have a spreadsheet to track this sort of thing but that's not really what spreadsheets are for and you know it.

    The launch checklist has been around (in multiple versions) for a few years at Boxhead HQ. It's not special in itself, but it keeps us on track to deliver outstanding websites to all our clients every time If you're a client of Boxhead, big budget or low, your site has had the launch checklist treatment.

    The checklist has seen multiple versions as we've re-visited what it takes to make great websites in our ever-evolving landscape. We expect the checklist to improve as we learn day-to-day, as development best practices settle and as new tools become a part of our workflow. As it stands, here are some of the core areas the checklist covers:

    • SEO Best practices
    • Performance checks
    • CMS specific configurations
    • Accessibility standards
    • Compatibility
    • Security
    • Legal things...

    Why disclose our secrets?

    We use the internet almost every single day, accessing websites, web-apps and other online tools. We know the pains of visiting a site that could with very little extra time have delivered a vastly better and more appropriate experience. With so much practice under our belts of building and launching great sites it would be inexcusable for us to hold this tool close to our chests. We want to make the web better for everyone. We can't do that on our own. Share and share alike.

    Our hope is that developers the world over will adopt some of the best practices outlined in the checklist as part of their workflow. Not every item will apply to every project, that's OK. There are probably some items missing (we're not perfect – let us know how to improve it for everybody).

    Why not give it a whirl on your next web project and see if it helps you launch something fantastic.

  • Craft CMS image upload fails

    Posted on 29th Oct 2015

    Yep, I'm sure all Craft developers have been there at some point. You go to upload an image, you get the pretty progress bar, it gets to the end, and then nothing.

    There can be many reasons why this might happen. Here's my checklist:

    1. Is the upload directory writable?
      Fix: Check your directories permissions. It should be set to 775 or 777 (if you live life on the edge)
    2. Are the asset paths set correctly? You can find these in admin/settings/assets
      Fix: Check you have the correct absolute or relative paths and URLs set. If you're using multiple-environment configurations and are using variables like {basePath} and {baseUrl}, check these are correct in your craft/config/general.php file.
    3. Is it a PHP memory_limit problem? If it is, chances are there's an error logged in craft/storage/runtime/logs
      Fix: Get your hosting provider to increase PHP's memory_limit to 64MB, 128MB or 256MB (if you're feeling particularly adventurous)
    4. Is the upload script timing out? Again, if this is the case there could be an error indicating so in craft/storage/runtime/logs
      Fix: Get your hosting provider to increase PHP's max_execution_time
    5. If you've got this far and are still having problems you may need to use the following wizardry to get things working. I've had this problem when trying to upload JPGs (where every other file would upload fine).
      Fix: Get your hosting provider to update the PHP configuration (typically the php.ini file) by adding mbstring.func_overload = 0
      Thanks go out to 'Oliver' over on this Stack Exchange thread for that fix!