proper way to solve mysql max user connection error

Question

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

while this operation perform I got the MYSQL error that:

'max_user_connections' active connections  

which is the best way to solve this problem.

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

this query will execute thousand of times.

and then server give connection error.

Answer 1

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.

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.

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.

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.

Hope it 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 );