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