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     if(d_enabled) {
  54       mysql_thread_end();
  55     }
  56   }
  57   void enable() {
  58    d_enabled = true;
  59   }
  60
  61 private:
  62   bool d_enabled = false;
  63 };
  64
  65 static thread_local MySQLThreadCloser threadcloser;
  66
  67 bool SMySQL::s_dolog;
  68 pthread_mutex_t SMySQL::s_myinitlock = PTHREAD_MUTEX_INITIALIZER;
  69
  70 class SMySQLStatement: public SSqlStatement
  71 {
  72 public:
  73   SMySQLStatement(const string& query, bool dolog, int nparams, MYSQL* db) : d_prepared(false)
  74   {
  75     d_db = db;
  76     d_dolog = dolog;
  77     d_query = query;
  78     d_paridx = d_fnum = d_resnum = d_residx = 0;
  79     d_parnum = nparams;
  80     d_req_bind = d_res_bind = NULL;
  81     d_stmt = NULL;
  82
  83     if (query.empty()) {
  84       return;
  85     }
  86   }
  87
  88   SSqlStatement* bind(const string& name, bool value) {
  89     prepareStatement();
  90     if (d_paridx >= d_parnum) {
  91       releaseStatement();
  92       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
  93     }
  94     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_TINY;
  95     d_req_bind[d_paridx].buffer = new char[1];
  96     *((char*)d_req_bind[d_paridx].buffer) = (value?1:0);
  97     d_paridx++;
  98     return this;
  99   }
 100   SSqlStatement* bind(const string& name, int value) {
 101     return bind(name, (long)value);
 102   }
 103   SSqlStatement* bind(const string& name, uint32_t value) {
 104     return bind(name, (unsigned long)value);
 105   }
 106   SSqlStatement* bind(const string& name, long value) {
 107     prepareStatement();
 108     if (d_paridx >= d_parnum) {
 109       releaseStatement();
 110       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 111     }
 112     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONG;
 113     d_req_bind[d_paridx].buffer = new long[1];
 114     *((long*)d_req_bind[d_paridx].buffer) = value;
 115     d_paridx++;
 116     return this;
 117   }
 118   SSqlStatement* bind(const string& name, unsigned long value) {
 119     prepareStatement();
 120     if (d_paridx >= d_parnum) {
 121       releaseStatement();
 122       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 123     }
 124     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONG;
 125     d_req_bind[d_paridx].buffer = new unsigned long[1];
 126     d_req_bind[d_paridx].is_unsigned = 1;
 127     *((unsigned long*)d_req_bind[d_paridx].buffer) = value;
 128     d_paridx++;
 129     return this;
 130   }
 131   SSqlStatement* bind(const string& name, long long value) {
 132     prepareStatement();
 133     if (d_paridx >= d_parnum) {
 134       releaseStatement();
 135       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 136     }
 137     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONGLONG;
 138     d_req_bind[d_paridx].buffer = new long long[1];
 139     *((long long*)d_req_bind[d_paridx].buffer) = value;
 140     d_paridx++;
 141     return this;
 142   }
 143   SSqlStatement* bind(const string& name, unsigned long long value) {
 144     prepareStatement();
 145     if (d_paridx >= d_parnum) {
 146       releaseStatement();
 147       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 148     }
 149     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_LONGLONG;
 150     d_req_bind[d_paridx].buffer = new unsigned long long[1];
 151     d_req_bind[d_paridx].is_unsigned = 1;
 152     *((unsigned long long*)d_req_bind[d_paridx].buffer) = value;
 153     d_paridx++;
 154     return this;
 155   }
 156   SSqlStatement* bind(const string& name, const std::string& value) {
 157     prepareStatement();
 158     if (d_paridx >= d_parnum) {
 159       releaseStatement();
 160       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 161     }
 162     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_STRING;
 163     d_req_bind[d_paridx].buffer = new char[value.size()+1];
 164     d_req_bind[d_paridx].length = new unsigned long[1];
 165     *d_req_bind[d_paridx].length = value.size();
 166     d_req_bind[d_paridx].buffer_length = *d_req_bind[d_paridx].length+1;
 167     memset(d_req_bind[d_paridx].buffer, 0, value.size()+1);
 168     value.copy((char*)d_req_bind[d_paridx].buffer, value.size());
 169     d_paridx++;
 170     return this;
 171   }
 172   SSqlStatement* bindNull(const string& name) {
 173     prepareStatement();
 174     if (d_paridx >= d_parnum) {
 175       releaseStatement();
 176       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
 177     }
 178     d_req_bind[d_paridx].buffer_type = MYSQL_TYPE_NULL;
 179     d_paridx++;
 180     return this;
 181   }
 182
 183   SSqlStatement* execute() {
 184     int err;
 185
 186     prepareStatement();
 187
 188     if (!d_stmt) return this;
 189
 190     if (d_dolog) {
 191       g_log<<Logger::Warning<< "Query "<<((long)(void*)this)<<": " << d_query << endl;
 192       d_dtime.set();
 193     }
 194
 195     if ((err = mysql_stmt_bind_param(d_stmt, d_req_bind))) {
 196       string error(mysql_stmt_error(d_stmt));
 197       releaseStatement();
 198       throw SSqlException("Could not bind mysql statement: " + d_query + string(": ") + error);
 199     }
 200
 201     if ((err = mysql_stmt_execute(d_stmt))) {
 202       string error(mysql_stmt_error(d_stmt));
 203       releaseStatement();
 204       throw SSqlException("Could not execute mysql statement: " + d_query + string(": ") + error);
 205     }
 206
 207     // MySQL documentation says you can call this safely for all queries
 208     if ((err = mysql_stmt_store_result(d_stmt))) {
 209       string error(mysql_stmt_error(d_stmt));
 210       releaseStatement();
 211       throw SSqlException("Could not store mysql statement: " + d_query + string(": ") + error);
 212     }
 213
 214     if ((d_fnum = static_cast<int>(mysql_stmt_field_count(d_stmt)))>0) {
 215       // prepare for result
 216       d_resnum = mysql_stmt_num_rows(d_stmt);
 217
 218       if (d_resnum > 0 && d_res_bind == nullptr) {
 219         MYSQL_RES* meta = mysql_stmt_result_metadata(d_stmt);
 220         d_fnum = static_cast<int>(mysql_num_fields(meta)); // ensure correct number of fields
 221         d_res_bind = new MYSQL_BIND[d_fnum];
 222         memset(d_res_bind, 0, sizeof(MYSQL_BIND)*d_fnum);
 223         MYSQL_FIELD* fields = mysql_fetch_fields(meta);
 224
 225         for(int i = 0; i < d_fnum; i++) {
 226           unsigned long len = std::max(fields[i].max_length, fields[i].length)+1;
 227           if (len > 128 * 1024) len = 128 * 1024; // LONGTEXT may tell us it needs 4GB!
 228           d_res_bind[i].is_null = new my_bool[1];
 229           d_res_bind[i].error = new my_bool[1];
 230           d_res_bind[i].length = new unsigned long[1];
 231           d_res_bind[i].buffer = new char[len];
 232           d_res_bind[i].buffer_length = len;
 233           d_res_bind[i].buffer_type = MYSQL_TYPE_STRING;
 234         }
 235
 236         mysql_free_result(meta);
 237       }
 238
 239       /* we need to bind the results array again because a call to mysql_stmt_next_result() followed
 240          by a call to mysql_stmt_store_result() might have invalidated it (the first one sets
 241          stmt->bind_result_done to false, causing the second to reset the existing binding),
 242          and we can't bind it right after the call to mysql_stmt_store_result() if it returned
 243          no rows, because then the statement 'contains no metadata' */
 244       if (d_res_bind != nullptr && (err = mysql_stmt_bind_result(d_stmt, d_res_bind))) {
 245         string error(mysql_stmt_error(d_stmt));
 246         releaseStatement();
 247         throw SSqlException("Could not bind parameters to mysql statement: " + d_query + string(": ") + error);
 248       }
 249     }
 250
 251     if(d_dolog)
 252       g_log<<Logger::Warning<< "Query "<<((long)(void*)this)<<": "<<d_dtime.udiffNoReset()<<" usec to execute"<<endl;
 253
 254     return this;
 255   }
 256
 257   bool hasNextRow() {
 258     if(d_dolog && d_residx == d_resnum) {
 259       g_log<<Logger::Warning<< "Query "<<((long)(void*)this)<<": "<<d_dtime.udiffNoReset()<<" total usec to last row"<<endl;
 260     }
 261     return d_residx < d_resnum;
 262   }
 263
 264   SSqlStatement* nextRow(row_t& row) {
 265     int err;
 266     row.clear();
 267     if (!hasNextRow()) {
 268       return this;
 269     }
 270
 271     if ((err = mysql_stmt_fetch(d_stmt))) {
 272       if (err != MYSQL_DATA_TRUNCATED) {
 273         string error(mysql_stmt_error(d_stmt));
 274         releaseStatement();
 275         throw SSqlException("Could not fetch result: " + d_query + string(": ") + error);
 276       }
 277     }
 278
 279     row.reserve(d_fnum);
 280
 281     for(int i=0;i<d_fnum;i++) {
 282       if (err == MYSQL_DATA_TRUNCATED && *d_res_bind[i].error) {
 283         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;
 284       }
 285       if (*d_res_bind[i].is_null) {
 286         row.emplace_back("");
 287         continue;
 288       } else {
 289         row.emplace_back((char*)d_res_bind[i].buffer, std::min(d_res_bind[i].buffer_length, *d_res_bind[i].length));
 290       }
 291     }
 292
 293     d_residx++;
 294 #if MYSQL_VERSION_ID >= 50500
 295     if (d_residx >= d_resnum) {
 296       mysql_stmt_free_result(d_stmt);
 297       while(!mysql_stmt_next_result(d_stmt)) {
 298         if ((err = mysql_stmt_store_result(d_stmt))) {
 299           string error(mysql_stmt_error(d_stmt));
 300           releaseStatement();
 301           throw SSqlException("Could not store mysql statement while processing additional sets: " + d_query + string(": ") + error);
 302         }
 303         d_resnum = mysql_stmt_num_rows(d_stmt);
 304         // XXX: For some reason mysql_stmt_result_metadata returns NULL here, so we cannot
 305         // ensure row field count matches first result set.
 306         if (d_resnum > 0) { // ignore empty result set
 307           if (d_res_bind != nullptr && (err = mysql_stmt_bind_result(d_stmt, d_res_bind))) {
 308             string error(mysql_stmt_error(d_stmt));
 309             releaseStatement();
 310             throw SSqlException("Could not bind parameters to mysql statement: " + d_query + string(": ") + error);
 311           }
 312           d_residx = 0;
 313           break;
 314         }
 315         mysql_stmt_free_result(d_stmt);
 316       }
 317     }
 318 #endif
 319     return this;
 320   }
 321
 322   SSqlStatement* getResult(result_t& result) {
 323     result.clear();
 324     result.reserve(d_resnum);
 325     row_t row;
 326
 327     while(hasNextRow()) {
 328       nextRow(row);
 329       result.push_back(std::move(row));
 330     }
 331
 332     return this;
 333   }
 334
 335   SSqlStatement* reset() {
 336     if (!d_stmt) return this;
 337     int err=0;
 338     mysql_stmt_free_result(d_stmt);
 339 #if MYSQL_VERSION_ID >= 50500
 340     while((err = mysql_stmt_next_result(d_stmt)) == 0) {
 341       mysql_stmt_free_result(d_stmt);
 342     }
 343 #endif
 344     if (err>0) {
 345       string error(mysql_stmt_error(d_stmt));
 346       releaseStatement();
 347       throw SSqlException("Could not get next result from mysql statement: " + d_query + string(": ") + error);
 348     }
 349     mysql_stmt_reset(d_stmt);
 350     if (d_req_bind) {
 351       for(int i=0;i<d_parnum;i++) {
 352         if (d_req_bind[i].buffer) delete [] (char*)d_req_bind[i].buffer;
 353         if (d_req_bind[i].length) delete [] d_req_bind[i].length;
 354       }
 355       memset(d_req_bind, 0, sizeof(MYSQL_BIND)*d_parnum);
 356     }
 357     d_residx = d_resnum = 0;
 358     d_paridx = 0;
 359     return this;
 360   }
 361
 362   const std::string& getQuery() { return d_query; }
 363
 364   ~SMySQLStatement() {
 365     releaseStatement();
 366   }
 367 private:
 368
 369   void prepareStatement() {
 370     int err;
 371
 372     if (d_prepared) return;
 373     if (d_query.empty()) {
 374       d_prepared = true;
 375       return;
 376     }
 377
 378     if ((d_stmt = mysql_stmt_init(d_db))==NULL)
 379       throw SSqlException("Could not initialize mysql statement, out of memory: " + d_query);
 380
 381     if ((err = mysql_stmt_prepare(d_stmt, d_query.c_str(), d_query.size()))) {
 382       string error(mysql_stmt_error(d_stmt));
 383       releaseStatement();
 384       throw SSqlException("Could not prepare statement: " + d_query + string(": ") + error);
 385     }
 386
 387     if (static_cast<int>(mysql_stmt_param_count(d_stmt)) != d_parnum) {
 388       releaseStatement();
 389       throw SSqlException("Provided parameter count does not match statement: " + d_query);
 390     }
 391
 392     if (d_parnum>0) {
 393       d_req_bind = new MYSQL_BIND[d_parnum];
 394       memset(d_req_bind, 0, sizeof(MYSQL_BIND)*d_parnum);
 395     }
 396
 397     d_prepared = true;
 398   }
 399
 400   void releaseStatement() {
 401     d_prepared = false;
 402     if (d_stmt)
 403       mysql_stmt_close(d_stmt);
 404     d_stmt = NULL;
 405     if (d_req_bind) {
 406       for(int i=0;i<d_parnum;i++) {
 407         if (d_req_bind[i].buffer) delete [] (char*)d_req_bind[i].buffer;
 408         if (d_req_bind[i].length) delete [] d_req_bind[i].length;
 409       }
 410       delete [] d_req_bind;
 411       d_req_bind = NULL;
 412     }
 413     if (d_res_bind) {
 414       for(int i=0;i<d_fnum;i++) {
 415         if (d_res_bind[i].buffer) delete [] (char*)d_res_bind[i].buffer;
 416         if (d_res_bind[i].length) delete [] d_res_bind[i].length;
 417         if (d_res_bind[i].error) delete [] d_res_bind[i].error;
 418         if (d_res_bind[i].is_null) delete [] d_res_bind[i].is_null;
 419       }
 420       delete [] d_res_bind;
 421       d_res_bind = NULL;
 422     }
 423     d_paridx = d_fnum = d_resnum = d_residx = 0;
 424   }
 425   MYSQL* d_db;
 426
 427   MYSQL_STMT* d_stmt;
 428   MYSQL_BIND* d_req_bind;
 429   MYSQL_BIND* d_res_bind;
 430
 431   string d_query;
 432
 433   bool d_prepared;
 434   bool d_dolog;
 435   DTime d_dtime; // only used if d_dolog is set
 436   int d_parnum;
 437   int d_paridx;
 438   int d_fnum;
 439   int d_resnum;
 440   int d_residx;
 441 };
 442
 443 void SMySQL::connect()
 444 {
 445   int retry=1;
 446
 447   Lock l(&s_myinitlock);
 448   if (d_threadCleanup) {
 449     threadcloser.enable();
 450   }
 451
 452   if (!mysql_init(&d_db))
 453     throw sPerrorException("Unable to initialize mysql driver");
 454
 455   do {
 456
 457 #if MYSQL_VERSION_ID >= 50013
 458     my_bool set_reconnect = 0;
 459     mysql_options(&d_db, MYSQL_OPT_RECONNECT, &set_reconnect);
 460 #endif
 461
 462 #if MYSQL_VERSION_ID >= 50100
 463     if(d_timeout) {
 464       mysql_options(&d_db, MYSQL_OPT_READ_TIMEOUT, &d_timeout);
 465       mysql_options(&d_db, MYSQL_OPT_WRITE_TIMEOUT, &d_timeout);
 466     }
 467 #endif
 468
 469 #if MYSQL_VERSION_ID >= 50500
 470     mysql_options(&d_db, MYSQL_SET_CHARSET_NAME, MYSQL_AUTODETECT_CHARSET_NAME);
 471 #endif
 472
 473     if (d_setIsolation && (retry == 1))
 474       mysql_options(&d_db, MYSQL_INIT_COMMAND,"SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
 475
 476     mysql_options(&d_db, MYSQL_READ_DEFAULT_GROUP, d_group.c_str());
 477
 478     if (!mysql_real_connect(&d_db, d_host.empty() ? NULL : d_host.c_str(),
 479                             d_user.empty() ? NULL : d_user.c_str(),
 480                             d_password.empty() ? NULL : d_password.c_str(),
 481                             d_database.empty() ? NULL : d_database.c_str(),
 482                             d_port,
 483                             d_msocket.empty() ? NULL : d_msocket.c_str(),
 484                             (d_clientSSL ? CLIENT_SSL : 0) | CLIENT_MULTI_RESULTS)) {
 485
 486       if (retry == 0)
 487         throw sPerrorException("Unable to connect to database");
 488       --retry;
 489     } else {
 490       if (retry == 0) {
 491         mysql_close(&d_db);
 492         throw sPerrorException("Please add '(gmysql-)innodb-read-committed=no' to your PowerDNS configuration, and reconsider your storage engine if it does not support transactions.");
 493       }
 494       retry=-1;
 495     }
 496   } while (retry >= 0);
 497 }
 498
 499 SMySQL::SMySQL(const string &database, const string &host, uint16_t port, const string &msocket, const string &user,
 500                const string &password, const string &group, bool setIsolation, unsigned int timeout, bool threadCleanup, bool clientSSL):
 501   d_database(database), d_host(host), d_msocket(msocket), d_user(user), d_password(password), d_group(group), d_timeout(timeout), d_port(port), d_setIsolation(setIsolation), d_threadCleanup(threadCleanup), d_clientSSL(clientSSL)
 502 {
 503   connect();
 504 }
 505
 506 void SMySQL::setLog(bool state)
 507 {
 508   s_dolog=state;
 509 }
 510
 511 SMySQL::~SMySQL()
 512 {
 513   mysql_close(&d_db);
 514 }
 515
 516 SSqlException SMySQL::sPerrorException(const string &reason)
 517 {
 518   return SSqlException(reason+string(": ")+mysql_error(&d_db));
 519 }
 520
 521 std::unique_ptr<SSqlStatement> SMySQL::prepare(const string& query, int nparams)
 522 {
 523   return std::unique_ptr<SSqlStatement>(new SMySQLStatement(query, s_dolog, nparams, &d_db));
 524 }
 525
 526 void SMySQL::execute(const string& query)
 527 {
 528   if(s_dolog)
 529     g_log<<Logger::Warning<<"Query: "<<query<<endl;
 530
 531   int err;
 532   if((err=mysql_query(&d_db,query.c_str())))
 533     throw sPerrorException("Failed to execute mysql_query '" + query + "' Err="+itoa(err));
 534 }
 535
 536 void SMySQL::startTransaction() {
 537   execute("begin");
 538 }
 539
 540 void SMySQL::commit() {
 541   execute("commit");
 542 }
 543
 544 void SMySQL::rollback() {
 545   execute("rollback");
 546 }
 547
 548 bool SMySQL::isConnectionUsable()
 549 {
 550   bool usable = false;
 551   int sd = d_db.net.fd;
 552   bool wasNonBlocking = isNonBlocking(sd);
 553
 554   if (!wasNonBlocking) {
 555     if (!setNonBlocking(sd)) {
 556       return usable;
 557     }
 558   }
 559
 560   usable = isTCPSocketUsable(sd);
 561
 562   if (!wasNonBlocking) {
 563     if (!setBlocking(sd)) {
 564       usable = false;
 565     }
 566   }
 567
 568   return usable;
 569 }