Can't trace database requests by oneagent SDK

[aokihiro]

To trace database requests(SQL) from C language program by Dynatrace, we are evaluating oneagent SDK.

According to help and github information, we embedded all necessary APIs and confirmed that the program successfully retrieves database data as expected. On the other hand, while we can only see the connection between the C language program(process) and a database process in smartcape and infograph of C language program(process), we can’t see any database requests executed by the C language program in Dynatrace.

Is there anything we are missing to capture database requests(SQL) executed by C language program or our understanding of oneagent SDK is wrong?

The below is a sample program with oneagent SDK we are using to trace database requests to mysql. The mysql is only accessed by this sample program.

---

int main(int argc, char **argv) { int i = 0;

   onesdk_databaseinfo_handle_t db_info_handle = ONESDK_INVALID_HANDLE;
   onesdk_tracer_handle_t tracer;
   int c;

   MYSQL *conn;
   MYSQL_RES *res;
   MYSQL_ROW row;

   const char query[256] = "SELECT * FROM AOKI_DATABASE.personal";

   const char *SERV = "localhost";
   const char *USER = "root";
   const char *PASSWORD = "admin";
   const char *DB_NAME = "AOKI_DATABASE";
   const unsigned int PORT = 3306; 

   conn = mysql_init(NULL);

   /* Initialize SDK */
   onesdk_result_t const onesdk_init_result = onesdk_initialize();

   if (onesdk_init_result != ONESDK_SUCCESS) {
          onesdk_shutdown();
          printf("SDK initialization error ....\n");
          exit(1);
   }

   /* Create Database Object */
   db_info_handle = onesdk_databaseinfo_create(
          onesdk_asciistr("AOKI_DATABASE"),   /* the name of the database that you connect to */
          onesdk_asciistr(ONESDK_DATABASE_VENDOR_MYSQL),   /* the type of the database   */
          ONESDK_CHANNEL_TYPE_TCP_IP,         /* channel type     */
          onesdk_asciistr("localhost:3306")  /* channel endpoint */);

   if (db_info_handle == ONESDK_INVALID_HANDLE) {
          printf("onesdk_databaseinfo_create() error...\n");
          onesdk_shutdown();
          exit(1);
   }

   /* ... perform the database request, consume results ... */
   /* database - AOKI_DATABASE, table - personal(id, name) */

   if (!mysql_real_connect(conn, SERV, USER, PASSWORD, DB_NAME, PORT, NULL, 0)) {
          fprintf(stderr, "%s\r\n", mysql_error(conn));
          exit(-1);
   }

   for (i = 1; i < 8; i++) {

          tracer = onesdk_databaserequesttracer_create_sql(    
                  db_info_handle,
                  onesdk_asciistr("SELECT * FROM AOKI_DATABASE.personal"));

          if (tracer == ONESDK_INVALID_HANDLE) {
                  printf("onesdk_databaserequesttracer_create_sql() eror...\n");
                  c = getchar();
                  onesdk_databaseinfo_delete(db_info_handle);
                  onesdk_shutdown();
                  exit(1);
          }
          else
                 printf("onesdk_databaserequesttracer_create_sql()...sucess\n");

          /* start tracer */
          onesdk_tracer_start(tracer);
          printf("onesdk_trace_start()...\n");

          if (mysql_query(conn, query)) {
                  fprintf(stderr, "%s\r\n", mysql_error(conn));
                  exit(-1);
          }

          res = mysql_use_result(conn);

          while (NULL != (row = mysql_fetch_row(res))) {
                  unsigned int col;
                  for (col = 0; col < mysql_num_fields(res); col++) {
                         printf("%s ", row[col]);
                  }
                  printf("\r\n");
          }

          if (NULL != res) {
                  mysql_free_result(res);
          }

          onesdk_tracer_end(tracer);

          Sleep(12000);

   }

   if (NULL != conn) {
          mysql_close(conn);
   }

   onesdk_databaseinfo_delete(db_info_handle);
   onesdk_shutdown();

}

---

[Oberon00]

Hello, You ran into a bit of a gotcha here: The problem is that database tracers will not create new PurePaths and only add to existing ones. So to see the database calls you must start another tracer around them so that a PurePath is active while you start and end the database tracer. For example an incoming remote call tracer would work (a custom service tracer would be ideal but that one isn't implemented in the OneAgent SDK for C yet).

Have a good new year!

[pgroke-dt]

Also please note that you should not treat a return value of ONESDK_INVALID_HANDLE as an error. Or, at the very least, you should not terminate your application if any of the SDK's factory functions returns ONESDK_INVALID_HANDLE. This can happen in situations like if the agent isn't installed, if it could not be loaded, if monitoring is disabled etc.

The SDK will happily, efficiently and silently ignore calls with a ONESDK_INVALID_HANDLE tracer handle, so it's not necessary to check the return value at all. Of course feeding information into a tracer that could not be created doesn't do anything useful, so it's fine if you skip those calls if the handle is ONESDK_INVALID_HANDLE. But please don't terminate your application :)

[aokihiro]

Thank you all for your advice.

According to your advice, combining remote call APIs and database tracer I could successfully see purepaths and database used by the sample programs. But I am experiencing other issue - caller and called processes are not connected/linked with tag. I attempted both byte and string tag, but it seems neither works.

The names of sample program are requester.c(exe) and db_access.c(exe) and I am running the programs on the same machine.

Any suggestions on this?

------ Requester.c ----- int main(int argc, char **argv) { int i = 0; onesdk_databaseinfo_handle_t db_info_handle = ONESDK_INVALID_HANDLE; char c;

onesdk_tracer_handle_t remotetracer;
onesdk_size_t byte_tag_size = 0;
unsigned char* byte_tag = NULL;
char tagid[128];
onesdk_size_t num_byte;

/* Initialize SDK */
onesdk_result_t const onesdk_init_result = onesdk_initialize();

if (onesdk_init_result != ONESDK_SUCCESS) {
    onesdk_shutdown();
    printf("SDK initialization error ....\n");
    exit(0);
}
else
    printf("onesdk_initialize() success...\n");

/* Remote call tracer */
remotetracer = onesdk_outgoingremotecalltracer_create(
    onesdk_asciistr("Database Request"), /* Request names -- shoud be SQL statement */
    onesdk_asciistr("Database Access Service"), /* Remote Service name */
    onesdk_asciistr("instance"),      /*Anything would be ok - service end point*/
    ONESDK_CHANNEL_TYPE_OTHER,           /* channel type   */
    onesdk_asciistr("OTHER"));/* channel endpoint, host/ip:port in case of TCP_IP */

onesdk_tracer_start(remotetracer);

/* get byte representation of tag  --- not necessary in this case as transfer of TAG is not conducted*/

num_byte = onesdk_tracer_get_outgoing_dynatrace_string_tag(remotetracer, tagid, 128, &byte_tag_size);

printf("string tag:num_byte =%d string =%s\n", num_byte, tagid);

printf("\n\ninput any character to trace end....\n");

onesdk_tracer_end(remotetracer);

c = getchar();

onesdk_shutdown();

}

------ db_access.c ----------- int main(int argc, char **argv) { int i = 0; onesdk_databaseinfo_handle_t db_info_handle = ONESDK_INVALID_HANDLE; onesdk_tracer_handle_t dbtracer; int c;

MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

onesdk_tracer_handle_t remotetracer;
onesdk_tracer_handle_t incomtracer;
onesdk_size_t byte_tag_size = 0;
unsigned char* byte_tag = NULL;
char tagid[128];
onesdk_string_t  string_tag;

const char query[256] = "SELECT * FROM AOKI_DATABASE.personal";

const char *SERV = "localhost";
const char *USER = "root";
const char *PASSWORD = "admin";
const char *DB_NAME = "AOKI_DATABASE";
const unsigned int PORT = 3306; 

conn = mysql_init(NULL);

/* Initialize SDK */
onesdk_result_t const onesdk_init_result = onesdk_initialize();

if (onesdk_init_result != ONESDK_SUCCESS) {
    onesdk_shutdown();
    printf("SDK initialization error ....\n");
    exit(0);
}
else
    printf("onesdk_initialize() success...\n");

/* Create Database Object */ 
db_info_handle = onesdk_databaseinfo_create(
    onesdk_asciistr("AOKI_DATABASE"),   /* the name of the database that you connect to */
    onesdk_asciistr(ONESDK_DATABASE_VENDOR_MYSQL),   /* the type of the database   */
    ONESDK_CHANNEL_TYPE_TCP_IP,         /* channel type     */
    onesdk_asciistr("localhost:3306")  /* channel endpoint */);

if (db_info_handle == ONESDK_INVALID_HANDLE) {
    printf("onesdk_databaseinfo_create() error...\n");
    onesdk_shutdown();
    exit(1);
}

/* set tag */
printf("input tag string....\n");
scanf_s("%s", tagid,128);

printf("tagid len =%d tagid =%s\n",strlen(tagid), tagid);

string_tag.data = tagid; 
string_tag.byte_length = strlen(tagid);
string_tag.ccsid = ONESDK_CCSID_ASCII;

/* Incom */
incomtracer = onesdk_incomingremotecalltracer_create(
    onesdk_asciistr("Database Request"),
    onesdk_asciistr("Database Access Service"),
    onesdk_asciistr("instance"));
if (incomtracer == ONESDK_INVALID_HANDLE) printf("incom trace handler error...\n");
else printf("incom trace handler success...\n");

onesdk_tracer_set_incoming_dynatrace_string_tag(incomtracer,string_tag);

onesdk_tracer_start(incomtracer);

/* ... perform the database request, consume results ... */
/* database - AOKI_DATABASE, table - personal(id, name) */

if (!mysql_real_connect(conn, SERV, USER, PASSWORD, DB_NAME, PORT, NULL, 0)) { /* ポート番号を定数PORTで指定する形に変更 */
    fprintf(stderr, "%s\r\n", mysql_error(conn));
    exit(-1);
}

for (i = 1; i < 10; i++) {

    dbtracer = onesdk_databaserequesttracer_create_sql( 
        db_info_handle,
        onesdk_asciistr("@@@@@ SELECT * FROM AOKI_DATABASE.personal ****"));

    if (dbtracer == ONESDK_INVALID_HANDLE) {
        printf("onesdk_databaserequesttracer_create_sql() eror...\n");
        c = getchar();
        onesdk_databaseinfo_delete(db_info_handle);
        onesdk_shutdown();
        exit(1);
    }
    else
        printf("onesdk_databaserequesttracer_create_sql()...sucess\n");

    /* start tracer */
    onesdk_tracer_start(dbtracer);
    printf("onesdk_trace_start()...\n");

    if (mysql_query(conn, query)) {
        fprintf(stderr, "%s\r\n", mysql_error(conn));
        exit(-1);
    }

    res = mysql_use_result(conn);

    while (NULL != (row = mysql_fetch_row(res))) {
        unsigned int col;
        for (col = 0; col < mysql_num_fields(res); col++) {
            printf("%s ", row[col]);
        }
        printf("\r\n");
    }

    if (NULL != res) {
        mysql_free_result(res);
    }

    onesdk_tracer_end(dbtracer);
    printf("oensdk_trace_end()...completed\n");

    Sleep(3000);
    printf("i = %d\n", i);
}

/* Remote call tracer */
onesdk_tracer_end(incomtracer);

if (NULL != conn) {
    mysql_close(conn);
}

printf("Wait for oneask_database_delete()....");
c = getchar();

onesdk_databaseinfo_delete(db_info_handle);
printf("oensdk_databaseinfo_delete()...completed\n");

printf("Wait for oneask_shutdown()....");
c = getchar();

onesdk_shutdown();

}

[Oberon00]

Hello, this code should actually work (assuming you have all required #include directives before the code you pasted). However, copy-pasting the string from one program into the other is error-prone. Make sure that when the db_access program starts the tracer after setting the incoming remote call, the requester program has not ended the tracer yet. EDIT: You should put the getchar() before ending the tracer to make that possible.

[Oberon00]

Was your problem solved / can this issue be closed?

[aokihiro]

I modified the codes and tried a lot of times but to no avail. I am confident the codes should work. We decided to ignore this minor issue.

[Oberon00]

OK. Should you encounter this or other issues again, please do not hesitate to contact us again. Closing this for now.