MySQL Bugs: #33292: Unable to connect using SSL (mysql 5.0.51, FreeBSD 6.2, OpenSSL 0.9.7e-p1)

Bug #33292	Unable to connect using SSL (mysql 5.0.51, FreeBSD 6.2, OpenSSL 0.9.7e-p1)
Submitted:	17 Dec 2007 16:09	Modified:	7 Feb 2008 7:45
Reporter:	Christian Karg	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.51	OS:	FreeBSD (6.2-STABLE)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any
Tags:	SSL

Description:
After upgrading from 5.0.45_1 to 5.0.51, SSL connections can no longer be established:

+ Command-line "mysql ..." fails with "ERROR 2026 (HY000): SSL connection error".

+ Replication from a 5.0.45_1 master to a 5.0.51 slave fails with "[ERROR] Slave I/O thread: error connecting to master '***:3306': Error: 'SSL connection error'  errno: 2026  retry-time: 60  retries: 86400" in the slave's error log.

How to repeat:
0) Start with: FreeBSD 6.2-STABLE; port mysql50_server (5.0.51 install from ports using "make WITH_SSL=yes all install"); /etc/make.conf contains "WITH_OPENSSL_BASE=yes"; system OpenSSL 0.9.7e-p1 (i.e. not OpenSSL from ports)

1) Set up certificates as per instructions at http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html (manual procedure, move certs to /var/db/mysql/openssl/test)

2) Include in my.cnf:
[mysqld]
...
ssl-ca=/var/db/mysql/openssl/test/cacert.pem
ssl-cert=/var/db/mysql/openssl/test/server-cert.pem
ssl-key=/var/db/mysql/openssl/test/server-key.pem
...

3) make world-readable copy of test certificates:
sudo cp /var/db/mysql/openssl/test/*.pem /tmp/
sudo chmod a+r /tmp/*.pem

3) Try to connect:
mysql --ssl=1 --ssl-ca=/tmp/cacert.pem --ssl-cert=/tmp/server-cert.pem --ssl-key=/tmp/server-key.pem -u root -p****

*** BEGIN OUTPUT ***
ERROR 2026 (HY000): SSL connection error
*** END OUTPUT ***

=== OPTIONAL DIAGNOSTIC STEPS ===

4) Verify that server is running SSL:
mysql --ssl=0 -u root -p****
show variables like '%ssl%';
*** BEGIN OUTPUT ***
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| have_openssl  | YES                                        | 
| have_ssl      | YES                                        | 
| ssl_ca        | /var/db/mysql/openssl/test/cacert.pem      | 
| ssl_capath    |                                            | 
| ssl_cert      | /var/db/mysql/openssl/test/server-cert.pem | 
| ssl_cipher    |                                            | 
| ssl_key       | /var/db/mysql/openssl/test/server-key.pem  | 
+---------------+--------------------------------------------+
7 rows in set (0.00 sec)
*** END OUTPUT ***

5) recompile client and server using "--with-debug=full" as an argument to configure in the Makefile, and do:
mysql --debug='d:t:o,./mysql.trace' --ssl=1 --ssl-ca=/tmp/cacert.pem --ssl-cert=/tmp/server-cert.pem --ssl-key=/tmp/server-key.pem -u root -p****
*** BEGIN EXTRACT mysql.trace ***
| | info: IO layer change in progress...
| | >sslconnect
| | | enter: ptr: 0x805b138, sd: 4  ctx: 0x8060200
| | | >vio_blocking
| | | | enter: set_blocking_mode: 1  old_mode: 1
| | | | exit: 0
| | | <vio_blocking
| | | info: ssl: 0x80b3400 timeout: 0
| | | error: SSL_do_handshake failure
| | | >report_errors
| | | | error: OpenSSL: error:140B4090:SSL routines:SSL_do_handshake:connection type not set:/usr/src/secure/lib/libssl/../../../crypto/openssl/ssl/ssl_lib.c:1819:

| | | | error: error: error:00000005:lib(0):func(0):DH lib
| | | | info: socket_errno: 31
| | | <report_errors
| | | >vio_blocking
| | | | enter: set_blocking_mode: 1  old_mode: 1
| | | | exit: 0
| | | <vio_blocking
| | <sslconnect
| | >set_mysql_error
| | | enter: error :2026 'SSL connection error'
| | <set_mysql_error
| | error: message: 2026/HY000 (SSL connection error)
| | >end_server
*** END EXTRACT mysql.trace ***

Suggested fix:
Temporary workrounds:
1) Don't use SSL
2) Continue using 5.0.45_1

I can confirm that this issue is affecting our servers too.

I can confirm that this happens for me, too (Linux).

Patch fixing the issue (SSL handshake needs to explictly know if it's connect or accept)

Attachment: mysql-ssl.patch (application/octet-stream, text), 480 bytes.

Many thanks for writing a bug report.

I can't reproduce this behaviour.

$ ./bin/mysql --ssl=1 --ssl-ca=/usr/home/miracee/newcerts/ca-cert.pem --ssl-cert=/usr/home/miracee/newcerts/server-cert.pem --ssl-key=/usr/home/miracee/newcerts/server-key.pem

Welcome to the MySQL monitor. ...
...

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.0.51-debug
1 row in set (0.00 sec)

$ uname -a
FreeBSD myhost 7.0-CURRENT-200703 FreeBSD 7.0-CURRENT-200703 #0: Tue Mar  6 22:35:04 UTC 2007

$ openssl version
OpenSSL 0.9.8d 28 Sep 2006

I installed MySQL from source and OpenSSL from ports.

Please, consider that we don't support packages/ports that are made from the distributor. We only support the packages, that you can download from our websites.

Please, upate your OpenSSL, install MySQL 5.0.51 from our download sites and let us know, if the error still occurs.

I'm able to reproduce this problem on Debian with MySQL 5.0.51 compiled using --without-openssl and --with-yassl.

I'm also able to reproduce this;

./configure --with-vio --with-openssl

Without the patch in previous comments, we had the following matrix of connectivity success using SSL.

c\s | 45 | 51
----+----+----
45  | ok |fail
51  |fail|fail

With the patch applied, client 5.0.51 _was_ now able to connect to server 5.0.45.

However, client 5.0.51 was still not able to connect to server 5.0.51:

c\s | 45 | 51
----+----+----
45  | ok |fail
51  | ok |fail

updated version that handles server side, too (not tested; please test and share with results)

Attachment: mysql-ssl.patch (application/octet-stream, text), 1.51 KiB.

Aye, worked like a charm. 51->51 connects without any problems now.

c\s | 45 | 51
----+----+----
45  | ok | ok
51  | ok | ok

Thanks !

Arkadiusz's patch fixed our ssl problem too(5.0.56 client against 4.1.24 server).
Thanks!

I tried a lot to reproduce this.
I used MySQL 5.0.45, 5.0.51 and the 5.0.56 source tree from our source repository.
Neither with Linux nor with FreeBSD I can reproduce this behaviour.

I tried all compination of replication like:
5.0.45 as master and 5.0.51 as slave, 5.0.51 as master and slave, 5.0.51 as master and 5.0.45 as slave, and all combinations with 5.0.56 and 5.0.45.

SSL works fine with all.

I used OpenSSL 0.9.8d 28 Sep 2006.

Maybe your certifications will occur the problem.

Also most of you seems to use not supported packages.

Consider, that we don't support packages/ports that are made from the distributor.
We only support the packages and source code, that you can download from our websites.

Please update your openssl version and install our supported source code. Also check your certificates.

OpenSSL documentation says:

"SSL_do_handshake() will wait for a SSL/TLS handshake to take place. If the connection is in client mode, the handshake will be started. The handshake routines may have to be explicitly set in advance using either SSL_set_connect_state(3) or SSL_set_accept_state(3)."

Now it says "may have to be explictly set". Looks like for us we have to explictly set it [1] while you Susanne don't. Why? That requires looking into OpenSSL source code to see which case happens when.

1. See reporter error, it's clear: error: OpenSSL: error:140B4090:SSL routines:SSL_do_handshake:connection type not set:/usr/src/secure/lib/libssl/../../../crypto/openssl/ssl/ssl_lib.c:1819:

See for more:
http://www.openssl.org/docs/ssl/SSL_do_handshake.html
http://www.openssl.org/docs/ssl/SSL_set_connect_state.html#

My client is the "mysql-server-5.0.45_1" port running on FreeBSD 6.2 using only the default settings. My server is the mysql 5.0.51 source release compiled on FreeBSD 4.10  and configured with "./configure --with-openssl" (ports no longer works on FreeBSD 4.10). My server also has the "openssl-0.9.8e" port installed, but I assume the build will have used the openssl version that comes with the OS instead of the port (the client machine has no openssl port installed). Using Arkadiusz Miskiewicz patch on the server fixes all the problems for me.

Let me know if you need any more info.

regards,
Patrick

Duplicate of BUG#33050, only occurs when MySQL is compiled to use the OpenSSL library