mysql_connection.cc 14.3 KB
Newer Older
1
// Copyright (C) 2012-2019 Internet Systems Consortium, Inc. ("ISC")
2
//
3 4 5
// This Source Code Form is subject to the terms of the Mozilla Public
// License, v. 2.0. If a copy of the MPL was not distributed with this
// file, You can obtain one at http://mozilla.org/MPL/2.0/.
6

7 8
#include <config.h>

9
#include <database/db_log.h>
10
#include <exceptions/exceptions.h>
11
#include <mysql/mysql_connection.h>
12

13 14
#include <boost/lexical_cast.hpp>

15
#include <algorithm>
16
#include <stdint.h>
17
#include <string>
18
#include <limits>
19 20 21 22 23

using namespace isc;
using namespace std;

namespace isc {
24
namespace db {
25

26 27
bool MySqlHolder::atexit_ = false;

28
/// @todo: Migrate this default value to src/bin/dhcpX/simple_parserX.cc
29
const int MYSQL_DEFAULT_CONNECTION_TIMEOUT = 5; // seconds
30

31 32
MySqlTransaction::MySqlTransaction(MySqlConnection& conn)
    : conn_(conn), committed_(false) {
33
    conn_.startTransaction();
34 35 36
}

MySqlTransaction::~MySqlTransaction() {
37 38
    // Rollback if the MySqlTransaction::commit wasn't explicitly
    // called.
39 40 41 42 43 44 45 46 47 48 49 50
    if (!committed_) {
        conn_.rollback();
    }
}

void
MySqlTransaction::commit() {
    conn_.commit();
    committed_ = true;
}


51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
// Open the database using the parameters passed to the constructor.

void
MySqlConnection::openDatabase() {

    // Set up the values of the parameters
    const char* host = "localhost";
    string shost;
    try {
        shost = getParameter("host");
        host = shost.c_str();
    } catch (...) {
        // No host.  Fine, we'll use "localhost"
    }

66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
    unsigned int port = 0;
    string sport;
    try {
        sport = getParameter("port");
    } catch (...) {
        // No port parameter, we are going to use the default port.
        sport = "";
    }

    if (sport.size() > 0) {
        // Port was given, so try to convert it to an integer.

        try {
            port = boost::lexical_cast<unsigned int>(sport);
        } catch (...) {
            // Port given but could not be converted to an unsigned int.
            // Just fall back to the default value.
            port = 0;
        }

        // The port is only valid when it is in the 0..65535 range.
        // Again fall back to the default when the given value is invalid.
        if (port > numeric_limits<uint16_t>::max()) {
            port = 0;
        }
    }

93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117
    const char* user = NULL;
    string suser;
    try {
        suser = getParameter("user");
        user = suser.c_str();
    } catch (...) {
        // No user.  Fine, we'll use NULL
    }

    const char* password = NULL;
    string spassword;
    try {
        spassword = getParameter("password");
        password = spassword.c_str();
    } catch (...) {
        // No password.  Fine, we'll use NULL
    }

    const char* name = NULL;
    string sname;
    try {
        sname = getParameter("name");
        name = sname.c_str();
    } catch (...) {
        // No database name.  Throw a "NoName" exception
118 119 120
        isc_throw(NoDatabaseName, "must specify a name for the database");
    }

121
    unsigned int connect_timeout = MYSQL_DEFAULT_CONNECTION_TIMEOUT;
122 123 124 125 126 127 128 129 130 131
    string stimeout;
    try {
        stimeout = getParameter("connect-timeout");
    } catch (...) {
        // No timeout parameter, we are going to use the default timeout.
        stimeout = "";
    }

    if (stimeout.size() > 0) {
        // Timeout was given, so try to convert it to an integer.
132

133
        try {
134
            connect_timeout = boost::lexical_cast<unsigned int>(stimeout);
135
        } catch (...) {
136 137 138 139
            // Timeout given but could not be converted to an unsigned int. Set
            // the connection timeout to an invalid value to trigger throwing
            // of an exception.
            connect_timeout = 0;
140 141
        }

142
        // The timeout is only valid if greater than zero, as depending on the
Andrei Pavel's avatar
Andrei Pavel committed
143
        // database, a zero timeout might signify something like "wait
144 145 146 147 148 149 150 151 152 153 154
        // indefinitely".
        //
        // The check below also rejects a value greater than the maximum
        // integer value.  The lexical_cast operation used to obtain a numeric
        // value from a string can get confused if trying to convert a negative
        // integer to an unsigned int: instead of throwing an exception, it may
        // produce a large positive value.
        if ((connect_timeout == 0) ||
            (connect_timeout > numeric_limits<int>::max())) {
            isc_throw(DbInvalidTimeout, "database connection timeout (" <<
                      stimeout << ") must be an integer greater than 0");
155
        }
156 157 158 159
    }

    // Set options for the connection:
    //
160 161 162 163 164
    // Set options for the connection:
    // Make sure auto_reconnect is OFF! Enabling it leaves us with an unusable
    // connection after a reconnect as among other things, it drops all our
    // pre-compiled statements.
    my_bool auto_reconnect = MLM_FALSE;
165 166 167 168 169 170
    int result = mysql_options(mysql_, MYSQL_OPT_RECONNECT, &auto_reconnect);
    if (result != 0) {
        isc_throw(DbOpenError, "unable to set auto-reconnect option: " <<
                  mysql_error(mysql_));
    }

171 172 173 174 175 176 177 178
    // Make sure we have a large idle time window ... say 30 days...
    const char *wait_time = "SET SESSION wait_timeout = 30 * 86400";
    result = mysql_options(mysql_, MYSQL_INIT_COMMAND, wait_time);
    if (result != 0) {
        isc_throw(DbOpenError, "unable to set wait_timeout " <<
                  mysql_error(mysql_));
    }

179 180 181 182 183 184 185 186 187
    // Set SQL mode options for the connection:  SQL mode governs how what
    // constitutes insertable data for a given column, and how to handle
    // invalid data.  We want to ensure we get the strictest behavior and
    // to reject invalid data with an error.
    const char *sql_mode = "SET SESSION sql_mode ='STRICT_ALL_TABLES'";
    result = mysql_options(mysql_, MYSQL_INIT_COMMAND, sql_mode);
    if (result != 0) {
        isc_throw(DbOpenError, "unable to set SQL mode options: " <<
                  mysql_error(mysql_));
188 189 190 191 192 193 194 195
    }

    // Connection timeout, the amount of time taken for the client to drop
    // the connection if the server is not responding.
    result = mysql_options(mysql_, MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
    if (result != 0) {
        isc_throw(DbOpenError, "unable to set database connection timeout: " <<
                  mysql_error(mysql_));
196 197 198 199 200 201 202 203 204 205 206 207 208
    }

    // Open the database.
    //
    // The option CLIENT_FOUND_ROWS is specified so that in an UPDATE,
    // the affected rows are the number of rows found that match the
    // WHERE clause of the SQL statement, not the rows changed.  The reason
    // here is that MySQL apparently does not update a row if data has not
    // changed and so the "affected rows" (retrievable from MySQL) is zero.
    // This makes it hard to distinguish whether the UPDATE changed no rows
    // because no row matching the WHERE clause was found, or because a
    // row was found but no data was altered.
    MYSQL* status = mysql_real_connect(mysql_, host, user, password, name,
209
                                       port, NULL, CLIENT_FOUND_ROWS);
210 211 212 213 214
    if (status != mysql_) {
        isc_throw(DbOpenError, mysql_error(mysql_));
    }
}

215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231
// Get schema version.

std::pair<uint32_t, uint32_t>
MySqlConnection::getVersion(const ParameterMap& parameters) {
    // Get a connection.
    MySqlConnection conn(parameters);

    // Open the database.
    conn.openDatabase();

    // Allocate a new statement.
    MYSQL_STMT *stmt = mysql_stmt_init(conn.mysql_);
    if (stmt == NULL) {
        isc_throw(DbOperationError, "unable to allocate MySQL prepared "
                "statement structure, reason: " << mysql_error(conn.mysql_));
    }

Francis Dupont's avatar
Francis Dupont committed
232
    try {
233

Francis Dupont's avatar
Francis Dupont committed
234 235 236 237 238 239 240 241
        // Prepare the statement from SQL text.
        const char* version_sql = "SELECT version, minor FROM schema_version";
        int status = mysql_stmt_prepare(stmt, version_sql, strlen(version_sql));
        if (status != 0) {
            isc_throw(DbOperationError, "unable to prepare MySQL statement <"
                      << version_sql << ">, reason: "
                      << mysql_error(conn.mysql_));
        }
242

Francis Dupont's avatar
Francis Dupont committed
243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277
        // Execute the prepared statement.
        if (mysql_stmt_execute(stmt) != 0) {
            isc_throw(DbOperationError, "cannot execute schema version query <"
                      << version_sql << ">, reason: "
                      << mysql_errno(conn.mysql_));
        }

        // Bind the output of the statement to the appropriate variables.
        MYSQL_BIND bind[2];
        memset(bind, 0, sizeof(bind));

        uint32_t major;
        bind[0].buffer_type = MYSQL_TYPE_LONG;
        bind[0].is_unsigned = 1;
        bind[0].buffer = &major;
        bind[0].buffer_length = sizeof(major);

        uint32_t minor;
        bind[1].buffer_type = MYSQL_TYPE_LONG;
        bind[1].is_unsigned = 1;
        bind[1].buffer = &minor;
        bind[1].buffer_length = sizeof(minor);

        if (mysql_stmt_bind_result(stmt, bind)) {
            isc_throw(DbOperationError, "unable to bind result set for <"
                      << version_sql << ">, reason: "
                      << mysql_errno(conn.mysql_));
        }

        // Fetch the data.
        if (mysql_stmt_fetch(stmt)) {
            isc_throw(DbOperationError, "unable to bind result set for <"
                      << version_sql << ">, reason: "
                      << mysql_errno(conn.mysql_));
        }
278

Francis Dupont's avatar
Francis Dupont committed
279
        // Discard the statement and its resources
280 281
        mysql_stmt_close(stmt);

Francis Dupont's avatar
Francis Dupont committed
282
        return (std::make_pair(major, minor));
283

Francis Dupont's avatar
Francis Dupont committed
284 285 286 287 288 289 290
    } catch (const std::exception&) {
        // Avoid a memory leak on error.
        mysql_stmt_close(stmt);

        // Send the exception to the caller.
        throw;
    }
291
}
292 293 294 295 296 297 298 299 300

// Prepared statement setup.  The textual form of an SQL statement is stored
// in a vector of strings (text_statements_) and is used in the output of
// error messages.  The SQL statement is also compiled into a "prepared
// statement" (stored in statements_), which avoids the overhead of compilation
// during use.  As prepared statements have resources allocated to them, the
// class destructor explicitly destroys them.

void
301
MySqlConnection::prepareStatement(uint32_t index, const char* text) {
302 303
    // Validate that there is space for the statement in the statements array
    // and that nothing has been placed there before.
304
    if ((index >= statements_.size()) || (statements_[index] != NULL)) {
305 306 307 308 309 310
        isc_throw(InvalidParameter, "invalid prepared statement index (" <<
                  static_cast<int>(index) << ") or indexed prepared " <<
                  "statement is not null");
    }

    // All OK, so prepare the statement
311 312 313
    text_statements_[index] = std::string(text);
    statements_[index] = mysql_stmt_init(mysql_);
    if (statements_[index] == NULL) {
314 315 316 317
        isc_throw(DbOperationError, "unable to allocate MySQL prepared "
                  "statement structure, reason: " << mysql_error(mysql_));
    }

318
    int status = mysql_stmt_prepare(statements_[index], text, strlen(text));
319 320 321 322 323 324 325
    if (status != 0) {
        isc_throw(DbOperationError, "unable to prepare MySQL statement <" <<
                  text << ">, reason: " << mysql_error(mysql_));
    }
}

void
326
MySqlConnection::prepareStatements(const TaggedStatement* start_statement,
327
                                   const TaggedStatement* end_statement) {
328
    // Created the MySQL prepared statements for each DML statement.
329 330
    for (const TaggedStatement* tagged_statement = start_statement;
         tagged_statement != end_statement; ++tagged_statement) {
331 332 333 334 335
        if (tagged_statement->index >= statements_.size()) {
            statements_.resize(tagged_statement->index + 1, NULL);
            text_statements_.resize(tagged_statement->index + 1,
                                    std::string(""));
        }
336 337
        prepareStatement(tagged_statement->index,
                         tagged_statement->text);
338 339 340
    }
}

341 342 343 344 345
void MySqlConnection::clearStatements() {
    statements_.clear();
    text_statements_.clear();
}

346 347 348 349 350 351 352 353 354 355 356 357 358
/// @brief Destructor
MySqlConnection::~MySqlConnection() {
    // Free up the prepared statements, ignoring errors. (What would we do
    // about them? We're destroying this object and are not really concerned
    // with errors on a database connection that is about to go away.)
    for (int i = 0; i < statements_.size(); ++i) {
        if (statements_[i] != NULL) {
            (void) mysql_stmt_close(statements_[i]);
            statements_[i] = NULL;
        }
    }
    statements_.clear();
    text_statements_.clear();
359 360 361 362 363 364 365 366 367 368 369 370 371 372 373
}

// Time conversion methods.
//
// Note that the MySQL TIMESTAMP data type (used for "expire") converts data
// from the current timezone to UTC for storage, and from UTC to the current
// timezone for retrieval.
//
// This causes no problems providing that:
// a) cltt is given in local time
// b) We let the system take care of timezone conversion when converting
//    from a time read from the database into a local time.
void
MySqlConnection::convertToDatabaseTime(const time_t input_time,
                                       MYSQL_TIME& output_time) {
374
    MySqlBinding::convertToDatabaseTime(input_time, output_time);
375 376 377 378 379 380
}

void
MySqlConnection::convertToDatabaseTime(const time_t cltt,
                                     const uint32_t valid_lifetime,
                                     MYSQL_TIME& expire) {
381
    MySqlBinding::convertToDatabaseTime(cltt, valid_lifetime, expire);
382 383 384 385
}

void
MySqlConnection::convertFromDatabaseTime(const MYSQL_TIME& expire,
386 387
                                         uint32_t valid_lifetime, time_t& cltt) {
    MySqlBinding::convertFromDatabaseTime(expire, valid_lifetime, cltt);
388 389
}

390 391
void
MySqlConnection::startTransaction() {
392
    DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, MYSQL_START_TRANSACTION);
393 394 395 396 397 398 399
    // We create prepared statements for all other queries, but MySQL
    // don't support prepared statements for START TRANSACTION.
    int status = mysql_query(mysql_, "START TRANSACTION");
    if (status != 0) {
        isc_throw(DbOperationError, "unable to start transaction, "
                  "reason: " << mysql_error(mysql_));
    }
400 401
}

402 403
void
MySqlConnection::commit() {
404
    DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, MYSQL_COMMIT);
405 406 407 408 409 410 411 412
    if (mysql_commit(mysql_) != 0) {
        isc_throw(DbOperationError, "commit failed: "
                  << mysql_error(mysql_));
    }
}

void
MySqlConnection::rollback() {
413
    DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, MYSQL_ROLLBACK);
414 415 416 417
    if (mysql_rollback(mysql_) != 0) {
        isc_throw(DbOperationError, "rollback failed: "
                  << mysql_error(mysql_));
    }
418 419 420
}


421
} // namespace isc::db
422
} // namespace isc