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