gnucash + mysql + OS X 10.10: getting it running

Tue, Apr 7, 2015 7-minute read

This post will help you to get gnucash running with MySQL on OS X 10.10.

Background

I do the bookkeeping and accounts for a variety of small NFPs or charitable organisations. I recently moved my bookkeeping for these from a combination of Sharepoint (seriously) and SSRS and Excel in to Gnucash. I had been looking for a decent free app for a while and whilst there are some interesting cloud options (in fact there are a million options) I stumbled across Gnucash - a nice looking open source, cross-platform tool that would work on my Mac (without X11) and decided to go for it.

It’s a great application, which, for basic businesses, should provide more than enough functionality.

The merits of the app, though, are not what this post is all about. This post is all about the pain and suffering I’ve just been through getting it work with MySQL.

OK, so whilst the website is pretty clear about saying it supports XML, SQLite, MySQL or PostgreSQL, what it doesn’t tell you is that out of the box it only actually supports XML and sqlite. To begin with, XML was fine and dandy - but I soon hit an issue - I wanted to export a list of ‘customers’ from one of my sets of accounts. After trying and failing with a number of things, I stumbled across a post that casually said ‘well, if you’re running MySQL, then it’s probably quicker to query the DB…’. No probs I thought, I’ll just save it to MySQL… until I discovered the nugget about not having MySQL support by default.

First mistake: I started digging. The reality is in the time it took me to fix this, I could have manually copied out my list of customers. My advice is if you really want gnucash + mysql running on your Mac then build an Ubuntu virtual machine and run it in VMware fusion (or other). That way you can use the package managers to just build and install everything for you.

The Issue

If you want to use MySQL you have to not only get MySQL running on your Mac but also for gnucash to work, you have to… wait for it… build the drivers yourself. Yep, that’s right - the drivers are not included so you have to build (compile) then.

Assumptions

My environment is OS X 10.10 (Yosemite) running on a 2012 Retina MBP and a new (ish) install of Gnucash 2.6.5. (2.6.6 is now available…).

So this post assumes you have nothing, and, with a bit of luck will end up with a MySQL server running with gnucash working with it.

It also assumes you’re moderately happy with the Terminal.

It also assumes that if you fry your expensive Mac doing this, it’s not my fault…

Seriously

Don’t waste your time!

OK then…

Not sure what your home directory is?

  • In terminal, type whoami. This will give you the ‘short name’ of your Mac’s user account. e.g., bobjeffries.
  • Your home directory will most likely be /Users/bobjeffries
  • In terminal, type ls /Users/[bobjeffries] and you should see the usual list of folders (Documents, Downloads, etc.). Wherever you see bobjeffries below, replace it with your actual value.

I started here: http://wiki.gnucash.org/wiki/MacOSX/Quartz#Building_Libdbi_Drivers which is the framework method you’re following. What you’ll quickly find is a lot of the dependencies you have to get in process. So I’ve more or less listed it in order.

  1. Install Gnucash (duh)
  2. Make sure Xcode is up to date. (Do this via the Mac App Store.)
  3. Install AMP on your Mac. This is the easy bit. I used this: http://coolestguidesontheplanet.com/get-apache-mysql-php-phpmyadmin-working-osx-10-10-yosemite/ but ultimately it was the root of my problem. (More on that later.)
  4. Install Perl/CPAN/DBD::MySQL. This was harder, but again just follow a guide: http://bixsolutions.net/forum/thread-8-page-2.html
  5. Install some more tools: http://www.jattcode.com/installing-autoconf-automake-libtool-on-mac-osx-mountain-lion/
  6. And yet more: https://wiki.gnome.org/action/show//Projects/GTK+/OSX/Building?action=show&redirect=GTK%2B%2FOSX%2FBuilding. Stop after: jhbuild build meta-gtk-osx-core
  7. Download the mysql-connector files from: http://dev.mysql.com/downloads/connector/c/. DOWNLOAD THE x86 32-bit version!! put these somewhere easy to find like your home directory, e.g. /Users/bobjeffries/mysql-connector
  8. Now for the fun bit: building the MySQL drivers. You have to jump through a million hoops to get this working as you need to install and build a builder. (From all the reading I’ve done, according to people who know a hell of a lot more than I do, this is possible in other ways. I’m sure it is.) Anyway, go here: http://wiki.gnucash.org/wiki/MacOSX/Quartz#Building_Libdbi_Drivers. In the section ‘Preliminaries’ download the linked file: .jhbuildrc-custom and save it in your home directory.

Other note In case you didn’t know, files that start with a . in Unix file systems will be hidden.

  1. Now edit the file you just created. Since it’s hidden, we’ll do this using nano in the terminal. Type cd /Users/bobjeffries<br /> sudo nano .jhbuildrc-custom

You will get prompted for your password (enter it) and then a file should open up. You need to change a few things here.

  • setup_sdk(“10.10”, “10.10”,[“i386”])
  • Insert this above the section that talks about MySQL

module_autogenargs['libdbi-drivers']='--with-mysql --disable-docs --with-mysql-incdir="/Users/bobjeffries/mysql-connector/include" --with-mysql-libdir="/Users/bobjeffries/mysql-connector/lib"'

(Make sure that matches where you actually saved those files.)

  1. Uncomment and update the 4 lines about MySQL: _gc_module=modules[-1]<br /> modules[-1]="mysql"<br /> modules.append(_gc_module)<br /> module_extra_env["mysql"] = { "CMAKE_INSTALL_PREFIX": prefix }<br /> append_autogenargs("libdbi-drivers", "--with-mysql --with-mysql-incdir=/Users/bobjeffries/mysql-connector/include --with-mysql-libdir=/Users/bobjeffries/mysql-connector/lib")

I have a feeling that last line duplicates the step we did earlier. Probably does.

  • Press CTRL + X, then “y” to save and exit the file.
  1. I think we’re now ready. In terminal type jhbuild build libdbd-drivers

This should initially create a folder called gnucash-stable in your home directory. If this process fails (complaining about libtoolise being required), then CTRL + C to get out and in the gnucash-stable folder, make a directory called bin. Then copy libtoolise in to it. Libtoolise will be wherever you installed libtool earlier: most likely in a folder called libtool-x.x.x in your home directory. With that done, issue the previous command again.

  1. With a bit of luck, everything will complete. (I did hit a number of roadblocks along the way; afraid only lots of googling helped me through it.)
  2. If all went well, then in /Users/bobjeffries/gnucash-stable/lib/dbd/ should be a file called libdbdmysql.so. We’re now going to give this to gnucash to use. Assuming you’ve put the main gnucash app in your Applications folder, find it, right click it and Show Package Contents. Then navigate to Contents > Resources > Lib > DBD and copy the file in. Also, from /Users/bobjeffries/mysql-connector/lib copy libmysqlclient.18.dylib in to the same DBD folder, so you have the .so and .dylib folder alongside each other.
  3. Finally, follow the last step on the gnucash guide about install_name_tool. I did this for both the libdbi.x.dylib file and also the libmysqlclient.18.dylib. [Note: in my case, I had libdbi.1.dylib, not 0.dylib as the gnucash website said.]

You can open gnucash from the command line, which is really helpful because it will show you warnings and errors as it loads. Just type

/Applications/gnucash.app/Contents/MacOS/gnucash

If all went well then you should now be able to save an existing set of accounts to a MySQL db.

Final notes

I was basically only able to cobble this together based on this thread: http://gnucash.1415818.n4.nabble.com/Compiling-method-and-mysql-on-OSX-td4664314.html / http://lists.gnucash.org/pipermail/gnucash-user/2013-October/050867.html and with a LOT of trial and error.

Upgrades

Gnucash 2.6.6 is now out and I’d like to upgrade. The upgrade process is basically just to copy the new version over the top of the old one, which, I assume will blow the final step of actually ‘installing’ the MySQL driver within Gnucash. I’d like to think though that as long as you hold on to the files, you should be able to just redo the last 2 steps each time you upgrade.

Root cause

As a closing thought, my main issue here was an architecture mismatch. When I downloaded MySQL (step 2 above) then this installed the x86_64 version of MySQL. I originally tried to use its source files when building the libdbd drivers. They (like many other things) will not work when mishmashed between 32 bit and 64 bit. Hence I only finally got this working by introducing Step 6 - to physically go and download the 32 bit MySQL files. Again, I’m sure there are other / better ways to do this that does not require so much stuff to happen.

Hope this was helpful - leave a comment if it worked for you (or if I’ve missed out a step - quite possible!) If you do get stuck then your best bet is to a) Google, b) ask the gnucash lists or c) comment here. I’ll try and help if I can.