MariaDB MaxScale Exasol Router

Learn how to configure the Exasol router in MariaDB MaxScale to route analytical queries to Exasol while maintaining transactional workloads in MariaDB

Description

The Exasol Router enables MariaDB MaxScale to execute analytical SQL queries directly against the Exasol analytics engine.

When used together with SmartRouter:

• Write queries are routed to MariaDB to mainatain transactional integrity.

• Read queries are dynamically routed to either MariaDB or Exasol, based on runtime performance measurements.

SmartRouter measures execution time using the canonical form of queries (constants replaced with placeholders). When a new canonical query is encountered:

1. The query is sent to all clusters.

2. The first cluster to respond is selected as the preferred backend.

3. Other in-flight queries are cancelled.

4. Performance metrics are periodically re-evaluated.

For a detailed explanation of the routing algorithm, see SmartRouter.

This architecture allows applications to use a single connection endpoint for both OLTP and analytics workloads without application-level routing logic.

Prerequisites

• MariaDB MaxScale 25.10.1 or later must be installed. See the installation guide if required.

• Operational MariaDB deployment

• Operational Exasol deployment

• Network connectivity between MaxScale, MariaDB, and Exasol

Default ports:

• MariaDB: 3306

• MaxScale: 3306

• Exasol: 8563

Configuring the Exasol router in MariaDB MaxScale

1. Install the Exasol ODBC driver on the MaxScale host. MaxScale’s Exasol router leverages Exasol’s native ODBC connector to deliver optimal performance and full functionality.

   1. Download the latest ODBC driver. Go to the Exasol ODBC download page to identify the latest version for Linux x86_64.

   2. Install the driver.

Replace the version number in the commands below with the version you downloaded:

curl https://x-up.s3.amazonaws.com/7_x/25.2.4/Exasol_ODBC-25.2.4-Linux_x86_64.tar.gz \
     -o Exasol_ODBC-25.2.4-Linux_x86_64.tar.gz
tar -xvf Exasol_ODBC-25.2.4-Linux_x86_64.tar.gz
chmod -R 755 Exasol_ODBC-25.2.4-Linux_x86_64

1. Create the required users in both MariaDB and Exasol. MariaDB User If you do not already have a MaxScale monitor and service user, create one using the following commands.

   mariadb -e "DROP USER IF EXISTS maxuser@'%'"
   mariadb -e "CREATE USER maxuser@'%' IDENTIFIED BY 'aBcd123%'"
   mariadb -e "GRANT SUPER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES ON *.* TO maxuser@'%'"
   mariadb -e "GRANT SELECT ON mysql.db TO maxuser@'%'"
   mariadb -e "GRANT SELECT ON mysql.user TO maxuser@'%'"
   mariadb -e "GRANT SELECT ON mysql.roles_mapping TO maxuser@'%'"
   mariadb -e "GRANT SELECT ON mysql.tables_priv TO maxuser@'%'"
   mariadb -e "GRANT SELECT ON mysql.columns_priv TO maxuser@'%'"
   mariadb -e "GRANT SELECT ON mysql.proxies_priv TO maxuser@'%'"
   mariadb -e "GRANT SELECT ON mysql.procs_priv TO maxuser@'%'"
   

Exasol User

It is best practice to avoid using the sys user for application access. Create a dedicated user with the appropriate privileges.

Replace the IP address, port, and passwords to match your environment:

exaplus -c 192.168.1.100/nocertcheck:8563 -u sys -p 'syspassword' \
   -sql "CREATE USER admin_user IDENTIFIED BY 'aBcd123%'"

exaplus -c 192.168.1.100/nocertcheck:8563 -u sys -p 'syspassword' \
   -sql "GRANT CREATE SESSION, CREATE TABLE, SELECT ANY TABLE, INSERT

Important: For all connections to Exasol, the Exasol router uses a single service user. Exasol does not currently receive user‑level authentication from MariaDB clients.

1. Configure the MaxScale server and monitor.

Define the MariaDB server that will handle primary OLTP workloads. Replace the IP address and password to match your environment:

maxctrl create server mariadb1 address=127.0.0.1 port=3306 protocol=MariaDBBackend;
maxctrl create monitor mariadb_monitor mariadbmon \
  servers=mariadb1 \
  user=maxuser \
  password=aBcd123% \
  monitor_interval=1s ;

1. Configure the MaxScale Exasol router. Create the Exasol router service. This service contains the connection information for Exasol, including the ODBC driver path and credentials.

   maxctrl create service mariadb_exasolrouter exasolrouter \
   user=maxuser \
   password=aBcd123% p \
   reprocessor=auto  ' \
   connection_string=DRIVER=/home/rocky/Exasol_ODBC-25.2.4-Linux_x86_64/lib/libexaodbc.so;
   EXAHOST=102.22.2.22:8563;UID=admin_user;PWD=aBc123%%;FINGERPRINT=NOCERTCHECK' 

Replace the following placeholders with values that match your actual environment:

• DRIVER: Full path to the libexaodbc.so file from Step 1

• EXAHOST: Your Exasol host and port

• UID and PWD: The Exasol user credentials created in Step 2

1. Configure the MaxScale SmartRouter.

   The SmartRouter integrates the MariaDB server with the Exasol Router and is responsible for distributing queries between the two backends.

   Replace the password to match your environment:

   maxctrl create service mariadb_smartrouter smartrouter \
     user=maxuser \
     password='aBcd123%' \
     targets=mariadb1,mariadb_exasolrouter \
     master=mariadb1

   The master parameter designates the cluster that receives all write operations. In this configuration, all writes are directed to MariaDB.

2. Configure the MaxScale service and listeners.

   Create a listener that defines the port on which MaxScale will accept client connections for the SmartRouter service.

   Replace the port number if a different port is required:

   maxctrl create listener mariadb_smartrouter mariadb_smartrouter_listener 3306

3. Test and verify the configuration. This step provides guidance on verifying whether the Exasol and SmartRouter components are connected and functioning correctly. It also explains how to enable logging for verification purposes and outlines data synchronization requirements.

   1. Connecting to the service.

      First, verify that you can connect to MaxScale on the configured listener port:

      mariadb \
        -h <maxscale-ip> \
        -P <mariadb exa port> \
        -u <user> \
        -p 

      Replace:

      • <maxscale-ip> with the IP address of your MaxScale host.

      • <exa-listener-port> with the port you configured for the Exasol router listener.

      • <username> with a valid MariaDB username that MaxScale can authenticate.

      To perform a very basic connectivity test:

      mariadb \
      -h <maxscale-ip> \
      -P <mariadb exa port> \
      -u <user> \
      -p -e “select 1 as connected”

      If the connection is established successfully, the result will return as connected = 1. This confirms that the client can reach MaxScale and that the router is actively listening.

   2. Enabling debug logs for verification To verify which backend (MariaDB or Exasol) executed a query and inspect routing decisions, enable debug and info logging in MaxScale, and then tail the main logs:

      maxctrl alter maxscale log_debug true
      maxctrl alter maxscale log_info true

      Then, monitor the MaxScale log:

      tail -f /var/log/maxscale/maxscale.log

      When SmartRouter re-measures a query, you will see log output messages similar to:

      2026-02-13 18:14:49   info   : (3) [smartrouter] (mariadb_smartrouter); Trigger re-measure, schedule 2min, perf: mariadb1, 15.2181s, SELECT DISTINCT( IF( domain_new IS NOT NULL, domain_new, IF( username ...
      2026-02-13 18:15:04   info   : (3) [smartrouter] (mariadb_smartrouter); Update perf: from mariadb1, 15.2181s to mariadb1, 14.5416s, SELECT DISTINCT( IF( domain_new IS NOT NULL, domain_new, IF( username ...

      These messages indicate which backend is being evaluated. Another way to determine how a query was executed is by using the Hint Filter. You can force routing to a specific backend by adding a SQL comment.

   3. Data synchronizing requirements

   The Exasol Router does not automatically synchronize data between MariaDB and Exasol.

In the event that Change Data Capture (CDC) is not set up:

• Data inserted into MariaDB will not automatically appear in Exasol.

• Unless the same dataset is present in both systems, queries sent to Exasol may result in empty results.

In order to conduct relevant query testing:

• Put the same datasets into Exasol and MariaDB, or

• Set up CDC to copy data to Exasol from MariaDB.

Behavior during Backend Unavailability

SmartRouter automatically connects to the designated master (MariaDB) in the event that Exasol is unavailable (for example, due to a network outage or unavailability). To maintain availability for OLTP activities, all reads and writes will only be sent to MariaDB.

Known Limitations

The MariaDB MaxScale–Exasol integration includes some limitations. It includes:

• Exasol access is limited to a single service user (unlike MariaDB, which required per user authentication)

• The SQL preparser does not support all MariaDB functions.

• The following function mappings are necessary:

  • FROM_UNIXTIME() → FROM_POSIX_TIME()

  • DATE_FORMAT → TO_CHAR

• The following interactive statements are not the primary focus and may not behave as expected:

  • SHOW TABLES

  • Use database

  • DESCRIBE table

  • DDL statements

See Also

• MaxScale Exasol Router

• MaxScale SmartRouter

• Hint Filter