Connect to MatrixOne with ODBC¶

MatrixOne provides an experimental ODBC driver, based on MySQL Connector/ODBC 9.7.0, for connecting ODBC applications and BI tools such as Power BI to MatrixOne. This guide covers installation, DSN and connection-string parameters, the default port 6001, diagnostics, and the current preview limitations.

Overview¶

MatrixOne ODBC is a preview, experimental fork of MySQL Connector/ODBC 9.7.0. It keeps the MySQL client protocol and registers two public driver names:

MatrixOne ODBC 9.7 Unicode Driver (myodbc9w)
MatrixOne ODBC 9.7 ANSI Driver (myodbc9a)

When PORT is omitted, the driver connects to MatrixOne’s MySQL-compatible listener port 6001. The driver reports MatrixOne through SQL_DBMS_NAME. In the v9.7.0-mo.3 release, only native authentication errors 1044 and 1045 are mapped to ODBC SQLSTATE 28000; do not assume that every MatrixOne authentication failure has this mapping.

Compatibility behavior¶

For MatrixOne, the driver also applies these catalog and SQL behaviors:

MatrixOne BOOL is reported as ODBC SQL_BIT in both catalog and result descriptors, while plain TINYINT remains SQL_TINYINT.
SQLColumns does not expose MatrixOne’s physical helper columns __mo_fake_pk_col and __mo_cpkey_col.
ODBC scalar function escapes such as {fn function(...)} are rewritten to plain function calls before they are sent to MatrixOne.

Before you start¶

You have completed Install and Start MatrixOne.
On Windows x64, the Visual C++ 2022 x64 runtime is required. The MSI installer checks for it.

Syntax¶

A DSN-less ODBC connection string uses semicolon-separated keyword=value pairs:

DRIVER={MatrixOne ODBC 9.7 Unicode Driver};SERVER=host;PORT=6001;DATABASE=db;UID=user;PWD=password;SSLMODE=DISABLED

You can also register a DSN and connect by name. On Windows, use the 64-bit ODBC Data Source Administrator or the Add-OdbcDsn cmdlet.

Arguments¶

Keyword	Required	Description
`DRIVER`	Yes	The registered driver name: `MatrixOne ODBC 9.7 Unicode Driver` or `MatrixOne ODBC 9.7 ANSI Driver`.
`SERVER`	Yes	MatrixOne host name or IP address.
`PORT`	No	MatrixOne listener port. Defaults to `6001` when omitted.
`DATABASE`	No	The database to open after connecting.
`UID`	No	The user name.
`PWD`	No	The password.
`SSLMODE`	No	TLS mode. Use `DISABLED` for MatrixOne’s default unencrypted local deployment, or `PREFERRED` to prefer TLS when the server offers it.

Examples¶

Connect without a DSN, using the default port 6001:

DRIVER={MatrixOne ODBC 9.7 Unicode Driver};SERVER=127.0.0.1;DATABASE=test;UID=root;PWD=111;SSLMODE=DISABLED

Create a 64-bit system DSN named MatrixOne on Windows:

Add-OdbcDsn -Name 'MatrixOne' -DriverName 'MatrixOne ODBC 9.7 Unicode Driver' -DsnType System -Platform '64-bit' -SetPropertyValue @("SERVER=127.0.0.1", "PORT=6001")

Installation¶

Windows x64¶

Download the Windows x64 MSI or portable ZIP from the MatrixOne ODBC GitHub Releases. The MSI installs:

The Unicode and ANSI drivers.
The bundled client runtime DLLs and authentication plugins (libmysql.dll, OpenSSL, Kerberos, and SASL).
The Power BI connector MatrixOne.mez.

A default install writes the connector to both Documents\Power BI Desktop\Custom Connectors and Documents\Microsoft Power BI Desktop\Custom Connectors. Customers do not need the MySQL SDK or a MySQL Server installation. The MSI installs into a separate Matrix Origin\MatrixOne ODBC directory so it does not upgrade or uninstall an Oracle MySQL ODBC installation. Each -mo.N package has a distinct MSI product version (9.7.N) while the ODBC driver version remains 9.7.0.

The v9.7.0-mo.3 MSI and Unicode/ANSI driver DLLs are not Authenticode-signed. This can affect UAC prompts, AppLocker, enterprise software distribution, and security approval. MatrixOne.mez is an unsigned Power Query extension, but Authenticode is not the MEZ trust model; Power Query’s signed/trusted connector distribution uses PQX. Verify downloads against the versioned SHA256SUMS.txt; the MSI SHA-256 is 203c0ba7866333a9f1094dcbd960f48e8e77082267fa1e56706a482af23d3543.

For the portable ZIP, open an Administrator terminal in the extracted directory and run the bundled Install.bat. It locates bin\myodbc-installer.exe and registers both the ANSI and Unicode drivers. System-driver registration requires administrator privileges.

For manual registration, use resolved absolute paths rather than assuming that myodbc-installer.exe is in the ZIP root or on PATH:

$Root = (Resolve-Path '.').Path
$Installer = Join-Path $Root 'bin\myodbc-installer.exe'
$Lib = Join-Path $Root 'lib'
& $Installer -d -a -n 'MatrixOne ODBC 9.7 ANSI Driver' -t "DRIVER=$Lib\myodbc9a.dll;SETUP=$Lib\myodbc9S.dll"
& $Installer -d -a -n 'MatrixOne ODBC 9.7 Unicode Driver' -t "DRIVER=$Lib\myodbc9w.dll;SETUP=$Lib\myodbc9S.dll"

macOS ARM64¶

Build and register the v9.7.0-mo.3 source. The command follows the release CI and discovers Homebrew paths instead of assuming /opt/homebrew:

git clone --branch v9.7.0-mo.3 --depth 1 https://github.com/matrixorigin/matrixone-odbc.git
cd matrixone-odbc
brew install cmake mysql-client ninja unixodbc

MYSQL_PREFIX="$(brew --prefix mysql-client)"
ODBC_PREFIX="$(brew --prefix unixodbc)"
OPENSSL_PREFIX="$(brew --prefix openssl@3)"
ZSTD_PREFIX="$(brew --prefix zstd)"
INSTALL_PREFIX="$HOME/.local/matrixone-odbc-9.7.0-mo.3"

cmake -S . -B build-macos -G Ninja \
  -DCMAKE_BUILD_TYPE=RelWithDebInfo \
  -DDISABLE_GUI=1 \
  -DWITH_UNIXODBC=1 \
  -DMYSQL_DIR="$MYSQL_PREFIX" \
  -DODBC_INCLUDES="$ODBC_PREFIX/include" \
  -DODBC_LIB_DIR="$ODBC_PREFIX/lib" \
  -DODBCINST_LIB_DIR="$ODBC_PREFIX/lib" \
  -DMYSQLCLIENT_STATIC_LINKING=0 \
  -DMYSQL_LINK_FLAGS="-L$ZSTD_PREFIX/lib -L$OPENSSL_PREFIX/lib" \
  -DWITH_PBI=1
cmake --build build-macos
cmake --install build-macos --prefix "$INSTALL_PREFIX"

Register both drivers with unixODBC. odbcinst -j shows the active configuration files; use sudo for the registration command only if that reported odbcinst.ini is not writable by your account.

DRIVER_TEMPLATE="$(mktemp)"
cat > "$DRIVER_TEMPLATE" <<EOF
[MatrixOne ODBC 9.7 Unicode Driver]
Description=MatrixOne ODBC 9.7 Unicode Driver
Driver=$INSTALL_PREFIX/lib/libmyodbc9w.so

[MatrixOne ODBC 9.7 ANSI Driver]
Description=MatrixOne ODBC 9.7 ANSI Driver
Driver=$INSTALL_PREFIX/lib/libmyodbc9a.so
EOF
odbcinst -i -d -f "$DRIVER_TEMPLATE"
odbcinst -q -d

Confirm that MatrixOne ODBC 9.7 Unicode Driver appears in the output, then test the first connection:

The following short-lived smoke command expands the password into the process argument list, where another local user or process monitor may observe it. Use it only on a controlled host, then clear the variable; for production testing, use a protected DSN/credential mechanism instead.

export MO_PASSWORD='your-password'
iusql -v "DRIVER={MatrixOne ODBC 9.7 Unicode Driver};SERVER=127.0.0.1;PORT=6001;DATABASE=test;UID=root;PWD=${MO_PASSWORD};SSLMODE=DISABLED"
unset MO_PASSWORD

Diagnostics¶

When a connection fails, record the ODBC SQLSTATE and the native error. The driver maps the following errors for MatrixOne:

Condition	SQLSTATE
Native authentication error 1044 or 1045	`28000`
Unable to establish a connection (unknown host, socket, or host errors)	`08001`
Missing table	`42S02`

Enable Windows ODBC tracing from ODBC Data Sources (64-bit) > Tracing only for a short reproduction, because it records connection activity and may include sensitive values. Do not commit passwords or raw traces without redaction.

Known limitations¶

This section records limitations from the tagged MatrixOne ODBC v9.7.0-mo.3 test suite and Windows installation report (driver commit 5cc5e1297f93809b1ea7352a1881b67c22b3a3ff). These inherited reports do not establish that every case was rerun against the exact MatrixOne v4.2.0 commit 01ae8eca88110dc29d2f51e421570bb3d850d328. A GitHub issue being closed on a newer branch does not mean its fix is present in the tested release baseline.

Limitation	Affected version	Fix status
An unknown user can return `HY000`/20101 instead of `28000`; unknown-database mapping is also incomplete.	ODBC `v9.7.0-mo.3`	Fixed after the `mo.3` assets in matrixone-odbc#22; not included in `v9.7.0-mo.3`.
utf8mb4 `VARCHAR` reports a reduced ODBC `ColumnSize`.	Tagged `mo.3` compatibility report; matrixone#26967	XFAIL in the tagged report; the issue was closed later.
`OFFSET` without `LIMIT` is rejected, limiting Power Query pagination folding.	Tagged `mo.3` compatibility report; matrixone#26769	XFAIL in the tagged report; the issue was closed later.
`sql_select_limit` is ignored, so `SQL_ATTR_MAX_ROWS` does not limit rows.	Tagged `mo.3` compatibility report; matrixone#27035	XFAIL in the tagged report; the issue was closed later.
A missing column returns `HY000`/20301 instead of `42S22`.	Tagged `mo.3` compatibility report; matrixone#27024	XFAIL in the tagged report; the issue was closed later.
`max_execution_time` is not enforced, so query timeouts do not reliably terminate queries.	Tagged `mo.3` compatibility report; matrixone#26678	XFAIL in the tagged report; the issue was closed later.
Unquoted Unicode identifiers are rejected.	Tagged `mo.3` compatibility report; matrixone#26715	XFAIL in the tagged report; the issue was closed later.
Parameter-array `SELECT` result mapping can associate returned rows with the wrong parameter sets. Generic ODBC applications remain affected even though the Power BI connector disables parameter binding.	Tagged `mo.3` deep test and Windows installation report; matrixone#27034	KnownIssue/XFAIL in the tagged reports; the issue was closed later. Verify a server fix or backport before enabling parameter binding.
`PAD_CHAR_TO_FULL_LENGTH` is accepted but `CHAR` values remain unpadded over ODBC.	Tagged `mo.3` compatibility report; matrixone#27036	XFAIL in the tagged report; issue remains open.

Later-main issue state is reported separately from the exact-release result; a closed issue does not turn an mo.3 XFAIL into a pass without a verified backport. The Power BI connector shipped with mo.3 disables parameter binding; the mo.3 ODBC driver itself does not. matrixone#27640 was reproduced with a newer MatrixOne main and ODBC PR #24, so this page does not claim that exact combination for mo.3.

On-premises data gateway refresh, signed custom connector policy, TLS certificate verification modes, and interactive MSI install/repair/uninstall on a clean Windows VM have not yet been validated for this release.