error message that says mysql_real_escape_string() expects parameter 2....

[DarkAngel612]

After going thru the posts which there were a lot to go thru...lol

The below area quoted is what I think is the code you are talkiing about. I originally did not do it cause you said you thought it fixed it...but no real "yes use this" was said, sorry for misunderstanding.

Now I see the file is the errors.php (later in the quote) but is that where the first edit is also? If not then which file to edit the debug code in, if you please.

OK, I think I hit a fix, at least it seems to be safe in the testing I've done.

Remove the debug code if you had it, and put this code after list ($values, $connection) = $db_callback;

Code Select Expand

if (!is_resource($connection))
    db_fatal_error();

This will push it out of regular execution and into a place where an error message should be emitted to the user, and execution will safely exit without any queries back to the database to log anything, which is where this all failed.

I'm still wondering if that function also needs to ensure output buffers are cleaned and closed before outputting, just in case some output occurred first or not.

If that IS needed, which I'm not 100% sure on, the code would be as follows in Errors.php:

Code (find) Select Expand

function show_db_error($loadavg = false)
{
   global $sourcedir, $mbname, $maintenance, $mtitle, $mmessage, $modSettings;
   global $db_connection, $webmaster_email, $db_last_error, $db_error_send, $smcFunc;

Code (after that, add) Select Expand

   // Just check we're not in any buffers, just in case.
   for ($i = ob_get_level(); $i > 0; $i--)
      @ob_end_clean();

In case anyone's wondering for the @, there are circumstances where ob_get_level will return 1 more output buffer than it should, so we're going to clean down all of them regardless.

EDIT: Tweaked that loop slightly, just in case of real oddities.

[Arantor]

Read the whole of this thread, I documented a change to Subs-Db-Mysql.php that will end execution safely rather than throw a lot of errors into the log.

Just before the lines that throw all the errors.

[Vince S]

Thanks Arantor, that's great. There is an answer and I'm happy to have a crack at it. Just before I do I would like to understand where this is at from a mug punter's perspective. Sure I can go change the code, and do both files while I'm at it, but what then? As soon as the next rev comes out won't it trash the changes made? I mean how does one control this stuff in the real world?

I guess I am saying it really needs to go into the base code for future release as it is silly to fix up this real world stuff on a case by case basis, when there is no possibility those of us reporting it are isolated cases. The only good part of this issue is that ignorance truly is bliss. Apart from seeing the ridiculous rate at which the error log builds up I don't think there are any actual other consequences beyond fretting about it. As in detectable performance loss I mean. Thus it is very likely thousands have the condition, but just don't know it........

So how about it, does this thread get shoved across to feature requests with a gold tick attached en route? Then we can do a couple of "early adopter" trials, and confirm the wanted behaviour outcomes. Thank you.

[Arantor]

You only actually need to do the one change, as I said and you quoted the second change to Errors.php should not be needed but it won't hurt to do it.

When 2.0.3 comes out, the patch from 2.0.2 to 2.0.3 only changes the actual lines that changed, not the entire set of files. So you won't lose the change. Then again the team might decide to implement it in 2.0.3 if it happens.

If and when 2.1 comes out it will likely have to be re-assessed.

*shrug* Makes little difference to me what the team decides to do with it. I know I've applied this change to my own codebase (though I never had this problem on any of my life forums), and I know I've investigated the root problem to the best of my ability and there's not really a lot more than SMF should do than it currently does, and I'm not entirely sure it should, but that's not my call to make.

[DarkAngel612]

Thanks Arantor,

I went to Subs-Db-Mysql.php

found:
list ($values, $connection) = $db_callback;



added this directly after it:
    if (!is_resource($connection))
    db_fatal_error();

from your reply to the other poster the second half need not be added but it would not hurt to do so?

[Arantor]

Yes, that's correct.

[charlottezweb]

Question for Arantor if you're still monitoring this...

Wanted to throw a scenario out on this issue to see what your thoughts are.

I have a forum on one server that started doing this whole issue immediately after I upgraded it from 1.x to 2.x a couple weeks back.  I did the two file edits suggested above which I've done on other SMF installs since this thread started and it partially worked but the account still gets the automated emails from time time that mysql is unavailable which match log entries.

What's interesting in this case that I didn't check in past cases is that I compared other forums on the same server (this particular customer has two) and the other ones aren't impacted at the same time -- it's only this one forum.   That would lead me to think this is just limited to this one account and not server wide.

So for testing, I did a brand new stock install of 2.x and then imported her db into it.  The errors continued as did the mysql connection issues.   (She had a custom theme and some mods so I was curious to rule all that out).  So it continues on a stock 2.x install whereas others on the same server are fine.  So weird.

Anyway, curious what your thought is -- is that normal?  Would that relate to something in her db?  Or something related to the mysql config of this one hosting account only?

By the way, this is what I'm getting now in her logs although it seems to have killed the huge writes of hundreds of simultaneous entries which represent when mysql would be unable to connect for just this forum.

Code Select Expand


[17-Nov-2012 02:39:09] PHP Warning:  Cannot modify header information - headers already sent by (output started at /home/xxx/public_html/forum/Sources/Errors.php:416) in /home/xxx/public_html/forum/Sources/Errors.php on line 349


Line 349 of Errors is the last line below:

   // Don't cache this page!
   header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
   header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
   header('Cache-Control: no-cache');

Thanks!

[DarkAngel612]

I gave up on that error myself. I have 2 servers (1 is our vps the other is a shared one) on two different hosts and the test forum on the shared host did the same thing as did one on the vps. on our vps we have several entities living there but only one had that problem. all were upgraded from 1.1.16 to smf f but the test forum was a fresh new install.

what I finally figured out after another member from here that is also helping at tiny portal joined as a member which I then made him an admin so he could double check what was happening.

He suggested that I re-upload every file for smf overwriting the old ones after un-installing all mods...then reinstalling mods with the security files first then the portal as last.

I had already done the files again about 5 times previously but not with a selective reinstall of mods so I gave it a go and sure enough...that error "sort of" went away.

I have since found that the extremely large log file is nonexistent unless there is a problem with a theme or when I edit files and miss putting punctuation in there...then the log file for errors happens with the problem and then repeated lines of the original error that we thought went away.

The only thing I can think of is that something in the code makes it come forward and hogs up the error file.

It still happens at our main site at the vps and sometimes with the test forum on the other host

[emanuele]

Not Arantor, but curious as well...

So for testing, I did a brand new stock install of 2.x and then imported her db into it.  The errors continued as did the mysql connection issues.   (She had a custom theme and some mods so I was curious to rule all that out).  So it continues on a stock 2.x install whereas others on the same server are fine.  So weird.

Anyway, curious what your thought is -- is that normal?  Would that relate to something in her db?  Or something related to the mysql config of this one hosting account only?

So the accounts are on the same server but with different MySQL configurations?
Did you try a clean SMF without re-importing the db?
I think I've seen previously some weird issues where importing a db caused the same malfunctioning previously reported even without mods installed, while using a new database the problem was not present. Unfortunately at the moment I can't remember what issue was and how it was fixed.

By the way, this is what I'm getting now in her logs although it seems to have killed the huge writes of hundreds of simultaneous entries which represent when mysql would be unable to connect for just this forum.

Code Select Expand


[17-Nov-2012 02:39:09] PHP Warning:  Cannot modify header information - headers already sent by (output started at /home/xxx/public_html/forum/Sources/Errors.php:416) in /home/xxx/public_html/forum/Sources/Errors.php on line 349


Line 349 of Errors is the last line below:

   // Don't cache this page!
   header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
   header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
   header('Cache-Control: no-cache');

Check the Errors.php, I feel there is an empty line or a space at the end of the file.
The reason is:

Code Select Expand

headers already sent by (output started at /home/xxx/public_html/forum/Sources/Errors.php:416)
In my Errors.php the (hopefully) corresponding line is the last.

[charlottezweb]

So the accounts are on the same server but with different MySQL configurations?

Not unless something VERY strange happened.  I definitely haven't set that up manually but at this point I was just throwing out ideas...

Did you try a clean SMF without re-importing the db?

No because it's a production forum for their business.  I could potentially install a second one somewhere on that account as stock if that would be useful.  Offhand, I'm not sure why it would be...

Check the Errors.php, I feel there is an empty line or a space at the end of the file.
The reason is:

Code Select Expand

headers already sent by (output started at /home/xxx/public_html/forum/Sources/Errors.php:416)
In my Errors.php the (hopefully) corresponding line is the last.

Cool -- I'll see if that's the case tonight. 

Thanks

[charlottezweb]

Check the Errors.php, I feel there is an empty line or a space at the end of the file.
The reason is:

Code Select Expand

headers already sent by (output started at /home/xxx/public_html/forum/Sources/Errors.php:416)
In my Errors.php the (hopefully) corresponding line is the last.

Cool -- I'll see if that's the case tonight. 

I don't see 416 in the logs anymore -- it looks like it's repeating the error but for lines 347, 348, 349, 352, 353, 354.  Those lines start with the edit from Arantor's post:

(346 is the "don't cache this page" comment line)

Code Select Expand


// Don't cache this page!
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: no-cache');

// Send the right error codes.
header('HTTP/1.1 503 Service Temporarily Unavailable');
header('Status: 503 Service Temporarily Unavailable');
header('Retry-After: 3600');


[emanuele]

Can you attach the file and the exact error in the log?

[charlottezweb]

Can you attach the file and the exact error in the log?

Attached.

Thanks!

[emanuele]

mmm...okay...this is weird: the line 416 is the last echo, just before the die...

It looks like the "die" doesn't stop the execution of the script that for a reason or another it runs the function again before actually die.

I would try a variant of Arantor's debug code but I'm not sure if it would help...
If you want to give it a try, it would look like:

Code Select Expand


if (headers_sent())
{
die('Something very bad happened, please advise the administrator of: ' . var_dump(debug_backtrace()));
}

just before the comment "// Don't cache this page!".

[charlottezweb]

Ok, I'll see what that one does.  Thanks!

-Jason

[PokémonS]

I have same problem. It gave me high mysql activity that makes my forum got suspended.
So, I add Arantor's code (Subs-Db-mysql.php):

Code Select Expand

if (!is_resource($connection))
    db_fatal_error()

But it still gave me less error like this:

[22-Nov-2012 15:50:23 Asia/Jakarta] PHP Warning:  Cannot modify header information - headers already sent in /home/x/public_html/forum/Sources/Errors.php on line 342
[22-Nov-2012 15:50:23 Asia/Jakarta] PHP Warning:  Cannot modify header information - headers already sent in /home/x/public_html/forum/Sources/Errors.php on line 343
[22-Nov-2012 15:50:23 Asia/Jakarta] PHP Warning:  Cannot modify header information - headers already sent in /home/x/public_html/forum/Sources/Errors.php on line 344
[22-Nov-2012 15:50:23 Asia/Jakarta] PHP Warning:  Cannot modify header information - headers already sent in /home/x/public_html/forum/Sources/Errors.php on line 347
[22-Nov-2012 15:50:23 Asia/Jakarta] PHP Warning:  Cannot modify header information - headers already sent in /home/x/public_html/forum/Sources/Errors.php on line 348
[22-Nov-2012 15:50:23 Asia/Jakarta] PHP Warning:  Cannot modify header information - headers already sent in /home/x/public_html/forum/Sources/Errors.php on line 349
[22-Nov-2012 15:50:28 Asia/Jakarta] PHP Warning:  mysql_affected_rows() expects parameter 1 to be resource, boolean given in /home/x/public_html/forum/Sources/Subs-Db-mysql.php on line 371

Also, that still gave me high mysql activity that makes my forum got suspended.
So, I am try to test emanuele's code, but doesn't remove the Arantor's code. Hope this code solve the problem, thx~

[charlottezweb]

Ok, I'll see what that one does.  Thanks!

No new errors so far.  Fingers crossed!  I'll check it again in a day or two. 

Thanks!

[PokémonS]

I'll test it for 7 days.

[emanuele]

So, I am try to test emanuele's code, but doesn't remove the Arantor's code. Hope this code solve the problem, thx~

It's useless to use my code.
And I highly doubt your forum got suspended due to this error. Most likely this error is the result of an excessive usage of MySQL by your forum (many mods?) and that's the real reason your account got suspended.

[PokémonS]

Error logs:

... (tons of error logs)
[11-Nov-2012 09:32:25 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:32:25 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:32:25 Asia/Jakarta] PHP Fatal error:  Allowed memory size of 67108864 bytes exhausted (tried to allocate 523800 bytes) in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 640

... (tons of error logs)
[11-Nov-2012 09:33:06 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:33:06 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:33:06 Asia/Jakarta] PHP Fatal error:  Allowed memory size of 67108864 bytes exhausted (tried to allocate 32 bytes) in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 651

... (tons of error logs)
[11-Nov-2012 09:34:19 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:34:19 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:34:19 Asia/Jakarta] PHP Fatal error:  Allowed memory size of 67108864 bytes exhausted (tried to allocate 71 bytes) in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 651

... (rons of error logs)
[11-Nov-2012 09:34:22 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:34:22 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:34:22 Asia/Jakarta] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 132
[11-Nov-2012 09:34:24 Asia/Jakarta] PHP Fatal error:  Allowed memory size of 67108864 bytes exhausted (tried to allocate 523800 bytes) in /home/USERNAME/public_html/forum/Sources/Subs-Db-mysql.php on line 640
[11-Nov-2012 09:38:19 Asia/Jakarta] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home/USERNAME/public_html/forum/Sources/ModerationCenter.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/USERNAME/public_html/forum/Sources/Security.php on line 544
[11-Nov-2012 09:38:21 Asia/Jakarta] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home/USERNAME/public_html/forum/Sources/ModerationCenter.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/USERNAME/public_html/forum/Sources/Security.php on line 544
[11-Nov-2012 09:38:25 Asia/Jakarta] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home/USERNAME/public_html/forum/Sources/Subs-Auth.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/USERNAME/public_html/forum/Sources/Security.php on line 535
[11-Nov-2012 09:55:27 Asia/Jakarta] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home/USERNAME/public_html/forum/Sources/Subs-Auth.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/USERNAME/public_html/forum/Sources/Security.php on line 535
[11-Nov-2012 09:55:27 Asia/Jakarta] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home/USERNAME/public_html/forum/Sources/Subs-Auth.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/USERNAME/public_html/forum/Sources/Security.php on line 535
[11-Nov-2012 09:55:27 Asia/Jakarta] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home/USERNAME/public_html/forum/Sources/Subs-Auth.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/USERNAME/public_html/forum/Sources/Security.php on line 535

Then, my account suspended.

The hosting said that my account is getting high mysql activity,
And this is what the hosting said about the error log in MySQL:

| 183 | DELAYED | localhost | eximstats | Delayed insert | 3 | Waiting for INSERT | |
| 831 | eximstats | localhost | eximstats | Sleep | 2 | | |
| 2295 | USERNAME_smf913 | localhost | | Query | 100 | Copying to tmp table | SELECT
mem.id_member, mem.real_name, COUNT(*) as posts,
mem.avatar, a.id_attach, a.attachmen |
| 2403 | DELAYED | localhost | eximstats | Delayed insert | 2 | Waiting for INSERT | |
| 2404 | DELAYED | localhost | eximstats | Delayed insert | 2 | Waiting for INSERT | |
| 2428 | USERNAME_smf913 | localhost | | Query | 93 | Copying to tmp table | SELECT
mem.id_member, mem.real_name, COUNT(*) as posts,
mem.avatar, a.id_attach, a.attachmen |
| 2656 | USERNAME_smf913 | localhost | | Query | 61 | Copying to tmp table | SELECT
mem.id_member, mem.real_name, COUNT(*) as posts,
mem.avatar, a.id_attach, a.attachmen |
| 2671 | USERNAME_smf913 | localhost | USERNAME_smf913 | Query | 58 | Locked | UPDATE smf_members
SET last_login = 1352602413, member_ip = '180.246.183.190', member_ip2 = '180.2 |
| 2688 | USERNAME_smf913 | localhost | | Query | 56 | Locked | SELECT
IFNULL(lo.log_time, 0) AS is_online, IFNULL(a.id_attach, 0) AS id_attach, a.filename, a.at |
| 2750 | USERNAME_smf913 | localhost | USERNAME_smf913 | Query | 46 | Locked | SELECT
c.id_cat, b.name AS bname, b.description, b.num_topics, b.member_groups,
b.id_parent, |
| 2752 | USERNAME_smf913 | localhost | USERNAME_smf913 | Query | 46 | Locked | SELECT
m.smileys_enabled, m.poster_time, m.id_msg, m.subject, m.body, m.id_topic, t.id_board,
|
| 2770 | USERNAME_smf913 | localhost | USERNAME_smf913 | Query | 44 | Locked | SELECT
m.smileys_enabled, m.poster_time, m.id_msg, m.subject, m.body, m.id_topic, t.id_board,
|
| 2836 | USERNAME_smf913 | localhost | | Query | 34 | Locked | SELECT
lo.id_member, lo.log_time, lo.id_spider, mem.real_name, mem.member_name, mem.show_online,
|
| 2943 | USERNAME_smf913 | localhost | | Query | 21 | Locked | SELECT
IFNULL(lo.log_time, 0) AS is_online, IFNULL(a.id_attach, 0) AS id_attach, a.filename, a.at |
| 3058 | USERNAME_smf913 | localhost | USERNAME_smf913 | Query | 11 | Locked | SELECT
c.id_cat, b.name AS bname, b.description, b.num_topics, b.member_groups,
b.id_parent, |
| 3068 | USERNAME_smf913 | localhost | | Query | 10 | Locked | SELECT mem.*, IFNULL(a.id_attach, 0) AS id_attach, a.filename, a.attachment_type
FROM `USERNAME_ |
| 3130 | USERNAME_smf913 | localhost | USERNAME_smf913 | Query | 2 | Locked | SELECT
c.id_cat, b.name AS bname, b.description, b.num_topics, b.member_groups,
b.id_parent, |