Kea  2.3.5-git
mysql_connection.cc
Go to the documentation of this file.
1 // Copyright (C) 2012-2023 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  try {
69  setIntParameterValue("port", 0, numeric_limits<uint16_t>::max(), port);
70 
71  } catch (const std::exception& ex) {
73  }
74 
75  const char* user = NULL;
76  string suser;
77  try {
78  suser = getParameter("user");
79  user = suser.c_str();
80  } catch (...) {
81  // No user. Fine, we'll use NULL
82  }
83 
84  const char* password = NULL;
85  string spassword;
86  try {
87  spassword = getParameter("password");
88  password = spassword.c_str();
89  } catch (...) {
90  // No password. Fine, we'll use NULL
91  }
92 
93  const char* name = NULL;
94  string sname;
95  try {
96  sname = getParameter("name");
97  name = sname.c_str();
98  } catch (...) {
99  // No database name. Throw a "NoName" exception
100  isc_throw(NoDatabaseName, "must specify a name for the database");
101  }
102 
103  unsigned int connect_timeout = MYSQL_DEFAULT_CONNECTION_TIMEOUT;
104  unsigned int read_timeout = 0;
105  unsigned int write_timeout = 0;
106  try {
107  // The timeout is only valid if greater than zero, as depending on the
108  // database, a zero timeout might signify something like "wait
109  // indefinitely".
110  setIntParameterValue("connect-timeout", 1, numeric_limits<int>::max(), connect_timeout);
111  // Other timeouts can be 0, meaning that the database client will follow a default
112  // behavior. Earlier MySQL versions didn't have these parameters, so we allow 0
113  // to skip setting them.
114  setIntParameterValue("read-timeout", 0, numeric_limits<int>::max(), read_timeout);
115  setIntParameterValue("write-timeout", 0, numeric_limits<int>::max(), write_timeout);
116 
117  } catch (const std::exception& ex) {
119  }
120 
121  const char* ca_file(0);
122  const char* ca_dir(0);
123  string sca;
124  try {
125  sca = getParameter("trust-anchor");
126  tls_ = true;
127  if (util::file::isDir(sca)) {
128  ca_dir = sca.c_str();
129  } else {
130  ca_file = sca.c_str();
131  }
132  } catch (...) {
133  // No trust anchor
134  }
135 
136  const char* cert_file(0);
137  string scert;
138  try {
139  scert = getParameter("cert-file");
140  tls_ = true;
141  cert_file = scert.c_str();
142  } catch (...) {
143  // No client certificate file
144  }
145 
146  const char* key_file(0);
147  string skey;
148  try {
149  skey = getParameter("key-file");
150  tls_ = true;
151  key_file = skey.c_str();
152  } catch (...) {
153  // No private key file
154  }
155 
156  const char* cipher_list(0);
157  string scipher;
158  try {
159  scipher = getParameter("cipher-list");
160  tls_ = true;
161  cipher_list = scipher.c_str();
162  } catch (...) {
163  // No cipher list
164  }
165 
166  // Set options for the connection:
167  //
168  // Set options for the connection:
169  // Make sure auto_reconnect is OFF! Enabling it leaves us with an unusable
170  // connection after a reconnect as among other things, it drops all our
171  // pre-compiled statements.
172  my_bool auto_reconnect = MLM_FALSE;
173  int result = mysql_options(mysql_, MYSQL_OPT_RECONNECT, &auto_reconnect);
174  if (result != 0) {
175  isc_throw(DbOpenError, "unable to set auto-reconnect option: " <<
176  mysql_error(mysql_));
177  }
178 
179  // Make sure we have a large idle time window ... say 30 days...
180  const char *wait_time = "SET SESSION wait_timeout = 30 * 86400";
181  result = mysql_options(mysql_, MYSQL_INIT_COMMAND, wait_time);
182  if (result != 0) {
183  isc_throw(DbOpenError, "unable to set wait_timeout " <<
184  mysql_error(mysql_));
185  }
186 
187  // Set SQL mode options for the connection: SQL mode governs how what
188  // constitutes insertable data for a given column, and how to handle
189  // invalid data. We want to ensure we get the strictest behavior and
190  // to reject invalid data with an error.
191  const char *sql_mode = "SET SESSION sql_mode ='STRICT_ALL_TABLES'";
192  result = mysql_options(mysql_, MYSQL_INIT_COMMAND, sql_mode);
193  if (result != 0) {
194  isc_throw(DbOpenError, "unable to set SQL mode options: " <<
195  mysql_error(mysql_));
196  }
197 
198  // Connection timeout, the amount of time taken for the client to drop
199  // the connection if the server is not responding.
200  result = mysql_options(mysql_, MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
201  if (result != 0) {
202  isc_throw(DbOpenError, "unable to set database connection timeout: " <<
203  mysql_error(mysql_));
204  }
205 
206  // Set the read timeout if it has been specified. Otherwise, the timeout is
207  // not used.
208  if (read_timeout > 0) {
209  result = mysql_options(mysql_, MYSQL_OPT_READ_TIMEOUT, &read_timeout);
210  if (result != 0) {
211  isc_throw(DbOpenError, "unable to set database read timeout: " <<
212  mysql_error(mysql_));
213  }
214  }
215 
216  // Set the write timeout if it has been specified. Otherwise, the timeout
217  // is not used.
218  if (write_timeout > 0) {
219  result = mysql_options(mysql_, MYSQL_OPT_WRITE_TIMEOUT, &write_timeout);
220  if (result != 0) {
221  isc_throw(DbOpenError, "unable to set database write timeout: " <<
222  mysql_error(mysql_));
223  }
224  }
225 
226  // If TLS is enabled set it. If something should go wrong it will happen
227  // later at the mysql_real_connect call.
228  if (tls_) {
229  mysql_ssl_set(mysql_, key_file, cert_file, ca_file, ca_dir,
230  cipher_list);
231  }
232 
233  // Open the database.
234  //
235  // The option CLIENT_FOUND_ROWS is specified so that in an UPDATE,
236  // the affected rows are the number of rows found that match the
237  // WHERE clause of the SQL statement, not the rows changed. The reason
238  // here is that MySQL apparently does not update a row if data has not
239  // changed and so the "affected rows" (retrievable from MySQL) is zero.
240  // This makes it hard to distinguish whether the UPDATE changed no rows
241  // because no row matching the WHERE clause was found, or because a
242  // row was found but no data was altered.
243  MYSQL* status = mysql_real_connect(mysql_, host, user, password, name,
244  port, NULL, CLIENT_FOUND_ROWS);
245  if (status != mysql_) {
246  isc_throw(DbOpenError, mysql_error(mysql_));
247  }
248 
249  // Enable autocommit. In case transaction is explicitly used, this
250  // setting will be overwritten for the transaction. However, there are
251  // cases when lack of autocommit could cause transactions to hang
252  // until commit or rollback is explicitly called. This already
253  // caused issues for some unit tests which were unable to cleanup
254  // the database after the test because of pending transactions.
255  // Use of autocommit will eliminate this problem.
256  my_bool autocommit_result = mysql_autocommit(mysql_, 1);
257  if (autocommit_result != 0) {
258  isc_throw(DbOperationError, mysql_error(mysql_));
259  }
260 
261  // To avoid a flush to disk on every commit, the global parameter
262  // innodb_flush_log_at_trx_commit should be set to 2. This will cause the
263  // changes to be written to the log, but flushed to disk in the background
264  // every second. Setting the parameter to that value will speed up the
265  // system, but at the risk of losing data if the system crashes.
266 }
267 
268 // Get schema version.
269 
270 std::pair<uint32_t, uint32_t>
272  // Get a connection.
273  MySqlConnection conn(parameters);
274 
275  // Open the database.
276  conn.openDatabase();
277 
278  // Allocate a new statement.
279  MYSQL_STMT *stmt = mysql_stmt_init(conn.mysql_);
280  if (stmt == NULL) {
281  isc_throw(DbOperationError, "unable to allocate MySQL prepared "
282  "statement structure, reason: " << mysql_error(conn.mysql_));
283  }
284 
285  try {
286 
287  // Prepare the statement from SQL text.
288  const char* version_sql = "SELECT version, minor FROM schema_version";
289  int status = mysql_stmt_prepare(stmt, version_sql, strlen(version_sql));
290  if (status != 0) {
291  isc_throw(DbOperationError, "unable to prepare MySQL statement <"
292  << version_sql << ">, reason: "
293  << mysql_error(conn.mysql_));
294  }
295 
296  // Execute the prepared statement.
297  if (MysqlExecuteStatement(stmt) != 0) {
298  isc_throw(DbOperationError, "cannot execute schema version query <"
299  << version_sql << ">, reason: "
300  << mysql_errno(conn.mysql_));
301  }
302 
303  // Bind the output of the statement to the appropriate variables.
304  MYSQL_BIND bind[2];
305  memset(bind, 0, sizeof(bind));
306 
307  uint32_t version;
308  bind[0].buffer_type = MYSQL_TYPE_LONG;
309  bind[0].is_unsigned = 1;
310  bind[0].buffer = &version;
311  bind[0].buffer_length = sizeof(version);
312 
313  uint32_t minor;
314  bind[1].buffer_type = MYSQL_TYPE_LONG;
315  bind[1].is_unsigned = 1;
316  bind[1].buffer = &minor;
317  bind[1].buffer_length = sizeof(minor);
318 
319  if (mysql_stmt_bind_result(stmt, bind)) {
320  isc_throw(DbOperationError, "unable to bind result set for <"
321  << version_sql << ">, reason: "
322  << mysql_errno(conn.mysql_));
323  }
324 
325  // Fetch the data.
326  if (mysql_stmt_fetch(stmt)) {
327  isc_throw(DbOperationError, "unable to bind result set for <"
328  << version_sql << ">, reason: "
329  << mysql_errno(conn.mysql_));
330  }
331 
332  // Discard the statement and its resources
333  mysql_stmt_close(stmt);
334 
335  return (std::make_pair(version, minor));
336 
337  } catch (const std::exception&) {
338  // Avoid a memory leak on error.
339  mysql_stmt_close(stmt);
340 
341  // Send the exception to the caller.
342  throw;
343  }
344 }
345 
346 // Prepared statement setup. The textual form of an SQL statement is stored
347 // in a vector of strings (text_statements_) and is used in the output of
348 // error messages. The SQL statement is also compiled into a "prepared
349 // statement" (stored in statements_), which avoids the overhead of compilation
350 // during use. As prepared statements have resources allocated to them, the
351 // class destructor explicitly destroys them.
352 
353 void
354 MySqlConnection::prepareStatement(uint32_t index, const char* text) {
355  // Validate that there is space for the statement in the statements array
356  // and that nothing has been placed there before.
357  if ((index >= statements_.size()) || (statements_[index] != NULL)) {
358  isc_throw(InvalidParameter, "invalid prepared statement index (" <<
359  static_cast<int>(index) << ") or indexed prepared " <<
360  "statement is not null");
361  }
362 
363  // All OK, so prepare the statement
364  text_statements_[index] = std::string(text);
365  statements_[index] = mysql_stmt_init(mysql_);
366  if (statements_[index] == NULL) {
367  isc_throw(DbOperationError, "unable to allocate MySQL prepared "
368  "statement structure, reason: " << mysql_error(mysql_));
369  }
370 
371  int status = mysql_stmt_prepare(statements_[index], text, strlen(text));
372  if (status != 0) {
373  isc_throw(DbOperationError, "unable to prepare MySQL statement <" <<
374  text << ">, reason: " << mysql_error(mysql_));
375  }
376 }
377 
378 void
380  const TaggedStatement* end_statement) {
381  // Created the MySQL prepared statements for each DML statement.
382  for (const TaggedStatement* tagged_statement = start_statement;
383  tagged_statement != end_statement; ++tagged_statement) {
384  if (tagged_statement->index >= statements_.size()) {
385  statements_.resize(tagged_statement->index + 1, NULL);
386  text_statements_.resize(tagged_statement->index + 1,
387  std::string(""));
388  }
389  prepareStatement(tagged_statement->index,
390  tagged_statement->text);
391  }
392 }
393 
395  statements_.clear();
396  text_statements_.clear();
397 }
398 
401  // Free up the prepared statements, ignoring errors. (What would we do
402  // about them? We're destroying this object and are not really concerned
403  // with errors on a database connection that is about to go away.)
404  for (int i = 0; i < statements_.size(); ++i) {
405  if (statements_[i] != NULL) {
406  (void) mysql_stmt_close(statements_[i]);
407  statements_[i] = NULL;
408  }
409  }
410  statements_.clear();
411  text_statements_.clear();
412 }
413 
414 // Time conversion methods.
415 //
416 // Note that the MySQL TIMESTAMP data type (used for "expire") converts data
417 // from the current timezone to UTC for storage, and from UTC to the current
418 // timezone for retrieval.
419 //
420 // This causes no problems providing that:
421 // a) cltt is given in local time
422 // b) We let the system take care of timezone conversion when converting
423 // from a time read from the database into a local time.
424 void
426  MYSQL_TIME& output_time) {
427  MySqlBinding::convertToDatabaseTime(input_time, output_time);
428 }
429 
430 void
432  const uint32_t valid_lifetime,
433  MYSQL_TIME& expire) {
434  MySqlBinding::convertToDatabaseTime(cltt, valid_lifetime, expire);
435 }
436 
437 void
439  uint32_t valid_lifetime, time_t& cltt) {
440  MySqlBinding::convertFromDatabaseTime(expire, valid_lifetime, cltt);
441 }
442 
443 void
445  // If it is nested transaction, do nothing.
446  if (++transaction_ref_count_ > 1) {
447  return;
448  }
449 
451  checkUnusable();
452  // We create prepared statements for all other queries, but MySQL
453  // don't support prepared statements for START TRANSACTION.
454  int status = mysql_query(mysql_, "START TRANSACTION");
455  if (status != 0) {
456  isc_throw(DbOperationError, "unable to start transaction, "
457  "reason: " << mysql_error(mysql_));
458  }
459 }
460 
461 bool
463  return (transaction_ref_count_ > 0);
464 }
465 
466 void
468  if (transaction_ref_count_ <= 0) {
469  isc_throw(Unexpected, "commit called for not started transaction - coding error");
470  }
471 
472  // When committing nested transaction, do nothing.
473  if (--transaction_ref_count_ > 0) {
474  return;
475  }
477  checkUnusable();
478  if (mysql_commit(mysql_) != 0) {
479  isc_throw(DbOperationError, "commit failed: "
480  << mysql_error(mysql_));
481  }
482 }
483 
484 void
486  if (transaction_ref_count_ <= 0) {
487  isc_throw(Unexpected, "rollback called for not started transaction - coding error");
488  }
489 
490  // When rolling back nested transaction, do nothing.
491  if (--transaction_ref_count_ > 0) {
492  return;
493  }
495  checkUnusable();
496  if (mysql_rollback(mysql_) != 0) {
497  isc_throw(DbOperationError, "rollback failed: "
498  << mysql_error(mysql_));
499  }
500 }
501 
502 template<typename T>
503 void
504 MySqlConnection::setIntParameterValue(const std::string& name, int64_t min, int64_t max, T& value) {
505  string svalue;
506  try {
507  svalue = getParameter(name);
508  } catch (...) {
509  // Do nothing if the parameter is not present.
510  }
511  if (svalue.empty()) {
512  return;
513  }
514  try {
515  // Try to convert the value.
516  auto parsed_value = boost::lexical_cast<T>(svalue);
517  // Check if the value is within the specified range.
518  if ((parsed_value < min) || (parsed_value > max)) {
519  isc_throw(BadValue, "bad " << svalue << " value");
520  }
521  // Everything is fine. Return the parsed value.
522  value = parsed_value;
523 
524  } catch (...) {
525  // We may end up here when lexical_cast fails or when the
526  // parsed value is not within the desired range. In both
527  // cases let's throw the same general error.
528  isc_throw(BadValue, name << " parameter (" <<
529  svalue << ") must be an integer between "
530  << min << " and " << max);
531  }
532 }
533 
534 } // namespace db
535 } // 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.
virtual const char * what() const
Returns a C-style character string of the cause of the exception.
Invalid port number.
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.
A generic exception that is thrown if a parameter given to a method is considered invalid in that con...
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.