Need Help! Server Timeing out

[ChopSticks]

Hi,

 

I'm a newb here but used OsCommerce for a over a year now. Just recently I got an error,

 

Fatal error: Maximum execution time of 30 seconds exceeded in /home/drtparts/public_html/admin/includes/functions/general.php on line 177

 

I get this error when trying to view my categories in the admin. I also want to mention that I have over 1000 categories. The frontend website itself seem to work fine. Please help me.. You can check out my website at drtparts.com. Thanks

[satish]

Hi,

 

I'm a newb here but used OsCommerce for a over a year now. Just recently I got an error,

 

Fatal error: Maximum execution time of 30 seconds exceeded in /home/drtparts/public_html/admin/includes/functions/general.php on line 177

 

I get this error when trying to view my categories in the admin. I also want to mention that I have over 1000 categories. The frontend website itself seem to work fine. Please help me.. You can check out my website at drtparts.com. Thanks

 

For a normal size dat base 30 seconds is a sufficient time to be queried and the page being displayed.

 

If some values have been modified manually defying the logic then the queries Might get stuck searching for a nonexistant category or products.

So you need to first check by extending the Time limit.

 

Satish

[Jan Zonjee]

Fatal error: Maximum execution time of 30 seconds exceeded in /home/drtparts/public_html/admin/includes/functions/general.php on line 177

 

I get this error when trying to view my categories in the admin. I also want to mention that I have over 1000 categories.

Perhaps the query right before it is a bit slow? Does it help if you change the query on line 175 to one using a left join? (original query commented out):

/*	$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and c.parent_id = '" . (int)$parent_id . "' order by c.sort_order, cd.categories_name"); */
	$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c left join " . TABLE_CATEGORIES_DESCRIPTION . " cd using(categories_id) where cd.language_id = '" . (int)$languages_id . "' and c.parent_id = '" . (int)$parent_id . "' order by c.sort_order, cd.categories_name");

[Jan Zonjee]

Fatal error: Maximum execution time of 30 seconds exceeded in /home/drtparts/public_html/admin/includes/functions/general.php on line 177

 

I get this error when trying to view my categories in the admin. I also want to mention that I have over 1000 categories.

I had another look at the particular function (tep_get_category_tree) in which the execution time is exceeded but it generates an insane amount of queries (a query for each category and subcategory). I now understand why the page categories.php in admin can get so slow.

 

I rewrote that function to do it all in one query and it seems to work well, also when there are subsubcategories. I suggest you comment out the original function and add this to admin/includes/functions/general.php.

	function tep_get_category_tree($parent_id = '0', $spacing = '', $exclude = '', $category_tree_array = '', $include_itself = false) {
global $languages_id;

if (!is_array($category_tree_array)) $category_tree_array = array();
if ( (sizeof($category_tree_array) < 1) && ($exclude != '0') ) $category_tree_array[] = array('id' => '0', 'text' => TEXT_TOP);

$category_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id, pcategories.parent_id as grand_parent_id from " . TABLE_CATEGORIES . " c  left join " . TABLE_CATEGORIES . " AS pcategories on pcategories.categories_id = c.parent_id," . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by grand_parent_id, c.parent_id, c.sort_order, cd.categories_name");

while ($categories = tep_db_fetch_array($category_query)) {
$_category_tree_array[] = array('grand_parent_id' => $categories['grand_parent_id'], 'parent_id' => $categories['parent_id'], 'id' => $categories['categories_id'], 'text' => ($categories['parent_id'] != '0' ? "& nbsp;& nbsp;& nbsp;".$categories['categories_name']: $categories['categories_name']));  
} 

foreach ($_category_tree_array as $index_of => $sub_array) {
	if (!tep_not_null($sub_array['grand_parent_id'])) {
		$parent_categories[] = array('id' => $sub_array['id'], 'text' =>  $sub_array['text']);		   
   }
}

for ($x = 0; $x < count($parent_categories); $x++) {
	array_push($category_tree_array, $parent_categories[$x]);
	foreach($_category_tree_array as $index_of_cta => $sub_array_cta) {
		if ($sub_array_cta['parent_id'] == $parent_categories[$x]['id']) {
			array_push($category_tree_array, array_slice($_category_tree_array[$index_of_cta], 2));
			// insert subsubcategories
			$categories_counter = $sub_array_cta['id'];
	foreach($_category_tree_array as $index_of_cta1 => $sub_array_cta1) {
		if ($sub_array_cta1['parent_id'] ==  $categories_counter && !in_array($categories_counter, $parent_categories) ) {
			$subsubcategory_to_add = array_slice($_category_tree_array[$index_of_cta1],2);
			$subsubcategory_to_add['text'] = "& nbsp;& nbsp;& nbsp;".$subsubcategory_to_add['text'];
			array_push($category_tree_array, $subsubcategory_to_add);
		}
	}
			// EOF insert subsubcategories
		}
	}
}
return $category_tree_array;
 }

 

Note that I had to add the nobreakspaces in the code above as & nbsp; otherwise it would just show spaces. So fix that when you cut and paste (in two places, three each).

 

You could do more to cut down the number of queries by replacing the function tep_get_path (starts around line 63 in the same file) with the one from this contribution That will help even if you don't change the following two queries in categories.php (starts around line 775) to:

	$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id, pcategories.parent_id as grand_parent_id, c.sort_order, c.date_added, c.last_modified from " . TABLE_CATEGORIES . " c left join " . TABLE_CATEGORIES . " AS pcategories on pcategories.categories_id = c.parent_id, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and cd.categories_name like '%" . tep_db_input($search) . "%' order by c.sort_order, cd.categories_name");
} else {
  $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id, pcategories.parent_id as grand_parent_id, c.sort_order, c.date_added, c.last_modified from " . TABLE_CATEGORIES . " c left join " . TABLE_CATEGORIES . " AS pcategories on pcategories.categories_id = c.parent_id, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by c.sort_order, cd.categories_name");

and change the tep_get_path($categories['categories_id']) on line 795 and 800 to:

tep_get_path($categories['categories_id'], $categories['parent_id'], $categories['grand_parent_id']).

From a CVS version of last July I see that in the upcoming version of osC this will be fixed.

 

Let us know if this works without problems.

[ChopSticks]

Wow this is the kind of help I was looking for. However, can you let me know what lines I need to replace? Sorry, I'm a newb and do not know much about PHP. I really apreciate your help!

[Jan Zonjee]

However, can you let me know what lines I need to replace? Sorry, I'm a newb and do not know much about PHP. I really apreciate your help!

Well, there is not much I can elaborate on this. I posted the complete function so everything you need to do is make a backup of your file admin/includes/functions/general.php (for safety) and then comment out the old function (for safety, line 167-186 in a stock file) and copy and paste the new one below or above it (don't forget to change the & nsbp;.

 

Commenting out a large piece of code is done by putting /* in front of it and */ at the end of it.

So:

/*   function tep_get_category_tree($parent_id = '0', $spacing = '', $exclude = '', $category_tree_array = '', $include_itself = false) {

etc. etc. etc.

return $category_tree_array;
 } */

The rest is icing on the cake. The new function saves you a query for each category/subcategory, so if you have 1000 categories, it will save you that amount. In a stock installation with the default products and categories there about 60 queries, including the 20 for tep_get_category_tree, so you do the math.

[ChopSticks]

Well, there is not much I can elaborate on this. I posted the complete function so everything you need to do is make a backup of your file admin/includes/functions/general.php (for safety) and then comment out the old function (for safety, line 167-186 in a stock file) and copy and paste the new one below or above it (don't forget to change the & nsbp;.

 

Commenting out a large piece of code is done by putting /* in front of it and */ at the end of it.

So:

/*   function tep_get_category_tree($parent_id = '0', $spacing = '', $exclude = '', $category_tree_array = '', $include_itself = false) {

etc. etc. etc.

return $category_tree_array;
 } */

The rest is icing on the cake. The new function saves you a query for each category/subcategory, so if you have 1000 categories, it will save you that amount. In a stock installation with the default products and categories there about 60 queries, including the 20 for tep_get_category_tree, so you do the math.

 

 

I did everything by the book and got this error:

 

Fatal error: Maximum execution time of 30 seconds exceeded in /home/drtparts/public_html/admin/includes/functions/general.php on line 250

 

This is what line 250 says:

 

foreach($_category_tree_array as $index_of_cta1 => $sub_array_cta1) {

 

Ahh this is driving me crazyyy!

[Jan Zonjee]

Fatal error: Maximum execution time of 30 seconds exceeded in /home/drtparts/public_html/admin/includes/functions/general.php on line 250

 

Ahh this is driving me crazyyy!

Hmm, not funny. Try appending a cPath=## to the url (I found there are less queries on the subpages), so for example make it blabla/name_of_your_admin_folder/categories.php?cPath=2 and see if that produces a page. If not you will have to find out what other things makes this page so slow (I assume this happened suddenly?).

[ChopSticks]

Hmm, not funny. Try appending a cPath=## to the url (I found there are less queries on the subpages), so for example make it blabla/name_of_your_admin_folder/categories.php?cPath=2 and see if that produces a page. If not you will have to find out what other things makes this page so slow (I assume this happened suddenly?).

 

I appreciate you staying with me on this topic. I tried inputing the url like you said and still get the same error. I inputed admin/categories.php?cPath=2. Oh yes, and I checked with one of the guys who was adding categories to the site, and he told me that there are about 13,000 categories. We basically sell car parts and needed to add every car make and model. And yes this did happen suddenly, Any other ideas?

[Jan Zonjee]

he told me that there are about 13,000 categories.

Oops.

We basically sell car parts and needed to add every car make and model. And yes this did happen suddenly, Any other ideas?

I think the first thing you should try is follow Satish's suggestion and ask your ISP if they can set the maximum execution time of PHP to say 60 seconds. Then if a page or subpage (cPath=##) comes up you can try the next thing (not easy for a newbie perhaps) and add Output Queries Debug (I suggest using version 1.4, not 1.6) to your admin files (no need yet for your catalog side, that might change also with 13.000 categories). It is not really described for the admin part, but it is basically the same thing. That will tell what queries take too much time.

 

You could also try to execute the query for the category tree in phpMyAdmin and see how long that takes:

select c.categories_id, cd.categories_name, c.parent_id, pcategories.parent_id as grand_parent_id from categories c left join categories AS pcategories on pcategories.categories_id = c.parent_id, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' order by grand_parent_id, c.parent_id, c.sort_order, cd.categories_name;

If that takes too long you might need to put an index on that table. I have heard of that from others on the forum, but I'm not an expert in SQL so .... perhaps an index on the id field?

 

With 13.000 categories you probably going to need an SQL expert to help you with this I'm afraid. A good start might be the thread on "Store Optimization from a vanilla install" in the Tips & Tricks section.

[Guest]

just use the advanced cache contribution. You don't have to change/optimize every single sql query and rewrite your osc store.

http://www.oscommerce.com/community/contributions,2873

it's far more efficient.

[Jan Zonjee]

Any other ideas?

For the time being I think it would be wise to forgo about the drop-down menu with 13.000 categories. A simple way to accomplish that is to add a return before the query is executed in tep_get_category_tree:

   function tep_get_category_tree($parent_id = '0', $spacing = '', $exclude = '', $category_tree_array = '', $include_itself = false) {
global $languages_id;

if (!is_array($category_tree_array)) $category_tree_array = array();
if ( (sizeof($category_tree_array) < 1) && ($exclude != '0') ) $category_tree_array[] = array('id' => '0', 'text' => TEXT_TOP);

// function stops here: only Top will be displayed in the drop-down
return $category_tree_array;

$category_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id, pcategories.parent_id as grand_parent_id from " . TABLE_CATEGORIES . " c  left join " . TABLE_CATEGORIES . " AS pcategories on pcategories.categories_id = c.parent_id," . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by grand_parent_id, c.parent_id, c.sort_order, cd.categories_name");

etc.etc.

[Jan Zonjee]

For the time being I think it would be wise to forgo about the drop-down menu with 13.000 categories.

If that helps, you might just leave the subsubcategories out from the drop-down menu. Look in the function for where it says : BOF insert subsubcategories and EOF insert subsubcategories and comment out the part in between.

[ChopSticks]

If that helps, you might just leave the subsubcategories out from the drop-down menu. Look in the function for where it says : BOF insert subsubcategories and EOF insert subsubcategories and comment out the part in between.

 

What do you mean by drop down menu?

[Jan Zonjee]

What do you mean by drop down menu?

The drop-down menu/box that is in the top right corner of the categories.php page. For the upcoming 3.0 version of osC there is a picture in the documentation. In 2.2 it is right under the search box, and the text "Go to:" stands before it.

[ChopSticks]

What do you mean by drop down menu?

 

Oh right. I'll try that and let you know. What do you think of enigma1 idea?

[Jan Zonjee]

What do you think of enigma1 idea?

No idea it that works well for the admin part. I have the feeling that to refresh the cache the page needs to load, so as long as that is not happening...

[ChopSticks]

If that helps, you might just leave the subsubcategories out from the drop-down menu. Look in the function for where it says : BOF insert subsubcategories and EOF insert subsubcategories and comment out the part in between.

 

I tried doing a search for the term EOF insert subsubcategories in the general.php file and categories.php file however I cant find it. What file am I suppose to edit?

 

I also tried

adding a return before the query is executed in tep_get_category_tree

and It didnt help.

[ChopSticks]

If that helps, you might just leave the subsubcategories out from the drop-down menu. Look in the function for where it says : BOF insert subsubcategories and EOF insert subsubcategories and comment out the part in between.

 

It worked!!!!!!! I guess it was the drop down that caused the timeout. Now it makes sense, the dropdown displays all of the categories including the subcategories which can take some time to load.

 

Man you made my day. I appreciate your patience with me and I will definately do the same to other community members. Thanks JanZ!!

[ChopSticks]

It worked!!!!!!! I guess it was the drop down that caused the timeout. Now it makes sense, the dropdown displays all of the categories including the subcategories which can take some time to load.

 

Man you made my day. I appreciate your patience with me and I will definately do the same to other community members. Thanks JanZ!!

Ahh now im getting this error at the bottom of the page.

 

Fatal error: Cannot redeclare tep_db_connect() (previously declared in /home/drtparts/public_html/admin/includes/functions/database.php:13) in /home/drtparts/public_html/admin/includes/functions/database.php on line 13

 

doesnt seem to affect anything.. but its annoying!

[ChopSticks]

JanZ, still there?

[Jan Zonjee]

JanZ, still there?

Yes, but this seems to be another issue. I have read-up about it on forum posts and it is often caused by inadvertently copying a file that belongs in the admin or catalog part to the language files. Then when they are "loaded" both call application_top.php causing that error.

 

I have no clue what would cause this on that page, because you haven't mentioned any contributions added lately...

[Guest]

If that helps, you might just leave the subsubcategories out from the drop-down menu. Look in the function for where it says : BOF insert subsubcategories and EOF insert subsubcategories and comment out the part in between.

 

We have about 4,000 categories but sub-categories that can be 10 deep from the top levels sometimes. Have you noticed that the new function you provided only seems to go 3 deep? It does indeed run faster with our 4,000 categories but presents a problem when trying to use the copy or move function in sub-categories that are deep off the top. As you know the copy and move function uses the dropdown to select the target subcategory and since they no longer get displayed, these functions have become unusable.

 

thoughts?

[Jan Zonjee]

We have about 4,000 categories but sub-categories that can be 10 deep from the top levels sometimes. Have you noticed that the new function you provided only seems to go 3 deep?

Ouch, I (apparently mistaken) assumed that osCommerce only allowed you to go three levels deep...

 

Of course more loops could be added but that would be dependent on how deep your site goes (so seven more in your case...). That will take more time in PHP because then you will loop through the array with categories 10 times for every category (although with the contribution I uploaded I added an unset feature, so that sorted categories are taken out of the array, but still).

 

It does indeed run faster with our 4,000 categories but presents a problem when trying to use the copy or move function in sub-categories that are deep off the top. As you know the copy and move function uses the dropdown to select the target subcategory and since they no longer get displayed, these functions have become unusable.

Actually, I didn't know that. I did notice that the function gets called a few times, but assumed it was for the same drop-down menu. Haven't looked any further. Apparently, I should have.

 

I will take a look again at how this was implemented in MS3 (I downloaded a snapshot once). Don't know it that will bring anything, but you never know.

 

Perhaps it is possible to just get the deeper categories when a subsubcategory is chosen? With 4.000 categories it don't think it makes much sense to have them all in the drop-down, does it?

[Guest]

Ouch, I (apparently mistaken) assumed that osCommerce only allowed you to go three levels deep...

 

Of course more loops could be added but that would be dependent on how deep your site goes (so seven more in your case...). That will take more time in PHP because then you will loop through the array with categories 10 times for every category (although with the contribution I uploaded I added an unset feature, so that sorted categories are taken out of the array, but still).

 

Actually, I didn't know that. I did notice that the function gets called a few times, but assumed it was for the same drop-down menu. Haven't looked any further. Apparently, I should have.

 

I will take a look again at how this was implemented in MS3 (I downloaded a snapshot once). Don't know it that will bring anything, but you never know.

 

Perhaps it is possible to just get the deeper categories when a subsubcategory is chosen? With 4.000 categories it don't think it makes much sense to have them all in the drop-down, does it?

 

 

actually, you might have another issue we found last night. when using your optimization code, which as listed above doesn't display the dropdown correctly (not all levels displayed) and you try to delete a lower level sub-category, this new code actually will delete all your products from the database as well as all categories! Set yourself up on a test database and create a simple structure with several sub-categories deep and then go an try to delete one of those lower level subcategories. we have been able to reproduce this problem. on our test site it started deleting all records in the following tables: products, products_description, products_to_categories, categories, categories_description, reviews and specials...at least those are the major ones we noticed. It wiped out 5,000 products and about 2,000 categories in about 5 seconds. we re-loaded the database and was able to reproduce this at will. needless to say we aren't going to run this code at this time.

 

as for the need, of course there is a need to populate the dropdown. here is an example. we sell car accessories. let's say your path looks something like this: top->interior accessories->floor mats->cadillac and let's sayyou decide for organization you create a few more categories under the cadillac subcategory called: CTS, Deville, Escalade then need to move the existing products into those new respective subcategories. The new code would prevent any work from being performed at these deep levels.