Upgrade a Single ColumnStore 1.4 on Ubuntu 16.04 LTS

These instructions detail the upgrade to MariaDB ColumnStore 1.4 on Ubuntu 16.04 LTS in a Single ColumnStore Instance configuration.

An upgrade is a change from an earlier release of MariaDB ColumnStore with a patched version of MariaDB Server, to MariaDB Enterprise Server 10.4.11-5 with MariaDB ColumnStore 1.4 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 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. We strongly advise performing 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.

$ 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 packages, connect 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

Before the old version can be uninstalled, stop the current MariaDB ColumnStore process.

To stop MariaDB ColumnStore, use mcsadmin with the stopSystem command:

$ mcsadmin stopSystem

Uninstall via APT (Debian/Ubuntu)

Uninstall all MariaDB ColumnStore packages. Note that a wildcard character is used 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 Ubuntu 16.04 LTS.

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

Retrieve your access token at https://customers.mariadb.com/downloads/token/ and substitute for customer_access_token in the following directions.

To configure APT package repositories:

$ sudo apt install wget

$ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup

$ echo "fc429a488e372c0d714c705b1dceb245904bcba8e76516fc8504f7e57d3530fd  mariadb_es_repo_setup" \
    | sha256sum -c -

$ chmod +x mariadb_es_repo_setup

$ sudo ./mariadb_es_repo_setup --token="customer_access_token" --apply \
   --mariadb-server-version="10.4"

$ sudo apt update

To install MariaDB ColumnStore and package dependencies:

$ sudo apt install mariadb-server \
    mariadb-columnstore-platform mariadb-plugin-columnstore

Installation loads software to the system. This software requires post-install actions and 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 process 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.

Renamed Server Configuration

For platforms that use YUM or ZYpp as a package manager, if you had edited the /etc/my.cnf.d/server.cnf configuration file from the previous installation, your edited version of that file may have been moved to /etc/my.cnf.d/server.cnf.rpmsave. If you wish to continue using it, you need to move it back. For instance:

$ sudo mv /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.original
$ sudo mv /etc/my.cnf.d/server.cnf.rpmsave /etc/my.cnf.d/server.cnf

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.service
$ sudo systemctl disable mariadb.service

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.

First, move the default data directory from the new installation 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 MariaDB Enterprise Server starts, it now uses the data directory of the old Server.

Restoring ColumnStore Data

In MariaDB ColumnStore 1.2, DBRoot files are located in the ColumnStore home directory. The convention for the file names is dataN:

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

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 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:

$ sudo /usr/bin/postConfigure -qs

The postConfigure script performs the following configuration:

  • Configures ColumnStore Instance to operate in single-node mode.

  • Sets the system name.

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

  • Starts MariaDB ColumnStore.

Restart the System

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

$ sudo mcsadmin restartSystem

Update 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 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 a user with the necessary privileges, use a GRANT statement with the relevant credentials:

GRANT SELECT ON *.* TO cross_engine@127.0.0.1 IDENTIFIED BY "cross_engine_passwd";

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

To check the MariaDB Enterprise Server status, attempt to connect to it using the MariaDB Client on 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.11-5-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

To check the MariaDB ColumnStore system status, use mcsadmin to call the 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