Corrupted product database entry leads to blank product listings

[delia]

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?

[lhungil]

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

[delia]

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?

[DrByte]

The customers and orders aren't needed.

[DrByte]

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?

[delia]

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.

[delia]

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),

[lhungil]

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.

[lhungil]

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.

[delia]

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