Kea  2.5.3
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  return (std::make_pair(version, minor));
335 
336  } catch (const std::exception&) {
337  // Avoid a memory leak on error.
338  mysql_stmt_close(stmt);
339 
340  // Send the exception to the caller.
341  throw;
342  }
343 }
344 
345 // Prepared statement setup. The textual form of an SQL statement is stored
346 // in a vector of strings (text_statements_) and is used in the output of
347 // error messages. The SQL statement is also compiled into a "prepared
348 // statement" (stored in statements_), which avoids the overhead of compilation
349 // during use. As prepared statements have resources allocated to them, the
350 // class destructor explicitly destroys them.
351 
352 void
353 MySqlConnection::prepareStatement(uint32_t index, const char* text) {
354  // Validate that there is space for the statement in the statements array
355  // and that nothing has been placed there before.
356  if ((index >= statements_.size()) || (statements_[index] != NULL)) {
357  isc_throw(InvalidParameter, "invalid prepared statement index (" <<
358  static_cast<int>(index) << ") or indexed prepared " <<
359  "statement is not null");
360  }
361 
362  // All OK, so prepare the statement
363  text_statements_[index] = std::string(text);
364  statements_[index] = mysql_stmt_init(mysql_);
365  if (statements_[index] == NULL) {
366  isc_throw(DbOperationError, "unable to allocate MySQL prepared "
367  "statement structure, reason: " << mysql_error(mysql_));
368  }
369 
370  int status = mysql_stmt_prepare(statements_[index], text, strlen(text));
371  if (status != 0) {
372  isc_throw(DbOperationError, "unable to prepare MySQL statement <" <<
373  text << ">, reason: " << mysql_error(mysql_));
374  }
375 }
376 
377 void
379  const TaggedStatement* end_statement) {
380  // Created the MySQL prepared statements for each DML statement.
381  for (const TaggedStatement* tagged_statement = start_statement;
382  tagged_statement != end_statement; ++tagged_statement) {
383  if (tagged_statement->index >= statements_.size()) {
384  statements_.resize(tagged_statement->index + 1, NULL);
385  text_statements_.resize(tagged_statement->index + 1,
386  std::string(""));
387  }
388  prepareStatement(tagged_statement->index,
389  tagged_statement->text);
390  }
391 }
392 
395  // Free up the prepared statements, ignoring errors. (What would we do
396  // about them? We're destroying this object and are not really concerned
397  // with errors on a database connection that is about to go away.)
398  for (int i = 0; i < statements_.size(); ++i) {
399  if (statements_[i] != NULL) {
400  (void) mysql_stmt_close(statements_[i]);
401  statements_[i] = NULL;
402  }
403  }
404  statements_.clear();
405  text_statements_.clear();
406 }
407 
408 // Time conversion methods.
409 //
410 // Note that the MySQL TIMESTAMP data type (used for "expire") converts data
411 // from the current timezone to UTC for storage, and from UTC to the current
412 // timezone for retrieval.
413 //
414 // This causes no problems providing that:
415 // a) cltt is given in local time
416 // b) We let the system take care of timezone conversion when converting
417 // from a time read from the database into a local time.
418 void
420  MYSQL_TIME& output_time) {
421  MySqlBinding::convertToDatabaseTime(input_time, output_time);
422 }
423 
424 void
426  const uint32_t valid_lifetime,
427  MYSQL_TIME& expire) {
428  MySqlBinding::convertToDatabaseTime(cltt, valid_lifetime, expire);
429 }
430 
431 void
433  uint32_t valid_lifetime, time_t& cltt) {
434  MySqlBinding::convertFromDatabaseTime(expire, valid_lifetime, cltt);
435 }
436 
437 void
439  // If it is nested transaction, do nothing.
440  if (++transaction_ref_count_ > 1) {
441  return;
442  }
443 
445  checkUnusable();
446  // We create prepared statements for all other queries, but MySQL
447  // don't support prepared statements for START TRANSACTION.
448  int status = mysql_query(mysql_, "START TRANSACTION");
449  if (status != 0) {
450  isc_throw(DbOperationError, "unable to start transaction, "
451  "reason: " << mysql_error(mysql_));
452  }
453 }
454 
455 bool
457  return (transaction_ref_count_ > 0);
458 }
459 
460 void
462  if (transaction_ref_count_ <= 0) {
463  isc_throw(Unexpected, "commit called for not started transaction - coding error");
464  }
465 
466  // When committing nested transaction, do nothing.
467  if (--transaction_ref_count_ > 0) {
468  return;
469  }
471  checkUnusable();
472  if (mysql_commit(mysql_) != 0) {
473  isc_throw(DbOperationError, "commit failed: "
474  << mysql_error(mysql_));
475  }
476 }
477 
478 void
480  if (transaction_ref_count_ <= 0) {
481  isc_throw(Unexpected, "rollback called for not started transaction - coding error");
482  }
483 
484  // When rolling back nested transaction, do nothing.
485  if (--transaction_ref_count_ > 0) {
486  return;
487  }
489  checkUnusable();
490  if (mysql_rollback(mysql_) != 0) {
491  isc_throw(DbOperationError, "rollback failed: "
492  << mysql_error(mysql_));
493  }
494 }
495 
496 template<typename T>
497 void
498 MySqlConnection::setIntParameterValue(const std::string& name, int64_t min, int64_t max, T& value) {
499  string svalue;
500  try {
501  svalue = getParameter(name);
502  } catch (...) {
503  // Do nothing if the parameter is not present.
504  }
505  if (svalue.empty()) {
506  return;
507  }
508  try {
509  // Try to convert the value.
510  auto parsed_value = boost::lexical_cast<T>(svalue);
511  // Check if the value is within the specified range.
512  if ((parsed_value < min) || (parsed_value > max)) {
513  isc_throw(BadValue, "bad " << svalue << " value");
514  }
515  // Everything is fine. Return the parsed value.
516  value = parsed_value;
517 
518  } catch (...) {
519  // We may end up here when lexical_cast fails or when the
520  // parsed value is not within the desired range. In both
521  // cases let's throw the same general error.
522  isc_throw(BadValue, name << " parameter (" <<
523  svalue << ") must be an integer between "
524  << min << " and " << max);
525  }
526 }
527 
528 } // namespace db
529 } // 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.
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 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.