Integrating Magento with Cloud Spanner

1. Introduction

Integrating Magento with a Cloud Spanner backend

Magento is a widely popular PHP based open source eCommerce platform which stores data in MySQL.

This codelab is a Proof of Concept to leverage Cloud Spanner instead of MySQL for the Catalog module. This is useful for anyone interested in integrating, testing, and deploying Magento, or other PHP applications, with Spanner.

Spanner is Google Cloud's fully managed, enterprise-grade, distributed, and consistent database which combines the benefits of the relational database model with non-relational horizontal scalability. It is designed to support global online transaction processing deployments, SQL semantics, highly available horizontal scaling and transactional consistency. Spanner is capable of handling large volumes of data. Its use is not limited to applications of large size but it allows the standardization of a single database engine for all workloads requiring RDBMS. Spanner provides zero-downtime for planned maintenance or region failures, with an availability SLA of 99.999%. It supports modern applications by providing high availability and scalability.

What you'll learn

• How to install Magento on GCE
• How to set up the Spanner Emulator
• How to migrate an existing MySQL schema to Spanner using HarbourBridge
• What you need to change to integrate PHP applications like Magento that use MySQL for the database backend to work with Spanner

What you'll build

This codelab is focused on integrating Magento with Spanner. Code blocks and setup instructions are provided for you to copy and paste, but are not discussed in detail.

In this codelab, you're going to begin integrating Magento with Spanner. You will:

• Set up a GCE instance with Magento installed
• Install the Spanner Emulator
• Install HarbourBridge Tool for data migration from MySQL to Spanner
• Modify Magento collections to load the product Catalog from Spanner

What you'll need

• A Google Cloud project that is connected to a billing account.
• Knowledge of PHP, Linux and Apache configuration is a plus.
• Magento experience will be helpful, but is not required.

2. Preparing the GCE instance

Create the GCE instance

Create a Compute Engine instance in Google Cloud Platform, by following the steps mentioned here.

When creating the GCE instance, change the instance type to e2-standard-2 and boot disk size to 20GB. You can leave everything as default, but be sure to select "Allow HTTP traffic" and "Allow HTTPs traffic", since we will be leveraging the web interface of Magento.

This results in a machine-type of e2-standard-2 which is not a shared core instance and has 2vCPU, 8GB of RAM and 20GB of disk space.

The operating system is Debian 10. Instance creation may take a minute or two.

Once it is created, go ahead and log in by clicking ‘SSH' in the Cloud Console:

This will open a new browser window and place you in a terminal.

Install the prerequisite software

Magento will need some prerequisite software to be installed before we can run Magento. Specifically, you will install PHP, Elastic, MySQL and Apache as detailed below.

1. Install some required packages.

sudo apt update

sudo apt -y install lsb-release apt-transport-https ca-certificates wget git screen composer google-cloud-sdk-spanner-emulator gcc

2. Install PHP modules required for Magento.

Note: Since we are on Debian 10, we need to install the SURY repository to get PHP 7.4 installed.

sudo wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg

echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/php.list

sudo apt update

sudo apt -y install php7.4-fpm php7.4-common php7.4-mysql php7.4-gmp php7.4-curl php7.4-intl php7.4-mbstring php7.4-xmlrpc php7.4-gd php7.4-xml php7.4-cli php7.4-zip php7.4-bcmath php7.4-soap php7.4-grpc

3. Install Elasticsearch and start the service

wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -

echo "deb https://artifacts.elastic.co/packages/7.x/apt stable main" | sudo tee /etc/apt/sources.list.d/elastic-7.x.list

sudo apt update && sudo apt -y install elasticsearch

echo "-Xms1g
-Xmx1g" | sudo tee /etc/elasticsearch/jvm.options.d/jvm.options

sudo systemctl start elasticsearch.service

4. Install MySQL

You are installing MySQL to get the default Magento schema installed. Later, you will migrate the schema to Spanner using HarbourBridge.

wget https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

sudo dpkg -i mysql-apt-config*

The dpkg command above will bring up an interactive prompt to install the MySQL 5.7 Server. Select the options:

• MySQL Server & Cluster
• mysql-5.7
• Ok

sudo apt update && sudo apt -y install mysql-server
# You will be prompted to enter a root password

Note: During installation you are asked to create a root password. Remember this, as you will need it later.

5. Install Apache2

sudo apt -y install apache2

sudo a2enmod proxy_fcgi rewrite

Note: The a2enmod command asks you to restart apache so that the new configuration can be activated. But you will restart apache later, so there's no need to do that now.

Install and configure Magento2

The Magento Commerce Cloud project includes a database schema and services to fully access the Magento site and store.

The easiest way to get this installed and running is to follow the Magento instructions to install using composer:

1. Install Magento version 2.4.2 using composer. Magento 2 requires the composer 1.x version. You may see some warnings about support for this version being deprecated.

composer create-project --repository-url=https://repo.magento.com/ magento/project-community-edition=2.4.2 magento2

Note: This step requires a Magento.com account. You will need to create an account if you do not already have one, and set up an Access Key according to these instructions. Note that you will need to use the public key as your username and the private key as your password.

Note: We are installing Magento 2.4.2 because this is the version of the code the Spanner port leveraged when created.

2. Set folder permissions

cd magento2

find var generated vendor pub/static pub/media app/etc -type f -exec chmod g+w {} +

find var generated vendor pub/static pub/media app/etc -type d -exec chmod g+ws {} +

3. Configure the Magento virtual host by creating /etc/apache2/sites-available/magento.conf with the contents below.

sudo nano /etc/apache2/sites-available/magento.conf

<VirtualHost *:80>
        ServerAdmin [e-mail]
        DocumentRoot /var/www/html/magento/

        <Directory /var/www/html/magento/>
                Options Indexes FollowSymlinks MultiViews
                AllowOverride All
                Order allow,deny
                allow from all
        </Directory>

        <FilesMatch \.php$>
               SetHandler "proxy:unix:/run/php/php7.4-fpm.sock|fcgi://localhost"
        </FilesMatch>

        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>

4. Create the symlinks and restart apache2.

cd ~/magento2
sudo ln -s $(pwd) /var/www/html/magento 
sudo ln -s /etc/apache2/sites-available/magento.conf  /etc/apache2/sites-enabled/magento.conf
sudo rm /etc/apache2/sites-enabled/000-default.conf

sudo systemctl restart apache2

5. Create the database and user for Magento in MySQL

export ROOT_PASSWORD="<root password from installation>"
export GCE_INSTANCE_IP="<GCE instance IP>"
mysql -uroot -p$ROOT_PASSWORD -e "create database magento"

bin/magento sampledata:deploy

bin/magento setup:install --base-url=http://$GCE_INSTANCE_IP/ --db-host=localhost \
--db-name=magento --db-user=root --db-password=$ROOT_PASSWORD --admin-firstname=admin \
--admin-lastname=demo --admin-email=[e-mail] --admin-user=admin \
--admin-password=magento123 --language=en_US --currency=USD --timezone=America/Chicago \
--use-rewrites=1

sudo chown -R :www-data ~/magento2/. 

Note: Be sure to use the GCE external IP for the base-url parameter. You can find this in the Google Cloud Console.

Note: The passwords and users created here are not recommended for production Magento configuration. They are meant simply as an example for this codelab.

6. Verify your local workspace To verify the local environment is hosting the server, access the store using the base URL you passed in the install command. For this example, you can access the local Magento store using the following URL formats:

• http://<GCEexternalIP>/
• http://<GCEexternalIP>/<adminuri>

The GCEexternalIP can be found in Cloud Console:

To change the URI for the Admin panel, use this command to locate it:

php bin/magento info:adminuri

7. Disable full page cache For development purposes, you can disable the full page cache of Magento2. This will allow you to modify the data in Spanner and have it reflected on the website without being impacted by cached values.

php bin/magento cache:disable full_page

Setup Spanner

Install the Spanner Emulator

The Cloud SDK provides a local, in-memory emulator, which you can use to develop and test your applications for free without creating a GCP Project or a billing account. As the emulator stores data only in memory, all state, including data, schema, and configs, is lost on restart. The emulator offers the same APIs as the Spanner production service and is intended for local development and testing, not for production deployments.

Please use the below link to refer further for installation, usage and deployment of the Emulator:

Using the Spanner Emulator

# Set up a new configuration to use the emulator
gcloud config configurations create emulator
gcloud config set auth/disable_credentials true
gcloud config set project magento
gcloud config set api_endpoint_overrides/spanner http://localhost:9020/

# Start emulator in a screen session
screen -S magento
gcloud emulators spanner start &
gcloud spanner instances create magento-instance --config=emulator-config --description='Magento Instance' --nodes=1

# Detach from screen 
ctrl+a+d

export SPANNER_EMULATOR_HOST=localhost:9010

Note: Starting the emulator in a screen session will allow it to persist if your SSH session terminates.

Migrate Magento MySQL to Spanner

Before we dive into integrating Spanner, we will use a tool called HarbourBridge to convert the MySQL database that was created as part of our Magento installation above to Spanner.

At its core, HarbourBridge provides an automated workflow for loading the contents of an existing MySQL or PostgreSQL database into Spanner. It requires zero configuration—no manifests or data maps to write. Instead, it imports the source database, builds a Spanner schema, creates a new Spanner database populated with data from the source database, and generates a detailed assessment report. HarbourBridge is intended for loading databases up to a few tens of GB for evaluation purposes, not full-scale migrations.

HarbourBridge bootstraps early-stage migration to Spanner by using an existing MySQL or PostgreSQL source database to quickly get you up and running on Spanner. It generates an assessment report with an overall migration-fitness score for Spanner, a table-by-table analysis of type mappings and a list of features used in the source database that aren't supported by Spanner.

HarbourBridge can be used with the Spanner Emulator, or directly with a Spanner instance.

The HarbourBridge README contains a step-by-step quick-start guide for using the tool with a Spanner instance.

Install HarbourBridge

Download the tool to your machine and install it. It is required to install golang for this to work. It can take a while to install all the required modules on a fresh instance without Go previously set up.

# Install golang
cd ~
wget https://golang.org/dl/go1.17.2.linux-amd64.tar.gz
sudo tar -zxvf go1.17.2.linux-amd64.tar.gz -C /usr/local
rm go1.17.2.linux-amd64.tar.gz

echo 'export GOROOT=/usr/local/go' | sudo tee -a /etc/profile
echo 'export PATH=/usr/local/go/bin:$HOME/go/bin:$PATH' | sudo tee -a /etc/profile
source /etc/profile

# Install harbourbridge
git clone https://github.com/cloudspannerecosystem/harbourbridge
cd harbourbridge
go run github.com/cloudspannerecosystem/harbourbridge help

Migrate the data

Use the following command to migrate the Magento database into Spanner:

mysqldump --user='root' --password=$ROOT_PASSWORD magento | go run github.com/cloudspannerecosystem/harbourbridge -driver=mysqldump -dbname=magento

Note: This migration can take several minutes to complete.

Set up the spanner-cli tool

go install github.com/cloudspannerecosystem/spanner-cli@latest

3. Convert Magento to work with Spanner

Now that we have Magento running, and the Spanner instance created with the Magento database migrated, we will work on modifying Magento to work with the data stored in Spanner.

The following steps will be performed to convert the Magento installation:

• Clone the magento-spanner-port project
• Change the connection to Spanner
• Validate that the Catalog details are populated from Spanner

Clone the fork of the Magento project

Clone the PHP application code for Magento which contains the modifications for the Catalog, Wishlist and Cart modules from the Git url mentioned below.

cd ~
git clone https://github.com/searceinc/magento-spanner-port

Note: The above repository is a fork of the Magento2 project at a specific point-in-time. It is meant as an example project, and will not be maintained or kept up-to-date with changes from the upstream codebase.

Your home directory should look something like this:

$ ls
go  harbourbridge  magento-spanner-port  magento2

Where magento2 is the codebase we will be modifying, using code from magento-spanner-port.

Change the connection to Spanner

To check if the code modifications reflect in the UI, we can follow the steps below -

Refer the Github Link https://github.com/searceinc/magento-spanner-port for sample implementation.

• Require the google/cloud-spanner PHP client library
• Add Spanner Adapter for creating connection to Spanner.
• Configure the Spanner instance and server information.
• Add SpannerInterface and Spanner in the Adapter to implement the connection to Spanner.

First, we need to install the cloud-spanner PHP library using composer. In the magento2 directory, run this command:

cd ~/magento2
composer require google/cloud-spanner

Then we add the Spanner Adapter files from the magento-spanner-port into our magento2 codebase:

~/magento2$ cp -r ../magento-spanner-port/lib/internal/Magento/Framework/DB/Adapter/Spanner vendor/magento/framework/DB/Adapter/.
~/magento2$ ls -l vendor/magento/framework/DB/Adapter/Spanner
total 16
-rw-r--r-- 1 derekdowney derekdowney 10378 Nov  9 21:03 Spanner.php
-rw-r--r-- 1 derekdowney derekdowney  2948 Nov  9 21:03 SpannerInterface.php

Now, modify the DB/Adapter/Spanner/Spanner.php file to input the Spanner connectivity information for $project_id, $instance, and $database:

$ nano vendor/magento/framework/DB/Adapter/Spanner/Spanner.php

class Spanner implements SpannerInterface
{
    /**
     * Google cloud project id
     * @var string
     */
    private $project_id = 'magento';

    /**
     * Google cloud instance name
     * @var string
     */
    private $instance  = 'magento-instance';

    /**
     * Cloud Spanner database name
     * @var string
     */
    private $database  = 'magento';

    /**
     * Is Cloud Spanner emulator
     * @var bool
     */
    private $is_emulator = true;
...
   /**
    * Set database connection adapter
    *
    * @param \Magento\Framework\DB\Adapter\AdapterInterface $conn
    * @return $this
    * @throws \Magento\Framework\Exception\LocalizedException
    */
   public function setConnection(\Magento\Framework\DB\Adapter\AdapterInterface $conn)
   {
       $this->_conn = $conn;
       $this->_select = $this->_conn->select();
       $this->_isOrdersRendered = false;
       return $this;
   }


   /**
     * Set Cloud Spanner database connection adapter
     *
     * @return void
     * @throws \Magento\Framework\Exception\LocalizedException
     */
    private function setSpannerConnection()
    {
        $this->_spanner_conn = new Spanner();
    }

Modify the AbstractDB class within Magento to now connect to Spanner using the newly created Connection function within Spanner Adapter. Add the green lines after the white lines in the file. Refer to vendor/magento/framework/Data/Collection/AbstractDb.php

$ nano vendor/magento/framework/Data/Collection/AbstractDb.php
...
use Psr\Log\LoggerInterface as Logger;
use Magento\Framework\DB\Adapter\Spanner\Spanner;
...
    protected $_conn;

    /**
     * Cloud Spanner connection
     *
     * @var \Magento\Framework\DB\Adapter\Spanner\SpannerAdapterInterface
     */
    protected $_spanner_conn;
...
       if ($connection !== null) {
            $this->setConnection($connection);
        }
        $this->setSpannerConnection();
        $this->_logger = $logger;
...
   /**
     * Retrieve connection object
     *
     * @return AdapterInterface
     */
    public function getConnection()
    {
        return $this->_conn;
    }

   /**
     * Retrieve connection object
     *
     * @return SpannerAdapterInterface
     */
    public function getSpannerConnection()
    {
        return $this->_spanner_conn;
    }
...

Once the connection is established we need to modify the data fetch method from the MySQL adapter to the Spanner adapter . Modify the _loadAttributes method in AbstractCollection to connect to Spanner and fetch the data from Spanner. Replace the red line with the lines in green.

Refer to /app/code/Magento/Eav/Model/Entity/Collection/AbstractCollection.php

$ nano ./vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php

use Magento\Framework\Exception\LocalizedException;
use Google\Cloud\Spanner\SpannerClient;

...
               try {
                    if (is_array($selects)) {
                        $select = implode(' UNION ALL ', $selects);
                    } else {
                        $select = $selects;
                    }
                   $values = $this->getConnection()->fetchAll($select);
                   $con = $this->getSpannerConnection();

                    /**
                     * Cloud Spanner follows strict type so cast the columns in common type
                     */
                    $select = $con->addCast($select, "`t_d`.`value`", 'string');
                    $select = $con->addCast($select, "`t_s`.`value`", 'string');
                    $select = $con->addCast($select, "IF(t_s.value_id IS NULL, t_d.value, t_s.value)", 'string');
                    
                    $values = $con->fetchAll($select);

...

Validate that the Catalog details are populated from Spanner

That's it! Now, you can go to your Magento install in the browser and check that the data is loading.

For example, these are the catalog entries for watches:

Modify Spanner data via the terminal for one of the products and query the data via terminal to confirm the modification in Spanner.

$ spanner-cli -pmagento -i magento-instance -d magento
spanner> SELECT * FROM catalog_product_entity_varchar WHERE value LIKE "Aim Analog%";
+----------+--------------+----------+-----------+--------------------+
| value_id | attribute_id | store_id | entity_id | value              |
+----------+--------------+----------+-----------+--------------------+
| 390      | 73           | 0        | 36        | Aim Analog Watch |
+----------+--------------+----------+-----------+--------------------+
1 rows in set (80.711542ms)

spanner> UPDATE catalog_product_entity_varchar SET value = "Aim Analog Spanner" WHERE value_id=390;
Query OK, 1 rows affected (0.19 sec)

spanner> SELECT * FROM catalog_product_entity_varchar WHERE value_id=390;
+----------+--------------+----------+-----------+--------------------+
| value_id | attribute_id | store_id | entity_id | value              |
+----------+--------------+----------+-----------+--------------------+
| 390      | 73           | 0        | 36        | Aim Analog Spanner |
+----------+--------------+----------+-----------+--------------------+
1 rows in set (80.711542ms)

Note: The actual value_id for this item may be different in your installation. Use the value_id output from the first query.

Now, reload the screen to confirm that the name of the watch is now changed to "Aim Analog Spanner" as updated via the Spanner terminal.

4. Congratulations

Congratulations, you have successfully connected the Catalog module of Magento to work with Spanner! It is not a full integration, but you now know the elements to get a PHP application like Magento connected to a Spanner instance.

Cleaning up

When the POC setup and validation is completed, you may want to delete the GCP resources created during the process. This would include the Compute Engine Virtual Machine, as well as a Cloud Spanner instance if you decided to use one instead of the Emulator.

What's next?

This is just a prototype model for a Spanner POC.

If you want to learn more about working with the Spanner and the technologies we leveraged in this codelab, here are some additional resources:

• Working with the Spanner Emulator
• Getting started with PHP and Spanner
• Migrating to Spanner with HarbourBridge