SSL-connection to MySQL, but how?

[bicpi]

Hi, i trie to setup a SSL connection to my mySQL-Server. The mySQL-Server supports SSL-connections (have_openssl = YES). For the certificates i followed the steps on the mysql.com-Website

Thats how my program looks like:

!/usr/bin/env python

import MySQLdb

ssl_test = {}
ssl_test['ca'] = '/root/cacert.pem'
ssl_test['capath'] = '/root'
ssl_test['cert'] = '/root/client-cert.pem'
ssl_test['key'] = '/root/client.key'

verbindung = MySQLdb.connect(host='192.168.0.103', \
user='bicpi', \
passwd='0661Bic', \
db='bicpi', \
port=3306, \
compress=1, \
ssl=ssl_test)
c = verbindung.cursor()
c.execute('SELECT * from test_db')
erg = c.fetchall()
print erg

But i get the following error:

Traceback (most recent call last):
File "/root/mysql_ssl.py", line 18, in -toplevel-
ssl=ssl_test)
File "/usr/lib/python2.3/site-packages/MySQLdb/init.py", line 66, in Connect
return Connection(args, kwargs)
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 134, in init
super(Connection, self).init(args, **kwargs2)
NotSupportedError: client library does not have SSL support

I installed the python-openssl-package, but it still don't works.

Whats the problem here?

Thx a lot
bicpi

[Andy Dustman]

Your MySQL client libraries were not built with SSL support. Try this command on your system (paths and versions may vary; example is on Gentoo):

ldd /usr/lib/mysql/libmysqlclient.so

    libz.so.1 => /lib/libz.so.1 (0x40048000)
    libcrypt.so.1 => /lib/libcrypt.so.1 (0x4005a000)
    libnsl.so.1 => /lib/libnsl.so.1 (0x40089000)
    libm.so.6 => /lib/libm.so.6 (0x4009e000)
    libssl.so.0.9.7 => /usr/lib/libssl.so.0.9.7 (0x400c1000)
    libcrypto.so.0.9.7 => /usr/lib/libcrypto.so.0.9.7 (0x400f2000)
    libc.so.6 => /lib/libc.so.6 (0x401f5000)
    /lib/ld-linux.so.2 (0x80000000)
    libdl.so.2 => /lib/libdl.so.2 (0x4030c000)

If you don't see libssl and libcrypto, then your MySQL client library needs to be rebuilt with SSL enabled. (Example assumes dynamic linking.)

python-openssl will not help you in this case.

[bicpi]

Thx.
Thats what ldd shows (SuSE 9.3):
spaceweb:~ # ldd /usr/lib/mysql/libmysqlclient.so
linux-gate.so.1 => (0xffffe000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x4011b000)
libnsl.so.1 => /lib/libnsl.so.1 (0x4014e000)
libm.so.6 => /lib/tls/libm.so.6 (0x40163000)
libz.so.1 => /lib/libz.so.1 (0x40186000)
libc.so.6 => /lib/tls/libc.so.6 (0x40197000)
/lib/ld-linux.so.2 (0x80000000)

So, no SLL-support.
How can I rebuild my client libriries, i never did that before?

Thx a lot,
bicpi

[Andy Dustman]

If you built MySQL yourself, you need to read the installation instructions to figure out how to configure it. Otherwise, you may need to check with your vendor (SuSE or MySQL, depending on what packages you are using).

[bicpi]

I built the mysql-server by myself now and ldd says:

ldd /usr/local/mysql/lib/mysql/libmysqlclient.so

    libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00002aaaaac36000)
    libnsl.so.1 => /lib64/libnsl.so.1 (0x00002aaaaad6f000)
    libm.so.6 => /lib64/tls/libm.so.6 (0x00002aaaaae85000)
    libssl.so.0.9.7 => /usr/lib64/libssl.so.0.9.7 (0x00002aaaaafdd000)
    libcrypto.so.0.9.7 => /usr/lib64/libcrypto.so.0.9.7 (0x00002aaaab112000)
    libz.so.0 => /usr/local/mysql/lib/mysql/libz.so.0 (0x00002aaaab345000)
    libc.so.6 => /lib64/tls/libc.so.6 (0x00002aaaab45a000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00002aaaab680000)
    libpthread.so.0 => /lib64/tls/libpthread.so.0 (0x00002aaaab783000)
    /lib64/ld-linux-x86-64.so.2 (0x0000555555554000)

so there is libssl and libcrypto. But i get still the same error-message in my Python-program:
"NotSupportedError: client library does not have SSL support"

The Python application runs on my Webserver and the database is on another machine.
Perhaps mysqldb is the problem, do i have to give some special-options when building mysqldb? I followed the steps in the README-file, even with "export mysqloptlibs='ssl crypto' "

What else is to do to get the SSL-support?

Thx

[Andy Dustman]

OK, repeat after me: Client library

You rebuilt on the database server, which may or may not have been necessary. But the problem is, "client library does not have SSL support". So must build MySQL with SSL support on your webserver.

[bicpi]

Thanks a lot for your help.

I now built mySQL with SSL-support on my webserver. If i run my python-script now, i get another error:

Traceback (most recent call last):
File "/root/mysql_ssl.py", line 3, in -toplevel-
import MySQLdb
File "/usr/lib/python2.4/site-packages/MySQLdb/init.py", line 27, in -toplevel-
import _mysql
ImportError: libmysqlclient_r.so.14: cannot open shared object file: No such file or directory

How can Iget libmysqlclient_r.so to avoid this import error?

[Andy Dustman]

First try rebuilding MySQL-python, since that's quick and easy. However, it is not finding the shared object version of the client library. It may not be on the system loader path, or you might not have built MySQL with shared library support, or maybe did not build the thread-safe library support.

README will tell you how to build a static version of MySQLdb (actually _mysql.so), if you so choose.

[bicpi]

No idea?

[bicpi]

Ok, i rebuilt mySQLdb, it was missing the myaql-devel-package ;-)

But after same, the same error appears, client library does not have SSL support.

...

[Andy Dustman]

Yeah, and mysql-devel probably has the libraries with SSL support. You probably need to get the SuSE source RPM (or .spec) and tinker with the configuration section to turn on SSL and use that on your servers.

Poking around rpmfind.net, it doesn't look like SuSE actually makes packages for MySQL-4.1, but the 4.0 packages do not appear to have SSL support.

If you are building it all yourself, you may have better luck using configure --prefix=/usr Aside from that, there are too many variables for me to guess at what's wrong.

[bicpi]

I can't rebuild my MySQLdb, i get an endless list of errors, looks like this

python setup.py build

...
...
...
> _mysql.c: In function _mysql_ResultObject_setattr': > _mysql.c:2360: error:v' undeclared (first use in this function)
-bash: syntax error near unexpected token (' spaceweb:~/Software/MySQL-python-1.2.0 # _mysql.c:2371: error:name' undeclared (first use in this function)
> _mysql.c:2372: error: self' undeclared (first use in this function) > _mysql.c: At top level: > _mysql.c:172: warning:_mysql_server_init__doc__' defined but not used
-bash: command substitution: line 1: unexpected EOF while looking for matching '' -bash: command substitution: line 3: syntax error: unexpected end of file -bash: _mysql.c:2371:: command not found spaceweb:~/Software/MySQL-python-1.2.0 # _mysql.c:183: warning:_mysql_server_init' defined but not used
> _mysql.c:267: warning: _mysql_server_end__doc__' defined but not used > _mysql.c:273: warning:_mysql_server_end' defined but not used
-bash: command substitution: line 1: unexpected EOF while looking for matching `''
-bash: command substitution: line 3: syntax error: unexpected end of file
-bash: _mysql.c:183:: command not found
spaceweb:~/Software/MySQL-python-1.2.0 # error: command 'gcc' failed with exit status 1

Then i tried with the mySQLdb-package from my SuSE-distribution and the old error reappeared (Client library does not have SSL-support).

[Andy Dustman]

I suspect the first error message is something about mysql.h not being found. It's pretty weird that your bash prompt is interspersed throughout the output.