SQLSTATE [HY000] [2002]资源暂时不可用 - mysql - innodb和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.

在我的错误日志中获得大量结果,如下面列出的那样。数据库中的所有表都是innodb,就与这些表的任何交互而言,所有表都是带有预处理语句的pdo。

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.

正如我所说,所有错误几乎与下面列出的错误相同,但是对于几个不同的页面会发生。无论页面如何,错误行总是指向我开始一个新语句的位置...例如$ stmt = $ db-> prepare(“........语句本身完全正常,没有错误让我对导致这种情况的原因感到有些困惑。

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

[25-Sep-2014 10:19:09 America / Chicago]无法连接数据库:SQLSTATE [HY000] [2002]资源暂时不可用[25-Sep-2014 10:19:09 America / Chicago] PHP致命错误:在第28行的/home/test/public_html/add_log.php中调用非对象的成员函数prepare()

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 - 在这种情况下为$ stmt = $ db-> prepare(“具体为行。它始终指向开始新预准备语句的行。

$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.

  在这个网站上,我对所有准备好的语句使用相同的格式... $ stmt = ... $ result或$ results = ...我没有关闭光标,但是,从我的研究中我不需要作为驱动程序是MySQL,它是自动完成的。

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

  我在我的连接设置中将PDO :: ATTR_PERSISTENT设置为true - 这会对此错误产生影响吗?

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

所有这些错误在这里发生了什么?任何人?

EDIT - more info :

编辑 - 更多信息:

• I have changed localhost to 127.0.0.1 explicitly

我明确地将localhost更改为127.0.0.1

• I have turned persistent connections off/false

我关闭了持久连接/ 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?

由于执行上述操作,错误已更改为SQLSTATE [HY000] [2002] Connection超时。这实际上是一个人/ comp连接到'我'的问题,或者实际上是我的服务器/数据库的问题?

EDIT 2 :

编辑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 :

我使用$ _SERVER ['DOCUMENT_ROOT']是否可能导致问题?由于此文件经常被“命中”,因此通常也需要连接脚本,如下所示。我需要在每个页面上按如下所示连接脚本:

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

5 个解决方案

#1

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.

我将在这个主题上指出@MrGomez主题,这个主题对你说的错误大多是简洁但描述性的。这不是PDO相关的问题,而是MySQL特定的。

Reasons of getting "Resource temporarily unavailable"

1. Running out of MySQL available memory

耗尽MySQL可用内存

2. Running out of MySQL file descriptors under current user

在当前用户下用完MySQL文件描述符

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

耗尽的PID可用于分叉

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.

但是,您的日志应该有更多的东西让您知道发生了什么。就像确定任何文件是否有任何锁定一样。

#2

It's not actually pointing to that line.

它实际上并没有指向那条线。

The error in your log

日志中的错误

[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]无法连接数据库:SQLSTATE [HY000] [2002]资源暂时不可用

looks like it is getting written by the catch statement here:

看起来它是由catch语句写的:

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()); // ERROR HERE
}

Note: $db is not set in the catch statement.

注意:$ db未在catch语句中设置。

Your script then goes on executing, until it hits the $db->prepare line. As $db is null, it outputs another error, this time with the line of the file.

然后你的脚本继续执行,直到它到达$ db-> prepare行。由于$ db为null,它会输出另一个错误,这次是文件行。

Beyond that - see revo's answer for why you might be getting the "Resource temporarily unavailable" error.

除此之外 - 请参阅revo的答案,了解为什么您可能会收到“资源暂时不可用”错误。

#3

Increase your max_connections in my.cnf:

增加my.cnf中的max_connections:

max_connections                 = 1000

Check file descriptors available for mysql:

检查可用于mysql的文件描述符:

ulimit -n

If it's value default like 1024, change to 65535 or more

如果它的值默认值为1024,则更改为65535或更高

ulimit -n 65535

restart mysqld

#4

Just a thought - are you able to store the DB connection in memory so you don't have to keep re-creating new ones? Also in terms of your include DIR magic constant is great (I've only fully realised it today). Hope that helps.

只是一个想法 - 您是否能够将数据库连接存储在内存中,这样您就不必继续重新创建新连接?另外在你的包含DIR魔法常数方面也很棒(我今天才完全意识到)。希望有所帮助。

#5

Usually means that you need to specify TCP/IP:

通常意味着您需要指定TCP / IP:

"mysql:host=127.0.0.1" or 
"mysql:host=localhost;port=3306"

In your case:

在你的情况下:

$db_host        = 'localhost';
$db_port        = '3306';

maybe something like this:

也许是这样的:

try {
    $db = new PDO("mysql:host=$db_host;port=$db_port;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()); // ERROR HERE
}