Upgrade a Multi-Node MariaDB Enterprise ColumnStore Deployment from 1.2 to 1.4 with MariaDB Enterprise Server 10.4 on Debian 9

These instructions detail the upgrade from MariaDB ColumnStore 1.2 to MariaDB Enterprise ColumnStore 1.4 with MariaDB Enterprise Server 10.4 on Debian 9 in a Multi-node ColumnStore Deployment configuration.

In this upgrade process, the system is upgraded from MariaDB ColumnStore 1.2, which is bundled with a patched version of MariaDB Server, to MariaDB Enterprise ColumnStore 1.4, which integrates with MariaDB Enterprise Server 10.4 as a storage engine plugin.

When MariaDB ColumnStore is upgraded, the old version must be uninstalled and the new version installed. Additionally, the configuration of MariaDB ColumnStore 1.2 and earlier is not compatible with MariaDB Enterprise ColumnStore 1.4. System variables and plugins have been renamed, which requires that you edit your configuration files to the new standard in addition to upgrading your software.

Data Backup

Occasionally, issues can be encountered during upgrades. These issues can even potentially corrupt the database's data files, preventing you from easily reverting to the old installation. It is generally best to perform a backup prior to upgrading. If an issue is encountered during the upgrade, you can use the backup to restore your MariaDB ColumnStore database to the old version. If the upgrade finishes without issue, the backup can be deleted.

Run columnstoreBackup for each Performance Module:

$ columnstoreBackup -zv 192.0.2.1 /data/backups/pm1

Confirm the successful completion of the backup operation.

Backups should be tested before they are trusted.

Additional information on columnstoreBackup is available on the MariaDB Knowledge Base.

Uninstall Old Version

When upgrading MariaDB ColumnStore, ColumnStore plugins as well as the previous installation of MariaDB server must be uninstalled before attempting to install the new release.

Remove ColumnStore Plugins

MariaDB ColumnStore 1.4 includes several changes to the names of the plugins used by the storage engine. These changes make the plugin loads from MariaDB ColumnStore 1.2 incompatible with a 1.4 installation.

Before removing the previous installation, connect to the Server through the ColumnStore 1.2 Client, mcsmysql, and use UNINSTALL PLUGIN to remove the MariaDB ColumnStore plugins:

UNINSTALL PLUGIN infinidb;
UNINSTALL PLUGIN columnstore_tables;
UNINSTALL PLUGIN columnstore_columns;
UNINSTALL PLUGIN columnstore_extents;
UNINSTALL PLUGIN columnstore_files;
UNINSTALL PLUGIN columnstore;

Removing these plugins ensures that when the upgraded Server starts, it doesn't raise errors attempting to load plugins under their old names.

Stop ColumnStore

The current MariaDB ColumnStore processes must be stopped before uninstalling the installation. To stop the ColumnStore Instance, use mcsadmin shutdownSystem:

$ mcsadmin shutdownSystem y

Uninstall via APT (Debian/Ubuntu)

Uninstall all MariaDB ColumnStore packages. Note that a wildcard character used in the command to ensure that all MariaDB ColumnStore packages are uninstalled. Check that this wildcard does not unintentionally refer to any of your custom applications.

To remove all MariaDB ColumnStore packages, run the following command:

$ sudo apt remove "mariadb-columnstore-*"

Before proceeding, verify that all MariaDB ColumnStore packages are uninstalled. The following command should not return any results, if it does, remove those packages individually:

$ apt list --installed | grep -i -E "mariadb-columnstore"

Installation

MariaDB Corporation provides a APT package repository for Debian 9.

MariaDB Enterprise Server 10.4 does not require additional software to operate as an analytics database with MariaDB ColumnStore.

Install via APT (Debian/Ubuntu)

  1. Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for customer_download_token in the following directions.

  2. Configure the APT package repository.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "957bc29576e8fd320fa18e35fa49b5733f3c8eeb4ca06792fb1f05e089c810ff  mariadb_es_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_es_repo_setup
    
    $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \
       --mariadb-server-version="10.4"
    
    $ sudo apt update
    
  3. Install MariaDB ColumnStore and package dependencies:

    $ sudo apt install mariadb-server \
        mariadb-columnstore-platform mariadb-plugin-columnstore
    
  4. Stop the Server and disable the service after installing the packages:

    $ sudo systemctl stop mariadb
    $ sudo systemctl disable mariadb
    

    MariaDB ColumnStore post-installation scripts fail if they find MariaDB Enterprise Server running on the system.

  5. Configure MariaDB ColumnStore.

    Installation only loads MariaDB ColumnStore to the system. MariaDB ColumnStore requires configuration before the database server is ready for use.

Configuration

Configuration Compatibility

MariaDB ColumnStore 1.4 updates the prefix used in system variables. The infinidb_ prefix used in MariaDB ColumnStore 1.2 has been replaced with the columnstore_ prefix.

MariaDB ColumnStore 1.2 configuration files are not compatible with MariaDB ColumnStore 1.4.

Cross Engine Joins

Cross Engine Joins use a TCP connection from the ExeMgr to the Server, using the root user with no password by default. MariaDB Enterprise Server 10.4 does not accept the default configuration. If you plan to use Cross Engine Joins, you need to create a new user for this purpose and update the MariaDB Columnstore.xml configuration file.

Warning

Editing the Columnstore.xml is dangerous and can have unexpected results. Do not edit this configuration unless you specifically need support for Cross Engine Joins.

Edit the /etc/columnstore/Columnstore.xml file, modifying the <CrossEngineSupport> element to use the configured cross_engine user on Cross Engine Joins:

<CrossEngineSupport>
   <Host>127.0.0.1</Host>
   <Port>3306</Port>
   <User>cross_engine</User>
   <Password>cross_engine_passwd</Password>
</CrossEngineSupport>

The configuration sets the user and password MariaDB ColumnStore uses for Cross Engine Joins. In order for this user to work, you must also create the user and grant it the appropriate privileges. Create the relevant user once you have the upgraded instance online.

Restore Data

MariaDB Enterprise Server with MariaDB ColumnStore 1.4 uses a different directory structure from MariaDB ColumnStore 1.2. In order to restore your data, you need to reconfigure the Server to use the 1.2 directory locations.

Stop the Server

Packages managers may start the service for MariaDB Enterprise Server during installation. When the Server operates as a ColumnStore Instance, it uses a separate conflicting service. Stop and disable the default service before proceeding:

$ sudo systemctl stop mariadb
$ sudo systemctl disable mariadb

Restoring Server Data

In MariaDB ColumnStore 1.2, MariaDB Server writes ColumnStore data to DBRoots and data from all other storage engines in a non-standard data directory located in root installations at /usr/local/mariadb/columnstore/mysql/db/. To restore the Server data from the 1.2 to the MariaDB Enterprise Server 10.4 installation, you need to move the old data directory to the new location.

On each MariaDB Enterprise Server, move the default data directory to an alternate location:

$ sudo mv /var/lib/mysql /var/lib/mysql_default

Then, move the data directory from MariaDB ColumnStore 1.2 to the new location:

$ sudo mv /usr/local/mariadb/columnstore/mysql/db /var/lib/mysql

When you start the ColumnStore Instance, it starts MariaDB Enterprise Server on the data directory from the old Server.

Restoring ColumnStore Data

In MariaDB ColumnStore 1.2, DBRoot files containing ColumnStore data are stored on Performance Modules (PM), in the ColumnStore home directory. The convention for the file names is dataN.

On each PM, identify the local DBRoots:

$ sudo ls /usr/local/mariadb/columnstore | grep "data[0-9]+"
data1   data2   data3

For each DBRoot, move it from the directory used by the ColumnStore 1.2 installation to the one used by the ColumnStore 1.4 installation:

$ sudo mv /usr/local/mariadb/columnstore/data1 /var/lib/columnstore/data1

Post Installation

Installing the MariaDB Enterprise Server and MariaDB ColumnStore packages provides you with necessary packages to run the Server as a ColumnStore Instance, but only configures the Server as a Server. A few additional steps are needed to configure the MariaDB ColumnStore storage back-end.

Update the Bash Profile

MariaDB ColumnStore 1.2 shipped with a columnstoreAlias.sh script, which created a series of aliases for various commands.

Remove reference to this file from your .bash_profile and any copies in /etc/profile.d/ to ensure that the commands below call the MariaDB ColumnStore 1.4 binaries in /usr/bin and not the MariaDB ColumnStore 1.2 aliases.

Note, that to restore the environment variables, you will need to open a new shell after the above change.

Post-Installation Script

Run the columnstore-post-install script on each ColumnStore Instance to provision the system to host the storage back-end:

$ sudo columnstore-post-install

Post-Configuration Script

MariaDB ColumnStore provides a post-configuration script to configure the ColumnStore Instance. Post-configuration is only required on the first Server in your deployment (called pm1 by convention). The postConfigure script connects over SSH to initialize Performance Modules on the other Servers.

Run the postConfigure script on the Server hosting the initial Performance Module (that is, pm1):

$ sudo /usr/bin/postConfigure

The postConfigure script performs the following configuration:

  • When prompted to configure the ColumnStore deployment for single- or multi-node, select multi-node.

  • Set the system name.

  • Configure the storage mount to store data internally on the local file system.

Once postConfigure has the information it needs, it connects to each Server and sets up the Performance Module to match the configuration, then starts MariaDB ColumnStore.

Restart the System

When the postConfigure script is complete, use mcsadmin restartSystem to restart MariaDB ColumnStore to clear the cache:

$ sudo mcsadmin restartSystem y

Upgrade the Data Directory

When the Server is running with MariaDB ColumnStore, run mariadb-upgrade to update the data directory:

$ sudo mariadb-upgrade

Create the Cross Engine User

Configuring MariaDB ColumnStore to perform Cross Engine Joins sets the user and password the system uses when reading data for the operation, it does not create the user or grant it the privileges to access the necessary data.

If you have configured your system to perform Cross Engine Joins, you must create a user with sufficient privileges to access that data. To create this user, use the CREATE USER and GRANT statements with the relevant credentials:

CREATE USER 'cross_engine'@'127.0.0.1'
   IDENTIFIED BY "cross_engine_passwd";

GRANT SELECT ON *.*
   TO 'cross_engine'@'127.0.0.1';

Administration

MariaDB ColumnStore includes an administrative utility called mcsadmin, which you can use to start and stop the ColumnStore processes:

Operation

Command

Start

mcsadmin startSystem

Stop

mcsadmin shutdownSystem

Restart

mcsadmin restartSystem

Status

mcsadmin getSystemStatus

Testing

When you have MariaDB ColumnStore up and running, you should test it to ensure that it is in working order and that there were not any issues during startup.

Checking Server Status

  1. Connect to the server using MariaDB Client using the root@localhost user account:

    $ sudo mariadb
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.4.13-7-MariaDB-Enterprise MariaDB Enterprise Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]>
    

Checking System Status

  1. Execute the mcsadmin getSystemStatus command:

    $ sudo mcsadmin getSystemStatus
    getsystemstatus   Wed Jan  8 23:44:55 2020
    
    System columnstore-1
    
    System and Module statuses
    
    Component     Status                       Last Status Change
    ------------  --------------------------   ------------------------
    System        ACTIVE                       Wed Jan  8 23:14:14 2020
    
    Module pm1    ACTIVE                       Wed Jan  8 23:14:11 2020
    
    Module pm2    ACTIVE                       Wed Jan  8 23:14:11 2020