sql.c

   1 #define _GNU_SOURCE 1
   2 #include "daemon.h"
   3
   4 #include <stdio.h> /* debuggering only. */
   5
   6 /* where to (optionally) stash performance data */
   7 char *host_perf_table = NULL;
   8 char *service_perf_table = NULL;
   9 int sql_table_crashed = 0;
  10 static long int commit_interval, commit_queries;
  11 static time_t last_commit;
  12 unsigned long total_queries = 0;
  13 static int db_type;
  14
  15 #define MERLIN_DBT_MYSQL 0
  16 #define MERLIN_DBT_PGSQL 2
  17 #define MERLIN_DBT_SQLITE 3
  18
  19 /*
  20  * File-scoped definition of the database settings we've tried
  21  * (or succeeded) connecting with
  22  */
  23 static struct {
  24         char const *host;
  25         char const *name;
  26         char const *user;
  27         char const *pass;
  28         char const *table;
  29         char const *type;
  30         char const *encoding;
  31         char const *conn_str;
  32         int port; /* signed int for compatibility with dbi_conn_set_option_numeric() (and similar)*/
  33         db_wrap *conn;
  34         db_wrap_result * result;
  35         int logSQL;
  36 } db = {
  37 NULL/*host*/,
  38 NULL/*name*/,
  39 NULL/*user*/,
  40 NULL/*pass*/,
  41 NULL/*table*/,
  42 NULL/*type*/,
  43 NULL/*encoding*/,
  44 NULL/*conn_str*/,
  45 0U/*port*/,
  46 NULL/*conn*/,
  47 NULL/*result*/,
  48 0/*logSQL*/
  49 };
  50
  51
  52
  53 /*
  54  * Quotes a string and escapes all meta-characters inside the string.
  55  * If src is NULL or !*src then 0 is returned and *dest is not modified.
  56  * *dst must be free()'d by the caller.
  57  */
  58 void sql_quote(const char *src, char **dst)
  59 {
  60         size_t ret;
  61
  62         if (!sql_is_connected(1)) {
  63                 *dst = NULL;
  64         }
  65
  66         assert(db.conn != NULL);
  67         ret = db.conn->api->sql_quote(db.conn, src, (src?strlen(src):0U), dst);
  68         if (!ret) {
  69                 *dst = NULL;
  70         }
  71 }
  72
  73 /*
  74  * these two functions are only here to allow callers
  75  * access to error reporting without having to expose
  76  * the db-link to theh callers. It's also nifty as we
  77  * want to remain database layer agnostic.
  78  *
  79  * The returned bytes are owned by the underlying DB driver and are
  80  * not guaranteed to be valid after the next call into the db
  81  * driver. It is up to the client to copy them, if needed, BEFORE
  82  * making ANY other calls into the DB API.
  83  */
  84 int sql_error(const char **msg)
  85 {
  86         int dbrc = 0;
  87
  88         if (!db.conn) {
  89                 *msg = "no database connection";
  90                 return DB_WRAP_E_UNKNOWN_ERROR;
  91         }
  92
  93         db.conn->api->error_info(db.conn, msg, NULL, &dbrc);
  94         return dbrc;
  95 }
  96
  97 /**
  98  * Convenience form of sql_error() which returns the error
  99  * string directly, or returns an unspecified string if
 100  * no connection is established.
 101  */
 102 const char *sql_error_msg(void)
 103 {
 104         const char *msg = NULL;
 105         sql_error(&msg);
 106         return msg;
 107 }
 108
 109 void sql_free_result(void)
 110 {
 111         if (db.result) {
 112                 db.result->api->finalize(db.result);
 113                 db.result = NULL;
 114         }
 115 }
 116
 117 db_wrap_result * sql_get_result(void)
 118 {
 119         return db.result;
 120 }
 121
 122 void sql_try_commit(int query)
 123 {
 124         static int queries;
 125         time_t now = time(NULL);
 126
 127         if (!db.conn || !use_database || !db.conn->api->commit)
 128                 return;
 129
 130         if (query > 0)
 131                 queries += query;
 132
 133         if (queries &&
 134             (query == -1 ||
 135              (commit_interval && last_commit + commit_interval <= now) ||
 136              (commit_queries && queries >= commit_queries)
 137             )
 138            )
 139         {
 140                 ldebug("Committing %d queries", queries);
 141                 /*
 142                  * we ignore the return value here, as each db
 143                  * seems to return a different code on success
 144                  * and failure. Bleh...
 145                  */
 146                 (void)db.conn->api->commit(db.conn);
 147                 last_commit = now;
 148                 total_queries += queries;
 149                 queries = 0;
 150         }
 151 }
 152
 153 static int run_query(char *query, size_t len, int rerunIGNORED)
 154 {
 155         db_wrap_result *res = NULL;
 156         int rc;
 157
 158         if (!db.conn && sql_init() < 0) {
 159                 lerr("DB: No connection. Skipping query [%s]\n", query);
 160                 return -1;
 161         }
 162
 163         assert(db.conn != NULL);
 164         rc = db.conn->api->query_result(db.conn, query, len, &res);
 165
 166         if (db.logSQL) {
 167                 ldebug("MERLIN SQL: [%s]\n\tResult code: %d, result object @%p\n", query, rc, res);
 168                 if (rc) {
 169                         ldebug("Error code: %d\n", rc);
 170                 }
 171         }
 172
 173         if (rc) {
 174                 assert(NULL == res);
 175                 return rc;
 176         }
 177
 178         sql_try_commit(1);
 179
 180         assert(res != NULL);
 181         assert(db.result == NULL);
 182         db.result = res;
 183         return rc;
 184 }
 185
 186 void sql_log_crashed(char *query)
 187 {
 188         static time_t now, last_log = 0;
 189
 190         sql_table_crashed = 1;
 191
 192         now = time(NULL);
 193         if (last_log + 30 > now)
 194                 return;
 195
 196         last_log = now;
 197         lerr("FATAL: One or more of your SQL tables have crashed. Please run 'mysqlrepair %s'",
 198                  sql_db_name());
 199         lerr("  Query was: %s", query);
 200 }
 201
 202 int sql_vquery(const char *fmt, va_list ap)
 203 {
 204         int len;
 205         char *query;
 206
 207         if (!fmt)
 208                 return 0;
 209
 210         if (!use_database) {
 211                 return -1;
 212         }
 213
 214         /*
 215          * don't even bother trying to run the query if the database
 216          * isn't online and we recently tried to connect to it
 217          */
 218         if (!sql_is_connected(1)) {
 219                 ldebug("DB: Not connected and re-init failed. Skipping query");
 220                 return -1;
 221         }
 222
 223         /* free any leftover result and run the new query */
 224         sql_free_result();
 225
 226         len = vasprintf(&query, fmt, ap);
 227         if (len == -1 || !query) {
 228                 lerr("sql_query: Failed to build query from format-string '%s'", fmt);
 229                 return -1;
 230         }
 231
 232         if (run_query(query, len, 0) != 0) {
 233                 const char *error_msg;
 234                 int db_error = sql_error(&error_msg);
 235                 int reconnect = 0;
 236
 237                 /*
 238                  * "table crashed" can get *very* spammy, so we put that in
 239                  * a logging function of its own
 240                  */
 241                 if (db_type != MERLIN_DBT_MYSQL ||
 242                         (db_error != 145 && db_error != 1194 && db_error != 1195))
 243                 {
 244                         lerr("Failed to run query [%s] due to error-code %d: %s",
 245                                  query, db_error, error_msg);
 246                 }
 247 #ifdef ENABLE_LIBDBI
 248                 if (db_type == MERLIN_DBT_MYSQL) {
 249                         /*
 250                          * if we failed because the connection has gone away, we try
 251                          * reconnecting once and rerunning the query before giving up.
 252                          * Otherwise we just ignore it and go on
 253                          */
 254                         switch (db_error) {
 255                         case 1062: /* duplicate key */
 256                         case 1068: /* duplicate primary key */
 257                         case 1146: /* table missing */
 258                         case 2029: /* null pointer */
 259                                 break;
 260
 261                         case 145: /* crashed table. ugh... */
 262                         case 1194: /* ER_CRASHED_ON_USAGE */
 263                         case 1195: /* ER_CRASHED_ON_REPAIR */
 264                                 sql_log_crashed(query);
 265                                 /*
 266                                  * XXX: autofix by repairing the table and
 267                                  * caching inbound queries while repair is running.
 268                                  * We don't want to try reconnecting now though.
 269                                  */
 270                                 break;
 271
 272                         default:
 273                                 reconnect = 1;
 274                                 break;
 275                         }
 276                 }
 277 #endif
 278                 if (reconnect) {
 279                         lwarn("Attempting to reconnect to database and re-run the query");
 280                         if (!sql_reinit()) {
 281                                 if (!run_query(query, len, 1))
 282                                         lwarn("Successfully ran the previously failed query");
 283                                 /* database backlog code goes here */
 284                         }
 285                 }
 286         }
 287
 288         free(query);
 289
 290         return !db.result;
 291 }
 292
 293 int sql_query(const char *fmt, ...)
 294 {
 295         int ret;
 296         va_list ap;
 297
 298         va_start(ap, fmt);
 299         ret = sql_vquery(fmt, ap);
 300         va_end(ap);
 301
 302         return ret;
 303 }
 304
 305 int sql_table_exists(const char *tablename)
 306 {
 307         db_wrap_result *result;
 308         sql_query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = '%s'", tablename);
 309         result = sql_get_result();
 310         if (!result) {
 311                 return -1;
 312         }
 313         return !(result->api->step(result) == DB_WRAP_E_DONE);
 314 }
 315
 316 int sql_is_connected(int reconnect)
 317 {
 318         int ret = (db.conn && db.conn->api->is_connected(db.conn)) ? 1 : 0;
 319
 320         if (ret || !reconnect)
 321                 return ret;
 322
 323         return sql_init() == 0;
 324 }
 325
 326 int sql_repair_table(const char *table)
 327 {
 328         int result;
 329
 330         if (db_type != MERLIN_DBT_MYSQL)
 331                 return 0;
 332
 333         result = sql_query("REPAIR TABLE %s", table);
 334         if (!result)
 335                 sql_table_crashed = 0;
 336         return result;
 337 }
 338
 339 int sql_init(void)
 340 {
 341         const char *env;
 342         int result, log_attempt = 0;
 343         static time_t last_logged = 0;
 344         db_wrap_conn_params connparam = db_wrap_conn_params_empty;
 345
 346         if (!use_database)
 347                 return -1;
 348
 349         if (sql_is_connected(0)) {
 350                 ldebug("sql_init(): Already connected. Not reconnecting");
 351                 return 0;
 352         }
 353
 354         if (last_logged + 30 >= time(NULL))
 355                 log_attempt = 0;
 356         else {
 357                 log_attempt = 1;
 358                 last_logged = time(NULL);
 359         }
 360
 361         env = getenv("MERLIN_LOG_SQL");
 362         if (env && *env != 0) {
 363                 db.logSQL = 1;
 364         }
 365
 366         /* free any remaining result set */
 367         sql_free_result();
 368
 369         db.name = sql_db_name();
 370         db.host = sql_db_host();
 371         db.user = sql_db_user();
 372         db.pass = sql_db_pass();
 373         db.table = sql_table_name();
 374         db.conn_str = sql_db_conn_str();
 375         if (!db.type) {
 376                 db.type = "mysql";
 377         }
 378
 379         if (!strcmp(db.type, "mysql") || !strcmp(db.type, "dbi:mysql")) {
 380                 db_type = MERLIN_DBT_MYSQL;
 381         } else if (!strcmp(db.type, "psql") || !strcmp(db.type, "postgresql") || !strcmp(db.type, "pgsql")) {
 382                 db_type = MERLIN_DBT_PGSQL;
 383         } else if (!strcmp(db.type, "sqlite")) {
 384                 db_type = MERLIN_DBT_SQLITE;
 385         }
 386
 387         connparam.host = db.host;
 388         connparam.dbname = db.name;
 389         connparam.username = db.user;
 390         connparam.password = db.pass;
 391         if (db.conn_str && *db.conn_str)
 392                 connparam.conn_str = db.conn_str;
 393         if (db.port)
 394                 connparam.port = db.port;
 395
 396         result = db_wrap_driver_init(db.type, &connparam, &db.conn);
 397         if (result) {
 398                 if (log_attempt) {
 399                         if (db.conn_str && *db.conn_str)
 400                                 lerr("Failed to connect to db '%s' using connection string '%s' as user %s using driver %s",
 401                                          db.name, db.conn_str, db.user, db.type);
 402                         else
 403                                 lerr("Failed to connect to db '%s' at host '%s':'%d' as user %s using driver %s.",
 404                                          db.name, db.host, db.port, db.user, db.type );
 405                         lerr("result: %d; errno: %d (%s)", result, errno, strerror(errno));
 406                 }
 407                 return -1;
 408         }
 409
 410         result = db.conn->api->option_set(db.conn, "encoding", db.encoding ? db.encoding : "latin1");
 411
 412         if (result && log_attempt) {
 413                 lwarn("Warning: Failed to set encoding for the connection to db '%s' at host '%s':'%d' as user %s using driver %s.",
 414                          db.name, db.host, db.port, db.user, db.type );
 415         }
 416
 417         result = db.conn->api->connect(db.conn);
 418         if (result) {
 419                 if (log_attempt) {
 420                         const char *error_msg;
 421                         sql_error(&error_msg);
 422                         if (db.conn_str)
 423                                 lerr("DB: Failed to connect to '%s' using connection string '%s' as %s:%s: %s",
 424                                          db.name, db.conn_str, db.user, db.pass, error_msg);
 425                         else
 426                                 lerr("DB: Failed to connect to '%s' at '%s':'%d' as %s:%s: %s",
 427                                          db.name, db.host, db.port, db.user, db.pass, error_msg);
 428                 }
 429                 sql_close();
 430                 return -1;
 431         } else if (log_attempt) {
 432                 ldebug("DB: Connected to db [%s] using driver [%s]",
 433                            db.name, db.type);
 434         }
 435
 436         /*
 437          * set auto-commit to ON if we have no commit parameters
 438          * Drivers that doesn't support it or doesn't need it shouldn't
 439          * have the "set_auto_commit()" function.
 440          */
 441         if (db.conn->api->set_auto_commit) {
 442                 int set = !(commit_interval | commit_queries);
 443
 444                 if (db.conn->api->set_auto_commit(db.conn, set) < 0) {
 445                         if (set) {
 446                                 /* fake auto-commit with commit_queries = 1 */
 447                                 commit_queries = 1;
 448                         }
 449                         if (log_attempt) {
 450                                 lwarn("DB: set_auto_commit(%d) failed.", set);
 451                                 if (set) {
 452                                         lwarn("DB: setting commit_interval = 1 as workaround");
 453                                 }
 454                         }
 455                 } else if (log_attempt) {
 456                         ldebug("DB: commit_queries: %ld; commit_interval: %ld",
 457                                   commit_queries, commit_interval);
 458                 }
 459                 last_commit = time(NULL);
 460         }
 461
 462         last_logged = 0;
 463         return 0;
 464 }
 465
 466
 467 int sql_close(void)
 468 {
 469         if (!use_database)
 470                 return 0;
 471
 472         sql_free_result();
 473         if (db.conn) {
 474                 db.conn->api->finalize(db.conn);
 475                 db.conn = NULL;
 476         }
 477
 478         return 0;
 479 }
 480
 481
 482 int sql_reinit(void)
 483 {
 484         sql_close();
 485         return sql_init();
 486 }
 487
 488 /*
 489  * Some nifty helper functions which still allow us to keep the
 490  * db struct in file scope
 491  */
 492
 493 const char *sql_db_name(void)
 494 {
 495         return db.name ? db.name : "merlin";
 496 }
 497
 498 const char *sql_db_user(void)
 499 {
 500         return db.user ? db.user : "merlin";
 501 }
 502
 503 const char *sql_db_pass(void)
 504 {
 505         return db.pass ? db.pass : "merlin";
 506 }
 507
 508 const char *sql_db_host(void)
 509 {
 510         return db.host ? db.host : "localhost";
 511 }
 512
 513 unsigned int sql_db_port(void)
 514 {
 515         return db.port;
 516 }
 517
 518 const char *sql_db_type(void)
 519 {
 520         return db.type ? db.type : "mysql";
 521 }
 522
 523 const char *sql_table_name(void)
 524 {
 525         return db.table ? db.table : "report_data";
 526 }
 527
 528 const char *sql_db_conn_str(void)
 529 {
 530         return db.conn_str ? db.conn_str : "";
 531 }
 532
 533
 534 /*
 535  * Config parameters from the "database" section end up here.
 536  *
 537  * The option "logsql" tells this API to log all SQL commands
 538  * to stderr if value is not NULL and does not start with the
 539  * character '0' (zero).
 540  */
 541 int sql_config(const char *key, const char *value)
 542 {
 543         char *value_cpy;
 544         int err;
 545
 546         value_cpy = value ? strdup(value) : NULL;
 547
 548         if (!prefixcmp(key, "name") || !prefixcmp(key, "database"))
 549                 db.name = value_cpy;
 550         else if (!prefixcmp(key, "logsql")) {
 551                 db.logSQL = strtobool(value);
 552                 free(value_cpy);
 553         }
 554         else if (!prefixcmp(key, "user"))
 555                 db.user = value_cpy;
 556         else if (!prefixcmp(key, "pass"))
 557                 db.pass = value_cpy;
 558         else if (!prefixcmp(key, "host"))
 559                 db.host = value_cpy;
 560         else if (!prefixcmp(key, "type"))
 561                 db.type = value_cpy;
 562         else if (!prefixcmp(key, "conn_str"))
 563                 db.conn_str = value_cpy;
 564         else if (!prefixcmp(key, "port") && value && value[0]) {
 565                 char *endp;
 566                 free(value_cpy);
 567                 db.port = (unsigned int)strtoul(value, &endp, 0);
 568
 569                 if (endp == value || *endp != 0)
 570                         return -1;
 571         }
 572         else if (!strcmp(key, "host_perfdata_table"))
 573                 host_perf_table = value_cpy;
 574         else if (!strcmp(key, "service_perfdata_table"))
 575                 service_perf_table = value_cpy;
 576         else if (!strcmp(key, "perfdata_table"))
 577                 host_perf_table = service_perf_table = value_cpy;
 578         else if (!strcmp(key, "commit_interval")) {
 579                 err = grok_seconds(value, &commit_interval);
 580                 ldebug("DB: commit_interval set to %ld seconds", commit_interval);
 581                 free(value_cpy);
 582                 return err;
 583         }
 584         else if (!strcmp(key, "commit_queries") && value_cpy != NULL) {
 585                 char *endp;
 586                 commit_queries = strtoul(value_cpy, &endp, 0);
 587                 ldebug("DB: commit_queries set to %ld queries", commit_queries);
 588                 free(value_cpy);
 589         }
 590         else {
 591                 if (value_cpy)
 592                         free(value_cpy);
 593                 return -1; /* config error */
 594         }
 595
 596         return 0;
 597 }