Correct way to fix mysql max user connection error

Question

I use PHP with the MYSQL database, as they are open and easy to use.

I get problems when performing insert and / or update from millions of rows one by one

while this operation is in progress, I received a MYSQL error:

'max_user_connections' active connections 

what is the best way to solve this problem.

I do not want to use another database or language other than 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()); 

This request will be executed thousands of times.

and then the server will throw a connection error.

Answer 1

First of all, try to find out from the administrator of your hosting server about the maximum consecutive active connections available for the MySQL database. This is the basic and basic information you need to know about.

If your pages load at a decent time and release the connection after the page loads, this should be fine. The problem occurs when your script takes a long time to retrieve information from a database or maintain connections.
Since you are performing INSERT and / or UPDATE operations from millions of rows, you may run into some problems.

In addition, if you cannot close the connections in script (s), maybe someone will load the page and instead of closing the connection when the page is loaded, it will remain open. Then no one can use this connection. Therefore, please make sure that at the end of all MySQL / SQL queries, the database connection is closed. Also make sure that your server provides more than 250 connections, as 100 connections are available on almost all servers.

Also, make sure that you do not use persistent connections (which are available using the built-in function mysql_pconnect() ), as this will block the user until the connection is closed manually.

Hope this helps.

Answer 2

 //this loop is for preparing the subquery for mutiple records for(// this loop for getting data for mutiple records){ $sub_query[] = "('".$to."','".$status."','".$report."','','".$timewsha1."','".$smsID."','','".$type."')"; } $query = "insert into table(mobno,status,description,date,send_deltime,sms_id,msg,send_type) values "; $query .= implode(',',$sub_query); mysql_query($query ); 

Answer 3

So, the remote application calls this script, sends it a list of values, and then this request is executed once, right? Is this not in the foreach or for or while loop? When you say that it will be performed millions of times, you do not mean in one go, I mean. If it is in a loop, then move the db connection outside the loop, otherwise it will try to connect again every time the iteration loop, and also remember to call mysql_close at the end of the script, just in case.

Answer 4

mysql_pconnect () will create a persistent connection, and this is the way to go if you don't want to exhaust the server connection pool.