proper way to solve mysql max user connection error

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 prob开发者_如何学Pythonlem.

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.

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.

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

So, a remote app calls into this script, sends it a list of values, and then this query is executed once, right? It's not in a foreach or for or while loop? When you say it will be executed millions of times, you don't mean in one sitting I mean. If it is in a loop, then move the db connect outside of the loop, otherwise it will attempt to connect again each time the loop iterates, and also, remember to call mysql_close at the end of the script, just in case.

mysql_pconnect() would create a persistent connection and that is the way to go if you don't want to exhaust your server's connection pool.