How to make app_mysql use one database connection for all queries

Skip to end of metadata
Go to start of metadata

How to make app_mysql use one database connection for all queries

Intro

This howto can be useful for those who heavily rely on app_mysql in their solutions. The examples below are written in AEL2 but can be easily re-written in pure asterisk extensions.
Note: AEL2 does not require to escape spaces and single quotes, but you MUST escape commas!!

The problem

Usually when you use app_mysql widely in your dialplan and you obey to the DRY principle (Don't Repeat Yourself) you use smth like this:

macro dbquery(QUERY, RESULT_VAR) {
        MySQL(connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB});
        MySQL(query resultid ${connid} ${QUERY});
        MySQL(fetch fetchid ${resultid} ${RESULT_VAR});
        MySQL(clear ${resultid});
        MySQL(disconnect ${connid});
}

context db-test {
        _X. => {
                Macro(dbquery,SELECT NOW(),now);
                NoOp(${now});
                Macro(dbquery,SELECT 1+1,res);
                NoOp(res is ${res});
        }
}

When you run this example you will get the following result:

-- Executing Macro("IAX2/demo_user1-1", "dbquery|SELECT NOW()|now") in new stack
    -- Executing Set("IAX2/demo_user1-1", "QUERY=SELECT NOW()") in new stack
    -- Executing Set("IAX2/demo_user1-1", "RESULT_VAR=now") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "connect connid 127.0.0.1 root root billing_v1") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "query resultid 19 SELECT NOW()") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "fetch fetchid 20 now") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "clear 20") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "disconnect 19") in new stack
    -- Executing NoOp("IAX2/demo_user1-1", "2007-07-02 17:47:32") in new stack
    -- Executing Macro("IAX2/demo_user1-1", "dbquery|SELECT 1+1|res") in new stack
    -- Executing Set("IAX2/demo_user1-1", "QUERY=SELECT 1+1") in new stack
    -- Executing Set("IAX2/demo_user1-1", "RESULT_VAR=res") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "connect connid 127.0.0.1 root root billing_v1") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "query resultid 19 SELECT 1+1") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "fetch fetchid 20 res") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "clear 20") in new stack
    -- Executing MYSQL("IAX2/demo_user1-1", "disconnect 19") in new stack
    -- Executing NoOp("IAX2/demo_user1-1", "res is 2") in new stack

The example above has the following limitations:

  • Macro application is very expensive operation. All Macros must be replaced with Gosub asterisk application. See !!!!!!!!!!
  • Every query makes it's own database connection. It's not only a waste of system resources but can dramatically increase PDD (Post Dial Delay) when Asterisk and [MySQL] database are located on different servers.
  • We do not do connection or operation errors checking.
    So we have to find the solution.

The solution

The solution will solve the issues above and a bit more

  • First of all we change Macro to Gosub. As we have to pass parameters we'll do it by using asterisk dialplan variables.
  • Imagine the situation when 300 simultaneous calls are hitting your server. If we simply use one database connection per channel it will be 300 database connections. Each database connection consumes system resources. So we have 2 options here:
  • Modify app_mysql to connect to the database on load.
  • Use global asterisk connid handle.
    The second option is easier so we use this for now.
    We've got the following diaplan:
    globals {
        // Database access
        DBHOST=127.0.0.1;
        DBUSER=asterisk;
        DBPASS=asterisk;
        DB=billing;
        SET_CALLERID=1;
        // Global database connection for all connid
        connid=;
    }
    
    
    context db-query {
        _X! => {
            // Connect if required
            if (${LEN(${connid})} = 0) {
                Verbose(***DEBUG*** Connecting connid to the database.);
                MySQL(connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB});
                Set(connid=${connid},g);
                if (${MYSQL_STATUS} = -1) {
                    Gosub(causes,100,db-connection-failed);
                    Hangup;
                }
                else {
                    Verbose(***DEBUG*** Connected to the database. Status: ${MYSQL_STATUS}. connid ${connid});
                }
            }
            else {
                Verbose(***DEBUG*** Reusing existing connid ${connid});
            }
            // Connected. Now execute the query.
            MySQL(query resultid ${connid} ${QUERY});
            if (${MYSQL_STATUS} = -1) {
                Verbose(***ERROR*** Query returned error ${MYSQL_STATUS}.);
                Gosub(causes,100,database-error);
                Gosub(db-disconnect,100,1);
                Hangup;
            }
            else {
                Verbose(***DEBUG*** Successfully executed database query ${QUERY});
            }
            // Query executed. Fetch the result if any
            if (${LEN(${resultid})} != 0) {
                MySQL(fetch fetchid ${resultid} ${RESULT_VAR});
                if (${MYSQL_STATUS} = -1) {
                    Gosub(causes,100,database-error);
                    Gosub(db-disconnect,100,1);
                    Hangup;
                }
                else {
                    MySQL(clear ${resultid});
                    Verbose(***DEBUG*** Successfully fetched ${${RESULT_VAR}} into ${RESULT_VAR});
                    // MySQL(disconnect ${connid});
                    // Set(connid=);
                }
            // MySQL(next_result ${connid}); - this is commented. See below.
            }
            Set(resultid=);
            Set(fetchid=);
            Set(RESULT_VAR=);
            Set(QUERY=);
            Return;
        }
    }
    
    context db-test2 {
            _X. => {
                    Set(QUERY=SELECT NOW());
                    Set(RESULT_VAR=now);
                    Gosub(db-query,100,1);
                    NoOp(${now});
                    // Next query will use same connection
                    Set(QUERY=SELECT 1+1,2+2);
                    Set(RESULT_VAR=arg1 arg2);
                    Gosub(db-query,100,1);
                    NoOp(arg1 is ${arg1}, arg2 is ${arg2});
            }
    }
    
    

Comments:

  • MYSQL_STATUS returns 0 on app_mysql success or -1 on failure. We check MYSQL_STATUS to handle errors.
  • We use Set(connid=${connid},g) to set global variable connid. The connection is made on the 1-st call and all later channels use same connection.
  • We can select many fields by simple setting RESULT_VAR to space delimited variables. See asterisk log for details.
  • We reset connid on any error so next call will make a new database connection. This can be due to connection timeout for example.

The test call log:

    -- Executing Set("IAX2/demo_user1-3", "QUERY=SELECT NOW()") in new stack
    -- Executing Set("IAX2/demo_user1-3", "RESULT_VAR=now") in new stack
    -- Executing Gosub("IAX2/demo_user1-3", "db-query|100|1") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "1?2:13") in new stack
    -- Goto (db-query,100,2)
    -- Executing Verbose("IAX2/demo_user1-3", "***DEBUG*** Connecting connid to the database.") in new stack
***DEBUG*** Connecting connid to the database.
    -- Executing MYSQL("IAX2/demo_user1-3", "connect connid 127.0.0.1 root root billing_v1") in new stack
    -- Executing Set("IAX2/demo_user1-3", "connid=4|g") in new stack
  == Setting global variable 'connid' to '4'
    -- Executing GotoIf("IAX2/demo_user1-3", "0?6:10") in new stack
    -- Goto (db-query,100,10)
    -- Executing Verbose("IAX2/demo_user1-3", "***DEBUG*** Connected to the database. Status: 0. connid 4") in new stack
***DEBUG*** Connected to the database. Status: 0. connid 4
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-if-db-query-489-490") in new stack
    -- Executing Goto("IAX2/demo_user1-3", "14") in new stack
    -- Goto (db-query,100,14)
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-db-query-489") in new stack
    -- Executing MYSQL("IAX2/demo_user1-3", "query resultid 4 SELECT NOW()") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "0?17:23") in new stack
    -- Goto (db-query,100,23)
    -- Executing Verbose("IAX2/demo_user1-3", "***DEBUG*** Successfully executed database query SELECT NOW()") in new stack
***DEBUG*** Successfully executed database query SELECT NOW()
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-db-query-491") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "1?26:37") in new stack
    -- Goto (db-query,100,26)
    -- Executing MYSQL("IAX2/demo_user1-3", "fetch fetchid 5 now") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "0?28:33") in new stack
    -- Goto (db-query,100,33)
    -- Executing MYSQL("IAX2/demo_user1-3", "clear 5") in new stack
    -- Executing Verbose("IAX2/demo_user1-3", "***DEBUG*** Successfully fetched 2007-07-02 18:28:33 into now") in new stack
***DEBUG*** Successfully fetched 2007-07-02 18:28:33 into now
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-if-db-query-492-493") in new stack
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-db-query-492") in new stack
    -- Executing Set("IAX2/demo_user1-3", "resultid=") in new stack
    -- Executing Set("IAX2/demo_user1-3", "fetchid=") in new stack
    -- Executing Set("IAX2/demo_user1-3", "RESULT_VAR=") in new stack
    -- Executing Set("IAX2/demo_user1-3", "QUERY=") in new stack
    -- Executing Return("IAX2/demo_user1-3", "") in new stack
    -- Executing NoOp("IAX2/demo_user1-3", "2007-07-02 18:28:33") in new stack
    -- Executing Set("IAX2/demo_user1-3", "QUERY=SELECT 1+1, 2+2") in new stack
    -- Executing Set("IAX2/demo_user1-3", "RESULT_VAR=arg1 arg2") in new stack
    -- Executing Gosub("IAX2/demo_user1-3", "db-query|100|1") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "0?2:13") in new stack
    -- Goto (db-query,100,13)
    -- Executing Verbose("IAX2/demo_user1-3", "***DEBUG*** Reusing existing connid 4") in new stack
***DEBUG*** Reusing existing connid 4
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-db-query-489") in new stack
    -- Executing MYSQL("IAX2/demo_user1-3", "query resultid 4 SELECT 1+1, 2+2") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "0?17:23") in new stack
    -- Goto (db-query,100,23)
    -- Executing Verbose("IAX2/demo_user1-3", "***DEBUG*** Successfully executed database query SELECT 1+1, 2+2") in new stack
***DEBUG*** Successfully executed database query SELECT 1+1, 2+2
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-db-query-491") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "1?26:37") in new stack
    -- Goto (db-query,100,26)
    -- Executing MYSQL("IAX2/demo_user1-3", "fetch fetchid 5 arg1 arg2") in new stack
    -- Executing GotoIf("IAX2/demo_user1-3", "0?28:33") in new stack
    -- Goto (db-query,100,33)
    -- Executing MYSQL("IAX2/demo_user1-3", "clear 5") in new stack
    -- Executing Verbose("IAX2/demo_user1-3", "***DEBUG*** Successfully fetched  into arg1 arg2") in new stack
***DEBUG*** Successfully fetched  into arg1 arg2
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-if-db-query-492-493") in new stack
    -- Executing NoOp("IAX2/demo_user1-3", "Finish if-db-query-492") in new stack
    -- Executing Set("IAX2/demo_user1-3", "resultid=") in new stack
    -- Executing Set("IAX2/demo_user1-3", "fetchid=") in new stack
    -- Executing Set("IAX2/demo_user1-3", "RESULT_VAR=") in new stack
    -- Executing Set("IAX2/demo_user1-3", "QUERY=") in new stack
    -- Executing Return("IAX2/demo_user1-3", "") in new stack
    -- Executing NoOp("IAX2/demo_user1-3", "arg1 is 2 arg2 is 4") in new stack

app_addon_sql_mysql bug

If you start using the db-query dialplan above you will notice that you can pass wrong sql query and it will not be detected by [MySQL](query ...). This is because [MySQL](query ...) always returns 0 even in case of failed query. The solution is to add a check for mysql_query C function exit status. See patch below.

Stored procedures problem

Everything would be perfect if our queries were simple selects. But real power comes when we start to use mysql 5 stored procedures. We cannot use legal semantics like

CALL proc(in_param1, in_param2, out_param1, out_param2)

because app_mysql concept requires procedure to return a result set like regular SELECT does. This can be solved by adding special parameters to database connection: CLIENT_MULTI_RESULTS and CLIENT_MULTI_STATEMENTS. Actually Mysql documentation says that we need to specify only CLIENT_MULTI_STATEMENTS as it implies CLIENT_MULTI_RESULTS.

So we have to patch app_mysql and add to mysql_real_connect procedures required parameters.

But this is only first problem when you want to use stored procedures. CLIENT_MULTI_STATEMENTS works perfect when you have a separate connection for all queries like in the first example. But when you come to using one global connection for all queries and use stored procedures your system fails with

Jul 2 18:40:28 ERROR![29168]: app_addon_sql_mysql.c:269 aMYSQL_query: Error 2014, Commands out of sync; you can't run this command now

This is because of probably bug in mysql because same situation can be easily replayed in other languages for example in python.
The solution was to create a special app_mysql argument next_result to call mysql_next_result C api function.

The total patch for app_mysql is below:

--- app_addon_sql_mysql.c       2007-07-02 18:57:47.000000000 +0300
+++ app_addon_sql_mysql.c-patch 2007-07-02 18:57:24.000000000 +0300
@@ -227,7 +227,7 @@
        if( connid_var && dbhost && dbuser && dbpass && dbname ) {
                mysql = mysql_init(NULL);
                if (mysql) {
-                       if (mysql_real_connect(mysql,dbhost,dbuser,dbpass,dbname,0,NULL,0)) {
+                       if (mysql_real_connect(mysql,dbhost,dbuser,dbpass,dbname,0,NULL,CLIENT_MULTI_STATEMENTS)) {
                                add_identifier_and_set_asterisk_int(chan,connid_var,AST_MYSQL_ID_CONNID,mysql);
                                return 0;
                        }
@@ -253,6 +253,7 @@

        char *resultid_var;
        int connid;
+       int query_status;
        char *querystring;

        strsep(&data," "); // eat the first token, we already know it :P
@@ -263,7 +264,11 @@

        if (resultid_var && (connid>=0) && querystring) {
                if ((mysql=find_identifier(connid,AST_MYSQL_ID_CONNID))!=NULL) {
-                       mysql_query(mysql,querystring);
+                       query_status = mysql_query(mysql,querystring);
+                       if (query_status != 0) {
+                           ast_log(LOG_ERROR, "Error %d, %s\n", mysql_errno(mysql), mysql_error(mysql));
+                           return -1;
+                       }
                        if ((mysqlres=mysql_use_result(mysql))!=NULL) {
                                add_identifier_and_set_asterisk_int(chan,resultid_var,AST_MYSQL_ID_RESID,mysqlres);
                                return 0;
@@ -354,6 +359,22 @@
        return 0;
 }

+static int aMYSQL_next_result(struct ast_channel *chan, char *data) {
+
+       MYSQL *mysql;
+       int id;
+       strsep(&data," "); // eat the first token, we already know it :P
+
+       id = safe_scan_int(&data," \n",-1);
+       if ((mysql=find_identifier(id,AST_MYSQL_ID_CONNID))==NULL) {
+               ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aMYSQL_disconnect\n",id);
+       } else {
+               mysql_next_result(mysql);
+       }
+       return 0;
+}
+
+
 static int aMYSQL_disconnect(struct ast_channel *chan, char *data) {

        MYSQL *mysql;
@@ -401,6 +422,8 @@
                result=aMYSQL_clear(chan,ast_strdupa(data));
        } else  if (strncasecmp("disconnect",data,strlen("disconnect"))==0) {
                result=aMYSQL_disconnect(chan,ast_strdupa(data));
+       } else  if (strncasecmp("next_result",data,strlen("next_result"))==0) {
+               result=aMYSQL_next_result(chan,ast_strdupa(data));
        } else {
                ast_log(LOG_WARNING, "Unknown argument to MYSQL application : %s\n",(char *)data);
                result=-1;

Don't forget to uncomment the following in db-query context:

[MySQL](next_result ${connid});

Links

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.