[Bug 828036] Re: MySQL daemon keeps dying and restarting when using ssl connections
Micah Gersten
launchpad at micahscomputing.com
Sun Mar 11 08:03:22 UTC 2012
Unsubscribing ubuntu-sponsors as there's nothing to sponsor here, please
resubscribe if something needs to be uploaded.
--
You received this bug notification because you are a member of Ubuntu
Sponsors Team, which is subscribed to the bug report.
https://bugs.launchpad.net/bugs/828036
Title:
MySQL daemon keeps dying and restarting when using ssl connections
Status in “mysql-5.1” package in Ubuntu:
Confirmed
Status in “mysql-5.1” source package in Lucid:
In Progress
Bug description:
[Impact]
After running thousands of mysql queries on lucid, the mysql daemon terminates with status 1. This can impact anybody doing a large number of transactions on a system.
[Development Fix]
Backporting mysql from maverick (5.1.49) to lucid solves the issue. An effort was made to backport specific patches from maverick to lucid, but the issue was unable to be resolved.
[Stable Fix]
In order to fix this bug, a backport is requested from maverick to lucid.
[Test Case]
1. Install Lucid + updates + LAMP stack
2. Configure MySQL + SSL
3. Create SSL certificates and configure the mysql client and server to use the certificates: http://dev.mysql.com/doc/refman/5.1/en/secure-create-certs.html
Add ssl options to /etc/mysql/my.cnf on the Server:
In the section [mysql] add:
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem
On the client:
In the section [client] add:
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem
4. Test the SSL connection using mysql command line:
# mysql --ssl -p
show variables like “%ssl%” (value should be “YES” for “have_openssl”)
\s (output should include “SSL: Cipher in use is....”)
5. Force the mysql user account for the database driven website to use SSL:
update user set ssl_type=’X509’ where user=’USERNAME’; flush privileges;
6. Bang the server using ab with a client script like the one below
ab
lborda at bordalnx:~/dev$ cat bb.php
<?php
error_reporting(E_ALL);
ini_set("display_errors", "1");
$obj = mysqli_init();
mysqli_options($obj, MYSQLI_OPT_CONNECT_TIMEOUT, 5);
mysqli_ssl_set( $obj,
'/etc/mysql/client-key.pem',
'/etc/mysql/client-cert.pem',
'/etc/mysql/ca-cert.pem',
NULL,
NULL);
$link = mysqli_real_connect($obj, '10.153.107.215', 'root', 'ubuntu', 'drupal1');
if (!$link)
{
die('<br /><br />Connect Error (' . mysqli_connect_errno() . ') '.mysqli_connect_error());
}
echo 'Success... ' . mysqli_get_host_info($obj) . "\n";
$obj->close();
?>
[Regression Potential]
There is potential since this is a backport.
--
On a server dedicated as a MySQL database server, MySQL keeps randomly "terminating with status 1" (from /var/log/daemon.log) and then immediately starting again.
MySQL-Server version 5.1.49 seems to include the fix as per this bug entry: http://bugs.mysql.com/bug.php?id=34236
Here are some timestamps from daemon.log from when Mysql terminated:
Jul 4 12:53:28 lightning init: mysql main process (9796) terminated with status 1
Jul 4 22:28:31 lightning init: mysql main process (8123) terminated with status 1
Jul 5 04:03:13 lightning init: mysql main process (20731) terminated with status 1
MySQL Error logs describe the following stack trace. probably due to the use of yaSSL when configuring mysql to use ssl.
http://bugs.mysql.com/bug.php?id=34236
*** PS: The server uses SSL connections through MySQL ***
110721 11:17:02 - mysqld got signal 11 ;
key_buffer_size=524288000
read_buffer_size=131072
max_used_connections=13
max_threads=151
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 842111 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x7f1d3406c780
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f1d3be68e58 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x7f1d64bb51a9]
/usr/sbin/mysqld(handle_segfault+0x404) [0x7f1d648c2884]
/lib/libpthread.so.0(+0xf8f0) [0x7f1d641338f0]
/usr/sbin/mysqld(yaSSL::SSL_SESSION::GetBornOn() const+0) [0x7f1d64bf1170]
/usr/sbin/mysqld(yaSSL::Sessions::Flush()+0x35) [0x7f1d64bf28e5]
/usr/sbin/mysqld(yaSSL::sendFinished(yaSSL::SSL&, yaSSL::ConnectionEnd, yaSSL::BufferOutput)+0x5f0) [0x7f1d64bff1d0]
/usr/sbin/mysqld(yaSSL_accept+0x114) [0x7f1d64be5194]
/usr/sbin/mysqld(+0x636f60) [0x7f1d64b99f60]
/usr/sbin/mysqld(+0x369ad6) [0x7f1d648ccad6]
/usr/sbin/mysqld(handle_one_connection+0x135) [0x7f1d648cd545]
/lib/libpthread.so.0(+0x69ca) [0x7f1d6412a9ca]
/lib/libc.so.6(clone+0x6d) [0x7f1d62c6070d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil) is an invalid pointer
thd->thread_id=42360
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
110721 11:17:02 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
110721 11:17:02 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
Package information:
ii mysql-server 5.1.41-3ubuntu12.10 MySQL database server (metapackage depending
ii mysql-server-5.1 5.1.41-3ubuntu12.10 MySQL database server binaries
ii mysql-server-core-5.1 5.1.41-3ubuntu12.10 MySQL database core server files
Reproducible: No, it is a random behaviour. It happens after running
thousands of queries...
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/mysql-5.1/+bug/828036/+subscriptions
More information about the Ubuntu-sponsors
mailing list