Database connection errors

The API formerly known as Tera-WURFL
digitalronin
Posts: 13
Joined: Tue Jul 05, 2011 10:18 am

Database connection errors

Postby digitalronin » Tue May 22, 2012 4:17 am

Hi there

I've just updated to the 1.4 API. Mostly, it's working fine, but every so often (maybe every 15 minutes or so) I get a flurry of these errors;

PHP Fatal error: Uncaught exception 'TeraWurflDatabaseException' with message 'Cannot connect to database: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock

When I look on the DB server, it doesn't seem to have anywhere near max_connections in use, at any given moment.

The errors occur in the constructor of TeraWurfl.php, at line 153.

Do you know what might cause this kind of behaviour, and if there's an easy fix?

Kind regards

David

kamermans
Posts: 393
Joined: Mon Jun 06, 2011 9:50 am

Re: Database connection errors

Postby kamermans » Tue May 22, 2012 1:07 pm

Hi David,

This may have something to do with persistent connections or something. I'll take a look at how we can troubleshoot this. Meanwhile, we have also added support for PDO MySQL, which is what we use internally. If you have this module on your server, you may want to take a look at that and see if the problem persists, or if it's specific to the MySQLi connector. Note that the connection string for PDO is different - you can look at the example config file and the Database API FAQ for inspiration.
Thanks,

Steve Kamerman
ScientiaMobile

Make sure you check out our WURFL Cloud, WURFL InSight and WURFL InFuze products!

digitalronin
Posts: 13
Joined: Tue Jul 05, 2011 10:18 am

Re: Database connection errors

Postby digitalronin » Wed May 23, 2012 4:32 am

Hi Steve

Thanks, but unfortunately switching to the PDO connector doesn't help. Just as with the mysql connector, it works most of the time, but every so often I get a flurry of these errors;

PHP Fatal error: Uncaught exception 'TeraWurflDatabaseException' with message 'Cannot connect to database: SQLSTATE[HY000] [2003] Can't connect to MySQL server on '127.0.0.1' (111)

David

kamermans
Posts: 393
Joined: Mon Jun 06, 2011 9:50 am

Re: Database connection errors

Postby kamermans » Wed May 23, 2012 8:51 am

This is very strange since it's the MySQL client driver itself that is telling you it can't connect. My only guesses at this point are that this may have been happening in the previous version and you didn't realize it due to less error handling (in our code); or the new version is leaving connections to MySQL open for whatever reason and you are bumping up against the max connections limit. You can try to disable persistent connections, but they are there to mitigate the problem of too many open connections. To disable them, you can change the connector's PERSISTENT_CONNECTION static property:

Code: Select all

require_once 'TeraWurfl.php';

// For MySQLi - note that they are only enabled if you have PHP 5.3.0+
TeraWurflDatabase_MySQL5::$PERSISTENT_CONNECTION = false;

// For PDO
TeraWurflDatabase_PDO::$PERSISTENT_CONNECTION = false;

$wurfl = new TeraWurfl();
You can also specify a UNIX domain socket for MySQL in your config instead of a TCP address, although there's really very little difference between a domain socket and a localhost TCP Socket.

Code: Select all

// This is in TeraWurflConfig.php
// MySQLi
public static $DB_HOST = "localhost:/var/run/mysqld/mysqld.sock";

// PDO
public static $DB_HOST = "mysql:unix_socket=/var/run/mysqld/mysqld.sock;dbname=testdb";
Thanks,

Steve Kamerman
ScientiaMobile

Make sure you check out our WURFL Cloud, WURFL InSight and WURFL InFuze products!

digitalronin
Posts: 13
Joined: Tue Jul 05, 2011 10:18 am

Re: Database connection errors

Postby digitalronin » Thu May 24, 2012 7:44 am

Hi Steve

Neither of those changes seemed to help, but I did find something in a comment on the PHP docs;

http://www.php.net/manual/en/mysqli.close.php
The mysqli_close() function and object method only remove the ability to use the mysql object again in the script, essentially on the surface only nulling out all object parameters.

At least with PHP5.3.2 and Windows connecting by tcp, this differs by the old mysql_close() function in that it does not actually close out the tcp socket being used. You should always use mysqli_kill() function before mysqli_close() to actually close and free up the tcp socket being used by PHP.
So, I added this before the call to dbcon->close in the destructor of TeraWurflDatabase_MySQL5.php

Code: Select all

@$this->dbcon->kill(@$this->dbcon->thread_id());
...and it seems to have done the trick.

David

digitalronin
Posts: 13
Joined: Tue Jul 05, 2011 10:18 am

Re: Database connection errors

Postby digitalronin » Thu May 24, 2012 8:28 am

Actually, although that seems to fix the DB connection problem, it also appears to slow things down so much that the system becomes unworkable. So, I've had to roll back that change.

David

kamermans
Posts: 393
Joined: Mon Jun 06, 2011 9:50 am

Re: Database connection errors

Postby kamermans » Thu May 24, 2012 10:03 am

Does this seem very periodic, like exactly every 15 minutes or so? Is there a cron script running periodically that is eating up all the file descriptors, so PHP can't get a handle on the socket/TCP port? Do you see anything in your other logs during this time, like in the MySQL logs, syslog/messages? I would expect to see some processes complaining about not being to open files, etc, during this timeframe if this is a file descriptor problem. Also, you can use "cat /proc/sys/fs/file-nr" to see the number of allocated file descriptors - the first number (allocated descriptors) should be far less (probably > 10x less) then the last number (max descriptors).
Thanks,

Steve Kamerman
ScientiaMobile

Make sure you check out our WURFL Cloud, WURFL InSight and WURFL InFuze products!

digitalronin
Posts: 13
Joined: Tue Jul 05, 2011 10:18 am

Re: Database connection errors

Postby digitalronin » Tue Jun 05, 2012 11:46 am

I think we've found the problem.

A security update to ruby included a change in how it hashes strings. We were using the hashing function to create unique server ids for our mysql instances.

After the ruby update, it seems that string hashes are no longer consistent from one run to the next, so the mysql server-id value was being changed every time Puppet ran. Because the server-id had changed, Puppet was restarting mysql - hence the lost connections.

An obscure side-effect, and nothing to do with TeraWURFL.

Thanks for looking into it, and sorry if I wasted any of your time.

David

kamermans
Posts: 393
Joined: Mon Jun 06, 2011 9:50 am

Re: Database connection errors

Postby kamermans » Tue Jun 05, 2012 12:19 pm

Hi David, no problem, we're always happy to help and I'm glad you were able to get to the bottom of this tricky problem!
Thanks,

Steve Kamerman
ScientiaMobile

Make sure you check out our WURFL Cloud, WURFL InSight and WURFL InFuze products!


Who is online

Users browsing this forum: No registered users and 1 guest