Problem on sending

Newbie Joined: Nov 03, 2007 Posts: 127

HTML Newsletter have functioned without problems now for a couple of years for PHP-Nuke 7.6, but the last weeks I have started to get problems. After hitting send test newsletter I get blank page - but sometimes I get the test newsletter, but not all times.

My site is pretty big with 249000 forumposts and over 120 active topics each week that is going to be included in the newsletter.

I have noted that if I diminish the forum topics from 120 to 100 I can send the newsletter, but I still get blank page.

Today I also got this error message:

ENCOUNTERED ERROR IN SQL: MySQL client ran out of memory

SQL = SELECT * FROM `nuke_bbposts`

ERROR IN MODULE: HTML_Newsletter

ERROR MSG: Unable to retrieve Statistics for number of forum posts

I had to skip statistics to send out the newsletter.

What I suspect is that my site seems to have too many forumposts that may create problems on sending the newsletter.

Is it possible to make the SQL queries more efficient or after the queries make a short pause - sleeper , so the PHP script will work better?

Site Admin Joined: Jul 18, 2005 Posts: 387

There isn't much you can do other than reduce the number of forum posts you are trying to fetch. It could be a limitation with the servers ODBC driver, server memory or simply the inability of the server to handle the required queries if it is being used in a 'shared' hosting environment.
The nuke_bbposts table actually tracks post id's and topic id's and forum id's so I think you would need to fetch the entire table in order for it to then calculate which forums are are the 'latest' ones.

It is possible to 'cache' the result of the query but you still need to run the initial query so that isn't going to help you too much.
You may have to resort to either retrieving fewer forum posts and sending your newsletter more often to compensate or consider a VPS or dedicated server.

mySQL itself can handle that number of queries with ease (I'm basing that on some work I did recently for a financial institution in Slovakia where we were running through hundreds of thousands of users), so I'm suspecting this is more of a hardware or server configuration limitation to be honest.

Newbie Joined: Nov 03, 2007 Posts: 127

Thanks! I have further investigated this problem upon send and it seems to be caused by this code of admin_make_nls.php line 215 that calculates the total amounts of forum posts:

Code:

//Total Amount of forum Posts

$sql = "SELECT * FROM `".$prefix."_bbposts`";
$result9 = msnl_fSQLCall( $sql );

I run this SQL query in phpMyAdmin on the database and it took 0,0531 seconds

If I remove this query from admin_make_nls.php the problem will probably be gone, but maybe this query can be made more efficient?

Site Admin/Owner Joined: Feb 12, 2005 Posts: 1360

Actually, for just getting counts, yes, this code can be much more efficient. I'll try and get to looking for replacement code this weekend. I'm not sure right now whether that query is used elsewhere or not... hang tight.

Site Admin/Owner Joined: Feb 12, 2005 Posts: 1360

Slack,

Just looked at it quickly. For each of the stat counters, you could try something like this. Here is the original code for the member count:

Code:

/*
* Total Members
*/
$sql = 'SELECT `user_id` FROM `' . $user_prefix . '_users` WHERE `username` <> \'Anonymous\'';
if (!$result = msnl_fSQLCall($sql)) {
msnl_fRaiseAppError(_MSNL_ADM_MAKE_ERR_DBGETSTATSUSR);
} else {
$msnl_iStatsTotUsr = $db->sql_numrows($result);
}

Could be changed to this:

Code:

/*
* Total Members
*/
$sql = 'SELECT COUNT(`user_id`) FROM `' . $user_prefix . '_users` WHERE `username` <> \'Anonymous\'';
if (!$result = msnl_fSQLCall($sql)) {
msnl_fRaiseAppError(_MSNL_ADM_MAKE_ERR_DBGETSTATSUSR);
} else {
list($msnl_iStatsTotUsr) = $db->sql_fetchrow($result);
}

Do something like this for each one of these (especially the forum posts). Also, make sure you optimize your DB often so that the database statistics are kept up-to-date with all your activity.

Let me know how this goes? Thx.

Newbie Joined: Nov 03, 2007 Posts: 127

Many thanks!

I have tried this and it worked very well and the code above for total members did also work:

Code:

//Total Amount of forum Posts

$sql = "SELECT COUNT(*) FROM `".$prefix."_bbposts`";
if (!$result = msnl_fSQLCall($sql)) {
msnl_fRaiseAppError(_MSNL_ADM_MAKE_ERR_DBGETSTATSUSR);
} else {
list($msnl_iStatsTotPosts) = $db->sql_fetchrow($result);
}

Site Admin/Owner Joined: Feb 12, 2005 Posts: 1360

I'll add these changes (for all the counts) to my 1.4 issue list. Thanks Slack for verifying the change.