mysql_connection.h 23.5 KB
Newer Older
1
// Copyright (C) 2012-2018 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 9

#ifndef MYSQL_CONNECTION_H
#define MYSQL_CONNECTION_H

10
#include <database/database_connection.h>
11
#include <database/db_exceptions.h>
12
#include <database/db_log.h>
13
#include <exceptions/exceptions.h>
14 15
#include <mysql/mysql_binding.h>
#include <mysql/mysql_constants.h>
16
#include <boost/scoped_ptr.hpp>
17
#include <mysql.h>
18 19
#include <mysqld_error.h>
#include <errmsg.h>
20
#include <functional>
21
#include <vector>
22
#include <stdint.h>
23 24

namespace isc {
25
namespace db {
26

27

28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
/// @brief Fetch and Release MySQL Results
///
/// When a MySQL statement is expected, to fetch the results the function
/// mysql_stmt_fetch() must be called.  As well as getting data, this
/// allocates internal state.  Subsequent calls to mysql_stmt_fetch can be
/// made, but when all the data is retrieved, mysql_stmt_free_result must be
/// called to free up the resources allocated.
///
/// Created prior to the first fetch, this class's destructor calls
/// mysql_stmt_free_result, so eliminating the need for an explicit release
/// in the method calling mysql_stmt_free_result.  In this way, it guarantees
/// that the resources are released even if the MySqlLeaseMgr method concerned
/// exits via an exception.

class MySqlFreeResult {
public:

    /// @brief Constructor
    ///
    /// Store the pointer to the statement for which data is being fetched.
    ///
    /// Note that according to the MySQL documentation, mysql_stmt_free_result
    /// only releases resources if a cursor has been allocated for the
    /// statement.  This implies that it is a no-op if none have been.  Either
    /// way, any error from mysql_stmt_free_result is ignored. (Generating
    /// an exception is not much help, as it will only confuse things if the
    /// method calling mysql_stmt_fetch is exiting via an exception.)
    MySqlFreeResult(MYSQL_STMT* statement) : statement_(statement)
    {}

    /// @brief Destructor
    ///
    /// Frees up fetch context if a fetch has been successfully executed.
    ~MySqlFreeResult() {
        (void) mysql_stmt_free_result(statement_);
    }

private:
    MYSQL_STMT*     statement_;     ///< Statement for which results are freed
};

69 70 71 72 73 74 75 76 77 78
/// @brief MySQL Selection Statements
///
/// Each statement is associated with an index, which is used to reference the
/// associated prepared statement.

struct TaggedStatement {
    uint32_t index;
    const char* text;
};

79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
/// @brief MySQL Handle Holder
///
/// Small RAII object for safer initialization, will close the database
/// connection upon destruction.  This means that if an exception is thrown
/// during database initialization, resources allocated to the database are
/// guaranteed to be freed.
///
/// It makes no sense to copy an object of this class.  After the copy, both
/// objects would contain pointers to the same MySql context object.  The
/// destruction of one would invalid the context in the remaining object.
/// For this reason, the class is declared noncopyable.
class MySqlHolder : public boost::noncopyable {
public:

    /// @brief Constructor
    ///
95
    /// Push a call to mysql_library_end() at exit time.
96 97 98 99
    /// Initialize MySql and store the associated context object.
    ///
    /// @throw DbOpenError Unable to initialize MySql handle.
    MySqlHolder() : mysql_(mysql_init(NULL)) {
100 101 102 103
        if (!atexit_) {
            atexit([]{ mysql_library_end(); });
            atexit_ = true;
        }
104
        if (mysql_ == NULL) {
105
            isc_throw(db::DbOpenError, "unable to initialize MySQL");
106 107 108 109 110 111 112 113 114 115
        }
    }

    /// @brief Destructor
    ///
    /// Frees up resources allocated by the initialization of MySql.
    ~MySqlHolder() {
        if (mysql_ != NULL) {
            mysql_close(mysql_);
        }
116
        // @note Moved the call to mysql_library_end() to atexit.
117 118 119 120 121 122 123 124 125 126 127
    }

    /// @brief Conversion Operator
    ///
    /// Allows the MySqlHolder object to be passed as the context argument to
    /// mysql_xxx functions.
    operator MYSQL*() const {
        return (mysql_);
    }

private:
128 129 130
    static bool atexit_; ///< Flag to call atexit once.

    MYSQL* mysql_;       ///< Initialization context
131 132
};

133
/// @brief Forward declaration to @ref MySqlConnection.
134 135
class MySqlConnection;

136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
/// @brief RAII object representing MySQL transaction.
///
/// An instance of this class should be created in a scope where multiple
/// INSERT statements should be executed within a single transaction. The
/// transaction is started when the constructor of this class is invoked.
/// The transaction is ended when the @ref MySqlTransaction::commit is
/// explicitly called or when the instance of this class is destroyed.
/// The @ref MySqlTransaction::commit commits changes to the database
/// and the changes remain in the database when the instance of the
/// class is destroyed. If the class instance is destroyed before the
/// @ref MySqlTransaction::commit is called, the transaction is rolled
/// back. The rollback on destruction guarantees that partial data is
/// not stored in the database when there is an error during any
/// of the operations belonging to a transaction.
///
/// The default MySQL backend configuration enables 'autocommit'.
/// Starting a transaction overrides 'autocommit' setting for this
/// particular transaction only. It does not affect the global 'autocommit'
/// setting for the database connection, i.e. all modifications to the
/// database which don't use transactions will still be auto committed.
156 157 158
class MySqlTransaction : public boost::noncopyable {
public:

159 160 161 162 163 164 165 166
    /// @brief Constructor.
    ///
    /// Starts transaction by making a "START TRANSACTION" query.
    ///
    /// @param conn MySQL connection to use for the transaction. This
    /// connection will be later used to commit or rollback changes.
    ///
    /// @throw DbOperationError if "START TRANSACTION" query fails.
167 168
    MySqlTransaction(MySqlConnection& conn);

169 170 171
    /// @brief Destructor.
    ///
    /// Rolls back the transaction if changes haven't been committed.
172 173
    ~MySqlTransaction();

174
    /// @brief Commits transaction.
175 176 177 178
    void commit();

private:

179
    /// @brief Holds reference to the MySQL database connection.
180 181
    MySqlConnection& conn_;

182 183 184 185
    /// @brief Boolean flag indicating if the transaction has been committed.
    ///
    /// This flag is used in the class destructor to assess if the
    /// transaction should be rolled back.
186 187 188 189
    bool committed_;
};


190 191
/// @brief Common MySQL Connector Pool
///
192 193 194 195 196
/// This class provides common operations for MySQL database connection
/// used by both MySqlLeaseMgr and MySqlHostDataSource. It manages connecting
/// to the database and preparing compiled statements. Its fields are
/// public, because they are used (both set and retrieved) in classes
/// that use instances of MySqlConnection.
197
class MySqlConnection : public db::DatabaseConnection {
198 199
public:

200
    /// @brief Function invoked to process fetched row.
201
    typedef std::function<void(MySqlBindingCollection&)> ConsumeResultFun;
202

203 204 205
    /// @brief Constructor
    ///
    /// Initialize MySqlConnection object with parameters needed for connection.
206
    MySqlConnection(const ParameterMap& parameters)
207
        : DatabaseConnection(parameters) {
208 209
    }

210
    /// @brief Destructor
211
    virtual ~MySqlConnection();
212 213 214 215 216 217 218 219 220 221 222

    /// @brief Prepare Single Statement
    ///
    /// Creates a prepared statement from the text given and adds it to the
    /// statements_ vector at the given index.
    ///
    /// @param index Index into the statements_ vector into which the text
    ///        should be placed.  The vector must be big enough for the index
    ///        to be valid, else an exception will be thrown.
    /// @param text Text of the SQL statement to be prepared.
    ///
223
    /// @throw isc::db::DbOperationError An operation on the open database has
224 225
    ///        failed.
    /// @throw isc::InvalidParameter 'index' is not valid for the vector.
226
    void prepareStatement(uint32_t index, const char* text);
227 228 229 230 231

    /// @brief Prepare statements
    ///
    /// Creates the prepared statements for all of the SQL statements used
    /// by the MySQL backend.
232 233 234 235 236
    ///
    /// @param start_statement Pointer to the first statement in range of the
    /// statements to be compiled.
    /// @param end_statement Pointer to the statement marking end of the
    /// range of statements to be compiled. This last statement is not compiled.
237
    ///
238
    /// @throw isc::db::DbOperationError An operation on the open database has
239 240 241
    ///        failed.
    /// @throw isc::InvalidParameter 'index' is not valid for the vector.  This
    ///        represents an internal error within the code.
242
    void prepareStatements(const TaggedStatement* start_statement,
243
                           const TaggedStatement* end_statement);
244

245 246 247
    /// @brief Clears prepared statements and text statements.
    void clearStatements();

248 249 250 251 252 253 254 255 256
    /// @brief Open Database
    ///
    /// Opens the database using the information supplied in the parameters
    /// passed to the constructor.
    ///
    /// @throw NoDatabaseName Mandatory database name not given
    /// @throw DbOpenError Error opening the database
    void openDatabase();

257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317
    ///@{
    /// The following methods are used to convert between times and time
    /// intervals stored in the Lease object, and the times stored in the
    /// database.  The reason for the difference is because in the DHCP server,
    /// the cltt (Client Time Since Last Transmission) is the natural data; in
    /// the lease file - which may be read by the user - it is the expiry time
    /// of the lease.

    /// @brief Convert time_t value to database time.
    ///
    /// @param input_time A time_t value representing time.
    /// @param output_time Reference to MYSQL_TIME object where converted time
    ///        will be put.
    static
    void convertToDatabaseTime(const time_t input_time, MYSQL_TIME& output_time);

    /// @brief Convert Lease Time to Database Times
    ///
    /// Within the DHCP servers, times are stored as client last transmit time
    /// and valid lifetime.  In the database, the information is stored as
    /// valid lifetime and "expire" (time of expiry of the lease).  They are
    /// related by the equation:
    ///
    /// - expire = client last transmit time + valid lifetime
    ///
    /// This method converts from the times in the lease object into times
    /// able to be added to the database.
    ///
    /// @param cltt Client last transmit time
    /// @param valid_lifetime Valid lifetime
    /// @param expire Reference to MYSQL_TIME object where the expiry time of
    ///        the lease will be put.
    ///
    /// @throw isc::BadValue if the sum of the calculated expiration time is
    /// greater than the value of @c LeaseMgr::MAX_DB_TIME.
    static
    void convertToDatabaseTime(const time_t cltt, const uint32_t valid_lifetime,
            MYSQL_TIME& expire);

    /// @brief Convert Database Time to Lease Times
    ///
    /// Within the database, time is stored as "expire" (time of expiry of the
    /// lease) and valid lifetime.  In the DHCP server, the information is
    /// stored client last transmit time and valid lifetime.  These are related
    /// by the equation:
    ///
    /// - client last transmit time = expire - valid_lifetime
    ///
    /// This method converts from the times in the database into times
    /// able to be inserted into the lease object.
    ///
    /// @param expire Reference to MYSQL_TIME object from where the expiry
    ///        time of the lease is taken.
    /// @param valid_lifetime lifetime of the lease.
    /// @param cltt Reference to location where client last transmit time
    ///        is put.
    static
    void convertFromDatabaseTime(const MYSQL_TIME& expire,
            uint32_t valid_lifetime, time_t& cltt);
    ///@}

318 319 320
    /// @brief Starts Transaction
    void startTransaction();

321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349
    /// @brief Executes SELECT query using prepared statement.
    ///
    /// The statement index must point to an existing prepared statement
    /// associated with the connection. The @c in_bindings size must match
    /// the number of placeholders in the prepared statement. The size of
    /// the @c out_bindings must match the number of selected columns. The
    /// output bindings must be created and must encapsulate values of
    /// the appropriate type, e.g. string, uint32_t etc.
    ///
    /// This method executes prepared statement using provided bindings and
    /// calls @c process_result function for each returned row. The
    /// @c process_result function is implemented by the caller and should
    /// gather and store each returned row in an external data structure prior
    /// to returning because the values in the @c out_bindings will be
    /// overwritten by the values of the next returned row when this function
    /// is called again.
    ///
    /// @tparam StatementIndex Type of the statement index enum.
    ///
    /// @param index Index of the query to be executed.
    /// @param in_bindings Input bindings holding values to substitue placeholders
    /// in the query.
    /// @param [out] out_bindings Output bindings where retrieved data will be
    /// stored.
    /// @param process_result Pointer to the function to be invoked for each
    /// retrieved row. This function consumes the retrieved data from the
    /// output bindings.
    template<typename StatementIndex>
    void selectQuery(const StatementIndex& index,
350 351
                     const MySqlBindingCollection& in_bindings,
                     MySqlBindingCollection& out_bindings,
352 353 354 355 356 357 358 359 360 361 362
                     ConsumeResultFun process_result) {
        // Extract native input bindings.
        std::vector<MYSQL_BIND> in_bind_vec;
        for (MySqlBindingPtr in_binding : in_bindings) {
            in_bind_vec.push_back(in_binding->getMySqlBinding());
        }

        int status = 0;
        if (!in_bind_vec.empty()) {
            // Bind parameters to the prepared statement.
            status = mysql_stmt_bind_param(statements_[index], &in_bind_vec[0]);
363
            checkError(status, index, "unable to bind parameters for select");
364 365 366 367 368 369 370 371 372
        }

        // Bind variables that will receive results as well.
        std::vector<MYSQL_BIND> out_bind_vec;
        for (MySqlBindingPtr out_binding : out_bindings) {
            out_bind_vec.push_back(out_binding->getMySqlBinding());
        }
        if (!out_bind_vec.empty()) {
            status = mysql_stmt_bind_result(statements_[index], &out_bind_vec[0]);
373
            checkError(status, index, "unable to bind result parameters for select");
374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427
        }

        // Execute query.
        status = mysql_stmt_execute(statements_[index]);
        checkError(status, index, "unable to execute");

        status = mysql_stmt_store_result(statements_[index]);
        checkError(status, index, "unable to set up for storing all results");

        // Fetch results.
        MySqlFreeResult fetch_release(statements_[index]);
        while ((status = mysql_stmt_fetch(statements_[index])) ==
               MLM_MYSQL_FETCH_SUCCESS) {
            try {
                // For each returned row call user function which should
                // consume the row and copy the data to a safe place.
                process_result(out_bindings);

            } catch (const std::exception& ex) {
                // Rethrow the exception with a bit more data.
                isc_throw(BadValue, ex.what() << ". Statement is <" <<
                          text_statements_[index] << ">");
            }
        }

        // How did the fetch end?
        // If mysql_stmt_fetch return value is equal to 1 an error occurred.
        if (status == MLM_MYSQL_FETCH_FAILURE) {
            // Error - unable to fetch results
            checkError(status, index, "unable to fetch results");

        } else if (status == MYSQL_DATA_TRUNCATED) {
            // Data truncated - throw an exception indicating what was at fault
            isc_throw(DataTruncated, text_statements_[index]
                      << " returned truncated data");
        }
    }

    /// @brief Executes INSERT prepared statement.
    ///
    /// The statement index must point to an existing prepared statement
    /// associated with the connection. The @c in_bindings size must match
    /// the number of placeholders in the prepared statement.
    ///
    /// This method executes prepared statement using provided bindings to
    /// insert data into the database.
    ///
    /// @tparam StatementIndex Type of the statement index enum.
    ///
    /// @param index Index of the query to be executed.
    /// @param in_bindings Input bindings holding values to substitue placeholders
    /// in the query.
    template<typename StatementIndex>
    void insertQuery(const StatementIndex& index,
428
                     const MySqlBindingCollection& in_bindings) {
429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465
        std::vector<MYSQL_BIND> in_bind_vec;
        for (MySqlBindingPtr in_binding : in_bindings) {
            in_bind_vec.push_back(in_binding->getMySqlBinding());
        }

        // Bind the parameters to the statement
        int status = mysql_stmt_bind_param(statements_[index], &in_bind_vec[0]);
        checkError(status, index, "unable to bind parameters");

        // Execute the statement
        status = mysql_stmt_execute(statements_[index]);

        if (status != 0) {
            // Failure: check for the special case of duplicate entry.
            if (mysql_errno(mysql_) == ER_DUP_ENTRY) {
                isc_throw(DuplicateEntry, "Database duplicate entry error");
        }
            checkError(status, index, "unable to execute");
        }
    }

    /// @brief Executes UPDATE or DELETE prepared statement and returns
    /// the number of affected rows.
    ///
    /// The statement index must point to an existing prepared statement
    /// associated with the connection. The @c in_bindings size must match
    /// the number of placeholders in the prepared statement.
    ///
    /// @tparam StatementIndex Type of the statement index enum.
    ///
    /// @param index Index of the query to be executed.
    /// @param in_bindings Input bindings holding values to substitue placeholders
    /// in the query.
    ///
    /// @return Number of affected rows.
    template<typename StatementIndex>
    uint64_t updateDeleteQuery(const StatementIndex& index,
466
                               const MySqlBindingCollection& in_bindings) {
467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487
        std::vector<MYSQL_BIND> in_bind_vec;
        for (MySqlBindingPtr in_binding : in_bindings) {
            in_bind_vec.push_back(in_binding->getMySqlBinding());
        }

        // Bind the parameters to the statement
        int status = mysql_stmt_bind_param(statements_[index], &in_bind_vec[0]);
        checkError(status, index, "unable to bind parameters");

        // Execute the statement
        status = mysql_stmt_execute(statements_[index]);

        if (status != 0) {
            checkError(status, index, "unable to execute");
        }

        // Let's return how many rows were affected.
        return (static_cast<uint64_t>(mysql_stmt_affected_rows(statements_[index])));
    }


488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503
    /// @brief Commit Transactions
    ///
    /// Commits all pending database operations. On databases that don't
    /// support transactions, this is a no-op.
    ///
    /// @throw DbOperationError If the commit failed.
    void commit();

    /// @brief Rollback Transactions
    ///
    /// Rolls back all pending database operations. On databases that don't
    /// support transactions, this is a no-op.
    ///
    /// @throw DbOperationError If the rollback failed.
    void rollback();

504 505 506 507 508 509 510 511 512 513
    /// @brief Check Error and Throw Exception
    ///
    /// Virtually all MySQL functions return a status which, if non-zero,
    /// indicates an error.  This function centralizes the error checking
    /// code.
    ///
    /// It is used to determine whether or not the function succeeded, and
    /// in the event of failures, decide whether or not those failures are
    /// recoverable.
    ///
514 515 516 517 518 519
    /// If the error is recoverable, the function will throw a DbOperationError.
    /// If the error is deemed unrecoverable, such as a loss of connectivity
    /// with the server, the function will call invokeDbLostCallback(). If the
    /// invocation returns false then either there is no callback registered
    /// or the callback has elected not to attempt to reconnect, and exit(-1)
    /// is called;
520
    ///
521 522 523
    /// If the invocation returns true, this indicates the calling layer will
    /// attempt recovery, and the function throws a DbOperationError to allow
    /// the caller to error handle the failed db access attempt.
524 525 526 527 528 529 530 531
    ///
    /// @param status Status code: non-zero implies an error
    /// @param index Index of statement that caused the error
    /// @param what High-level description of the error
    ///
    /// @tparam Enumeration representing index of a statement to which an
    /// error pertains.
    ///
532
    /// @throw isc::db::DbOperationError An operation on the open database has
533 534 535 536 537 538 539 540
    ///        failed.
    template<typename StatementIndex>
    void checkError(const int status, const StatementIndex& index,
                    const char* what) const {
        if (status != 0) {
            switch(mysql_errno(mysql_)) {
                // These are the ones we consider fatal. Remember this method is
                // used to check errors of API calls made subsequent to successfully
Josh Soref's avatar
Josh Soref committed
541
                // connecting.  Errors occurring while attempting to connect are
542 543 544 545 546 547 548
                // checked in the connection code. An alternative would be to call
                // mysql_ping() - assuming autoreconnect is off. If that fails
                // then we know connection is toast.
            case CR_SERVER_GONE_ERROR:
            case CR_SERVER_LOST:
            case CR_OUT_OF_MEMORY:
            case CR_CONNECTION_ERROR:
549
                DB_LOG_ERROR(db::MYSQL_FATAL_ERROR)
550 551 552 553
                    .arg(what)
                    .arg(text_statements_[static_cast<int>(index)])
                    .arg(mysql_error(mysql_))
                    .arg(mysql_errno(mysql_));
554

555 556 557 558 559 560 561 562
                // If there's no lost db callback or it returns false,
                // then we're not attempting to recover so we're done
                if (!invokeDbLostCallback()) {
                    exit (-1);
                }

                // We still need to throw so caller can error out of the current
                // processing.
563
                isc_throw(db::DbOperationError,
564
                          "fatal database errror or connectivity lost");
565 566
            default:
                // Connection is ok, so it must be an SQL error
567
                isc_throw(db::DbOperationError, what << " for <"
568 569 570 571 572 573 574 575
                          << text_statements_[static_cast<int>(index)]
                          << ">, reason: "
                          << mysql_error(mysql_) << " (error code "
                          << mysql_errno(mysql_) << ")");
            }
        }
    }

576 577 578 579 580
    /// @brief Prepared statements
    ///
    /// This field is public, because it is used heavily from MySqlConnection
    /// and will be from MySqlHostDataSource.
    std::vector<MYSQL_STMT*> statements_;
581

582 583 584 585 586
    /// @brief Raw text of statements
    ///
    /// This field is public, because it is used heavily from MySqlConnection
    /// and will be from MySqlHostDataSource.
    std::vector<std::string> text_statements_;
587

588
    /// @brief MySQL connection handle
589 590 591
    ///
    /// This field is public, because it is used heavily from MySqlConnection
    /// and will be from MySqlHostDataSource.
592 593 594
    MySqlHolder mysql_;
};

595
}; // end of isc::db namespace
596 597 598
}; // end of isc namespace

#endif // MYSQL_CONNECTION_H