SQLSTATE [HY000] [2002]资源暂时不可用-mysql-innodb和pdo [英] SQLSTATE[HY000] [2002] Resource temporarily unavailable - mysql - innodb and pdo

问题描述

在我的错误日志中获得了许多结果，例如下面列出的结果.数据库中的所有表都是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.

正如我所说，所有错误几乎都与下面列出的错误相同，但是发生在几个不同的页面上.无论页面如何，错误行始终指向我开始新语句的位置...例如$stmt = $db->prepare("........语句本身可以正常工作且没有错误，因此我对造成该错误的原因感到困惑.

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.

类似的错误针对不同的页面:

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

[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

示例stmt错误指向-在本例中为$stmt = $db->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 = $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);

连接脚本:

<?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());
}

?>

我已经想到的事情

• 此特定脚本运行很多...有时可以在一秒钟内被多次调用.

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

在此站点上，我所有准备好的语句都使用相同的格式... $ stmt = ... $ result或$ results = ...我没有关闭游标，但是，根据我的研究，不需要，因为驱动程序是MySQL，并且它是自动完成的.

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.

我已在我的连接设置中将PDO :: ATTR_PERSISTENT设置为true-对此错误有影响吗?

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?

编辑-更多信息:

• 我已将localhost明确更改为127.0.0.1
• 我已关闭持久连接/false

由于执行了上述操作，该错误已更改为SQLSTATE[HY000] [2002] Connection timed out.这是与连接到我"的人员/计算机有关的问题，还是与我的服务器/数据库有关的问题?

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?

使用$_SERVER['DOCUMENT_ROOT']是否有可能引起问题?由于此文件经常被命中"，因此它也需要连接脚本，就像下面的行一样.我需要在每个页面上按如下所示连接脚本:

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'].'/custom/functions/connect.php';

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

推荐答案

我要指出@MrGomez 主题 关于该主题的内容，虽然简洁明了，但仍能描述您所陈述的错误.这不是与PDO相关的问题，而是特定于MySQL的.

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.

1. 用尽MySQL可用内存
2. 在当前用户下用完MySQL文件描述符
3. 遇到死锁或死锁
4. 上游代理资源不足
5. 分叉可用的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.

这篇关于SQLSTATE [HY000] [2002]资源暂时不可用-mysql-innodb和pdo的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！