Native SQL Tasks: manual runs, gates, retries, and scheduling¶
Complete standalone deployment and use a test account allowed to create databases and Tasks. Run in an isolated instance or test account. Task names are unique within an account: check that mo_docs_task_* names are unused. Drop Tasks before dropping their databases.
Native SQL Tasks exist in v4.2.5. They differ from MySQL EVENT, external DolphinScheduler jobs, and other internal TaskService work. See the SQL Task reference.
Submit the complete task body¶
Send CREATE TASK ... AS BEGIN ... END as one driver request; do not split its internal semicolons. In the MySQL CLI, use DELIMITER $$, terminate the complete creation request with END$$, then restore DELIMITER ;. DELIMITER is a client command, not server SQL. The following blocks require a driver or editor that submits complete statements.
Manual runs, gates, and idempotent writes¶
CREATE DATABASE mo_task_lab;
USE mo_task_lab;
CREATE TABLE task_sink(id INT PRIMARY KEY);
CREATE TABLE task_gate(id INT PRIMARY KEY);
CREATE TASK mo_docs_task_gate
WHEN (EXISTS(SELECT 1 FROM task_gate WHERE id=1))
RETRY 1 TIMEOUT '30s'
AS BEGIN
INSERT INTO task_sink
SELECT 1 WHERE NOT EXISTS(SELECT 1 FROM task_sink WHERE id=1);
END;
EXECUTE TASK mo_docs_task_gate;
-- Expected-Value: 0
SELECT COUNT(*) FROM task_sink;
SHOW TASK RUNS FOR mo_docs_task_gate LIMIT 1;
INSERT INTO task_gate VALUES(1);
EXECUTE TASK mo_docs_task_gate;
EXECUTE TASK mo_docs_task_gate;
-- Expected-Value: 1
SELECT COUNT(*) FROM task_sink;
ALTER TASK mo_docs_task_gate SUSPEND;
EXECUTE TASK mo_docs_task_gate;
-- Expected-Value: 1
SELECT COUNT(*) FROM task_sink;
SHOW TASK RUNS FOR mo_docs_task_gate LIMIT 5;
DROP TASK mo_docs_task_gate;
SHOW TASK RUNS FOR mo_docs_task_gate LIMIT 5;
DROP DATABASE mo_task_lab;
With no gate row, the first execution succeeds without writing; history records SKIPPED and there is no retry. After inserting the gate row, history records SUCCESS. Repeated runs leave one row because of the primary key and deduplication condition. SUSPEND disables scheduled triggers but still allows manual EXECUTE TASK. History remains queryable after dropping the definition.
Retries do not roll back the whole body¶
The second statement below fails deliberately. RETRY 1 means up to two attempts. Ensure the missing table does not exist, expect EXECUTE TASK to fail, and then inspect data and history.
CREATE DATABASE mo_task_retry_lab;
USE mo_task_retry_lab;
CREATE TABLE task_effects(id INT);
CREATE TASK mo_docs_task_retry RETRY 1
AS BEGIN
INSERT INTO task_effects VALUES(1);
INSERT INTO missing_task_lab_table VALUES(1);
END;
-- Expected-Success: false
EXECUTE TASK mo_docs_task_retry;
-- Expected-Value: 2
SELECT COUNT(*) FROM task_effects;
SHOW TASK RUNS FOR mo_docs_task_retry LIMIT 2;
DROP TASK mo_docs_task_retry;
DROP DATABASE mo_task_retry_lab;
The v4.2.5 probe leaves 2 rows, with FAILED attempts 1 and 2. Task statements execute individually, retaining earlier successful writes. A retry starts again at the beginning. BEGIN ... END wraps the body; it does not make the entire body atomic. Use idempotent writes, business deduplication keys, and watermark checks. Retries do not provide exactly-once execution.
Call one SQL procedure from a Task¶
When target writes and watermark updates must fail together, place the DML in one SQL procedure and call it from the Task. This example verifies rollback for an unhandled SQL error in that procedure; it is not a guarantee for every procedure or Task.
CREATE DATABASE mo_task_call_lab;
USE mo_task_call_lab;
CREATE TABLE task_rows(id INT);
CREATE TABLE task_watermark(id INT PRIMARY KEY, pos INT);
INSERT INTO task_watermark VALUES(1,0);
CREATE PROCEDURE task_ingest_fail()
'begin INSERT INTO task_rows VALUES(1); UPDATE task_watermark SET pos=1 WHERE id=1; INSERT INTO missing_task_call_table VALUES(1); end';
CREATE TASK mo_docs_task_call AS BEGIN CALL task_ingest_fail(); END;
-- Expected-Success: false
EXECUTE TASK mo_docs_task_call;
-- Expected-Value: 0
SELECT COUNT(*) FROM task_rows;
-- Expected-Value: 0
SELECT pos FROM task_watermark WHERE id=1;
DROP TASK mo_docs_task_call;
DROP PROCEDURE task_ingest_fail;
DROP DATABASE mo_task_call_lab;
Expect both row count and watermark to remain 0. The procedure uses a string body, unlike the Task’s direct AS BEGIN ... END syntax.
Scheduled execution experiment¶
This experiment depends on background scheduling and waiting. Syntax validation remains enabled, but execution requires manual phases. After creation, wait and poll history until trigger_type=SCHEDULED and status=SUCCESS appear, then suspend, resume, and clean up. A run need not appear immediately; a cron expression containing seconds does not promise a scheduling latency.
CREATE DATABASE mo_task_cron_lab;
USE mo_task_cron_lab;
CREATE TABLE cron_sink(marker VARCHAR(20));
CREATE TASK mo_docs_task_cron
SCHEDULE '*/5 * * * * *' TIMEZONE 'UTC'
TIMEOUT '30s'
AS BEGIN
INSERT INTO cron_sink VALUES('scheduled');
END;
SHOW TASKS;
SHOW TASK RUNS FOR mo_docs_task_cron LIMIT 5;
ALTER TASK mo_docs_task_cron SUSPEND;
ALTER TASK mo_docs_task_cron RESUME;
ALTER TASK mo_docs_task_cron SUSPEND;
DROP TASK mo_docs_task_cron;
DROP DATABASE mo_task_cron_lab;
Use six cron fields including seconds. Omitting TIMEZONE selects UTC. A run already started may finish after suspension; distinguish active work from new triggers in history. After resuming, wait for another success. SHOW TASKS displays definitions and latest status; SHOW TASK RUNS displays trigger type, attempt, duration, and errors.
Timeouts and troubleshooting¶
v4.2.5 stores whole seconds:
500mstruncates to 0 (no body timeout), and1500mstruncates to 1 second. Use explicit positive whole seconds. A probe usingTIMEOUT '1s'withSLEEP(2)failed after about 1 second withTIMEOUThistory.task service not ready yet: wait for TaskService readiness before retrying. Applications should not assume immediate readiness during startup.sql task is already running: an active run exists. A second concurrent manual execution was rejected in the probe. Inspect history and avoid overlapping trigger sources.After
FAILED, inspect committed data before retrying.rows_affecteddoes not imply that the whole body was rolled back.Native Tasks suit SQL jobs inside the database. For cross-system dependencies, complex DAGs, and external programs, use tools such as DolphinScheduler.
Manual runs, gates, retries, timeouts, overlap rejection, scheduled execution, and retained history were probed on 2026-10-14 against single-CN v4.2.5. These results do not certify multi-CN failover, privilege isolation, or production scheduler capacity.