Kea  2.1.7-git
mysql_connection.cc
Go to the documentation of this file.
1 // Copyright (C) 2012-2022 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 #include <util/file_utilities.h>
13 
14 #include <boost/lexical_cast.hpp>
15 
16 #include <algorithm>
17 #include <stdint.h>
18 #include <string>
19 #include <limits>
20 
21 using namespace isc;
22 using namespace std;
23 
24 namespace isc {
25 namespace db {
26 
27 int MySqlHolder::atexit_ = [] {
28  return atexit([] { mysql_library_end(); });
29 }();
30 
32 const int MYSQL_DEFAULT_CONNECTION_TIMEOUT = 5; // seconds
33 
34 MySqlTransaction::MySqlTransaction(MySqlConnection& conn)
35  : conn_(conn), committed_(false) {
36  conn_.startTransaction();
37 }
38 
40  // Rollback if the MySqlTransaction::commit wasn't explicitly
41  // called.
42  if (!committed_) {
43  conn_.rollback();
44  }
45 }
46 
47 void
49  conn_.commit();
50  committed_ = true;
51 }
52 
53 // Open the database using the parameters passed to the constructor.
54 
55 void
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  const char* ca_file(0);
160  const char* ca_dir(0);
161  string sca;
162  try {
163  sca = getParameter("trust-anchor");
164  tls_ = true;
165  if (util::file::isDir(sca)) {
166  ca_dir = sca.c_str();
167  } else {
168  ca_file = sca.c_str();
169  }
170  } catch (...) {
171  // No trust anchor
172  }
173 
174  const char* cert_file(0);
175  string scert;
176  try {
177  scert = getParameter("cert-file");
178  tls_ = true;
179  cert_file = scert.c_str();
180  } catch (...) {
181  // No client certificate file
182  }
183 
184  const char* key_file(0);
185  string skey;
186  try {
187  skey = getParameter("key-file");
188  tls_ = true;
189  key_file = skey.c_str();
190  } catch (...) {
191  // No private key file
192  }
193 
194  const char* cipher_list(0);
195  string scipher;
196  try {
197  scipher = getParameter("cipher-list");
198  tls_ = true;
199  cipher_list = scipher.c_str();
200  } catch (...) {
201  // No cipher list
202  }
203 
204  // Set options for the connection:
205  //
206  // Set options for the connection:
207  // Make sure auto_reconnect is OFF! Enabling it leaves us with an unusable
208  // connection after a reconnect as among other things, it drops all our
209  // pre-compiled statements.
210  my_bool auto_reconnect = MLM_FALSE;
211  int result = mysql_options(mysql_, MYSQL_OPT_RECONNECT, &auto_reconnect);
212  if (result != 0) {
213  isc_throw(DbOpenError, "unable to set auto-reconnect option: " <<
214  mysql_error(mysql_));
215  }
216 
217  // Make sure we have a large idle time window ... say 30 days...
218  const char *wait_time = "SET SESSION wait_timeout = 30 * 86400";
219  result = mysql_options(mysql_, MYSQL_INIT_COMMAND, wait_time);
220  if (result != 0) {
221  isc_throw(DbOpenError, "unable to set wait_timeout " <<
222  mysql_error(mysql_));
223  }
224 
225  // Set SQL mode options for the connection: SQL mode governs how what
226  // constitutes insertable data for a given column, and how to handle
227  // invalid data. We want to ensure we get the strictest behavior and
228  // to reject invalid data with an error.
229  const char *sql_mode = "SET SESSION sql_mode ='STRICT_ALL_TABLES'";
230  result = mysql_options(mysql_, MYSQL_INIT_COMMAND, sql_mode);
231  if (result != 0) {
232  isc_throw(DbOpenError, "unable to set SQL mode options: " <<
233  mysql_error(mysql_));
234  }
235 
236  // Connection timeout, the amount of time taken for the client to drop
237  // the connection if the server is not responding.
238  result = mysql_options(mysql_, MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
239  if (result != 0) {
240  isc_throw(DbOpenError, "unable to set database connection timeout: " <<
241  mysql_error(mysql_));
242  }
243 
244  // If TLS is enabled set it. If something should go wrong it will happen
245  // later at the mysql_real_connect call.
246  if (tls_) {
247  mysql_ssl_set(mysql_, key_file, cert_file, ca_file, ca_dir,
248  cipher_list);
249  }
250 
251  // Open the database.
252  //
253  // The option CLIENT_FOUND_ROWS is specified so that in an UPDATE,
254  // the affected rows are the number of rows found that match the
255  // WHERE clause of the SQL statement, not the rows changed. The reason
256  // here is that MySQL apparently does not update a row if data has not
257  // changed and so the "affected rows" (retrievable from MySQL) is zero.
258  // This makes it hard to distinguish whether the UPDATE changed no rows
259  // because no row matching the WHERE clause was found, or because a
260  // row was found but no data was altered.
261  MYSQL* status = mysql_real_connect(mysql_, host, user, password, name,
262  port, NULL, CLIENT_FOUND_ROWS);
263  if (status != mysql_) {
264  isc_throw(DbOpenError, mysql_error(mysql_));
265  }
266 
267  // Enable autocommit. In case transaction is explicitly used, this
268  // setting will be overwritten for the transaction. However, there are
269  // cases when lack of autocommit could cause transactions to hang
270  // until commit or rollback is explicitly called. This already
271  // caused issues for some unit tests which were unable to cleanup
272  // the database after the test because of pending transactions.
273  // Use of autocommit will eliminate this problem.
274  my_bool autocommit_result = mysql_autocommit(mysql_, 1);
275  if (autocommit_result != 0) {
276  isc_throw(DbOperationError, mysql_error(mysql_));
277  }
278 
279  // To avoid a flush to disk on every commit, the global parameter
280  // innodb_flush_log_at_trx_commit should be set to 2. This will cause the
281  // changes to be written to the log, but flushed to disk in the background
282  // every second. Setting the parameter to that value will speed up the
283  // system, but at the risk of losing data if the system crashes.
284 }
285 
286 // Get schema version.
287 
288 std::pair<uint32_t, uint32_t>
290  // Get a connection.
291  MySqlConnection conn(parameters);
292 
293  // Open the database.
294  conn.openDatabase();
295 
296  // Allocate a new statement.
297  MYSQL_STMT *stmt = mysql_stmt_init(conn.mysql_);
298  if (stmt == NULL) {
299  isc_throw(DbOperationError, "unable to allocate MySQL prepared "
300  "statement structure, reason: " << mysql_error(conn.mysql_));
301  }
302 
303  try {
304 
305  // Prepare the statement from SQL text.
306  const char* version_sql = "SELECT version, minor FROM schema_version";
307  int status = mysql_stmt_prepare(stmt, version_sql, strlen(version_sql));
308  if (status != 0) {
309  isc_throw(DbOperationError, "unable to prepare MySQL statement <"
310  << version_sql << ">, reason: "
311  << mysql_error(conn.mysql_));
312  }
313 
314  // Execute the prepared statement.
315  if (MysqlExecuteStatement(stmt) != 0) {
316  isc_throw(DbOperationError, "cannot execute schema version query <"
317  << version_sql << ">, reason: "
318  << mysql_errno(conn.mysql_));
319  }
320 
321  // Bind the output of the statement to the appropriate variables.
322  MYSQL_BIND bind[2];
323  memset(bind, 0, sizeof(bind));
324 
325  uint32_t version;
326  bind[0].buffer_type = MYSQL_TYPE_LONG;
327  bind[0].is_unsigned = 1;
328  bind[0].buffer = &version;
329  bind[0].buffer_length = sizeof(version);
330 
331  uint32_t minor;
332  bind[1].buffer_type = MYSQL_TYPE_LONG;
333  bind[1].is_unsigned = 1;
334  bind[1].buffer = &minor;
335  bind[1].buffer_length = sizeof(minor);
336 
337  if (mysql_stmt_bind_result(stmt, bind)) {
338  isc_throw(DbOperationError, "unable to bind result set for <"
339  << version_sql << ">, reason: "
340  << mysql_errno(conn.mysql_));
341  }
342 
343  // Fetch the data.
344  if (mysql_stmt_fetch(stmt)) {
345  isc_throw(DbOperationError, "unable to bind result set for <"
346  << version_sql << ">, reason: "
347  << mysql_errno(conn.mysql_));
348  }
349 
350  // Discard the statement and its resources
351  mysql_stmt_close(stmt);
352 
353  return (std::make_pair(version, minor));
354 
355  } catch (const std::exception&) {
356  // Avoid a memory leak on error.
357  mysql_stmt_close(stmt);
358 
359  // Send the exception to the caller.
360  throw;
361  }
362 }
363 
364 // Prepared statement setup. The textual form of an SQL statement is stored
365 // in a vector of strings (text_statements_) and is used in the output of
366 // error messages. The SQL statement is also compiled into a "prepared
367 // statement" (stored in statements_), which avoids the overhead of compilation
368 // during use. As prepared statements have resources allocated to them, the
369 // class destructor explicitly destroys them.
370 
371 void
372 MySqlConnection::prepareStatement(uint32_t index, const char* text) {
373  // Validate that there is space for the statement in the statements array
374  // and that nothing has been placed there before.
375  if ((index >= statements_.size()) || (statements_[index] != NULL)) {
376  isc_throw(InvalidParameter, "invalid prepared statement index (" <<
377  static_cast<int>(index) << ") or indexed prepared " <<
378  "statement is not null");
379  }
380 
381  // All OK, so prepare the statement
382  text_statements_[index] = std::string(text);
383  statements_[index] = mysql_stmt_init(mysql_);
384  if (statements_[index] == NULL) {
385  isc_throw(DbOperationError, "unable to allocate MySQL prepared "
386  "statement structure, reason: " << mysql_error(mysql_));
387  }
388 
389  int status = mysql_stmt_prepare(statements_[index], text, strlen(text));
390  if (status != 0) {
391  isc_throw(DbOperationError, "unable to prepare MySQL statement <" <<
392  text << ">, reason: " << mysql_error(mysql_));
393  }
394 }
395 
396 void
398  const TaggedStatement* end_statement) {
399  // Created the MySQL prepared statements for each DML statement.
400  for (const TaggedStatement* tagged_statement = start_statement;
401  tagged_statement != end_statement; ++tagged_statement) {
402  if (tagged_statement->index >= statements_.size()) {
403  statements_.resize(tagged_statement->index + 1, NULL);
404  text_statements_.resize(tagged_statement->index + 1,
405  std::string(""));
406  }
407  prepareStatement(tagged_statement->index,
408  tagged_statement->text);
409  }
410 }
411 
413  statements_.clear();
414  text_statements_.clear();
415 }
416 
419  // Free up the prepared statements, ignoring errors. (What would we do
420  // about them? We're destroying this object and are not really concerned
421  // with errors on a database connection that is about to go away.)
422  for (int i = 0; i < statements_.size(); ++i) {
423  if (statements_[i] != NULL) {
424  (void) mysql_stmt_close(statements_[i]);
425  statements_[i] = NULL;
426  }
427  }
428  statements_.clear();
429  text_statements_.clear();
430 }
431 
432 // Time conversion methods.
433 //
434 // Note that the MySQL TIMESTAMP data type (used for "expire") converts data
435 // from the current timezone to UTC for storage, and from UTC to the current
436 // timezone for retrieval.
437 //
438 // This causes no problems providing that:
439 // a) cltt is given in local time
440 // b) We let the system take care of timezone conversion when converting
441 // from a time read from the database into a local time.
442 void
444  MYSQL_TIME& output_time) {
445  MySqlBinding::convertToDatabaseTime(input_time, output_time);
446 }
447 
448 void
450  const uint32_t valid_lifetime,
451  MYSQL_TIME& expire) {
452  MySqlBinding::convertToDatabaseTime(cltt, valid_lifetime, expire);
453 }
454 
455 void
457  uint32_t valid_lifetime, time_t& cltt) {
458  MySqlBinding::convertFromDatabaseTime(expire, valid_lifetime, cltt);
459 }
460 
461 void
463  // If it is nested transaction, do nothing.
464  if (++transaction_ref_count_ > 1) {
465  return;
466  }
467 
469  checkUnusable();
470  // We create prepared statements for all other queries, but MySQL
471  // don't support prepared statements for START TRANSACTION.
472  int status = mysql_query(mysql_, "START TRANSACTION");
473  if (status != 0) {
474  isc_throw(DbOperationError, "unable to start transaction, "
475  "reason: " << mysql_error(mysql_));
476  }
477 }
478 
479 bool
481  return (transaction_ref_count_ > 0);
482 }
483 
484 void
486  if (transaction_ref_count_ <= 0) {
487  isc_throw(Unexpected, "commit called for not started transaction - coding error");
488  }
489 
490  // When committing nested transaction, do nothing.
491  if (--transaction_ref_count_ > 0) {
492  return;
493  }
495  checkUnusable();
496  if (mysql_commit(mysql_) != 0) {
497  isc_throw(DbOperationError, "commit failed: "
498  << mysql_error(mysql_));
499  }
500 }
501 
502 void
504  if (transaction_ref_count_ <= 0) {
505  isc_throw(Unexpected, "rollback called for not started transaction - coding error");
506  }
507 
508  // When rolling back nested transaction, do nothing.
509  if (--transaction_ref_count_ > 0) {
510  return;
511  }
513  checkUnusable();
514  if (mysql_rollback(mysql_) != 0) {
515  isc_throw(DbOperationError, "rollback failed: "
516  << mysql_error(mysql_));
517  }
518 }
519 
520 } // namespace db
521 } // namespace isc
We want to reuse the database backend connection and exchange code for other uses, in particular for hook libraries.
bool my_bool
my_bool type in MySQL 8.x.
MySqlHolder mysql_
MySQL connection handle.
static void convertToDatabaseTime(const time_t input_time, MYSQL_TIME &output_time)
Convert time_t value to database time.
static void convertToDatabaseTime(const time_t input_time, MYSQL_TIME &output_time)
Converts time_t value to database time.
A generic exception that is thrown if a parameter given to a method or function is considered invalid...
static std::pair< uint32_t, uint32_t > getVersion(const ParameterMap &parameters)
Get the schema version.
void commit()
Commits transaction.
STL namespace.
static void convertFromDatabaseTime(const MYSQL_TIME &expire, uint32_t valid_lifetime, time_t &cltt)
Convert Database Time to Lease Times.
Exception thrown on failure to open database.
int MysqlExecuteStatement(MYSQL_STMT *stmt)
Execute a prepared statement.
#define isc_throw(type, stream)
A shortcut macro to insert known values into exception arguments.
const my_bool MLM_FALSE
MySQL false value.
Exception thrown if name of database is not specified.
A generic exception that is thrown when an unexpected error condition occurs.
int version()
returns Kea hooks version.
static void convertFromDatabaseTime(const MYSQL_TIME &expire, uint32_t valid_lifetime, time_t &cltt)
Converts Database Time to Lease Times.
void clearStatements()
Clears prepared statements and text statements.
const int MYSQL_DEFAULT_CONNECTION_TIMEOUT
virtual ~MySqlConnection()
Destructor.
bool isDir(const string &name)
Is a directory predicate.
bool isTransactionStarted() const
Checks if there is a transaction in progress.
const int DB_DBG_TRACE_DETAIL
Database logging levels.
Definition: db_log.cc:21
Defines the logger used by the top-level component of kea-lfc.
void startTransaction()
Starts new transaction.
void rollback()
Rollbacks current transaction.
void commit()
Commits current transaction.
std::map< std::string, std::string > ParameterMap
Database configuration parameter map.
void prepareStatement(uint32_t index, const char *text)
Prepare Single Statement.
void prepareStatements(const TaggedStatement *start_statement, const TaggedStatement *end_statement)
Prepare statements.
void openDatabase()
Open Database.
MySQL Selection Statements.
Exception thrown on failure to execute a database function.
Common MySQL Connector Pool.