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