Sandbox script for quick testing in Magento2

September 14, 2016  |  No Comments  |  by Raj (MagePsycho)  |  Linux, Mac OS-X, Magento 2

What’s your approach for quick & dirty testing in Magento2?

  • Creating a test module with a controller and executing it from browser to see the output?
  • Creating a Console module and executing via CLI?

Obviously, above two approaches takes time.

Rather I would create a simple script (a single file) and put it somewhere in /[path-to-magento2]/pub/sandbox.php

 * @author Raj KB<>
 * @website
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
require __DIR__ . '/../app/bootstrap.php';
$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);
$obj = $bootstrap->getObjectManager();
$state = $obj->get('Magento\Framework\App\State');

// Your quick and dirty code goes here...
$quote = $obj->get('Magento\Checkout\Model\Session')->getQuote()->load(1);

Now you can easily test by pointing to URL

But wait, this won’t work in the case when you are using Nginx + PHP-FPM server.
The reason you can explore from the Nginx conf file: [path/to/magento2]/nginx.conf.sample

# PHP entry point for main application
location ~ (index|get|static|report|404|503)\.php$ {
    try_files $uri =404;
    fastcgi_pass   fastcgi_backend;
    fastcgi_buffers 1024 4k;

    fastcgi_param  PHP_FLAG  "session.auto_start=off \n suhosin.session.cryptua=off";
    fastcgi_param  PHP_VALUE "memory_limit=768M \n max_execution_time=600";
    fastcgi_read_timeout 600s;
    fastcgi_connect_timeout 600s;

    fastcgi_index  index.php;
    fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
    include        fastcgi_params;

As you can see only PHP files: index.php, get.php, static.php, report.php, 404.php & 503.php files are parsed by the PHP-FPM.

In order to include your sandbox.php in the whitelist, just edit the line of Nginx conf file:

location ~ (index|get|static|report|404|503)\.php$ {


location ~ (index|get|static|report|404|503|sandbox)\.php$ {

And don’t forget to reload or restart your Nginx server depending upon your Operating System.

sudo service nginx reload
sudo service nginx restart


sudo nginx -s reload
sudo nginx -s stop && sudo nginx

What’s your approach for quick & dirty testing in Magento2? Please do comment below.

MySQL Issue: Table storage engine for ‘catalog_product_relation’ doesn’t have this option

July 30, 2016  |  1 Comments  |  by Raj (MagePsycho)  |  Linux, Mac OS-X, Magento, Mysql

When you export the Magento database dump from MySQL 5.5.x and try to import in MySQL 5.7.x, You are likely to face an error:

ERROR 1031 (HY000) at line 3002: Table storage engine for ‘catalog_product_relation’ doesn’t have this option


This is probably due to the table option that you have in your CREATE TABLE DDL: ROW_FORMAT=FIXED

Let’s check if there is any such string in the SQL dump (Ex: magento-db-dump.sql).

cat magento-db-dump.sql | grep '=FIXED'

Which resulted as:

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Catalog Product To Website Linkage Table';

Refer – MySQL Row Format Option


Removing ROW_FORMAT=FIXED option from CREATE TABLE DDL will fix the issue.
So let’s try possible solutions.


sed -i 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

This didn’t work for me in MacOSx which resulted in following error:

sed: 1: “magento-db-dump.sql”: invalid command code m


sed -i '' 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

And even this resulted as:

sed: RE error: illegal byte sequence

But this one worked for me in MacOSx (Refer Mac OS X, Sed, and strange document encoding to know more about the issue)

LC_ALL=C sed -i '' 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

Re-check if the string has been removed or not:

cat magento-db-dump.sql | grep '=FIXED'

If the string is removed, now try to import as:

mysql -u <user> -p <db-name> < magento-db-dump.sql

Yay! Now it imported successfully.
Please do share and care if you liked this article.


Install Bash Completion for Mac OS-X

May 22, 2016  |  No Comments  |  by Raj (MagePsycho)  |  Linux, Mac OS-X

One of the nicest features of the modern shell is the built in “completion” support. This allows you to complete commands and their arguments easily without memorizing.

Unfortunately, Mac OS-X bash shell doesn’t have completion feature by default unlike Ubuntu(Debian based Linux OS).
But the good thing is you can easily install the auto completion by using Homebrew or Macports.

Homebrew being my favourite packaging tool for OS-X, I will be explaining the installation using this tool.

Bash Completion Installation

1. Install bash-completion package using brew

brew install bash-completion

Brew Bash Completion

2. Edit the ~/.bash_profile

vi ~/.bash_profile

And add the following code:

if [ -f $(brew --prefix)/etc/bash_completion ]; then
    . $(brew --prefix)/etc/bash_completion

3. Reload the bash shell

source ~/.bash_profile

Now try to type few letters of command and press [TAB], you will see the auto-completed command or auto-suggested commands(for more than one matches). For example:
nets[TAB] ->


net[TAB] ->

net-server               net-server5.18           net-snmp-config
net-server5.16           net-snmp-cert            net-snmp-create-v3-user


You can also install additional completions from Homebrew-Completions
All you have to do is:

brew tap homebrew/completions
brew install <formula>

If you know how to create bash completions, you can create your own file and drop in the folder:


After sourcing(loading) it, your custom completion is ready to take into account.

Please do share/comment on your favourite bash-completion.

Install & Configure Varnish Cache for Magento 2

May 19, 2016  |  13 Comments  |  by Raj (MagePsycho)  |  Linux, Magento 2

The performance of a website is always the key factor for the higher conversion rate. Fortunately, Magento 2 (both CE & EE) comes by default with the support for FPC (Full Page Caching), Varnish & Redis to make your store fly.
In this tutorial we will be discussing brief info about Varnish, it’s installation and configuration so bear with me.


Magento 2 supports Varnish versions 3.0.5 or later or any Varnish 4.x version.
And Magento team strongly recommends using Varnish in production as the built-in full-page caching (to either the file system or database) is much slower than Varnish, and Varnish is designed to accelerate HTTP traffic.

Varnish Cache is an open source web application accelerator (also referred to as an HTTP accelerator or caching HTTP reverse proxy). Varnish stores (or caches) files or fragments of files in memory; this enables Varnish to reduce the response time and network bandwidth consumption on future, equivalent requests. Unlike web servers like Apache and Nginx, Varnish was designed for use exclusively with the HTTP protocol.


We have carried out the Varnish installation in the following enviroment:

  • Ubuntu – 14.04 LTS
  • Nginx – 1.9.6
  • MySQL – 5.6.28
  • PHP – 5.6.20 (but PHP 7 is recommended)
  • Magento EE – 2.0.5


Run the following series of command as a root user (sudo su).

apt-get install apt-transport-https
curl | apt-key add -
echo "deb trusty varnish-4.1" \
     >> /etc/apt/sources.list.d/varnish-cache.list
apt-get update
apt-get install varnish

If everything goes right you should be able to see the Varnish version with:

varnishd -V


Once Varnish is installed, you need to configure it and your web server(Nginx in our case).

Configure your Web Server(Nginx)

Configure your web server to listen on a port (say: 8080) other than the default port 80 because Varnish responds directly to incoming HTTP requests, not the web server.
For this add the ‘listen’ directive in ‘server’ block as:

server {
    listen 8080 default_server;

to default nginx configuration file (generally located at /path/to/nginx/sites-enabled/default)

Configure Varnish Configuration

1. Modify Varnish System Configuration

vim /etc/default/varnish

And edit the file to have similar code:


# Maximum number of open files (for ulimit -n)

# Maximum locked memory size (for ulimit -l)
# Used for locking the shared memory log in memory.  If you increase log size,
# you need to increase this number as well

DAEMON_OPTS="-a :80 \
             -T localhost:6082 \
             -p http_resp_hdr_len=64000 \
             -f /etc/varnish/default.vcl \
             -S /etc/varnish/secret \
             -s malloc,256m"

2. Modify default.vcl
Make a backup of default one.

cp /etc/varnish/default.vcl /etc/varnish/default.vcl.bak
vim /etc/varnish/default.vcl

And edit the lines under ‘backend default’:

# Default backend definition. Set this to point to your content server.
backend default {
.host = "";
.port = "8080";


  • .host – fully qualified host name or IP address and listen port of the Varnish backend or origin server; that is, the server providing the content Varnish will accelerate. Typically, this is your web server.
  • .port – the web server’s listen port (8080 in this example)

Note:This is the minimal configuration. You need to use the configuration which you get from Magento 2’s Varnish Export (Ref – #3).

3. Configure Magento to use Varnish
Instead of manually editing default.vcl, Magneto 2 comes up with an export option for it.
First enable the Full Page Cache to use Varnish (Go to Admin > Stores > Configuration > Advanced > System > Full Page Cache), you will see as:
Save the settings and hit the Export VCL button (Varnish 3 or 4 as per your Varnish Version).
And use this default.vcl to replace the file in /etc/varnish/default.vcl

Finally, restart the Varnish & Nginx

service nginx restart
service varnish restart


I assume You have followed all the above steps (Installation & Configuration). Now lets check if Varnish cache server is taking into account for HTTP response or not.

Check if Varnish is listening to port 80
Run the following command:

netstat -tulpn | grep varnishd


Verify contents in var/cache, var/page_cache folder is not regenerated
Since FPC is configured to serve the content from Varnish, once cleared, cache folders (var/cache, var/page_cache) should always be empty .

Verify the HTTP response headers
Load your Magento 2 Frontend and check the response from browser console, you will see similar as shown in the below screenshot:

Or you can simply use CLI as:

curl -I

And You will see some similar Varnish keywords.

Voila, you’re done. Let your store fly!
Please do comment or contact us if you have any issues regarding Varnish + Magento2 Installation & Configuration.

Run Magento database repair tool from CLI using Ruby’s Mechanize

June 11, 2015  |  No Comments  |  by Raj (MagePsycho)  |  Linux, Magento, Ruby

Shell Scripting which is great for task automation, falls short when it comes to web browser automation.
In case of Magento, using Shell script you can do many operations like Installation, Migration, Deployment, Backup etc. & even more. But when it comes to run the Database Repair Tool(a great tool for repairing Magento database while upgrading) it becomes trickier and complex.

Magento Database Repair Tool

Magento Database Repair Tool

While Upgrading Magento say from to, running DB repair tool for every version manually from browser is repetitive & hectic. So I thought of writing a Ruby CLI script to automate it which will be a simple command with just two parameters(DB repair Url and Magento version).

This console script is a Ruby script which uses Mechanize gem/library.

The Mechanize library is used for automating interaction with websites. Mechanize automatically stores and sends cookies, follows redirects, and can follow links and submit forms. Form fields can be populated and submitted. Mechanize also keeps track of the sites that you have visited as a history.

Install Ruby

Ruby comes pre-installed in Mac. If you are in other operating system please refer to the Installation Document
To test if ruby is already installed, run the following command:

ruby -v

Which will give you the output like:

ruby 2.0.0p481 (2014-05-08 revision 45883) [universal.x86_64-darwin14]

Install Mechanize Gem

You need to install RVM(Ruby Version Manager) first.
First install the stable version of RVM stable with ruby:

\curl -sSL | bash -s stable --ruby

Update your gems:

sudo gem update --system

Now you can instally any gems. Here we will be installing mechanize gem:

gem install mechanize

Prepare & Run the Script

After installation of Ruby, RVM and all it’s required gems, we are ready to go for coding the script.
OS: Mac OS X Yosemite 10.10.4
Ruby: 2.0
Magento Reference Database: magento[version]_vanilla

1. Prepare the Script
Copy the following ruby script to the root of your Magento Dir:
File: mage-db-repair-tool.rb

#!/usr/bin/env ruby

# Magento DB Repair tool using CLI
# Uses Ruby's Mechanize gem
# @author   Raj KB <>
# @website
# Tested on Mac OS-X 10.X

require 'mechanize'
require 'fileutils'

def checkError(page)
  if'.msg_error').length > 0
    puts "[ERROR]"'.msg_error li').each do |li|
      puts li.text.strip

def checkResult(page)
  puts "[RESULT]"'.msg_success li').each do |li|
    puts li.text.strip

def checkNotice(page)
  puts "[NOTICE]"'.msg-note').each do |note|
    puts note.text.strip

  if'.msg-note').length > 1
    puts "See log for more details"

abort "#{$0} Argument Missing" if (ARGV.size < 1)

dbRepairUrl     = ARGV[0]
mageVersion     = ARGV[1]
mageDir         = Dir.pwd
dbRepairLogDir  = "#{mageDir}/var/dp-repair-tool"

FileUtils.mkdir_p "#{dbRepairLogDir}"
fp ="#{dbRepairLogDir}/mage-#{mageVersion}-result.html", "a+")

agent =
page = agent.get(dbRepairUrl)
puts "Loading page: #{page.title}..."
form = page.forms.first

puts "Setting db repair form values..."

# Get DB value from app/etc/local.xml
xmlFile ="#{mageDir}/app/etc/local.xml")
doc = Nokogiri::XML(xmlFile)
corruptedHostname = doc.xpath('/config/global/resources/default_setup/connection/host').text()
corruptedUsername = doc.xpath('/config/global/resources/default_setup/connection/username').text()
corruptedPassword = doc.xpath('/config/global/resources/default_setup/connection/password').text()
corruptedDatabase = doc.xpath('/config/global/resources/default_setup/connection/dbname').text()

form['post_form']           = 'true'
form['corrupted[hostname]'] = corruptedHostname
form['corrupted[database]'] = corruptedDatabase
form['corrupted[username]'] = corruptedUsername
form['corrupted[password]'] = corruptedPassword

# Edit reference database credentials
form['reference[hostname]'] = corruptedHostname
form['reference[database]'] = "magento#{mageVersion}_vanilla"
form['reference[username]'] = corruptedUsername
form['reference[password]'] = corruptedPassword

#p form; exit

puts "Submitting db repair form..."
result_page = form.submit(form.button_with(:id => "button-continue"))

# Check if there is an error & exit

# Continue if there is not an error
# Check if it further requires submission
if'button#button-continue').length > 0

    # Form found again?
    puts "Submitting again..."
    form = result_page.forms.first
    result_page = form.submit(form.button_with(:id => "button-continue"))


You can also download the script from: Magento DB Repair Tool Using CLI – Ruby + Mechanize

2. Run the Script
Give script the executable permission:

cd /path/to/mage-db-repair-tool.rb
chmod +x ./mage-db-repair-tool.rb

Now, You can run the script either by using command:

ruby mage-db-repair-tool.rb <magento-db-repair-url> <magento-version>

or simply using:

./mage-db-repair-tool.rb 1702


./mage-db-repair-tool.rb 1702
Ruby DB Repair Tool Console Output

Ruby DB Repair Tool Console Output

Apart from the results shown at the console output (refer to the above snapshot), You can also check the detailed output logged in your /path/to/magento/var/dp-repair-tool/mage-[magento-version]-result.html

Bonus Tips:

If you want to make system-wide command, then copy the file to the system-wide paths like /usr/local/bin, /usr/bin, /bin etc:

sudo cp /path/to/your/mage-db-repair-tool.rb /usr/local/bin/mageDbRepairToolRb

Now you can run the command from anywhere

mageDbRepairToolRb 1702

Shall you have any issues please post a comment below and I’ll try and help you out.