Skip to main content
ChartDB provides full support for MariaDB, a MySQL-compatible open-source database. MariaDB support is nearly identical to MySQL with some enhanced features.

​
Overview

MariaDB is a fork of MySQL that maintains compatibility while adding improvements. ChartDB’s MariaDB support includes:
  • MySQL-compatible Smart Query
  • Enhanced GROUP_CONCAT support
  • InnoDB statistics
  • Full DDL import/export

​
Smart Query Import

The MariaDB Smart Query is based on the MySQL query with optimizations for MariaDB features.

​
MariaDB Query

SET SESSION group_concat_max_len = 10000000;

SELECT CAST(CONCAT(
    '{"fk_info": [',
    IFNULL((SELECT GROUP_CONCAT(
        CONCAT('{"schema":"', cast(fk.table_schema as CHAR),
               '","table":"', fk.table_name,
               '","column":"', IFNULL(fk.fk_column, ''),
               '","foreign_key_name":"', IFNULL(fk.foreign_key_name, ''),
               '","reference_table":"', IFNULL(fk.reference_table, ''),
               '","reference_schema":"', IFNULL(fk.reference_schema, ''),
               '","reference_column":"', IFNULL(fk.reference_column, ''),
               '","fk_def":"', IFNULL(fk.fk_def, ''), '"}')
    ) FROM (
        SELECT kcu.table_schema,
               kcu.table_name,
               kcu.column_name AS fk_column,
               kcu.constraint_name AS foreign_key_name,
               kcu.referenced_table_schema as reference_schema,
               kcu.referenced_table_name AS reference_table,
               kcu.referenced_column_name AS reference_column,
               CONCAT('FOREIGN KEY (', kcu.column_name, ') REFERENCES ',
                      kcu.referenced_table_name, '(', kcu.referenced_column_name, ') ',
                      'ON UPDATE ', rc.update_rule,
                      ' ON DELETE ', rc.delete_rule) AS fk_def
        FROM information_schema.key_column_usage kcu
        JOIN information_schema.referential_constraints rc
          ON kcu.constraint_name = rc.constraint_name
         AND kcu.table_schema = rc.constraint_schema
         AND kcu.table_name = rc.table_name
        WHERE kcu.referenced_table_name IS NOT NULL
          AND kcu.table_schema = DATABASE()
    ) AS fk), ''),
    '], "pk_info": [',
    IFNULL((SELECT GROUP_CONCAT(
        CONCAT('{"schema":"', cast(pk.TABLE_SCHEMA as CHAR),
               '","table":"', pk.pk_table,
               '","column":"', pk.pk_column,
               '","pk_def":"', IFNULL(pk.pk_def, ''), '"}')
    ) FROM (
        SELECT TABLE_SCHEMA,
               TABLE_NAME AS pk_table,
               COLUMN_NAME AS pk_column,
               (SELECT CONCAT('PRIMARY KEY (', GROUP_CONCAT(inc.COLUMN_NAME ORDER BY inc.ORDINAL_POSITION SEPARATOR ', '), ')')
                               FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as inc
                               WHERE inc.CONSTRAINT_NAME = 'PRIMARY' and
                                     outc.TABLE_SCHEMA = inc.TABLE_SCHEMA and
                                     outc.TABLE_NAME = inc.TABLE_NAME) AS pk_def
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as outc
        WHERE CONSTRAINT_NAME = 'PRIMARY'
              and table_schema LIKE IFNULL(NULL, '%')
              AND table_schema = DATABASE()
        GROUP BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    ) AS pk), ''),
    '], "columns": [',
    IFNULL((SELECT GROUP_CONCAT(
        CONCAT('{"schema":"', cast(cols.table_schema as CHAR),
               '","table":"', cols.table_name,
               '","name":"', REPLACE(cols.column_name, '"', '\\"'),
               '","type":"', LOWER(cols.data_type),
               '","character_maximum_length":"', IFNULL(cols.character_maximum_length, 'null'),
               '","precision":',
               IF(cols.data_type IN ('decimal', 'numeric'),
                  CONCAT('{"precision":', IFNULL(cols.numeric_precision, 'null'),
                         ',"scale":', IFNULL(cols.numeric_scale, 'null'), '}'), 'null'),
               ',"ordinal_position":', cols.ordinal_position,
               ',"nullable":', IF(cols.is_nullable = 'YES', 'true', 'false'),
               ',"default":"', IFNULL(REPLACE(REPLACE(cols.column_default, '\\\\', ''), '"', 'ֿֿֿ\\"'), ''),
               '","collation":"', IFNULL(cols.collation_name, ''),
               '","is_identity":', IF(cols.extra LIKE '%auto_increment%', 'true', 'false'),
               ',"comment":"', REPLACE(REPLACE(IFNULL(cols.column_comment, ''), '"', '\\"'), '\\n', ' '), '"}')
    ) FROM (
        SELECT cols.table_schema,
               cols.table_name,
               cols.column_name,
               LOWER(cols.data_type) AS data_type,
               cols.character_maximum_length,
               cols.numeric_precision,
               cols.numeric_scale,
               cols.ordinal_position,
               cols.is_nullable,
               cols.column_default,
               cols.collation_name,
               cols.extra,
               cols.column_comment
        FROM information_schema.columns cols
        WHERE cols.table_schema = DATABASE()
    ) AS cols), ''),
    '], "indexes": [',
    IFNULL((SELECT GROUP_CONCAT(
        CONCAT('{"schema":"', cast(idx.table_schema as CHAR),
               '","table":"', idx.table_name,
               '","name":"', idx.index_name,
               '","size":', IFNULL(
                    (SELECT SUM(stat_value * @@innodb_page_size)
                     FROM mysql.innodb_index_stats
                     WHERE stat_name = 'size'
                       AND index_name != 'PRIMARY'
                       AND index_name = idx.index_name
                       AND TABLE_NAME = idx.table_name
                       AND database_name = idx.table_schema), -1),
               ',"column":"', idx.column_name,
               '","index_type":"', LOWER(idx.index_type),
               '","cardinality":', idx.cardinality,
               ',"direction":"', (CASE WHEN idx.collation = 'D' THEN 'desc' ELSE 'asc' END),
               '","column_position":', idx.seq_in_index,
               ',"unique":', IF(idx.non_unique = 1, 'false', 'true'), '}')
    ) FROM (
        SELECT indexes.table_schema,
               indexes.table_name,
               indexes.index_name,
               indexes.column_name,
               LOWER(indexes.index_type) AS index_type,
               indexes.cardinality,
               indexes.collation,
               indexes.non_unique,
               indexes.seq_in_index
        FROM information_schema.statistics indexes
        WHERE indexes.table_schema = DATABASE()
    ) AS idx), ''),
    '], "tables":[',
    IFNULL((SELECT GROUP_CONCAT(
        CONCAT('{"schema":"', cast(tbls.TABLE_SCHEMA as CHAR),
               '","table":"', tbls.TABLE_NAME,
               '","rows":', IFNULL(tbls.TABLE_ROWS, 0),
               ',"type":"', IFNULL(tbls.TABLE_TYPE, ''),
               '","engine":"', IFNULL(tbls.ENGINE, ''),
               '","collation":"', IFNULL(tbls.TABLE_COLLATION, ''),
               '","comment":"', REPLACE(REPLACE(IFNULL(tbls.TABLE_COMMENT, ''), '"', '\\"'), '\\n', ' '), '"}')
    ) FROM (
        SELECT `TABLE_SCHEMA`,
               `TABLE_NAME`,
               `TABLE_ROWS`,
               `TABLE_TYPE`,
               `ENGINE`,
               `TABLE_COLLATION`,
               `TABLE_COMMENT`
        FROM information_schema.tables tbls
        WHERE tbls.table_schema = DATABASE()
    ) AS tbls), ''),
    '], "views":[',
    IFNULL((SELECT GROUP_CONCAT(
        CONCAT('{"schema":"', cast(vws.TABLE_SCHEMA as CHAR),
               '","view_name":"', vws.view_name,
               '","view_definition":"', view_definition, '"}')
    ) FROM (
        SELECT `TABLE_SCHEMA`,
               `TABLE_NAME` AS view_name,
               null AS view_definition
        FROM information_schema.views vws
        WHERE vws.table_schema = DATABASE()
    ) AS vws), ''),
    '], "database_name": "', DATABASE(),
    '", "version": "', VERSION(), '"}') AS CHAR) AS metadata_json_to_import
Always run SET SESSION group_concat_max_len = 10000000; before the query to prevent truncation of large result sets.

​
What the Smart Query Retrieves

The MariaDB Smart Query retrieves the same metadata as MySQL:
  • Foreign Keys: Full FK definitions with update/delete rules
  • Primary Keys: Single and composite primary keys
  • Columns: All data types, lengths, precision, defaults, and comments
  • Indexes: Index statistics from mysql.innodb_index_stats
  • Tables: Table information including storage engine and row counts
  • Views: View names and schemas

​
SQL Import/Export

MariaDB uses the same SQL import and export functionality as MySQL.

​
Import Features

MariaDB can import all MySQL-compatible DDL:
  • CREATE TABLE statements
  • All MariaDB/MySQL data types
  • AUTO_INCREMENT columns
  • Storage engines (InnoDB, MyISAM, Aria, etc.)
  • Foreign keys with referential actions
  • Indexes (BTREE, HASH, FULLTEXT)
  • Views
  • CHECK constraints (MariaDB 10.2.1+)

​
Export Example

START TRANSACTION;

CREATE DATABASE IF NOT EXISTS `app`;

CREATE TABLE IF NOT EXISTS `app`.`users` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL UNIQUE COMMENT 'User email address',
    `username` VARCHAR(100) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User accounts';

CREATE UNIQUE INDEX `idx_users_email` ON `app`.`users` (`email`);

ALTER TABLE `app`.`posts` ADD CONSTRAINT `fk_posts_user_id` 
    FOREIGN KEY(`user_id`) REFERENCES `app`.`users`(`id`);

COMMIT;

​
MariaDB-Specific Features

​
Storage Engines

MariaDB supports additional storage engines beyond MySQL:
  • InnoDB: Default transactional engine
  • Aria: Crash-safe MyISAM replacement
  • ColumnStore: Columnar storage for analytics
  • Spider: Sharding/partitioning engine
  • MyRocks: RocksDB-based engine
CREATE TABLE logs (...) ENGINE=Aria;
CREATE TABLE analytics (...) ENGINE=ColumnStore;

​
Enhanced CHECK Constraints

MariaDB has supported CHECK constraints since version 10.2.1: 
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2),
    quantity INT,
    CONSTRAINT chk_price CHECK (price >= 0),
    CONSTRAINT chk_qty CHECK (quantity >= 0)
);

​
Sequences

MariaDB 10.3+ supports sequences (similar to PostgreSQL): 
CREATE SEQUENCE seq_order_id START WITH 1000 INCREMENT BY 1;
ChartDB does not currently have dedicated sequence support, but you can use AUTO_INCREMENT for similar functionality.

​
JSON Data Type

MariaDB 10.2+ supports native JSON: 
metadata JSON
CHECK (JSON_VALID(metadata))

​
Key Differences from MySQL

MariaDB has full CHECK constraint support since 10.2.1, while MySQL only added it in 8.0.16.
MariaDB offers additional engines like Aria and ColumnStore that aren’t available in MySQL.
MariaDB often has better query optimization and performance for complex queries.
MariaDB is fully GPL-licensed, while MySQL has a dual license model (GPL + Commercial).

​
Best Practices

1

Use utf8mb4 character set

Always use utf8mb4 for full Unicode support including emojis
2

Choose InnoDB or Aria

Use InnoDB for transactional tables, Aria for non-transactional with better crash recovery than MyISAM
3

Leverage CHECK constraints

Use CHECK constraints for data validation at the database level
4

Add indexes for foreign keys

Unlike some databases, MariaDB doesn’t auto-index foreign key columns
5

Set group_concat_max_len

Always increase this setting before running the Smart Query

​
Troubleshooting

The Smart Query may be truncated for large databases. Increase the limit:
SET SESSION group_concat_max_len = 10000000;
Ensure your user has SELECT permissions on:
  • information_schema.*
  • mysql.innodb_index_stats
CHECK constraints require MariaDB 10.2.1+. For older versions, they are parsed but not enforced.
Like MySQL, MariaDB doesn’t support inline REFERENCES. Use table-level FOREIGN KEY constraints.

​
Compatibility Notes

  • SQL Syntax: MariaDB is highly compatible with MySQL syntax
  • Data Type Conversion: Use the same type mappings as MySQL
  • Import/Export: MariaDB can import MySQL dumps and vice versa (with some engine exceptions)
  • Smart Query: Works identically to MySQL with minor optimizations