From 3786fbcb20e77ff6a4367349f22b6817671ab62a Mon Sep 17 00:00:00 2001 From: onefang Date: Fri, 22 May 2020 14:06:15 +1000 Subject: Deal with the database connection going away, and various related clean ups. Or try to, MariaDB don't make it easy. --- src/sledjchisl/sledjchisl.c | 757 +++++++++++++++++++++++--------------------- 1 file changed, 402 insertions(+), 355 deletions(-) (limited to 'src/sledjchisl/sledjchisl.c') diff --git a/src/sledjchisl/sledjchisl.c b/src/sledjchisl/sledjchisl.c index 537b435..a8d8b3f 100644 --- a/src/sledjchisl/sledjchisl.c +++ b/src/sledjchisl/sledjchisl.c @@ -375,7 +375,6 @@ struct _reqData qhashtbl_t *configs, *queries, *body, *cookies, *headers, *valid, *stuff, *database, *Rcookies, *Rheaders; char *Scheme, *Host, *Method, *Script, *Path, *RUri, *doit, *form, *output, *outQuery; sesh shs, *lnk; - MYSQL *db; gridStats *stats; qlist_t *errors, *messages; qgrow_t *reply; @@ -411,6 +410,9 @@ char toybuf[4096]; lua_State *L; qhashtbl_t *configs; MYSQL *database, *dbconn; +unsigned int dbTimeout; +struct timespec dbLast; +my_bool dbReconnect; gridStats *stats; boolean isTmux = 0; boolean isWeb = 0; @@ -882,36 +884,203 @@ static void dumpArray(int d, char **ar) } -/* How to deal with prepared SQL statements. -http://karlssonondatabases.blogspot.com/2010/07/prepared-statements-are-they-useful-or.html -https://blog.cotten.io/a-taste-of-mysql-in-c-87c5de84a31d?gi=ab3dd1425b29 -https://raspberry-projects.com/pi/programming-in-c/databases-programming-in-c/mysql/accessing-the-database -IG and CG now both have sims connected to other grids, so some sort of -multi database solution would be good, then we can run the grid and the -external sims all in one. +typedef struct _dbFields dbFields; +struct _dbFields +{ + qlisttbl_t *flds; + int count; +}; +typedef struct _dbField dbField; +struct _dbField +{ + char *name; + enum enum_field_types type; + unsigned long length; + unsigned int flags; + unsigned int decimals; +}; -Not sure if this'll work with Count(*). +void dbFreeFields(dbFields *flds, boolean all) +{ + flds->count--; ---------------------------------------------- +// TODO - sigh, looks to be inconsistant why some do and some don't leak. +// I guess the ones that don't leak are the ones that crash? +// It's only a tiny leak anyway, 80 bytes total. +// if ((0 >= flds->count) || all) // CRASHY + if ((0 >= flds->count)) // LEAKY + { + qlisttbl_obj_t obj; -The complicated bit is the binds. + memset((void *) &obj, 0, sizeof(obj)); + flds->flds->lock(flds->flds); + while(flds->flds->getnext(flds->flds, &obj, NULL, false) == true) + { + dbField *fld = (dbField *) obj.data; + free(fld->name); + } + flds->flds->unlock(flds->flds); + flds->flds->free(flds->flds); + flds->flds = NULL; + free(flds); + } +} -You are binding field values to C memory locations. -The parameters and returned fields need binds. -Mostly seems to be the value parts of the SQL statements. +enum dbCommandType +{ + CT_SELECT, + CT_CREATE, + CT_UPDATE, + CT_NONE +}; -I suspect most will be of the form - - ... WHERE x=? and foo=? - INSERT INTO table VALUES (?,?,?) - UPDATE table SET x=?, foo=? WHERE id=? +typedef struct _dbRequest dbRequest; +struct _dbRequest +{ + char *table, *join, *where, *order, *sql; + MYSQL_STMT *prep; // NOTE - executing it stores state in this. + dbFields *fields; + int inCount, outCount, rowCount; + char **inParams, **outParams; + MYSQL_BIND *inBind, *outBind; + rowData *rows; + my_ulonglong count; + enum dbCommandType type; + boolean freeOutParams; +}; - A multi table update - - UPDATE items,month SET items.price=month.price WHERE items.id=month.id; -*/ +void dbFreeRequest(dbRequest *req, boolean all) +{ + int i; + + D("Cleaning up prepared database request %s - %s %d %d", req->table, req->where, req->outCount, req->inCount); + + if (NULL != req->outBind) + { + for (i = 0; i < req->outCount; i++) + { + if (NULL != req->outBind[i].buffer) free(req->outBind[i].buffer); + if (NULL != req->outBind[i].length) free(req->outBind[i].length); + if (NULL != req->outBind[i].error) free(req->outBind[i].error); + if (NULL != req->outBind[i].is_null) free(req->outBind[i].is_null); + } + free(req->outBind); + req->outBind = NULL; + } + else + D(" No out binds to clean up for %s - %s.", req->table, req->where); + if (NULL != req->inBind) + { + for (i = 0; i < req->inCount; i++) + { + if (NULL != req->inBind[i].buffer) free(req->inBind[i].buffer); + if (NULL != req->inBind[i].length) free(req->inBind[i].length); + if (NULL != req->inBind[i].error) free(req->inBind[i].error); + if (NULL != req->inBind[i].is_null) free(req->inBind[i].is_null); + } + free(req->inBind); + req->inBind = NULL; + } + else + D(" No in binds to clean up for %s - %s.", req->table, req->where); + + if (req->freeOutParams && all) + { + if (NULL != req->outParams) + { + free(req->outParams); + req->outParams = NULL; + } + else + D(" No out params to clean up for %s - %s.", req->table, req->where); + } + if (NULL != req->sql) free(req->sql); + else + D(" No SQL to clean up for %s - %s.", req->table, req->where); + req->sql = NULL; + if (NULL != req->prep) + { + if (0 != mysql_stmt_close(req->prep)) + C(" Unable to close the prepared statement!"); + req->prep = NULL; + } + + if (all) + { + if (NULL != req->fields) + { + dbFreeFields(req->fields, all); + req->fields = NULL; + } + else + D(" No fields to clean up for %s - %s.", req->table, req->where); + } +} + +void freeDb(boolean all) +{ + dbRequest **rq; + + if (dbRequests) + { + if (all) + { + while (NULL != (rq = (dbRequest **) dbRequests->popfirst(dbRequests, NULL))) + { + dbFreeRequest(*rq, all); + free(rq); + } + dbRequests->free(dbRequests); + dbRequests = NULL; + } + else + { + qlist_obj_t obj; + + memset((void*)&obj, 0, sizeof(obj)); // must be cleared before call + dbRequests->lock(dbRequests); + while (dbRequests->getnext(dbRequests, &obj, false) == true) + dbFreeRequest(*((dbRequest **) obj.data), all); + dbRequests->unlock(dbRequests); + } + } + + if (database) mysql_close(database); + database = NULL; + mysql_library_end(); +} static boolean dbConnect() { + database = mysql_init(NULL); + if (NULL == database) + { + E("mysql_init() failed - %s", mysql_error(database)); + return FALSE; + } + +/* TODO - dammit, no mysql_get_option(), MariaDB docs say mysql_get_optionv(), which doesn't exist either. + Says "This function was added in MariaDB Connector/C 3.0.0.", I have MariaDB / MySQL client version: 10.1.44-MariaDB. + + if (mysql_get_option(database, MYSQL_OPT_CONNECT_TIMEOUT, &dbTimeout)) + E("mysql_get_option(MYSQL_OPT_CONNECT_TIMEOUT) failed - %s", mysql_error(database)); + else + D("Database MYSQL_OPT_CONNECT_TIMEOUT = %d", dbTimeout); + + if (mysql_get_option(database, MYSQL_OPT_RECONNECT, &dbReconnect)) + E("mysql_get_option(MYSQL_OPT_RECONNECT) failed - %s", mysql_error(database)); + else + D("Database MYSQL_OPT_RECONNECT = %d", (int) dbReconnect); +*/ + + // Seems best to disable auto-reconnect, so I have more control over reconnections. + dbReconnect = 0; + if (mysql_options(database, MYSQL_OPT_RECONNECT, &dbReconnect)) + E("mysql_options(MYSQL_OPT_RECONNECT) failed - %s", mysql_error(database)); + else + D("Database MYSQL_OPT_RECONNECT is now %d", (int) dbReconnect); + dbconn = mysql_real_connect(database, getStrH(configs, "Data Source"), getStrH(configs, "User ID"), @@ -925,6 +1094,21 @@ static boolean dbConnect() E("mysql_real_connect() failed - %s", mysql_error(database)); return FALSE; } + + // Just set the fucking thing. Pffft. + dbTimeout = 60 * 60 * 24 * 7 * 52; +dbTimeout = 100; + char *sql = xmprintf("SET SESSION wait_timeout=%d", (int) dbTimeout); + + if (mysql_query(database, sql)) + E("SET SESSION wait_timeout=%d failed - %s", (int) dbTimeout, mysql_error(database)); + else + D("Database wait_timeout = %d", (int) dbReconnect); + free(sql); + + if (-1 == clock_gettime(CLOCK_REALTIME, &dbLast)) + perror_msg("Unable to get the time."); + return TRUE; } @@ -933,43 +1117,41 @@ static boolean dbConnect() // 1129? 1152? 1184? 1218? 1927 3032? 4150? // "server has gone away" isn't listed there, that's the one I was getting. Pffft // It's 2006, https://dev.mysql.com/doc/refman/8.0/en/gone-away.html -// Though none of the mentioned reasons make sense here. // Ah it could be "connection inactive for 8 hours". // Which might be why OpenSim opens a new connection for EVERYTHING. -// TODO - see if I can either find out what the time out is, or just check and re open for each db thing. -// int mysql_ping(MYSQL * mysql); // https://mariadb.com/kb/en/mysql_ping/ -// "If it has gone down, and global option reconnect is enabled an automatic reconnection is attempted." -// "Returns zero on success, nonzero if an error occured." -// "resources bundled to the connection (prepared statements, locks, temporary tables, ...) will be released." sigh -// Quick'n'dirty until this is properly event driven - have a cron job curl the stats page every hour. -static boolean dbCheckError(MYSQL *db, char *error, char *sql) +// https://dev.mysql.com/doc/refman/5.7/en/c-api-auto-reconnect.html +// Has more details. +static boolean dbCheckError(char *error, char *sql) { - int e = mysql_errno(db); + int e = mysql_errno(database); - E("MariaDB error %d - %s: %s\n%s", e, error, mysql_error(db), sql); + E("MariaDB error %d - %s: %s\n%s", e, error, mysql_error(database), sql); if (2006 == e) + { + W("Reconnecting to database."); + freeDb(false); return dbConnect(); + } return FALSE; } - -typedef struct _dbFields dbFields; -struct _dbFields +// "Statement execute failed 2013: Lost connection to MySQL server during query" +static boolean dbStmtCheckError(dbRequest *req, char *error, char *sql) { - qlisttbl_t *flds; - int count; -}; -typedef struct _dbField dbField; -struct _dbField -{ - char *name; - enum enum_field_types type; - unsigned long length; - unsigned int flags; - unsigned int decimals; -}; + int e = mysql_stmt_errno(req->prep); + + E("MariaDB prepared statement error %d - %s: %s\n%s", e, error, mysql_stmt_error(req->prep), sql); + if (2013 == e) + { + W("Reconnecting to database."); + freeDb(false); + return dbConnect(); + } + + return FALSE; +} -dbFields *dbGetFields(MYSQL *db, char *table) +dbFields *dbGetFields(char *table) { static qhashtbl_t *tables = NULL; if (NULL == tables) tables = qhashtbl(0, 0); @@ -982,33 +1164,33 @@ dbFields *dbGetFields(MYSQL *db, char *table) char *sql = xmprintf("SELECT * FROM %s LIMIT 0", table); d("Getting field metadata for %s", table); - if (mysql_query(db, sql)) + if (mysql_query(database, sql)) { -// E("MariaDB error %d - Query failed 0: %s\n%s", mysql_errno(db), mysql_error(db), sql); - if (dbCheckError(db, "Query failed 0", sql)) +// E("MariaDB error %d - Query failed 0: %s\n%s", mysql_errno(database), mysql_error(database), sql); + if (dbCheckError("Query failed 0", sql)) { - ret = dbGetFields(db, table); + ret = dbGetFields(table); free(sql); return ret; } } else { - MYSQL_RES *res = mysql_store_result(db); + MYSQL_RES *res = mysql_store_result(database); if (!res) - E("MariaDB error %d - Couldn't get results set from %s\n %s", mysql_errno(db), mysql_error(db), sql); + E("MariaDB error %d - Couldn't get results set from %s\n %s", mysql_errno(database), mysql_error(database), sql); else { MYSQL_FIELD *fields = mysql_fetch_fields(res); if (!fields) - E("MariaDB error %d - Failed fetching fields: %s", mysql_errno(db), mysql_error(db)); + E("MariaDB error %d - Failed fetching fields: %s", mysql_errno(database), mysql_error(database)); else { unsigned int i, num_fields = mysql_num_fields(res); - ret = xmalloc(sizeof(dbFields)); + ret = xmalloc(sizeof(dbFields)); // Little bit LEAKY ret->flds = qlisttbl(QLISTTBL_UNIQUE | QLISTTBL_LOOKUPFORWARD); ret->count = 1; for (i = 0; i < num_fields; i++) @@ -1035,62 +1217,61 @@ d("Getting field metadata for %s", table); return ret; } -void dbFreeFields(dbFields *flds) -{ - flds->count--; - if (0 >= flds->count) - { - qlisttbl_obj_t obj; - memset((void *) &obj, 0, sizeof(obj)); - flds->flds->lock(flds->flds); - while(flds->flds->getnext(flds->flds, &obj, NULL, false) == true) - { - dbField *fld = (dbField *) obj.data; - free(fld->name); - } - flds->flds->unlock(flds->flds); - flds->flds->free(flds->flds); - free(flds); - } -} +/* How to deal with prepared SQL statements. +http://karlssonondatabases.blogspot.com/2010/07/prepared-statements-are-they-useful-or.html +https://blog.cotten.io/a-taste-of-mysql-in-c-87c5de84a31d?gi=ab3dd1425b29 +https://raspberry-projects.com/pi/programming-in-c/databases-programming-in-c/mysql/accessing-the-database -enum dbCommandType -{ - CT_SELECT, - CT_CREATE, - CT_UPDATE, - CT_NONE -}; +IG and CG now both have sims connected to other grids, so some sort of +multi database solution would be good, then we can run the grid and the +external sims all in one. -typedef struct _dbRequest dbRequest; -struct _dbRequest -{ - MYSQL *db; - char *table, *join, *where, *order, *sql; - MYSQL_STMT *prep; // NOTE - executing it stores state in this. - dbFields *fields; - qlisttbl_t *flds; - int inCount, outCount, rowCount; - char **inParams, **outParams; - MYSQL_BIND *inBind, *outBind; - rowData *rows; - my_ulonglong count; - enum dbCommandType type; - boolean freeOutParams; -}; +Not sure if this'll work with Count(*). + +--------------------------------------------- + +The complicated bit is the binds. + +You are binding field values to C memory locations. +The parameters and returned fields need binds. +Mostly seems to be the value parts of the SQL statements. + +I suspect most will be of the form - + ... WHERE x=? and foo=? + INSERT INTO table VALUES (?,?,?) + UPDATE table SET x=?, foo=? WHERE id=? + + A multi table update - + UPDATE items,month SET items.price=month.price WHERE items.id=month.id; +*/ int dbDoSomething(dbRequest *req, boolean count, ...) { int ret = 0; va_list ap; - struct timespec now, then; + struct timespec then; int i, j; MYSQL_RES *prepare_meta_result = NULL; if (-1 == clock_gettime(CLOCK_REALTIME, &then)) perror_msg("Unable to get the time."); + double n = (dbLast.tv_sec * 1000000000.0) + dbLast.tv_nsec; + double t = (then.tv_sec * 1000000000.0) + then.tv_nsec; + +t("Database timeout test %lf > %lf", ((t - n) / 1000000000.0), (dbTimeout / 2.0)); + if (((t - n) / 1000000000.0) > (dbTimeout / 2.0)) + { + T("Avoid database timeout of %d seconds, pinging it.", dbTimeout); + if (0 != mysql_ping(database)) + { + W("Reconnecting to database."); + freeDb(false); + dbConnect(); + } + } + va_start(ap, count); if (NULL == req->prep) @@ -1100,14 +1281,13 @@ int dbDoSomething(dbRequest *req, boolean count, ...) if (0 == req->type) req->type = CT_SELECT; - req->fields = dbGetFields(req->db, req->table); + req->fields = dbGetFields(req->table); if (NULL == req->fields) { E("Unknown fields for table %s.", req->table); ret++; goto end; } - req->flds = req->fields->flds; switch (req->type) { @@ -1189,7 +1369,7 @@ int dbDoSomething(dbRequest *req, boolean count, ...) d("New SQL statement - %s", req->sql); // prepare statement with the other fields - req->prep = mysql_stmt_init(req->db); + req->prep = mysql_stmt_init(database); if (NULL == req->prep) { E("Statement prepare init failed: %s\n", mysql_stmt_error(req->prep)); @@ -1218,7 +1398,7 @@ d("New SQL statement - %s", req->sql); //W("Allocated %d %d inBinds for %s", i, req->inCount, req->sql); for (i = 0; i < req->inCount; i++) { - dbField *fld = req->flds->get(req->flds, req->inParams[i], NULL, false); + dbField *fld = req->fields->flds->get(req->fields->flds, req->inParams[i], NULL, false); if (NULL == fld) { @@ -1359,15 +1539,15 @@ I("count!!!!!!!!!!!!!!!!"); req->freeOutParams = TRUE; qlisttbl_obj_t obj; memset((void*)&obj, 0, sizeof(obj)); - req->flds->lock(req->flds); - while (req->flds->getnext(req->flds, &obj, NULL, false) == true) + req->fields->flds->lock(req->fields->flds); + while (req->fields->flds->getnext(req->fields->flds, &obj, NULL, false) == true) { dbField *fld = (dbField *) obj.data; req->outParams[i] = fld->name; i++; } req->outParams[i] = NULL; - req->flds->unlock(req->flds); + req->fields->flds->unlock(req->fields->flds); } if (i != req->outCount) { @@ -1379,7 +1559,7 @@ I("count!!!!!!!!!!!!!!!!"); //W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); for (i = 0; i < req->outCount; i++) { - dbField *fld = req->flds->get(req->flds, req->outParams[i], NULL, false); + dbField *fld = req->fields->flds->get(req->fields->flds, req->outParams[i], NULL, false); if (NULL == fld) { @@ -1503,7 +1683,7 @@ I("count!!!!!!!!!!!!!!!!"); //d("input bind for %s", req->sql); for (i = 0; i < req->inCount; i++) { - dbField *fld = req->flds->get(req->flds, req->inParams[i], NULL, false); + dbField *fld = req->fields->flds->get(req->fields->flds, req->inParams[i], NULL, false); if (NULL == fld) { @@ -1651,9 +1831,11 @@ I("count!!!!!!!!!!!!!!!!"); // do the prepared statement req->prep. if (mysql_stmt_execute(req->prep)) { - E("Statement execute failed %d: %s\n", mysql_stmt_errno(req->prep), mysql_stmt_error(req->prep)); - ret++; - goto freeIt; + if (dbStmtCheckError(req, "Statement failed 0", req->sql)) + { + ret++; + goto freeIt; + } } int fs = mysql_stmt_field_count(req->prep); @@ -1681,7 +1863,7 @@ I("count!!!!!!!!!!!!!!!!"); for (i = 0; i < req->outCount; i++) { - dbField *fld = req->flds->get(req->flds, req->outParams[i], NULL, false); + dbField *fld = req->fields->flds->get(req->fields->flds, req->outParams[i], NULL, false); req->rows->fieldNames[i] = fld->name; if (!*(req->outBind[i].is_null)) @@ -1803,10 +1985,9 @@ freeIt: end: va_end(ap); - if (-1 == clock_gettime(CLOCK_REALTIME, &now)) + if (-1 == clock_gettime(CLOCK_REALTIME, &dbLast)) perror_msg("Unable to get the time."); - double n = (now.tv_sec * 1000000000.0) + now.tv_nsec; - double t = (then.tv_sec * 1000000000.0) + then.tv_nsec; + n = (dbLast.tv_sec * 1000000000.0) + dbLast.tv_nsec; T("dbDoSomething(%s) took %lf seconds", req->sql, (n - t) / 1000000000.0); return ret; @@ -1838,62 +2019,7 @@ d("dbPull(Rd->database) %s = %s", where, (char *) obj.data); free(rows); } -void dbFreeRequest(dbRequest *req) -{ - int i; - - D("Cleaning up prepared database request %s - %s %d %d", req->table, req->where, req->outCount, req->inCount); - - if (NULL != req->fields) - { - dbFreeFields(req->fields); - req->fields = NULL; - } - else - D(" No fields to clean up for %s - %s.", req->table, req->where); - - if (NULL != req->outBind) - { - for (i = 0; i < req->outCount; i++) - { - if (NULL != req->outBind[i].buffer) free(req->outBind[i].buffer); - if (NULL != req->outBind[i].length) free(req->outBind[i].length); - if (NULL != req->outBind[i].error) free(req->outBind[i].error); - if (NULL != req->outBind[i].is_null) free(req->outBind[i].is_null); - } - free(req->outBind); - } - else - D(" No out binds to clean up for %s - %s.", req->table, req->where); - if (NULL != req->inBind) - { - for (i = 0; i < req->inCount; i++) - { - if (NULL != req->inBind[i].buffer) free(req->inBind[i].buffer); - if (NULL != req->inBind[i].length) free(req->inBind[i].length); - if (NULL != req->inBind[i].error) free(req->inBind[i].error); - if (NULL != req->inBind[i].is_null) free(req->inBind[i].is_null); - } - free(req->inBind); - } - else - D(" No in binds to clean up for %s - %s.", req->table, req->where); - - if (req->freeOutParams) free(req->outParams); - else - D(" No out params to clean up for %s - %s.", req->table, req->where); - if (NULL != req->sql) free(req->sql); - else - D(" No SQL to clean up for %s - %s.", req->table, req->where); - if (NULL != req->prep) - { - if (0 != mysql_stmt_close(req->prep)) - C(" Unable to close the prepared statement!"); - req->prep = NULL; - } -} - -my_ulonglong dbCount(MYSQL *db, char *table, char *where) +my_ulonglong dbCount(char *table, char *where) { my_ulonglong ret = 0; char *sql; @@ -1907,27 +2033,27 @@ my_ulonglong dbCount(MYSQL *db, char *table, char *where) else sql = xmprintf("SELECT Count(*) FROM %s", table); - if (mysql_query(db, sql)) + if (mysql_query(database, sql)) { -// E("MariaDB error %d - Query failed 1: %s", mysql_errno(db), mysql_error(db)); - if (dbCheckError(db, "Query failed 1", sql)) +// E("MariaDB error %d - Query failed 1: %s", mysql_errno(database), mysql_error(database)); + if (dbCheckError("Query failed 1", sql)) { - ret = dbCount(db, table, where); + ret = dbCount(table, where); free(sql); return ret; } } else { - MYSQL_RES *result = mysql_store_result(db); + MYSQL_RES *result = mysql_store_result(database); if (!result) - E("Couldn't get results set from %s\n: %s", sql, mysql_error(db)); + E("Couldn't get results set from %s\n: %s", sql, mysql_error(database)); else { MYSQL_ROW row = mysql_fetch_row(result); if (!row) - E("MariaDB error %d - Couldn't get row from %s\n: %s", mysql_errno(db), sql, mysql_error(db)); + E("MariaDB error %d - Couldn't get row from %s\n: %s", mysql_errno(database), sql, mysql_error(database)); else ret = atoll(row[0]); mysql_free_result(result); @@ -1943,7 +2069,7 @@ my_ulonglong dbCount(MYSQL *db, char *table, char *where) return ret; } -my_ulonglong dbCountJoin(MYSQL *db, char *table, char *select, char *join, char *where) +my_ulonglong dbCountJoin(char *table, char *select, char *join, char *where) { my_ulonglong ret = 0; char *sql; @@ -1962,22 +2088,22 @@ my_ulonglong dbCountJoin(MYSQL *db, char *table, char *select, char *join, char else sql = xmprintf("SELECT %s FROM %s", select, table, join); - if (mysql_query(db, sql)) + if (mysql_query(database, sql)) { -// E("MariaDB error %d - Query failed 2: %s", mysql_errno(db), mysql_error(db)); - if (dbCheckError(db, "Query failed 2", sql)) +// E("MariaDB error %d - Query failed 2: %s", mysql_errno(database), mysql_error(database)); + if (dbCheckError("Query failed 2", sql)) { - ret = dbCountJoin(db, table, select, join, where); + ret = dbCountJoin(table, select, join, where); free(sql); return ret; } } else { - MYSQL_RES *result = mysql_store_result(db); + MYSQL_RES *result = mysql_store_result(database); if (!result) - E("MariaDB error %d - Couldn't get results set from %s\n: %s", mysql_errno(db), sql, mysql_error(db)); + E("MariaDB error %d - Couldn't get results set from %s\n: %s", mysql_errno(database), sql, mysql_error(database)); else ret = mysql_num_rows(result); mysql_free_result(result); @@ -1992,51 +2118,6 @@ my_ulonglong dbCountJoin(MYSQL *db, char *table, char *select, char *join, char return ret; } -MYSQL_RES *dbSelect(MYSQL *db, char *table, char *select, char *join, char *where, char *order) -{ - MYSQL_RES *ret = NULL; - char *sql; - struct timespec now, then; - - if (-1 == clock_gettime(CLOCK_REALTIME, &then)) - perror_msg("Unable to get the time."); - - if (NULL == select) - select = "*"; - if (NULL == join) - join = ""; - - if (where) - sql = xmprintf("SELECT %s FROM %s %s WHERE %s", select, table, join, where); - else - sql = xmprintf("SELECT %s FROM %s", select, table, join); - - if (order) - { - char *t = xmprintf("%s ORDER BY %s", sql, order); - - free(sql); - sql = t; - } - - if (mysql_query(db, sql)) - E("MariaDB error %d - Query failed 3: %s\n%s", mysql_errno(db), mysql_error(db), sql); - else - { - ret = mysql_store_result(db); - if (!ret) - E("MariaDB error %d - Couldn't get results set from %s\n %s", mysql_errno(db), mysql_error(db), sql); - } - - if (-1 == clock_gettime(CLOCK_REALTIME, &now)) - perror_msg("Unable to get the time."); - double n = (now.tv_sec * 1000000000.0) + now.tv_nsec; - double t = (then.tv_sec * 1000000000.0) + then.tv_nsec; - T("dbSelect(%s) took %lf seconds", sql, (n - t) / 1000000000.0); - free(sql); - return ret; -} - void replaceStr(qhashtbl_t *ssi, char *key, char *value) { @@ -2105,91 +2186,88 @@ gridStats *getStats(MYSQL *db, gridStats *stats) replaceStr(stats->stats, "gridOnline", "online"); else replaceStr(stats->stats, "gridOnline", "offline"); - if (db) - { - char *tmp; - my_ulonglong locIn = dbCount(db, "Presence", "RegionID != '00000000-0000-0000-0000-000000000000'"); // Locals online but not HGing, and HGers in world. - my_ulonglong HGin = dbCount(db, "Presence", "UserID NOT IN (SELECT PrincipalID FROM UserAccounts)"); // HGers in world. - // Collect stats about members. - replaceLong(stats->stats, "hgers", HGin); - if (locIn >= HGin) // Does OpenSim have too many ghosts? - replaceLong(stats->stats, "inworld", locIn - HGin); - else - replaceLong(stats->stats, "inworld", 0); - tmp = xmprintf("GridExternalName != '%s'", stats->stats->getstr(stats->stats, "uri", false)); - replaceLong(stats->stats, "outworld", dbCount(db, "hg_traveling_data", tmp)); - free(tmp); - replaceLong(stats->stats, "members", dbCount(db, "UserAccounts", NULL)); - - // Count local and HG visitors for the last 30 and 60 days. - locIn = dbCountJoin(db, "GridUser", "GridUser.UserID", "INNER JOIN UserAccounts ON GridUser.UserID = UserAccounts.PrincipalID", - "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 2419200))"); - HGin = dbCount(db, "GridUser", "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 2419200))"); - replaceLong(stats->stats, "locDay30", locIn); - replaceLong(stats->stats, "day30", HGin); - replaceLong(stats->stats, "HGday30", HGin - locIn); - - locIn = dbCountJoin(db, "GridUser", "GridUser.UserID", "INNER JOIN UserAccounts ON GridUser.UserID = UserAccounts.PrincipalID", - "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 4838400))"); - HGin = dbCount(db, "GridUser", "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 4838400))"); - replaceLong(stats->stats, "locDay60", locIn); - replaceLong(stats->stats, "day60", HGin); - replaceLong(stats->stats, "HGday60", HGin - locIn); - - // Collect stats about sims. - replaceLong(stats->stats, "sims", dbCount(db, "regions", NULL)); - replaceLong(stats->stats, "onlineSims", dbCount(db, "regions", "sizeX != 0")); - replaceLong(stats->stats, "varRegions", dbCount(db, "regions", "sizeX > 256 or sizeY > 256")); - replaceLong(stats->stats, "singleSims", dbCount(db, "regions", "sizeX = 256 and sizeY = 256")); - replaceLong(stats->stats, "offlineSims", dbCount(db, "regions", "sizeX = 0")); - - // Calculate total size of all regions. - my_ulonglong simSize = 0; - static dbRequest *rgnSizes = NULL; - if (NULL == rgnSizes) - { - static char *szi[] = {NULL}; - static char *szo[] = {"sizeX", "sizeY", NULL}; - rgnSizes = xzalloc(sizeof(dbRequest)); - rgnSizes->db = db; - rgnSizes->table = "regions"; - rgnSizes->inParams = szi; - rgnSizes->outParams = szo; - rgnSizes->where = "sizeX != 0"; - dbRequests->addfirst(dbRequests, &rgnSizes, sizeof(dbRequest *)); - } - dbDoSomething(rgnSizes, FALSE); - rowData *rows = rgnSizes->rows; + char *tmp; + my_ulonglong locIn = dbCount("Presence", "RegionID != '00000000-0000-0000-0000-000000000000'"); // Locals online but not HGing, and HGers in world. + my_ulonglong HGin = dbCount("Presence", "UserID NOT IN (SELECT PrincipalID FROM UserAccounts)"); // HGers in world. - qhashtbl_t *row; - while (NULL != (row = rows->rows->getat(rows->rows, 0, NULL, true))) - { - my_ulonglong x = 0, y = 0; + // Collect stats about members. + replaceLong(stats->stats, "hgers", HGin); + if (locIn >= HGin) // Does OpenSim have too many ghosts? + replaceLong(stats->stats, "inworld", locIn - HGin); + else + replaceLong(stats->stats, "inworld", 0); + tmp = xmprintf("GridExternalName != '%s'", stats->stats->getstr(stats->stats, "uri", false)); + replaceLong(stats->stats, "outworld", dbCount("hg_traveling_data", tmp)); + free(tmp); + replaceLong(stats->stats, "members", dbCount("UserAccounts", NULL)); - tmp = row->getstr(row, "sizeX", false); - if (NULL == tmp) - E("No regions.sizeX!"); - else - x = atoll(tmp); - tmp = row->getstr(row, "sizeY", false); - if (NULL == tmp) - E("No regions.sizeY!"); - else - y = atoll(tmp); - simSize += x * y; - row->free(row); - rows->rows->removefirst(rows->rows); - } - free(rows->fieldNames); - rows->rows->free(rows->rows); - free(rows); + // Count local and HG visitors for the last 30 and 60 days. + locIn = dbCountJoin("GridUser", "GridUser.UserID", "INNER JOIN UserAccounts ON GridUser.UserID = UserAccounts.PrincipalID", + "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 2419200))"); + HGin = dbCount("GridUser", "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 2419200))"); + replaceLong(stats->stats, "locDay30", locIn); + replaceLong(stats->stats, "day30", HGin); + replaceLong(stats->stats, "HGday30", HGin - locIn); - tmp = xmprintf("%lu", simSize); - stats->stats->putstr(stats->stats, "simsSize", tmp); - free(tmp); - gettimeofday(&(stats->last), NULL); + locIn = dbCountJoin("GridUser", "GridUser.UserID", "INNER JOIN UserAccounts ON GridUser.UserID = UserAccounts.PrincipalID", + "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 4838400))"); + HGin = dbCount("GridUser", "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 4838400))"); + replaceLong(stats->stats, "locDay60", locIn); + replaceLong(stats->stats, "day60", HGin); + replaceLong(stats->stats, "HGday60", HGin - locIn); + + // Collect stats about sims. + replaceLong(stats->stats, "sims", dbCount("regions", NULL)); + replaceLong(stats->stats, "onlineSims", dbCount("regions", "sizeX != 0")); + replaceLong(stats->stats, "varRegions", dbCount("regions", "sizeX > 256 or sizeY > 256")); + replaceLong(stats->stats, "singleSims", dbCount("regions", "sizeX = 256 and sizeY = 256")); + replaceLong(stats->stats, "offlineSims", dbCount("regions", "sizeX = 0")); + + // Calculate total size of all regions. + my_ulonglong simSize = 0; + static dbRequest *rgnSizes = NULL; + if (NULL == rgnSizes) + { + static char *szi[] = {NULL}; + static char *szo[] = {"sizeX", "sizeY", NULL}; + rgnSizes = xzalloc(sizeof(dbRequest)); + rgnSizes->table = "regions"; + rgnSizes->inParams = szi; + rgnSizes->outParams = szo; + rgnSizes->where = "sizeX != 0"; + dbRequests->addfirst(dbRequests, &rgnSizes, sizeof(dbRequest *)); + } + dbDoSomething(rgnSizes, FALSE); // LEAKY + rowData *rows = rgnSizes->rows; + + qhashtbl_t *row; + while (NULL != (row = rows->rows->getat(rows->rows, 0, NULL, true))) + { + my_ulonglong x = 0, y = 0; + + tmp = row->getstr(row, "sizeX", false); + if (NULL == tmp) + E("No regions.sizeX!"); + else + x = atoll(tmp); + tmp = row->getstr(row, "sizeY", false); + if (NULL == tmp) + E("No regions.sizeY!"); + else + y = atoll(tmp); + simSize += x * y; + row->free(row); + rows->rows->removefirst(rows->rows); } + free(rows->fieldNames); + rows->rows->free(rows->rows); + free(rows); + + tmp = xmprintf("%lu", simSize); + stats->stats->putstr(stats->stats, "simsSize", tmp); + free(tmp); + gettimeofday(&(stats->last), NULL); return stats; } @@ -3587,7 +3665,7 @@ static void generateAccountUUID(reqData *Rd) // Try database. where = xmprintf("UserAccounts.PrincipalID = '%s'", uuid); D("Trying new UUID %s.", where); - users = dbCount(Rd->db, "UserAccounts", where); + users = dbCount("UserAccounts", where); free(where); } while (users != 0); if (NULL != Rd->shs.UUID) free(Rd->shs.UUID); @@ -3828,7 +3906,6 @@ notWritten: }; static char *szo[] = {NULL}; acntsI = xzalloc(sizeof(dbRequest)); - acntsI->db = Rd->db; acntsI->table = "UserAccounts"; acntsI->inParams = szi; acntsI->outParams = szo; @@ -3842,7 +3919,6 @@ notWritten: static char *szi[] = {"UUID", "passwordSalt", "passwordHash", "accountType", "webLoginKey", NULL}; static char *szo[] = {NULL}; authI = xzalloc(sizeof(dbRequest)); - authI->db = Rd->db; authI->table = "auth"; authI->inParams = szi; authI->outParams = szo; @@ -3865,7 +3941,6 @@ notWritten: }; static char *szo[] = {NULL}; invFolderI = xzalloc(sizeof(dbRequest)); - invFolderI->db = Rd->db; invFolderI->table = "inventoryfolders"; invFolderI->inParams = szi; invFolderI->outParams = szo; @@ -3880,7 +3955,6 @@ notWritten: static char *szi[] = {"UserID", NULL}; // All the defaults are what we would set anyway. static char *szo[] = {NULL}; gUserI = xzalloc(sizeof(dbRequest)); - gUserI->db = Rd->db; gUserI->table = "GridUser"; gUserI->inParams = szi; gUserI->outParams = szo; @@ -3922,7 +3996,7 @@ notWritten: uuid_unparse_lower(binuuidI, uuidI); // TODO - should check there isn't a folder with this UUID already. D("Creating %s inventory folder for user %s.", sysFolders[i].name, getStrH(Rd->stuff, "name")); - r += dbDoSomething(invFolderI, FALSE, uuid, sysFolders[i].name, sysFolders[i].type, 1, uuidI, uuidR); + r += dbDoSomething(invFolderI, FALSE, uuid, sysFolders[i].name, sysFolders[i].type, 1, uuidI, uuidR); // LEAKY if (0 != r) bitch(Rd, "Internal error.", "Failed to create invenoryFolder record."); if (strcmp("My Inventory", sysFolders[i].name) == 0) @@ -3965,7 +4039,7 @@ notWritten: { // Create location record. D("Creating home and last positions for user %s.", getStrH(Rd->stuff, "name")); - if (0 != dbDoSomething(gUserI, FALSE, uuid)) + if (0 != dbDoSomething(gUserI, FALSE, uuid)) // LEAKY bitch(Rd, "Internal error.", "Failed to create GridUser record."); else { @@ -5055,7 +5129,6 @@ static int accountRead(reqData *Rd, char *uuid, char *firstName, char *lastName) static char *szi[] = {"PrincipalID", NULL}; static char *szo[] = {NULL}; uuids = xzalloc(sizeof(dbRequest)); - uuids->db = Rd->db; uuids->table = "UserAccounts"; uuids->inParams = szi; uuids->outParams = szo; @@ -5068,7 +5141,6 @@ static int accountRead(reqData *Rd, char *uuid, char *firstName, char *lastName) static char *szi[] = {"FirstName", "LastName", NULL}; static char *szo[] = {NULL}; acnts = xzalloc(sizeof(dbRequest)); - acnts->db = Rd->db; acnts->table = "UserAccounts"; acnts->inParams = szi; acnts->outParams = szo; @@ -5081,7 +5153,6 @@ static int accountRead(reqData *Rd, char *uuid, char *firstName, char *lastName) static char *szi[] = {"UUID", NULL}; static char *szo[] = {"passwordSalt", "passwordHash", NULL}; auth = xzalloc(sizeof(dbRequest)); - auth->db = Rd->db; auth->table = "auth"; auth->inParams = szi; auth->outParams = szo; @@ -5145,7 +5216,7 @@ d("accountRead() UUID %s, name %s %s", uuid, first, last); rt = LuaToHash(Rd, where, "user", tnm, ret, &st, &now, "user"); free(where); - dbDoSomething(acnts, FALSE, first, last); + dbDoSomething(acnts, FALSE, first, last); // LEAKY rows = acnts->rows; } } @@ -5194,7 +5265,7 @@ T("Found database record."); Rd->fromDb = TRUE; Rd->database->putstr(Rd->database, "Lua.name", name); free(name); - dbDoSomething(auth, FALSE, getStrH(Rd->database, "UserAccounts.PrincipalID")); + dbDoSomething(auth, FALSE, getStrH(Rd->database, "UserAccounts.PrincipalID")); // LEAKY rows = auth->rows; if (rows) { @@ -6263,18 +6334,6 @@ static void cleanup(void) { // TODO - not sure why, but this gets called twice on quitting sometimes. C("Caught signal, or quitting, cleaning up."); - dbRequest **rq; - - if (dbRequests) - { - while (NULL != (rq = (dbRequest **) dbRequests->popfirst(dbRequests, NULL))) - { - dbFreeRequest(*rq); - free(rq); - } - dbRequests->free(dbRequests); - dbRequests = NULL; - } if (accountPages) { @@ -6326,9 +6385,7 @@ static void cleanup(void) } if (mimeTypes) mimeTypes->free(mimeTypes); mimeTypes = NULL; - if (database) mysql_close(database); - database = NULL; - mysql_library_end(); + freeDb(true); if (L) lua_close(L); L = NULL; if (stats) @@ -6671,28 +6728,19 @@ jit library is loaded or the JIT compiler will not be activated. E("mysql_library_init() failed!"); goto finished; } - database = mysql_init(NULL); - if (NULL == database) - { - E("mysql_init() failed - %s", mysql_error(database)); + if (!dbConnect()) goto finished; - } - else - { - if (!dbConnect()) - goto finished; - // Need to kick this off. - stats = getStats(database, stats); - char *h = qstrunchar(qconfig->getstr(qconfig, "Const.HostName", false), '"', '"'); - char *p = qstrunchar(qconfig->getstr(qconfig, "Const.PublicPort", false), '"', '"'); - stats->stats->putstr(stats->stats, "grid", qstrunchar(qconfig->getstr(qconfig, "Const.GridName", false), '"', '"')); - stats->stats->putstr(stats->stats, "HostName", h); - stats->stats->putstr(stats->stats, "PublicPort", p); - snprintf(toybuf, sizeof(toybuf), "http://%s:%s/", h, p); + // Need to kick this off. + stats = getStats(database, stats); + char *h = qstrunchar(qconfig->getstr(qconfig, "Const.HostName", false), '"', '"'); + char *p = qstrunchar(qconfig->getstr(qconfig, "Const.PublicPort", false), '"', '"'); + stats->stats->putstr(stats->stats, "grid", qstrunchar(qconfig->getstr(qconfig, "Const.GridName", false), '"', '"')); + stats->stats->putstr(stats->stats, "HostName", h); + stats->stats->putstr(stats->stats, "PublicPort", p); + snprintf(toybuf, sizeof(toybuf), "http://%s:%s/", h, p); - stats->stats->putstr(stats->stats, "uri", toybuf); - } + stats->stats->putstr(stats->stats, "uri", toybuf); qconfig->free(qconfig); } @@ -6746,7 +6794,6 @@ jit library is loaded or the JIT compiler will not be activated. Rd->database = qhashtbl(0, 0); Rd->Rcookies = qhashtbl(0, 0); Rd->Rheaders = qhashtbl(0, 0); - Rd->db = database; Rd->stats = stats; Rd->errors = qlist(0); Rd->messages = qlist(0); -- cgit v1.1