SSL connection error: protocol version mismatch

To make this really complete... Here is the cert.

-----BEGIN CERTIFICATE-----
MIIDyzCCArOgAwIBAgIJAJuwzOnesbogMA0GCSqGSIb3DQEBBQUAMHwxCzAJBgNV
BAYTAlVTMREwDwYDVQQIDAhPa2xhaG9tYTEWMBQGA1UEBwwNT2tsYWhvbWEgQ2l0
eTEUMBIGA1UECgwLZ3Jlbm5hbi5jb20xCzAJBgNVBAMMAmRiMR8wHQYJKoZIhvcN
AQkBFhBtYXJrQGdyZW5uYW4uY29tMB4XDTEyMDUzMTE1NTk0OVoXDTE1MDIyNTE1
NTk0OVowfDELMAkGA1UEBhMCVVMxETAPBgNVBAgMCE9rbGFob21hMRYwFAYDVQQH
DA1Pa2xhaG9tYSBDaXR5MRQwEgYDVQQKDAtncmVubmFuLmNvbTELMAkGA1UEAwwC
ZGIxHzAdBgkqhkiG9w0BCQEWEG1hcmtAZ3Jlbm5hbi5jb20wggEiMA0GCSqGSIb3
DQEBAQUAA4IBDwAwggEKAoIBAQDJ0Qyn5JuoBJt5b6+I1BNDBKdb4rMolJB5x0Pv
ZmOC0B5Q4dluYz41KHhgQf/+zkudvnH1P8zFMPS908RZAC09/uZpGHbfGq/ZYBd3
qcnkFVePAPoyChGQc9OW8vv5jvNVCpsV/6gfL27+RkuT6axFR5PNzpPAuGGJ5QyY
2O9k0/zYXbiAj0aQXaLe3UhUI0JGC34Tc8kjCw3Ml1Vqq8jmeSgT+mlw1T5c1hfD
1+mD8E/ErPIbbqU5yuFevF+ENxZste2m2Xa92V3Oe3A5gM53+xsJ/171uNiS6Fuy
Labbdgp4y1asun8Zl64KmBFDw/doGf8pggwagRO7REdMn8YPAgMBAAGjUDBOMB0G
A1UdDgQWBBRHYMkqlq9Qp02WU8oi6mCMu/Ut8zAfBgNVHSMEGDAWgBRHYMkqlq9Q
p02WU8oi6mCMu/Ut8zAMBgNVHRMEBTADAQH/MA0GCSqGSIb3DQEBBQUAA4IBAQB7
MZivAO6l2XhPvz6hE6XZgmZg9LVQU0gn7AG6QZWaPFRuM9uRqY2E2ymWBKn+n0vy
m0cY03MewFTbDBNOdlfRdUJ7oiKZuxL9a/eEcipME7CBINKZzcYS3JCCqqjrE04v
FUZhc3jHBhN0y0lMT9whC+sByDmmtO2c6E99Bmurgp4KHSvPRUScmV/S/tEoHxuO
tAeyZ4n4431Rt03TTtTOzF3tPj1VjTIBgENmLMbgjevcDAQ6kPeHTMkL9u2mKpxd
7o3HOus5/ArbiF+Q7RSFK6sReTqG4TDyuEGK3MfD8Ugqfpcb2I9Iwf5pf0M0BSnY
F4K47ha0tZkWX1eGoI78
-----END CERTIFICATE-----

Hi,

I can confirm the same. It looks like mysql cannot handle certificate files generated with openssl version newer than 0.9.*. Old version of openssl (from CentOS 5.8) produced certificates that worked.

Regards

Is this failure Percona Server-specific or does MySQL 5.5.23 fail too in the same way?

This issue also occurs in MySQL 5.5.23

The upstream bug has not been verified so far. We don't have a reproducible procedure either. Which means the correct status is New, not Confirmed/Triaged.

Tested and works fine. (both mysql and PS)

>>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.27-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant ALL on *.* to 'testuser'@'localhost' identified by 'test' require ssl;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

>>mysql -u testuser -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.27-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.5.27, for Linux (x86_64) using readline 5.1

Connection id: 6
Current database:
Current user: testuser@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: less -R -i -M -s -j.5 -J -W -z-5
Using outfile: ''
Using delimiter: ;
Server version: 5.5.27-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 10 min 30 sec

Threads: 1 Questions: 30 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.047
--------------

Generation:

I generated certificates as follows: (thanks to http://serverfault.com/a/399581)

openssl req -x509 -newkey rsa:1024 \
-keyout server-key-enc.pem -out server-cert.pem \
-subj '/DC=com/DC=example/CN=server' -passout pass:qwerty

openssl rsa -in server-key-enc.pem -out server-key.pem \
-passin pass:qwerty -passout pass:

openssl req -x509 -newkey rsa:1024 \
-keyout client-key-enc.pem -out client-cert.pem \
-subj '/DC=com/DC=example/CN=client' -passout pass:qwerty

openssl rsa -in client-key-enc.pem -out client-key.pem \
-passin pass:qwerty -passout pass:

cat server-cert.pem client-cert.pem > ca.pem

with configuration being:

ssl-ca=/qemu/share/certs/ca.pem
ssl-cert=/qemu/share/certs/client-cert.pem
ssl-key=/qemu/share/certs/client-key.pem

under [client]

and

ssl-ca=/qemu/share/certs/ca.pem
ssl-cert=/qemu/share/certs/server-cert.pem
ssl-key=/qemu/share/certs/server-key.pem

under [mysqld]

===============================

The key (as also mentioned in serverfault link) is to note that the certs are self-signed and follow procedure for them (like -x509 to req)

Make sure that the Common Name (CN) of the Server certificate does NOT match that of the Client, which is hinted in #6 with "CN=client" and "CN=server"

This bug still exists.
Upstream bug report:
http://bugs.mysql.com/bug.php?id=64870

However the workaround doesn't work form me.

Works fine with certificates created by openssl 0.9.8o from debian squeeze.

The official guide (http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html) has -set_serial 01 for both server and client certificates, that is the reason why the issue appears.

use -set_serial 02 for the client certificate, and everything should be good. Make sure the output of the check command is the same as in the howto (openssl verify ...).

All reporters,

Do you agree that with certificates properly created this problem is not repeatable?

If you tried everything, but SSL is not working, and at the same time you're running mysqld in chroot, then the cause for the errors like:

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

or

ERROR 2026 (HY000): SSL connection error: protocol version mismatch

could be that you forgot to create dev/random and dev/urandom devices in the chroot environment (and openssl lib can't obtain entropy - it opens these devices *after* chroot). You can do that this way (replace /srv/mysqld with your chroot dir and mysqld with the user mysqld is running under):

sudo install -d -o mysqld -g mysqld -m 500 /srv/mysqld/dev
sudo mknod -m 444 /srv/mysqld/dev/random c 1 8
sudo mknod -m 444 /srv/mysqld/dev/urandom c 1 9

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2759