Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
MySQL Connection Errors
  • Hello,

    We have a Fuel based server application that is a few days from a very big launch.  We are running a load test right now.  

    Once we get past a certain threshold we start to get Database errors.  When we use the MySQLi driver, they look like this: [2006] MySQL server has gone away

    Following on from a suggestion you made here I switched to the PDO driver.  At about the same point, I start getting: Error - invalid data source name.  I've verified that the configuration it is attempting to load is valid (one of the queries showing the error includes a looked up value using the same connection).

    The database itself does not appear to be under strain.

    Below a certain threshold all queries succeed, after that, error city.  Any ideas?

    Thanks in advance - Michael




  • FYI Here's the stack for the PDO exception:

    Reason: /var/www/deployed/services-933/fuel/core/classes/database/pdo/connection.php 94: invalid data source name


    Source: invalid data source name


    #0 /var/www/deployed/services-933/fuel/core/classes/database/pdo/connection.php(199): Fuel\Core\Database_PDO_Connection->connect()


    #1 /var/www/deployed/services-933/fuel/core/classes/database/query.php(256): Fuel\Core\Database_PDO_Connection->query(1, 'SELECT * FROM s...', false)


    #2 /var/www/deployed/services-933/fuel/app/classes/model/session.php(148): Fuel\Core\Database_Query->execute()


    #3 /var/www/deployed/services-933/fuel/app/classes/model/accesspoint.php(297): Model_Session::findByPrimaryKey('332017', 'AAAAAFKD1eI=')

    ...


    And here is the calling code: 


    $parameters = array(

        'access_point_id' => $access_point_id,

        'session_id'      => $session_id

     );

    $qry = \Fuel\Core\DB::query('

        SELECT * FROM session 

        WHERE access_point_id=:access_point_id AND id=:session_id');

    $rs = $qry->parameters( $parameters )->execute();

    and just a side note that pk is the auto increment value and defined like so in the model:

        protected static $_primary_key = 'pk';

  • You get this error because of a timeout, either on the PHP side, the network level (only applicable if MySQL is not running on the same server as the application), or in the MySQL engine itself.

    For PHP:
    - check the mysql.connect_timeout in your php.ini

    For the network:
    - can only be found using a sniffer (for example wireshark)

    For MySQL:
    - check the default_socket_timeout in the my.cnf
    - see http://dev.mysql.com/doc/refman/5.7/en/gone-away.html

    It doesn't really matter which driver you use, any recent PHP version will use the mysqlnd driver underneath.

    What worries me a bit is that the PDO driver gives you "invalid datasource name", which indicates it can't even connect to the database at that point (and assumes your DB configuration is wrong).

    Which means you should also look at the MySQL configuration in detail. Does it hit a connection limit? Are connections dropped? What is the configured connection timeout? and the wait timeout? A high wait timeout can cause you to run out of connections because old ones remain in a WAIT state.

    So, thousand and one possible reasons...
  • Harro, Thanks for getting back to me so promptly...

    No, connection limit is not being hit, although there are a lot of idle connections when I look.

    Not sure if the connections are being dropped.

    Here are my settings:
    php.ini

    [MySQL]
    mysql.allow_persistent = On
    mysql.max_persistent = -1
    mysql.connect_timeout = 60

    [MySQLi]
    Same as above but there isn't a connect_timeout setting

    Right now I'm experimenting with turning persistence off entirely to do an end run around the problem, at least temporarily.  

    I'm curious about the relationship between drivers and ini settings...
    Do you happen to know of the PDO driver uses any of the mysql[i] settings?
  • Oh, and here are my mysql configs for timeouts (it is not hosted on the same machine)

    connect_timeout: 30
    interactive_timeout: 30
    wait_timeout: 30

    Also, do you happen to know if pdo connection defaults to interactive or not?

  • HarroHarro
    Accepted Answer
    interactive_timeout : interactive time out for mysql shell sessions in seconds like mysqldump or mysql command line tools.

    wait_timeout” : the amount of seconds during
    inactivity that MySQL will wait before it will close a connection on a
    non-interactive connection in seconds.

    So it's the connect_timeout and the wait_timeout that is relevant.

    The longer the wait timeout, the longer it takes for inactive connections to be dropped. But also the sooner you get "server away" messages if you have long(er) running processes.

    For interactive applications, it's usually the connect timeout that is the culprit. A timeout of 30 means EVERY page request needs to be finished and cleaned up within 30 seconds, otherwise the server will drop the connection (causing a "2006 server has gone away").
  • We've applied the following settings and still get connection errors.  
    connect_timeout: 90
    wait_timeout: 10

    And php.ini settings:
    [MySQLi]
    mysqli.max_persistent = 100
    mysqli.allow_persistent = On
    mysqli.max_links = -1
    mysqli.cache_size = 2000
    mysqli.reconnect = On

    With and without persistent connections.  On one occasion it definitely was caused by too many connections (restarting db sorted things out quickly), however the latest problem occurs below the connection limit and is again limited to a couple of app servers (out of 8) even after stopping and starting the server.  Which is odd because we've got push button deployment though jenkins and the code is the same on all servers.  Can you think of anything about the db connection that might change over time and be cached?

    I'm trying to go back to persisting connections to monitor how they do.  

    Any additional thoughts you have are much appreciated.
  • It could also be that it's not MySQL that is closing the connection, but the operating system (the tcp connect timeout) or a firewall.

    Do I understand you correctly that you have a farm of identical servers, all connecting to the same database, and only some show this behaviour? That would suggest an issue on the app server side.
  • Hey Harro,

    Just to follow up, the above configuration actual does work properly, not getting any timeouts or dropped connections.  I just had to restart the RDS (amazon) instance for the config changes to take effect and everything leveled out.  As to why it was affecting one app server more than others, I cannot say. but thanks again for your help.
  • The php settings you mean?

    A lot of people have these issues, good to know there is a solution.
  • Another follow up here: Following another product launch we quickly reached max connections on the database again.  I'd been staring at a bunch of sleeping connections for a while but had assumed that the queries using them were just happening too quickly to be observed.  This was not the case.  I have now turned persistence off in php.ini (mysql.allow_persistent = Off) and connections are now very low and we've had another product launch that exceeded our previous DAU without incident.  If you are looking at a long list of sleeping connections, I recommend this solution.  You must also turn persistence off at the config level or you will get an error.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion