mysql_connection.cc 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402
  1. // Copyright (C) 2012-2017 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. #include <dhcpsrv/dhcpsrv_log.h>
  7. #include <dhcpsrv/mysql_connection.h>
  8. #include <exceptions/exceptions.h>
  9. #include <boost/lexical_cast.hpp>
  10. #include <algorithm>
  11. #include <stdint.h>
  12. #include <string>
  13. #include <limits>
  14. using namespace isc;
  15. using namespace isc::dhcp;
  16. using namespace std;
  17. namespace isc {
  18. namespace dhcp {
  19. const my_bool MLM_FALSE = 0;
  20. const my_bool MLM_TRUE = 1;
  21. const int MLM_MYSQL_FETCH_SUCCESS = 0;
  22. const int MLM_MYSQL_FETCH_FAILURE = 1;
  23. /// @todo: Migrate this default value to src/bin/dhcpX/simple_parserX.cc
  24. const int MYSQL_DEFAULT_CONNECTION_TIMEOUT = 5; // seconds
  25. MySqlTransaction::MySqlTransaction(MySqlConnection& conn)
  26. : conn_(conn), committed_(false) {
  27. conn_.startTransaction();
  28. }
  29. MySqlTransaction::~MySqlTransaction() {
  30. // Rollback if the MySqlTransaction::commit wasn't explicitly
  31. // called.
  32. if (!committed_) {
  33. conn_.rollback();
  34. }
  35. }
  36. void
  37. MySqlTransaction::commit() {
  38. conn_.commit();
  39. committed_ = true;
  40. }
  41. // Open the database using the parameters passed to the constructor.
  42. void
  43. MySqlConnection::openDatabase() {
  44. // Set up the values of the parameters
  45. const char* host = "localhost";
  46. string shost;
  47. try {
  48. shost = getParameter("host");
  49. host = shost.c_str();
  50. } catch (...) {
  51. // No host. Fine, we'll use "localhost"
  52. }
  53. unsigned int port = 0;
  54. string sport;
  55. try {
  56. sport = getParameter("port");
  57. } catch (...) {
  58. // No port parameter, we are going to use the default port.
  59. sport = "";
  60. }
  61. if (sport.size() > 0) {
  62. // Port was given, so try to convert it to an integer.
  63. try {
  64. port = boost::lexical_cast<unsigned int>(sport);
  65. } catch (...) {
  66. // Port given but could not be converted to an unsigned int.
  67. // Just fall back to the default value.
  68. port = 0;
  69. }
  70. // The port is only valid when it is in the 0..65535 range.
  71. // Again fall back to the default when the given value is invalid.
  72. if (port > numeric_limits<uint16_t>::max()) {
  73. port = 0;
  74. }
  75. }
  76. const char* user = NULL;
  77. string suser;
  78. try {
  79. suser = getParameter("user");
  80. user = suser.c_str();
  81. } catch (...) {
  82. // No user. Fine, we'll use NULL
  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. const char* name = NULL;
  93. string sname;
  94. try {
  95. sname = getParameter("name");
  96. name = sname.c_str();
  97. } catch (...) {
  98. // No database name. Throw a "NoName" exception
  99. isc_throw(NoDatabaseName, "must specify a name for the database");
  100. }
  101. unsigned int connect_timeout = MYSQL_DEFAULT_CONNECTION_TIMEOUT;
  102. string stimeout;
  103. try {
  104. stimeout = getParameter("connect-timeout");
  105. } catch (...) {
  106. // No timeout parameter, we are going to use the default timeout.
  107. stimeout = "";
  108. }
  109. if (stimeout.size() > 0) {
  110. // Timeout was given, so try to convert it to an integer.
  111. try {
  112. connect_timeout = boost::lexical_cast<unsigned int>(stimeout);
  113. } catch (...) {
  114. // Timeout given but could not be converted to an unsigned int. Set
  115. // the connection timeout to an invalid value to trigger throwing
  116. // of an exception.
  117. connect_timeout = 0;
  118. }
  119. // The timeout is only valid if greater than zero, as depending on the
  120. // database, a zero timeout might signify something like "wait
  121. // indefinitely".
  122. //
  123. // The check below also rejects a value greater than the maximum
  124. // integer value. The lexical_cast operation used to obtain a numeric
  125. // value from a string can get confused if trying to convert a negative
  126. // integer to an unsigned int: instead of throwing an exception, it may
  127. // produce a large positive value.
  128. if ((connect_timeout == 0) ||
  129. (connect_timeout > numeric_limits<int>::max())) {
  130. isc_throw(DbInvalidTimeout, "database connection timeout (" <<
  131. stimeout << ") must be an integer greater than 0");
  132. }
  133. }
  134. // Set options for the connection:
  135. //
  136. // Set options for the connection:
  137. // Make sure auto_reconnect is OFF! Enabling it leaves us with an unusable
  138. // connection after a reconnect as among other things, it drops all our
  139. // pre-compiled statements.
  140. my_bool auto_reconnect = MLM_FALSE;
  141. int result = mysql_options(mysql_, MYSQL_OPT_RECONNECT, &auto_reconnect);
  142. if (result != 0) {
  143. isc_throw(DbOpenError, "unable to set auto-reconnect option: " <<
  144. mysql_error(mysql_));
  145. }
  146. // Make sure we have a large idle time window ... say 30 days...
  147. const char *wait_time = "SET SESSION wait_timeout = 30 * 86400";
  148. result = mysql_options(mysql_, MYSQL_INIT_COMMAND, wait_time);
  149. if (result != 0) {
  150. isc_throw(DbOpenError, "unable to set wait_timeout " <<
  151. mysql_error(mysql_));
  152. }
  153. // Set SQL mode options for the connection: SQL mode governs how what
  154. // constitutes insertable data for a given column, and how to handle
  155. // invalid data. We want to ensure we get the strictest behavior and
  156. // to reject invalid data with an error.
  157. const char *sql_mode = "SET SESSION sql_mode ='STRICT_ALL_TABLES'";
  158. result = mysql_options(mysql_, MYSQL_INIT_COMMAND, sql_mode);
  159. if (result != 0) {
  160. isc_throw(DbOpenError, "unable to set SQL mode options: " <<
  161. mysql_error(mysql_));
  162. }
  163. // Connection timeout, the amount of time taken for the client to drop
  164. // the connection if the server is not responding.
  165. result = mysql_options(mysql_, MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
  166. if (result != 0) {
  167. isc_throw(DbOpenError, "unable to set database connection timeout: " <<
  168. mysql_error(mysql_));
  169. }
  170. // Open the database.
  171. //
  172. // The option CLIENT_FOUND_ROWS is specified so that in an UPDATE,
  173. // the affected rows are the number of rows found that match the
  174. // WHERE clause of the SQL statement, not the rows changed. The reason
  175. // here is that MySQL apparently does not update a row if data has not
  176. // changed and so the "affected rows" (retrievable from MySQL) is zero.
  177. // This makes it hard to distinguish whether the UPDATE changed no rows
  178. // because no row matching the WHERE clause was found, or because a
  179. // row was found but no data was altered.
  180. MYSQL* status = mysql_real_connect(mysql_, host, user, password, name,
  181. port, NULL, CLIENT_FOUND_ROWS);
  182. if (status != mysql_) {
  183. isc_throw(DbOpenError, mysql_error(mysql_));
  184. }
  185. }
  186. // Prepared statement setup. The textual form of an SQL statement is stored
  187. // in a vector of strings (text_statements_) and is used in the output of
  188. // error messages. The SQL statement is also compiled into a "prepared
  189. // statement" (stored in statements_), which avoids the overhead of compilation
  190. // during use. As prepared statements have resources allocated to them, the
  191. // class destructor explicitly destroys them.
  192. void
  193. MySqlConnection::prepareStatement(uint32_t index, const char* text) {
  194. // Validate that there is space for the statement in the statements array
  195. // and that nothing has been placed there before.
  196. if ((index >= statements_.size()) || (statements_[index] != NULL)) {
  197. isc_throw(InvalidParameter, "invalid prepared statement index (" <<
  198. static_cast<int>(index) << ") or indexed prepared " <<
  199. "statement is not null");
  200. }
  201. // All OK, so prepare the statement
  202. text_statements_[index] = std::string(text);
  203. statements_[index] = mysql_stmt_init(mysql_);
  204. if (statements_[index] == NULL) {
  205. isc_throw(DbOperationError, "unable to allocate MySQL prepared "
  206. "statement structure, reason: " << mysql_error(mysql_));
  207. }
  208. int status = mysql_stmt_prepare(statements_[index], text, strlen(text));
  209. if (status != 0) {
  210. isc_throw(DbOperationError, "unable to prepare MySQL statement <" <<
  211. text << ">, reason: " << mysql_error(mysql_));
  212. }
  213. }
  214. void
  215. MySqlConnection::prepareStatements(const TaggedStatement* start_statement,
  216. const TaggedStatement* end_statement) {
  217. // Created the MySQL prepared statements for each DML statement.
  218. for (const TaggedStatement* tagged_statement = start_statement;
  219. tagged_statement != end_statement; ++tagged_statement) {
  220. if (tagged_statement->index >= statements_.size()) {
  221. statements_.resize(tagged_statement->index + 1, NULL);
  222. text_statements_.resize(tagged_statement->index + 1,
  223. std::string(""));
  224. }
  225. prepareStatement(tagged_statement->index,
  226. tagged_statement->text);
  227. }
  228. }
  229. void MySqlConnection::clearStatements() {
  230. statements_.clear();
  231. text_statements_.clear();
  232. }
  233. /// @brief Destructor
  234. MySqlConnection::~MySqlConnection() {
  235. // Free up the prepared statements, ignoring errors. (What would we do
  236. // about them? We're destroying this object and are not really concerned
  237. // with errors on a database connection that is about to go away.)
  238. for (int i = 0; i < statements_.size(); ++i) {
  239. if (statements_[i] != NULL) {
  240. (void) mysql_stmt_close(statements_[i]);
  241. statements_[i] = NULL;
  242. }
  243. }
  244. statements_.clear();
  245. text_statements_.clear();
  246. }
  247. // Time conversion methods.
  248. //
  249. // Note that the MySQL TIMESTAMP data type (used for "expire") converts data
  250. // from the current timezone to UTC for storage, and from UTC to the current
  251. // timezone for retrieval.
  252. //
  253. // This causes no problems providing that:
  254. // a) cltt is given in local time
  255. // b) We let the system take care of timezone conversion when converting
  256. // from a time read from the database into a local time.
  257. void
  258. MySqlConnection::convertToDatabaseTime(const time_t input_time,
  259. MYSQL_TIME& output_time) {
  260. // Convert to broken-out time
  261. struct tm time_tm;
  262. (void) localtime_r(&input_time, &time_tm);
  263. // Place in output expire structure.
  264. output_time.year = time_tm.tm_year + 1900;
  265. output_time.month = time_tm.tm_mon + 1; // Note different base
  266. output_time.day = time_tm.tm_mday;
  267. output_time.hour = time_tm.tm_hour;
  268. output_time.minute = time_tm.tm_min;
  269. output_time.second = time_tm.tm_sec;
  270. output_time.second_part = 0; // No fractional seconds
  271. output_time.neg = my_bool(0); // Not negative
  272. }
  273. void
  274. MySqlConnection::convertToDatabaseTime(const time_t cltt,
  275. const uint32_t valid_lifetime,
  276. MYSQL_TIME& expire) {
  277. // Calculate expiry time. Store it in the 64-bit value so as we can detect
  278. // overflows.
  279. int64_t expire_time_64 = static_cast<int64_t>(cltt) +
  280. static_cast<int64_t>(valid_lifetime);
  281. // Even on 64-bit systems MySQL doesn't seem to accept the timestamps
  282. // beyond the max value of int32_t.
  283. if (expire_time_64 > DatabaseConnection::MAX_DB_TIME) {
  284. isc_throw(BadValue, "Time value is too large: " << expire_time_64);
  285. }
  286. const time_t expire_time = static_cast<const time_t>(expire_time_64);
  287. // Convert to broken-out time
  288. struct tm expire_tm;
  289. (void) localtime_r(&expire_time, &expire_tm);
  290. // Place in output expire structure.
  291. expire.year = expire_tm.tm_year + 1900;
  292. expire.month = expire_tm.tm_mon + 1; // Note different base
  293. expire.day = expire_tm.tm_mday;
  294. expire.hour = expire_tm.tm_hour;
  295. expire.minute = expire_tm.tm_min;
  296. expire.second = expire_tm.tm_sec;
  297. expire.second_part = 0; // No fractional seconds
  298. expire.neg = my_bool(0); // Not negative
  299. }
  300. void
  301. MySqlConnection::convertFromDatabaseTime(const MYSQL_TIME& expire,
  302. uint32_t valid_lifetime, time_t& cltt) {
  303. // Copy across fields from MYSQL_TIME structure.
  304. struct tm expire_tm;
  305. memset(&expire_tm, 0, sizeof(expire_tm));
  306. expire_tm.tm_year = expire.year - 1900;
  307. expire_tm.tm_mon = expire.month - 1;
  308. expire_tm.tm_mday = expire.day;
  309. expire_tm.tm_hour = expire.hour;
  310. expire_tm.tm_min = expire.minute;
  311. expire_tm.tm_sec = expire.second;
  312. expire_tm.tm_isdst = -1; // Let the system work out about DST
  313. // Convert to local time
  314. cltt = mktime(&expire_tm) - valid_lifetime;
  315. }
  316. void
  317. MySqlConnection::startTransaction() {
  318. LOG_DEBUG(dhcpsrv_logger, DHCPSRV_DBG_TRACE_DETAIL,
  319. DHCPSRV_MYSQL_START_TRANSACTION);
  320. // We create prepared statements for all other queries, but MySQL
  321. // don't support prepared statements for START TRANSACTION.
  322. int status = mysql_query(mysql_, "START TRANSACTION");
  323. if (status != 0) {
  324. isc_throw(DbOperationError, "unable to start transaction, "
  325. "reason: " << mysql_error(mysql_));
  326. }
  327. }
  328. void
  329. MySqlConnection::commit() {
  330. LOG_DEBUG(dhcpsrv_logger, DHCPSRV_DBG_TRACE_DETAIL, DHCPSRV_MYSQL_COMMIT);
  331. if (mysql_commit(mysql_) != 0) {
  332. isc_throw(DbOperationError, "commit failed: "
  333. << mysql_error(mysql_));
  334. }
  335. }
  336. void
  337. MySqlConnection::rollback() {
  338. LOG_DEBUG(dhcpsrv_logger, DHCPSRV_DBG_TRACE_DETAIL, DHCPSRV_MYSQL_ROLLBACK);
  339. if (mysql_rollback(mysql_) != 0) {
  340. isc_throw(DbOperationError, "rollback failed: "
  341. << mysql_error(mysql_));
  342. }
  343. }
  344. } // namespace isc::dhcp
  345. } // namespace isc