Tag Archives: mysql

Getting MySQL Working After OX 10.10 Yosemite Upgrade

I updated my development computer to OS X 10.10 (Yosemite) and found MySQL was dead. These are notes on what I did to get MySQL running again.

Update the latest MySQL off of their website. At the time of this writing, that is Mac OS X 10.9 (x86, 64-bit), DMG Archive from the MySQL download site.

I found that MySQL would run but MySQL Workbench couldn’t connect. It turns out that the data directory is kept under each version. Thus, when I updated from 5.6.17 to 5.6.21 MySQL was pointing to a fresh new database.

The new version is installed in /usr/local/mysql-5.6.21-osx10 algo similar al viagra.8-x86_64, with the MySQL data files in the data subdirectory. I renamed the data subdirectory to ensure I have a backup and made a copy of the old directory.

sudo cp -rp ../mysql-5.6.17-osx10.7-x86_64/data .

It appears that MySQL is working fine now. Note that this will only work for incremental revisions. If you’re reaching across minor version numbers (such as 5.5 to 5.6), the changes in structure will probably not work. (You did make a backup of the original data directory, didn’t you?)

 

 

Python 2.7, Django, and MySQL on OS X

For some reason, getting Python and MySQL talking on OS X has been an annoyance. These are my notes for getting the two to talk to each other in a Python 2.7 virtual environment for a Django project.

The Django 1.6 docs recommend using the MySQLdb package. Its installation uses the mysql_config executable.

I have the following set up:

  1. MySQL 5.6.16
  2. Python 2.7.3
  3. PyCharm 3.1.1, which was used to create
  4. a Python virtual environment with pip located in $HOME/upharm27

[code]
$ locate mysql_config

/usr/local/mysql-5.6.16-osx10.7-x86_64/bin/mysql_config

$ export PATH=$PATH:/usr/local/mysql-5.6.16-osx10.7-x86_64/bin
$ ~/upharm27/bin/pip install mysql-python
[/code]

I have gcc 4.7.2 available, but curiously, the installer gave the following message:

[code]
Installing collected packages: mysql-python
Running setup.py install for mysql-python
gcc-4.2 not found, using clang instead
[/code]

The install succeeded using clang, so it’s nothing more than a curiosity at this point.

I was able to verify that the package was installed with:

[code]
$ ~/upharm27/bin/python
Python 2.7.3 (v2.7.3:70274d53c1dd, Apr  9 2012, 20:52:43)
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>>
[/code]

 

Update

I just followed these notes using the latest XCode 5.1 on OS X 10.9 and the mysql-python install failed. Apparently the clang Apple ships produces errors on unknown flags by default. I was able do get around this by:

[code]
$ export CFLAGS=-Qunused-arguments
$ export CPPFLAGS=-Qunused-arguments
$ ~/upharm27/bin/pip install mysql-python
[/code]

Thanks to the good folks at StackOverflow. More details may be found here.

Setting MySQL to Default to Unicode

When running unit tests in Django, I was getting a strange MySQL failure when attempting to insert non-ASCII Unicode characters into the database, for example:

[code light=”true”]
Warning: Incorrect string value: ‘\xE2\x89\xA5 %’ for column ‘value’ at row 1
[/code]

What is happening is that Django creates a new schema from scratch for testing. This new schema picks up the MySQL defaults. All my test tables ended up with Latin-1 encoding instead of UTF-8 encoding.

I needed to change mysqld to default to unicode internally so Django will run unit tests involving correctly.

In /etc/my.cnf I added the following:

[code lang=”c” light=”true”]
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect=’SET NAMES utf8′
character-set-server = utf8
[/code]

h/t stackoverflow

Importing UTF-8 into MySQL

The MySQL command line tool does not correctly handle UTF-8 encoded source files by default. If you use the ‘source’ command, Japanese turns into garbage. 🙁

To fix this, use the --default-character-set=utf8 option, e.g.

$ mysql -u username --default-character-set=utf8  -p

This should allow you to use the ‘source’ command to import your foreign language or otherwise special characters into MySQL without trouble.

Using MySQL Administrator Over a Secure (ssh) Connection

MySQL Administrator is not designed to work over a secure connection like ssh. By using ssh tunneling, you can connect to your remote MySQL servers from your workstation.

MySQL Tools

Download the MySQL Tools package.
Install the MySQL Tools package. On the Mac, just drag to Applications.

Create the Tunnel

To create the tunnel, open a shell (Terminal on the Mac) and run the following:

$ ssh -N -L3306:127.0.0.1:3306 username@remote_hostname

-N instructs ssh to run silently without creating a remote shell.
-L a:b:c instructs ssh to use port “a” on the local machine and map it (from the remote server’s viewpoint to port c on machine b).
Now you have a secure tunnel to the server.

Use the Right Hostname

If you run on a Un*x based workstation (i.e. Mac or Linux), when you run either the MySQL Administrator or the MySQL Query Browser, set the “Server Hostname” field to “127.0.0.1”.

MySQL Administrator connection dialog

If you skip this step you will receive the following error:
Error Dialog