Kea  2.3.9
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
int version()
returns Kea hooks version.
A generic exception that is thrown if a parameter given to a method is considered invalid in that con...
virtual const char * what() const
Returns a C-style character string of the cause of the exception.
A generic exception that is thrown if a parameter given to a method or function is considered invalid...
A generic exception that is thrown when an unexpected error condition occurs.
std::string getParameter(const std::string &name) const
Returns value of a connection parameter.
void checkUnusable()
Throws an exception if the connection is not usable.
std::map< std::string, std::string > ParameterMap
Database configuration parameter map.
Invalid port number.
Exception thrown on failure to open database.
Exception thrown on failure to execute a database function.
static void convertFromDatabaseTime(const MYSQL_TIME &expire, uint32_t valid_lifetime, time_t &cltt)
Converts Database Time to Lease Times.
static void convertToDatabaseTime(const time_t input_time, MYSQL_TIME &output_time)
Converts time_t value to database time.
Common MySQL Connector Pool.
MySqlHolder mysql_
MySQL connection handle.
void prepareStatement(uint32_t index, const char *text)
Prepare Single Statement.
std::vector< MYSQL_STMT * > statements_
Prepared statements.
bool isTransactionStarted() const
Checks if there is a transaction in progress.
std::vector< std::string > text_statements_
Raw text of statements.
bool tls_
TLS flag (true when TLS was required, false otherwise).
static void convertToDatabaseTime(const time_t input_time, MYSQL_TIME &output_time)
Convert time_t value to database time.
static void convertFromDatabaseTime(const MYSQL_TIME &expire, uint32_t valid_lifetime, time_t &cltt)
Convert Database Time to Lease Times.
void commit()
Commits current transaction.
void openDatabase()
Open Database.
void prepareStatements(const TaggedStatement *start_statement, const TaggedStatement *end_statement)
Prepare statements.
static std::pair< uint32_t, uint32_t > getVersion(const ParameterMap &parameters)
Get the schema version.
int transaction_ref_count_
Reference counter for transactions.
void startTransaction()
Starts new transaction.
virtual ~MySqlConnection()
Destructor.
void clearStatements()
Clears prepared statements and text statements.
void rollback()
Rollbacks current transaction.
void commit()
Commits transaction.
Exception thrown if name of database is not specified.
We want to reuse the database backend connection and exchange code for other uses,...
#define isc_throw(type, stream)
A shortcut macro to insert known values into exception arguments.
const int DB_DBG_TRACE_DETAIL
Database logging levels.
Definition: db_log.cc:21
const my_bool MLM_FALSE
MySQL false value.
const int MYSQL_DEFAULT_CONNECTION_TIMEOUT
@ MYSQL_START_TRANSACTION
Definition: db_log.h:64
@ MYSQL_ROLLBACK
Definition: db_log.h:66
@ MYSQL_COMMIT
Definition: db_log.h:65
bool my_bool
my_bool type in MySQL 8.x.
int MysqlExecuteStatement(MYSQL_STMT *stmt)
Execute a prepared statement.
bool isDir(const string &name)
Is a directory predicate.
Defines the logger used by the top-level component of kea-lfc.
MySQL Selection Statements.