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)
204  .arg(m_db.databaseName()).arg(m_db.hostName()));
205 
206  InitSessionVars();
207 
208  // WriteDelayed depends on SetHaveDBConnection() and SetHaveSchema()
209  // both being called with true, so order is important here.
210  GetMythDB()->SetHaveDBConnection(true);
211  if (!GetMythDB()->HaveSchema())
212  {
213  // We can't just check the count of QSqlDatabase::tables()
214  // because it returns all tables visible to the user in *all*
215  // databases (not just the current DB).
216  bool have_schema = false;
217  QString sql = "SELECT COUNT(TABLE_NAME) "
218  " FROM INFORMATION_SCHEMA.TABLES "
219  " WHERE TABLE_SCHEMA = DATABASE() "
220  " AND TABLE_TYPE = 'BASE TABLE';";
221  // We can't use MSqlQuery to determine if we have a schema,
222  // since it will open a new connection, which will try to check
223  // if we have a schema
224  QSqlQuery query = m_db.exec(sql); // don't convert to MSqlQuery
225  if (query.next())
226  have_schema = query.value(0).toInt() > 1;
227  GetMythDB()->SetHaveSchema(have_schema);
228  }
229  GetMythDB()->WriteDelayedSettings();
230  }
231  }
232 
233  if (!connected)
234  {
235  GetMythDB()->SetHaveDBConnection(false);
236  LOG(VB_GENERAL, LOG_ERR, QString("[%1] Unable to connect to database!").arg(m_name));
237  LOG(VB_GENERAL, LOG_ERR, MythDB::DBErrorMessage(m_db.lastError()));
238  }
239 
240  return connected;
241 }
242 
244 {
245  m_lastDBKick = MythDate::current().addSecs(-60);
246 
247  if (!m_db.isOpen())
248  m_db.open();
249 
250  return m_db.isOpen();
251 }
252 
254 {
255  m_db.close();
256  m_db.open();
257 
258  bool open = m_db.isOpen();
259  if (open)
260  {
261  LOG(VB_GENERAL, LOG_INFO, "MySQL reconnected successfully");
262  InitSessionVars();
263  }
264 
265  return open;
266 }
267 
269 {
270  // Make sure NOW() returns time in UTC...
271  m_db.exec("SET @@session.time_zone='+00:00'");
272  // Disable strict mode
273  m_db.exec("SET @@session.sql_mode=''");
274 }
275 
276 // -----------------------------------------------------------------------
277 
278 
279 
281 {
282  CloseDatabases();
283 
284  if (m_connCount != 0 || m_schedCon || m_channelCon)
285  {
286  LOG(VB_GENERAL, LOG_CRIT,
287  "MDBManager exiting with connections still open");
288  }
289 #if 0 /* some post logStop() debugging... */
290  cout<<"m_connCount: "<<m_connCount<<endl;
291  cout<<"m_schedCon: "<<m_schedCon<<endl;
292  cout<<"m_channelCon: "<<m_channelCon<<endl;
293 #endif
294 }
295 
297 {
298  PurgeIdleConnections(true);
299 
300  m_lock.lock();
301 
302  MSqlDatabase *db = nullptr;
303 
304 #if REUSE_CONNECTION
305  if (reuse)
306  {
307  db = m_inuse[QThread::currentThread()];
308  if (db != nullptr)
309  {
310  m_inuseCount[QThread::currentThread()]++;
311  m_lock.unlock();
312  return db;
313  }
314  }
315 #endif
316 
317  DBList &list = m_pool[QThread::currentThread()];
318  if (list.isEmpty())
319  {
320  db = new MSqlDatabase("DBManager" + QString::number(m_nextConnID++));
321  ++m_connCount;
322  LOG(VB_DATABASE, LOG_INFO,
323  QString("New DB connection, total: %1").arg(m_connCount));
324  }
325  else
326  {
327  db = list.back();
328  list.pop_back();
329  }
330 
331 #if REUSE_CONNECTION
332  if (reuse)
333  {
334  m_inuseCount[QThread::currentThread()]=1;
335  m_inuse[QThread::currentThread()] = db;
336  }
337 #endif
338 
339  m_lock.unlock();
340 
341  db->OpenDatabase();
342 
343  return db;
344 }
345 
347 {
348  m_lock.lock();
349 
350 #if REUSE_CONNECTION
351  if (db == m_inuse[QThread::currentThread()])
352  {
353  int cnt = --m_inuseCount[QThread::currentThread()];
354  if (cnt > 0)
355  {
356  m_lock.unlock();
357  return;
358  }
359  m_inuse[QThread::currentThread()] = nullptr;
360  }
361 #endif
362 
363  if (db)
364  {
366  m_pool[QThread::currentThread()].push_front(db);
367  }
368 
369  m_lock.unlock();
370 
371  PurgeIdleConnections(true);
372 }
373 
375 {
376  QMutexLocker locker(&m_lock);
377 
378  leaveOne = leaveOne || (gCoreContext && gCoreContext->IsUIThread());
379 
380  QDateTime now = MythDate::current();
381  DBList &list = m_pool[QThread::currentThread()];
382  DBList::iterator it = list.begin();
383 
384  uint purgedConnections = 0;
385  uint totalConnections = 0;
386  MSqlDatabase *newDb = nullptr;
387  while (it != list.end())
388  {
389  totalConnections++;
390  if ((*it)->m_lastDBKick.secsTo(now) <= kPurgeTimeout.count())
391  {
392  ++it;
393  continue;
394  }
395 
396  // This connection has not been used in the kPurgeTimeout
397  // seconds close it.
398  MSqlDatabase *entry = *it;
399  it = list.erase(it);
400  --m_connCount;
401  purgedConnections++;
402 
403  // Qt's MySQL driver apparently keeps track of the number of
404  // open DB connections, and when it hits 0, calls
405  // my_thread_global_end(). The mysql library then assumes the
406  // application is ending and that all threads that created DB
407  // connections have already exited. This is rarely true, and
408  // may result in the mysql library pausing 5 seconds and
409  // printing a message like "Error in my_thread_global_end(): 1
410  // threads didn't exit". This workaround simply creates an
411  // extra DB connection before all pooled connections are
412  // purged so that my_thread_global_end() won't be called.
413  if (leaveOne && it == list.end() &&
414  purgedConnections > 0 &&
415  totalConnections == purgedConnections)
416  {
417  newDb = new MSqlDatabase("DBManager" +
418  QString::number(m_nextConnID++));
419  ++m_connCount;
420  LOG(VB_GENERAL, LOG_INFO,
421  QString("New DB connection, total: %1").arg(m_connCount));
422  newDb->m_lastDBKick = MythDate::current();
423  }
424 
425  LOG(VB_DATABASE, LOG_INFO, "Deleting idle DB connection...");
426  delete entry;
427  LOG(VB_DATABASE, LOG_INFO, "Done deleting idle DB connection.");
428  }
429  if (newDb)
430  list.push_front(newDb);
431 
432  if (purgedConnections)
433  {
434  LOG(VB_DATABASE, LOG_INFO,
435  QString("Purged %1 idle of %2 total DB connections.")
436  .arg(purgedConnections).arg(totalConnections));
437  }
438 }
439 
440 MSqlDatabase *MDBManager::getStaticCon(MSqlDatabase **dbcon, const QString& name)
441 {
442  if (!dbcon)
443  return nullptr;
444 
445  if (!*dbcon)
446  {
447  *dbcon = new MSqlDatabase(name);
448  LOG(VB_GENERAL, LOG_INFO, "New static DB connection" + name);
449  }
450 
451  (*dbcon)->OpenDatabase();
452 
453  if (!m_staticPool[QThread::currentThread()].contains(*dbcon))
454  m_staticPool[QThread::currentThread()].push_back(*dbcon);
455 
456  return *dbcon;
457 }
458 
460 {
461  return getStaticCon(&m_schedCon, "SchedCon");
462 }
463 
465 {
466  return getStaticCon(&m_channelCon, "ChannelCon");
467 }
468 
470 {
471  m_lock.lock();
472  DBList list = m_pool[QThread::currentThread()];
473  m_pool[QThread::currentThread()].clear();
474  m_lock.unlock();
475 
476  for (auto *conn : qAsConst(list))
477  {
478  LOG(VB_DATABASE, LOG_INFO,
479  "Closing DB connection named '" + conn->m_name + "'");
480  conn->m_db.close();
481  delete conn;
482  m_connCount--;
483  }
484 
485  m_lock.lock();
486  DBList &slist = m_staticPool[QThread::currentThread()];
487  while (!slist.isEmpty())
488  {
489  MSqlDatabase *db = slist.takeFirst();
490  LOG(VB_DATABASE, LOG_INFO,
491  "Closing DB connection named '" + db->m_name + "'");
492  db->m_db.close();
493  delete db;
494 
495  if (db == m_schedCon)
496  m_schedCon = nullptr;
497  if (db == m_channelCon)
498  m_channelCon = nullptr;
499  }
500  m_lock.unlock();
501 }
502 
503 
504 // -----------------------------------------------------------------------
505 
507 {
508  qi.db = nullptr;
509  qi.qsqldb = QSqlDatabase();
510  qi.returnConnection = true;
511 }
512 
513 
515  : QSqlQuery(QString(), qi.qsqldb)
516 {
517  m_db = qi.db;
519 
520  m_isConnected = m_db && m_db->isOpen();
521 }
522 
524 {
525  if (m_returnConnection)
526  {
527  MDBManager *dbmanager = GetMythDB()->GetDBManager();
528 
529  if (dbmanager && m_db)
530  {
531  dbmanager->pushConnection(m_db);
532  }
533  }
534 }
535 
537 {
538  bool reuse = kNormalConnection == _reuse;
539  MSqlDatabase *db = GetMythDB()->GetDBManager()->popConnection(reuse);
540  MSqlQueryInfo qi;
541 
542  InitMSqlQueryInfo(qi);
543 
544 
545  // Bootstrapping without a database?
546  //if (db->pretendHaveDB)
547  if (db->m_db.hostName().isEmpty())
548  {
549  // Return an invalid database so that QSqlQuery does nothing.
550  // Also works around a Qt4 bug where QSqlQuery::~QSqlQuery
551  // calls QMYSQLResult::cleanup() which uses mysql_next_result()
552 
553  GetMythDB()->GetDBManager()->pushConnection(db);
554  qi.returnConnection = false;
555  return qi;
556  }
557 
558  qi.db = db;
559  qi.qsqldb = db->db();
560 
561  db->KickDatabase();
562 
563  return qi;
564 }
565 
567 {
568  MSqlDatabase *db = GetMythDB()->GetDBManager()->getSchedCon();
569  MSqlQueryInfo qi;
570 
571  InitMSqlQueryInfo(qi);
572  qi.returnConnection = false;
573 
574  if (db)
575  {
576  qi.db = db;
577  qi.qsqldb = db->db();
578 
579  db->KickDatabase();
580  }
581 
582  return qi;
583 }
584 
586 {
587  MSqlDatabase *db = GetMythDB()->GetDBManager()->getChannelCon();
588  MSqlQueryInfo qi;
589 
590  InitMSqlQueryInfo(qi);
591  qi.returnConnection = false;
592 
593  if (db)
594  {
595  qi.db = db;
596  qi.qsqldb = db->db();
597 
598  db->KickDatabase();
599  }
600 
601  return qi;
602 }
603 
605 {
606  if (!m_db)
607  {
608  // Database structure's been deleted
609  return false;
610  }
611 
612  if (m_lastPreparedQuery.isEmpty())
613  {
614  LOG(VB_GENERAL, LOG_ERR,
615  "MSqlQuery::exec(void) called without a prepared query.");
616  return false;
617  }
618 
619 #if DEBUG_RECONNECT
620  if (random() < RAND_MAX / 50)
621  {
622  LOG(VB_GENERAL, LOG_INFO,
623  "MSqlQuery disconnecting DB to test reconnection logic");
624  m_db->m_db.close();
625  }
626 #endif
627 
628  // Database connection down. Try to restart it, give up if it's still
629  // down
630  if (!m_db->isOpen() && !Reconnect())
631  {
632  LOG(VB_GENERAL, LOG_INFO, "MySQL server disconnected");
633  return false;
634  }
635 
636  QElapsedTimer timer;
637  timer.start();
638 
639  bool result = QSqlQuery::exec();
640  qint64 elapsed = timer.elapsed();
641 
642  // if the query failed with "MySQL server has gone away"
643  // Close and reopen the database connection and retry the query if it
644  // connects again
645  if (!result
646  && QSqlQuery::lastError().nativeErrorCode() == "2006"
647  && Reconnect())
648  result = QSqlQuery::exec();
649 
650  if (!result)
651  {
652  QString err = MythDB::GetError("MSqlQuery", *this);
653 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
654  MSqlBindings tmp = QSqlQuery::boundValues();
655 #else
656  QVariantList tmp = QSqlQuery::boundValues();
657 #endif
658  bool has_null_strings = false;
659  // NOLINTNEXTLINE(modernize-loop-convert)
660  for (auto it = tmp.begin(); it != tmp.end(); ++it)
661  {
662  if (it->type() != QVariant::String)
663  continue;
664  if (it->isNull() || it->toString().isNull())
665  {
666  has_null_strings = true;
667  *it = QVariant(QString(""));
668  }
669  }
670  if (has_null_strings)
671  {
672 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
673  bindValues(tmp);
674 #else
675  for (int i = 0; i < static_cast<int>(tmp.size()); i++)
676  QSqlQuery::bindValue(i, tmp.at(i));
677 #endif
678  timer.restart();
679  result = QSqlQuery::exec();
680  elapsed = timer.elapsed();
681  }
682  if (result)
683  {
684  LOG(VB_GENERAL, LOG_ERR,
685  QString("Original query failed, but resend with empty "
686  "strings in place of NULL strings worked. ") +
687  "\n" + err);
688  }
689  }
690 
691  if (VERBOSE_LEVEL_CHECK(VB_DATABASE, LOG_INFO))
692  {
693  QString str = lastQuery();
694 
695  // Database logging will cause an infinite loop here if not filtered
696  // out
697  if (!str.startsWith("INSERT INTO logging "))
698  {
699  // Sadly, neither executedQuery() nor lastQuery() display
700  // the values in bound queries against a MySQL5 database.
701  // So, replace the named placeholders with their values.
702 
703 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
704  QMapIterator<QString, QVariant> b = boundValues();
705  while (b.hasNext())
706  {
707  b.next();
708  str.replace(b.key(), '\'' + b.value().toString() + '\'');
709  }
710 #else
711  QVariantList b = boundValues();
712  static const QRegularExpression placeholders { "(:\\w+)" };
713  auto match = placeholders.match(str);
714  while (match.hasMatch())
715  {
716  str.replace(match.capturedStart(), match.capturedLength(),
717  b.isEmpty()
718  ? "\'INVALID\'"
719  : '\'' + b.takeFirst().toString() + '\'');
720  match = placeholders.match(str);
721  }
722 #endif
723 
724  LOG(VB_DATABASE, LOG_INFO,
725  QString("MSqlQuery::exec(%1) %2%3%4")
726  .arg(m_db->MSqlDatabase::GetConnectionName()).arg(str)
727  .arg(QString(" <<<< Took %1ms").arg(QString::number(elapsed)))
728  .arg(isSelect() ? QString(", Returned %1 row(s)")
729  .arg(size()) : 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 the query failed with "MySQL server has gone away"
755  // Close and reopen the database connection and retry the query if it
756  // connects again
757  if (!result
758  && QSqlQuery::lastError().nativeErrorCode() == "2006"
759  && Reconnect())
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()).arg(query)
765  .arg(isSelect() ? QString(" <<<< Returns %1 row(s)")
766  .arg(size()) : 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).arg(m_db->MSqlDatabase::GetConnectionName())
801  .arg(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 the prepare failed with "MySQL server has gone away"
858  // Close and reopen the database connection and retry the query if it
859  // connects again
860  if (!ok
861  && QSqlQuery::lastError().nativeErrorCode() == "2006"
862  && Reconnect())
863  ok = true;
864 
865  if (!ok && !(GetMythDB()->SuppressDBMessages()))
866  {
867  LOG(VB_GENERAL, LOG_ERR,
868  QString("Error preparing query: %1").arg(query));
869  LOG(VB_GENERAL, LOG_ERR,
870  MythDB::DBErrorMessage(QSqlQuery::lastError()));
871  }
872 
873  return ok;
874 }
875 
877 {
878  MSqlDatabase *db = GetMythDB()->GetDBManager()->popConnection(true);
879 
880  // popConnection() has already called OpenDatabase(),
881  // so we only have to check if it was successful:
882  bool isOpen = db->isOpen();
883 
884  GetMythDB()->GetDBManager()->pushConnection(db);
885  return isOpen;
886 }
887 
888 void MSqlQuery::bindValue(const QString &placeholder, const QVariant &val)
889 {
890  QSqlQuery::bindValue(placeholder, val, QSql::In);
891 }
892 
893 void MSqlQuery::bindValueNoNull(const QString &placeholder, const QVariant &val)
894 {
895  if ((val.type() == QVariant::String) && val.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  MSqlBindings::Iterator it;
942  for (it = addfrom.begin(); it != addfrom.end(); ++it)
943  {
944  output.insert(it.key(), it.value());
945  }
946 }
947 
948 struct Holder {
949  explicit Holder( QString hldr = QString(), int pos = -1 )
950  : m_holderName(std::move( hldr )), m_holderPos( pos ) {}
951 
952  bool operator==( const Holder& h ) const
953  { return h.m_holderPos == m_holderPos && h.m_holderName == m_holderName; }
954  bool operator!=( const Holder& h ) const
955  { return h.m_holderPos != m_holderPos || h.m_holderName != m_holderName; }
956  QString m_holderName;
958 };
959 
960 void MSqlEscapeAsAQuery(QString &query, const MSqlBindings &bindings)
961 {
962  MSqlQuery result(MSqlQuery::InitCon());
963 
964  QRegularExpression rx { "('[^']+'|:\\w+)",
965  QRegularExpression::UseUnicodePropertiesOption};
966 
967  QVector<Holder> holders;
968 
969  auto matchIter = rx.globalMatch(query);
970  while (matchIter.hasNext())
971  {
972  auto match = matchIter.next();
973  if (match.capturedLength(1) > 0)
974  holders.append(Holder(match.captured(), match.capturedStart()));
975  }
976 
977  QVariant val;
978  QString holder;
979 
980  for (int i = holders.count() - 1; i >= 0; --i)
981  {
982  holder = holders[(uint)i].m_holderName;
983  val = bindings[holder];
984  QSqlField f("", val.type());
985  if (val.isNull())
986  f.clear();
987  else
988  f.setValue(val);
989 
990  query = query.replace((uint)holders[(uint)i].m_holderPos, holder.length(),
991  result.driver()->formatValue(f));
992  }
993 }
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:807
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:893
MSqlQuery::size
int size(void) const
Definition: mythdbcon.h:207
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:514
MSqlDatabase::InitSessionVars
void InitSessionVars(void)
Definition: mythdbcon.cpp:268
kPurgeTimeout
static constexpr std::chrono::seconds kPurgeTimeout
Definition: mythdbcon.cpp:36
mythdb.h
MythDB::DBErrorMessage
static QString DBErrorMessage(const QSqlError &err)
Definition: mythdb.cpp:199
MDBManager::getSchedCon
MSqlDatabase * getSchedCon(void)
Definition: mythdbcon.cpp:459
DatabaseParams
Structure containing the basic Database parameters.
Definition: mythdbparams.h:10
Holder::operator==
bool operator==(const Holder &h) const
Definition: mythdbcon.cpp:952
MSqlQuery::m_db
MSqlDatabase * m_db
Definition: mythdbcon.h:242
MDBManager::m_inuseCount
QHash< QThread *, int > m_inuseCount
Definition: mythdbcon.h:77
MSqlQuery::record
QSqlRecord record(void) const
Definition: mythdbcon.h:209
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:939
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:912
MSqlDatabase::KickDatabase
bool KickDatabase(void)
Definition: mythdbcon.cpp:243
MDBManager::m_nextConnID
int m_nextConnID
Definition: mythdbcon.h:80
Holder::m_holderPos
int m_holderPos
Definition: mythdbcon.cpp:957
MSqlQuery::value
QVariant value(int i) const
Definition: mythdbcon.h:197
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:374
MSqlQuery::exec
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:604
MythCoreContext::IsUIThread
bool IsUIThread(void)
Definition: mythcorecontext.cpp:1361
LOG
#define LOG(_MASK_, _LEVEL_, _QSTRING_)
Definition: mythlogging.h:23
Holder::Holder
Holder(QString hldr=QString(), int pos=-1)
Definition: mythdbcon.cpp:949
GetMythDB
MythDB * GetMythDB(void)
Definition: mythdb.cpp:46
MDBManager::getChannelCon
MSqlDatabase * getChannelCon(void)
Definition: mythdbcon.cpp:464
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:948
mythsystemlegacy.h
MSqlDatabase::isOpen
bool isOpen(void)
Definition: mythdbcon.cpp:112
sleep
unsigned sleep(unsigned int x)
Definition: compat.h:160
InitMSqlQueryInfo
static void InitMSqlQueryInfo(MSqlQueryInfo &qi)
Definition: mythdbcon.cpp:506
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:812
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:917
MDBManager::m_connCount
int m_connCount
Definition: mythdbcon.h:81
MDBManager::CloseDatabases
void CloseDatabases(void)
Definition: mythdbcon.cpp:469
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:817
MythDB::GetError
static QString GetError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:163
MDBManager::~MDBManager
~MDBManager(void)
Definition: mythdbcon.cpp:280
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:536
compat.h
MSqlDatabase::Reconnect
bool Reconnect(void)
Definition: mythdbcon.cpp:253
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:876
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:204
MythWakeup
bool MythWakeup(const QString &wakeUpCommand, uint flags, std::chrono::seconds timeout)
Definition: mythmiscutil.cpp:678
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:566
MSqlQuery::setForwardOnly
void setForwardOnly(bool f)
Definition: mythdbcon.h:211
MSqlQuery::kNormalConnection
@ kNormalConnection
Definition: mythdbcon.h:222
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: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:228
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:960
uint
unsigned int uint
Definition: compat.h:140
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:440
MDBManager::popConnection
MSqlDatabase * popConnection(bool reuse)
Definition: mythdbcon.cpp:296
MSqlQuery::m_lastPreparedQuery
QString m_lastPreparedQuery
Definition: mythdbcon.h:245
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:585
MSqlQueryInfo::returnConnection
bool returnConnection
Definition: mythdbcon.h:93
MSqlQuery::~MSqlQuery
~MSqlQuery()
Returns connection to pool.
Definition: mythdbcon.cpp:523
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:956
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:149
MSqlQuery::bindValue
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
Definition: mythdbcon.cpp:888
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:346
Holder::operator!=
bool operator!=(const Holder &h) const
Definition: mythdbcon.cpp:954
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:92
MythCoreContext::IsWOLAllowed
bool IsWOLAllowed() const
Definition: mythcorecontext.cpp:659
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:219
MDBManager::m_schedCon
MSqlDatabase * m_schedCon
Definition: mythdbcon.h:83
exitcodes.h
MSqlQuery::m_isConnected
bool m_isConnected
Definition: mythdbcon.h:243
output
#define output
Definition: synaesthesia.cpp:220
MSqlQuery::m_returnConnection
bool m_returnConnection
Definition: mythdbcon.h:244
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:122
MSqlQuery::driver
const QSqlDriver * driver(void) const
Definition: mythdbcon.h:213
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:48