src/lib-sql/driver-mysql.c

	driver-mysql.c revision 7bafda1813454621e03615e83d55bccfa7cc56bd
949N/A/* Copyright (c) 2003-2009 Dovecot authors, see the included COPYING file */
949N/A
949N/A#include "lib.h"
949N/A#include "array.h"
949N/A#include "str.h"
949N/A#include "sql-api-private.h"
949N/A
949N/A#ifdef BUILD_MYSQL
949N/A#include <stdlib.h>
949N/A#include <unistd.h>
949N/A#include <time.h>
949N/A#include <mysql.h>
949N/A#include <errmsg.h>
949N/A
949N/A/* Abort connect() if it can't connect within this time. */
949N/A#define MYSQL_CONNECT_FAILURE_TIMEOUT 10
949N/A
949N/A/* Minimum delay between reconnecting to same server */
949N/A#define CONNECT_MIN_DELAY 1
949N/A/* Maximum time to avoiding reconnecting to same server */
949N/A#define CONNECT_MAX_DELAY (60*30)
949N/A/* If no servers are connected but a query is requested, try reconnecting to
949N/A   next server which has been disconnected longer than this (with a single
949N/A   server setup this is really the "max delay" and the CONNECT_MAX_DELAY
949N/A   is never used). */
992N/A#define CONNECT_RESET_DELAY 15
949N/A
949N/Astruct mysql_db {
949N/A    struct sql_db api;
949N/A
949N/A    pool_t pool;
949N/A    const char *user, *password, *dbname, *unix_socket;
949N/A    const char *ssl_cert, *ssl_key, *ssl_ca, *ssl_ca_path, *ssl_cipher;
949N/A    const char *option_file, *option_group;
949N/A    unsigned int port, client_flags;
949N/A
949N/A    ARRAY_DEFINE(connections, struct mysql_connection);
949N/A    unsigned int next_query_connection;
949N/A};
949N/A
949N/Astruct mysql_connection {
949N/A    struct mysql_db *db;
949N/A
949N/A    MYSQL *mysql;
949N/A    const char *host;
949N/A
949N/A    unsigned int connect_delay;
949N/A    unsigned int connect_failure_count;
949N/A
949N/A    time_t last_connect;
949N/A    unsigned int connected:1;
949N/A    unsigned int ssl_set:1;
949N/A};
949N/A
949N/Astruct mysql_result {
949N/A    struct sql_result api;
949N/A    struct mysql_connection *conn;
949N/A
949N/A    MYSQL_RES *result;
949N/A        MYSQL_ROW row;
949N/A
949N/A    MYSQL_FIELD *fields;
949N/A    unsigned int fields_count;
949N/A};
949N/A
949N/Astruct mysql_transaction_context {
949N/A    struct sql_transaction_context ctx;
949N/A
949N/A    pool_t query_pool;
949N/A    struct mysql_query_list *head, *tail;
949N/A
949N/A    const char *error;
949N/A
949N/A    unsigned int failed:1;
949N/A};
949N/A
949N/Astruct mysql_query_list {
949N/A    struct mysql_query_list *next;
949N/A    const char *query;
949N/A    unsigned int *affected_rows;
949N/A};
949N/A
949N/Aextern struct sql_db driver_mysql_db;
949N/Aextern struct sql_result driver_mysql_result;
949N/Aextern struct sql_result driver_mysql_error_result;
949N/A
949N/Astatic bool driver_mysql_connect(struct mysql_connection *conn)
949N/A{
949N/A    struct mysql_db *db = conn->db;
949N/A    const char *unix_socket, *host;
949N/A    time_t now;
949N/A    bool failed;
949N/A
949N/A    if (conn->connected)
949N/A        return TRUE;
949N/A
949N/A    /* don't try reconnecting more than once a second */
949N/A    now = time(NULL);
949N/A    if (conn->last_connect + (time_t)conn->connect_delay > now)
949N/A        return FALSE;
949N/A    conn->last_connect = now;
949N/A
949N/A    if (*conn->host == '/') {
949N/A        unix_socket = conn->host;
949N/A        host = NULL;
949N/A    } else {
949N/A        unix_socket = NULL;
949N/A        host = conn->host;
949N/A    }
949N/A
949N/A    if (db->option_file != NULL) {
949N/A        mysql_options(conn->mysql, MYSQL_READ_DEFAULT_FILE,
949N/A                  db->option_file);
949N/A    }
949N/A
949N/A    mysql_options(conn->mysql, MYSQL_READ_DEFAULT_GROUP,
949N/A              db->option_group != NULL ? db->option_group : "client");
949N/A
949N/A    if (!conn->ssl_set && (db->ssl_ca != NULL || db->ssl_ca_path != NULL)) {
949N/A#ifdef HAVE_MYSQL_SSL
949N/A        mysql_ssl_set(conn->mysql, db->ssl_key, db->ssl_cert,
949N/A                  db->ssl_ca, db->ssl_ca_path
949N/A#ifdef HAVE_MYSQL_SSL_CIPHER
949N/A                  , db->ssl_cipher
949N/A#endif
949N/A                 );
949N/A        conn->ssl_set = TRUE;
992N/A#else
992N/A        i_fatal("mysql: SSL support not compiled in "
992N/A            "(remove ssl_ca and ssl_ca_path settings)");
992N/A#endif
992N/A    }
992N/A
992N/A    alarm(MYSQL_CONNECT_FAILURE_TIMEOUT);
992N/A    failed = mysql_real_connect(conn->mysql, host, db->user, db->password,
949N/A                    db->dbname, db->port, unix_socket,
949N/A                    db->client_flags) == NULL;
949N/A    alarm(0);
949N/A    if (failed) {
949N/A        if (conn->connect_failure_count > 0) {
949N/A            /* increase delay between reconnections to this
949N/A               server */
949N/A            conn->connect_delay *= 5;
949N/A            if (conn->connect_delay > CONNECT_MAX_DELAY)
949N/A                conn->connect_delay = CONNECT_MAX_DELAY;
949N/A        }
949N/A        conn->connect_failure_count++;
949N/A
949N/A        i_error("mysql: Connect failed to %s (%s): %s - "
949N/A            "waiting for %u seconds before retry",
949N/A            host != NULL ? host : unix_socket, db->dbname,
949N/A            mysql_error(conn->mysql), conn->connect_delay);
949N/A        return FALSE;
949N/A    } else {
949N/A        i_info("mysql: Connected to %s%s (%s)",
949N/A               host != NULL ? host : unix_socket,
949N/A               conn->ssl_set ? " using SSL" : "", db->dbname);
949N/A
949N/A        conn->connect_failure_count = 0;
992N/A        conn->connect_delay = CONNECT_MIN_DELAY;
992N/A        conn->connected = TRUE;
992N/A        return TRUE;
992N/A    }
992N/A}
949N/A
949N/Astatic int driver_mysql_connect_all(struct sql_db *_db)
949N/A{
949N/A    struct mysql_db *db = (struct mysql_db *)_db;
949N/A    struct mysql_connection *conn;
949N/A    int ret = -1;
949N/A
949N/A    array_foreach_modifiable(&db->connections, conn) {
949N/A        if (driver_mysql_connect(conn))
949N/A            ret = 1;
949N/A    }
949N/A    return ret;
949N/A}
992N/A
949N/Astatic void driver_mysql_connection_add(struct mysql_db *db, const char *host)
949N/A{
949N/A    struct mysql_connection *conn;
949N/A
949N/A    conn = array_append_space(&db->connections);
949N/A    conn->db = db;
992N/A    conn->host = p_strdup(db->pool, host);
992N/A    conn->mysql = mysql_init(NULL);
949N/A    if (conn->mysql == NULL)
949N/A        i_fatal("mysql_init() failed");
949N/A
949N/A    conn->connect_delay = CONNECT_MIN_DELAY;
949N/A}
992N/A
949N/Astatic void driver_mysql_connection_free(struct mysql_connection *conn)
949N/A{
949N/A    mysql_close(conn->mysql);
949N/A}
949N/A
949N/Astatic void driver_mysql_parse_connect_string(struct mysql_db *db,
949N/A                          const char *connect_string)
949N/A{
949N/A    const char *const *args, *name, *value;
949N/A    const char **field;
949N/A
949N/A    db->ssl_cipher = "HIGH";
992N/A
949N/A    args = t_strsplit_spaces(connect_string, " ");
949N/A    for (; *args != NULL; args++) {
949N/A        value = strchr(*args, '=');
949N/A        if (value == NULL) {
949N/A            i_fatal("mysql: Missing value in connect string: %s",
949N/A                *args);
949N/A        }
949N/A        name = t_strdup_until(*args, value);
949N/A        value++;
949N/A
949N/A        field = NULL;
992N/A        if (strcmp(name, "host") == 0 ||
949N/A            strcmp(name, "hostaddr") == 0)
949N/A            driver_mysql_connection_add(db, value);
949N/A        else if (strcmp(name, "user") == 0)
949N/A            field = &db->user;
949N/A        else if (strcmp(name, "password") == 0)
949N/A            field = &db->password;
949N/A        else if (strcmp(name, "dbname") == 0)
949N/A            field = &db->dbname;
949N/A        else if (strcmp(name, "port") == 0)
949N/A            db->port = atoi(value);
949N/A        else if (strcmp(name, "client_flags") == 0)
949N/A            db->client_flags = atoi(value);
949N/A        else if (strcmp(name, "ssl_cert") == 0)
949N/A            field = &db->ssl_cert;
949N/A        else if (strcmp(name, "ssl_key") == 0)
949N/A            field = &db->ssl_key;
949N/A        else if (strcmp(name, "ssl_ca") == 0)
949N/A            field = &db->ssl_ca;
949N/A        else if (strcmp(name, "ssl_ca_path") == 0)
949N/A            field = &db->ssl_ca_path;
949N/A        else if (strcmp(name, "ssl_cipher") == 0)
949N/A            field = &db->ssl_cipher;
949N/A        else if (strcmp(name, "option_file") == 0)
949N/A            field = &db->option_file;
949N/A        else if (strcmp(name, "option_group") == 0)
949N/A            field = &db->option_group;
992N/A        else
992N/A            i_fatal("mysql: Unknown connect string: %s", name);
992N/A
949N/A        if (field != NULL)
992N/A            *field = p_strdup(db->pool, value);
992N/A    }
992N/A
992N/A    if (array_count(&db->connections) == 0)
992N/A        i_fatal("mysql: No hosts given in connect string");
949N/A}
949N/A
949N/Astatic struct sql_db *driver_mysql_init_v(const char *connect_string)
949N/A{
949N/A    struct mysql_db *db;
949N/A    pool_t pool;
949N/A
949N/A    pool = pool_alloconly_create("mysql driver", 1024);
949N/A    db = p_new(pool, struct mysql_db, 1);
949N/A    db->pool = pool;
949N/A    db->api = driver_mysql_db;
949N/A    p_array_init(&db->connections, db->pool, 6);
949N/A
949N/A    T_BEGIN {
949N/A        driver_mysql_parse_connect_string(db, connect_string);
949N/A    } T_END;
949N/A    return &db->api;
949N/A}
949N/A
949N/Astatic void driver_mysql_deinit_v(struct sql_db *_db)
{
    struct mysql_db *db = (struct mysql_db *)_db;
    struct mysql_connection *conn;

    array_foreach_modifiable(&db->connections, conn)
        (void)driver_mysql_connection_free(conn);

    array_free(&_db->module_contexts);
    pool_unref(&db->pool);
}

static enum sql_db_flags
driver_mysql_get_flags(struct sql_db *db ATTR_UNUSED)
{
    return SQL_DB_FLAG_BLOCKING;
}

static int driver_mysql_connection_do_query(struct mysql_connection *conn,
                        const char *query)
{
    int i;

    for (i = 0; i < 2; i++) {
        if (!driver_mysql_connect(conn))
            return 0;

        if (mysql_query(conn->mysql, query) == 0)
            return 1;

        /* failed */
        switch (mysql_errno(conn->mysql)) {
        case CR_SERVER_GONE_ERROR:
        case CR_SERVER_LOST:
            /* connection lost - try immediate reconnect */
            conn->connected = FALSE;
            break;
        default:
            return -1;
        }
    }

    /* connected -> lost it -> connected -> lost again */
    return 0;
}

static int driver_mysql_do_query(struct mysql_db *db, const char *query,
                 struct mysql_connection **conn_r)
{
    struct mysql_connection *conn;
    unsigned int i, start, count;
    bool reset;
    int ret;

    conn = array_get_modifiable(&db->connections, &count);

    /* go through the connections in round robin. if the connection
       isn't available, try next one that is. */
    start = db->next_query_connection % count;
    db->next_query_connection++;

    for (reset = FALSE;; reset = TRUE) {
        i = start;
        do {
            ret = driver_mysql_connection_do_query(&conn[i], query);
            if (ret != 0) {
                /* success / failure */
                *conn_r = &conn[i];
                return ret;
            }

            /* not connected, try next one */
            i = (i + 1) % count;
        } while (i != start);

        if (reset)
            break;

        /* none are connected. connect_delays may have gotten too high,
           reset all of them to see if some are still alive. */
        for (i = 0; i < count; i++)
            conn[i].connect_delay = CONNECT_RESET_DELAY;
    }

    *conn_r = NULL;
    return 0;
}

static const char *
driver_mysql_escape_string(struct sql_db *_db, const char *string)
{
    struct mysql_db *db = (struct mysql_db *)_db;
    struct mysql_connection *conn;
    unsigned int i, count;
    size_t len = strlen(string);
    char *to;

    /* All the connections should be identical, so just use the first
       connected one */
    conn = array_get_modifiable(&db->connections, &count);
    for (i = 0; i < count; i++) {
        if (conn[i].connected)
            break;
    }
    if (i == count) {
        /* so, try connecting.. */
        for (i = 0; i < count; i++) {
            if (driver_mysql_connect(&conn[i]))
                break;
        }
        if (i == count) {
            /* FIXME: we don't have a valid connection, so fallback
               to using default escaping. the next query will most
               likely fail anyway so it shouldn't matter that much
               what we return here.. Anyway, this API needs
               changing so that the escaping function could already
               fail the query reliably. */
            to = t_buffer_get(len * 2 + 1);
            len = mysql_escape_string(to, string, len);
            t_buffer_alloc(len + 1);
            return to;
        }
    }

    to = t_buffer_get(len * 2 + 1);
    len = mysql_real_escape_string(conn[i].mysql, to, string, len);
    t_buffer_alloc(len + 1);
    return to;
}

static void driver_mysql_exec(struct sql_db *_db, const char *query)
{
    struct mysql_db *db = (struct mysql_db *)_db;
    struct mysql_connection *conn;

    (void)driver_mysql_do_query(db, query, &conn);
}

static void driver_mysql_query(struct sql_db *db, const char *query,
                   sql_query_callback_t *callback, void *context)
{
    struct sql_result *result;

    result = sql_query_s(db, query);
    result->callback = TRUE;
    callback(result, context);
    result->callback = FALSE;
    sql_result_unref(result);
}

static struct sql_result *
driver_mysql_query_s(struct sql_db *_db, const char *query)
{
    struct mysql_db *db = (struct mysql_db *)_db;
    struct mysql_connection *conn;
    struct mysql_result *result;

    result = i_new(struct mysql_result, 1);
    result->api = driver_mysql_result;
    result->api.db = _db;

    switch (driver_mysql_do_query(db, query, &conn)) {
    case 0:
        /* not connected */
        result->api = sql_not_connected_result;
        break;
    case 1:
        /* query ok */
        result->result = mysql_store_result(conn->mysql);
        if (result->result != NULL || mysql_errno(conn->mysql) == 0)
            break;
        /* fallback */
    case -1:
        /* error */
        result->api = driver_mysql_error_result;
        break;
    }

    result->api.refcount = 1;
    result->conn = conn;
    return &result->api;
}

static void driver_mysql_result_free(struct sql_result *_result)
{
    struct mysql_result *result = (struct mysql_result *)_result;

    i_assert(_result != &sql_not_connected_result);
    if (_result->callback)
        return;

    if (result->result != NULL)
        mysql_free_result(result->result);
    i_free(result);
}

static int driver_mysql_result_next_row(struct sql_result *_result)
{
    struct mysql_result *result = (struct mysql_result *)_result;

    if (result->result == NULL) {
        /* no results */
        return 0;
    }

    result->row = mysql_fetch_row(result->result);
    if (result->row != NULL)
        return 1;

    return mysql_errno(result->conn->mysql) != 0 ? -1 : 0;
}

static void driver_mysql_result_fetch_fields(struct mysql_result *result)
{
    if (result->fields != NULL)
        return;

    result->fields_count = mysql_num_fields(result->result);
    result->fields = mysql_fetch_fields(result->result);
}

static unsigned int
driver_mysql_result_get_fields_count(struct sql_result *_result)
{
    struct mysql_result *result = (struct mysql_result *)_result;

        driver_mysql_result_fetch_fields(result);
    return result->fields_count;
}

static const char *
driver_mysql_result_get_field_name(struct sql_result *_result, unsigned int idx)
{
    struct mysql_result *result = (struct mysql_result *)_result;

    driver_mysql_result_fetch_fields(result);
    i_assert(idx < result->fields_count);
    return result->fields[idx].name;
}

static int driver_mysql_result_find_field(struct sql_result *_result,
                      const char *field_name)
{
    struct mysql_result *result = (struct mysql_result *)_result;
    unsigned int i;

    driver_mysql_result_fetch_fields(result);
    for (i = 0; i < result->fields_count; i++) {
        if (strcmp(result->fields[i].name, field_name) == 0)
            return i;
    }
    return -1;
}

static const char *
driver_mysql_result_get_field_value(struct sql_result *_result,
                    unsigned int idx)
{
    struct mysql_result *result = (struct mysql_result *)_result;

    return (const char *)result->row[idx];
}

static const unsigned char *
driver_mysql_result_get_field_value_binary(struct sql_result *_result ATTR_UNUSED,
                       unsigned int idx ATTR_UNUSED,
                       size_t *size_r ATTR_UNUSED)
{
    /* FIXME */
    return NULL;
}

static const char *
driver_mysql_result_find_field_value(struct sql_result *result,
                     const char *field_name)
{
    int idx;

    idx = driver_mysql_result_find_field(result, field_name);
    if (idx < 0)
        return NULL;
    return driver_mysql_result_get_field_value(result, idx);
}

static const char *const *
driver_mysql_result_get_values(struct sql_result *_result)
{
    struct mysql_result *result = (struct mysql_result *)_result;

    return (const char *const *)result->row;
}

static const char *driver_mysql_result_get_error(struct sql_result *_result)
{
    struct mysql_result *result = (struct mysql_result *)_result;

    return mysql_error(result->conn->mysql);
}

static struct sql_transaction_context *
driver_mysql_transaction_begin(struct sql_db *db)
{
    struct mysql_transaction_context *ctx;

    ctx = i_new(struct mysql_transaction_context, 1);
    ctx->ctx.db = db;
    ctx->query_pool = pool_alloconly_create("mysql transaction", 1024);
    return &ctx->ctx;
}

static void
driver_mysql_transaction_commit(struct sql_transaction_context *ctx,
                sql_commit_callback_t *callback, void *context)
{
    const char *error;

    if (sql_transaction_commit_s(&ctx, &error) < 0)
        callback(error, context);
    else
        callback(NULL, context);
}

static int transaction_send_query(struct mysql_transaction_context *ctx,
                  const char *query)
{
    struct sql_result *result;
    my_ulonglong rows;
    int ret = 0;

    if (ctx->failed)
        return -1;

    result = sql_query_s(ctx->ctx.db, query);
    if (sql_result_next_row(result) < 0) {
        ctx->error = sql_result_get_error(result);
        ctx->failed = TRUE;
        ret = -1;
    } else if (ctx->head != NULL && ctx->head->affected_rows != NULL) {
        struct mysql_result *my_result = (struct mysql_result *)result;

        rows = mysql_affected_rows(my_result->conn->mysql);
        i_assert(rows != (my_ulonglong)-1);
        *ctx->head->affected_rows = rows;
    }
    sql_result_unref(result);
    return ret;
}

static int
driver_mysql_transaction_commit_s(struct sql_transaction_context *_ctx,
                  const char **error_r)
{
    struct mysql_transaction_context *ctx =
        (struct mysql_transaction_context *)_ctx;
    int ret = 0;

    *error_r = NULL;

    if (ctx->head != NULL) {
        /* try to use a transaction in any case,
           even if it doesn't work. */
        (void)transaction_send_query(ctx, "BEGIN");
        while (ctx->head != NULL) {
            if (transaction_send_query(ctx, ctx->head->query) < 0)
                break;
            ctx->head = ctx->head->next;
        }
        ret = transaction_send_query(ctx, "COMMIT");
        *error_r = ctx->error;
    }
    sql_transaction_rollback(&_ctx);
    return ret;
}

static void
driver_mysql_transaction_rollback(struct sql_transaction_context *_ctx)
{
    struct mysql_transaction_context *ctx =
        (struct mysql_transaction_context *)_ctx;

    pool_unref(&ctx->query_pool);
    i_free(ctx);
}

static void
driver_mysql_update(struct sql_transaction_context *_ctx, const char *query,
            unsigned int *affected_rows)
{
    struct mysql_transaction_context *ctx =
        (struct mysql_transaction_context *)_ctx;
    struct mysql_query_list *list;

    list = p_new(ctx->query_pool, struct mysql_query_list, 1);
    list->query = p_strdup(ctx->query_pool, query);
    list->affected_rows = affected_rows;

    if (ctx->head == NULL)
        ctx->head = list;
    else
        ctx->tail->next = list;
    ctx->tail = list;
}

struct sql_db driver_mysql_db = {
    "mysql",

    .v = {
        driver_mysql_init_v,
        driver_mysql_deinit_v,
        driver_mysql_get_flags,
        driver_mysql_connect_all,
        driver_mysql_escape_string,
        driver_mysql_exec,
        driver_mysql_query,
        driver_mysql_query_s,

        driver_mysql_transaction_begin,
        driver_mysql_transaction_commit,
        driver_mysql_transaction_commit_s,
        driver_mysql_transaction_rollback,

        driver_mysql_update
    }
};

struct sql_result driver_mysql_result = {
    .v = {
        driver_mysql_result_free,
        driver_mysql_result_next_row,
        driver_mysql_result_get_fields_count,
        driver_mysql_result_get_field_name,
        driver_mysql_result_find_field,
        driver_mysql_result_get_field_value,
        driver_mysql_result_get_field_value_binary,
        driver_mysql_result_find_field_value,
        driver_mysql_result_get_values,
        driver_mysql_result_get_error
    }
};

static int
driver_mysql_result_error_next_row(struct sql_result *result ATTR_UNUSED)
{
    return -1;
}

struct sql_result driver_mysql_error_result = {
    .v = {
        driver_mysql_result_free,
        driver_mysql_result_error_next_row,
        NULL, NULL, NULL, NULL, NULL, NULL, NULL,
        driver_mysql_result_get_error
    }
};

void driver_mysql_init(void);
void driver_mysql_deinit(void);

void driver_mysql_init(void)
{
    sql_driver_register(&driver_mysql_db);
}

void driver_mysql_deinit(void)
{
    sql_driver_unregister(&driver_mysql_db);
}

#endif