Search index error

[IronZebra]

Recently while trying to index my forum I ran into a particular error.  It seems the actual forum software continues to work but the search indexing breaks down.

The forum reports an error within ManageSearch.php on line 646. I've checked this line and it contains an array called smcFunc which contains values for insert, doesn't seem terribly conspicous, but I'm not sure, just to ensure that it wasn't a bad string or something I tried to dump the contents of the array to a temporary file just to see what it contained.

It contains an erray of items that I'm guessing is trying to be saved in the database.

Just to ensure things were connecting to the db properly, I checked other pages and they all seem to load fine. But I also tailed the apache error log and I kept getting this show up...hundreds of times a second actually

[Sat Jan 14 12:23:08 2012] [error] [client 192.168.0.195] PHP Warning:  mysql_real_escape_string() expects parameter 2 to be resource, boolean given in /home/ironzebra/public_html/forum/Sources/Subs-Db-mysql.php on line 132, referer: http://www.ironzebra.com/forum/index.php?action=admin;area=managesearch;isa=createmsgindex;resume=;a75299d=1cd30d47b4985d6dd23ea7b0f2279885 [nofollow]

I saw in another post that it would be a a good idea to check settings so using the repair_settings.php tool I ran a quick check. Everything was set up correctly.

So any ideas here would be helpful.

Just some info for everyone, I have a fairly large forum approximately 600k in posts and some 6000 ish members. I was creating a medium sized index, which is already up to approximately 700 MiB in size. It's all running in a VM at the moment just so I can build the index in a reasonable time frame, I've pulled a copy of the database and creating the index against that locally on a Win 7 machine with 4 GB of RAM that I don't use very much.

The VM runs ubuntu 11.10 with php 5.3, apache 2.2 and mysql 5.1 installed. It's provisioned with 2 GB of RAM and can utilize 1.8 GHz of CPU power. The main parition has about 10 GB of hd space, which is less about 42% used.

Any ideas on how to fix this would be fantastic.

Thanks
IronZebra

Which led me to beleive that the problem is more like an issue with the db

[Arantor]

Firstly, don't use the regular search facilities for that many posts, instead use Sphinx to handle the indexing, it can do a much, much better job of it.

Apply to the Big Boards group here (in your profile > Group Membership) and in there you'll find details on how to use Sphinx instead of trying to use SMF's stuff for it.

I suspect, actually, that the reason it's failing the way it is, isn't really an issue in SMF at all, but something more subtle. There was a thread on it a bit back where some in depth investigation on the matter, ultimately what happens is that between the page starting to run and by the time it gets so far in, the DB connection closes (so the real-escape routine gets a second parameter of boolean false rather than the resource it's expecting), and that triggers an error. Then, because there's been an error, it tries to contact the DB to log the error, only to find it can't because the DB connection has been closed - and then triggers another error etc.

Something is causing the connection to close, and it probably isn't the search, but the general settings of MySQL itself.

[IronZebra]

I suspect, actually, that the reason it's failing the way it is, isn't really an issue in SMF at all, but something more subtle. There was a thread on it a bit back where some in depth investigation on the matter, ultimately what happens is that between the page starting to run and by the time it gets so far in, the DB connection closes (so the real-escape routine gets a second parameter of boolean false rather than the resource it's expecting), and that triggers an error. Then, because there's been an error, it tries to contact the DB to log the error, only to find it can't because the DB connection has been closed - and then triggers another error etc.

Something is causing the connection to close, and it probably isn't the search, but the general settings of MySQL itself.

Interesting so you believe it's the mysql timeout setting. That's plausible, I'll try increasing the timeout setting. I would go the Sphinx way but I'm a little reluctant to use it because of my lack of knowledge regarding Sphinx.

[Arantor]

I believe it's something causing the connection to die before the page load ends, which is usually timeout related, but can be caused for other reasons.

The Sphinx way is definitely an improvement, because it's a dedicated solution for searching, which works faster, uses less memory on the server and actually requires less maintenance once set up.

[IronZebra]

It doesn't seem to be timeout related I increased the php mysql connection timeout limit to double it's original and increased it's memory allocation as well to be on the safe side. Unfortunately 0 improvement.

[Arantor]

(And rebooted MySQL's daemon?)

Timeout is one of the possible causes, there are others. The last time I saw it really in the wild was actually a bug in the MySQL library being used, where too many connections was causing earlier connections to drop unexpectedly, and SMF doesn't handle it gracefully.

[IronZebra]

No the apache daemon because it was a setting in php.ini but I'll restart the mysql daemon too.

[Arantor]

It's not a PHP issue, it's a timeout on MySQL's end because it's closing the connection abruptly (if it was doing it on the client side, it should have done something different)

[IronZebra]

Unfortunately nothing seems to have changed. I am still experiencing the same issue, perhaps there's a mysql setting I need to change too. I'll try that and let you know how it goes.

FYI thanks for your help.