Someone please help....

BigDeezy

I have this part of a script that sends a message from the admin to everyone on my website. The script works fine when sending just 1 message to a certain user but when I try to send a message to their mail for every single user ( which is about 800 ) ...I keep getting this message after about 2 minutes:

mysql error: Lost connection to MySQL server during query

I read something on the inet about a bug for inserts when sending a lot of data but i can't figure out how to fix this ...can someone look at this peice of code and tell me if there is an alternative way to write it with out me losing connection to MySQL

			$totalusers=count($sendto);
			foreach ($sendto as $user_id) {
                          $query="INSERT INTO mail_inbox VALUES('',0,'$user_id','$from_id','$subject','$body','',now(),'$from','1')";        

				if (!($res=mysql_query($query))) {general_error(mysql_error(),__LINE__,__FILE__);}
				if (is_send_newmessage_alerts($user_id)) {send_newmessage_alert($from_id,$user_id);}
			}		}

Thanks

bpat1434

Why not just insert all the rows, and after the query just build an array of successful and unsuccessful inserts. Those that are successful, you send the message to, and those unsuccessful will be logged.

mikell

It could be a permissions problem...I doubt it though, but make sure the user you are connecting as has permission to insert and select from whatever table you are using.

What is the code for is_send_newmessage_alerts() and send_newmessage_alert()?

Does it always break at the same place? As in...insert #100.
Does it always break on the same query?
Does it always break after the same amount of time?

BigDeezy

mikell wrote:
It could be a permissions problem...I doubt it though, but make sure the user you are connecting as has permission to insert and select from whatever table you are using.

What is the code for is_send_newmessage_alerts() and send_newmessage_alert()?

Does it always break at the same place? As in...insert #100.
Does it always break on the same query?
Does it always break after the same amount of time?

This is what happens ....if I just send 1 message ..it executes with no problem ....but if I change the option in admin to message all .....the script will start inserting message into about 30 - 90 rows before the error message appears and sometimes the browser will timeout as if I wasn't connected to the internet ....usually takes 30 secs to 2 mins before the error message or browser time out happens. The mysql says error line 70 which would be: if (!($res=mysql_query($query))) {general_error(mysql_error(),LINE,FILE);} if I included all lines in the code below ...

the code for is_send_newmessage_alerts() and send_newmessage_alert() is a function to email the user letting them know they have a new message on the site mailbox

Here is the full script:

<?

/*
SOME INCLUDE FILES REMOVED
*/

db_connect();
check_login_member();

set_time_limit(0);
$tpl = new phemplate(_TPLPATH_,'remove_nonjs');

$topass=array();
$error=false;
$message="";
if ($_SERVER['REQUEST_METHOD']=='POST') {
	$from=addslashes_mq($_POST['from']);
	$query="SELECT user_id FROM users WHERE name='$from'";
	if (!($res=mysql_query($query))) {general_error(mysql_error(),__LINE__,__FILE__);}
	if (mysql_num_rows($res)) {
		$from_id=mysql_result($res,0,0);
	} else {
		$error=true;
		$topass['message']="Invalid FROM field! Message not sent.";
	}
	if (isset($_POST['subject']) && !empty($_POST['subject'])) {
		$subject=addslashes_mq($_POST['subject']);
	} else {
		$error=true;
		$topass['message']="No subject specified. Message not sent.";
	}
	if (isset($_POST['body']) && !empty($_POST['body'])) {
		$body=unix2dos(addslashes_mq($_POST['body']));
	} else {
		$error=true;
		$topass['message']="No message body! Message not sent.";
	}
	$action=0;
	if (isset($_POST['act']) && !empty($_POST['act'])) {
		$action=addslashes_mq($_POST['act']);
	}
	if (!$error) {
		$sendto=array();
		if ($action==-4) {
			$name=addslashes_mq($_POST['name']);
			$query="SELECT user_id FROM users WHERE name='$name'";
		} elseif ($action==-3) {
			$query="SELECT user_id FROM users";
		} elseif ($action==-2) {
			$query="SELECT user_id FROM users WHERE membership="._REGISTERLEVEL_;
		} elseif ($action==-1) {
			$query="SELECT user_id FROM users WHERE membership="._SUBSCRIBERLEVEL_;
		} elseif ($action>=1 && $action<=count($accepted_genders)) {
			$query="SELECT user_id FROM users WHERE gender='$action'";
		} else {
			$error=true;
			$topass['message']="You have to specify a member/group where to send the message";
		}
		if (!$error) {
			if (!($res=mysql_query($query))) {general_error(mysql_error(),__LINE__,__FILE__);}
			if (mysql_num_rows($res)) {
				for ($i=0;$i<mysql_num_rows($res);$i++) {
					$sendto[]=mysql_result($res,$i,0);
				}
			}
			$totalusers=count($sendto);
			foreach ($sendto as $user_id) {
                          $query="INSERT INTO mail_inbox VALUES('',0,'$user_id','$from_id','$subject','$body','',now(),'$from','1')";        

				if (!($res=mysql_query($query))) {general_error(mysql_error(),__LINE__,__FILE__);}
				if (is_send_newmessage_alerts($user_id)) {send_newmessage_alert($from_id,$user_id);}
			}
			$topass['message']="Message sent to $totalusers members.";
		} else {
			$topass['from']=$from;
			$topass['subject']=$subject;
			$topass['body']=$body;
			$topass['name']=$name;
			$topass['act']=$act;
			redirect2page('admin/send_message.php',$topass);
		}
	} else {
		$topass['from']=$from;
		$topass['subject']=$subject;
		$topass['body']=$body;
		$topass['name']=$name;
		$topass['act']=$act;
		redirect2page('admin/send_message.php',$topass);
	}
}
redirect2page('admin/controlpanel.php',$topass);
?>

bpat1434

Try freeing the result of the first query, before you make the second:

if (!$error) {
            if (!($res=mysql_query($query))) {general_error(mysql_error(),__LINE__,__FILE__);}
            if (mysql_num_rows($res)) {
                for ($i=0;$i<mysql_num_rows($res);$i++) {
                    $sendto[]=mysql_result($res,$i,0);
                }
            }
            @mysql_free_result($res);
            $totalusers=count($sendto);
            foreach ($sendto as $user_id) {
                          $query="INSERT INTO mail_inbox VALUES('',0,'$user_id','$from_id','$subject','$body','',now(),'$from','1')";        

                if (!($res=mysql_query($query))) {general_error(mysql_error(),__LINE__,__FILE__);}
                if (is_send_newmessage_alerts($user_id)) {send_newmessage_alert($from_id,$user_id);}
            }
            $topass['message']="Message sent to $totalusers members.";
        }

You could also try freeing the result in every itteration of the foreach()loop; however, that might be a bit extreme.

Also, try flushing something to the browser, even if it's just an Apache reset timeout [man]apache_reset_timeout/man 😉

BigDeezy

If you don't mind bpat1434, could you please explain in more detail what you're telling me to do ....will that one line you added ( @mysql_free_result($res); ) work or do I need to do something else ....I'm not familiar with what your telling me to do .......sorry🙁

bpat1434

mysql_free_result() will free the result resource $res that you previously declared and frees up the memory used by the query. It can help make the script faster to call it when dealing with large amounts of data (as you seem to be doing, or will be doing eventually).

You'll just have to test somethings out by doing benchmarks. It may come down that you have to modify your code to break it into chunks of like 100 or 50 people at a time. In which case you then present an intermittent screen that pauses for 10 seconds and refreshes automatically (or by clicking of a link by the user) to do the next group. SMF does this with their newlsetter feature. If you send it to over like 150 people, it splits it into groups of no more than like 100 so that nothing is really "over burdened".

BigDeezy

didn't work ...same problem ....I did notice that it always stop on user_id 94 or 95 or 96 in the database ...but anyway ....where can I get info on how to write a script that would send the messages in bulk (50 users at a time) or could you help me with that? ...This will be my last message of the day ..have to get some sleep so i can go to work tonight ...thanks for the help

mpb001

It is a bit dirty but you can try a disconnect and reconnect for each one.

Sending a mail takes longer and lengthens the process which could be causing the issue somewhere.

bpat1434

Essentially, here's the basics of grouping the inserts:

Do a full SELECT query for all users to be contacted
Store all results into one large array

Chunk this array into smaller arrays with a maximum of 80 or 90 elements per array. It's best to use a multi-dimensional array like:

Array (
    [0] => Array (
          [0] => 'item'
          .....
          [80] => 'item80'
    )
    [1] => Array (
          [0] => 'item'
          .....
          [80] => 'item80'
    )
)

Store this chunked array in either a Cookie or Session (or hidden POST field)
Now, take the first chunk and insert those into the databaes
Take that first chunk and notify those members
Send a temporary page to refresh and do the next chunk
Repeat the previous step until you finish all the chunks

That's pretty much it. If you need help, just ask.

mikell

If your version of PHP is < 4.2.0 then ignore this.

Replace everything from

$totalusers=count($sendto);
...to...
$topass['message']="Message sent to $totalusers members.";

with the following:

$totalusers=count($sendto);
$emailssent=1;
foreach ($sendto as $user_id) 
{
	$query="INSERT INTO mail_inbox VALUES('',0,'$user_id','$from_id','$subject','$body','',now(),'$from','1')";        

	if (!($res=mysql_query($query))) 
	{
		echo('query failed:'.$query.'<br>');
		general_error(mysql_error(),__LINE__,__FILE__);
	}
	if (is_send_newmessage_alerts($user_id)) 
	{
		send_newmessage_alert($from_id,$user_id);
		echo "emailssent: $emailssent<br>";
		echo "query: $query<br>";
		$emailssent++;
	}

//flush output buffer to browser
ob_flush();
flush();
}
$topass['message']="Message sent to $totalusers members.";

With that you will get a little more information about whats going on and
flushing the output after every email sent should keep the browser from timing out. If mysql still drops the connection then look at the last two queries in the output and see if there is anything odd about them. I've read mysql will break a connection if the query (packet size) is too big.