More on MySQLdb on Snow Leopard

Well, my frustrations with MySQL and MacOSX continue. This time after a copmlete clean reinstall of everything I have stumbled upon the ‘old friend’ MySQLdb Python module. Apparently setting up this one causes a lot of frustration and blogging. Here is what I have found.

1. Check your platform. I have an old MacBook and naively installed 32 bit MySQL on it. Man, I was wrong. Since I wanted to use the default MacOSX Python I should have checked that one fist. You can do that by executing the following commands in the Python interactive shell:

import sys
import math
math.log(sys.maxint, 2)

It gave me… “63.0”. Wow… Apparently I do have 64-bit Python!

2. Having 64 bit Python you must install 64 bit MySQL. So go and get the proper version.

3. If you think you’re there you’re not getting my frustration yet. If you would get MySQLdb, build it now and run as I did, the following statement

import MySQLdb

will likely to result in

Traceback (most recent call last):
File "", line 1, in
File "build/bdist.macosx-10.6-universal/egg/MySQLdb/__init__.py", line 19, in
File "build/bdist.macosx-10.6-universal/egg/_mysql.py", line 7, in
File "build/bdist.macosx-10.6-universal/egg/_mysql.py", line 6, in __bootstrap__
ImportError: dlopen(/Users/oleksii/.python-eggs/MySQL_python-1.2.3-py2.6-macosx-10.6-universal.egg-tmp/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
Referenced from: /Users/oleksii/.python-eggs/MySQL_python-1.2.3-py2.6-macosx-10.6-universal.egg-tmp/_mysql.so
Reason: image not found

Ok, this was it. I had to dig around and finally found out that it is a known bug in the at least 5.5 version of MySQL, explanation and the fix can be looked up at the MySQL bugs website.

In short you have to perform the following

sudo install_name_tool -id /usr/local/mysql/lib/libmysqlclient.18.dylib libmysqlclient.dylib

where ’18’ needs to be replaced with your version.

4. Finally get the MySQLdb (I’ve got 1.2.3). Build it, make sure the proper architecture is used. The architecture can be enforced with e.g. the following commands:

export VERSIONER_PYTHON_PREFER_64_BIT=yes

(or the coresponding one for 32 bits)

Wow, finally import worked as I had expected. WTFx10…

…and all this after several hours debugging for a problem that existed for 10+ years and appeared due to changes in the new Visual Studio compiler optimizations… great… another sleepless night…

MySQL and SSL: ERROR 1045 (28000): Access denied for user…

Ok, this one was just so frustrating I cannot keep it to myself. It costs so much time and nerves to realize that you were pointed in all but the right direction after all.

So here it goes. I have added a user to the MySQL database, I require the SSL connection (actually X509) and it all works on my development system (MacOSX 10.4). So I naively didn’t expect any problems on the test server running Ubuntu. I was wrong. And my mistake apparently has a name… But not all.

So what I see is that when I try to connect to the DB using either the Python/MySQLdb or giving the command line is the same:

_mysql_exceptions.OperationalError: (1045, "Access denied for user
'xxx'@'localhost' (using password: YES)")

or

ERROR 1045 (28000): Access denied for user 'xxx'@'localhost' (using password: YES)

Great. Now what?

I tried every single thing that made sense to me:

  • Re-created the user, updated the privileges and even added
    FLUSH PRIVILEGES
    at the end (never needed it before).
  • Re-generated the certificate files
  • Different DB

Ok, after all it boiled down to the following messages in the dmesg output:

[877463.513737] audit(1263600950.291:21): type=1503 operation="inode_permission"
requested_mask="r::" denied_mask="r::" name="/xxx/xxx/certificates/server-cert.pem"
pid=11840 profile="/usr/sbin/mysqld" namespace="default"

Bingo! MySQL cannot read the f…g certificate? Why-y-y-y-y? Apparently because the
apparmor
(now I know the enemy’s name!) does not allow it. And this is all because I have installed certificates to the ‘non-standard’ folder. Well, adding the following line to the

/etc/apparmor.d/usr.sbin.mysqld
/xxx/xxx/certificates/*.pem r,

and then issuing the following commands:

#apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld 

#sudo /etc/init.d/mysql restart

finally put everything on their places.

2 hours, a lot of frustration, but it seem to work now. I hope you spend less time finding this in Google :).

Have a nice weekend!

CherryPy + MySQLdb

After hours of searching and hitting the walls with the following error:

File "build/bdist.macosx-10.3-i386/egg/MySQLdb/__init__.py", line 19, in
File "build/bdist.macosx-10.3-i386/egg/_mysql.py", line 7, in
File "build/bdist.macosx-10.3-i386/egg/_mysql.py", line 6, in __bootstrap__
ImportError: dlopen(/Users/oleksii/.python-eggs/MySQL_python-1.2.2-py2.5-macosx-10.3-i386.egg-tmp/_mysql.so, 2): image not found

I finally decided just to look on what actually was in the .python-eggs folder. Rrrriiight:

-rw------- 1 oleksii oleksii 123024 Sep 20 2008 _mysql.so

Isn’t this just beautiful? I don’t get why it is created under my user anyway while Apache2 is running under different user, but since the created library was only readable by me myself… Well, Apache just couldn’t get to it. Damn. What a loss of time (simply not mentioning all previous frustrations with installs, etc).

MySQL TIMESTAMP

Original date: 2008-12-27

Apparently using the TIMESTAMP fields in the MySQL the MySQLdb library in python results in the datetime.datetime() objects. A valid value can be set by providing a valid datetime.datetime instance.