Corrupted product database entry leads to blank product listings

I have, of course, run into this over the years but usually it's only one product that creates the problem. The solution is to deactivate the problem product (finding it just takes deactivating each one in the list to see which one is the problem).

However, after the 1.5.3 from 1.3.9h, we have found multiple entries in one category. And there are more across the site. I never have know why this happens or what actually creates the problem. I just know that you have to create a new product and delete the old. My client is freaking - he thinks the upgrade caused this but I don't know that he had scrutinized his site that closely before the upgrade.

Does anyone know why this happens and is there database cleanup possible?

Could you provide a database export (SQL - via mysqldump or phpmyadmin) (of the products_xxxx tables) containing the bad / good records?

I did remember that the database and php were also upgraded after the site was upgraded.

The dump is pretty fair-sized - 1500 customers and 6000 orders. You can actually find out something looking at it that way?

The customers and orders aren't needed.

A few things to consider:

Does it have any products-and-subcategories nested at the same level?
Are products managed only within ZC? Or externally via something like EasyPopulate?

Are there any debug logs as a result of the "blank product listing"? Or is it not actually a totally blank page?

Are there any charset issues at stake? ie: a mix of utf8 and iso-8859-1

Are there any products incorrectly left in the specials or salemaker tables, but which are broken?

Are master categories out of sync? If you're brave, take a backup and do a reset of master-categories via Store Manager.

What's the PHP version? MySQL version?

Which product-types are affected? Is it isolated to just one certain type?

Does it make a difference if you switch to Classic template temporarily?

DrByte's questions as usual are probably where I should have started my line of questioning!

In phpmyadmin one can export the results from a single SQL query (very useful in cases like this):

1. Run the SQL query.
2. Find the "Query results operations" section (usually under the results from the query).
3. Click "Export" in the "Query results operations" section.
4. Select "custom".
5. Under "Format-specific options:" change from "structure and data" to just "data".
6. Press "Go" to export.

The SQL queries to grab (one at a time):

// Add your DB_PREFIX if one is defined. For example if the DB_PREFIX is zen_
// FROM `products` below would become `zen_products`.

// Replace '1','2' with the product ids of a couple bad and good entries
// preferably the old product (which was disabled) and the new product (replacing it).
SELECT * FROM `products` WHERE `products_id` IN ('1','2');
SELECT * FROM `products_attributes` WHERE `products_id` IN ('1','2');
SELECT * FROM `products_discount_quantity` WHERE `products_id` IN ('1','2');
SELECT * FROM `products_to_categories` WHERE `products_id` IN ('1','2');
SELECT * FROM `products_description` WHERE `products_id` IN ('1','2');
SELECT * FROM `specials` WHERE `products_id` IN ('1','2');

// For rest of the queries replace '4' with the category id returned in
// the first SQL query (FROM `products`).
SELECT * FROM `salemaker_sales` WHERE `sale_categories_all` LIKE '%,4,%' ;

// Doubt this is involved, but make sure it returns no entries.
SELECT `categories_id` FROM `categories` WHERE `parent_id` = '4';

The above queries should hopefully help us see any differences in the data for the affected products. I'm going to guess the differences will be one or more of: character data, sales data, or image data. These are the ones I've seen cause a blank page w/o a debug log when viewing the product listing page in the past.

I suppose it COULD also be a corrupted MySQL table (can run a repair on the tables from phpMyAdmin) - but usually I've gotten debug logs for corrupt tables with Zen Cart 1.5.3.

Ah, yes, all those things. I was hoping for a quick easy answer. I'll pull the entries from the database as soon as I can do that.

But no, not a blank page - the product listing - in search as well in the actual category listing - is blank from where it starts in the code. So it's a partial page that throws no error.

Apsona may have been used to import - the first batch of products we found were all in one category with product ids that are close together which makes me think they were all entered at the same time. So possibly an import gone wrong? That was my first guess but it's just now being noticed after the upgrades.

i haven't found products - categories at the same level.

php 5.4.34 , mysql 5.5.40

only one product type on site.

Switch to classic - no because this is only happening in a few categories - not site wide.

Character set - haven't converted the database to ut8f. I have checked the actual product descriptions and products in the past - rewriting those fields to make sure there wasn't something screwy in them. That has never worked.

bad one:

(1732, 1, 1000, 'D7 & CS & L3', 'D7 CS L3.jpg', '72.9500', 0, '2014-10-29 16:46:55', '2014-11-13 13:04:45', NULL, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, '64.1960', 38, 1, 1, 1, 1, 1, 1),
(1732, 1, 'Lucas 25D4 Distributor 12V 3.0 ohms points coil and set of 8mm Red HT leads', '<p>Developed to replace the troublesome points system with a modern magnetic pick-up giving you a more reliable and effective ignition system.</p>\r\n\r\n<p>This top-entry A-Series distributor with coil and 8mm HT leads is ideal for most A-Series applications such as Mini, MGB, MG Midget, MGA, Triumph, Morris, Land Rover, Sprite etc<br />\r\nplease ask for lengths as we can match sets for you....</p>\r\n\r\n<p>Ideal for performance engines where good spark control is required over the full rev range. This unit has been designed and built for competition engines for reliability and performance.</p>\r\n\r\n<p>The ignition coil supplied is a Powerspark sports coil and has high performance ratings.</p>', '', 3),

good one
(1727, 1, 1000, 'D4 & R2 & L2', 'D4 & R2 & L1.jpg', '66.9400', 0, '2014-10-24 14:16:31', '2014-11-13 13:03:50', NULL, 1.3, 1, 0, 0, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, '58.9072', 38, 1, 1, 1, 1, 1, 1),
(1727, 1, 'Land Rover Series 2 & 3 electronic distributor & red rotor arm & 7mm HT leads', '<p><font class="ITEMMAIN">Fitted with our Powerspark electronic ignition module developed to replace the troublesome points system with a modern magnetic pick-up and the new POWERMAX red rotor arm giving you a more reliable and effective ignition system.<br />\r\n<br />\r\nOne set of Genuine 4 Cylinder POWERSPARK Grey 7mm High Quality double silicone HT Leads. Designed to meet or exceed original equipment specifications.<br />\r\n<br />\r\nDouble silicon cable is used for maximum suppression and minimal interference, resistant to temperature, water, oil degradation and chemical attack. Highest quality and performance ISO 3808 Class Noise Suppression.</font></p>\r\n\r\n<p>To be used with a coil with around 3.0 ohms</p>\r\n\r\n<p>Connect red wire to +VE on ignition coil<br />\r\nConnect black wire to -VE on ignition coil<br />\r\nEnsure Switchable 12v live to +ve on ignition coil only</p>', '', 28),

delia:

bad one:
NULL, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0,

Are all of those supposed to be 0 ?

Yeah, that's the way it is - it is disabled right now and he hasn't put in the rest of the info. Looks like he doesn't need it.

Hi,

Do you have multiple languages installed. ?

wilt:

Hi,

Do you have multiple languages installed. ?

Nope. Dang I wanted a one word reply and the forum won't let. Thus the last sentence.

The "bad" product has no weight, could that affect its display?

I expect no products have weight. He uses table shipping based on country not weight.

Was hoping to see (all) the results from a single product... First the old product (turned off status) . Second with the new product (replaces the old product)...

Any "template" specific product_listing module(s)? Any modifications to the core product_listing?

Have you tried editing and re-saving the products causing issues (using the Zen Cart admin)? While rare, I've seen issues caused by images or the installed gd2 library in the past... Have you tried changing the image to a known good / working image?

I do see both of the two different products posted earlier use images with spaces and punctuation in the name... While probably unrelated, I would strongly recommend all images use only alphanumerical, dash, and underscore characters.

It could also be related to character set or encoding issues (as noted by Dr Byte). What languages are used on the store? Was the original store iso-latin-1 (or something else)? Did any items entered in the original store include characters not part of the iso-latin-1 character set?

What character set is the database currently configured to use? Are they mixed (different on different tables)? What character set is in use for the database communication (DB_CHARSET)? What character set is configured and used by the Zen Cart language files? What character set does the HTML "template" tell browsers is in use?

I am going to be doing the conversion to utf8 today as he is a brit doing business with European customers. Can't avoid this one. Though I really don't expect it will make any difference. As to answers to your questions about the character set, it was a 1.3.9h cart with the normal english setting - now whether products got imported thru apsona with something odd we don't know at this point. I am thinking that is the most likely culprit. I don't know how that can go wrong though.

So much of the questions - situations you mention I looked at the first time this ever came up - that would have been an earlier version of zen cart - maybe much earlier such as 1.3.9h. Though I'm thinking each time this has happened after an upgrade.

No, it's not the images - as all on this site are named similarly.

I check the rest of the database settings for the two samples and there was no difference between them and that's why I only gave you the product and descriptions fields. Both on sale because of salemaker with a percent off. No attributes etc.....

Yes, editing and resaving was the very thing I did and it makes no difference. But if you delete that one entry and recreate it exactly - then it works. That's why I named this post corrupted database field. That's what it appears to be though nothing wrong is discernible.

With no debug logs / PHP errors, and stock ZC files, leaves us with charater set / encoding... phpMyAdmin (if I remember correctly) converts character data for display (from the database) to the defined HTML charset / encoding... So may not see the difference (by default I think the export as UTF8 is also enabled in phpMyAdmin)...

Not sure at this point the final cause, just making some educated guesses.

Okay, doing the database conversion today and we'll see if the problem goes away.

face - eggy.

image handler and too large images - why I didn't go there first I don't know. But this was not what I thought it was.

Makes for a great thread though!!!

Thanks everyone for flogging this horse!

Was the "too big" because it was blowing out RAM for processing the images? Why wasn't Image Handler triggering PHP errors? Or what's the logic error in IH that's causing it to abort without error?