Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLSTATE[HY000] [2002] Resource temporarily unavailable - mysql - innodb and pdo

Tags:

sql

php

mysql

pdo

Getting numerous results in my error logs like the one listed below. All tables in the database are innodb and as far as any interaction with those tables everything is pdo with prepared statements.

As I said, all the errors are almost identical to the one listed below, but happen for a few different pages. Regardless of the page, the error line ALWAYS points to the point where I start a new statement... for instance $stmt = $db->prepare("........ The statements themselves work perfectly fine with no errors so I am a little bit baffled as to what is causing this.

Multiple errors like this for different pages :

[25-Sep-2014 10:19:09 America/Chicago] Failed to connect to database: SQLSTATE[HY000] [2002] Resource temporarily unavailable [25-Sep-2014 10:19:09 America/Chicago] PHP Fatal error: Call to a member function prepare() on a non-object in /home/test/public_html/add_log.php on line 28

Example stmt the error points to - in this case $stmt = $db->prepare(" line specifically. It always points to the line starting a new prepared statement.

$stmt = $db->prepare("
    SELECT 
        accounts.account_id,
        computers.computer_id,
        computers.status,
        users.user_id
    FROM accounts
    LEFT JOIN computers
        ON computers.account_id = accounts.account_id AND computers.computer_uid = :computer_uid
    LEFT JOIN users
        ON users.computer_id = computers.computer_id AND users.username = :username
    WHERE accounts.account_key = :account_key
");

//bindings
$binding = array(
    'account_key' => $_POST['account_key'],
    'computer_uid' => $_POST['computer_uid'],
    'username' => $_POST['username']
);
$stmt->execute($binding);   
//result (can only be one or none)
$result = $stmt->fetch(PDO::FETCH_ASSOC);

connect script :

<?php

if(!defined('INCLUDE_CHECK')) die('You are not allowed to execute this file directly');

// db config
$db_host        = 'localhost';
$db_database    = '*******';
$db_user        = '*******';
$db_pass        = '*******';

//db connection
try {
    $db = new PDO("mysql:host=$db_host;dbname=$db_database;charset=utf8", $db_user, $db_pass, array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => true));
}
catch(PDOException $e) {
    error_log("Failed to connect to database: ".$e->getMessage());
}

?>

Things that have crossed my mind:

  • This particular script runs a lot... at times it can be called multiple times in one second.

  • On this site I use the same format for all my prepared statements... $stmt = ... $result or $results = ... I am not closing the cursor, however, from my research it is not needed as the driver is MySQL and it is done so automatically.

  • I have set PDO::ATTR_PERSISTENT to true in my connection settings - would this have any bearing on this error?

What is going on here with all these errors? Anyone?

EDIT - more info :

  • I have changed localhost to 127.0.0.1 explicitly
  • I have turned persistent connections off/false

Since doing the above the error has changed to SQLSTATE[HY000] [2002] Connection timed out. Is this actually an issue with the person/comp connecting to 'me' or actually an issue with my server/db?

EDIT 2 :

Is it possible that my use of $_SERVER['DOCUMENT_ROOT'] could be causing an issue? Since this file is 'hit' so often it is also requiring the connect script just as often by the line below. I require my connection script as follows on each page it is needed :

require $_SERVER['DOCUMENT_ROOT'].'/custom/functions/connect.php';

like image 813
user756659 Avatar asked Sep 27 '14 04:09

user756659


1 Answers

I'm going to point out @MrGomez topic on this subject, which is mostly a concise but yet descriptive on the error you stated. This is not PDO related problem but MySQL specific.

Reasons of getting "Resource temporarily unavailable"

  1. Running out of MySQL available memory
  2. Running out of MySQL file descriptors under current user
  3. Running into a livelock or a deadlock
  4. Running out of resources on an upstream proxy
  5. Running out of PIDs avaiable to fork

However, your logs should have more things to let you know what's happening. Like determining if a there is any lock on any file.

like image 94
revo Avatar answered Sep 23 '22 06:09

revo