Magento 2 : How to fix tablespace for table exists | base table or view already exists

Fixing General error: 1813 Tablespace for table xx Please DISCARD the tablespace before IMPORT and SQLSTATE[42S01]: Base table or view already exists

Recently I encountered this error while upgrading an extension on Magento 2.2.2
The error was

SQLSTATE[HY000]: General error: 1813 Tablespace for table '`jack_db1`.`jill_table`' exists. Please DISCARD the tablespace before IMPORT., query was: CREATE TABLE IF NOT EXISTS `jill_table`

When I tried to fix the above error, I received:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table '`jack_db1`.`jill_table`' already exists, query was: CREATE TABLE IF NOT EXISTS `jill_table`

So here is how to fix it:

Important Note: Replace jack_db1 with your database Name and jill_table with your table Name

PART 1 – Fixing MySQL backend

First log in to server and check the mysql data folder (mine was /var/lib/mysql/jack_db1)
say your database name is jack_db1 and your table is jill_table
so issue

ls /var/lib/mysql/jack_db1

You will find .frm and .idb files
Note that these files are present as a couple (each frm file has a corresponding idb file)
But the table that is causing you issues will have one of them missing.
.frm was missing in my case, so copy any other frm file and name it as jill_table.frm (i copied wishlist.frm using following command )
cd /var/lib/mysql/jack_db1;cp wishlist.frm jill_table.frm
use the following command to fix ownership

chown -R mysql:mysql /var/lib/mysql/jack_db1/*

Part 2: Fixing Magento Backend

Login to phpMyAdmin if available or use MySQL CLI to perform the following actions.
In the table setup_module of your magento installation
Delete the row which is related to your corrupted extension

DELETE FROM `setup_module` WHERE module="Custom_Module"

you can also do the same from phpMyAdmin

Now delete the table which was causing the issue, (you would not be able to view the table in phpmMyAdmin)
Just run the following query in SQL

DROP TABLE IF EXISTS `jill_table`;

Part 3: Upgrade the Magento using php cli

run the following commands:

rm -rf var/cache/*
rm -rf generated/*
php bin/magento module:enable Custom_Module --clear-static-content
php bin/magento setup:upgrade
#  Hopefully this time you wont see any errors, proceed as usual if upgrade command worked.
#  run
php bin/magento setup:di:compile
#  and 
php bin/magento setup:static-content:deploy -f

Good Luck with Magento 🙂

Remove sku column from Magento 2 invoice and pdf

Edit the file
/vendor/magento/module-sales/Model/Order/Pdf/Items/Invoice/DefaultInvoice.php

Inside function draw(), remove/comment following code:

   // draw SKU
        $lines[0][] = [
            'text' => $this->string->split($this->getSku($item), 17),
            'feed' => 290,
            'align' => 'right',
        ];

To remove the SKU header in table header, edit the file
/vendor/magento/module-sales/Model/Order/Pdf/Invoice.php

//$lines[0][] = ['text' => __('SKU'), 'feed' => 290, 'align' => 'right'];

and copy the file vendor/magento/module-sales/view/frontend/templates/email/items/order/default.phtml
to app/design/your/theme/Magento_Sales/templates/email/items/order/default.phtml
and remove the code

<p class="sku"><?= /* @escapeNotVerified */  __('SKU') ?>: <?= $block->escapeHtml($block->getSku($_item)) ?></p>

Magento 2 temporary fix for Alphabetical sorting of Configurable product options

Just re-write the file vendor/magento/module-configurable-product/Model/ConfigurableAttributeData.php

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace Magento\ConfigurableProduct\Model;

use Magento\Catalog\Model\Product;
use Magento\ConfigurableProduct\Model\Product\Type\Configurable\Attribute;

/**
 * Class ConfigurableAttributeData
 * @api
 * @since 100.0.2
 */
class ConfigurableAttributeData
{
    /**
     * Get product attributes
     *
     * @param Product $product
     * @param array $options
     * @return array
     */
    public function getAttributesData(Product $product, array $options = [])
    {
        $defaultValues = [];
        $attributes = [];
        foreach ($product->getTypeInstance()->getConfigurableAttributes($product) as $attribute) {
            $attributeOptionsData = $this->getAttributeOptionsData($attribute, $options);
            if ($attributeOptionsData) {
                $productAttribute = $attribute->getProductAttribute();
                $attributeId = $productAttribute->getId();
                $attributes[$attributeId] = [
                    'id' => $attributeId,
                    'code' => $productAttribute->getAttributeCode(),
                    'label' => $productAttribute->getStoreLabel($product->getStoreId()),
                    'options' => $attributeOptionsData,
                    'position' => $attribute->getPosition(),
                ];
                $defaultValues[$attributeId] = $this->getAttributeConfigValue($attributeId, $product);
            }
        }
        return [
            'attributes' => $attributes,
            'defaultValues' => $defaultValues,
        ];
    }
    public function labelsort($a,$b){ return strcmp($a['label'], $b['label']);}

    /**
     * @param Attribute $attribute
     * @param array $config
     * @return array
     */
    protected function getAttributeOptionsData($attribute, $config)
    {
        $attributeOptionsData = [];
        foreach ($attribute->getOptions() as $attributeOption) {
            $optionId = $attributeOption['value_index'];
            $attributeOptionsData[] = [
                'id' => $optionId,
                'label' => $attributeOption['label'],
                'products' => isset($config[$attribute->getAttributeId()][$optionId])
                    ? $config[$attribute->getAttributeId()][$optionId]
                    : [],
            ];
        }
        if(count($attributeOptionsData) > 1){usort($attributeOptionsData,array($this,'labelsort'));}
        
        return $attributeOptionsData;
    }

    /**
     * @param int $attributeId
     * @param Product $product
     * @return mixed|null
     */
    protected function getAttributeConfigValue($attributeId, $product)
    {
        return $product->hasPreconfiguredValues()
            ? $product->getPreconfiguredValues()->getData('super_attribute/' . $attributeId)
            : null;
    }
}

How to add custom attributes in Magento 2 product CSV export

Core Method is to edit the file
/vendor/magento/module-catalog-import-export/Model/Export/Product.php

and Edit the variable $_exportMainAttrCodes

Add your attributes in the array like “custom1″,”custom2”, etc and save the file.

Next time you export the csv, you will see new columns in the file.

Insert Indian States in Magento Database

Connect to your server’s database using mysql cli or phpmyadmin and use the following query

INSERT INTO `directory_country_region` VALUES 
(NULL,"IN","AP","Andhra Pradesh"),
(NULL,"IN","AR","Arunachal Pradesh"),
(NULL,"IN","AS","Assam"),
(NULL,"IN","BR","Bihar"),
(NULL,"IN","CG","Chhattisgarh"),
(NULL,"IN","GA","Goa"),
(NULL,"IN","GJ","Gujarat"),
(NULL,"IN","HR","Haryana"),
(NULL,"IN","HP","Himachal Pradesh"),
(NULL,"IN","JK","Jammu and Kashmir"),
(NULL,"IN","JH","Jharkhand"),
(NULL,"IN","KA","Karnataka"),
(NULL,"IN","KL","Kerala"),
(NULL,"IN","MP","Madhya Pradesh"),
(NULL,"IN","MH","Maharashtra"),
(NULL,"IN","MN","Manipur"),
(NULL,"IN","ML","Meghalaya"),
(NULL,"IN","MZ","Mizoram"),
(NULL,"IN","NL","Nagaland"),
(NULL,"IN","OD","Odisha"),
(NULL,"IN","PB","Punjab"),
(NULL,"IN","RJ","Rajasthan"),
(NULL,"IN","SK","Sikkim"),
(NULL,"IN","TN","Tamil Nadu"),
(NULL,"IN","TL","Telangana"),
(NULL,"IN","TR","Tripura"),
(NULL,"IN","UK","Uttarakhand"),
(NULL,"IN","UP","Uttar Pradesh"),
(NULL,"IN","WB","West Bengal"),
(NULL,"IN","AN","Andaman and Nicobar Islands"),
(NULL,"IN","CH","Chandigarh"),
(NULL,"IN","DH","Dadra and Nagar Haveli"),
(NULL,"IN","DD","Daman and Diu"),
(NULL,"IN","DL","Delhi"),
(NULL,"IN","LD","Lakshadweep"),
(NULL,"IN","PY","Puducherry");

How to create Magento 2 category programmatically?

Use this code:

<?php
use \Magento\Framework\App\Bootstrap;
echo 'code by harshvardhanmalpani';
include('./app/bootstrap.php');
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();

function createCategory($a='',$b=2,$c=true,$d='',$e='',$f='',$g='') {
        global $objectManager;
        $category = $objectManager->get('\Magento\Catalog\Model\CategoryFactory')->create();
        $category->setName($a);
        $category->setParentId($b); // 1: root category.
        $category->setIsActive($c);
        $category->setCustomAttributes([
'description' => $d,
'meta_title' => $e,
'meta_keywords' => $f,
 'meta_description' => $g,
     ]);
        $objectManager->get('\Magento\Catalog\Api\CategoryRepositoryInterface')->save($category);
}
createCategory("Abc");
createCategory("Xyz",4,false,"description","m title");
?>

How to secure a folder by whitelisting one IP using HTACCESS and denying all others

The process involves matching all requests for an IP, if the IP does not match redirect all secured directory requests.

RewriteCond %{REMOTE_ADDR} !^120\.120\.120\.120
RewriteRule ^admin/.* - [L,R=403] 
RewriteCond %{REMOTE_ADDR} ^120\.120\.120\.120
RewriteRule ^admin$ - [L,R=403]

If you want to control this IP using a script, you can add 2 lines

###CUSTOM RULES###
# your rules will appear here using php script
###CUSTOM RULES###

Now add a script, say happy.php
and use the following code:

$htaccess = file_get_contents('.htaccess');
$ip= str_replace(".","\.",$_SERVER['REMOTE_ADDR']);
$rules="RewriteCond %{REMOTE_ADDR} !^".$ip."
RewriteRule ^admin/.* - [L,R=403]
RewriteCond %{REMOTE_ADDR} !^".$ip."
RewriteRule ^admin$ - [L,R=403]";
$problem="###CUSTOM RULES###\r\n".$rules.
"\r\n###CUSTOM RULES###";
#please copy the next line carefully, no extra spaces or new lines
$solution=preg_replace('/###CUSTOM RULES###.*?###CUSTOM RULES###/sm',$problem,$htaccess);
file_put_contents('.htaccess', $solution);

you can also specify your error page for specific http code: 403
ErrorDocument 403 /er.htm

Start a magento instance programmatically

This Code:

$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
//your mage code here

Commonly used regular expressions (regex) for developers in PHP

Commonly used regular expressions (regex) for developers in PHP

Regex are used or can be used almost everywhere strings are involved. If you are dealing with text strings, you would often find patterns in them and you might be required to filter few of them based on some similarities and properties. For example: a currency or money is often written in this form –
[Currency symbol] [Digits] ([.] [Digits])maybe

Now, in order to filter any such things programmatically using a script, few of really helpful regular expressions are mentioned below:

1. Match 4 characters

.{4} 
or ....

2. Match first 4 characters

^.{4}

3. Match last 3 characters

.{3}$ 
or
 ...$

4. Match a digit

[0-9]

5. Match an alphabet

[a-zA-Z]

6. Match a digit occurring 0 or more times

[0-9]*

7. Match a lowercase alphabet occurring 1 or more times

[a-z]+

8. Match an uppercase alphabet either NOT occurring or occurring once ( 0 or 1 time)

[A-Z]?

9. Match a character occurring exactly once
write the character itself in regex; similar to #4 and #5

10. Match a character literally (used to match meta-characters)
use \ before the character to be matched
If you want to find the occurrence of question mark(s) in a string
use \? (remember ? is used to find occurrence of at most 1 time but \? with find occurrence of ? itself exactly once)

Pro things

Match an e-Mail address
a regular expression to match an email address with 100% efficiency is really tough to be learnt here because according to RFC 0822
https://www.ietf.org/rfc/rfc0822.txt
, an email address can have letters like { } # $ @ \ / – _ . alphabets numbers and more.

So, a quick regex for matching emails can be

^[a-zA-Z0-9\._%\-^#\{\}]+@([a-zA-Z0-9.-]+\.)+[a-zA-Z]{2,9}$

this will match {^}#@technacy.net which is an actual email address and is totally valid.

However this won’t work all the times, a more detailed regex would be something like this: (email addresses can end with IP addresses too)

/^(?!(?:(?:\x22?\x5C[\x00-\x7E]\x22?)|(?:\x22?[^\x5C\x22]\x22?)){255,})(?!(?:(?:\x22?\x5C[\x00-\x7E]\x22?)|(?:\x22?[^\x5C\x22]\x22?)){65,}@)(?:(?:[\x21\x23-\x27\x2A\x2B\x2D\x2F-\x39\x3D\x3F\x5E-\x7E]+)|(?:\x22(?:[\x01-\x08\x0B\x0C\x0E-\x1F\x21\x23-\x5B\x5D-\x7F]|(?:\x5C[\x00-\x7F]))*\x22))(?:\.(?:(?:[\x21\x23-\x27\x2A\x2B\x2D\x2F-\x39\x3D\x3F\x5E-\x7E]+)|(?:\x22(?:[\x01-\x08\x0B\x0C\x0E-\x1F\x21\x23-\x5B\x5D-\x7F]|(?:\x5C[\x00-\x7F]))*\x22)))*@(?:(?:(?!.*[^.]{64,})(?:(?:(?:xn--)?[a-z0-9]+(?:-[a-z0-9]+)*\.){1,126}){1,}(?:(?:[a-z][a-z0-9]*)|(?:(?:xn--)[a-z0-9]+))(?:-[a-z0-9]+)*)|(?:\[(?:(?:IPv6:(?:(?:[a-f0-9]{1,4}(?::[a-f0-9]{1,4}){7})|(?:(?!(?:.*[a-f0-9][:\]]){7,})(?:[a-f0-9]{1,4}(?::[a-f0-9]{1,4}){0,5})?::(?:[a-f0-9]{1,4}(?::[a-f0-9]{1,4}){0,5})?)))|(?:(?:IPv6:(?:(?:[a-f0-9]{1,4}(?::[a-f0-9]{1,4}){5}:)|(?:(?!(?:.*[a-f0-9]:){5,})(?:[a-f0-9]{1,4}(?::[a-f0-9]{1,4}){0,3})?::(?:[a-f0-9]{1,4}(?::[a-f0-9]{1,4}){0,3}:)?)))?(?:(?:25[0-5])|(?:2[0-4][0-9])|(?:1[0-9]{2})|(?:[1-9]?[0-9]))(?:\.(?:(?:25[0-5])|(?:2[0-4][0-9])|(?:1[0-9]{2})|(?:[1-9]?[0-9]))){3}))\]))$/iD

So, what should be the ideal solution because you won’t like to check this^ regex all the time
Use PHP’s inbuilt filter – FILTER_VALIDATE_EMAIL
http://php.net/manual/en/filter.filters.validate.php

if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {   $emailErr = "Invalid email format";  }

For security, w3schools mentioned passing and sanitizing all the data first with a function:

function test_input($data) {   $data = trim($data);   $data = stripslashes($data);   $data = htmlspecialchars($data);   return $data; }

Matching a URL
A URL can have letters, digits, any character as a parameter or in host details. A simple version of regex to match http urls is

^(http|https):\/\/((([a-z0-9.-]+\.)+[a-z]{2,4})|([0-9\.]{1,4}){4})(\/([a-zA-Z?-?0-9-_\?\:%\.\?\!\=\+\&\/\#\~\;\,\@]+)?)?$

A detailed and more efficient regex:

+// _^(?:(?:https?|ftp)://)(?:\S+(?::\S*)?@)?(?:(?!(?:10|127)(?:\.\d{1,3}){3})(?!(?:169\.254|192\.168)(?:\.\d{1,3}){2})(?!172\.(?:1[6-9]|2\d|3[0-1])(?:\.\d{1,3}){2})(?:[1-9]\d?|1\d\d|2[01]\d|22[0-3])(?:\.(?:1?\d{1,2}|2[0-4]\d|25[0-5])){2}(?:\.(?:[1-9]\d?|1\d\d|2[0-4]\d|25[0-4]))|(?:(?:[a-z\x{00a1}-\x{ffff}0-9]-*)*[a-z\x{00a1}-\x{ffff}0-9]+)(?:\.(?:[a-z\x{00a1}-\x{ffff}0-9]-*)*[a-z\x{00a1}-\x{ffff}0-9]+)*(?:\.(?:[a-z\x{00a1}-\x{ffff}]{2,}))\.?)(?::\d{2,5})?(?:[/?#]\S*)?$_iuS

Diego Perini
https://gist.github.com/dperini/729294

However PHP’s inbuilt filter can also be used as the most ideal
use FILTER_VALIDATE_URL as we used FILTER_VALIDATE_EMAIL earlier

Match a Phone number
Regex for a phone number is easy if you want to match only MOBILE NUMBERS which must be of 10 digits and can include a country code like +91, 091, (91), (+91) or 0

^(091|\+91|91|\(091\)|\(\+91\)|\(91\)|0)? ?[7-9][0-9]{9}$

https://regex101.com/r/kA6oD0/6

Match a Name
A name is supposed to have alphabets only but the length of first names and last names is not fixed or general at all. These lengths may vary thus the regex sounds funny at times.
^[a-zA-Z’ -]+$

expression, match, php, regex

Clean html code with REGEX

$stripped = trim(preg_replace('/\s+/', ' ', $sentence));