From 5c588f89e79e02cba392abe3d00688772321f88b Mon Sep 17 00:00:00 2001 From: Loïc Blot Date: Wed, 15 Apr 2020 08:01:11 +0200 Subject: Verify database connection on interval (#9665) --- src/database/database-postgresql.cpp | 38 +++++++++++++++++++++--------------- src/database/database-postgresql.h | 8 ++++++-- src/database/database-sqlite3.cpp | 26 ++++++++++++------------ src/database/database-sqlite3.h | 12 +++++++++--- src/database/database.h | 4 ++++ 5 files changed, 54 insertions(+), 34 deletions(-) (limited to 'src/database') diff --git a/src/database/database-postgresql.cpp b/src/database/database-postgresql.cpp index 92816205c..c1b81586d 100644 --- a/src/database/database-postgresql.cpp +++ b/src/database/database-postgresql.cpp @@ -90,13 +90,19 @@ void Database_PostgreSQL::connectToDatabase() initStatements(); } -void Database_PostgreSQL::verifyDatabase() +void Database_PostgreSQL::pingDatabase() { - if (PQstatus(m_conn) == CONNECTION_OK) - return; - - PQreset(m_conn); - ping(); + // Verify DB connection with ping + try { + ping(); + } catch (const DatabaseException &e) { + // If ping failed, show the error and try reconnect + PQreset(m_conn); + + errorstream << e.what() << std::endl + << "Reconnecting to database " << m_connect_string << std::endl; + connectToDatabase(); + } } void Database_PostgreSQL::ping() @@ -151,7 +157,7 @@ void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name, void Database_PostgreSQL::beginSave() { - verifyDatabase(); + pingDatabase(); checkResults(PQexec(m_conn, "BEGIN;")); } @@ -227,7 +233,7 @@ bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data) return false; } - verifyDatabase(); + pingDatabase(); s32 x, y, z; x = htonl(pos.X); @@ -251,7 +257,7 @@ bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data) void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block) { - verifyDatabase(); + pingDatabase(); s32 x, y, z; x = htonl(pos.X); @@ -275,7 +281,7 @@ void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block) bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos) { - verifyDatabase(); + pingDatabase(); s32 x, y, z; x = htonl(pos.X); @@ -293,7 +299,7 @@ bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos) void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector &dst) { - verifyDatabase(); + pingDatabase(); PGresult *results = execPrepared("list_all_loadable_blocks", 0, NULL, NULL, NULL, false, false); @@ -435,7 +441,7 @@ void PlayerDatabasePostgreSQL::initStatements() bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername) { - verifyDatabase(); + pingDatabase(); const char *values[] = { playername.c_str() }; PGresult *results = execPrepared("load_player", 1, values, false); @@ -451,7 +457,7 @@ void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player) if (!sao) return; - verifyDatabase(); + pingDatabase(); v3f pos = sao->getBasePosition(); std::string pitch = ftos(sao->getLookPitch()); @@ -535,7 +541,7 @@ void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player) bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao) { sanity_check(sao); - verifyDatabase(); + pingDatabase(); const char *values[] = { player->getName() }; PGresult *results = execPrepared("load_player", 1, values, false, false); @@ -610,7 +616,7 @@ bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name) if (!playerDataExists(name)) return false; - verifyDatabase(); + pingDatabase(); const char *values[] = { name.c_str() }; execPrepared("remove_player", 1, values); @@ -620,7 +626,7 @@ bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name) void PlayerDatabasePostgreSQL::listPlayers(std::vector &res) { - verifyDatabase(); + pingDatabase(); PGresult *results = execPrepared("load_player_list", 0, NULL, false); diff --git a/src/database/database-postgresql.h b/src/database/database-postgresql.h index db0b505c9..5a8b89a51 100644 --- a/src/database/database-postgresql.h +++ b/src/database/database-postgresql.h @@ -32,12 +32,13 @@ public: Database_PostgreSQL(const std::string &connect_string); ~Database_PostgreSQL(); + virtual void pingDatabase(); + void beginSave(); void endSave(); bool initialized() const; - protected: // Conversion helpers inline int pg_to_int(PGresult *res, int row, int col) @@ -82,7 +83,6 @@ protected: } void createTableIfNotExists(const std::string &table_name, const std::string &definition); - void verifyDatabase(); // Database initialization void connectToDatabase(); @@ -113,6 +113,8 @@ public: MapDatabasePostgreSQL(const std::string &connect_string); virtual ~MapDatabasePostgreSQL() = default; + virtual void pingDatabase() { Database_PostgreSQL::pingDatabase(); } + bool saveBlock(const v3s16 &pos, const std::string &data); void loadBlock(const v3s16 &pos, std::string *block); bool deleteBlock(const v3s16 &pos); @@ -132,6 +134,8 @@ public: PlayerDatabasePostgreSQL(const std::string &connect_string); virtual ~PlayerDatabasePostgreSQL() = default; + virtual void pingDatabase() { Database_PostgreSQL::pingDatabase(); } + void savePlayer(RemotePlayer *player); bool loadPlayer(RemotePlayer *player, PlayerSAO *sao); bool removePlayer(const std::string &name); diff --git a/src/database/database-sqlite3.cpp b/src/database/database-sqlite3.cpp index 4560743b9..116096f68 100644 --- a/src/database/database-sqlite3.cpp +++ b/src/database/database-sqlite3.cpp @@ -121,7 +121,7 @@ Database_SQLite3::Database_SQLite3(const std::string &savedir, const std::string void Database_SQLite3::beginSave() { - verifyDatabase(); + pingDatabase(); SQLRES(sqlite3_step(m_stmt_begin), SQLITE_DONE, "Failed to start SQLite3 transaction"); sqlite3_reset(m_stmt_begin); @@ -129,7 +129,7 @@ void Database_SQLite3::beginSave() void Database_SQLite3::endSave() { - verifyDatabase(); + pingDatabase(); SQLRES(sqlite3_step(m_stmt_end), SQLITE_DONE, "Failed to commit SQLite3 transaction"); sqlite3_reset(m_stmt_end); @@ -171,7 +171,7 @@ void Database_SQLite3::openDatabase() "Failed to enable sqlite3 foreign key support"); } -void Database_SQLite3::verifyDatabase() +void Database_SQLite3::pingDatabase() { if (m_initialized) return; @@ -247,7 +247,7 @@ inline void MapDatabaseSQLite3::bindPos(sqlite3_stmt *stmt, const v3s16 &pos, in bool MapDatabaseSQLite3::deleteBlock(const v3s16 &pos) { - verifyDatabase(); + pingDatabase(); bindPos(m_stmt_delete, pos); @@ -263,7 +263,7 @@ bool MapDatabaseSQLite3::deleteBlock(const v3s16 &pos) bool MapDatabaseSQLite3::saveBlock(const v3s16 &pos, const std::string &data) { - verifyDatabase(); + pingDatabase(); #ifdef __ANDROID__ /** @@ -290,7 +290,7 @@ bool MapDatabaseSQLite3::saveBlock(const v3s16 &pos, const std::string &data) void MapDatabaseSQLite3::loadBlock(const v3s16 &pos, std::string *block) { - verifyDatabase(); + pingDatabase(); bindPos(m_stmt_read, pos); @@ -311,7 +311,7 @@ void MapDatabaseSQLite3::loadBlock(const v3s16 &pos, std::string *block) void MapDatabaseSQLite3::listAllLoadableBlocks(std::vector &dst) { - verifyDatabase(); + pingDatabase(); while (sqlite3_step(m_stmt_list) == SQLITE_ROW) dst.push_back(getIntegerAsBlock(sqlite3_column_int64(m_stmt_list, 0))); @@ -439,7 +439,7 @@ void PlayerDatabaseSQLite3::initStatements() bool PlayerDatabaseSQLite3::playerDataExists(const std::string &name) { - verifyDatabase(); + pingDatabase(); str_to_sqlite(m_stmt_player_load, 1, name); bool res = (sqlite3_step(m_stmt_player_load) == SQLITE_ROW); sqlite3_reset(m_stmt_player_load); @@ -536,7 +536,7 @@ void PlayerDatabaseSQLite3::savePlayer(RemotePlayer *player) bool PlayerDatabaseSQLite3::loadPlayer(RemotePlayer *player, PlayerSAO *sao) { - verifyDatabase(); + pingDatabase(); str_to_sqlite(m_stmt_player_load, 1, player->getName()); if (sqlite3_step(m_stmt_player_load) != SQLITE_ROW) { @@ -600,7 +600,7 @@ bool PlayerDatabaseSQLite3::removePlayer(const std::string &name) void PlayerDatabaseSQLite3::listPlayers(std::vector &res) { - verifyDatabase(); + pingDatabase(); while (sqlite3_step(m_stmt_player_list) == SQLITE_ROW) res.push_back(sqlite_to_string(m_stmt_player_list, 0)); @@ -673,7 +673,7 @@ void AuthDatabaseSQLite3::initStatements() bool AuthDatabaseSQLite3::getAuth(const std::string &name, AuthEntry &res) { - verifyDatabase(); + pingDatabase(); str_to_sqlite(m_stmt_read, 1, name); if (sqlite3_step(m_stmt_read) != SQLITE_ROW) { sqlite3_reset(m_stmt_read); @@ -735,7 +735,7 @@ bool AuthDatabaseSQLite3::createAuth(AuthEntry &authEntry) bool AuthDatabaseSQLite3::deleteAuth(const std::string &name) { - verifyDatabase(); + pingDatabase(); str_to_sqlite(m_stmt_delete, 1, name); sqlite3_vrfy(sqlite3_step(m_stmt_delete), SQLITE_DONE); @@ -749,7 +749,7 @@ bool AuthDatabaseSQLite3::deleteAuth(const std::string &name) void AuthDatabaseSQLite3::listNames(std::vector &res) { - verifyDatabase(); + pingDatabase(); while (sqlite3_step(m_stmt_list_names) == SQLITE_ROW) { res.push_back(sqlite_to_string(m_stmt_list_names, 0)); diff --git a/src/database/database-sqlite3.h b/src/database/database-sqlite3.h index d7202a918..647eddf7a 100644 --- a/src/database/database-sqlite3.h +++ b/src/database/database-sqlite3.h @@ -36,13 +36,13 @@ public: void beginSave(); void endSave(); + // Open and initialize the database if needed + virtual void pingDatabase(); + bool initialized() const { return m_initialized; } protected: Database_SQLite3(const std::string &savedir, const std::string &dbname); - // Open and initialize the database if needed - void verifyDatabase(); - // Convertors inline void str_to_sqlite(sqlite3_stmt *s, int iCol, const std::string &str) const { @@ -146,6 +146,8 @@ public: MapDatabaseSQLite3(const std::string &savedir); virtual ~MapDatabaseSQLite3(); + virtual void pingDatabase() { Database_SQLite3::pingDatabase(); } + bool saveBlock(const v3s16 &pos, const std::string &data); void loadBlock(const v3s16 &pos, std::string *block); bool deleteBlock(const v3s16 &pos); @@ -173,6 +175,8 @@ public: PlayerDatabaseSQLite3(const std::string &savedir); virtual ~PlayerDatabaseSQLite3(); + virtual void pingDatabase() { Database_SQLite3::pingDatabase(); } + void savePlayer(RemotePlayer *player); bool loadPlayer(RemotePlayer *player, PlayerSAO *sao); bool removePlayer(const std::string &name); @@ -208,6 +212,8 @@ public: AuthDatabaseSQLite3(const std::string &savedir); virtual ~AuthDatabaseSQLite3(); + virtual void pingDatabase() { Database_SQLite3::pingDatabase(); } + virtual bool getAuth(const std::string &name, AuthEntry &res); virtual bool saveAuth(const AuthEntry &authEntry); virtual bool createAuth(AuthEntry &authEntry); diff --git a/src/database/database.h b/src/database/database.h index b7d551935..47605a07e 100644 --- a/src/database/database.h +++ b/src/database/database.h @@ -32,6 +32,7 @@ public: virtual void beginSave() = 0; virtual void endSave() = 0; virtual bool initialized() const { return true; } + virtual void pingDatabase() {} }; class MapDatabase : public Database @@ -57,6 +58,8 @@ class PlayerDatabase public: virtual ~PlayerDatabase() = default; + virtual void pingDatabase() {} + virtual void savePlayer(RemotePlayer *player) = 0; virtual bool loadPlayer(RemotePlayer *player, PlayerSAO *sao) = 0; virtual bool removePlayer(const std::string &name) = 0; @@ -83,4 +86,5 @@ public: virtual bool deleteAuth(const std::string &name) = 0; virtual void listNames(std::vector &res) = 0; virtual void reload() = 0; + virtual void pingDatabase() {} }; -- cgit v1.2.3