modules/gmysqlbackend/smysql.cc

   1 /*
   2  * This file is part of PowerDNS or dnsdist.
   3  * Copyright -- PowerDNS.COM B.V. and its contributors
   4  *
   5  * This program is free software; you can redistribute it and/or modify
   6  * it under the terms of version 2 of the GNU General Public License as
   7  * published by the Free Software Foundation.
   8  *
   9  * In addition, for the avoidance of any doubt, permission is granted to
  10  * link this program with OpenSSL and to (re)distribute the binaries
  11  * produced as the result of such linking.
  12  *
  13  * This program is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16  * GNU General Public License for more details.
  17  *
  18  * You should have received a copy of the GNU General Public License
  19  * along with this program; if not, write to the Free Software
  20  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  21  */
  22
  23 #ifdef HAVE_CONFIG_H
  24 #include "config.h"
  25 #endif
  26 #include "smysql.hh"
  27 #include <string>
  28 #include <iostream>
  29 #include "pdns/misc.hh"
  30 #include "pdns/logger.hh"
  31 #include "pdns/dns.hh"
  32 #include "pdns/namespaces.hh"
  33 #include "pdns/lock.hh"
  34
  35 #if MYSQL_VERSION_ID >= 80000 && !defined(MARIADB_BASE_VERSION)
  36 // Need to keep this for compatibility with MySQL < 8.0.0, which used typedef char my_bool;
  37 // MariaDB up to 10.4 also always define it.
  38 typedef bool my_bool;
  39 #endif
  40
  41 /*
  42  * Older versions of the MySQL and MariaDB client leak memory
  43  * because they expect the application to call mysql_thread_end()
  44  * when a thread ends. This thread_local static object provides
  45  * that closure, but only when the user has asked for it
  46  * by setting gmysql-thread-cleanup.
  47  * For more discussion, see https://github.com/PowerDNS/pdns/issues/6231
  48  */
  49 class MySQLThreadCloser
  50 {
  51 public:
  52   ~MySQLThreadCloser()
  53   {
  54     if (d_enabled) {
  55       mysql_thread_end();
  56     }
  57   }
  58   void enable()
  59   {
  60     d_enabled = true;
  61   }
  62
  63 private:
  64   bool d_enabled = false;
  65 };
  66
  67 static thread_local MySQLThreadCloser threadcloser;
  68
  69 bool SMySQL::s_dolog;
  70 std::mutex SMySQL::s_myinitlock;
  71
  72 class SMySQLStatement : public SSqlStatement
  73 {
  74 public:
  75   SMySQLStatement(const string& query, bool dolog, int nparams, MYSQL* db) :
  76     d_prepared(false)
  77   {
  78     d_db = db;
  79     d_dolog = dolog;
  80     d_query = query;
  81     d_paridx = d_fnum = d_resnum = d_residx = 0;
  82     d_parnum = nparams;
  83     d_req_bind = d_res_bind = nullptr;
  84     d_stmt = nullptr;
  85
  86     if (query.empty()) {
  87       return;
  88     }
  89   }
  90
  91   SSqlStatement* bind(const string& name, bool value)
  92   {
  93     prepareStatement();
  94     if (d_paridx >= d_parnum) {
  95       releaseStatement();
  96       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
  97     }
  98     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_TINY;
  99     d_req_bind[d_paridx].buffer = new char[1];
 100     *((char*)d_req_bind[d_paridx].buffer) = (value ? 1 : 0);
 101     d_paridx++;
 102     return this;
 103   }
 104   SSqlStatement* bind(const string& name, int value)
 105   {
 106     return bind(name, (long)value);
 107   }
 108   SSqlStatement* bind(const string& name, uint32_t value)
 109   {
 110     return bind(name, (unsigned long)value);
 111   }
 112   SSqlStatement* bind(const string& name, long value)
 113   {
 114     prepareStatement();
 115     if (d_paridx >= d_parnum) {
 116       releaseStatement();
 117       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 118     }
 119     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONG;
 120     d_req_bind[d_paridx].buffer = new long[1];
 121     *((long*)d_req_bind[d_paridx].buffer) = value;
 122     d_paridx++;
 123     return this;
 124   }
 125   SSqlStatement* bind(const string& name, unsigned long value)
 126   {
 127     prepareStatement();
 128     if (d_paridx >= d_parnum) {
 129       releaseStatement();
 130       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 131     }
 132     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONG;
 133     d_req_bind[d_paridx].buffer = new unsigned long[1];
 134     d_req_bind[d_paridx].is_unsigned = 1;
 135     *((unsigned long*)d_req_bind[d_paridx].buffer) = value;
 136     d_paridx++;
 137     return this;
 138   }
 139   SSqlStatement* bind(const string& name, long long value)
 140   {
 141     prepareStatement();
 142     if (d_paridx >= d_parnum) {
 143       releaseStatement();
 144       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 145     }
 146     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONGLONG;
 147     d_req_bind[d_paridx].buffer = new long long[1];
 148     *((long long*)d_req_bind[d_paridx].buffer) = value;
 149     d_paridx++;
 150     return this;
 151   }
 152   SSqlStatement* bind(const string& name, unsigned long long value)
 153   {
 154     prepareStatement();
 155     if (d_paridx >= d_parnum) {
 156       releaseStatement();
 157       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 158     }
 159     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONGLONG;
 160     d_req_bind[d_paridx].buffer = new unsigned long long[1];
 161     d_req_bind[d_paridx].is_unsigned = 1;
 162     *((unsigned long long*)d_req_bind[d_paridx].buffer) = value;
 163     d_paridx++;
 164     return this;
 165   }
 166   SSqlStatement* bind(const string& name, const std::string& value)
 167   {
 168     prepareStatement();
 169     if (d_paridx >= d_parnum) {
 170       releaseStatement();
 171       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 172     }
 173     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_STRING;
 174     d_req_bind[d_paridx].buffer = new char[value.size() + 1];
 175     d_req_bind[d_paridx].length = new unsigned long[1];
 176     *d_req_bind[d_paridx].length = value.size();
 177     d_req_bind[d_paridx].buffer_length = *d_req_bind[d_paridx].length + 1;
 178     memset(d_req_bind[d_paridx].buffer, 0, value.size() + 1);
 179     value.copy((char*)d_req_bind[d_paridx].buffer, value.size());
 180     d_paridx++;
 181     return this;
 182   }
 183   SSqlStatement* bindNull(const string& name)
 184   {
 185     prepareStatement();
 186     if (d_paridx >= d_parnum) {
 187       releaseStatement();
 188       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 189     }
 190     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_NULL;
 191     d_paridx++;
 192     return this;
 193   }
 194
 195   SSqlStatement* execute()
 196   {
 197     prepareStatement();
 198
 199     if (!d_stmt)
 200       return this;
 201
 202     if (d_dolog) {
 203       g_log << Logger::Warning << "Query " << ((long)(void*)this) << ": " << d_query << endl;
 204       d_dtime.set();
 205     }
 206
 207     if (mysql_stmt_bind_param(d_stmt, d_req_bind) != 0) {
 208       string error(mysql_stmt_error(d_stmt));
 209       releaseStatement();
 210       throw SSqlException("Could not bind mysql statement: " + d_query + string(": ") + error);
 211     }
 212
 213     if (mysql_stmt_execute(d_stmt) != 0) {
 214       string error(mysql_stmt_error(d_stmt));
 215       releaseStatement();
 216       throw SSqlException("Could not execute mysql statement: " + d_query + string(": ") + error);
 217     }
 218
 219     // MySQL documentation says you can call this safely for all queries
 220     if (mysql_stmt_store_result(d_stmt) != 0) {
 221       string error(mysql_stmt_error(d_stmt));
 222       releaseStatement();
 223       throw SSqlException("Could not store mysql statement: " + d_query + string(": ") + error);
 224     }
 225
 226     if ((d_fnum = static_cast<int>(mysql_stmt_field_count(d_stmt))) > 0) {
 227       // prepare for result
 228       d_resnum = mysql_stmt_num_rows(d_stmt);
 229
 230       if (d_resnum > 0 && d_res_bind == nullptr) {
 231         MYSQL_RES* meta = mysql_stmt_result_metadata(d_stmt);
 232         d_fnum = static_cast<int>(mysql_num_fields(meta)); // ensure correct number of fields
 233         d_res_bind = new MYSQL_BIND[d_fnum];
 234         memset(d_res_bind, 0, sizeof(MYSQL_BIND) * d_fnum);
 235         MYSQL_FIELD* fields = mysql_fetch_fields(meta);
 236
 237         for (int i = 0; i < d_fnum; i++) {
 238           unsigned long len = std::max(fields[i].max_length, fields[i].length) + 1;
 239           if (len > 128 * 1024)
 240             len = 128 * 1024; // LONGTEXT may tell us it needs 4GB!
 241           d_res_bind[i].is_null = new my_bool[1];
 242           d_res_bind[i].error = new my_bool[1];
 243           d_res_bind[i].length = new unsigned long[1];
 244           d_res_bind[i].buffer = new char[len];
 245           d_res_bind[i].buffer_length = len;
 246           d_res_bind[i].buffer_type = MYSQL_TYPE_STRING;
 247         }
 248
 249         mysql_free_result(meta);
 250       }
 251
 252       /* we need to bind the results array again because a call to mysql_stmt_next_result() followed
 253          by a call to mysql_stmt_store_result() might have invalidated it (the first one sets
 254          stmt->bind_result_done to false, causing the second to reset the existing binding),
 255          and we can't bind it right after the call to mysql_stmt_store_result() if it returned
 256          no rows, because then the statement 'contains no metadata' */
 257       if (d_res_bind != nullptr && mysql_stmt_bind_result(d_stmt, d_res_bind) != 0) {
 258         string error(mysql_stmt_error(d_stmt));
 259         releaseStatement();
 260         throw SSqlException("Could not bind parameters to mysql statement: " + d_query + string(": ") + error);
 261       }
 262     }
 263
 264     if (d_dolog)
 265       g_log << Logger::Warning << "Query " << ((long)(void*)this) << ": " << d_dtime.udiffNoReset() << " usec to execute" << endl;
 266
 267     return this;
 268   }
 269
 270   bool hasNextRow()
 271   {
 272     if (d_dolog && d_residx == d_resnum) {
 273       g_log << Logger::Warning << "Query " << ((long)(void*)this) << ": " << d_dtime.udiffNoReset() << " total usec to last row" << endl;
 274     }
 275     return d_residx < d_resnum;
 276   }
 277
 278   SSqlStatement* nextRow(row_t& row)
 279   {
 280     int err;
 281     row.clear();
 282     if (!hasNextRow()) {
 283       return this;
 284     }
 285
 286     if ((err = mysql_stmt_fetch(d_stmt))) {
 287       if (err != MYSQL_DATA_TRUNCATED) {
 288         string error(mysql_stmt_error(d_stmt));
 289         releaseStatement();
 290         throw SSqlException("Could not fetch result: " + d_query + string(": ") + error);
 291       }
 292     }
 293
 294     row.reserve(d_fnum);
 295
 296     for (int i = 0; i < d_fnum; i++) {
 297       if (err == MYSQL_DATA_TRUNCATED && *d_res_bind[i].error) {
 298         g_log << Logger::Warning << "Result field at row " << d_residx << " column " << i << " has been truncated, we allocated " << d_res_bind[i].buffer_length << " bytes but at least " << *d_res_bind[i].length << " was needed" << endl;
 299       }
 300       if (*d_res_bind[i].is_null) {
 301         row.emplace_back("");
 302         continue;
 303       }
 304       else {
 305         row.emplace_back((char*)d_res_bind[i].buffer, std::min(d_res_bind[i].buffer_length, *d_res_bind[i].length));
 306       }
 307     }
 308
 309     d_residx++;
 310 #if MYSQL_VERSION_ID >= 50500
 311     if (d_residx >= d_resnum) {
 312       mysql_stmt_free_result(d_stmt);
 313       while (!mysql_stmt_next_result(d_stmt)) {
 314         if (mysql_stmt_store_result(d_stmt) != 0) {
 315           string error(mysql_stmt_error(d_stmt));
 316           releaseStatement();
 317           throw SSqlException("Could not store mysql statement while processing additional sets: " + d_query + string(": ") + error);
 318         }
 319         d_resnum = mysql_stmt_num_rows(d_stmt);
 320         // XXX: For some reason mysql_stmt_result_metadata returns NULL here, so we cannot
 321         // ensure row field count matches first result set.
 322         // We need to check the field count as stored procedure return the final values of OUT and INOUT parameters
 323         // as an extra single-row result set following any result sets produced by the procedure itself.
 324         // mysql_stmt_field_count() will return 0 for those.
 325         if (mysql_stmt_field_count(d_stmt) > 0 && d_resnum > 0) { // ignore empty result set
 326           if (d_res_bind != nullptr && mysql_stmt_bind_result(d_stmt, d_res_bind) != 0) {
 327             string error(mysql_stmt_error(d_stmt));
 328             releaseStatement();
 329             throw SSqlException("Could not bind parameters to mysql statement: " + d_query + string(": ") + error);
 330           }
 331           d_residx = 0;
 332           break;
 333         }
 334         mysql_stmt_free_result(d_stmt);
 335       }
 336     }
 337 #endif
 338     return this;
 339   }
 340
 341   SSqlStatement* getResult(result_t& result)
 342   {
 343     result.clear();
 344     result.reserve(d_resnum);
 345     row_t row;
 346
 347     while (hasNextRow()) {
 348       nextRow(row);
 349       result.push_back(std::move(row));
 350     }
 351
 352     return this;
 353   }
 354
 355   SSqlStatement* reset()
 356   {
 357     if (!d_stmt)
 358       return this;
 359     int err = 0;
 360     mysql_stmt_free_result(d_stmt);
 361 #if MYSQL_VERSION_ID >= 50500
 362     while ((err = mysql_stmt_next_result(d_stmt)) == 0) {
 363       mysql_stmt_free_result(d_stmt);
 364     }
 365 #endif
 366     if (err > 0) {
 367       string error(mysql_stmt_error(d_stmt));
 368       releaseStatement();
 369       throw SSqlException("Could not get next result from mysql statement: " + d_query + string(": ") + error);
 370     }
 371     mysql_stmt_reset(d_stmt);
 372     if (d_req_bind) {
 373       for (int i = 0; i < d_parnum; i++) {
 374         if (d_req_bind[i].buffer)
 375           delete[](char*) d_req_bind[i].buffer;
 376         if (d_req_bind[i].length)
 377           delete[] d_req_bind[i].length;
 378       }
 379       memset(d_req_bind, 0, sizeof(MYSQL_BIND) * d_parnum);
 380     }
 381     d_residx = d_resnum = 0;
 382     d_paridx = 0;
 383     return this;
 384   }
 385
 386   const std::string& getQuery() { return d_query; }
 387
 388   ~SMySQLStatement()
 389   {
 390     releaseStatement();
 391   }
 392
 393 private:
 394   void prepareStatement()
 395   {
 396     if (d_prepared)
 397       return;
 398     if (d_query.empty()) {
 399       d_prepared = true;
 400       return;
 401     }
 402
 403     if ((d_stmt = mysql_stmt_init(d_db)) == nullptr)
 404       throw SSqlException("Could not initialize mysql statement, out of memory: " + d_query);
 405
 406     if (mysql_stmt_prepare(d_stmt, d_query.c_str(), d_query.size()) != 0) {
 407       string error(mysql_stmt_error(d_stmt));
 408       releaseStatement();
 409       throw SSqlException("Could not prepare statement: " + d_query + string(": ") + error);
 410     }
 411
 412     if (static_cast<int>(mysql_stmt_param_count(d_stmt)) != d_parnum) {
 413       releaseStatement();
 414       throw SSqlException("Provided parameter count does not match statement: " + d_query);
 415     }
 416
 417     if (d_parnum > 0) {
 418       d_req_bind = new MYSQL_BIND[d_parnum];
 419       memset(d_req_bind, 0, sizeof(MYSQL_BIND) * d_parnum);
 420     }
 421
 422     d_prepared = true;
 423   }
 424
 425   void releaseStatement()
 426   {
 427     d_prepared = false;
 428     if (d_stmt)
 429       mysql_stmt_close(d_stmt);
 430     d_stmt = nullptr;
 431     if (d_req_bind) {
 432       for (int i = 0; i < d_parnum; i++) {
 433         if (d_req_bind[i].buffer)
 434           delete[](char*) d_req_bind[i].buffer;
 435         if (d_req_bind[i].length)
 436           delete[] d_req_bind[i].length;
 437       }
 438       delete[] d_req_bind;
 439       d_req_bind = nullptr;
 440     }
 441     if (d_res_bind) {
 442       for (int i = 0; i < d_fnum; i++) {
 443         if (d_res_bind[i].buffer)
 444           delete[](char*) d_res_bind[i].buffer;
 445         if (d_res_bind[i].length)
 446           delete[] d_res_bind[i].length;
 447         if (d_res_bind[i].error)
 448           delete[] d_res_bind[i].error;
 449         if (d_res_bind[i].is_null)
 450           delete[] d_res_bind[i].is_null;
 451       }
 452       delete[] d_res_bind;
 453       d_res_bind = nullptr;
 454     }
 455     d_paridx = d_fnum = d_resnum = d_residx = 0;
 456   }
 457   MYSQL* d_db;
 458
 459   MYSQL_STMT* d_stmt;
 460   MYSQL_BIND* d_req_bind;
 461   MYSQL_BIND* d_res_bind;
 462
 463   string d_query;
 464
 465   bool d_prepared;
 466   bool d_dolog;
 467   DTime d_dtime; // only used if d_dolog is set
 468   int d_parnum;
 469   int d_paridx;
 470   int d_fnum;
 471   int d_resnum;
 472   int d_residx;
 473 };
 474
 475 void SMySQL::connect()
 476 {
 477   int retry = 1;
 478
 479   {
 480     std::lock_guard<std::mutex> l(s_myinitlock);
 481     if (d_threadCleanup) {
 482       threadcloser.enable();
 483     }
 484
 485     if (!mysql_init(&d_db)) {
 486       throw sPerrorException("Unable to initialize mysql driver");
 487     }
 488   }
 489
 490   do {
 491
 492 #if MYSQL_VERSION_ID >= 50013
 493     my_bool set_reconnect = 0;
 494     mysql_options(&d_db, MYSQL_OPT_RECONNECT, &set_reconnect);
 495 #endif
 496
 497 #if MYSQL_VERSION_ID >= 50100
 498     if (d_timeout) {
 499       mysql_options(&d_db, MYSQL_OPT_READ_TIMEOUT, &d_timeout);
 500       mysql_options(&d_db, MYSQL_OPT_WRITE_TIMEOUT, &d_timeout);
 501     }
 502 #endif
 503
 504     if (d_setIsolation && (retry == 1))
 505       mysql_options(&d_db, MYSQL_INIT_COMMAND, "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
 506
 507     mysql_options(&d_db, MYSQL_READ_DEFAULT_GROUP, d_group.c_str());
 508
 509     if (!mysql_real_connect(&d_db, d_host.empty() ? nullptr : d_host.c_str(),
 510                             d_user.empty() ? nullptr : d_user.c_str(),
 511                             d_password.empty() ? nullptr : d_password.c_str(),
 512                             d_database.empty() ? nullptr : d_database.c_str(),
 513                             d_port,
 514                             d_msocket.empty() ? nullptr : d_msocket.c_str(),
 515                             (d_clientSSL ? CLIENT_SSL : 0) | CLIENT_MULTI_RESULTS)) {
 516
 517       if (retry == 0)
 518         throw sPerrorException("Unable to connect to database");
 519       --retry;
 520     }
 521     else {
 522       if (retry == 0) {
 523         mysql_close(&d_db);
 524         throw sPerrorException("Please add '(gmysql-)innodb-read-committed=no' to your PowerDNS configuration, and reconsider your storage engine if it does not support transactions.");
 525       }
 526       retry = -1;
 527     }
 528   } while (retry >= 0);
 529 }
 530
 531 SMySQL::SMySQL(string database, string host, uint16_t port, string msocket, string user,
 532                string password, string group, bool setIsolation, unsigned int timeout, bool threadCleanup, bool clientSSL) :
 533   d_database(std::move(database)), d_host(std::move(host)), d_msocket(std::move(msocket)), d_user(std::move(user)), d_password(std::move(password)), d_group(std::move(group)), d_timeout(timeout), d_port(port), d_setIsolation(setIsolation), d_threadCleanup(threadCleanup), d_clientSSL(clientSSL)
 534 {
 535   connect();
 536 }
 537
 538 void SMySQL::setLog(bool state)
 539 {
 540   s_dolog = state;
 541 }
 542
 543 SMySQL::~SMySQL()
 544 {
 545   mysql_close(&d_db);
 546 }
 547
 548 SSqlException SMySQL::sPerrorException(const string& reason)
 549 {
 550   return SSqlException(reason + string(": ERROR ") + std::to_string(mysql_errno(&d_db)) + " (" + string(mysql_sqlstate(&d_db)) + "): " + mysql_error(&d_db));
 551 }
 552
 553 std::unique_ptr<SSqlStatement> SMySQL::prepare(const string& query, int nparams)
 554 {
 555   return std::make_unique<SMySQLStatement>(query, s_dolog, nparams, &d_db);
 556 }
 557
 558 void SMySQL::execute(const string& query)
 559 {
 560   if (s_dolog)
 561     g_log << Logger::Warning << "Query: " << query << endl;
 562
 563   int err;
 564   if ((err = mysql_query(&d_db, query.c_str())))
 565     throw sPerrorException("Failed to execute mysql_query '" + query + "' Err=" + itoa(err));
 566 }
 567
 568 void SMySQL::startTransaction()
 569 {
 570   execute("begin");
 571 }
 572
 573 void SMySQL::commit()
 574 {
 575   execute("commit");
 576 }
 577
 578 void SMySQL::rollback()
 579 {
 580   execute("rollback");
 581 }
 582
 583 bool SMySQL::isConnectionUsable()
 584 {
 585   bool usable = false;
 586   int sd = d_db.net.fd;
 587   bool wasNonBlocking = isNonBlocking(sd);
 588
 589   if (!wasNonBlocking) {
 590     if (!setNonBlocking(sd)) {
 591       return usable;
 592     }
 593   }
 594
 595   usable = isTCPSocketUsable(sd);
 596
 597   if (!wasNonBlocking) {
 598     if (!setBlocking(sd)) {
 599       usable = false;
 600     }
 601   }
 602
 603   return usable;
 604 }