解决mysql最大用户连接错误的正确方法 [英] proper way to solve mysql max user connection error

问题描述

我将 PHP 与 MYSQL 数据库一起使用，因为两者都是开源且易于使用.

I'm using PHP with MYSQL database as both are open source and easy to use.

当我一个接一个地执行插入和/或更新数百万行时出现问题

I'm getting problem when I execute insert and/or update of millions of row one after another

在执行此操作时，我收到了 MYSQL 错误:

while this operation perform I got the MYSQL error that:

'max_user_connections' active connections  

这是解决此问题的最佳方法.

which is the best way to solve this problem.

我不想使用除 PHP 之外的其他数据库或语言.

I don't want to use another database or language other then PHP.

 connect_db();
   $query = "insert into table(mobno,status,description,date,send_deltime,sms_id,msg,send_type) values('".$to."','".$status."','".$report."','','".$timewsha1."','".$smsID."','','".$type."')";

    $result = mysql_query($query) or ("Query failed : " . mysql_error());

此查询将执行数千次.

然后服务器给出连接错误.

and then server give connection error.

推荐答案

首先，尝试从您的托管服务器管理员处了解 MySQL 数据库可用的最大连续活动连接数.这是最基本的&要了解的主要信息.

First of all, try to know from your hosting server administrator about the max consecutive active connections available to the MySQL database. This is the most basic & primary information to have knowledge about.

如果您的页面加载时间相当长，并在页面加载后释放连接，那应该没问题.当您的脚本需要很长时间才能从数据库中检索信息或维护连接时，就会出现问题.
由于您正在执行数百万行的 INSERT 和/或 UPDATE 操作，因此您可能会遇到一些问题.

If your page(s) load in a decent amount of time and release the connection once the page is loaded, it should be fine. The problem occurs when your script takes some long time to retrieve information from the database or maintains the connections.
Since you are executing INSERT and / or UPDATE operations of millions of rows, so you may have some problem.

此外，如果您无法关闭脚本中的连接，则可能有人会加载页面，并且在页面加载时不会关闭连接，而是保持打开状态.没有其他人可以使用该连接.所以请确保在所有 MySQL/SQL 查询执行结束时，关闭数据库连接.另外请确保您的服务器提供超过 250 个连接，因为通常几乎所有服务器都提供 100 个连接.

Additionally, if you fail to close connections in your script(s), it is possible that someone will load a page and instead of closing the connection when the page is loaded, it is left open. No one else can then use that connection. So please make sure that at the end of execution of all the MySQL / SQL queries, the database connection is closed. Also please make sure that your server provides more than 250 connections, since 100 connections is available in almost all the servers generally.

还要确保您没有使用持久连接(当使用内置函数mysql_pconnect()"时可用)，因为这将锁定用户，直到连接成功手动关闭.

Also make sure that you are not using the persistent connections (which is available when using the built-in function "mysql_pconnect()"), since this will lock up the user until the connection is manually closed.

希望有帮助.

这篇关于解决mysql最大用户连接错误的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！