As of MySQL 9.3.1, you can run SQL statements as asynchronous tasks that run in the background. This lets your current session to continue without waiting for these SQL statements or tasks to complete. It is particularly helpful for long-running tasks such as data processing, report generation, or any workload that would normally block the session if run synchronously. You can run such tasks independent of the session where you initiate them and your session or application stays responsive while the task continues to run in the background.
For applications that have multiple application users, and there is no direct mapping between MySQL users and application users, you can use the application-level variants of the asynchronous task routines. Application-level asynchronous task routines let you filter and track tasks for individual application users using the application-managed user ID.
This topic includes the following sections:
To learn about the limitations of asynchronous tasks, see Section 2.21.1, “Asynchronous Task Limitations”.
-
Ensure that you have the following roles and the roles are active:
To initiate and manage your own asynchronous tasks, you require the
mysql_task_user
role.To manage all the other users' tasks, backup and restore data, or change the maximum task limits, you require the
mysql_task_admin
role.
Ensure that you are connected to the DB System.
The mysql_tasks
schema includes the
following components:
Task metadata: stores information about each task, including the task ID, task name, name of the MySQL user who created the task, MySQL connection ID used to create the task, task type, application-managed user ID used to create the task, and any additional metadata related to the task.
Logs and status: stores details about task progress and log messages.
APIs: stores the routines for creating, monitoring, and terminating asynchronous tasks.
The mysql_tasks.config
table stores the
following key values that are used to define maximum task
limits:
The
limits.maximumPreparedStmtAsyncTasks
key specifies the maximum number of SQL statements that can run concurrently within the HeatWave Cluster. This value can be between1
and128
.-
The
limits.maximumHeatwaveLoadingTasks
key specifies the maximum number ofVECTOR_STORE_LOAD
tasks that can run concurrently for loading unstructured data into the HeatWave Cluster. This value can be between1
and10
.The value specified for the
limits.maximumHeatwaveLoadingTasks
key cannot exceed the value of thelimits.maximumPreparedStmtAsyncTasks
key.
To view or update the maximum task limits, perform the following steps:
-
View the maximum number of concurrent tasks you can run:
Press CTRL+C to copymysql> SELECT * from mysql_tasks.config;
The output is similar to the following:
Press CTRL+C to copy+----+--------------------------------------------------------------------------------------+ | id | data | +----+--------------------------------------------------------------------------------------+ | 1 | {"limits": {"maximumHeatwaveLoadingTasks": 5, "maximumPreparedStmtAsyncTasks": 100}} | +----+--------------------------------------------------------------------------------------+
-
Update the maximum number of concurrent task limits:
Press CTRL+C to copymysql> UPDATE mysql_tasks.config SET data = '{"limits": {"maximumHeatwaveLoadingTasks": MaxVectoStoreLoadTasks, "maximumPreparedStmtAsyncTasks": MaxSQLTasks}}' WHERE id = 1;
Replace
MaxVectoStoreLoadTasks
with the maximum limit forVECTOR_STORE_LOAD
tasks andMaxSQLTasks
with the maximum limit for SQL statements. For example:Press CTRL+C to copymysql> UPDATE mysql_tasks.config SET data = '{"limits": {"maximumHeatwaveLoadingTasks": 10, "maximumPreparedStmtAsyncTasks": 10}}' WHERE id = 1;
Perform the following steps:
-
To run SQL statements asynchronously, use the
execute_prepared_stmt_async
routine:Press CTRL+C to copymysql> CALL mysql_tasks.execute_prepared_stmt_async ('SQLStatements', 'DBName', 'TaskName', 'TaskData', @task_id);
Replace the following:
SQLStatements
: one or more SQL statements separated by a semi-colon (;
).DBName
: database name to run the SQL statements. If you are using a specific database in the current session, you can set this toNULL
to continue using the current session database.TaskName
: name for the asynchronous task. If set toNULL
,execute_prepared_stmt_async
is used as the default task name.TaskData
: any additional metadata related to the task represented as JSON data. If there is no metadata, set toNULL
.
The statement returns a unique task identifier (task ID) for the asynchronous task that is created, which is stored in the
@task_id
variable.For example:
Press CTRL+C to copymysql> CALL mysql_tasks.execute_prepared_stmt_async('DO SLEEP(5); SELECT JSON_OBJECT("Async Task completed at", now()) into @task_result', 'demo_db', 'DemoAsyncTask', NULL, @task_id);
In this example:
The first SQL statement
DO SLEEP(5)
simulates a time-consuming task. This can be any long-running task.The second statement generates an output that is stored in the reserved session variable
@task_result
. You can optionally use the reserved session variable@task_result
to add a custom task status message for the asynchronous task, or to store an output generated by an SQL statement that you are running asynchronously. The value that you want to store in@task_result
must be in JSON format.The task ID generated for the asynchronous task that is returned by the routine is stored in the session variable
@task_id
.
-
View the task ID that is stored in the session variable
@task_id
:Press CTRL+C to copymysql> SELECT @task_id;
The output is similar to the following:
Press CTRL+C to copy17efb56b-52a1-11f0-bca4-020017205654
This task ID is independent of the session. You can use it to monitor or terminate the task from any SQL session that you own.
-
View the asynchronous task status brief:
Press CTRL+C to copymysql> SELECT JSON_PRETTY(mysql_tasks.task_status_brief(@task_id));
The output is similar to the following:
Press CTRL+C to copy| { "data": { "Async Task completed at": "2025-06-26 14:17:38.000000" }, "status": "COMPLETED", "message": "Execution finished.", "progress": 100 } |
Notice that the JSON value stored in
@task_result
is included in the task statusdata
field.
Tasks that are completed, canceled, or encountered an error are automatically removed after six days.
To asynchronously run SQL statements on an application level
using an application-managed user ID, use the
execute_prepared_stmt_from_app_async
routine. This routine includes additional parameters that you
can use to customise the format for storing task status and
log data. Similarly, to view the status brief of an
application-level asynchronous task created by an application
user, use the
app_task_status_brief
routine.
Learn more about Asynchronous Task Routines.