Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 45.6Mb
PDF (A4) - 45.7Mb
PDF (RPM) - 41.2Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 209.9Kb
Man Pages (Zip) - 312.0Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  C API Asynchronous Interface

28.7.12 C API Asynchronous Interface

As of MySQL 8.0.16, the C API includes asynchronous functions that enable nonblocking communication with the MySQL server. Asynchronous functions enable development of applications that differ from the query processing model based on synchronous functions that block if reads from or writes to the server connection must wait. Using the asynchronous functions, an application can check whether work on the server connection is ready to proceed. If not, the application can perform other work before checking again later.

For example, an application might open multiple connections to the server and use them to submit multiple statements for execution. The application then can poll the connections to see which of them have results to be fetched, while doing other work.

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 asychronous 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 28.7.15.1, “mysql_fetch_row_nonblocking()”, and Section 28.7.15.6, “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 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 one of these authentication plugins: mysql_native_password, 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.

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