Sku field null in category_product_flat tables

Question

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.

Answer 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