0

We have a store, Magento 1.6 with circa 4500 products. Half of the products approx are having this issue:

Symptoms...

Admin Product Grid -> sort by SKU causes a fatal error.

Admin Product Grid -> searching for some products by sku yields no result (but they are in the grid and have a numeric sku set)

Frontend -> the products look and work fine, but cannot be found by searching the sku

Edit...

It seems that the products that are not working dont have an entry in: catalog_product_entity_varchar

If I edit a product, change the sku, save, then re-edit and restore the original sku its all back to normal and working again. dont want to do that 2500+ times. Do you think I can programatically rebuild the missing sku entries in catalog_product_entity_varchar ? is there a better route?

Looking in the category_product_flat tables, the sku field for the broken products is set to null. Why would the sku data not carry over from the main catalog_product_entity table?

any help or pointers greatly appreciated, im out of ideas!

Final Edit....

I wrote a simple script that went through each product, looked for a matching sku entry in the _entity_varchar table, and if one did not ecist it inserted a row. Then I ran the indexing process fully and the problem seems to be resolved. Searching for those skus in the frontend and backend is now working as expected, and the flat tables have sku values.

Something went wrong, but hopefully its ok now.

  • Did you try to rebuild the Catalog Product Flat index? And is the SKU displayed if you edit the product? – Vladimir Kerkhoff Aug 15 '13 at 12:55
  • 2
    What did you originally use to create the products? Also, are you meaning that there are ZERO values in the _entity_varchar table or no records in there for the SKU attribute? There aren't supposed to be values in there for the SKU since it's a static attribute, so I'm just trying to clarify here. – davidalger Aug 15 '13 at 13:41
  • Hi David, It not just been myself working on the site, so there has been some importing and exporting with 3rd party tools. The _entity_varchar table has lots of data, but it had no values for the 'sku' attribute for the products that were causing problems. – jardinec Aug 15 '13 at 19:31
  • edited the original post to say i did a workaround that seems to have resolved it, no idea why though – jardinec Aug 15 '13 at 19:40
  • Please add your solution as an answer and take the points :-) – Fabian Blechschmidt Aug 16 '13 at 6:15
1

Here is the php script I ran, not sure it will ever help anyone else given how obscure the problem was, but you never know.

//connect to mysql
$link = mysql_connect('###', '###', '###');
if (!$link) {
    die('Not connected : ' . mysql_error());
}
//select DB
$db_selected = mysql_select_db('###', $link);
if (!$db_selected) {
    die ('Can\'t use DB : ' . mysql_error());
}


//fetch all products
$query = "SELECT * FROM catalog_product_entity";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {

//see if there is a valid varchar table entry
$query2 = "SELECT *
FROM catalog_product_entity_varchar
WHERE entity_type_id =4
AND attribute_id =68
AND store_id =0
AND entity_id = $row[entity_id]
AND value = $row[sku]";

$result2 = mysql_query($query2);

if(mysql_num_rows($result2) == 1){
echo 'Product ID: '.$row['entity_id'].' | SKU: '.$row['sku'].' VARCHAR FOUND<br/>';
}
else{
echo 'Product ID: '.$row['entity_id'].' | SKU: '.$row['sku'].' NOT FOUND<br/>';

    //INSERT ONE MANUALLY
    $query3 = "INSERT INTO catalog_product_entity_varchar (value_id, entity_type_id, attribute_id, store_id, entity_id, value) VALUES (NULL , '4', '68', '0', $row[entity_id], $row[sku])";
    //echo $query3.'<br/>';

    $result3 = mysql_query($query3);

}

}

//rebuild indexes once run

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.