MySQL 9.0 C API Developer Guide  /  C API Asynchronous Interface  /  Overview of the C API Asynchronous Interface

7.1 Overview of the C API Asynchronous Interface

This section describes how to use the C API asynchronous interface. In this discussion, asynchronous and nonblocking are used as synonyms, as are synchronous and blocking.

The asynchronous C API functions cover operations that might otherwise block when reading to or writing from the server connection: The initial connection operation, sending a query, reading the result, and so forth. Each asynchronous function has the same name as its synchronous counterpart, plus a _nonblocking suffix:

Applications can mix asynchronous and synchronous functions if there are operations that need not be done asynchronously or for which the asynchronous functions do not apply.

The following discussion describes in more detail how to use asynchronous C API functions.

Asynchronous Function Calling Conventions

All asynchronous C API functions return an enum net_async_status value. The return value can be one of the following values to indicate operation status:

  • NET_ASYNC_NOT_READY: The operation is still in progress and not yet complete.

  • NET_ASYNC_COMPLETE: The operation completed successfully.

  • NET_ASYNC_ERROR: The operation terminated in error.

  • NET_ASYNC_COMPLETE_NO_MORE_RESULTS: The operation completed successfully and no more results are available. This status applies only to mysql_next_result_nonblocking().

In general, to use an asynchronous function, do this:

  • Call the function repeatedly until it no longer returns a status of NET_ASYNC_NOT_READY.

  • Check whether the final status indicates successful completion (NET_ASYNC_COMPLETE) or an error (NET_ASYNC_ERROR).

The following examples illustrate some typical calling patterns. function(args) represents an asynchronous function and its argument list.

  • If it is desirable to perform other processing while the operation is in progress:

    enum net_async_status status;
    
    status = function(args);
    while (status == NET_ASYNC_NOT_READY) {
      /* perform other processing */
      other_processing ();
      /* invoke same function and arguments again */
      status = function(args);
    }
    if (status == NET_ASYNC_ERROR) {
      /* call failed; handle error */
    } else {
      /* call successful; handle result */
    }
  • If there is no need to perform other processing while the operation is in progress:

    enum net_async_status status;
    
    while ((status = function(args)) == NET_ASYNC_NOT_READY)
      ; /* empty loop */
    if (status == NET_ASYNC_ERROR) {
      /* call failed; handle error */
    } else {
      /* call successful; handle result */
    }
  • If the function success/failure result does not matter and you want to ensure only that the operation has completed:

    while (function (args) != NET_ASYNC_COMPLETE)
      ; /* empty loop */

For mysql_next_result_nonblocking(), it is also necessary to account for the NET_ASYNC_COMPLETE_NO_MORE_RESULTS status, which indicates that the operation completed successfully and no more results are available. Use it like this:

while ((status = mysql_next_result_nonblocking()) != NET_ASYNC_COMPLETE) {
  if (status == NET_ASYNC_COMPLETE_NO_MORE_RESULTS) {
    /* no more results */
  }
  else if (status == NET_ASYNC_ERROR) {
    /* handle error by calling mysql_error(); */
    break;
  }
}

In most cases, arguments for the asynchronous functions are the same as for the corresponding synchronous functions. Exceptions are mysql_fetch_row_nonblocking() and mysql_store_result_nonblocking(), each of which takes an extra argument compared to its synchronous counterpart. For details, see Section 7.4.1, “mysql_fetch_row_nonblocking()”, and Section 7.4.8, “mysql_store_result_nonblocking()”.

Example Program

This section shows an example C++ program that illustrates use of asynchronous C API functions.

To set up the SQL objects used by the program, execute the following statements. Substitute a different database or user as desired; in this case, you will need to make some adjustments to the program as well.

CREATE DATABASE db;
USE db;
CREATE TABLE test_table (id INT NOT NULL);
INSERT INTO test_table VALUES (10), (20), (30);

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpass';
GRANT ALL ON db.* TO 'testuser'@'localhost';

Create a file named async_app.cc containing the following program. Adjust the connection parameters as necessary.

#include <stdio.h>
#include <string.h>
#include <iostream>
#include <mysql.h>
#include <mysqld_error.h>

using namespace std;

/* change following connection parameters as necessary */
static const char * c_host = "localhost";
static const char * c_user = "testuser";
static const char * c_auth = "testpass";
static int          c_port = 3306;
static const char * c_sock = "/usr/local/mysql/mysql.sock";
static const char * c_dbnm = "db";

void perform_arithmetic() {
  cout<<"dummy function invoked\n";
  for (int i = 0; i < 1000; i++)
    i*i;
}

int main(int argc, char ** argv)
{
  MYSQL *mysql_local;
  MYSQL_RES *result;
  MYSQL_ROW row;
  net_async_status status;
  const char *stmt_text;

  if (!(mysql_local = mysql_init(NULL))) {
    cout<<"mysql_init() failed\n";
    exit(1);
  }
  while ((status = mysql_real_connect_nonblocking(mysql_local, c_host, c_user,
                                                  c_auth, c_dbnm, c_port,
                                                  c_sock, 0))
            == NET_ASYNC_NOT_READY)
    ; /* empty loop */
  if (status == NET_ASYNC_ERROR) {
    cout<<"mysql_real_connect_nonblocking() failed\n";
    exit(1);
  }

  /* run query asynchronously */
  stmt_text = "SELECT * FROM test_table ORDER BY id";
  status = mysql_real_query_nonblocking(mysql_local, stmt_text,
                                        (unsigned long)strlen(stmt_text));
  /* do some other task before checking function result */
  perform_arithmetic();
  while (status == NET_ASYNC_NOT_READY) {
    status = mysql_real_query_nonblocking(mysql_local, stmt_text,
                                          (unsigned long)strlen(stmt_text));
    perform_arithmetic();
  }
  if (status == NET_ASYNC_ERROR) {
    cout<<"mysql_real_query_nonblocking() failed\n";
    exit(1);
  }

  /* retrieve query result asynchronously */
  status = mysql_store_result_nonblocking(mysql_local, &result);
  /* do some other task before checking function result */
  perform_arithmetic();
  while (status == NET_ASYNC_NOT_READY) {
    status = mysql_store_result_nonblocking(mysql_local, &result);
    perform_arithmetic();
  }
  if (status == NET_ASYNC_ERROR) {
    cout<<"mysql_store_result_nonblocking() failed\n";
    exit(1);
  }
  if (result == NULL) {
    cout<<"mysql_store_result_nonblocking() found 0 records\n";
    exit(1);
  }

  /* fetch a row synchronously */
  row = mysql_fetch_row(result);
  if (row != NULL && strcmp(row[0], "10") == 0)
    cout<<"ROW: " << row[0] << "\n";
  else
    cout<<"incorrect result fetched\n";

  /* fetch a row asynchronously, but without doing other work */
  while (mysql_fetch_row_nonblocking(result, &row) != NET_ASYNC_COMPLETE)
    ; /* empty loop */
  /* 2nd row fetched */
  if (row != NULL && strcmp(row[0], "20") == 0)
    cout<<"ROW: " << row[0] << "\n";
  else
    cout<<"incorrect result fetched\n";

  /* fetch a row asynchronously, doing other work while waiting */
  status = mysql_fetch_row_nonblocking(result, &row);
  /* do some other task before checking function result */
  perform_arithmetic();
  while (status != NET_ASYNC_COMPLETE) {
    status = mysql_fetch_row_nonblocking(result, &row);
    perform_arithmetic();
  }
  /* 3rd row fetched */
  if (row != NULL && strcmp(row[0], "30") == 0)
    cout<<"ROW: " << row[0] << "\n";
  else
    cout<<"incorrect result fetched\n";

  /* fetch a row asynchronously (no more rows expected) */
  while ((status = mysql_fetch_row_nonblocking(result, &row))
           != NET_ASYNC_COMPLETE)
    ; /* empty loop */
  if (row == NULL)
    cout <<"No more rows to process.\n";
  else
    cout <<"More rows found than expected.\n";

  /* free result set memory asynchronously */
  while (mysql_free_result_nonblocking(result) != NET_ASYNC_COMPLETE)
    ; /* empty loop */

  mysql_close(mysql_local);
}

Compile the program using a command similar to this; adjust the compiler and options as necessary:

gcc -g async_app.cc -std=c++11 \
  -I/usr/local/mysql/include \
  -o async_app -L/usr/lib64/ -lstdc++ \
  -L/usr/local/mysql/lib/ -lmysqlclient

Run the program. The results should be similar to what you see here, although you might see a varying number of dummy function invoked instances.

dummy function invoked
dummy function invoked
ROW: 10
ROW: 20
dummy function invoked
ROW: 30
No more rows to process.

To experiment with the program, add and remove rows from test_table, running the program again after each change.

Asynchronous Function Restrictions

These restrictions apply to the use of asynchronous C API functions:

  • mysql_real_connect_nonblocking() can be used only for accounts that authenticate with sha256_password or caching_sha2_password.

  • mysql_real_connect_nonblocking() can be used only to establish TCP/IP or Unix socket file connections.

  • These statements are not supported and must be processed using synchronous C API functions: LOAD DATA, LOAD XML.

  • Input arguments passed to an asynchronous C API call that initiates a nonblocking operation may remain in use until the operation terminates later, and should not be reused until termination occurs.

  • Protocol compression is not supported for asynchronous C API functions.