MariaDB
This documentation describes the integration of MindsDB with MariaDB, one of the most popular open source relational databases. The integration allows MindsDB to access data from MariaDB and enhance MariaDB with AI capabilities.
Prerequisites
Before proceeding, ensure the following prerequisites are met:
- Install MindsDB locally via Docker or Docker Desktop.
- To connect MariaDB to MindsDB, install the required dependencies following this instruction.
Connection
Establish a connection to MariaDB from MindsDB by executing the following SQL command and providing its handler name as an engine.
CREATE DATABASE mariadb_conn
WITH ENGINE = 'mariadb',
PARAMETERS = {
"host": "host-name",
"port": 3307,
"database": "db-name",
"user": "user-name",
"password": "password"
};
Or:
CREATE DATABASE mariadb_conn
WITH
ENGINE = 'mariadb',
PARAMETERS = {
"url": "mariadb://user-name@host-name:3307"
};
Required connection parameters include the following:
user
: The username for the MariaDB database.password
: The password for the MariaDB database.host
: The hostname, IP address, or URL of the MariaDB server.port
: The port number for connecting to the MariaDB server.database
: The name of the MariaDB database to connect to.
Or:
url
: You can specify a connection to MariaDB Server using a URI-like string, as an alternative connection option. You can also usemysql://
as the protocol prefix
Optional connection parameters include the following:
ssl
: Boolean parameter that indicates whether SSL encryption is enabled for the connection. Set to True to enable SSL and enhance connection security, or set to False to use the default non-encrypted connection.ssl_ca
: Specifies the path to the Certificate Authority (CA) file in PEM format.ssl_cert
: Specifies the path to the SSL certificate file. This certificate should be signed by a trusted CA specified in thessl_ca
file or be a self-signed certificate trusted by the server.ssl_key
: Specifies the path to the private key file (in PEM format).
Usage
The following usage examples utilize the connection to MariaDB made via the CREATE DATABASE
statement and named mariadb_conn
.
Retrieve data from a specified table by providing the integration and table name.
SELECT *
FROM mariadb_conn.table_name
LIMIT 10;
Troubleshooting
Database Connection Error
- Symptoms: Failure to connect MindsDB with the MariaDB database.
- Checklist:
- Ensure that the MariaDB server is running and accessible
- Confirm that host, port, user, and password are correct. Try a direct MySQL connection.
- Test the network connection between the MindsDB host and the MariaDB server.
SQL statement cannot be parsed by mindsdb_sql
- Symptoms: SQL queries failing or not recognizing table names containing spaces, reserved words or special characters.
- Checklist:
- Ensure table names with spaces or special characters are enclosed in backticks.
- Examples:
- Incorrect: SELECT * FROM integration.travel data
- Incorrect: SELECT * FROM integration.‘travel data’
- Correct: SELECT * FROM integration.`travel data`