Search docs.molo17.com
    Configuration guide Connectors

    Set up a SingleStore target with bulk loads

    SingleStore spreads every table over its leaf nodes by a shard key, and it stores a table as a rowstore or as a columnstore. Both choices decide how Gluesync can write to the table. This guide creates a SingleStore user with only the grants Gluesync needs, chooses the table type, the shard key and the unique key, connects the target, copies a PostgreSQL table with standard writes and with bulk load, follows live changes, and shows the errors you are most likely to meet on a new setup.

    In this guide

    You will create a database and a user with least grants, including the separate GLUESYNC schema that the bulk path needs. You will create a columnstore table and a rowstore table, and learn the rule that links the shard key to the unique key. You will connect the SingleStore target, add one entity that Gluesync creates the table for and one that uses a table you created, run a snapshot with standard writes, repeat it with bulk load and compare the time, and follow inserts, updates and deletes with CDC. A table at the end lists the errors that were reproduced for this guide.

    Quick information

    Goal

    Replicate a PostgreSQL table to SingleStore with a snapshot and with bulk load, then keep it in sync with change data capture (CDC)

    Audience

    Data engineers and database administrators who own a SingleStore cluster

    Level

    Intermediate

    Time required

    About 45 minutes

    Area

    SingleStore → SQL; Control Plane → Pipelines → SingleStore target → Base settings and Advanced settings; entity → start options and Edit settings

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have a SingleStore database that Core Hub can reach on port 3306 (the MySQL protocol port), and you can run SQL on it as an administrator. SingleStore is a target only: you need another database as the source. This guide was tested with the singlestoredb-dev container image;

    2. You have a source that Core Hub can read with CDC. This guide uses PostgreSQL 16 with wal_level = logical, set up as in Set up a PostgreSQL WAL source;

    3. You can sign in to the Control Plane with a role that can create pipelines;

    4. You have source tables with a primary key. This guide uses a PostgreSQL table public.events with 200,000 rows (id bigint primary key, kind varchar(20), val integer, amount numeric(12,2), created_at timestamp, note text) and a table public.readings with 1,000 rows (id bigint primary key, device integer, taken_at timestamp(6), temp double precision, status varchar(10)).

    The statements and the messages quoted in this guide were run and captured against a SingleStore server and the Control Plane that ships with Gluesync 2.3. The (conn=<number>) part of a message is the connection id of your server and changes from run to run.

    Guide steps

    Create the SingleStore user and grant what the target needs

    SQL

    Gluesync does not need an administrator. Connect to SingleStore as an administrator and run the statements below. Replace the database name and the password.

    1. Create the target database, the user, and the grants on the target database:

      CREATE DATABASE demo;
CREATE USER 'gluesync'@'%' IDENTIFIED BY '<a strong password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON demo.* TO 'gluesync'@'%';

      These four grants are enough to write, update and delete rows, and to empty a table before a snapshot, in tables that already exist. Add CREATE on the database if you want Gluesync to create the target tables for you (step 4).

    2. Create the schema that the bulk path uses for its staging tables, and grant on it. The agent calls it GLUESYNC and creates it when it is missing, but a user without the global CREATE privilege cannot create a database, so you create it:

      CREATE DATABASE GLUESYNC;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON GLUESYNC.* TO 'gluesync'@'%';

      CREATE lets the agent create one staging table for each entity that has bulk operations. DROP lets it remove that table when you delete the entity. Without DROP everything else works, and the staging table stays in GLUESYNC after you delete the entity. If you do not use bulk operations, you do not need this schema. To use another name, set Gluesync schema name in step 3.

    3. Check the grants:

      SHOW GRANTS FOR 'gluesync'@'%';
    Expected result

    The output lists GRANT USAGE ON *.* and the two grant lines, one for demo with SELECT, INSERT, UPDATE, DELETE and one for GLUESYNC with SELECT, INSERT, UPDATE, DELETE, CREATE, DROP.

    If a grant is missing, the setup fails with a specific message. The troubleshooting table at the end lists them.

    Choose rowstore or columnstore, the shard key and the unique key

    SQL

    SingleStore keeps a table either in memory as a rowstore or on disk as a columnstore. As a rule of thumb, a columnstore suits large tables that you scan and aggregate, and a rowstore suits small tables with many single-row reads and updates. Gluesync writes to both.

    Two rules matter for Gluesync:

    • A standard write is an upsert: INSERT …​ ON DUPLICATE KEY UPDATE. It updates a row only if the table has a unique key on the entity key. Without one, every update adds a new row;

    • SingleStore only accepts a unique key that contains every column of the shard key. A table with a primary key and no explicit shard key is sharded by the primary key, which always satisfies the rule.

      1. Create a columnstore table with a sort key for range reads, and a rowstore table. Leave out CREATE ROWSTORE and the table is a columnstore if default_table_type is columnstore, which is the default of the container image used here:

        CREATE TABLE demo.readings (
    id BIGINT NOT NULL,
    device INT,
    taken_at DATETIME(6),
    temp DOUBLE,
    status VARCHAR(10),
    SORT KEY (taken_at),
    SHARD KEY (id),
    PRIMARY KEY (id)
);

CREATE ROWSTORE TABLE demo.orders_small (
    id INT NOT NULL,
    customer VARCHAR(50),
    total DECIMAL(10,2),
    PRIMARY KEY (id)
);

      2. Check what SingleStore made of the first table:

        SHOW CREATE TABLE demo.readings\G

      3. See the shard key rule fail, so that you recognize the message. This statement shards by customer, which the primary key id does not contain:

        CREATE TABLE demo.bad (
    id INT NOT NULL,
    customer VARCHAR(10),
    PRIMARY KEY (id),
    SHARD KEY (customer)
);
    Expected result

    SHOW CREATE TABLE shows a primary key on id, a shard key on id (named __SHARDKEY) and the sort key taken_at. The last statement fails with error 1744: The unique key named: 'PRIMARY(id)' cannot be created because unique keys must contain all columns of the shard key '(customer)'. The same statement with CREATE ROWSTORE TABLE fails the same way.

    If the primary key must be wider than the shard key, put the shard key columns into the key: PRIMARY KEY (id, customer) with SHARD KEY (id) is accepted. Keep every column of the entity key in the unique key, so that the upsert finds the row.
    Create the pipeline and the SingleStore target connection

    UI

    1. In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

    2. In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings: Host name / Address, Port, Database name, Username and Password of your source database. Select Test and continue. The source appears with a green check mark. Select Next;

    3. In 2. Setup target(s), choose the SingleStore agent tile and fill Base settings:

      • Host name / Address: your SingleStore aggregator;

      • Port: 3306;

      • Database name: demo;

      • Username and Password: the user of step 1;

    4. Select Test and continue. The target appears with a green check mark. Select Next, enter a Pipeline name and select Finish.

    Edit target page of the SingleStore agent with host name
    Figure 1. The SingleStore target connection. Under Advanced connection properties, the key Gluesync schema name (necessary for bulk operations) holds the name of the staging schema, GLUESYNC by default.

    The connection form has three more places for settings:

    • Under Advanced connection properties, select Add property to set Use connection pool, Gluesync schema name (necessary for bulk operations) and Disable automatic schema creation. With the last one on, the agent checks that the schema exists and refuses to start a bulk entity when it does not, instead of trying to create it;

    • In the Advanced settings tab, select Add property to set Load balancing mode, Use server prepared statements and Disable truncate on target table before snapshot inserts;

    • Connection string, Min connections count (at least 3) and Max connections count are under Settings. The pool defaults to 5 and 25 connections.

    Expected result

    The pipeline appears in the HEALTHY group of the pipeline list, with the PostgreSQL and the SingleStore icons. Test and continue also succeeds before the GLUESYNC schema exists: the schema is only needed when an entity starts with bulk operations.

    For TLS and the REST API setup, see the SingleStore target reference.

    Add an entity and let Gluesync create the table

    UI

    1. In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the source schema public and select the table events. The Field mapping dialog opens;

    2. Under Target, choose the Target Schema demo. In Target Table type events and choose Create table "events". The dialog shows New table events will be created on confirm. and the proposed target types;

    3. Fix the proposals that SingleStore cannot use:

      • The note column is text and its Length is 0. The dialog shows the error Missing required field and Confirm stays disabled. Enter a length, for example 1000;

      • The proposal for a timestamp with time zone column is datetimeoffset, which SingleStore does not have. Change the type to datetime. The dialog marks the column with a yellow warning, Data type mismatch detected, and the value is stored as UTC;

      • A bytea column is proposed as varbinary without a length, which SingleStore rejects. Change the type to blob and enter a length such as 65535;

    4. Select Confirm. The Create table events dialog shows the Read-only SQL statement. Read it, then select Create table events to run it on SingleStore;

    5. Back on the Add entities page, select Confirm and continue.

    The statement for the events table has this form:

    CREATE TABLE `demo`.`events` (
   id bigint,
   kind varchar(20),
   val int,
   amount numeric(12, 2),
   created_at datetime,
   note text,
   PRIMARY KEY (
      id
   )
)

    It has no ROWSTORE keyword, so the table has the default table type of your server. Check what SingleStore made of it:

    SHOW CREATE TABLE demo.events\G
    Expected result

    The entity appears in the pipeline on Hold as public.events. The table has a primary key on id, a shard key on id (named __SHARDKEY) and an empty sort key (named __UNORDERED): the primary key became the shard key, and the table has no sort key. To get another sort key or shard key, create the table yourself as in step 2 and choose it in Target Table.

    See Target table creation for the dialog.

    Add an entity that uses a table you created

    UI

    1. In the pipeline, select Entities and then, in the Object browser, the table readings. In the Field mapping dialog choose the Target Schema demo, and in Target Table choose the existing table readings. The mapping loads the types from SingleStore. Confirm and Confirm and continue as before;

    2. Do the same for orders_small if you created it in step 2.

    Field mapping dialog of the readings table with the target table readings
    Figure 2. The mapping to an existing columnstore table. The target types come from the table: datetime (6) and the primary key on id.

    A target table without a unique key on the entity key gets a warning in the same dialog. Create a table without a key to see it:

    CREATE TABLE demo.ledger (id INT, memo VARCHAR(40), amount DECIMAL(10,2), SHARD KEY (id));
    Field mapping dialog of a table with the warning The target has logical keys only: its upsert needs a unique index on them
    Figure 3. The warning for a target table that has logical keys only.
    Expected result

    Both entities appear on Hold. The mapping of readings has no error. The mapping of ledger shows the yellow banner The target has logical keys only: its upsert needs a unique index on them, and you can still confirm it. Remove the ledger entity if you do not want to keep it. The troubleshooting table explains what the warning costs you.

    Run a snapshot with standard writes

    UI + SQL

    Standard writes use JDBC batches. This is the default, and it is the baseline for the next step.

    1. In the pipeline, find the entity public.events. On Hold, select the start options button, the one next to the play button. The Snapshot (copy-only) dialog opens;

    2. Keep Keep and merge under Data already in the target and Standard under Speed. The summary shows 2 writers · standard writes. Select Start snapshot.

    Check the rows in SingleStore:

    SELECT count(*) FROM demo.events;
SELECT * FROM demo.events WHERE id IN (1, 200000);
    Expected result

    The count equals the source count, 200000. In the lab, this snapshot took about 40 seconds with two writers. The write method is UPSERT, an INSERT …​ ON DUPLICATE KEY UPDATE for each row.

    A timestamp(6) source column arrives with whole seconds: the value 2026-11-09 16:41:19.988552 is stored as 2026-11-09 16:41:19. This holds for the snapshot, for CDC and for bulk load, and for a column you created as DATETIME(6) too, which then shows .000000.
    Switch to bulk load and compare

    UI + SQL

    Bulk load writes the rows to a CSV file in Core Hub, loads the file into a staging table with LOAD DATA LOCAL INFILE, and applies it to the target table in one step. The staging table lives in the GLUESYNC schema. Bulk operations are a setting of each entity, and the snapshot and live changes are separate.

    1. On Hold, select the start options button of public.events again;

    2. Under Data already in the target select Empty the table first. Under Speed select Custom;

    3. In the Writing column, under During the copy select Bulk load, and under During live changes select Bulk load. The diagram shows the Bulk lane into a Staging table, and the field Writers during the copy turns off: bulk load writes in one stream;

    4. Tick I understand that rows in the target will be deleted and select Start snapshot. The settings are kept for the next start.

    Snapshot copy-only dialog with Speed Custom and Writing with During the copy and During live changes both set to Bulk load
    Figure 4. The Writing column of the Custom speed: bulk load for the copy and for live changes.

    Check the rows and the staging table:

    SELECT count(*) FROM demo.events;
SHOW TABLES IN GLUESYNC;
SHOW CREATE TABLE GLUESYNC.<staging table>\G

    Then read the Core Hub log for the load:

    docker logs <core hub container> 2>&1 | grep -E "LOAD DATA returned|Executing merge"
    Expected result

    The count is 200000 again. In the lab, the same 200,000 rows took about 4 seconds, including the truncate, against about 40 seconds with standard writes. The log shows LOAD DATA returned: 200000 rows and Executing merge, delete, insert operations.

    SHOW TABLES IN GLUESYNC lists one staging table for each bulk entity. Its name is de_ followed by the first three letters of the table name and the entity id, for example de_eve_<entity id>. The staging table holds the columns of the table, an <key>_OLD column for each key column, a <column>_UP flag for each other column and an OPERATION column. It has no shard key, so it is spread over the leaves without a key. The setup log shows Creating staging table: …​ for each of them.

    Two entities that write into the same target table each get their own staging table.

    See Bulk load for how it works for every target.

    Capture live changes and read the results

    UI + SQL

    The copy-only snapshot leaves the entity on Hold. Start it to follow the changes, then change the source and read the target.

    1. Select the play button of the entity. The Sync changes (CDC) dialog opens. Under Start from choose Now, because the rows are already in the target. The Writing column shows Bulk load under During live changes. Select Start entity. The entity becomes Active;

    2. On the source, insert, update and delete one row each:

      INSERT INTO events VALUES (200001, 'new', 1, 1.5, now(), 'inserted');
UPDATE events SET val = -1, note = 'updated' WHERE id = 5;
DELETE FROM events WHERE id = 6;

    3. After a few seconds, read the three keys and look for duplicates on SingleStore:

      SELECT id, kind, val, note FROM demo.events WHERE id IN (5, 6, 200001) ORDER BY id;
SELECT count(*) FROM demo.events;
SELECT count(*) FROM (SELECT id FROM demo.events GROUP BY id HAVING count(*) > 1) d;
    Expected result

    The first query returns the row 5 with val -1 and the note updated, and the row 200001, and no row 6. The count is 200000: 200,000 rows, one more and one less. The last query returns 0. The log shows one flush of 3 rows: LOAD DATA returned: 3 rows, then Executing merge, delete, insert operations.

    With bulk operations during CDC, an update is not an UPDATE statement. The agent loads the change into the staging table, deletes the row from the target table by its key and inserts the new version. This is also why bulk load works on a table that has no unique key: the delete finds the row by its key, whatever the table has. In the lab, three rows with the same key that standard writes had left in a table without a key collapsed to one row at the next bulk update. The agent reference explains the effect on columns that Gluesync does not write, in Updates with bulk operations during CDC.
    Fix the errors you are most likely to meet

    SQL + UI

    Each error below was reproduced by removing one grant or changing one setting. The troubleshooting table at the end lists them with the cause. The most common three:

    1. Delete the GLUESYNC schema (or the grants on it) and start an entity with bulk operations. A toast shows Entity Upsert Failed with (conn=<number>) Access denied for user 'gluesync'@'%' to database 'GLUESYNC'. Fix: run the statements of step 1.2;

    2. Remove CREATE from the target database and select Create table. The toast shows Create Target Table with Create target table execution error: (conn=<number>) CREATE command denied to user 'gluesync'@'%' for table 't_new'. Fix: grant CREATE on the database, or create the table yourself and choose it in Target Table;

    3. Map a timestamp with time zone column to datetimeoffset and select Create table. The dialog shows Failed to create table and the toast shows You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datetimeoffset,. Fix: change the type to datetime.

    Create table t_new dialog with the message Failed to create table and a toast Create Target Table with the text CREATE command denied to user gluesync for table t_new
    Figure 5. The dialog and the toast when the user cannot create tables.
    Expected result

    After each fix, select Create table or start the entity again. No restart of Core Hub is needed.

    Data types

    The proposal in the Field mapping dialog for a PostgreSQL source is below. The agent reference lists the alternatives for each Gluesync type in SingleStore target. Values were read back from SingleStore after a snapshot with standard writes.

    Source type SingleStore type Note

    smallint, integer, bigint

    smallint, int, bigint

    Values written unchanged

    numeric(p,s)

    numeric(p,s) (stored as decimal(p,s))

    Keep the precision and the scale of the source

    real, double precision

    float, double

    boolean

    bit

    Stored as bit(1): true reads as 1 when you cast it with c_bool + 0

    date, time

    date, time

    timestamp

    datetime

    Whole seconds only, see the note in step 6

    timestamp with time zone

    datetimeoffset is proposed and is invalid. Use datetime

    The instant is stored as UTC. With bulk load, a column of this type makes the bulk write fail and fall back, see the troubleshooting table

    varchar(n), char(n)

    varchar(n)

    A char(5) value ab is stored as ab with three trailing spaces

    text

    text

    The dialog needs a length for it. An existing text column shows the length of the column

    bytea

    varbinary is proposed. Use blob with a length

    The bytes are kept: \xdeadbeef reads back as DEADBEEF

    json, jsonb

    varchar

    The text is kept, with the spacing of jsonb. The mapping dialog lists a json type too

    uuid

    varchar

    Use a length of 36

    The target dialog also offers tinyint, mediumint, year, tinytext, mediumtext, longtext, tinyblob, mediumblob, longblob, enum, set, geography and geometry.

    Bulk and throughput settings

    Setting Default When to change it

    Speed in the start dialog: Standard, Faster, Custom

    Standard

    Custom gives separate write methods for the copy and for live changes, and the number of writers

    Writers during the copy (standard writes)

    2

    Between 1 and 10. More writers copy faster and load the SingleStore aggregator more. Not used with bulk load

    Bulk load during the copy and during live changes

    Off

    Turn it on for large tables and for sustained change rates. See the numbers in step 7

    Max Write Batch Size (entity settings)

    500

    The upper limit of rows in one batch of standard writes

    Max Delete Batch Size (entity settings)

    1000

    The upper limit of rows in one delete batch

    Max Write Bulk Size (entity settings)

    250000

    The upper limit of rows in one bulk file

    Min connections count, Max connections count

    5, 25

    Raise the maximum if several entities write at the same time

    Disable truncate on target table before snapshot inserts

    Off

    Turn it on if the agent must not truncate the target table before the snapshot inserts

    The entity settings are under the ⋮ menu of the entity, Edit settings, then Entity. Snapshot writing concurrency and the bulk switches are under Fine tuning. Read Tune replication throughput for the settings that apply to every target. TLS for the connection is in Enable HTTPS and TLS to databases.

    Verify the result

    You have set up the SingleStore target if:

    • The user has SELECT, INSERT, UPDATE, DELETE on the target database, and CREATE on the database only if Gluesync creates tables;

    • With bulk operations, the GLUESYNC schema exists and the user can create tables in it;

    • Every target table has a unique key on the entity key, and the key contains the shard key, or you use bulk load on a table without one;

    • The row count of the target equals the source after the snapshot;

    • A CDC insert, update and delete reach the target, and no key is duplicated;

    • The log shows LOAD DATA returned for the entities that you set to bulk load.

    Troubleshooting

    Problem Possible cause What to do

    Failed to save with The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: Failed to initialize pool: (conn=<number>) Access denied for user 'gluesync'@'<address>' (using password: YES)

    The password is wrong, or the user is not allowed from the address of Core Hub

    Fix the password. Create the user for the host that Core Hub connects from, or '%'

    Failed to save with Failed to initialize pool: (conn=<number>) Access denied for user 'gluesync'@'%' to database '<database>'

    The database does not exist, or the user has no grant on it

    Create the database and run the grants of step 1

    Failed to save with Failed to initialize pool: Socket fail to connect to host:address=(host=<name>)(port=3306). <name>

    Core Hub cannot resolve or reach the host or the port

    Check the host name, the port and the firewall between Core Hub and SingleStore

    Entity Upsert Failed: (conn=<number>) Access denied for user 'gluesync'@'%' to database 'GLUESYNC'

    An entity has bulk operations, and the GLUESYNC schema does not exist or the user has no grant on it. A user without the global CREATE privilege cannot create the schema

    Create the schema and run the grants of step 1.2. To use another schema, set Gluesync schema name

    Create Target Table: Create target table execution error: (conn=<number>) CREATE command denied to user 'gluesync'@'%' for table '<table>'

    The user has no CREATE on the target database

    Grant CREATE on the database, or create the table yourself and map to it

    Failed to create table with You have an error in your SQL syntax …​ near 'datetimeoffset,

    The mapping proposes datetimeoffset for a timestamp with time zone column

    Change the target type to datetime

    Failed to create table with You have an error in your SQL syntax …​ near ', after a varbinary column

    A bytea column is proposed as varbinary without a length

    Change the type to blob and enter a length

    Missing required field in the Field mapping dialog, and Confirm is disabled

    The Length of a text, blob, varchar or similar column is 0

    Enter a length

    The yellow banner The target has logical keys only: its upsert needs a unique index on them, and updates add rows

    The target table has no unique key on the entity key. With standard writes, INSERT …​ ON DUPLICATE KEY UPDATE has no key to match: in the lab, two updates of one key left three rows with that key

    Create the table with a primary key or a unique key that contains the shard key. Or turn on bulk load for live changes: it deletes by the key and inserts. See Choose keys for tables without a primary key

    The unique key named: 'PRIMARY(id)' cannot be created because unique keys must contain all columns of the shard key '(customer)' (error 1744) when you create a table

    The shard key has a column that the primary key or the unique key does not contain

    Add the column to the key, or shard by a column of the key

    The log shows Bulk insert failed for entity <name> (<id>), falling back to the batch write: java.sql.SQLException: (conn=<number>) Leaf Error (<address>): Invalid DATE/TIME in type conversion for column '<column>'

    The column is a timestamp with time zone that you mapped to datetime. The bulk file carries the offset, which datetime does not accept. The load is not lost: the agent falls back to standard writes for that flush

    Use standard writes for an entity that has such a column, or keep the column out of the entity

    A staging table stays in GLUESYNC after you delete an entity

    The user has no DROP on GLUESYNC

    GRANT DROP ON GLUESYNC.* TO 'gluesync'@'%'; and drop the table named de_…​ yourself

    A datetime(6) column shows .000000

    The agent writes whole seconds

    Plan for whole seconds. See the note in step 6

    ALTER TABLE …​ MODIFY of a column type fails with ALTER TABLE which modifies type of column …​ is not supported on a columnstore table

    SingleStore does not change a column type of a columnstore table in place

    Create the table again with the right type, or copy it to a new table

    Related guides

    Contact MOLO17 Support if you need assistance with a SingleStore target.