MySQL

MySQL is a free relational database, now owned by Oracle.

Installing MySQL

Here are instructions for setting up a 64-bit MySQL on Mac OS X, tested August 2010 with 10.6.4 Snow Leopard (MySQL 5.1.49), October 2012 with 10.8.2 Mountain Lion (MySQL 5.6.17), April 2014 on 10.9.2 Mavericks (MySQL 5.6.25), Oct 2015 on 10.10.5 Yosemite (MySQL 5.6.25), May 2016 on 10.11.5 El Capitan (MySQL 5.7.13), and July 2017 on 10.12.6 Sierra (MySQL 5.7.19).

Oracle provides version 5.7.19 for OS X 10.12 Sierra. It has many bug fixes and new features. Download the OSX version DMG file of the MySQL Community Server from mysql.com. (Note that there are many features of MySQL that I don't exercise and have not tested; test it for your needs.) For Sierra, see Neil Gee's page for instructions. (I do not install or use PHP or PHPMyAdmin, for security reasons, so I skip those parts.) The Oracle installer sets the MySQL root password to a temporary value for MySQL 5.7. Change this value after installation, before it expires (see below).

Setting up Auto Start

In El Capitan and Sierra, the MySQL preference pane works again, so you can use it to start MySQL on system restart. See below for Yosemite directions.

Cleaning Up

Your old MySQL database may still be on disk in a directory like /usr/local/mysql-VERSIION/data/, and can be deleted at some point.

.my.cnf

If you make a file named .my.cnf in your home directory, then you can use the mysql command without giving a password. Mine contains

    [client]
    user=root
    password=WHATEVER
    host=localhost

If you have only one database, you can put that value in the file also, with database=DBNAME.

Problems

Mountain Lion

In Mountain Lion, I got a failure message about dyld when trying to install the Perl DBD module. Fixed with cd /usr/local; sudo mkdir lib; cd lib; ln -s/usr/local/mysql/lib/*.dylib .

(Gotcha:) With MySQL 5.6.17 on Mountain Lion, some of my database files would not load, because the mysql command fails, instead of warning, when a field value is too long for the field. This problem occurs because STRICT mode is now the default. The fix is to turn STRICT mode off, or fix the data.

Yosemite

For Yosemite the Systen Preferences pane for MySQL did not work, so you had to set up a .plist file and point launchctl at it. See MySQL does not start in OSX Yosemite 10.10. This note is not quite accurate.. follow these steps: set up the .plist file as described in the note, and set its access; then do sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysql.plist . MySQL should start, and restart after a system restart.

El Capitan

Install problems

Under El Capitan the install appeared to fail, because the old version was running. Stop MySQL before installing with sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysql.plist . This command will also stop MySQL from being started by the system, so that you can control it from the System Preferences pane.

Unable to Connect

I had trouble getting MySQL to work. It worked for a while but then after a reboot I kept getting a socket error.

    # mysqladmin status
    mysqladmin: connect to server at 'localhost' failed
    error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
    Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!

Searching the web was little help. Finally I looked in /usr/local/mysql/data/mysqld.local.err and noticed the messages

    2016-06-05T22:18:39.011501Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
    2016-06-05T22:18:39.011603Z 0 [Note] IPv6 is available.
    2016-06-05T22:18:39.011619Z 0 [Note]   - '::' resolves to '::';
    2016-06-05T22:18:39.011683Z 0 [Note] Server socket created on IP: '::'.

This looked like a bad idea.. I was not using IPV6 anywhere else. In  ► System Preferences... ► Network ► Ethernet ► Advanced I changed "Configure IPV6" to link-local only and restarted. MySQL 5.7.13 was running and I could contact it.

Syntax Change

You can no longer say
mysql dbname --exec='source temppic.sql;'
and must change this to
mysql dbname --execute='source temppic.sql;'
so I had to modify all my shell scripts and Perl programs that used --exec.

Default SQL Mode Changed

Executing queries that had worked on older versions of MySQL, I got messages like

  Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.hits.systime'
  which is not functionally dependent on columns in GROUP BY clause;
  this is incompatible with sql_mode=only_full_group_by

because MySQL has strengthened the ONLY_FULL_GROUP_BY mode and made it the default with version 5.7. (Basically, this was a MySQL-only feature that was not in standard SQL and whose interpretation was arguable.) A quick temporary fix is to execute the mysql command

  set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

omitting the ONLY_FULL_GROUP_BY mode (this will stick until you restart mysqld). You could also include this mode setting in the MySQL configuration to make it permanent.

A better long term fix is to recode the query that got the error: one way is to wrap the offending items with ANY_VALUE(), but this function is not backward compatible with older versions of MySQL. I recoded my queries to wrap the offending variables with an aggregation function such as MAX(), and that worked for most of them. For others, I actually had to think about what I was doing.

Home | FAQ © 2010-2017, Tom Van Vleck updated 2017-07-24 16:45