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 = m_db.exec(sql); // 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  // Make sure NOW() returns time in UTC...
282  m_db.exec("SET @@session.time_zone='+00:00'");
283  // Disable strict mode
284  m_db.exec("SET @@session.sql_mode=''");
285 }
286 
287 // -----------------------------------------------------------------------
288 
289 
290 
292 {
293  CloseDatabases();
294 
295  if (m_connCount != 0 || m_schedCon || m_channelCon)
296  {
297  LOG(VB_GENERAL, LOG_CRIT,
298  "MDBManager exiting with connections still open");
299  }
300 #if 0 /* some post logStop() debugging... */
301  cout<<"m_connCount: "<<m_connCount<<endl;
302  cout<<"m_schedCon: "<<m_schedCon<<endl;
303  cout<<"m_channelCon: "<<m_channelCon<<endl;
304 #endif
305 }
306 
308 {
309  PurgeIdleConnections(true);
310 
311  m_lock.lock();
312 
313  MSqlDatabase *db = nullptr;
314 
315 #if REUSE_CONNECTION
316  if (reuse)
317  {
318  db = m_inuse[QThread::currentThread()];
319  if (db != nullptr)
320  {
321  m_inuseCount[QThread::currentThread()]++;
322  m_lock.unlock();
323  return db;
324  }
325  }
326 #endif
327 
328  DBList &list = m_pool[QThread::currentThread()];
329  if (list.isEmpty())
330  {
331  DatabaseParams params = GetMythDB()->GetDatabaseParams();
332  db = new MSqlDatabase("DBManager" + QString::number(m_nextConnID++),
333  params.m_dbType);
334  ++m_connCount;
335  LOG(VB_DATABASE, LOG_INFO,
336  QString("New DB connection, total: %1").arg(m_connCount));
337  }
338  else
339  {
340  db = list.back();
341  list.pop_back();
342  }
343 
344 #if REUSE_CONNECTION
345  if (reuse)
346  {
347  m_inuseCount[QThread::currentThread()]=1;
348  m_inuse[QThread::currentThread()] = db;
349  }
350 #endif
351 
352  m_lock.unlock();
353 
354  db->OpenDatabase();
355 
356  return db;
357 }
358 
360 {
361  m_lock.lock();
362 
363 #if REUSE_CONNECTION
364  if (db == m_inuse[QThread::currentThread()])
365  {
366  int cnt = --m_inuseCount[QThread::currentThread()];
367  if (cnt > 0)
368  {
369  m_lock.unlock();
370  return;
371  }
372  m_inuse[QThread::currentThread()] = nullptr;
373  }
374 #endif
375 
376  if (db)
377  {
379  m_pool[QThread::currentThread()].push_front(db);
380  }
381 
382  m_lock.unlock();
383 
384  PurgeIdleConnections(true);
385 }
386 
388 {
389  QMutexLocker locker(&m_lock);
390 
391  leaveOne = leaveOne || (gCoreContext && gCoreContext->IsUIThread());
392 
393  QDateTime now = MythDate::current();
394  DBList &list = m_pool[QThread::currentThread()];
395  DBList::iterator it = list.begin();
396 
397  uint purgedConnections = 0;
398  uint totalConnections = 0;
399  MSqlDatabase *newDb = nullptr;
400  while (it != list.end())
401  {
402  totalConnections++;
403  if ((*it)->m_lastDBKick.secsTo(now) <= kPurgeTimeout.count())
404  {
405  ++it;
406  continue;
407  }
408 
409  // This connection has not been used in the kPurgeTimeout
410  // seconds close it.
411  MSqlDatabase *entry = *it;
412  it = list.erase(it);
413  --m_connCount;
414  purgedConnections++;
415 
416  // Qt's MySQL driver apparently keeps track of the number of
417  // open DB connections, and when it hits 0, calls
418  // my_thread_global_end(). The mysql library then assumes the
419  // application is ending and that all threads that created DB
420  // connections have already exited. This is rarely true, and
421  // may result in the mysql library pausing 5 seconds and
422  // printing a message like "Error in my_thread_global_end(): 1
423  // threads didn't exit". This workaround simply creates an
424  // extra DB connection before all pooled connections are
425  // purged so that my_thread_global_end() won't be called.
426  if (leaveOne && it == list.end() &&
427  purgedConnections > 0 &&
428  totalConnections == purgedConnections)
429  {
430  newDb = new MSqlDatabase("DBManager" +
431  QString::number(m_nextConnID++));
432  ++m_connCount;
433  LOG(VB_GENERAL, LOG_INFO,
434  QString("New DB connection, total: %1").arg(m_connCount));
435  newDb->m_lastDBKick = MythDate::current();
436  }
437 
438  LOG(VB_DATABASE, LOG_INFO, "Deleting idle DB connection...");
439  delete entry;
440  LOG(VB_DATABASE, LOG_INFO, "Done deleting idle DB connection.");
441  }
442  if (newDb)
443  list.push_front(newDb);
444 
445  if (purgedConnections)
446  {
447  LOG(VB_DATABASE, LOG_INFO,
448  QString("Purged %1 idle of %2 total DB connections.")
449  .arg(purgedConnections).arg(totalConnections));
450  }
451 }
452 
453 MSqlDatabase *MDBManager::getStaticCon(MSqlDatabase **dbcon, const QString& name)
454 {
455  if (!dbcon)
456  return nullptr;
457 
458  if (!*dbcon)
459  {
460  *dbcon = new MSqlDatabase(name);
461  LOG(VB_GENERAL, LOG_INFO, "New static DB connection" + name);
462  }
463 
464  (*dbcon)->OpenDatabase();
465 
466  if (!m_staticPool[QThread::currentThread()].contains(*dbcon))
467  m_staticPool[QThread::currentThread()].push_back(*dbcon);
468 
469  return *dbcon;
470 }
471 
473 {
474  return getStaticCon(&m_schedCon, "SchedCon");
475 }
476 
478 {
479  return getStaticCon(&m_channelCon, "ChannelCon");
480 }
481 
483 {
484  m_lock.lock();
485  DBList list = m_pool[QThread::currentThread()];
486  m_pool[QThread::currentThread()].clear();
487  m_lock.unlock();
488 
489  for (auto *conn : qAsConst(list))
490  {
491  LOG(VB_DATABASE, LOG_INFO,
492  "Closing DB connection named '" + conn->m_name + "'");
493  conn->m_db.close();
494  delete conn;
495  m_connCount--;
496  }
497 
498  m_lock.lock();
499  DBList &slist = m_staticPool[QThread::currentThread()];
500  while (!slist.isEmpty())
501  {
502  MSqlDatabase *db = slist.takeFirst();
503  LOG(VB_DATABASE, LOG_INFO,
504  "Closing DB connection named '" + db->m_name + "'");
505  db->m_db.close();
506  delete db;
507 
508  if (db == m_schedCon)
509  m_schedCon = nullptr;
510  if (db == m_channelCon)
511  m_channelCon = nullptr;
512  }
513  m_lock.unlock();
514 }
515 
516 
517 // -----------------------------------------------------------------------
518 
520 {
521  qi.db = nullptr;
522  qi.qsqldb = QSqlDatabase();
523  qi.returnConnection = true;
524 }
525 
526 
528  : QSqlQuery(QString(), qi.qsqldb)
529 {
530  m_db = qi.db;
532 
533  m_isConnected = m_db && m_db->isOpen();
534 }
535 
537 {
538  if (m_returnConnection)
539  {
540  MDBManager *dbmanager = GetMythDB()->GetDBManager();
541 
542  if (dbmanager && m_db)
543  {
544  dbmanager->pushConnection(m_db);
545  }
546  }
547 }
548 
550 {
551  bool reuse = kNormalConnection == _reuse;
552  MSqlDatabase *db = GetMythDB()->GetDBManager()->popConnection(reuse);
553  MSqlQueryInfo qi;
554 
555  InitMSqlQueryInfo(qi);
556 
557 
558  // Bootstrapping without a database?
559  //if (db->pretendHaveDB)
560  if (db->m_db.hostName().isEmpty())
561  {
562  // Return an invalid database so that QSqlQuery does nothing.
563  // Also works around a Qt4 bug where QSqlQuery::~QSqlQuery
564  // calls QMYSQLResult::cleanup() which uses mysql_next_result()
565 
566  GetMythDB()->GetDBManager()->pushConnection(db);
567  qi.returnConnection = false;
568  return qi;
569  }
570 
571  qi.db = db;
572  qi.qsqldb = db->db();
573 
574  db->KickDatabase();
575 
576  return qi;
577 }
578 
580 {
581  MSqlDatabase *db = GetMythDB()->GetDBManager()->getSchedCon();
582  MSqlQueryInfo qi;
583 
584  InitMSqlQueryInfo(qi);
585  qi.returnConnection = false;
586 
587  if (db)
588  {
589  qi.db = db;
590  qi.qsqldb = db->db();
591 
592  db->KickDatabase();
593  }
594 
595  return qi;
596 }
597 
599 {
600  MSqlDatabase *db = GetMythDB()->GetDBManager()->getChannelCon();
601  MSqlQueryInfo qi;
602 
603  InitMSqlQueryInfo(qi);
604  qi.returnConnection = false;
605 
606  if (db)
607  {
608  qi.db = db;
609  qi.qsqldb = db->db();
610 
611  db->KickDatabase();
612  }
613 
614  return qi;
615 }
616 
618 {
619  if (!m_db)
620  {
621  // Database structure's been deleted
622  return false;
623  }
624 
625  if (m_lastPreparedQuery.isEmpty())
626  {
627  LOG(VB_GENERAL, LOG_ERR,
628  "MSqlQuery::exec(void) called without a prepared query.");
629  return false;
630  }
631 
632 #if DEBUG_RECONNECT
633  if (rand_bool(50))
634  {
635  LOG(VB_GENERAL, LOG_INFO,
636  "MSqlQuery disconnecting DB to test reconnection logic");
637  m_db->m_db.close();
638  }
639 #endif
640 
641  // Database connection down. Try to restart it, give up if it's still
642  // down
643  if (!m_db->isOpen() && !Reconnect())
644  {
645  LOG(VB_GENERAL, LOG_INFO, "MySQL server disconnected");
646  return false;
647  }
648 
649  QElapsedTimer timer;
650  timer.start();
651 
652  bool result = QSqlQuery::exec();
653  qint64 elapsed = timer.elapsed();
654 
655  if (!result && lostConnectionCheck())
656  result = QSqlQuery::exec();
657 
658  if (!result)
659  {
660  QString err = MythDB::GetError("MSqlQuery", *this);
661 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
662  MSqlBindings tmp = QSqlQuery::boundValues();
663 #else
664  QVariantList tmp = QSqlQuery::boundValues();
665 #endif
666  bool has_null_strings = false;
667  // NOLINTNEXTLINE(modernize-loop-convert)
668  for (auto it = tmp.begin(); it != tmp.end(); ++it)
669  {
670 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
671  auto type = static_cast<QMetaType::Type>(it->type());
672 #else
673  auto type = it->typeId();
674 #endif
675  if (type != QMetaType::QString)
676  continue;
677  if (it->isNull() || it->toString().isNull())
678  {
679  has_null_strings = true;
680  *it = QVariant(QString(""));
681  }
682  }
683  if (has_null_strings)
684  {
685 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
686  bindValues(tmp);
687 #else
688  for (int i = 0; i < static_cast<int>(tmp.size()); i++)
689  QSqlQuery::bindValue(i, tmp.at(i));
690 #endif
691  timer.restart();
692  result = QSqlQuery::exec();
693  elapsed = timer.elapsed();
694  }
695  if (result)
696  {
697  LOG(VB_GENERAL, LOG_ERR,
698  QString("Original query failed, but resend with empty "
699  "strings in place of NULL strings worked. ") +
700  "\n" + err);
701  }
702  }
703 
704  if (VERBOSE_LEVEL_CHECK(VB_DATABASE, LOG_INFO))
705  {
706  QString str = lastQuery();
707 
708  // Sadly, neither executedQuery() nor lastQuery() display
709  // the values in bound queries against a MySQL5 database.
710  // So, replace the named placeholders with their values.
711 
712 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
713  QMapIterator<QString, QVariant> b = boundValues();
714  while (b.hasNext())
715  {
716  b.next();
717  str.replace(b.key(), '\'' + b.value().toString() + '\'');
718  }
719 #else
720  QVariantList b = boundValues();
721  static const QRegularExpression placeholders { "(:\\w+)" };
722  auto match = placeholders.match(str);
723  while (match.hasMatch())
724  {
725  str.replace(match.capturedStart(), match.capturedLength(),
726  b.isEmpty()
727  ? "\'INVALID\'"
728  : '\'' + b.takeFirst().toString() + '\'');
729  match = placeholders.match(str);
730  }
731 #endif
732 
733  LOG(VB_DATABASE, LOG_INFO,
734  QString("MSqlQuery::exec(%1) %2%3%4")
735  .arg(m_db->MSqlDatabase::GetConnectionName(), str,
736  QString(" <<<< Took %1ms").arg(QString::number(elapsed)),
737  isSelect()
738  ? QString(", Returned %1 row(s)").arg(size())
739  : QString()));
740  }
741 
742  return result;
743 }
744 
745 bool MSqlQuery::exec(const QString &query)
746 {
747  if (!m_db)
748  {
749  // Database structure's been deleted
750  return false;
751  }
752 
753  // Database connection down. Try to restart it, give up if it's still
754  // down
755  if (!m_db->isOpen() && !Reconnect())
756  {
757  LOG(VB_GENERAL, LOG_INFO, "MySQL server disconnected");
758  return false;
759  }
760 
761  bool result = QSqlQuery::exec(query);
762 
763  if (!result && lostConnectionCheck())
764  result = QSqlQuery::exec(query);
765 
766  LOG(VB_DATABASE, LOG_INFO,
767  QString("MSqlQuery::exec(%1) %2%3")
768  .arg(m_db->MSqlDatabase::GetConnectionName(), query,
769  isSelect()
770  ? QString(" <<<< Returns %1 row(s)").arg(size())
771  : QString()));
772 
773  return result;
774 }
775 
776 bool MSqlQuery::seekDebug(const char *type, bool result,
777  int where, bool relative) const
778 {
779  if (result && VERBOSE_LEVEL_CHECK(VB_DATABASE, LOG_DEBUG))
780  {
781  QString str;
782  QSqlRecord rec = record();
783 
784  for (int i = 0; i < rec.count(); i++)
785  {
786  if (!str.isEmpty())
787  str.append(", ");
788 
789  str.append(rec.fieldName(i) + " = " +
790  value(i).toString());
791  }
792 
793  if (QString("seek")==type)
794  {
795  LOG(VB_DATABASE, LOG_DEBUG,
796  QString("MSqlQuery::seek(%1,%2,%3) Result: \"%4\"")
797  .arg(m_db->MSqlDatabase::GetConnectionName())
798  .arg(where).arg(relative)
799  .arg(str));
800  }
801  else
802  {
803  LOG(VB_DATABASE, LOG_DEBUG,
804  QString("MSqlQuery::%1(%2) Result: \"%3\"")
805  .arg(type, m_db->MSqlDatabase::GetConnectionName(), str));
806  }
807  }
808  return result;
809 }
810 
811 bool MSqlQuery::next(void)
812 {
813  return seekDebug("next", QSqlQuery::next(), 0, false);
814 }
815 
817 {
818  return seekDebug("previous", QSqlQuery::previous(), 0, false);
819 }
820 
822 {
823  return seekDebug("first", QSqlQuery::first(), 0, false);
824 }
825 
826 bool MSqlQuery::last(void)
827 {
828  return seekDebug("last", QSqlQuery::last(), 0, false);
829 }
830 
831 bool MSqlQuery::seek(int where, bool relative)
832 {
833  return seekDebug("seek", QSqlQuery::seek(where, relative), where, relative);
834 }
835 
836 bool MSqlQuery::prepare(const QString& query)
837 {
838  if (!m_db)
839  {
840  // Database structure's been deleted
841  return false;
842  }
843 
844  m_lastPreparedQuery = query;
845 
846  if (!m_db->isOpen() && !Reconnect())
847  {
848  LOG(VB_GENERAL, LOG_INFO, "MySQL server disconnected");
849  return false;
850  }
851 
852  // QT docs indicate that there are significant speed ups and a reduction
853  // in memory usage by enabling forward-only cursors
854  //
855  // Unconditionally enable this since all existing uses of the database
856  // iterate forward over the result set.
857  setForwardOnly(true);
858 
859  bool ok = QSqlQuery::prepare(query);
860 
861  if (!ok && lostConnectionCheck())
862  ok = true;
863 
864  if (!ok && !(GetMythDB()->SuppressDBMessages()))
865  {
866  LOG(VB_GENERAL, LOG_ERR,
867  QString("Error preparing query: %1").arg(query));
868  LOG(VB_GENERAL, LOG_ERR,
869  MythDB::DBErrorMessage(QSqlQuery::lastError()));
870  }
871 
872  return ok;
873 }
874 
876 {
877  MSqlDatabase *db = GetMythDB()->GetDBManager()->popConnection(true);
878 
879  // popConnection() has already called OpenDatabase(),
880  // so we only have to check if it was successful:
881  bool isOpen = db->isOpen();
882 
883  GetMythDB()->GetDBManager()->pushConnection(db);
884  return isOpen;
885 }
886 
887 void MSqlQuery::bindValue(const QString &placeholder, const QVariant &val)
888 {
889 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
890  if (static_cast<QMetaType::Type>(val.type()) == QMetaType::QDateTime)
891  {
892  QSqlQuery::bindValue(placeholder,
893  MythDate::toString(val.toDateTime(), MythDate::kDatabase),
894  QSql::In);
895  return;
896  }
897 #endif
898  QSqlQuery::bindValue(placeholder, val, QSql::In);
899 }
900 
901 void MSqlQuery::bindValueNoNull(const QString &placeholder, const QVariant &val)
902 {
903 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
904  auto type = static_cast<QMetaType::Type>(val.type());
905 #else
906  auto type = val.typeId();
907 #endif
908  if (type == QMetaType::QString && val.toString().isNull())
909  {
910  QSqlQuery::bindValue(placeholder, QString(""), QSql::In);
911  return;
912  }
913 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
914  if (type == QMetaType::QDateTime)
915  {
916  QSqlQuery::bindValue(placeholder,
917  MythDate::toString(val.toDateTime(), MythDate::kDatabase),
918  QSql::In);
919  return;
920  }
921 #endif
922  QSqlQuery::bindValue(placeholder, val, QSql::In);
923 }
924 
925 void MSqlQuery::bindValues(const MSqlBindings &bindings)
926 {
927  MSqlBindings::const_iterator it;
928  for (it = bindings.begin(); it != bindings.end(); ++it)
929  {
930  bindValue(it.key(), it.value());
931  }
932 }
933 
935 {
936  return QSqlQuery::lastInsertId();
937 }
938 
940 {
941  if (!m_db->Reconnect())
942  return false;
943  if (!m_lastPreparedQuery.isEmpty())
944  {
945 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
946  MSqlBindings tmp = QSqlQuery::boundValues();
947  if (!QSqlQuery::prepare(m_lastPreparedQuery))
948  return false;
949  bindValues(tmp);
950 #else
951  QVariantList tmp = QSqlQuery::boundValues();
952  if (!QSqlQuery::prepare(m_lastPreparedQuery))
953  return false;
954  for (int i = 0; i < static_cast<int>(tmp.size()); i++)
955  QSqlQuery::bindValue(i, tmp.at(i));
956 #endif
957  }
958  return true;
959 }
960 
962 {
963  // MySQL: Error number: 2006; Symbol: CR_SERVER_GONE_ERROR
964  // MySQL: Error number: 2013; Symbol: CR_SERVER_LOST
965  // MySQL: Error number: 4031; Symbol: ER_CLIENT_INTERACTION_TIMEOUT
966  // Note: In MariaDB, 4031 = ER_REFERENCED_TRG_DOES_NOT_EXIST
967 
968  static QStringList kLostConnectionCodes = { "2006", "2013", "4031" };
969 
970  QString error_code = QSqlQuery::lastError().nativeErrorCode();
971 
972  // Make capturing of new 'lost connection' like error codes easy.
973  LOG(VB_GENERAL, LOG_DEBUG, QString("SQL Native Error Code: %1")
974  .arg(error_code));
975 
976  // If the query failed with any of the error codes that say the server
977  // is gone, close and reopen the database connection.
978  return (kLostConnectionCodes.contains(error_code) && Reconnect());
979 
980 }
981 
983 {
984  MSqlBindings::Iterator it;
985  for (it = addfrom.begin(); it != addfrom.end(); ++it)
986  {
987  output.insert(it.key(), it.value());
988  }
989 }
990 
991 struct Holder {
992  explicit Holder( QString hldr = QString(), int pos = -1 )
993  : m_holderName(std::move( hldr )), m_holderPos( pos ) {}
994 
995  bool operator==( const Holder& h ) const
996  { return h.m_holderPos == m_holderPos && h.m_holderName == m_holderName; }
997  bool operator!=( const Holder& h ) const
998  { return h.m_holderPos != m_holderPos || h.m_holderName != m_holderName; }
999  QString m_holderName;
1001 };
1002 
1003 void MSqlEscapeAsAQuery(QString &query, const MSqlBindings &bindings)
1004 {
1005  MSqlQuery result(MSqlQuery::InitCon());
1006 
1007  static const QRegularExpression rx { "('[^']+'|:\\w+)",
1008  QRegularExpression::UseUnicodePropertiesOption};
1009 
1010  QVector<Holder> holders;
1011 
1012  auto matchIter = rx.globalMatch(query);
1013  while (matchIter.hasNext())
1014  {
1015  auto match = matchIter.next();
1016  if (match.capturedLength(1) > 0)
1017  holders.append(Holder(match.captured(), match.capturedStart()));
1018  }
1019 
1020  QVariant val;
1021  QString holder;
1022 
1023  for (int i = holders.count() - 1; i >= 0; --i)
1024  {
1025  holder = holders[(uint)i].m_holderName;
1026  val = bindings[holder];
1027 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
1028  QSqlField f("", val.type());
1029 #else
1030  QSqlField f("", val.metaType());
1031 #endif
1032  if (val.isNull())
1033  f.clear();
1034  else
1035  f.setValue(val);
1036 
1037  query = query.replace((uint)holders[(uint)i].m_holderPos, holder.length(),
1038  result.driver()->formatValue(f));
1039  }
1040 }
MSqlBindings
QMap< QString, QVariant > MSqlBindings
typedef for a map of string -> string bindings for generic queries.
Definition: mythdbcon.h:101
MSqlQuery::next
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
Definition: mythdbcon.cpp:811
MSqlQuery
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:128
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:901
MSqlQuery::size
int size(void) const
Definition: mythdbcon.h:215
MDBManager::m_lock
QMutex m_lock
Definition: mythdbcon.h:76
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:527
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:472
DatabaseParams
Structure containing the basic Database parameters.
Definition: mythdbparams.h:10
Holder::operator==
bool operator==(const Holder &h) const
Definition: mythdbcon.cpp:995
MSqlQuery::m_db
MSqlDatabase * m_db
Definition: mythdbcon.h:252
MDBManager::m_inuseCount
QHash< QThread *, int > m_inuseCount
Definition: mythdbcon.h:81
MSqlQuery::record
QSqlRecord record(void) const
Definition: mythdbcon.h:217
MSqlQuery::bindValues
void bindValues(const MSqlBindings &bindings)
Add all the bindings in the passed in bindings.
Definition: mythdbcon.cpp:925
MSqlAddMoreBindings
void MSqlAddMoreBindings(MSqlBindings &output, MSqlBindings &addfrom)
Add the entries in addfrom to the map in output.
Definition: mythdbcon.cpp:982
MDBManager
DB connection pool, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:55
MSqlQuery::lastInsertId
QVariant lastInsertId()
Return the id of the last inserted row.
Definition: mythdbcon.cpp:934
MSqlDatabase::KickDatabase
bool KickDatabase(void)
Definition: mythdbcon.cpp:254
MDBManager::m_nextConnID
int m_nextConnID
Definition: mythdbcon.h:84
Holder::m_holderPos
int m_holderPos
Definition: mythdbcon.cpp:1000
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:205
mythdbcon.h
MSqlDatabase::db
QSqlDatabase db(void) const
Definition: mythdbcon.h:42
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:387
MSqlQuery::exec
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:617
MythCoreContext::IsUIThread
bool IsUIThread(void)
Definition: mythcorecontext.cpp:1347
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:992
GetMythDB
MythDB * GetMythDB(void)
Definition: mythdb.cpp:50
MDBManager::getChannelCon
MSqlDatabase * getChannelCon(void)
Definition: mythdbcon.cpp:477
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:991
mythsystemlegacy.h
MSqlDatabase::isOpen
bool isOpen(void)
Definition: mythdbcon.cpp:122
InitMSqlQueryInfo
static void InitMSqlQueryInfo(MSqlQueryInfo &qi)
Definition: mythdbcon.cpp:519
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:816
MDBManager::DBList
QList< MSqlDatabase * > DBList
Definition: mythdbcon.h:77
mythdate.h
MSqlQuery::Reconnect
bool Reconnect(void)
Reconnects server and re-prepares and re-binds the last prepared query.
Definition: mythdbcon.cpp:939
MDBManager::m_connCount
int m_connCount
Definition: mythdbcon.h:85
MDBManager::CloseDatabases
void CloseDatabases(void)
Definition: mythdbcon.cpp:482
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:93
MSqlQuery::first
bool first(void)
Wrap QSqlQuery::first() so we can display the query results.
Definition: mythdbcon.cpp:821
MythDB::GetError
static QString GetError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:195
MDBManager::~MDBManager
~MDBManager(void)
Definition: mythdbcon.cpp:291
MDBManager::m_pool
QHash< QThread *, DBList > m_pool
Definition: mythdbcon.h:78
MSqlDatabase::m_driver
QString m_driver
Definition: mythdbcon.h:48
MSqlQuery::InitCon
static MSqlQueryInfo InitCon(ConnectionReuse _reuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:549
compat.h
MSqlDatabase::Reconnect
bool Reconnect(void)
Definition: mythdbcon.cpp:264
MDBManager::m_inuse
QHash< QThread *, MSqlDatabase * > m_inuse
Definition: mythdbcon.h:80
MSqlQuery::testDBConnection
static bool testDBConnection()
Checks DB connection + login (login info via Mythcontext)
Definition: mythdbcon.cpp:875
MDBManager::m_staticPool
QHash< QThread *, DBList > m_staticPool
Definition: mythdbcon.h:89
MSqlQueryInfo::db
MSqlDatabase * db
Definition: mythdbcon.h:95
MSqlQuery::boundValues
QVariantList boundValues(void) const
Definition: mythdbcon.h:212
MythCoreContext::GetDB
MythDB * GetDB(void)
Definition: mythcorecontext.cpp:1756
MSqlQuery::lostConnectionCheck
bool lostConnectionCheck(void)
lostConnectionCheck tests for SQL error codes that indicate the connection to the server has been los...
Definition: mythdbcon.cpp:961
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:49
MSqlQuery::SchedCon
static MSqlQueryInfo SchedCon()
Returns dedicated connection. (Required for using temporary SQL tables.)
Definition: mythdbcon.cpp:579
MSqlQuery::setForwardOnly
void setForwardOnly(bool f)
Definition: mythdbcon.h:219
MSqlQuery::kNormalConnection
@ kNormalConnection
Definition: mythdbcon.h:230
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:831
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:1003
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:453
MDBManager::popConnection
MSqlDatabase * popConnection(bool reuse)
Definition: mythdbcon.cpp:307
MSqlQuery::m_lastPreparedQuery
QString m_lastPreparedQuery
Definition: mythdbcon.h:255
MSqlDatabase
QSqlDatabase wrapper, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:26
MSqlQuery::ChannelCon
static MSqlQueryInfo ChannelCon()
Returns dedicated connection. (Required for using temporary SQL tables.)
Definition: mythdbcon.cpp:598
MSqlQueryInfo::returnConnection
bool returnConnection
Definition: mythdbcon.h:97
MSqlQuery::~MSqlQuery
~MSqlQuery()
Returns connection to pool.
Definition: mythdbcon.cpp:536
mythmiscutil.h
MSqlDatabase::m_lastDBKick
QDateTime m_lastDBKick
Definition: mythdbcon.h:50
Holder::m_holderName
QString m_holderName
Definition: mythdbcon.cpp:999
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:887
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:359
Holder::operator!=
bool operator!=(const Holder &h) const
Definition: mythdbcon.cpp:997
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:47
MSqlQuery::seekDebug
bool seekDebug(const char *type, bool result, int where, bool relative) const
Definition: mythdbcon.cpp:776
MSqlQueryInfo::qsqldb
QSqlDatabase qsqldb
Definition: mythdbcon.h:96
MythCoreContext::IsWOLAllowed
bool IsWOLAllowed() const
Definition: mythcorecontext.cpp:633
MDBManager::m_channelCon
MSqlDatabase * m_channelCon
Definition: mythdbcon.h:88
DatabaseParams::m_wolEnabled
bool m_wolEnabled
true if wake-on-lan params are used
Definition: mythdbparams.h:34
MSqlQuery::ConnectionReuse
ConnectionReuse
Definition: mythdbcon.h:227
MDBManager::m_schedCon
MSqlDatabase * m_schedCon
Definition: mythdbcon.h:87
exitcodes.h
MSqlQuery::m_isConnected
bool m_isConnected
Definition: mythdbcon.h:253
output
#define output
Definition: synaesthesia.cpp:220
MSqlQuery::m_returnConnection
bool m_returnConnection
Definition: mythdbcon.h:254
MSqlQuery::last
bool last(void)
Wrap QSqlQuery::last() so we can display the query results.
Definition: mythdbcon.cpp:826
MSqlDatabase::OpenDatabase
bool OpenDatabase(bool skipdb=false)
Definition: mythdbcon.cpp:132
MSqlQuery::driver
const QSqlDriver * driver(void) const
Definition: mythdbcon.h:221
MSqlQuery::prepare
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.
Definition: mythdbcon.cpp:836
MSqlDatabase::m_dbparms
DatabaseParams m_dbparms
Definition: mythdbcon.h:51
GENERIC_EXIT_DB_ERROR
@ GENERIC_EXIT_DB_ERROR
Database error.
Definition: exitcodes.h:18