Magento 2 : Remove Products / Category's / Customers /Orders /Product Reviews Using MySQL Script

Below Truncate worked for me in Magento ver. 2.1.7


Truncate Category Tables

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;

INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES ('1', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'),
('2', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0');

INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES
('1', '69', '0', '1', '1'),
('2', '46', '0', '2', '1'),
('3', '69', '0', '2', '1');

INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES
('1', '45', '0', '1', 'Root Catalog'),
('2', '45', '0', '2', 'Default Category');

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM url_rewrite WHERE entity_type = 'category';

*****************************************************************

Truncate Product Tables


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock_status_idx`;
TRUNCATE TABLE `cataloginventory_stock_status_tmp`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product_index_tmp`;
TRUNCATE TABLE `catalog_compare_item`;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_price_index`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_bundle_selection_price`;
TRUNCATE TABLE `catalog_product_bundle_stock_index`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value_to_entity`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value_video`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_index_eav`;
TRUNCATE TABLE `catalog_product_index_eav_decimal`;
TRUNCATE TABLE `catalog_product_index_eav_decimal_idx`;
TRUNCATE TABLE `catalog_product_index_eav_decimal_tmp`;
TRUNCATE TABLE `catalog_product_index_eav_idx`;
TRUNCATE TABLE `catalog_product_index_eav_tmp`;
TRUNCATE TABLE `catalog_product_index_price`;
TRUNCATE TABLE `catalog_product_index_price_bundle_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_downlod_idx`;
TRUNCATE TABLE `catalog_product_index_price_downlod_tmp`;
TRUNCATE TABLE `catalog_product_index_price_final_idx`;
TRUNCATE TABLE `catalog_product_index_price_final_tmp`;
TRUNCATE TABLE `catalog_product_index_price_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_tmp`;
TRUNCATE TABLE `catalog_product_index_tier_price`;
TRUNCATE TABLE `catalog_product_index_website`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_relation`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_url_rewrite_product_category`;
TRUNCATE TABLE `downloadable_link`;
TRUNCATE TABLE `downloadable_link_price`;
TRUNCATE TABLE `downloadable_link_purchased`;
TRUNCATE TABLE `downloadable_link_purchased_item`;
TRUNCATE TABLE `downloadable_link_title`;
TRUNCATE TABLE `downloadable_sample`;
TRUNCATE TABLE `downloadable_sample_title`;
TRUNCATE TABLE `product_alert_price`;
TRUNCATE TABLE `product_alert_stock`;
TRUNCATE TABLE `report_compared_product_index`;
TRUNCATE TABLE `report_viewed_product_aggregated_daily`;
TRUNCATE TABLE `report_viewed_product_aggregated_monthly`;
TRUNCATE TABLE `report_viewed_product_aggregated_yearly`;
TRUNCATE TABLE `report_viewed_product_index`;

SET FOREIGN_KEY_CHECKS = 1;
DELETE FROM url_rewrite WHERE entity_type = 'product';

************************************************************************

Truncate Review Tables


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_detail`;
TRUNCATE TABLE `review_entity_summary`;
TRUNCATE TABLE `review_store`;

SET FOREIGN_KEY_CHECKS = 1;

************************************************************************

Truncate Customer Tables

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `customer_address_entity`;
TRUNCATE TABLE `customer_address_entity_datetime`;
TRUNCATE TABLE `customer_address_entity_decimal`;
TRUNCATE TABLE `customer_address_entity_int`;
TRUNCATE TABLE `customer_address_entity_text`;
TRUNCATE TABLE `customer_address_entity_varchar`;
TRUNCATE TABLE `customer_entity`;
TRUNCATE TABLE `customer_entity_datetime`;
TRUNCATE TABLE `customer_entity_decimal`;
TRUNCATE TABLE `customer_entity_int`;
TRUNCATE TABLE `customer_entity_text`;
TRUNCATE TABLE `customer_entity_varchar`;
TRUNCATE TABLE `customer_grid_flat`;
TRUNCATE TABLE `customer_log`;
TRUNCATE TABLE `customer_log`;
TRUNCATE TABLE `customer_visitor`;
TRUNCATE TABLE `persistent_session`;
TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;
SET FOREIGN_KEY_CHECKS = 1;


************************************************************************

Truncate Order Tables

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `gift_message`;
TRUNCATE TABLE `quote`;
TRUNCATE TABLE `quote_address`;
TRUNCATE TABLE `quote_address_item`;
TRUNCATE TABLE `quote_id_mask`;
TRUNCATE TABLE `quote_item`;
TRUNCATE TABLE `quote_item_option`;
TRUNCATE TABLE `quote_payment`;
TRUNCATE TABLE `quote_shipping_rate`;
TRUNCATE TABLE `reporting_orders`;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
TRUNCATE TABLE `sales_creditmemo`;
TRUNCATE TABLE `sales_creditmemo_comment`;
TRUNCATE TABLE `sales_creditmemo_grid`;
TRUNCATE TABLE `sales_creditmemo_item`;
TRUNCATE TABLE `sales_invoice`;
TRUNCATE TABLE `sales_invoiced_aggregated`;
TRUNCATE TABLE `sales_invoiced_aggregated_order`;
TRUNCATE TABLE `sales_invoice_comment`;
TRUNCATE TABLE `sales_invoice_grid`;
TRUNCATE TABLE `sales_invoice_item`;
TRUNCATE TABLE `sales_order`;
TRUNCATE TABLE `sales_order_address`;
TRUNCATE TABLE `sales_order_aggregated_created`;
TRUNCATE TABLE `sales_order_aggregated_updated`;
TRUNCATE TABLE `sales_order_grid`;
TRUNCATE TABLE `sales_order_item`;
TRUNCATE TABLE `sales_order_payment`;
TRUNCATE TABLE `sales_order_status_history`;
TRUNCATE TABLE `sales_order_tax`;
TRUNCATE TABLE `sales_order_tax_item`;
TRUNCATE TABLE `sales_payment_transaction`;
TRUNCATE TABLE `sales_refunded_aggregated`;
TRUNCATE TABLE `sales_refunded_aggregated_order`;
TRUNCATE TABLE `sales_shipment`;
TRUNCATE TABLE `sales_shipment_comment`;
TRUNCATE TABLE `sales_shipment_grid`;
TRUNCATE TABLE `sales_shipment_item`;
TRUNCATE TABLE `sales_shipment_track`;
TRUNCATE TABLE `sales_shipping_aggregated`;
TRUNCATE TABLE `sales_shipping_aggregated_order`;
TRUNCATE TABLE `tax_order_aggregated_created`;
TRUNCATE TABLE `tax_order_aggregated_updated`;

SET FOREIGN_KEY_CHECKS = 1;

Magento 2 : Product Add/Update “Asymmetric transaction rollback.” error [SOLVED]

When creating a new product or update a product then trying to save get the following error message:

'Asymmetric transaction rollback error'

The issue is resolved and the following are the steps.

1. Reindex by using the following command
   

   php bin/magento indexer:reindex

2. run below commands
   

   rm -rf var/cache/*
   rm -rf var/page_cache/*
   rm -rf var/generation/*

3. Give permissions to var/generation folder.
4. Just you can change reindex mode for product related indexer ->Update by schedule. 
1. Product Flat Data 
2. Category Flat Data
3. Category Products
4. Product Categories
5. Product Price
6. Product EAV 

Magento 2 : Create Review & Rating programmatically from other db or csv?

This code is for import reviews from the other database table into magento2 product review.
Step1- Please empty review and rating tables.

Magento 2 Database table list.

1. review
2. review_detail
3. review_entity_summary
4. review_store
5. rating_option_vote
6. rating_option_vote_aggregated

Step2- Create 2 tables in your magento database test_customer and test_reviews.
CREATE TABLE `test_customer` (
  `customerid` int(11) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `emailaddress` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE `test_reviews` (
  `id` int(11) NOT NULL,
  `lastmodified` varchar(255) NOT NULL,
  `productcode` varchar(255) NOT NULL,
  `reviewtitle` varchar(255) NOT NULL,
  `rate` varchar(255) NOT NULL,
  `customerid` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `location` varchar(255) NOT NULL,
  `active` varchar(255) NOT NULL,
  `reviewdescription` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



Here you can insert the data from your csv's or other database

Step3- create a php page and paste the below code.

<?php
error_reporting(E_ALL);
set_time_limit(0);
ini_set('display_errors', '1');
ini_set('max_execution_time', 0);
ini_set('memory_limit', '3072M');
ini_set('auto_detect_line_endings', TRUE);

use Magento\Framework\App\Bootstrap;
require __DIR__ . '/app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$obj = $bootstrap->getObjectManager();
$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();


//Get Customer email for oldsite
function get_customer_email($id)
{
    $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
    $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
    $connection = $resource->getConnection();
    $sql = "Select * FROM test_customer_au where customerid=".$id;
    $result = $connection->fetchAll($sql);
   if(count($result)>0){
    $email= $result[0]['emailaddress'];
   }
   else{$email= 'abc@testwebsite.com';}
 
    return $email;
}

// start code where old site review
/*
 * Select * FROM test_reviews where 1
 * I got all the reivew by this query.
 *
 *
 * */

$sql = "Select * FROM test_reviews where 1 ";
$result = $connection->fetchAll($sql);


foreach($result as $kk){
 
    $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
    $customerId = '1234'; // default userid in my case when user is not avalable other wise blank
    $nickname= $kk['name'].', '.$kk['location'];
    $timestamp = '';
    $new_date_format = '';
    $timestamp = strtotime($kk['lastmodified']);
    $new_date_format = date('Y-m-d H:i:s', $timestamp);
 
   // Load product data
    $sku =$kk['productcode'];  
    $productObject = $objectManager->get('Magento\Catalog\Model\Product');
    $product = $productObject->loadByAttribute('sku', $sku);
    $productId = $product->getId();
 
    // Load customer data
    /*
*  I load email id from here by the mapping of ID from the magento2 and custom table using function
* get_customer_email()
*
* */
    if($kk['customerid']!=""){
        $email = get_customer_email($kk['customerid']);
        $customerObj = $objectManager->create('Magento\Customer\Model\ResourceModel\Customer\Collection');
        $collection = $customerObj->addAttributeToSelect('*')
        ->addAttributeToFilter('email',$email)
        ->load();
 
       $cdata=$collection->getData();
       if(isset($cdata[0]['entity_id']) && $cdata[0]['entity_id']!=''){$customerId = $cdata[0]['entity_id'];}else{$customerId = '4611';}
 
    }
 
  // Save Reviews
$bootstrap = Bootstrap::create(BP, $_SERVER);
$obj = $bootstrap->getObjectManager();
$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');

     if($kk['active']=="Y"){
$_review = $objectManager->get("Magento\Review\Model\Review")
        ->setEntityPkValue($productId)//product Id
        ->setStatusId(\Magento\Review\Model\Review::STATUS_APPROVED)// approved /PENDING
        ->setTitle($kk['reviewtitle'])
        ->setDetail($kk['reviewdescription'])
        ->setEntityId(1)
        ->setStoreId(2)
        ->setStores(2)
        ->setCustomerId($customerId)//get dynamically here
        ->setNickname($nickname)
        ->save();
    }
    if($kk['active']=="N"){
$_review = $objectManager->get("Magento\Review\Model\Review")
        ->setEntityPkValue($productId)//product Id
        ->setStatusId(\Magento\Review\Model\Review::STATUS_PENDING)// approved /PENDING
        ->setTitle($kk['reviewtitle'])
        ->setDetail($kk['reviewdescription'])
        ->setEntityId(1)
        ->setStoreId(2)
        ->setStores(2)
        ->setCustomerId($customerId)//get dynamically here
        ->setNickname($nickname)
        ->save();
    }
 
   // echo "Review Has been saved ";
 
    /*
    $_ratingOptions = array(
        1 => array(1 => 1,  2 => 2,  3 => 3,  4 => 4,  5 => 5), //quality
        2 => array(1 => 6,  2 => 7,  3 => 8,  4 => 9,  5 => 10),//value
        3 => array(1 => 11, 2 => 12, 3 => 13, 4 => 14, 5 => 15),//price
        4 => array(1 => 16, 2 => 17, 3 => 18, 4 => 19, 5 => 20) //rating
    );
    */
 
    //just Assume user selected rating options
    $ratingOptions ='';
    if($kk['rate'] == "1")
    {
        $ratingOptions = array(
           // '1' => '1',
           // '2' => '7',
           // '3' => '13',
            '4' => '16',
        );
    }
    if($kk['rate'] == "2")
    {
        $ratingOptions = array(
           // '1' => '1',
           // '2' => '7',
           // '3' => '13',
            '4' => '17',
        );
    }
    if($kk['rate'] == "3")
    {
        $ratingOptions = array(
           // '1' => '1',
           // '2' => '7',
           // '3' => '13',
            '4' => '18',
        );
    }
    if($kk['rate'] == "4")
    {
        $ratingOptions = array(
           // '1' => '1',
           // '2' => '7',
           // '3' => '13',
            '4' => '19',
        );
    }
    if($kk['rate'] == "5")
    {
        $ratingOptions = array(
           // '1' => '1',
           // '2' => '7',
           // '3' => '13',
            '4' => '20',
        );
    }
 
    foreach ($ratingOptions as $ratingId => $optionIds) {
        $objectManager->get("Magento\Review\Model\Rating")
            ->setRatingId($ratingId)
            ->setReviewId($_review->getId())
            ->addOptionVote($optionIds, $productId);
    }
 
    // generates summary
            $_review->aggregate();
            // if you want to save review in previous dates
            $_review->setCreatedAt($new_date_format);
            $_review->save();
         

echo "<br/>Rating has been saved success !!!!!!!!!= ".$_review->getId();

}


?>

Magento 2 : issues with M2E Pro 'Duplicated line on invoice/order for configurable product'[solved]

Duplicated line on invoice/order for configurable product

When configurable product is ordered, Magento show 2 lines in order (and in invoice)
configurable product generates 2 lines : one with the actual price and the other one with $0 price.

Go this location:
app/code/Ess/M2ePro/Plugin/Order/Magento/Quote/Model/Quote/Item/

Replace 2 function :

public function aroundConvert($interceptor, \Closure $callback, $item)
    {
        return $this->execute('convert', $interceptor, $callback, [$item]);
    }

    // ---------------------------------------

    protected function processConvert($interceptor, \Closure $callback, $arguments)
    {
        $orderItem = $callback($arguments[0]);

        $this->eventManager->dispatch(
            'ess_sales_convert_quote_item_to_order_item',
            [
                'order_item' => $orderItem,
                'item' => $arguments[0],
            ]
        );

        return $orderItem;
    }
 

    To:
 
 
   public function aroundConvert($interceptor, \Closure $callback, ...$arguments)
    {
        return $this->execute('convert', $interceptor, $callback, $arguments);
    }

    // ---------------------------------------

    protected function processConvert($interceptor, \Closure $callback, $arguments)
    {
        $orderItem = $callback(...$arguments);

        $this->eventManager->dispatch(
            'ess_sales_convert_quote_item_to_order_item',
            [
                'order_item' => $orderItem,
                'item' => $arguments[0],
            ]
        );

        return $orderItem;
    }
    

Kuldeep Singh Dagar: [SOLVED] Magento ver. 1.9.*.* Cant view product im...

Kuldeep Singh Dagar: [SOLVED] Magento ver. 1.9.*.* Cant view product im...: Problem Reasons: Wrong permissions set to uploaded files Solution: Open /lib/Varien/File/Uploader.php and make sure following permission...

[SOLVED] Magento ver. 1.9.*.* Cant view product images in admin after upload

Problem Reasons:

• Wrong permissions set to uploaded files Solution: Open /lib/Varien/File/Uploader.php and make sure following permissions are set at chmod($destinationFile, 0640);. This code will be at around line 219.

• Replace: line no 219 -  chmod($destinationFile, 0640); to chmod($destinationFile, 0644); or as per requirement.

• Then rename .htaccess file to .htaccess.old in /media folder.

I tried the solution and it work fine. 

Please feel free to contact me any Magento issue at any time. 

Regards

Kuldeep

kuldeep4110@gmail.com

Magento [SOLVED]Can not login to admin panel and website footer error 'Access to undeclared static property'

Fatal error: Access to undeclared static property: Mage::$_isDeveloperMode in app/Mage.php on line 881

Go to Mage.php line number 881 find the function.

public static function getIsDeveloperMode()
{
    return self::$_isDeveloperMode;
}

This method simply returns a static property $_isDeveloperMode of the class app\Mage.php. You can find that the definition of that static property
 in that file itself in line 117 (magento 1.9.1) like this.

Add below code of the class app\Mage.php "static private $_isDeveloperMode = false;"

Find: static private $_isDownloader = false;

    /**
     * Is developer mode flag
     *
     * @var bool
     */
ADD: static private $_isDeveloperMode = false;

I tried the solution and it work fine.

Please feel free to contact me any Magento issue at any time.

Regards
Kuldeep
kuldeep4110@gmail.com