How to do a regular expression replace string in MySQL?

How to do a regular expression replace string in MySQL?

Install a MySQL user defined function called lib_mysqludf_preg from:

http://www.mysqludf.org/

https://github.com/mysqludf/lib_mysqludf_preg

For RedHat / CentOS:

# yum -y install pcre-devel gcc make automake mysql-devel

For Debian / Ubuntu:

# apt-get update
# apt-get install build-essential libmysqld-dev libpcre3-dev

Install lib_mysqludf_preg:

# git clone https://github.com/mysqludf/lib_mysqludf_preg.git
# ./configure
# make
# make install
# make installdb
# make test

Example:

SELECT CONVERT( PREG_REPLACE( '/fox/i' , 'dog' , 'The brown fox' ) USING UTF8) as replaced;

To reset admin password:

Method 1 - reset admin password directly in database:

mysql> SET @salt = MD5(UNIX_TIMESTAMP());
mysql> UPDATE admin_user SET password = CONCAT(SHA2(CONCAT(@salt, 'MyNewPassword'), 256), ':', @salt, ':1') WHERE username = 'admin';

Method 2 - using PHP to generate the password. Then, reset it in database:

# php -r '$salt = md5(time()); echo hash("sha256", $salt . $argv[1]).":$salt:1\n";' MyNewPassword

66bdd4e5008cad465a6cd23eb6ac3aa6ef4c65d07a179157bab11935f9f4d62f:a4506164831ba6f12474a3ffe57602d0:1

mysql> UPDATE admin_user SET password = '<code above>' WHERE username='admin';

Method 3 - Generating the password. Then, reset it in database:

Add the following line at the last line and loot at the footer of any page.

# vim pub/index.php

echo \Magento\Framework\App\ObjectManager::getInstance()->get("\Magento\Framework\Encryption\Encryptor")->getHash("MyNewPassword");

Method 4 - Create a new admin user. Then, reset the previous admin password:

# php bin/magento admin:user:create --admin-user=admin2 --admin-password=MyNewPassword2 --admin-email=admin@example.com --admin-firstname=admin --admin-lastname=admin

Reference:

http://magento.stackexchange.com/questions/90922/how-to-reset-lost-admin-password-in-magento-2/161792#161792

To dump access database to MySQL sql file

To dump access database to MySQL sql file

1. Use Navicat

https://www.navicat.com/

2. Use Access To MySQL by bullzip

http://www.bullzip.com/products/a2m/info.php

View Google Chrome downloaded cached images and videos

View Google Chrome downloaded cached images and videos

Type: chrome://cache/

Print SQL query for the collection for debugging in Magento 2

Print SQL query for the collection for debugging in Magento 2

$productCollection->getSelect()->assemble();

Change Session timeout in Magento 2

Change Session timeout in Magento 2

System > Configuration > Advanced > Admin > Security > Session Lifetime (Seconds)

# vim php.ini

session.gc_maxlifetime = 36000

Get product tier pricing programmatically in Magento 2

Get product tier pricing programmatically in Magento 2

<?php
use \Magento\Framework\App\Bootstrap;

#require __DIR__ . '/../app/bootstrap.php';
require '/www/mag2.local/app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();

### Setting area code
### NOTE: for more info http://devdocs.magento.com/guides/v2.1/architecture/archi_perspectives/components/modules/mod_and_areas.html
$state = $objectManager->get('\Magento\Framework\App\State');
#$state->setAreaCode('base');

$productId = 1;
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$product_obj = $objectManager->create('\Magento\Catalog\Model\Product')->load($productId);

getDefaultGroup($product_obj);
getAnyGroup($product_obj);

function getDefaultGroup($product_obj) {
 $tier_price = $product_obj->getTierPrice();

 if(count($tier_price) > 0){
  echo "price_id\tprice_qty\tprice\twebsite_price\n";

  foreach($tier_price as $price){
   echo $price['price_id'];
   echo "\t";
   echo $price['price_qty'];
   echo "\t";
   echo $price['price'];
   echo "\t";
   echo $price['website_price'];
   echo "\n";
  }
 } else {
  echo 'There is no tiering price for the default group.' . PHP_EOL;
 }
}

function getAnyGroup($product_obj) {
 $tier_price = $product_obj->getTierPrices();

 if(count($tier_price) > 0){
  echo "price_qty\tprice\tCustomerGroupId\n";

  foreach($tier_price as $price){
   echo $price->getQty();
   echo "\t";
   echo $price->getValue();
   echo "\t";
   echo $price->getCustomerGroupId();
   echo "\t";
   echo "\n";
   print_r($price->getData());
   echo "\t";
   echo "\n";
  }
 }
}

#print_r($tier_price);
#print_r(get_class_methods($price));