IPB 2.1.4 post-upgrade rebuild problem

[ZYV]

Hello,

I have just upgraded my forum (shurup.com) from the old outdated IPB 1.2 (hopefully I tried to protect it to not get exploited, but anyway it was big time to upgrade) to the latest available IPB 2.1 with all security patches installed. The upgrade was quite successfull, BUT now, according the the Invision's manual I need to rebuild all the posts, pictures etc. in the forum for it to function correctly, because many things have changed: the style of quotes, the BB tags, the smilies paths etc.

I have 29 000 posts in total and I started the rebuild thing using 50 posts per iteration setting. All went well until the 1970'th post... it started to show "500 Internal server error" message. The cPanel logs aren't helpful because they keep saying

Premature end of script headers: /home/***/admin.php

while I want to find out the reason. I haven't found the error_log file either. I have tried many times with different posts per iteration settings (even 1) but it just didn't work. Then it came to my mind that I should check top if some of those "dead" scripts are still running... I was scared by what I've seen - 2 of them were actually running and probably taking a lot of ressources I killed those processes using kill. Well, I am not sure how it would work if I didn't stop them and possible it wouldn't have done anything harmfull at all, but still I am considering myself lucky that I quickly discovered them and didn't get my account suspended.

Now back to the topic: what should I do? Perhaps some of you already encountered this problem? I need to get the posts rebuild! But also I am afraid to experiment, because I don't want to harm other customers if something goes out of control.

Any clues? I have heard of the "nice" program which is something I can use to make my scripts run with lowest possible priority, but I am not sure of how it works and can it help me. Perhaps there is some way to run the rebuild system from the console?

[ZYV]

Well, actually I tried to run this stuff again and it did stop at 2100 post. It seems to me that I might be running into some kind of overload protection system (which is fair I suppose!). I am going to ask IPS if they have some kind of a console tool for that.

Anyone?

[ZYV]

FYI, I have found the problem. In fact, LIMIT clause only limits the output, but SELECT still keeps selecting the whole table, that's how mySQL works. The authors of IPB were unaware of this, so of course, selecting 2k+ rows is unacceptable on shared hosting environment, so the script just dies resulting an 500 Internal server error.

I am currently negociating with local IPB support and I hope they will provide the fix very soon.

[ZYV]

Otherwise I have to say that IPB 2.1.4 runs perfectly on Dathorn's PHP/mySQL setup

[-Oz-]

FYI, I have found the problem. In fact, LIMIT clause only limits the output, but SELECT still keeps selecting the whole table, that's how mySQL works.

I didn't know that. That is very intersting. Wonder what a good way around it is...

[ZYV]

The good way around that is to hire some professional bulletin boards developers as I am waiting for this fix from IPS already for a week Speaking seriousely you should normally use:

SELECT foo FROM bar WHERE condition1 AND (id > 10 AND id < 20) LIMIT 10

instead of doing horrible things like this:

SELECT foo FROM bar WHERE condition1 LIMIT 10,20

I wonder how come they didn't know about that. They are professionals and after all I am just a customer.

[sdjl]

FYI, I have found the problem. In fact, LIMIT clause only limits the output, but SELECT still keeps selecting the whole table, that's how mySQL works.

I've never ever heard that one before.. That would defeat the purpose of having a limit clause in the first place, which is what it is and what it does.
As stated in the manual:

"The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements)."

That to me says it only selects the rows based on your limit code. Where did you hear otherwise?

David

[Buddha]

If the script never exits then it may not be releasing the resources. I doubt this is a LIMIT problem.

mysql_free_result

[ZYV]

Hello,

Sorry for the confusion. I think I was being mislead by the following quote that I have found in phorum.org's old FAQ:

7. What is $cutoff?
---------------------------------------------------------------------------
It was discovered that some queries were selecting the whole table. In our
case, 98,000 rows. MySQL did not like this at all. So, in lieu of a better
solution, we put in this cutoff to limit the rows it selects. You see, a
limit clause in a query does not limit the rows selected, only the rows
returned. Therefore a query like:

select * from table where id<1000200 limit 10

returns 10 rows, but to get those 10 it would select every row with an id
less than 1000200. In this case it could be 1000199 rows. So we made it
now look like:

select * from table where id<1000200 and id>(1000200-$cutoff) limit 10

Now the max you will have selected is $cutoff. I picked 800 because it will
be fine 99.9% of the time.

If someone wanted to display more than 800 messages on a page or they had a
gap in message id's of more than 800 (some strange importers), they would
need to increase this figure.

Now it seems to me that IPB's author clearly doesn't understand how LIMIT works (and I was also mislead by his comments). The first number being the offset from start and the second being the number of rows to select. Anyway when I look into the code I see things like this:

        $start  = input['st'];
        $end    = input['pergo'] ) ? input['pergo'] ) : 100;
        $end   += $start;

        simple_construct( array( 'select' => 'p.*, t.forum_id', 'from' => 'posts p, ibf_topics t', 'where' => "p.topic_id=t.tid", 'order' => 'pid ASC', 'limit' => array($start,$end) ) ); 

I have changed it and so far it works. I hope I will be finally able to rebuild the database. IS there anyone who uses IPB and can confirm the issue?

In any case I am disappointed with the support. Got stupid answers like "Upgrade your mySQL version" or "It's a problem with your mySQL setup." Huh.

[Buddha]

simple_construct( array( 'select' => 'p.*, t.forum_id', 'from' => 'posts p, ibf_topics t', 'where' => "p.topic_id=t.tid", 'order' => 'pid ASC', 'limit' => array($start,$end) ) ); 

ORDER BY probably needs to creates a temporary table to sort that query and MySQL is probably doing a table scan. Your sort of right in your thinking, if the WHERE clause was compared against a constant then MySQL could use an index to sort. However, this certainly isn't a comparison involving a constant:

'where' => "p.topic_id=t.tid" 

In the cases where MySQL have to sort the result, it uses the following algorithm:

* Read all rows according to key or by table scanning. Rows that don't match the WHERE clause are skipped.
* Store the sort-key in a buffer (of size sort_buffer).
* When the buffer gets full, run a qsort on it and store the result in a temporary file. Save a pointer to the sorted block. (In the case where all rows fits into the sort buffer, no temporary file is created)
* Repeat the above until all rows have been read.
* Do a multi-merge of up to MERGEBUFF (7) regions to one block in another temporary file. Repeat until all blocks from the first file are in the second file.
* Repeat the following until there is less than MERGEBUFF2 (15) blocks left.
* On the last multi-merge, only the pointer to the row (last part of the sort-key) is written to a result file.
* Now the code in `sql/records.cc' will be used to read through them in sorted order by using the row pointers in the result file. To optimise this, we read in a big block of row pointers, sort these and then we read the rows in the sorted order into a row buffer (record_rnd_buffer).

MySQL 5.2.7

[ZYV]

Hopefully that's not the case.

My second assumption (Matt doesn't know LIMIT syntax - LIMIT offset_from_start, how_many_rows_to_return and he thinks that its like this LIMIT start_offset, end_offset) turned out to be right.

If somebody needs my fix it's available here: http://www.ibresource.ru/forums/inde...400&hl=rebuild . Trying to convince their support that this bug really exists