MariaDB

Write data to a MariaDB table using INSERT ON DUPLICATE KEY UPDATE for upsert. Use Zeotap to materialize model results, enriched profiles, or audience membership into MariaDB.

Prerequisites

• A MariaDB database (version 10.3+)
• A database user with INSERT, UPDATE, DELETE, and CREATE TABLE privileges
• Network connectivity between Zeotap and the MariaDB server

Authentication

MariaDB uses Username & Password authentication.

1. Enter the database Username and Password in Zeotap

Configuration

Target Settings

Supported Operations

Sync Modes

Audience Sync Modes

Features

• Field Mapping: Yes
• Schema Introspection: Yes — Zeotap reads column metadata from MariaDB’s information_schema
• Auto-create Target Table: Yes — Zeotap creates the table automatically on the first sync if it does not exist

How It Works

1. Zeotap opens a connection to MariaDB using the configured host, port, database, and credentials
2. A temporary staging table is created for each batch
3. Rows are inserted into the staging table with their diff operation (added, changed, removed) and primary key
4. Based on the sync mode, Zeotap applies the appropriate SQL operations:
   • Upsert: INSERT ... ON DUPLICATE KEY UPDATE for added and changed rows
   • Insert: INSERT for added rows only
   • Update: UPDATE ... INNER JOIN for changed rows
   • Mirror: Upsert for added/changed rows, then DELETE ... INNER JOIN for removed rows
5. The staging table is dropped after each batch
6. Missing columns are automatically added to the target table as TEXT columns

Rate Limits

MariaDB does not impose API rate limits. Performance depends on the server’s hardware, network bandwidth, and concurrent workload. For large syncs, consider:

• Increasing max_allowed_packet for large batch inserts
• Ensuring adequate innodb_buffer_pool_size for write-heavy workloads
• Using connection pooling if running frequent syncs

Best Practices

• Use a dedicated database user with only the privileges required (INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE)
• Enable TLS when connecting over public networks or cloud-hosted instances
• Define a primary key on the target table before the first sync to enable upsert and update modes
• Monitor disk space on the MariaDB server, especially for mirror syncs that may grow the table over time
• Use InnoDB tables (the default storage engine in MariaDB 10.2+) for transactional consistency

Troubleshooting

Connection refused

Verify the host, port, and that the MariaDB server allows remote connections. Check the bind-address setting in the MariaDB configuration file (/etc/mysql/mariadb.conf.d/50-server.cnf).

Access denied

Verify the username and password. Ensure the user has the required privileges on the target database. You can verify with:

SHOW GRANTS FOR 'your_user'@'your_host';

Table does not exist

Zeotap creates the target table automatically on the first sync. Ensure the user has CREATE TABLE privileges on the target database.

TLS connection issues

If the server requires TLS, enable the Enable TLS toggle. MariaDB cloud-hosted instances (SkySQL, AWS RDS for MariaDB) typically require TLS for all connections.

Packet too large

If you see Error 1153: Got a packet bigger than 'max_allowed_packet' bytes, increase the max_allowed_packet setting on the MariaDB server.

Duplicate column name errors

Zeotap automatically adds missing columns to the target table. If the column already exists with a different type, the sync will continue without modification. To change a column type, alter it manually in MariaDB before the next sync.