MythTV  master
mythdbcon.cpp
Go to the documentation of this file.
1 #include <unistd.h>
2 
3 // ANSI C
4 #include <cstdlib>
5 
6 // Qt
7 #include <QCoreApplication>
8 #include <QElapsedTimer>
9 #include <QRegularExpression>
10 #include <QSemaphore>
11 #include <QSqlDriver>
12 #include <QSqlError>
13 #include <QSqlField>
14 #include <QSqlRecord>
15 #include <QVector>
16 #include <utility>
17 
18 // MythTV
19 #include "compat.h"
20 #include "mythdbcon.h"
21 #include "mythdb.h"
22 #include "mythcorecontext.h"
23 #include "mythlogging.h"
24 #include "mythsystemlegacy.h"
25 #include "exitcodes.h"
26 #include "mthread.h"
27 #include "mythdate.h"
28 #include "portchecker.h"
29 #include "mythmiscutil.h"
30 #include "mythrandom.h"
31 
32 #define DEBUG_RECONNECT 0
33 #if DEBUG_RECONNECT
34 #include <cstdlib>
35 #endif
36 
37 static constexpr std::chrono::seconds kPurgeTimeout { 1h };
38 
39 static QMutex sMutex;
40 
41 bool TestDatabase(const QString& dbHostName,
42  const QString& dbUserName,
43  QString dbPassword,
44  QString dbName,
45  int dbPort)
46 {
47  // ensure only one of these runs at a time, otherwise
48  // a segfault may happen as a connection is destroyed while
49  // being used. QSqlDatabase will remove a connection
50  // if another is created with the same name.
51  QMutexLocker locker(&sMutex);
52  bool ret = false;
53 
54  if (dbHostName.isEmpty() || dbUserName.isEmpty())
55  return ret;
56 
57  auto *db = new MSqlDatabase("dbtest");
58  if (!db)
59  return ret;
60 
61  DatabaseParams dbparms;
62  dbparms.m_dbName = std::move(dbName);
63  dbparms.m_dbUserName = dbUserName;
64  dbparms.m_dbPassword = std::move(dbPassword);
65  dbparms.m_dbHostName = dbHostName;
66  dbparms.m_dbPort = dbPort;
67 
68  // Just use some sane defaults for these values
69  dbparms.m_wolEnabled = false;
70  dbparms.m_wolReconnect = 1s;
71  dbparms.m_wolRetry = 3;
72  dbparms.m_wolCommand = QString();
73 
74  db->SetDBParams(dbparms);
75 
76  ret = db->OpenDatabase(true);
77 
78  delete db;
79  db = nullptr;
80 
81  return ret;
82 }
83 
84 MSqlDatabase::MSqlDatabase(QString name, QString driver)
85  : m_name(std::move(name)), m_driver(std::move(driver))
86 {
87  if (!QSqlDatabase::isDriverAvailable(m_driver))
88  {
89  LOG(VB_FLUSH, LOG_CRIT,
90  QString("FATAL: Unable to load the QT %1 driver, is it installed?")
91  .arg(m_driver));
92  exit(GENERIC_EXIT_DB_ERROR); // Exits before we can process the log queue
93  //return;
94  }
95 
96  m_db = QSqlDatabase::addDatabase(m_driver, m_name);
97  LOG(VB_DATABASE, LOG_INFO, "Database object created: " + m_name);
98 
99  if (!m_db.isValid() || m_db.isOpenError())
100  {
101  LOG(VB_FLUSH, LOG_CRIT, MythDB::DBErrorMessage(m_db.lastError()));
102  LOG(VB_FLUSH, LOG_CRIT, QString("FATAL: Unable to create database object (%1), the installed QT driver may be invalid.").arg(m_name));
103  exit(GENERIC_EXIT_DB_ERROR); // Exits before we can process the log queue
104  //return;
105  }
106  m_lastDBKick = MythDate::current().addSecs(-60);
107 }
108 
110 {
111  if (m_db.isOpen())
112  {
113  m_db.close();
114  m_db = QSqlDatabase(); // forces a destroy and must be done before
115  // removeDatabase() so that connections
116  // and queries are cleaned up correctly
117  QSqlDatabase::removeDatabase(m_name);
118  LOG(VB_DATABASE, LOG_INFO, "Database object deleted: " + m_name);
119  }
120 }
121 
123 {
124  if (m_db.isValid())
125  {
126  if (m_db.isOpen())
127  return true;
128  }
129  return false;
130 }
131 
132 bool MSqlDatabase::OpenDatabase(bool skipdb)
133 {
134  if (gCoreContext->GetDB()->IsDatabaseIgnored() && m_name != "dbtest")
135  return false;
136  if (!m_db.isValid())
137  {
138  LOG(VB_GENERAL, LOG_ERR,
139  "MSqlDatabase::OpenDatabase(), db object is not valid!");
140  return false;
141  }
142 
143  bool connected = true;
144 
145  if (!m_db.isOpen())
146  {
147  if (!skipdb)
148  m_dbparms = GetMythDB()->GetDatabaseParams();
149  m_db.setDatabaseName(m_dbparms.m_dbName);
150  m_db.setUserName(m_dbparms.m_dbUserName);
151  m_db.setPassword(m_dbparms.m_dbPassword);
152 
153  if (m_dbparms.m_dbHostName.isEmpty()) // Bootstrapping without a database?
154  {
155  // Pretend to be connected to reduce errors
156  return true;
157  }
158 
159  // code to ensure that a link-local ip address has the scope
160  int port = 3306;
161  if (m_dbparms.m_dbPort)
162  port = m_dbparms.m_dbPort;
164  m_db.setHostName(m_dbparms.m_dbHostName);
165 
166  if (m_dbparms.m_dbPort)
167  m_db.setPort(m_dbparms.m_dbPort);
168 
169  // Prefer using the faster localhost connection if using standard
170  // ports, even if the user specified a DBHostName of 127.0.0.1. This
171  // will cause MySQL to use a Unix socket (on *nix) or shared memory (on
172  // Windows) connection.
173  if ((m_dbparms.m_dbPort == 0 || m_dbparms.m_dbPort == 3306) &&
174  m_dbparms.m_dbHostName == "127.0.0.1")
175  m_db.setHostName("localhost");
176 
177  // Default read timeout is 10 mins - set a better value 300 seconds
178  m_db.setConnectOptions(QString("MYSQL_OPT_READ_TIMEOUT=300"));
179 
180  connected = m_db.open();
181 
182  if (!connected && m_dbparms.m_wolEnabled
184  {
185  int trycount = 0;
186 
187  while (!connected && trycount++ < m_dbparms.m_wolRetry)
188  {
189  LOG(VB_GENERAL, LOG_INFO,
190  QString("Using WOL to wakeup database server (Try %1 of "
191  "%2)")
192  .arg(trycount).arg(m_dbparms.m_wolRetry));
193 
195  {
196  LOG(VB_GENERAL, LOG_ERR,
197  QString("Failed to run WOL command '%1'")
198  .arg(m_dbparms.m_wolCommand));
199  }
200 
201  sleep(m_dbparms.m_wolReconnect.count());
202  connected = m_db.open();
203  }
204 
205  if (!connected)
206  {
207  LOG(VB_GENERAL, LOG_ERR,
208  "WOL failed, unable to connect to database!");
209  }
210  }
211  if (connected)
212  {
213  LOG(VB_DATABASE, LOG_INFO,
214  QString("[%1] Connected to database '%2' at host: %3")
215  .arg(m_name, m_db.databaseName(), m_db.hostName()));
216 
217  InitSessionVars();
218 
219  // WriteDelayed depends on SetHaveDBConnection() and SetHaveSchema()
220  // both being called with true, so order is important here.
221  GetMythDB()->SetHaveDBConnection(true);
222  if (!GetMythDB()->HaveSchema())
223  {
224  // We can't just check the count of QSqlDatabase::tables()
225  // because it returns all tables visible to the user in *all*
226  // databases (not just the current DB).
227  bool have_schema = false;
228  QString sql = "SELECT COUNT(TABLE_NAME) "
229  " FROM INFORMATION_SCHEMA.TABLES "
230  " WHERE TABLE_SCHEMA = DATABASE() "
231  " AND TABLE_TYPE = 'BASE TABLE';";
232  // We can't use MSqlQuery to determine if we have a schema,
233  // since it will open a new connection, which will try to check
234  // if we have a schema
235  QSqlQuery query(sql, m_db); // don't convert to MSqlQuery
236  if (query.next())
237  have_schema = query.value(0).toInt() > 1;
238  GetMythDB()->SetHaveSchema(have_schema);
239  }
240  GetMythDB()->WriteDelayedSettings();
241  }
242  }
243 
244  if (!connected)
245  {
246  GetMythDB()->SetHaveDBConnection(false);
247  LOG(VB_GENERAL, LOG_ERR, QString("[%1] Unable to connect to database!").arg(m_name));
248  LOG(VB_GENERAL, LOG_ERR, MythDB::DBErrorMessage(m_db.lastError()));
249  }
250 
251  return connected;
252 }
253 
255 {
256  m_lastDBKick = MythDate::current().addSecs(-60);
257 
258  if (!m_db.isOpen())
259  m_db.open();
260 
261  return m_db.isOpen();
262 }
263 
265 {
266  m_db.close();
267  m_db.open();
268 
269  bool open = m_db.isOpen();
270  if (open)
271  {
272  LOG(VB_GENERAL, LOG_INFO, "MySQL reconnected successfully");
273  InitSessionVars();
274  }
275 
276  return open;
277 }
278 
280 {
281  QSqlQuery query(m_db);
282 
283  // Make sure NOW() returns time in UTC...
284  query.exec("SET @@session.time_zone='+00:00'");
285  // Disable strict mode
286  query.exec("SET @@session.sql_mode=''");
287 }
288 
289 // -----------------------------------------------------------------------
290 
291 
292 
294 {
295  CloseDatabases();
296 
297  if (m_connCount != 0 || m_schedCon || m_channelCon)
298  {
299  LOG(VB_GENERAL, LOG_CRIT,
300  "MDBManager exiting with connections still open");
301  }
302 #if 0 /* some post logStop() debugging... */
303  cout<<"m_connCount: "<<m_connCount<<endl;
304  cout<<"m_schedCon: "<<m_schedCon<<endl;
305  cout<<"m_channelCon: "<<m_channelCon<<endl;
306 #endif
307 }
308 
310 {
311  PurgeIdleConnections(true);
312 
313  m_lock.lock();
314 
315  MSqlDatabase *db = nullptr;
316 
317 #if REUSE_CONNECTION
318  if (reuse)
319  {
320  db = m_inuse[QThread::currentThread()];
321  if (db != nullptr)
322  {
323  m_inuseCount[QThread::currentThread()]++;
324  m_lock.unlock();
325  return db;
326  }
327  }
328 #endif
329 
330  DBList &list = m_pool[QThread::currentThread()];
331  if (list.isEmpty())
332  {
333  DatabaseParams params = GetMythDB()->GetDatabaseParams();
334  db = new MSqlDatabase("DBManager" + QString::number(m_nextConnID++),
335  params.m_dbType);
336  ++m_connCount;
337  LOG(VB_DATABASE, LOG_INFO,
338  QString("New DB connection, total: %1").arg(m_connCount));
339  }
340  else
341  {
342  db = list.back();
343  list.pop_back();
344  }
345 
346 #if REUSE_CONNECTION
347  if (reuse)
348  {
349  m_inuseCount[QThread::currentThread()]=1;
350  m_inuse[QThread::currentThread()] = db;
351  }
352 #endif
353 
354  m_lock.unlock();
355 
356  db->OpenDatabase();
357 
358  return db;
359 }
360 
362 {
363  m_lock.lock();
364 
365 #if REUSE_CONNECTION
366  if (db == m_inuse[QThread::currentThread()])
367  {
368  int cnt = --m_inuseCount[QThread::currentThread()];
369  if (cnt > 0)
370  {
371  m_lock.unlock();
372  return;
373  }
374  m_inuse[QThread::currentThread()] = nullptr;
375  }
376 #endif
377 
378  if (db)
379  {
381  m_pool[QThread::currentThread()].push_front(db);
382  }
383 
384  m_lock.unlock();
385 
386  PurgeIdleConnections(true);
387 }
388 
390 {
391  QMutexLocker locker(&m_lock);
392 
393  leaveOne = leaveOne || (gCoreContext && gCoreContext->IsUIThread());
394 
395  QDateTime now = MythDate::current();
396  DBList &list = m_pool[QThread::currentThread()];
397  DBList::iterator it = list.begin();
398 
399  uint purgedConnections = 0;
400  uint totalConnections = 0;
401  MSqlDatabase *newDb = nullptr;
402  while (it != list.end())
403  {
404  totalConnections++;
405  if ((*it)->m_lastDBKick.secsTo(now) <= kPurgeTimeout.count())
406  {
407  ++it;
408  continue;
409  }
410 
411  // This connection has not been used in the kPurgeTimeout
412  // seconds close it.
413  MSqlDatabase *entry = *it;
414  it = list.erase(it);
415  --m_connCount;
416  purgedConnections++;
417 
418  // Qt's MySQL driver apparently keeps track of the number of
419  // open DB connections, and when it hits 0, calls
420  // my_thread_global_end(). The mysql library then assumes the
421  // application is ending and that all threads that created DB
422  // connections have already exited. This is rarely true, and
423  // may result in the mysql library pausing 5 seconds and
424  // printing a message like "Error in my_thread_global_end(): 1
425  // threads didn't exit". This workaround simply creates an
426  // extra DB connection before all pooled connections are
427  // purged so that my_thread_global_end() won't be called.
428  if (leaveOne && it == list.end() &&
429  purgedConnections > 0 &&
430  totalConnections == purgedConnections)
431  {
432  newDb = new MSqlDatabase("DBManager" +
433  QString::number(m_nextConnID++));
434  ++m_connCount;
435  LOG(VB_GENERAL, LOG_INFO,
436  QString("New DB connection, total: %1").arg(m_connCount));
437  newDb->m_lastDBKick = MythDate::current();
438  }
439 
440  LOG(VB_DATABASE, LOG_INFO, "Deleting idle DB connection...");
441  delete entry;
442  LOG(VB_DATABASE, LOG_INFO, "Done deleting idle DB connection.");
443  }
444  if (newDb)
445  list.push_front(newDb);
446 
447  if (purgedConnections)
448  {
449  LOG(VB_DATABASE, LOG_INFO,
450  QString("Purged %1 idle of %2 total DB connections.")
451  .arg(purgedConnections).arg(totalConnections));
452  }
453 }
454 
455 MSqlDatabase *MDBManager::getStaticCon(MSqlDatabase **dbcon, const QString& name)
456 {
457  if (!dbcon)
458  return nullptr;
459 
460  if (!*dbcon)
461  {
462  *dbcon = new MSqlDatabase(name);
463  LOG(VB_GENERAL, LOG_INFO, "New static DB connection" + name);
464  }
465 
466  (*dbcon)->OpenDatabase();
467 
468  if (!m_staticPool[QThread::currentThread()].contains(*dbcon))
469  m_staticPool[QThread::currentThread()].push_back(*dbcon);
470 
471  return *dbcon;
472 }
473 
475 {
476  return getStaticCon(&m_schedCon, "SchedCon");
477 }
478 
480 {
481  return getStaticCon(&m_channelCon, "ChannelCon");
482 }
483 
485 {
486  m_lock.lock();
487  DBList list = m_pool[QThread::currentThread()];
488  m_pool[QThread::currentThread()].clear();
489  m_lock.unlock();
490 
491  for (auto *conn : std::as_const(list))
492  {
493  LOG(VB_DATABASE, LOG_INFO,
494  "Closing DB connection named '" + conn->m_name + "'");
495  conn->m_db.close();
496  delete conn;
497  m_connCount--;
498  }
499 
500  m_lock.lock();
501  DBList &slist = m_staticPool[QThread::currentThread()];
502  while (!slist.isEmpty())
503  {
504  MSqlDatabase *db = slist.takeFirst();
505  LOG(VB_DATABASE, LOG_INFO,
506  "Closing DB connection named '" + db->m_name + "'");
507  db->m_db.close();
508  delete db;
509 
510  if (db == m_schedCon)
511  m_schedCon = nullptr;
512  if (db == m_channelCon)
513  m_channelCon = nullptr;
514  }
515  m_lock.unlock();
516 }
517 
518 
519 // -----------------------------------------------------------------------
520 
522 {
523  qi.db = nullptr;
524  qi.qsqldb = QSqlDatabase();
525  qi.returnConnection = true;
526 }
527 
528 
530  : QSqlQuery(QString(), qi.qsqldb)
531 {
532  m_db = qi.db;
534 
535  m_isConnected = m_db && m_db->isOpen();
536 }
537 
539 {
540  if (m_returnConnection)
541  {
542  MDBManager *dbmanager = GetMythDB()->GetDBManager();
543 
544  if (dbmanager && m_db)
545  {
546  dbmanager->pushConnection(m_db);
547  }
548  }
549 }
550 
552 {
553  bool reuse = kNormalConnection == _reuse;
554  MSqlDatabase *db = GetMythDB()->GetDBManager()->popConnection(reuse);
555  MSqlQueryInfo qi;
556 
557  InitMSqlQueryInfo(qi);
558 
559 
560  // Bootstrapping without a database?
561  //if (db->pretendHaveDB)
562  if (db->m_db.hostName().isEmpty())
563  {
564  // Return an invalid database so that QSqlQuery does nothing.
565  // Also works around a Qt4 bug where QSqlQuery::~QSqlQuery
566  // calls QMYSQLResult::cleanup() which uses mysql_next_result()
567 
568  GetMythDB()->GetDBManager()->pushConnection(db);
569  qi.returnConnection = false;
570  return qi;
571  }
572 
573  qi.db = db;
574  qi.qsqldb = db->db();
575 
576  db->KickDatabase();
577 
578  return qi;
579 }
580 
582 {
583  MSqlDatabase *db = GetMythDB()->GetDBManager()->getSchedCon();
584  MSqlQueryInfo qi;
585 
586  InitMSqlQueryInfo(qi);
587  qi.returnConnection = false;
588 
589  if (db)
590  {
591  qi.db = db;
592  qi.qsqldb = db->db();
593 
594  db->KickDatabase();
595  }
596 
597  return qi;
598 }
599 
601 {
602  MSqlDatabase *db = GetMythDB()->GetDBManager()->getChannelCon();
603  MSqlQueryInfo qi;
604 
605  InitMSqlQueryInfo(qi);
606  qi.returnConnection = false;
607 
608  if (db)
609  {
610  qi.db = db;
611  qi.qsqldb = db->db();
612 
613  db->KickDatabase();
614  }
615 
616  return qi;
617 }
618 
620 {
621  if (!m_db)
622  {
623  // Database structure's been deleted
624  return false;
625  }
626 
627  if (m_lastPreparedQuery.isEmpty())
628  {
629  LOG(VB_GENERAL, LOG_ERR,
630  "MSqlQuery::exec(void) called without a prepared query.");
631  return false;
632  }
633 
634 #if DEBUG_RECONNECT
635  if (rand_bool(50))
636  {
637  LOG(VB_GENERAL, LOG_INFO,
638  "MSqlQuery disconnecting DB to test reconnection logic");
639  m_db->m_db.close();
640  }
641 #endif
642 
643  // Database connection down. Try to restart it, give up if it's still
644  // down
645  if (!m_db->isOpen() && !Reconnect())
646  {
647  LOG(VB_GENERAL, LOG_INFO, "MySQL server disconnected");
648  return false;
649  }
650 
651  QElapsedTimer timer;
652  timer.start();
653 
654  bool result = QSqlQuery::exec();
655  qint64 elapsed = timer.elapsed();
656 
657  if (!result && lostConnectionCheck())
658  result = QSqlQuery::exec();
659 
660  if (!result)
661  {
662  QString err = MythDB::GetError("MSqlQuery", *this);
663 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
664  MSqlBindings tmp = QSqlQuery::boundValues();
665 #else
666  QVariantList tmp = QSqlQuery::boundValues();
667 #endif
668  bool has_null_strings = false;
669  // NOLINTNEXTLINE(modernize-loop-convert)
670  for (auto it = tmp.begin(); it != tmp.end(); ++it)
671  {
672 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
673  auto type = static_cast<QMetaType::Type>(it->type());
674 #else
675  auto type = it->typeId();
676 #endif
677  if (type != QMetaType::QString)
678  continue;
679  if (it->isNull() || it->toString().isNull())
680  {
681  has_null_strings = true;
682  *it = QVariant(QString(""));
683  }
684  }
685  if (has_null_strings)
686  {
687 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
688  bindValues(tmp);
689 #else
690  for (int i = 0; i < static_cast<int>(tmp.size()); i++)
691  QSqlQuery::bindValue(i, tmp.at(i));
692 #endif
693  timer.restart();
694  result = QSqlQuery::exec();
695  elapsed = timer.elapsed();
696  }
697  if (result)
698  {
699  LOG(VB_GENERAL, LOG_ERR,
700  QString("Original query failed, but resend with empty "
701  "strings in place of NULL strings worked. ") +
702  "\n" + err);
703  }
704  }
705 
706  if (VERBOSE_LEVEL_CHECK(VB_DATABASE, LOG_INFO))
707  {
708  QString str = lastQuery();
709 
710  // Sadly, neither executedQuery() nor lastQuery() display
711  // the values in bound queries against a MySQL5 database.
712  // So, replace the named placeholders with their values.
713 
714 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
715  QMapIterator<QString, QVariant> b = boundValues();
716  while (b.hasNext())
717  {
718  b.next();
719  str.replace(b.key(), '\'' + b.value().toString() + '\'');
720  }
721 #else
722  QVariantList b = boundValues();
723  static const QRegularExpression placeholders { "(:\\w+)" };
724  auto match = placeholders.match(str);
725  while (match.hasMatch())
726  {
727  str.replace(match.capturedStart(), match.capturedLength(),
728  b.isEmpty()
729  ? "\'INVALID\'"
730  : '\'' + b.takeFirst().toString() + '\'');
731  match = placeholders.match(str);
732  }
733 #endif
734 
735  LOG(VB_DATABASE, LOG_INFO,
736  QString("MSqlQuery::exec(%1) %2%3%4")
737  .arg(m_db->MSqlDatabase::GetConnectionName(), str,
738  QString(" <<<< Took %1ms").arg(QString::number(elapsed)),
739  isSelect()
740  ? QString(", Returned %1 row(s)").arg(size())
741  : QString()));
742  }
743 
744  return result;
745 }
746 
747 bool MSqlQuery::exec(const QString &query)
748 {
749  if (!m_db)
750  {
751  // Database structure's been deleted
752  return false;
753  }
754 
755  // Database connection down. Try to restart it, give up if it's still
756  // down
757  if (!m_db->isOpen() && !Reconnect())
758  {
759  LOG(VB_GENERAL, LOG_INFO, "MySQL server disconnected");
760  return false;
761  }
762 
763  bool result = QSqlQuery::exec(query);
764 
765  if (!result && lostConnectionCheck())
766  result = QSqlQuery::exec(query);
767 
768  LOG(VB_DATABASE, LOG_INFO,
769  QString("MSqlQuery::exec(%1) %2%3")
770  .arg(m_db->MSqlDatabase::GetConnectionName(), query,
771  isSelect()
772  ? QString(" <<<< Returns %1 row(s)").arg(size())
773  : QString()));
774 
775  return result;
776 }
777 
778 bool MSqlQuery::seekDebug(const char *type, bool result,
779  int where, bool relative) const
780 {
781  if (result && VERBOSE_LEVEL_CHECK(VB_DATABASE, LOG_DEBUG))
782  {
783  QString str;
784  QSqlRecord rec = record();
785 
786  for (int i = 0; i < rec.count(); i++)
787  {
788  if (!str.isEmpty())
789  str.append(", ");
790 
791  str.append(rec.fieldName(i) + " = " +
792  value(i).toString());
793  }
794 
795  if (QString("seek")==type)
796  {
797  LOG(VB_DATABASE, LOG_DEBUG,
798  QString("MSqlQuery::seek(%1,%2,%3) Result: \"%4\"")
799  .arg(m_db->MSqlDatabase::GetConnectionName())
800  .arg(where).arg(relative)
801  .arg(str));
802  }
803  else
804  {
805  LOG(VB_DATABASE, LOG_DEBUG,
806  QString("MSqlQuery::%1(%2) Result: \"%3\"")
807  .arg(type, m_db->MSqlDatabase::GetConnectionName(), str));
808  }
809  }
810  return result;
811 }
812 
813 bool MSqlQuery::next(void)
814 {
815  return seekDebug("next", QSqlQuery::next(), 0, false);
816 }
817 
819 {
820  return seekDebug("previous", QSqlQuery::previous(), 0, false);
821 }
822 
824 {
825  return seekDebug("first", QSqlQuery::first(), 0, false);
826 }
827 
828 bool MSqlQuery::last(void)
829 {
830  return seekDebug("last", QSqlQuery::last(), 0, false);
831 }
832 
833 bool MSqlQuery::seek(int where, bool relative)
834 {
835  return seekDebug("seek", QSqlQuery::seek(where, relative), where, relative);
836 }
837 
838 bool MSqlQuery::prepare(const QString& query)
839 {
840  if (!m_db)
841  {
842  // Database structure's been deleted
843  return false;
844  }
845 
846  m_lastPreparedQuery = query;
847 
848  if (!m_db->isOpen() && !Reconnect())
849  {
850  LOG(VB_GENERAL, LOG_INFO, "MySQL server disconnected");
851  return false;
852  }
853 
854  // QT docs indicate that there are significant speed ups and a reduction
855  // in memory usage by enabling forward-only cursors
856  //
857  // Unconditionally enable this since all existing uses of the database
858  // iterate forward over the result set.
859  setForwardOnly(true);
860 
861  bool ok = QSqlQuery::prepare(query);
862 
863  if (!ok && lostConnectionCheck())
864  ok = true;
865 
866  if (!ok && !(GetMythDB()->SuppressDBMessages()))
867  {
868  LOG(VB_GENERAL, LOG_ERR,
869  QString("Error preparing query: %1").arg(query));
870  LOG(VB_GENERAL, LOG_ERR,
871  MythDB::DBErrorMessage(QSqlQuery::lastError()));
872  }
873 
874  return ok;
875 }
876 
878 {
879  MSqlDatabase *db = GetMythDB()->GetDBManager()->popConnection(true);
880 
881  // popConnection() has already called OpenDatabase(),
882  // so we only have to check if it was successful:
883  bool isOpen = db->isOpen();
884 
885  GetMythDB()->GetDBManager()->pushConnection(db);
886  return isOpen;
887 }
888 
889 void MSqlQuery::bindValue(const QString &placeholder, const QVariant &val)
890 {
891 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
892  if (static_cast<QMetaType::Type>(val.type()) == QMetaType::QDateTime)
893  {
894  QSqlQuery::bindValue(placeholder,
895  MythDate::toString(val.toDateTime(), MythDate::kDatabase),
896  QSql::In);
897  return;
898  }
899 #endif
900  QSqlQuery::bindValue(placeholder, val, QSql::In);
901 }
902 
903 void MSqlQuery::bindValueNoNull(const QString &placeholder, const QVariant &val)
904 {
905 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
906  auto type = static_cast<QMetaType::Type>(val.type());
907 #else
908  auto type = val.typeId();
909 #endif
910  if (type == QMetaType::QString && val.toString().isNull())
911  {
912  QSqlQuery::bindValue(placeholder, QString(""), QSql::In);
913  return;
914  }
915 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
916  if (type == QMetaType::QDateTime)
917  {
918  QSqlQuery::bindValue(placeholder,
919  MythDate::toString(val.toDateTime(), MythDate::kDatabase),
920  QSql::In);
921  return;
922  }
923 #endif
924  QSqlQuery::bindValue(placeholder, val, QSql::In);
925 }
926 
927 void MSqlQuery::bindValues(const MSqlBindings &bindings)
928 {
929  MSqlBindings::const_iterator it;
930  for (it = bindings.begin(); it != bindings.end(); ++it)
931  {
932  bindValue(it.key(), it.value());
933  }
934 }
935 
937 {
938  return QSqlQuery::lastInsertId();
939 }
940 
942 {
943  if (!m_db->Reconnect())
944  return false;
945  if (!m_lastPreparedQuery.isEmpty())
946  {
947 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
948  MSqlBindings tmp = QSqlQuery::boundValues();
949  if (!QSqlQuery::prepare(m_lastPreparedQuery))
950  return false;
951  bindValues(tmp);
952 #else
953  QVariantList tmp = QSqlQuery::boundValues();
954  if (!QSqlQuery::prepare(m_lastPreparedQuery))
955  return false;
956  for (int i = 0; i < static_cast<int>(tmp.size()); i++)
957  QSqlQuery::bindValue(i, tmp.at(i));
958 #endif
959  }
960  return true;
961 }
962 
964 {
965  // MySQL: Error number: 2006; Symbol: CR_SERVER_GONE_ERROR
966  // MySQL: Error number: 2013; Symbol: CR_SERVER_LOST
967  // MySQL: Error number: 4031; Symbol: ER_CLIENT_INTERACTION_TIMEOUT
968  // Note: In MariaDB, 4031 = ER_REFERENCED_TRG_DOES_NOT_EXIST
969 
970  static QStringList kLostConnectionCodes = { "2006", "2013", "4031" };
971 
972  QString error_code = QSqlQuery::lastError().nativeErrorCode();
973 
974  // Make capturing of new 'lost connection' like error codes easy.
975  LOG(VB_GENERAL, LOG_DEBUG, QString("SQL Native Error Code: %1")
976  .arg(error_code));
977 
978  // If the query failed with any of the error codes that say the server
979  // is gone, close and reopen the database connection.
980  return (kLostConnectionCodes.contains(error_code) && Reconnect());
981 
982 }
983 
985 {
986  MSqlBindings::Iterator it;
987  for (it = addfrom.begin(); it != addfrom.end(); ++it)
988  {
989  output.insert(it.key(), it.value());
990  }
991 }
992 
993 struct Holder {
994  explicit Holder( QString hldr = QString(), int pos = -1 )
995  : m_holderName(std::move( hldr )), m_holderPos( pos ) {}
996 
997  bool operator==( const Holder& h ) const
998  { return h.m_holderPos == m_holderPos && h.m_holderName == m_holderName; }
999  bool operator!=( const Holder& h ) const
1000  { return h.m_holderPos != m_holderPos || h.m_holderName != m_holderName; }
1001  QString m_holderName;
1003 };
1004 
1005 void MSqlEscapeAsAQuery(QString &query, const MSqlBindings &bindings)
1006 {
1007  MSqlQuery result(MSqlQuery::InitCon());
1008 
1009  static const QRegularExpression rx { "('[^']+'|:\\w+)",
1010  QRegularExpression::UseUnicodePropertiesOption};
1011 
1012  QVector<Holder> holders;
1013 
1014  auto matchIter = rx.globalMatch(query);
1015  while (matchIter.hasNext())
1016  {
1017  auto match = matchIter.next();
1018  if (match.capturedLength(1) > 0)
1019  holders.append(Holder(match.captured(), match.capturedStart()));
1020  }
1021 
1022  QVariant val;
1023  QString holder;
1024 
1025  for (int i = holders.count() - 1; i >= 0; --i)
1026  {
1027  holder = holders[(uint)i].m_holderName;
1028  val = bindings[holder];
1029 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
1030  QSqlField f("", val.type());
1031 #else
1032  QSqlField f("", val.metaType());
1033 #endif
1034  if (val.isNull())
1035  f.clear();
1036  else
1037  f.setValue(val);
1038 
1039  query = query.replace((uint)holders[(uint)i].m_holderPos, holder.length(),
1040  result.driver()->formatValue(f));
1041  }
1042 }
MSqlBindings
QMap< QString, QVariant > MSqlBindings
typedef for a map of string -> string bindings for generic queries.
Definition: mythdbcon.h:100
MSqlQuery::next
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
Definition: mythdbcon.cpp:813
MSqlQuery
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:127
MythDate::toString
QString toString(const QDateTime &raw_dt, uint format)
Returns formatted string representing the time.
Definition: mythdate.cpp:84
MSqlQuery::bindValueNoNull
void bindValueNoNull(const QString &placeholder, const QVariant &val)
Add a single binding, taking care not to set a NULL value.
Definition: mythdbcon.cpp:903
MSqlQuery::size
int size(void) const
Definition: mythdbcon.h:214
MDBManager::m_lock
QMutex m_lock
Definition: mythdbcon.h:75
DatabaseParams::m_dbHostName
QString m_dbHostName
database server
Definition: mythdbparams.h:22
MSqlQuery::MSqlQuery
MSqlQuery(const MSqlQueryInfo &qi)
Get DB connection from pool.
Definition: mythdbcon.cpp:529
MSqlDatabase::InitSessionVars
void InitSessionVars(void)
Definition: mythdbcon.cpp:279
kPurgeTimeout
static constexpr std::chrono::seconds kPurgeTimeout
Definition: mythdbcon.cpp:37
mythdb.h
MythDB::DBErrorMessage
static QString DBErrorMessage(const QSqlError &err)
Definition: mythdb.cpp:231
mythrandom.h
MDBManager::getSchedCon
MSqlDatabase * getSchedCon(void)
Definition: mythdbcon.cpp:474
DatabaseParams
Structure containing the basic Database parameters.
Definition: mythdbparams.h:10
Holder::operator==
bool operator==(const Holder &h) const
Definition: mythdbcon.cpp:997
MSqlQuery::m_db
MSqlDatabase * m_db
Definition: mythdbcon.h:251
MDBManager::m_inuseCount
QHash< QThread *, int > m_inuseCount
Definition: mythdbcon.h:80
MSqlQuery::record
QSqlRecord record(void) const
Definition: mythdbcon.h:216
MSqlQuery::bindValues
void bindValues(const MSqlBindings &bindings)
Add all the bindings in the passed in bindings.
Definition: mythdbcon.cpp:927
MSqlAddMoreBindings
void MSqlAddMoreBindings(MSqlBindings &output, MSqlBindings &addfrom)
Add the entries in addfrom to the map in output.
Definition: mythdbcon.cpp:984
MDBManager
DB connection pool, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:54
MSqlQuery::lastInsertId
QVariant lastInsertId()
Return the id of the last inserted row.
Definition: mythdbcon.cpp:936
MSqlDatabase::KickDatabase
bool KickDatabase(void)
Definition: mythdbcon.cpp:254
MDBManager::m_nextConnID
int m_nextConnID
Definition: mythdbcon.h:83
Holder::m_holderPos
int m_holderPos
Definition: mythdbcon.cpp:1002
VERBOSE_LEVEL_CHECK
static bool VERBOSE_LEVEL_CHECK(uint64_t mask, LogLevel_t level)
Definition: mythlogging.h:29
MSqlQuery::value
QVariant value(int i) const
Definition: mythdbcon.h:204
mythdbcon.h
MSqlDatabase::db
QSqlDatabase db(void) const
Definition: mythdbcon.h:41
TestDatabase
bool TestDatabase(const QString &dbHostName, const QString &dbUserName, QString dbPassword, QString dbName, int dbPort)
Definition: mythdbcon.cpp:41
MDBManager::PurgeIdleConnections
void PurgeIdleConnections(bool leaveOne=false)
Definition: mythdbcon.cpp:389
MSqlQuery::exec
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:619
MythCoreContext::IsUIThread
bool IsUIThread(void)
Definition: mythcorecontext.cpp:1348
LOG
#define LOG(_MASK_, _LEVEL_, _QSTRING_)
Definition: mythlogging.h:39
DatabaseParams::m_dbType
QString m_dbType
database type (MySQL, Postgres, etc.)
Definition: mythdbparams.h:28
Holder::Holder
Holder(QString hldr=QString(), int pos=-1)
Definition: mythdbcon.cpp:994
GetMythDB
MythDB * GetMythDB(void)
Definition: mythdb.cpp:50
MDBManager::getChannelCon
MSqlDatabase * getChannelCon(void)
Definition: mythdbcon.cpp:479
MythDate::current
QDateTime current(bool stripped)
Returns current Date and Time in UTC.
Definition: mythdate.cpp:14
MythRandomStd::rand_bool
bool rand_bool(uint32_t chance=2)
return a random bool with P(true) = 1/chance
Definition: mythrandom.h:75
DatabaseParams::m_wolReconnect
std::chrono::seconds m_wolReconnect
seconds to wait for reconnect
Definition: mythdbparams.h:35
tmp
static guint32 * tmp
Definition: goom_core.cpp:26
Holder
Definition: mythdbcon.cpp:993
mythsystemlegacy.h
MSqlDatabase::isOpen
bool isOpen(void)
Definition: mythdbcon.cpp:122
InitMSqlQueryInfo
static void InitMSqlQueryInfo(MSqlQueryInfo &qi)
Definition: mythdbcon.cpp:521
DatabaseParams::m_dbPort
int m_dbPort
database port
Definition: mythdbparams.h:24
MSqlQuery::previous
bool previous(void)
Wrap QSqlQuery::previous() so we can display the query results.
Definition: mythdbcon.cpp:818
MDBManager::DBList
QList< MSqlDatabase * > DBList
Definition: mythdbcon.h:76
mythdate.h
MSqlQuery::Reconnect
bool Reconnect(void)
Reconnects server and re-prepares and re-binds the last prepared query.
Definition: mythdbcon.cpp:941
MDBManager::m_connCount
int m_connCount
Definition: mythdbcon.h:84
MDBManager::CloseDatabases
void CloseDatabases(void)
Definition: mythdbcon.cpp:484
MSqlDatabase::MSqlDatabase
MSqlDatabase(QString name, QString driver="QMYSQL")
Definition: mythdbcon.cpp:84
mythlogging.h
MSqlQueryInfo
MSqlDatabase Info, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:92
MSqlQuery::first
bool first(void)
Wrap QSqlQuery::first() so we can display the query results.
Definition: mythdbcon.cpp:823
MythDB::GetError
static QString GetError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:195
MDBManager::~MDBManager
~MDBManager(void)
Definition: mythdbcon.cpp:293
MDBManager::m_pool
QHash< QThread *, DBList > m_pool
Definition: mythdbcon.h:77
MSqlDatabase::m_driver
QString m_driver
Definition: mythdbcon.h:47
MSqlQuery::InitCon
static MSqlQueryInfo InitCon(ConnectionReuse _reuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:551
compat.h
MSqlDatabase::Reconnect
bool Reconnect(void)
Definition: mythdbcon.cpp:264
MDBManager::m_inuse
QHash< QThread *, MSqlDatabase * > m_inuse
Definition: mythdbcon.h:79
MSqlQuery::testDBConnection
static bool testDBConnection()
Checks DB connection + login (login info via Mythcontext)
Definition: mythdbcon.cpp:877
MDBManager::m_staticPool
QHash< QThread *, DBList > m_staticPool
Definition: mythdbcon.h:88
MSqlQueryInfo::db
MSqlDatabase * db
Definition: mythdbcon.h:94
MSqlQuery::boundValues
QVariantList boundValues(void) const
Definition: mythdbcon.h:211
MythCoreContext::GetDB
MythDB * GetDB(void)
Definition: mythcorecontext.cpp:1753
MSqlQuery::lostConnectionCheck
bool lostConnectionCheck(void)
lostConnectionCheck tests for SQL error codes that indicate the connection to the server has been los...
Definition: mythdbcon.cpp:963
MythWakeup
bool MythWakeup(const QString &wakeUpCommand, uint flags, std::chrono::seconds timeout)
Definition: mythmiscutil.cpp:630
MSqlDatabase::m_db
QSqlDatabase m_db
Definition: mythdbcon.h:48
MSqlQuery::SchedCon
static MSqlQueryInfo SchedCon()
Returns dedicated connection. (Required for using temporary SQL tables.)
Definition: mythdbcon.cpp:581
MSqlQuery::setForwardOnly
void setForwardOnly(bool f)
Definition: mythdbcon.h:218
MSqlQuery::kNormalConnection
@ kNormalConnection
Definition: mythdbcon.h:229
MSqlDatabase::~MSqlDatabase
~MSqlDatabase(void)
Definition: mythdbcon.cpp:109
MSqlQuery::seek
bool seek(int where, bool relative=false)
Wrap QSqlQuery::seek(int,bool)
Definition: mythdbcon.cpp:833
DatabaseParams::m_dbPassword
QString m_dbPassword
DB password.
Definition: mythdbparams.h:26
portchecker.h
DatabaseParams::m_wolRetry
int m_wolRetry
times to retry to reconnect
Definition: mythdbparams.h:36
PortChecker::resolveLinkLocal
static bool resolveLinkLocal(QString &host, int port, std::chrono::milliseconds timeLimit=30s)
Convenience method to resolve link-local address.
Definition: portchecker.cpp:226
DatabaseParams::m_dbName
QString m_dbName
database name
Definition: mythdbparams.h:27
MSqlEscapeAsAQuery
void MSqlEscapeAsAQuery(QString &query, const MSqlBindings &bindings)
Given a partial query string and a bindings object, escape the string.
Definition: mythdbcon.cpp:1005
uint
unsigned int uint
Definition: compat.h:81
gCoreContext
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
Definition: mythcorecontext.cpp:55
MDBManager::getStaticCon
MSqlDatabase * getStaticCon(MSqlDatabase **dbcon, const QString &name)
Definition: mythdbcon.cpp:455
MDBManager::popConnection
MSqlDatabase * popConnection(bool reuse)
Definition: mythdbcon.cpp:309
MSqlQuery::m_lastPreparedQuery
QString m_lastPreparedQuery
Definition: mythdbcon.h:254
MSqlDatabase
QSqlDatabase wrapper, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:25
MSqlQuery::ChannelCon
static MSqlQueryInfo ChannelCon()
Returns dedicated connection. (Required for using temporary SQL tables.)
Definition: mythdbcon.cpp:600
MSqlQueryInfo::returnConnection
bool returnConnection
Definition: mythdbcon.h:96
MSqlQuery::~MSqlQuery
~MSqlQuery()
Returns connection to pool.
Definition: mythdbcon.cpp:538
mythmiscutil.h
MSqlDatabase::m_lastDBKick
QDateTime m_lastDBKick
Definition: mythdbcon.h:49
Holder::m_holderName
QString m_holderName
Definition: mythdbcon.cpp:1001
mythcorecontext.h
DatabaseParams::m_wolCommand
QString m_wolCommand
command to use for wake-on-lan
Definition: mythdbparams.h:37
MSqlQuery::bindValue
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
Definition: mythdbcon.cpp:889
std
Definition: mythchrono.h:23
DatabaseParams::m_dbUserName
QString m_dbUserName
DB user name.
Definition: mythdbparams.h:25
sMutex
static QMutex sMutex
Definition: mythdbcon.cpp:39
MDBManager::pushConnection
void pushConnection(MSqlDatabase *db)
Definition: mythdbcon.cpp:361
Holder::operator!=
bool operator!=(const Holder &h) const
Definition: mythdbcon.cpp:999
mthread.h
Reconnect
Definition: backendconnectionmanager.cpp:28
MythDate::kDatabase
@ kDatabase
Default UTC, database format.
Definition: mythdate.h:27
MSqlDatabase::m_name
QString m_name
Definition: mythdbcon.h:46
MSqlQuery::seekDebug
bool seekDebug(const char *type, bool result, int where, bool relative) const
Definition: mythdbcon.cpp:778
MSqlQueryInfo::qsqldb
QSqlDatabase qsqldb
Definition: mythdbcon.h:95
MythCoreContext::IsWOLAllowed
bool IsWOLAllowed() const
Definition: mythcorecontext.cpp:634
MDBManager::m_channelCon
MSqlDatabase * m_channelCon
Definition: mythdbcon.h:87
DatabaseParams::m_wolEnabled
bool m_wolEnabled
true if wake-on-lan params are used
Definition: mythdbparams.h:34
MSqlQuery::ConnectionReuse
ConnectionReuse
Definition: mythdbcon.h:226
MDBManager::m_schedCon
MSqlDatabase * m_schedCon
Definition: mythdbcon.h:86
exitcodes.h
MSqlQuery::m_isConnected
bool m_isConnected
Definition: mythdbcon.h:252
output
#define output
Definition: synaesthesia.cpp:220
MSqlQuery::m_returnConnection
bool m_returnConnection
Definition: mythdbcon.h:253
MSqlQuery::last
bool last(void)
Wrap QSqlQuery::last() so we can display the query results.
Definition: mythdbcon.cpp:828
MSqlDatabase::OpenDatabase
bool OpenDatabase(bool skipdb=false)
Definition: mythdbcon.cpp:132
MSqlQuery::driver
const QSqlDriver * driver(void) const
Definition: mythdbcon.h:220
MSqlQuery::prepare
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.
Definition: mythdbcon.cpp:838
MSqlDatabase::m_dbparms
DatabaseParams m_dbparms
Definition: mythdbcon.h:50
GENERIC_EXIT_DB_ERROR
@ GENERIC_EXIT_DB_ERROR
Database error.
Definition: exitcodes.h:18