Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Magento 2: Reindex Error - 1062 Duplicate entry `catalog_product_index_eav_temp` (?, ?, ?, ?, ?)...
<?php
$conn = mysqli_connect("HOST", "USER", "PASS", "DATABASE");
if (!$conn) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
}
/* change character set to utf8 */
if (!$conn->set_charset("utf8")) {
exit();
}
$attributeId = 138; // Your Attribute Code!
$stores = array(0,1,2,3,4,6); // Your STORE IDs
foreach ($stores as $store) {
clearEmptyAttributes($conn, $attributeId, $store);
fixDuplicates($conn, $attributeId, $store);
}
mysqli_close($conn);
############################## FUNCTION ENVIROMENT ##############################
/**
* Fixing duplicates
*
* @param $conn
* @param $attributeId
* @param int $storeId
*/
function fixDuplicates($conn, $attributeId, $storeId=0){
echo "<h3>Fixing the duplicates</h3>";
$recordsToDelete = array();
$result = $conn->query("SELECT * FROM `catalog_product_entity_varchar` where attribute_id = $attributeId and store_id = $storeId order by entity_id");
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$hash = $row['entity_id'] . "-" . $row['attribute_id'] . "-" . $row['store_id'] ."-". $row['value'];
$recordsToDelete[$hash][] = $row['value_id'];
}
} else {
echo "0 results";
}
$recordsToDelete = saveLastElementAndPrepareSQLDataToDelete($recordsToDelete);
if($recordsToDelete){
deleteDuplicates($conn, $recordsToDelete);
return true;
}
return false;
}
/**
* @param $data
* @return mixed
*/
function saveLastElementAndPrepareSQLDataToDelete($data){
foreach ($data as $key => $item){
if(count($item) > 1){
array_pop($item);
$data[$key] = implode(",", $item);
}else{
unset($data[$key]);
}
}
return $data;
}
/**
* Delete duplicates records from Magento
*
* @param $conn
* @param $attributeId
*/
function deleteDuplicates($conn, $data){
echo "<h3>Delete Duplicate Entities</h3>";
foreach ($data as $key => $items) {
$sql = "DELETE FROM catalog_product_entity_varchar WHERE value_id IN ($items) ";
if ($conn->query($sql) === TRUE) {
echo "$items have been deleted! <br/>";
} else {
echo "Error deleting record: " . $conn->error;
}
}
}
/**
* Delete unuseful records from Magento
*
* @param $conn
* @param $attributeId
*/
function clearEmptyAttributes($conn, $attributeId, $storeId=0){
$sql = "DELETE FROM catalog_product_entity_varchar WHERE attribute_id = $attributeId AND store_id = $storeId AND value IS NULL ";
if ($conn->query($sql) === TRUE) {
echo "Empty attributes cleared!";
} else {
echo "Error deleting record: " . $conn->error;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.