1 // Copyright (C) 2012-2021 Internet Systems Consortium, Inc. ("ISC")
2 //
3 // This Source Code Form is subject to the terms of the Mozilla Public
4 // License, v. 2.0. If a copy of the MPL was not distributed with this
5 // file, You can obtain one at http://mozilla.org/MPL/2.0/.
6
7 #include <config.h>
8
9 #include <database/db_log.h>
10 #include <exceptions/exceptions.h>
11 #include <mysql/mysql_connection.h>
12
13 #include <boost/lexical_cast.hpp>
14
15 #include <algorithm>
16 #include <stdint.h>
17 #include <string>
18 #include <limits>
19
20 using namespace isc;
21 using namespace std;
22
23 namespace isc {
24 namespace db {
25
__anon6d6833570102null26 int MySqlHolder::atexit_ = [] {
27 return atexit([] { mysql_library_end(); });
28 }();
29
30 /// @todo: Migrate this default value to src/bin/dhcpX/simple_parserX.cc
31 const int MYSQL_DEFAULT_CONNECTION_TIMEOUT = 5; // seconds
32
MySqlTransaction(MySqlConnection & conn)33 MySqlTransaction::MySqlTransaction(MySqlConnection& conn)
34 : conn_(conn), committed_(false) {
35 conn_.startTransaction();
36 }
37
~MySqlTransaction()38 MySqlTransaction::~MySqlTransaction() {
39 // Rollback if the MySqlTransaction::commit wasn't explicitly
40 // called.
41 if (!committed_) {
42 conn_.rollback();
43 }
44 }
45
46 void
commit()47 MySqlTransaction::commit() {
48 conn_.commit();
49 committed_ = true;
50 }
51
52
53 // Open the database using the parameters passed to the constructor.
54
55 void
openDatabase()56 MySqlConnection::openDatabase() {
57 // Set up the values of the parameters
58 const char* host = "localhost";
59 string shost;
60 try {
61 shost = getParameter("host");
62 host = shost.c_str();
63 } catch (...) {
64 // No host. Fine, we'll use "localhost"
65 }
66
67 unsigned int port = 0;
68 string sport;
69 try {
70 sport = getParameter("port");
71 } catch (...) {
72 // No port parameter, we are going to use the default port.
73 sport = "";
74 }
75
76 if (sport.size() > 0) {
77 // Port was given, so try to convert it to an integer.
78
79 try {
80 port = boost::lexical_cast<unsigned int>(sport);
81 } catch (...) {
82 // Port given but could not be converted to an unsigned int.
83 // Just fall back to the default value.
84 port = 0;
85 }
86
87 // The port is only valid when it is in the 0..65535 range.
88 // Again fall back to the default when the given value is invalid.
89 if (port > numeric_limits<uint16_t>::max()) {
90 port = 0;
91 }
92 }
93
94 const char* user = NULL;
95 string suser;
96 try {
97 suser = getParameter("user");
98 user = suser.c_str();
99 } catch (...) {
100 // No user. Fine, we'll use NULL
101 }
102
103 const char* password = NULL;
104 string spassword;
105 try {
106 spassword = getParameter("password");
107 password = spassword.c_str();
108 } catch (...) {
109 // No password. Fine, we'll use NULL
110 }
111
112 const char* name = NULL;
113 string sname;
114 try {
115 sname = getParameter("name");
116 name = sname.c_str();
117 } catch (...) {
118 // No database name. Throw a "NoName" exception
119 isc_throw(NoDatabaseName, "must specify a name for the database");
120 }
121
122 unsigned int connect_timeout = MYSQL_DEFAULT_CONNECTION_TIMEOUT;
123 string stimeout;
124 try {
125 stimeout = getParameter("connect-timeout");
126 } catch (...) {
127 // No timeout parameter, we are going to use the default timeout.
128 stimeout = "";
129 }
130
131 if (stimeout.size() > 0) {
132 // Timeout was given, so try to convert it to an integer.
133
134 try {
135 connect_timeout = boost::lexical_cast<unsigned int>(stimeout);
136 } catch (...) {
137 // Timeout given but could not be converted to an unsigned int. Set
138 // the connection timeout to an invalid value to trigger throwing
139 // of an exception.
140 connect_timeout = 0;
141 }
142
143 // The timeout is only valid if greater than zero, as depending on the
144 // database, a zero timeout might signify something like "wait
145 // indefinitely".
146 //
147 // The check below also rejects a value greater than the maximum
148 // integer value. The lexical_cast operation used to obtain a numeric
149 // value from a string can get confused if trying to convert a negative
150 // integer to an unsigned int: instead of throwing an exception, it may
151 // produce a large positive value.
152 if ((connect_timeout == 0) ||
153 (connect_timeout > numeric_limits<int>::max())) {
154 isc_throw(DbInvalidTimeout, "database connection timeout (" <<
155 stimeout << ") must be an integer greater than 0");
156 }
157 }
158
159 // Set options for the connection:
160 //
161 // Set options for the connection:
162 // Make sure auto_reconnect is OFF! Enabling it leaves us with an unusable
163 // connection after a reconnect as among other things, it drops all our
164 // pre-compiled statements.
165 my_bool auto_reconnect = MLM_FALSE;
166 int result = mysql_options(mysql_, MYSQL_OPT_RECONNECT, &auto_reconnect);
167 if (result != 0) {
168 isc_throw(DbOpenError, "unable to set auto-reconnect option: " <<
169 mysql_error(mysql_));
170 }
171
172 // Make sure we have a large idle time window ... say 30 days...
173 const char *wait_time = "SET SESSION wait_timeout = 30 * 86400";
174 result = mysql_options(mysql_, MYSQL_INIT_COMMAND, wait_time);
175 if (result != 0) {
176 isc_throw(DbOpenError, "unable to set wait_timeout " <<
177 mysql_error(mysql_));
178 }
179
180 // Set SQL mode options for the connection: SQL mode governs how what
181 // constitutes insertable data for a given column, and how to handle
182 // invalid data. We want to ensure we get the strictest behavior and
183 // to reject invalid data with an error.
184 const char *sql_mode = "SET SESSION sql_mode ='STRICT_ALL_TABLES'";
185 result = mysql_options(mysql_, MYSQL_INIT_COMMAND, sql_mode);
186 if (result != 0) {
187 isc_throw(DbOpenError, "unable to set SQL mode options: " <<
188 mysql_error(mysql_));
189 }
190
191 // Connection timeout, the amount of time taken for the client to drop
192 // the connection if the server is not responding.
193 result = mysql_options(mysql_, MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
194 if (result != 0) {
195 isc_throw(DbOpenError, "unable to set database connection timeout: " <<
196 mysql_error(mysql_));
197 }
198
199 // Open the database.
200 //
201 // The option CLIENT_FOUND_ROWS is specified so that in an UPDATE,
202 // the affected rows are the number of rows found that match the
203 // WHERE clause of the SQL statement, not the rows changed. The reason
204 // here is that MySQL apparently does not update a row if data has not
205 // changed and so the "affected rows" (retrievable from MySQL) is zero.
206 // This makes it hard to distinguish whether the UPDATE changed no rows
207 // because no row matching the WHERE clause was found, or because a
208 // row was found but no data was altered.
209 MYSQL* status = mysql_real_connect(mysql_, host, user, password, name,
210 port, NULL, CLIENT_FOUND_ROWS);
211 if (status != mysql_) {
212 isc_throw(DbOpenError, mysql_error(mysql_));
213 }
214
215 // Enable autocommit. In case transaction is explicitly used, this
216 // setting will be overwritten for the transaction. However, there are
217 // cases when lack of autocommit could cause transactions to hang
218 // until commit or rollback is explicitly called. This already
219 // caused issues for some unit tests which were unable to cleanup
220 // the database after the test because of pending transactions.
221 // Use of autocommit will eliminate this problem.
222 my_bool autocommit_result = mysql_autocommit(mysql_, 1);
223 if (autocommit_result != 0) {
224 isc_throw(DbOperationError, mysql_error(mysql_));
225 }
226
227 // To avoid a flush to disk on every commit, the global parameter
228 // innodb_flush_log_at_trx_commit should be set to 2. This will cause the
229 // changes to be written to the log, but flushed to disk in the background
230 // every second. Setting the parameter to that value will speed up the
231 // system, but at the risk of losing data if the system crashes.
232 }
233
234 // Get schema version.
235
236 std::pair<uint32_t, uint32_t>
getVersion(const ParameterMap & parameters)237 MySqlConnection::getVersion(const ParameterMap& parameters) {
238 // Get a connection.
239 MySqlConnection conn(parameters);
240
241 // Open the database.
242 conn.openDatabase();
243
244 // Allocate a new statement.
245 MYSQL_STMT *stmt = mysql_stmt_init(conn.mysql_);
246 if (stmt == NULL) {
247 isc_throw(DbOperationError, "unable to allocate MySQL prepared "
248 "statement structure, reason: " << mysql_error(conn.mysql_));
249 }
250
251 try {
252
253 // Prepare the statement from SQL text.
254 const char* version_sql = "SELECT version, minor FROM schema_version";
255 int status = mysql_stmt_prepare(stmt, version_sql, strlen(version_sql));
256 if (status != 0) {
257 isc_throw(DbOperationError, "unable to prepare MySQL statement <"
258 << version_sql << ">, reason: "
259 << mysql_error(conn.mysql_));
260 }
261
262 // Execute the prepared statement.
263 if (MysqlExecuteStatement(stmt) != 0) {
264 isc_throw(DbOperationError, "cannot execute schema version query <"
265 << version_sql << ">, reason: "
266 << mysql_errno(conn.mysql_));
267 }
268
269 // Bind the output of the statement to the appropriate variables.
270 MYSQL_BIND bind[2];
271 memset(bind, 0, sizeof(bind));
272
273 uint32_t version;
274 bind[0].buffer_type = MYSQL_TYPE_LONG;
275 bind[0].is_unsigned = 1;
276 bind[0].buffer = &version;
277 bind[0].buffer_length = sizeof(version);
278
279 uint32_t minor;
280 bind[1].buffer_type = MYSQL_TYPE_LONG;
281 bind[1].is_unsigned = 1;
282 bind[1].buffer = &minor;
283 bind[1].buffer_length = sizeof(minor);
284
285 if (mysql_stmt_bind_result(stmt, bind)) {
286 isc_throw(DbOperationError, "unable to bind result set for <"
287 << version_sql << ">, reason: "
288 << mysql_errno(conn.mysql_));
289 }
290
291 // Fetch the data.
292 if (mysql_stmt_fetch(stmt)) {
293 isc_throw(DbOperationError, "unable to bind result set for <"
294 << version_sql << ">, reason: "
295 << mysql_errno(conn.mysql_));
296 }
297
298 // Discard the statement and its resources
299 mysql_stmt_close(stmt);
300
301 return (std::make_pair(version, minor));
302
303 } catch (const std::exception&) {
304 // Avoid a memory leak on error.
305 mysql_stmt_close(stmt);
306
307 // Send the exception to the caller.
308 throw;
309 }
310 }
311
312 // Prepared statement setup. The textual form of an SQL statement is stored
313 // in a vector of strings (text_statements_) and is used in the output of
314 // error messages. The SQL statement is also compiled into a "prepared
315 // statement" (stored in statements_), which avoids the overhead of compilation
316 // during use. As prepared statements have resources allocated to them, the
317 // class destructor explicitly destroys them.
318
319 void
prepareStatement(uint32_t index,const char * text)320 MySqlConnection::prepareStatement(uint32_t index, const char* text) {
321 // Validate that there is space for the statement in the statements array
322 // and that nothing has been placed there before.
323 if ((index >= statements_.size()) || (statements_[index] != NULL)) {
324 isc_throw(InvalidParameter, "invalid prepared statement index (" <<
325 static_cast<int>(index) << ") or indexed prepared " <<
326 "statement is not null");
327 }
328
329 // All OK, so prepare the statement
330 text_statements_[index] = std::string(text);
331 statements_[index] = mysql_stmt_init(mysql_);
332 if (statements_[index] == NULL) {
333 isc_throw(DbOperationError, "unable to allocate MySQL prepared "
334 "statement structure, reason: " << mysql_error(mysql_));
335 }
336
337 int status = mysql_stmt_prepare(statements_[index], text, strlen(text));
338 if (status != 0) {
339 isc_throw(DbOperationError, "unable to prepare MySQL statement <" <<
340 text << ">, reason: " << mysql_error(mysql_));
341 }
342 }
343
344 void
prepareStatements(const TaggedStatement * start_statement,const TaggedStatement * end_statement)345 MySqlConnection::prepareStatements(const TaggedStatement* start_statement,
346 const TaggedStatement* end_statement) {
347 // Created the MySQL prepared statements for each DML statement.
348 for (const TaggedStatement* tagged_statement = start_statement;
349 tagged_statement != end_statement; ++tagged_statement) {
350 if (tagged_statement->index >= statements_.size()) {
351 statements_.resize(tagged_statement->index + 1, NULL);
352 text_statements_.resize(tagged_statement->index + 1,
353 std::string(""));
354 }
355 prepareStatement(tagged_statement->index,
356 tagged_statement->text);
357 }
358 }
359
clearStatements()360 void MySqlConnection::clearStatements() {
361 statements_.clear();
362 text_statements_.clear();
363 }
364
365 /// @brief Destructor
~MySqlConnection()366 MySqlConnection::~MySqlConnection() {
367 // Free up the prepared statements, ignoring errors. (What would we do
368 // about them? We're destroying this object and are not really concerned
369 // with errors on a database connection that is about to go away.)
370 for (int i = 0; i < statements_.size(); ++i) {
371 if (statements_[i] != NULL) {
372 (void) mysql_stmt_close(statements_[i]);
373 statements_[i] = NULL;
374 }
375 }
376 statements_.clear();
377 text_statements_.clear();
378 }
379
380 // Time conversion methods.
381 //
382 // Note that the MySQL TIMESTAMP data type (used for "expire") converts data
383 // from the current timezone to UTC for storage, and from UTC to the current
384 // timezone for retrieval.
385 //
386 // This causes no problems providing that:
387 // a) cltt is given in local time
388 // b) We let the system take care of timezone conversion when converting
389 // from a time read from the database into a local time.
390 void
convertToDatabaseTime(const time_t input_time,MYSQL_TIME & output_time)391 MySqlConnection::convertToDatabaseTime(const time_t input_time,
392 MYSQL_TIME& output_time) {
393 MySqlBinding::convertToDatabaseTime(input_time, output_time);
394 }
395
396 void
convertToDatabaseTime(const time_t cltt,const uint32_t valid_lifetime,MYSQL_TIME & expire)397 MySqlConnection::convertToDatabaseTime(const time_t cltt,
398 const uint32_t valid_lifetime,
399 MYSQL_TIME& expire) {
400 MySqlBinding::convertToDatabaseTime(cltt, valid_lifetime, expire);
401 }
402
403 void
convertFromDatabaseTime(const MYSQL_TIME & expire,uint32_t valid_lifetime,time_t & cltt)404 MySqlConnection::convertFromDatabaseTime(const MYSQL_TIME& expire,
405 uint32_t valid_lifetime, time_t& cltt) {
406 MySqlBinding::convertFromDatabaseTime(expire, valid_lifetime, cltt);
407 }
408
409 void
startTransaction()410 MySqlConnection::startTransaction() {
411 // If it is nested transaction, do nothing.
412 if (++transaction_ref_count_ > 1) {
413 return;
414 }
415
416 DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, MYSQL_START_TRANSACTION);
417 checkUnusable();
418 // We create prepared statements for all other queries, but MySQL
419 // don't support prepared statements for START TRANSACTION.
420 int status = mysql_query(mysql_, "START TRANSACTION");
421 if (status != 0) {
422 isc_throw(DbOperationError, "unable to start transaction, "
423 "reason: " << mysql_error(mysql_));
424 }
425 }
426
427 bool
isTransactionStarted() const428 MySqlConnection::isTransactionStarted() const {
429 return (transaction_ref_count_ > 0);
430 }
431
432 void
commit()433 MySqlConnection::commit() {
434 if (transaction_ref_count_ <= 0) {
435 isc_throw(Unexpected, "commit called for not started transaction - coding error");
436 }
437
438 // When committing nested transaction, do nothing.
439 if (--transaction_ref_count_ > 0) {
440 return;
441 }
442 DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, MYSQL_COMMIT);
443 checkUnusable();
444 if (mysql_commit(mysql_) != 0) {
445 isc_throw(DbOperationError, "commit failed: "
446 << mysql_error(mysql_));
447 }
448 }
449
450 void
rollback()451 MySqlConnection::rollback() {
452 if (transaction_ref_count_ <= 0) {
453 isc_throw(Unexpected, "rollback called for not started transaction - coding error");
454 }
455
456 // When rolling back nested transaction, do nothing.
457 if (--transaction_ref_count_ > 0) {
458 return;
459 }
460 DB_LOG_DEBUG(DB_DBG_TRACE_DETAIL, MYSQL_ROLLBACK);
461 checkUnusable();
462 if (mysql_rollback(mysql_) != 0) {
463 isc_throw(DbOperationError, "rollback failed: "
464 << mysql_error(mysql_));
465 }
466 }
467
468 } // namespace db
469 } // namespace isc
470