Author Topic: Entire products disappearing from Categories at random - **SOLVED!**  (Read 6578 times)

TomTomm

  • Beginner
  • *
  • Posts: 4
    • Chadwick Web Design
From a fully operational store, with all categories using the same browse page (browse_1), the entire range of products from some categories are disappearing from the front-end view at random.  
I cannot find anything different to distinguish the faulty categories from the good ones. Have come up with a work-around: have created an unpublished spare category, and if I move all products from faulty category temporarily into this, and then back again, the products reappear as they should in the original category.
I've looked at some of the underlying php code which populates the category template and put a trace on some of the variables by echoing their values to the screen and it seems that in the case of the faulty categories, the $products array has no value (is null). This is inspite of the fact that there are clearly products in the categories which can still be seen in the back end.
Anyone any idea what's wrong? Obviously I cannot hand over to client with this workaround and have products keep disappearing.
Also there is one category from which the products disappear when you log in, but re-appear straight away if you log out. HELP!

stinga

  • Contributing Developer
  • Full Member
  • *
  • Posts: 872
    • Squangle ltd
Re: Entire products disappearing from Categories at random
« Reply #1 on: December 12, 2009, 16:23:35 pm »
G'day,

It won't be random, something will be doing the delete.
Does the category get deleted as well if so then it is the category that is getting deleted, it will take all the products with it.

You might have to enable logging to detect when it happens, or you could remove delete privilege in mysql from your logon, this might throw up an error or too, if nothing else it will stop products from being deleted.
Stinga.
614869 products in 747 categories with 15749 products in 1 category.
                                             Document Complete   Fully Loaded
                Load Time First Byte Start Render   Time      Requests      Time      Requests
First View     2.470s     0.635s     1.276s          2.470s       31            2.470s      31
Repeat View  1.064s     0.561s     1.100s          1.064s       4             1.221s       4

TomTomm

  • Beginner
  • *
  • Posts: 4
    • Chadwick Web Design
Re: Entire products disappearing from Categories at random
« Reply #2 on: December 14, 2009, 12:01:15 pm »
The category does NOT get deleted. It is still there, but on the front end there are no products showing. From the back end you can still see all the products and if these are moved to another category and then moved back to the original, they become visible again on the front end.
Any ideas?

rb

  • Advanced
  • Full Member
  • *****
  • Posts: 1503
Re: Entire products disappearing from Categories at random
« Reply #3 on: December 14, 2009, 15:55:15 pm »
If you have SEF turned on, try it with it off (just a wild guess that perhaps the value for category_id isn't being set properly).

stinga

  • Contributing Developer
  • Full Member
  • *
  • Posts: 872
    • Squangle ltd
Re: Entire products disappearing from Categories at random
« Reply #4 on: December 15, 2009, 11:19:49 am »
G'day,

So nothing gets deleted, just moved.
Are the product in the back end correct, if so the problem is most likely an SEF.
Have you turned SEF off and see what happens?
Stinga.
614869 products in 747 categories with 15749 products in 1 category.
                                             Document Complete   Fully Loaded
                Load Time First Byte Start Render   Time      Requests      Time      Requests
First View     2.470s     0.635s     1.276s          2.470s       31            2.470s      31
Repeat View  1.064s     0.561s     1.100s          1.064s       4             1.221s       4

TomTomm

  • Beginner
  • *
  • Posts: 4
    • Chadwick Web Design
Re: Entire products disappearing from Categories at random - **SOLVED!**
« Reply #5 on: December 19, 2009, 14:45:36 pm »
Problem solved!

With thanks to stinga and rb for their suggestions... after much head-scratching I finally figured out why the products were not being displayed and it was nothing to do with SEF.
It was actually a MySQL query problem (caused no doubt by the large number of products and categories together with multiple pricing options and attribute lists).
At the end of this post is the fix I employed, but I thought it might be helpful to others to explain how I tracked down the fault.

1) To a few of the relevant php files (browse_1.php, shop.browse.php, ps_products.php, etc)  I added a line to force verbose PHP error reporting but no errors were reported

2) I then decided to look at the values of a few of the variables in the file shop.browse.php which does a lot of the work involved in listing the products. Clearly, the $products array should have had a number of elements but was apparently empty. Had the site been at the testing/development stage I could have just used an 'echo' statement to print the variables to the screen, but unfortunately the site was live so I couldn't do that. Instead I used  Firefox with Firebug/ FirePHP to echo the variables to the Firebug console. The upshot of this was that in cases where the products were not showing up, the $products array was indeed empty. Debugging with FirePHP also gave me the full SQL statement which looked correct and should have yielded a list of products, so I copied out the SQL into Notepad.

3) Using the excellent program Navicat MySQL (although I guess PHPMyAdmin would do just as well) I ran the suspect SQL query and lo and behold it failed with the error 1104 - 'The select would examine more than SQL_MAX_JOIN_SIZE'. Whether this was because of an overly complicated store or down to sloppy practice in the coding of the Virtuemart SQL I'm not sure, but the perceived wisdom was to overcome the problem by prefacing the query with an instruction to expect complicated SQL statements (SET OPTION SQL_BIG_SELECTS=1).

4) I looked beyond Virtuemart to the host Joomla environment and the way all SQL is handled. SOOOO.... here's the fix:
--------------------------------------------------
in the Joomla file mysql.php I found a line which says:
$this->setQuery( "SET sql_mode = 'MYSQL40'" );
(around line 153).
UNDER this line I added:
$this->setQuery( "SET OPTION SQL_BIG_SELECTS=1" );
---------------------------------------------------

So that's it... the fact that products disappeared mainly when the user was logged in was due to the increased complexity of the query (since logged-in users bring into play the extra info relating to shopper groups, etc.) However, getting to the bottom of this does raise another question. Why, when MySQL 5 has been around for so long and is faster, more secure and altogether more efficient than MySQL 4 does Joomla force all SQL transactions into version 4 compatability mode?

rb

  • Advanced
  • Full Member
  • *****
  • Posts: 1503
Re: Entire products disappearing from Categories at random - **SOLVED!**
« Reply #6 on: December 19, 2009, 15:14:43 pm »
TomTomm, thanks for the nice write-up.  Did the MySQL 1104 error show up in the server's errorlog?

stinga

  • Contributing Developer
  • Full Member
  • *
  • Posts: 872
    • Squangle ltd
Re: Entire products disappearing from Categories at random - **SOLVED!**
« Reply #7 on: December 27, 2009, 19:53:15 pm »
G'day,

Great you found the problem.

For live sites I use...
file_put_contents('/tmp/stinga.log',"Here is a debug line\n",FILE_APPEND);

I assume there is a function that does this, put this is simpler.

Yeah...
I also wonder why
$this->setQuery( "SET sql_mode = 'MYSQL40'" );
is used

Anyone tried removing it?
Stinga.
614869 products in 747 categories with 15749 products in 1 category.
                                             Document Complete   Fully Loaded
                Load Time First Byte Start Render   Time      Requests      Time      Requests
First View     2.470s     0.635s     1.276s          2.470s       31            2.470s      31
Repeat View  1.064s     0.561s     1.100s          1.064s       4             1.221s       4

denversdesk

  • Beginner
  • *
  • Posts: 2
Re: Entire products disappearing from Categories at random - **SOLVED!**
« Reply #8 on: March 08, 2010, 21:43:49 pm »
I still have products not showing. If you check this link I'm supposed to have 3 products showing and yet nothing shows up. I've done all that you've mentioned and also tried deleting and re-entering the products... still no change.

xdoktor

  • Jr. Member
  • **
  • Posts: 71
Hey, i have been running site very smoothly for 2 years until this same problem happened to me!!

after uninstalling the last 'free version' of sh404sef(you have to pay for it now!) i must of turned on 'system sef'....thinking this would help with problems....it made things worse!

after then small problems started to appear in navigation....

i too deleted all my catergories after moving all products to new temp catergory(easy for us as we only have 20 products :-) )
same problem...products not showing....

i turned off 'system sef' after reading this thread and wada u no...it works again!!

thanks guys & gals