From 0915b3ef56dfac3113cce55a59a5765dc94976be Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 28 Apr 2024 14:34:54 +0200 Subject: Adding upstream version 2.13.6. Signed-off-by: Daniel Baumann --- lib/db_ido_mysql/CMakeLists.txt | 41 + lib/db_ido_mysql/idomysqlconnection.cpp | 1268 +++++++++++++++++++++ lib/db_ido_mysql/idomysqlconnection.hpp | 114 ++ lib/db_ido_mysql/idomysqlconnection.ti | 42 + lib/db_ido_mysql/schema/mysql.sql | 1666 ++++++++++++++++++++++++++++ lib/db_ido_mysql/schema/upgrade/2.0.2.sql | 20 + lib/db_ido_mysql/schema/upgrade/2.1.0.sql | 17 + lib/db_ido_mysql/schema/upgrade/2.11.0.sql | 89 ++ lib/db_ido_mysql/schema/upgrade/2.12.7.sql | 15 + lib/db_ido_mysql/schema/upgrade/2.13.0.sql | 23 + lib/db_ido_mysql/schema/upgrade/2.13.3.sql | 15 + lib/db_ido_mysql/schema/upgrade/2.2.0.sql | 23 + lib/db_ido_mysql/schema/upgrade/2.3.0.sql | 26 + lib/db_ido_mysql/schema/upgrade/2.4.0.sql | 75 ++ lib/db_ido_mysql/schema/upgrade/2.5.0.sql | 103 ++ lib/db_ido_mysql/schema/upgrade/2.6.0.sql | 151 +++ lib/db_ido_mysql/schema/upgrade/2.8.0.sql | 81 ++ lib/db_ido_mysql/schema/upgrade/2.8.1.sql | 67 ++ 18 files changed, 3836 insertions(+) create mode 100644 lib/db_ido_mysql/CMakeLists.txt create mode 100644 lib/db_ido_mysql/idomysqlconnection.cpp create mode 100644 lib/db_ido_mysql/idomysqlconnection.hpp create mode 100644 lib/db_ido_mysql/idomysqlconnection.ti create mode 100644 lib/db_ido_mysql/schema/mysql.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.0.2.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.1.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.11.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.12.7.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.13.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.13.3.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.2.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.3.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.4.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.5.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.6.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.8.0.sql create mode 100644 lib/db_ido_mysql/schema/upgrade/2.8.1.sql (limited to 'lib/db_ido_mysql') diff --git a/lib/db_ido_mysql/CMakeLists.txt b/lib/db_ido_mysql/CMakeLists.txt new file mode 100644 index 0000000..70cb90d --- /dev/null +++ b/lib/db_ido_mysql/CMakeLists.txt @@ -0,0 +1,41 @@ +# Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ + +mkclass_target(idomysqlconnection.ti idomysqlconnection-ti.cpp idomysqlconnection-ti.hpp) + +set(db_ido_mysql_SOURCES + idomysqlconnection.cpp idomysqlconnection.hpp idomysqlconnection-ti.hpp +) + +if(ICINGA2_UNITY_BUILD) + mkunity_target(db_ido_mysql db_ido_mysql db_ido_mysql_SOURCES) +endif() + +add_library(db_ido_mysql OBJECT ${db_ido_mysql_SOURCES}) + +include_directories(${MYSQL_INCLUDE_DIR}) + +add_dependencies(db_ido_mysql base config icinga db_ido) + +set_target_properties ( + db_ido_mysql PROPERTIES + FOLDER Components +) + +install_if_not_exists( + ${PROJECT_SOURCE_DIR}/etc/icinga2/features-available/ido-mysql.conf + ${ICINGA2_CONFIGDIR}/features-available +) + +install( + DIRECTORY schema + DESTINATION ${CMAKE_INSTALL_DATADIR}/icinga2-ido-mysql + FILES_MATCHING PATTERN "*.sql" +) + +install( + DIRECTORY schema/upgrade + DESTINATION ${CMAKE_INSTALL_DATADIR}/icinga2-ido-mysql/schema + FILES_MATCHING PATTERN "*.sql" +) + +set(CPACK_NSIS_EXTRA_INSTALL_COMMANDS "${CPACK_NSIS_EXTRA_INSTALL_COMMANDS}" PARENT_SCOPE) diff --git a/lib/db_ido_mysql/idomysqlconnection.cpp b/lib/db_ido_mysql/idomysqlconnection.cpp new file mode 100644 index 0000000..42c8332 --- /dev/null +++ b/lib/db_ido_mysql/idomysqlconnection.cpp @@ -0,0 +1,1268 @@ +/* Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ */ + +#include "db_ido_mysql/idomysqlconnection.hpp" +#include "db_ido_mysql/idomysqlconnection-ti.cpp" +#include "db_ido/dbtype.hpp" +#include "db_ido/dbvalue.hpp" +#include "base/logger.hpp" +#include "base/objectlock.hpp" +#include "base/convert.hpp" +#include "base/utility.hpp" +#include "base/perfdatavalue.hpp" +#include "base/application.hpp" +#include "base/configtype.hpp" +#include "base/exception.hpp" +#include "base/statsfunction.hpp" +#include "base/defer.hpp" +#include + +using namespace icinga; + +REGISTER_TYPE(IdoMysqlConnection); +REGISTER_STATSFUNCTION(IdoMysqlConnection, &IdoMysqlConnection::StatsFunc); + +const char * IdoMysqlConnection::GetLatestSchemaVersion() const noexcept +{ + return "1.15.1"; +} + +const char * IdoMysqlConnection::GetCompatSchemaVersion() const noexcept +{ + return "1.14.3"; +} + +void IdoMysqlConnection::OnConfigLoaded() +{ + ObjectImpl::OnConfigLoaded(); + + m_QueryQueue.SetName("IdoMysqlConnection, " + GetName()); + + Library shimLibrary{"mysql_shim"}; + + auto create_mysql_shim = shimLibrary.GetSymbolAddress("create_mysql_shim"); + + m_Mysql.reset(create_mysql_shim()); + + std::swap(m_Library, shimLibrary); +} + +void IdoMysqlConnection::StatsFunc(const Dictionary::Ptr& status, const Array::Ptr& perfdata) +{ + DictionaryData nodes; + + for (const IdoMysqlConnection::Ptr& idomysqlconnection : ConfigType::GetObjectsByType()) { + size_t queryQueueItems = idomysqlconnection->m_QueryQueue.GetLength(); + double queryQueueItemRate = idomysqlconnection->m_QueryQueue.GetTaskCount(60) / 60.0; + + nodes.emplace_back(idomysqlconnection->GetName(), new Dictionary({ + { "version", idomysqlconnection->GetSchemaVersion() }, + { "instance_name", idomysqlconnection->GetInstanceName() }, + { "connected", idomysqlconnection->GetConnected() }, + { "query_queue_items", queryQueueItems }, + { "query_queue_item_rate", queryQueueItemRate } + })); + + perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_rate", idomysqlconnection->GetQueryCount(60) / 60.0)); + perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_1min", idomysqlconnection->GetQueryCount(60))); + perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_5mins", idomysqlconnection->GetQueryCount(5 * 60))); + perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_queries_15mins", idomysqlconnection->GetQueryCount(15 * 60))); + perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_query_queue_items", queryQueueItems)); + perfdata->Add(new PerfdataValue("idomysqlconnection_" + idomysqlconnection->GetName() + "_query_queue_item_rate", queryQueueItemRate)); + } + + status->Set("idomysqlconnection", new Dictionary(std::move(nodes))); +} + +void IdoMysqlConnection::Resume() +{ + Log(LogInformation, "IdoMysqlConnection") + << "'" << GetName() << "' resumed."; + + SetConnected(false); + + m_QueryQueue.SetExceptionCallback([this](boost::exception_ptr exp) { ExceptionHandler(std::move(exp)); }); + + /* Immediately try to connect on Resume() without timer. */ + m_QueryQueue.Enqueue([this]() { Reconnect(); }, PriorityImmediate); + + m_TxTimer = new Timer(); + m_TxTimer->SetInterval(1); + m_TxTimer->OnTimerExpired.connect([this](const Timer * const&) { NewTransaction(); }); + m_TxTimer->Start(); + + m_ReconnectTimer = new Timer(); + m_ReconnectTimer->SetInterval(10); + m_ReconnectTimer->OnTimerExpired.connect([this](const Timer * const&){ ReconnectTimerHandler(); }); + m_ReconnectTimer->Start(); + + /* Start with queries after connect. */ + DbConnection::Resume(); + + ASSERT(m_Mysql->thread_safe()); +} + +void IdoMysqlConnection::Pause() +{ + Log(LogDebug, "IdoMysqlConnection") + << "Attempting to pause '" << GetName() << "'."; + + DbConnection::Pause(); + + m_ReconnectTimer.reset(); + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Rescheduling disconnect task."; +#endif /* I2_DEBUG */ + + Log(LogInformation, "IdoMysqlConnection") + << "'" << GetName() << "' paused."; + +} + +void IdoMysqlConnection::ExceptionHandler(boost::exception_ptr exp) +{ + Log(LogCritical, "IdoMysqlConnection", "Exception during database operation: Verify that your database is operational!"); + + Log(LogDebug, "IdoMysqlConnection") + << "Exception during database operation: " << DiagnosticInformation(std::move(exp)); + + if (GetConnected()) { + m_Mysql->close(&m_Connection); + + SetConnected(false); + } +} + +void IdoMysqlConnection::AssertOnWorkQueue() +{ + ASSERT(m_QueryQueue.IsWorkerThread()); +} + +void IdoMysqlConnection::Disconnect() +{ + AssertOnWorkQueue(); + + if (!GetConnected()) + return; + + Query("COMMIT"); + m_Mysql->close(&m_Connection); + + SetConnected(false); + + Log(LogInformation, "IdoMysqlConnection") + << "Disconnected from '" << GetName() << "' database '" << GetDatabase() << "'."; +} + +void IdoMysqlConnection::NewTransaction() +{ + if (IsPaused() && GetPauseCalled()) + return; + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling new transaction and finishing async queries."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this]() { InternalNewTransaction(); }, PriorityHigh); +} + +void IdoMysqlConnection::InternalNewTransaction() +{ + AssertOnWorkQueue(); + + if (!GetConnected()) + return; + + IncreasePendingQueries(2); + + AsyncQuery("COMMIT"); + AsyncQuery("BEGIN"); + + FinishAsyncQueries(); +} + +void IdoMysqlConnection::ReconnectTimerHandler() +{ +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling reconnect task."; +#endif /* I2_DEBUG */ + + /* Only allow Reconnect events with high priority. */ + m_QueryQueue.Enqueue([this]() { Reconnect(); }, PriorityImmediate); +} + +void IdoMysqlConnection::Reconnect() +{ + AssertOnWorkQueue(); + + if (!IsActive()) + return; + + CONTEXT("Reconnecting to MySQL IDO database '" + GetName() + "'"); + + double startTime = Utility::GetTime(); + + SetShouldConnect(true); + + bool reconnect = false; + + /* Ensure to close old connections first. */ + if (GetConnected()) { + /* Check if we're really still connected */ + if (m_Mysql->ping(&m_Connection) == 0) + return; + + m_Mysql->close(&m_Connection); + SetConnected(false); + reconnect = true; + } + + Log(LogDebug, "IdoMysqlConnection") + << "Reconnect: Clearing ID cache."; + + ClearIDCache(); + + String ihost, isocket_path, iuser, ipasswd, idb; + String isslKey, isslCert, isslCa, isslCaPath, isslCipher; + const char *host, *socket_path, *user , *passwd, *db; + const char *sslKey, *sslCert, *sslCa, *sslCaPath, *sslCipher; + bool enableSsl; + long port; + + ihost = GetHost(); + isocket_path = GetSocketPath(); + iuser = GetUser(); + ipasswd = GetPassword(); + idb = GetDatabase(); + + enableSsl = GetEnableSsl(); + isslKey = GetSslKey(); + isslCert = GetSslCert(); + isslCa = GetSslCa(); + isslCaPath = GetSslCapath(); + isslCipher = GetSslCipher(); + + host = (!ihost.IsEmpty()) ? ihost.CStr() : nullptr; + port = GetPort(); + socket_path = (!isocket_path.IsEmpty()) ? isocket_path.CStr() : nullptr; + user = (!iuser.IsEmpty()) ? iuser.CStr() : nullptr; + passwd = (!ipasswd.IsEmpty()) ? ipasswd.CStr() : nullptr; + db = (!idb.IsEmpty()) ? idb.CStr() : nullptr; + + sslKey = (!isslKey.IsEmpty()) ? isslKey.CStr() : nullptr; + sslCert = (!isslCert.IsEmpty()) ? isslCert.CStr() : nullptr; + sslCa = (!isslCa.IsEmpty()) ? isslCa.CStr() : nullptr; + sslCaPath = (!isslCaPath.IsEmpty()) ? isslCaPath.CStr() : nullptr; + sslCipher = (!isslCipher.IsEmpty()) ? isslCipher.CStr() : nullptr; + + /* connection */ + if (!m_Mysql->init(&m_Connection)) { + Log(LogCritical, "IdoMysqlConnection") + << "mysql_init() failed: out of memory"; + + BOOST_THROW_EXCEPTION(std::bad_alloc()); + } + + /* Read "latin1" (here, in the schema and in Icinga Web) as "bytes". + Icinga 2 and Icinga Web use byte-strings everywhere and every byte-string is a valid latin1 string. + This way the (actually mostly UTF-8) bytes are transferred end-to-end as-is. */ + m_Mysql->options(&m_Connection, MYSQL_SET_CHARSET_NAME, "latin1"); + + if (enableSsl) + m_Mysql->ssl_set(&m_Connection, sslKey, sslCert, sslCa, sslCaPath, sslCipher); + + if (!m_Mysql->real_connect(&m_Connection, host, user, passwd, db, port, socket_path, CLIENT_FOUND_ROWS | CLIENT_MULTI_STATEMENTS)) { + Log(LogCritical, "IdoMysqlConnection") + << "Connection to database '" << db << "' with user '" << user << "' on '" << host << ":" << port + << "' " << (enableSsl ? "(SSL enabled) " : "") << "failed: \"" << m_Mysql->error(&m_Connection) << "\""; + + BOOST_THROW_EXCEPTION(std::runtime_error(m_Mysql->error(&m_Connection))); + } + + Log(LogNotice, "IdoMysqlConnection") + << "Reconnect: '" << GetName() << "' is now connected to database '" << GetDatabase() << "'."; + + SetConnected(true); + + IdoMysqlResult result = Query("SELECT @@global.max_allowed_packet AS max_allowed_packet"); + + Dictionary::Ptr row = FetchRow(result); + + if (row) + m_MaxPacketSize = row->Get("max_allowed_packet"); + else + m_MaxPacketSize = 64 * 1024; + + DiscardRows(result); + + String dbVersionName = "idoutils"; + result = Query("SELECT version FROM " + GetTablePrefix() + "dbversion WHERE name='" + Escape(dbVersionName) + "'"); + + row = FetchRow(result); + + if (!row) { + m_Mysql->close(&m_Connection); + SetConnected(false); + + Log(LogCritical, "IdoMysqlConnection", "Schema does not provide any valid version! Verify your schema installation."); + + BOOST_THROW_EXCEPTION(std::runtime_error("Invalid schema.")); + } + + DiscardRows(result); + + String version = row->Get("version"); + + SetSchemaVersion(version); + + if (Utility::CompareVersion(GetCompatSchemaVersion(), version) < 0) { + m_Mysql->close(&m_Connection); + SetConnected(false); + + Log(LogCritical, "IdoMysqlConnection") + << "Schema version '" << version << "' does not match the required version '" + << GetCompatSchemaVersion() << "' (or newer)! Please check the upgrade documentation at " + << "https://icinga.com/docs/icinga2/latest/doc/16-upgrading-icinga-2/#upgrading-mysql-db"; + + BOOST_THROW_EXCEPTION(std::runtime_error("Schema version mismatch.")); + } + + String instanceName = GetInstanceName(); + + result = Query("SELECT instance_id FROM " + GetTablePrefix() + "instances WHERE instance_name = '" + Escape(instanceName) + "'"); + row = FetchRow(result); + + if (!row) { + Query("INSERT INTO " + GetTablePrefix() + "instances (instance_name, instance_description) VALUES ('" + Escape(instanceName) + "', '" + Escape(GetInstanceDescription()) + "')"); + m_InstanceID = GetLastInsertID(); + } else { + m_InstanceID = DbReference(row->Get("instance_id")); + } + + DiscardRows(result); + + Endpoint::Ptr my_endpoint = Endpoint::GetLocalEndpoint(); + + /* we have an endpoint in a cluster setup, so decide if we can proceed here */ + if (my_endpoint && GetHAMode() == HARunOnce) { + /* get the current endpoint writing to programstatus table */ + result = Query("SELECT UNIX_TIMESTAMP(status_update_time) AS status_update_time, endpoint_name FROM " + + GetTablePrefix() + "programstatus WHERE instance_id = " + Convert::ToString(m_InstanceID)); + row = FetchRow(result); + DiscardRows(result); + + String endpoint_name; + + if (row) + endpoint_name = row->Get("endpoint_name"); + else + Log(LogNotice, "IdoMysqlConnection", "Empty program status table"); + + /* if we did not write into the database earlier, another instance is active */ + if (endpoint_name != my_endpoint->GetName()) { + double status_update_time; + + if (row) + status_update_time = row->Get("status_update_time"); + else + status_update_time = 0; + + double now = Utility::GetTime(); + + double status_update_age = now - status_update_time; + double failoverTimeout = GetFailoverTimeout(); + + if (status_update_age < failoverTimeout) { + Log(LogInformation, "IdoMysqlConnection") + << "Last update by endpoint '" << endpoint_name << "' was " + << status_update_age << "s ago (< failover timeout of " << failoverTimeout << "s). Retrying."; + + m_Mysql->close(&m_Connection); + SetConnected(false); + SetShouldConnect(false); + + return; + } + + /* activate the IDO only, if we're authoritative in this zone */ + if (IsPaused()) { + Log(LogNotice, "IdoMysqlConnection") + << "Local endpoint '" << my_endpoint->GetName() << "' is not authoritative, bailing out."; + + m_Mysql->close(&m_Connection); + SetConnected(false); + + return; + } + + SetLastFailover(now); + + Log(LogInformation, "IdoMysqlConnection") + << "Last update by endpoint '" << endpoint_name << "' was " + << status_update_age << "s ago. Taking over '" << GetName() << "' in HA zone '" << Zone::GetLocalZone()->GetName() << "'."; + } + + Log(LogNotice, "IdoMysqlConnection", "Enabling IDO connection in HA zone."); + } + + Log(LogInformation, "IdoMysqlConnection") + << "MySQL IDO instance id: " << static_cast(m_InstanceID) << " (schema version: '" + version + "')"; + + /* set session time zone to utc */ + Query("SET SESSION TIME_ZONE='+00:00'"); + + Query("SET SESSION SQL_MODE='NO_AUTO_VALUE_ON_ZERO'"); + + Query("BEGIN"); + + /* update programstatus table */ + UpdateProgramStatus(); + + /* record connection */ + Query("INSERT INTO " + GetTablePrefix() + "conninfo " + + "(instance_id, connect_time, last_checkin_time, agent_name, agent_version, connect_type, data_start_time) VALUES (" + + Convert::ToString(static_cast(m_InstanceID)) + ", NOW(), NOW(), 'icinga2 db_ido_mysql', '" + Escape(Application::GetAppVersion()) + + "', '" + (reconnect ? "RECONNECT" : "INITIAL") + "', NOW())"); + + /* clear config tables for the initial config dump */ + PrepareDatabase(); + + std::ostringstream q1buf; + q1buf << "SELECT object_id, objecttype_id, name1, name2, is_active FROM " + GetTablePrefix() + "objects WHERE instance_id = " << static_cast(m_InstanceID); + result = Query(q1buf.str()); + + std::vector activeDbObjs; + + while ((row = FetchRow(result))) { + DbType::Ptr dbtype = DbType::GetByID(row->Get("objecttype_id")); + + if (!dbtype) + continue; + + DbObject::Ptr dbobj = dbtype->GetOrCreateObjectByName(row->Get("name1"), row->Get("name2")); + SetObjectID(dbobj, DbReference(row->Get("object_id"))); + bool active = row->Get("is_active"); + SetObjectActive(dbobj, active); + + if (active) + activeDbObjs.emplace_back(std::move(dbobj)); + } + + SetIDCacheValid(true); + + EnableActiveChangedHandler(); + + for (const DbObject::Ptr& dbobj : activeDbObjs) { + if (dbobj->GetObject()) + continue; + + Log(LogNotice, "IdoMysqlConnection") + << "Deactivate deleted object name1: '" << dbobj->GetName1() + << "' name2: '" << dbobj->GetName2() + "'."; + DeactivateObject(dbobj); + } + + UpdateAllObjects(); + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling session table clear and finish connect task."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this]() { ClearTablesBySession(); }, PriorityNormal); + + m_QueryQueue.Enqueue([this, startTime]() { FinishConnect(startTime); }, PriorityNormal); +} + +void IdoMysqlConnection::FinishConnect(double startTime) +{ + AssertOnWorkQueue(); + + if (!GetConnected() || IsPaused()) + return; + + FinishAsyncQueries(); + + Log(LogInformation, "IdoMysqlConnection") + << "Finished reconnecting to '" << GetName() << "' database '" << GetDatabase() << "' in " + << std::setw(2) << Utility::GetTime() - startTime << " second(s)."; + + Query("COMMIT"); + Query("BEGIN"); +} + +void IdoMysqlConnection::ClearTablesBySession() +{ + /* delete all comments and downtimes without current session token */ + ClearTableBySession("comments"); + ClearTableBySession("scheduleddowntime"); +} + +void IdoMysqlConnection::ClearTableBySession(const String& table) +{ + Query("DELETE FROM " + GetTablePrefix() + table + " WHERE instance_id = " + + Convert::ToString(static_cast(m_InstanceID)) + " AND session_token <> " + + Convert::ToString(GetSessionToken())); +} + +void IdoMysqlConnection::AsyncQuery(const String& query, const std::function& callback) +{ + AssertOnWorkQueue(); + + IdoAsyncQuery aq; + aq.Query = query; + /* XXX: Important: The callback must not immediately execute a query, but enqueue it! + * See https://github.com/Icinga/icinga2/issues/4603 for details. + */ + aq.Callback = callback; + m_AsyncQueries.emplace_back(std::move(aq)); +} + +void IdoMysqlConnection::FinishAsyncQueries() +{ + std::vector queries; + m_AsyncQueries.swap(queries); + + std::vector::size_type offset = 0; + + // This will be executed if there is a problem with executing the queries, + // at which point this function throws an exception and the queries should + // not be listed as still pending in the queue. + Defer decreaseQueries ([this, &offset, &queries]() { + auto lostQueries = queries.size() - offset; + + if (lostQueries > 0) { + DecreasePendingQueries(lostQueries); + } + }); + + while (offset < queries.size()) { + std::ostringstream querybuf; + + std::vector::size_type count = 0; + size_t num_bytes = 0; + + Defer decreaseQueries ([this, &offset, &count]() { + offset += count; + DecreasePendingQueries(count); + m_UncommittedAsyncQueries += count; + }); + + for (std::vector::size_type i = offset; i < queries.size(); i++) { + const IdoAsyncQuery& aq = queries[i]; + + size_t size_query = aq.Query.GetLength() + 1; + + if (count > 0) { + if (num_bytes + size_query > m_MaxPacketSize - 512) + break; + + querybuf << ";"; + } + + IncreaseQueryCount(); + count++; + + Log(LogDebug, "IdoMysqlConnection") + << "Query: " << aq.Query; + + querybuf << aq.Query; + num_bytes += size_query; + } + + String query = querybuf.str(); + + if (m_Mysql->query(&m_Connection, query.CStr()) != 0) { + std::ostringstream msgbuf; + String message = m_Mysql->error(&m_Connection); + msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\""; + Log(LogCritical, "IdoMysqlConnection", msgbuf.str()); + + BOOST_THROW_EXCEPTION( + database_error() + << errinfo_message(m_Mysql->error(&m_Connection)) + << errinfo_database_query(query) + ); + } + + for (std::vector::size_type i = offset; i < offset + count; i++) { + const IdoAsyncQuery& aq = queries[i]; + + MYSQL_RES *result = m_Mysql->store_result(&m_Connection); + + m_AffectedRows = m_Mysql->affected_rows(&m_Connection); + + IdoMysqlResult iresult; + + if (!result) { + if (m_Mysql->field_count(&m_Connection) > 0) { + std::ostringstream msgbuf; + String message = m_Mysql->error(&m_Connection); + msgbuf << "Error \"" << message << "\" when executing query \"" << aq.Query << "\""; + Log(LogCritical, "IdoMysqlConnection", msgbuf.str()); + + BOOST_THROW_EXCEPTION( + database_error() + << errinfo_message(m_Mysql->error(&m_Connection)) + << errinfo_database_query(query) + ); + } + } else + iresult = IdoMysqlResult(result, [this](MYSQL_RES* result) { m_Mysql->free_result(result); }); + + if (aq.Callback) + aq.Callback(iresult); + + if (m_Mysql->next_result(&m_Connection) > 0) { + std::ostringstream msgbuf; + String message = m_Mysql->error(&m_Connection); + msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\""; + Log(LogCritical, "IdoMysqlConnection", msgbuf.str()); + + BOOST_THROW_EXCEPTION( + database_error() + << errinfo_message(m_Mysql->error(&m_Connection)) + << errinfo_database_query(query) + ); + } + } + } + + if (m_UncommittedAsyncQueries > 25000) { + m_UncommittedAsyncQueries = 0; + + Query("COMMIT"); + Query("BEGIN"); + } +} + +IdoMysqlResult IdoMysqlConnection::Query(const String& query) +{ + AssertOnWorkQueue(); + + IncreasePendingQueries(1); + Defer decreaseQueries ([this]() { DecreasePendingQueries(1); }); + + /* finish all async queries to maintain the right order for queries */ + FinishAsyncQueries(); + + Log(LogDebug, "IdoMysqlConnection") + << "Query: " << query; + + IncreaseQueryCount(); + + if (m_Mysql->query(&m_Connection, query.CStr()) != 0) { + std::ostringstream msgbuf; + String message = m_Mysql->error(&m_Connection); + msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\""; + Log(LogCritical, "IdoMysqlConnection", msgbuf.str()); + + BOOST_THROW_EXCEPTION( + database_error() + << errinfo_message(m_Mysql->error(&m_Connection)) + << errinfo_database_query(query) + ); + } + + MYSQL_RES *result = m_Mysql->store_result(&m_Connection); + + m_AffectedRows = m_Mysql->affected_rows(&m_Connection); + + if (!result) { + if (m_Mysql->field_count(&m_Connection) > 0) { + std::ostringstream msgbuf; + String message = m_Mysql->error(&m_Connection); + msgbuf << "Error \"" << message << "\" when executing query \"" << query << "\""; + Log(LogCritical, "IdoMysqlConnection", msgbuf.str()); + + BOOST_THROW_EXCEPTION( + database_error() + << errinfo_message(m_Mysql->error(&m_Connection)) + << errinfo_database_query(query) + ); + } + + return IdoMysqlResult(); + } + + return IdoMysqlResult(result, [this](MYSQL_RES* result) { m_Mysql->free_result(result); }); +} + +DbReference IdoMysqlConnection::GetLastInsertID() +{ + AssertOnWorkQueue(); + + return {static_cast(m_Mysql->insert_id(&m_Connection))}; +} + +int IdoMysqlConnection::GetAffectedRows() +{ + AssertOnWorkQueue(); + + return m_AffectedRows; +} + +String IdoMysqlConnection::Escape(const String& s) +{ + AssertOnWorkQueue(); + + String utf8s = Utility::ValidateUTF8(s); + + size_t length = utf8s.GetLength(); + auto *to = new char[utf8s.GetLength() * 2 + 1]; + + m_Mysql->real_escape_string(&m_Connection, to, utf8s.CStr(), length); + + String result = String(to); + + delete [] to; + + return result; +} + +Dictionary::Ptr IdoMysqlConnection::FetchRow(const IdoMysqlResult& result) +{ + AssertOnWorkQueue(); + + MYSQL_ROW row; + MYSQL_FIELD *field; + unsigned long *lengths, i; + + row = m_Mysql->fetch_row(result.get()); + + if (!row) + return nullptr; + + lengths = m_Mysql->fetch_lengths(result.get()); + + if (!lengths) + return nullptr; + + Dictionary::Ptr dict = new Dictionary(); + + m_Mysql->field_seek(result.get(), 0); + for (field = m_Mysql->fetch_field(result.get()), i = 0; field; field = m_Mysql->fetch_field(result.get()), i++) + dict->Set(field->name, String(row[i], row[i] + lengths[i])); + + return dict; +} + +void IdoMysqlConnection::DiscardRows(const IdoMysqlResult& result) +{ + Dictionary::Ptr row; + + while ((row = FetchRow(result))) + ; /* empty loop body */ +} + +void IdoMysqlConnection::ActivateObject(const DbObject::Ptr& dbobj) +{ + if (IsPaused()) + return; + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling object activation task for '" << dbobj->GetName1() << "!" << dbobj->GetName2() << "'."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this, dbobj]() { InternalActivateObject(dbobj); }, PriorityNormal); +} + +void IdoMysqlConnection::InternalActivateObject(const DbObject::Ptr& dbobj) +{ + AssertOnWorkQueue(); + + if (IsPaused()) + return; + + if (!GetConnected()) + return; + + DbReference dbref = GetObjectID(dbobj); + std::ostringstream qbuf; + + if (!dbref.IsValid()) { + if (!dbobj->GetName2().IsEmpty()) { + qbuf << "INSERT INTO " + GetTablePrefix() + "objects (instance_id, objecttype_id, name1, name2, is_active) VALUES (" + << static_cast(m_InstanceID) << ", " << dbobj->GetType()->GetTypeID() << ", " + << "'" << Escape(dbobj->GetName1()) << "', '" << Escape(dbobj->GetName2()) << "', 1)"; + } else { + qbuf << "INSERT INTO " + GetTablePrefix() + "objects (instance_id, objecttype_id, name1, is_active) VALUES (" + << static_cast(m_InstanceID) << ", " << dbobj->GetType()->GetTypeID() << ", " + << "'" << Escape(dbobj->GetName1()) << "', 1)"; + } + + Query(qbuf.str()); + SetObjectID(dbobj, GetLastInsertID()); + } else { + qbuf << "UPDATE " + GetTablePrefix() + "objects SET is_active = 1 WHERE object_id = " << static_cast(dbref); + IncreasePendingQueries(1); + AsyncQuery(qbuf.str()); + } +} + +void IdoMysqlConnection::DeactivateObject(const DbObject::Ptr& dbobj) +{ + if (IsPaused()) + return; + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling object deactivation task for '" << dbobj->GetName1() << "!" << dbobj->GetName2() << "'."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this, dbobj]() { InternalDeactivateObject(dbobj); }, PriorityNormal); +} + +void IdoMysqlConnection::InternalDeactivateObject(const DbObject::Ptr& dbobj) +{ + AssertOnWorkQueue(); + + if (IsPaused()) + return; + + if (!GetConnected()) + return; + + DbReference dbref = GetObjectID(dbobj); + + if (!dbref.IsValid()) + return; + + std::ostringstream qbuf; + qbuf << "UPDATE " + GetTablePrefix() + "objects SET is_active = 0 WHERE object_id = " << static_cast(dbref); + IncreasePendingQueries(1); + AsyncQuery(qbuf.str()); + + /* Note that we're _NOT_ clearing the db refs via SetReference/SetConfigUpdate/SetStatusUpdate + * because the object is still in the database. */ + + SetObjectActive(dbobj, false); +} + +bool IdoMysqlConnection::FieldToEscapedString(const String& key, const Value& value, Value *result) +{ + if (key == "instance_id") { + *result = static_cast(m_InstanceID); + return true; + } else if (key == "session_token") { + *result = GetSessionToken(); + return true; + } + + Value rawvalue = DbValue::ExtractValue(value); + + if (rawvalue.GetType() == ValueEmpty) { + *result = "NULL"; + } else if (rawvalue.IsObjectType()) { + DbObject::Ptr dbobjcol = DbObject::GetOrCreateByObject(rawvalue); + + if (!dbobjcol) { + *result = 0; + return true; + } + + if (!IsIDCacheValid()) + return false; + + DbReference dbrefcol; + + if (DbValue::IsObjectInsertID(value)) { + dbrefcol = GetInsertID(dbobjcol); + + if (!dbrefcol.IsValid()) + return false; + } else { + dbrefcol = GetObjectID(dbobjcol); + + if (!dbrefcol.IsValid()) { + InternalActivateObject(dbobjcol); + + dbrefcol = GetObjectID(dbobjcol); + + if (!dbrefcol.IsValid()) + return false; + } + } + + *result = static_cast(dbrefcol); + } else if (DbValue::IsTimestamp(value)) { + long ts = rawvalue; + std::ostringstream msgbuf; + msgbuf << "FROM_UNIXTIME(" << ts << ")"; + *result = Value(msgbuf.str()); + } else if (DbValue::IsObjectInsertID(value)) { + auto id = static_cast(rawvalue); + + if (id <= 0) + return false; + + *result = id; + return true; + } else { + Value fvalue; + + if (rawvalue.IsBoolean()) + fvalue = Convert::ToLong(rawvalue); + else + fvalue = rawvalue; + + *result = "'" + Escape(fvalue) + "'"; + } + + return true; +} + +void IdoMysqlConnection::ExecuteQuery(const DbQuery& query) +{ + if (IsPaused() && GetPauseCalled()) + return; + + ASSERT(query.Category != DbCatInvalid); + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling execute query task, type " << query.Type << ", table '" << query.Table << "'."; +#endif /* I2_DEBUG */ + + IncreasePendingQueries(1); + m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, -1); }, query.Priority, true); +} + +void IdoMysqlConnection::ExecuteMultipleQueries(const std::vector& queries) +{ + if (IsPaused()) + return; + + if (queries.empty()) + return; + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling multiple execute query task, type " << queries[0].Type << ", table '" << queries[0].Table << "'."; +#endif /* I2_DEBUG */ + + IncreasePendingQueries(queries.size()); + m_QueryQueue.Enqueue([this, queries]() { InternalExecuteMultipleQueries(queries); }, queries[0].Priority, true); +} + +bool IdoMysqlConnection::CanExecuteQuery(const DbQuery& query) +{ + if (query.Object && !IsIDCacheValid()) + return false; + + if (query.WhereCriteria) { + ObjectLock olock(query.WhereCriteria); + Value value; + + for (const Dictionary::Pair& kv : query.WhereCriteria) { + if (!FieldToEscapedString(kv.first, kv.second, &value)) + return false; + } + } + + if (query.Fields) { + ObjectLock olock(query.Fields); + + for (const Dictionary::Pair& kv : query.Fields) { + Value value; + + if (!FieldToEscapedString(kv.first, kv.second, &value)) + return false; + } + } + + return true; +} + +void IdoMysqlConnection::InternalExecuteMultipleQueries(const std::vector& queries) +{ + AssertOnWorkQueue(); + + if (IsPaused()) { + DecreasePendingQueries(queries.size()); + return; + } + + if (!GetConnected()) { + DecreasePendingQueries(queries.size()); + return; + } + + + for (const DbQuery& query : queries) { + ASSERT(query.Type == DbQueryNewTransaction || query.Category != DbCatInvalid); + + if (!CanExecuteQuery(query)) { + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling multiple execute query task again: Cannot execute query now. Type '" + << query.Type << "', table '" << query.Table << "', queue size: '" << GetPendingQueryCount() << "'."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this, queries]() { InternalExecuteMultipleQueries(queries); }, query.Priority); + return; + } + } + + for (const DbQuery& query : queries) { + InternalExecuteQuery(query); + } +} + +void IdoMysqlConnection::InternalExecuteQuery(const DbQuery& query, int typeOverride) +{ + AssertOnWorkQueue(); + + if (IsPaused() && GetPauseCalled()) { + DecreasePendingQueries(1); + return; + } + + if (!GetConnected()) { + DecreasePendingQueries(1); + return; + } + + if (query.Type == DbQueryNewTransaction) { + DecreasePendingQueries(1); + InternalNewTransaction(); + return; + } + + /* check whether we're allowed to execute the query first */ + if (GetCategoryFilter() != DbCatEverything && (query.Category & GetCategoryFilter()) == 0) { + DecreasePendingQueries(1); + return; + } + + if (query.Object && query.Object->GetObject()->GetExtension("agent_check").ToBool()) { + DecreasePendingQueries(1); + return; + } + + /* check if there are missing object/insert ids and re-enqueue the query */ + if (!CanExecuteQuery(query)) { + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling execute query task again: Cannot execute query now. Type '" + << typeOverride << "', table '" << query.Table << "', queue size: '" << GetPendingQueryCount() << "'."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this, query, typeOverride]() { InternalExecuteQuery(query, typeOverride); }, query.Priority); + return; + } + + std::ostringstream qbuf, where; + int type; + + if (query.WhereCriteria) { + where << " WHERE "; + + ObjectLock olock(query.WhereCriteria); + Value value; + bool first = true; + + for (const Dictionary::Pair& kv : query.WhereCriteria) { + if (!FieldToEscapedString(kv.first, kv.second, &value)) { + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling execute query task again: Cannot execute query now. Type '" + << typeOverride << "', table '" << query.Table << "', queue size: '" << GetPendingQueryCount() << "'."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, -1); }, query.Priority); + return; + } + + if (!first) + where << " AND "; + + where << kv.first << " = " << value; + + if (first) + first = false; + } + } + + type = (typeOverride != -1) ? typeOverride : query.Type; + + bool upsert = false; + + if ((type & DbQueryInsert) && (type & DbQueryUpdate)) { + bool hasid = false; + + if (query.Object) { + if (query.ConfigUpdate) + hasid = GetConfigUpdate(query.Object); + else if (query.StatusUpdate) + hasid = GetStatusUpdate(query.Object); + } + + if (!hasid) + upsert = true; + + type = DbQueryUpdate; + } + + if ((type & DbQueryInsert) && (type & DbQueryDelete)) { + std::ostringstream qdel; + qdel << "DELETE FROM " << GetTablePrefix() << query.Table << where.str(); + IncreasePendingQueries(1); + AsyncQuery(qdel.str()); + + type = DbQueryInsert; + } + + switch (type) { + case DbQueryInsert: + qbuf << "INSERT INTO " << GetTablePrefix() << query.Table; + break; + case DbQueryUpdate: + qbuf << "UPDATE " << GetTablePrefix() << query.Table << " SET"; + break; + case DbQueryDelete: + qbuf << "DELETE FROM " << GetTablePrefix() << query.Table; + break; + default: + VERIFY(!"Invalid query type."); + } + + if (type == DbQueryInsert || type == DbQueryUpdate) { + std::ostringstream colbuf, valbuf; + + if (type == DbQueryUpdate && query.Fields->GetLength() == 0) + return; + + ObjectLock olock(query.Fields); + + bool first = true; + for (const Dictionary::Pair& kv : query.Fields) { + Value value; + + if (!FieldToEscapedString(kv.first, kv.second, &value)) { + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Scheduling execute query task again: Cannot extract required INSERT/UPDATE fields, key '" + << kv.first << "', val '" << kv.second << "', type " << typeOverride << ", table '" << query.Table << "'."; +#endif /* I2_DEBUG */ + + m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, -1); }, query.Priority); + return; + } + + if (type == DbQueryInsert) { + if (!first) { + colbuf << ", "; + valbuf << ", "; + } + + colbuf << kv.first; + valbuf << value; + } else { + if (!first) + qbuf << ", "; + + qbuf << " " << kv.first << " = " << value; + } + + if (first) + first = false; + } + + if (type == DbQueryInsert) + qbuf << " (" << colbuf.str() << ") VALUES (" << valbuf.str() << ")"; + } + + if (type != DbQueryInsert) + qbuf << where.str(); + + AsyncQuery(qbuf.str(), [this, query, type, upsert](const IdoMysqlResult&) { FinishExecuteQuery(query, type, upsert); }); +} + +void IdoMysqlConnection::FinishExecuteQuery(const DbQuery& query, int type, bool upsert) +{ + if (upsert && GetAffectedRows() == 0) { + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Rescheduling DELETE/INSERT query: Upsert UPDATE did not affect rows, type " << type << ", table '" << query.Table << "'."; +#endif /* I2_DEBUG */ + + IncreasePendingQueries(1); + m_QueryQueue.Enqueue([this, query]() { InternalExecuteQuery(query, DbQueryDelete | DbQueryInsert); }, query.Priority); + + return; + } + + if (type == DbQueryInsert && query.Object) { + if (query.ConfigUpdate) { + SetInsertID(query.Object, GetLastInsertID()); + SetConfigUpdate(query.Object, true); + } else if (query.StatusUpdate) + SetStatusUpdate(query.Object, true); + } + + if (type == DbQueryInsert && query.Table == "notifications" && query.NotificationInsertID) + query.NotificationInsertID->SetValue(static_cast(GetLastInsertID())); +} + +void IdoMysqlConnection::CleanUpExecuteQuery(const String& table, const String& time_column, double max_age) +{ + if (IsPaused()) + return; + +#ifdef I2_DEBUG /* I2_DEBUG */ + Log(LogDebug, "IdoMysqlConnection") + << "Rescheduling cleanup query for table '" << table << "' and column '" + << time_column << "'. max_age is set to '" << max_age << "'."; +#endif /* I2_DEBUG */ + + IncreasePendingQueries(1); + m_QueryQueue.Enqueue([this, table, time_column, max_age]() { InternalCleanUpExecuteQuery(table, time_column, max_age); }, PriorityLow, true); +} + +void IdoMysqlConnection::InternalCleanUpExecuteQuery(const String& table, const String& time_column, double max_age) +{ + AssertOnWorkQueue(); + + if (IsPaused()) { + DecreasePendingQueries(1); + return; + } + + if (!GetConnected()) { + DecreasePendingQueries(1); + return; + } + + AsyncQuery("DELETE FROM " + GetTablePrefix() + table + " WHERE instance_id = " + + Convert::ToString(static_cast(m_InstanceID)) + " AND " + time_column + + " < FROM_UNIXTIME(" + Convert::ToString(static_cast(max_age)) + ")"); +} + +void IdoMysqlConnection::FillIDCache(const DbType::Ptr& type) +{ + String query = "SELECT " + type->GetIDColumn() + " AS object_id, " + type->GetTable() + "_id, config_hash FROM " + GetTablePrefix() + type->GetTable() + "s"; + IdoMysqlResult result = Query(query); + + Dictionary::Ptr row; + + while ((row = FetchRow(result))) { + DbReference dbref(row->Get("object_id")); + SetInsertID(type, dbref, DbReference(row->Get(type->GetTable() + "_id"))); + SetConfigHash(type, dbref, row->Get("config_hash")); + } +} + +int IdoMysqlConnection::GetPendingQueryCount() const +{ + return m_QueryQueue.GetLength(); +} diff --git a/lib/db_ido_mysql/idomysqlconnection.hpp b/lib/db_ido_mysql/idomysqlconnection.hpp new file mode 100644 index 0000000..5a5c120 --- /dev/null +++ b/lib/db_ido_mysql/idomysqlconnection.hpp @@ -0,0 +1,114 @@ +/* Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ */ + +#ifndef IDOMYSQLCONNECTION_H +#define IDOMYSQLCONNECTION_H + +#include "db_ido_mysql/idomysqlconnection-ti.hpp" +#include "mysql_shim/mysqlinterface.hpp" +#include "base/array.hpp" +#include "base/timer.hpp" +#include "base/workqueue.hpp" +#include "base/library.hpp" +#include + +namespace icinga +{ + +typedef std::shared_ptr IdoMysqlResult; + +typedef std::function IdoAsyncCallback; + +struct IdoAsyncQuery +{ + String Query; + IdoAsyncCallback Callback; +}; + +/** + * An IDO MySQL database connection. + * + * @ingroup ido + */ +class IdoMysqlConnection final : public ObjectImpl +{ +public: + DECLARE_OBJECT(IdoMysqlConnection); + DECLARE_OBJECTNAME(IdoMysqlConnection); + + static void StatsFunc(const Dictionary::Ptr& status, const Array::Ptr& perfdata); + + const char * GetLatestSchemaVersion() const noexcept override; + const char * GetCompatSchemaVersion() const noexcept override; + + int GetPendingQueryCount() const override; + +protected: + void OnConfigLoaded() override; + void Resume() override; + void Pause() override; + + void ActivateObject(const DbObject::Ptr& dbobj) override; + void DeactivateObject(const DbObject::Ptr& dbobj) override; + void ExecuteQuery(const DbQuery& query) override; + void ExecuteMultipleQueries(const std::vector& queries) override; + void CleanUpExecuteQuery(const String& table, const String& time_key, double time_value) override; + void FillIDCache(const DbType::Ptr& type) override; + void NewTransaction() override; + void Disconnect() override; + +private: + DbReference m_InstanceID; + + Library m_Library; + std::unique_ptr m_Mysql; + + MYSQL m_Connection; + int m_AffectedRows; + unsigned int m_MaxPacketSize; + + std::vector m_AsyncQueries; + uint_fast32_t m_UncommittedAsyncQueries = 0; + + Timer::Ptr m_ReconnectTimer; + Timer::Ptr m_TxTimer; + + IdoMysqlResult Query(const String& query); + DbReference GetLastInsertID(); + int GetAffectedRows(); + String Escape(const String& s); + Dictionary::Ptr FetchRow(const IdoMysqlResult& result); + void DiscardRows(const IdoMysqlResult& result); + + void AsyncQuery(const String& query, const IdoAsyncCallback& callback = IdoAsyncCallback()); + void FinishAsyncQueries(); + + bool FieldToEscapedString(const String& key, const Value& value, Value *result); + void InternalActivateObject(const DbObject::Ptr& dbobj); + void InternalDeactivateObject(const DbObject::Ptr& dbobj); + + void Reconnect(); + + void AssertOnWorkQueue(); + + void ReconnectTimerHandler(); + + bool CanExecuteQuery(const DbQuery& query); + + void InternalExecuteQuery(const DbQuery& query, int typeOverride = -1); + void InternalExecuteMultipleQueries(const std::vector& queries); + + void FinishExecuteQuery(const DbQuery& query, int type, bool upsert); + void InternalCleanUpExecuteQuery(const String& table, const String& time_key, double time_value); + void InternalNewTransaction(); + + void ClearTableBySession(const String& table); + void ClearTablesBySession(); + + void ExceptionHandler(boost::exception_ptr exp); + + void FinishConnect(double startTime); +}; + +} + +#endif /* IDOMYSQLCONNECTION_H */ diff --git a/lib/db_ido_mysql/idomysqlconnection.ti b/lib/db_ido_mysql/idomysqlconnection.ti new file mode 100644 index 0000000..681148f --- /dev/null +++ b/lib/db_ido_mysql/idomysqlconnection.ti @@ -0,0 +1,42 @@ +/* Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ */ + +#include "db_ido/dbconnection.hpp" + +library db_ido_mysql; + +namespace icinga +{ + +class IdoMysqlConnection : DbConnection +{ + activation_priority 100; + + [config] String host { + default {{{ return "localhost"; }}} + }; + [config] int port { + default {{{ return 3306; }}} + }; + [config] String socket_path; + [config] String user { + default {{{ return "icinga"; }}} + }; + [config, no_user_view, no_user_modify] String password { + default {{{ return "icinga"; }}} + }; + [config] String database { + default {{{ return "icinga"; }}} + }; + [config] bool enable_ssl; + [config] String ssl_key; + [config] String ssl_cert; + [config] String ssl_ca; + [config] String ssl_capath; + [config] String ssl_cipher; + [config] String instance_name { + default {{{ return "default"; }}} + }; + [config] String instance_description; +}; + +} diff --git a/lib/db_ido_mysql/schema/mysql.sql b/lib/db_ido_mysql/schema/mysql.sql new file mode 100644 index 0000000..020ba3c --- /dev/null +++ b/lib/db_ido_mysql/schema/mysql.sql @@ -0,0 +1,1666 @@ +-- -------------------------------------------------------- +-- mysql.sql +-- DB definition for IDO MySQL +-- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- -- -------------------------------------------------------- + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; + +-- +-- Database: icinga +-- + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_acknowledgements +-- + +CREATE TABLE IF NOT EXISTS icinga_acknowledgements ( + acknowledgement_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + entry_time timestamp NULL, + entry_time_usec int default 0, + acknowledgement_type smallint default 0, + object_id bigint unsigned default 0, + state smallint default 0, + author_name varchar(64) character set latin1 default '', + comment_data TEXT character set latin1, + is_sticky smallint default 0, + persistent_comment smallint default 0, + notify_contacts smallint default 0, + end_time timestamp NULL, + PRIMARY KEY (acknowledgement_id) +) ENGINE=InnoDB COMMENT='Current and historical host and service acknowledgements'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_commands +-- + +CREATE TABLE IF NOT EXISTS icinga_commands ( + command_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + object_id bigint unsigned default 0, + command_line TEXT character set latin1, + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (command_id), + UNIQUE KEY instance_id (instance_id,object_id,config_type) +) ENGINE=InnoDB COMMENT='Command definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_commenthistory +-- + +CREATE TABLE IF NOT EXISTS icinga_commenthistory ( + commenthistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + entry_time timestamp NULL, + entry_time_usec int default 0, + comment_type smallint default 0, + entry_type smallint default 0, + object_id bigint unsigned default 0, + comment_time timestamp NULL, + internal_comment_id bigint unsigned default 0, + author_name varchar(64) character set latin1 default '', + comment_data TEXT character set latin1, + is_persistent smallint default 0, + comment_source smallint default 0, + expires smallint default 0, + expiration_time timestamp NULL, + deletion_time timestamp NULL, + deletion_time_usec int default 0, + name TEXT character set latin1 default NULL, + PRIMARY KEY (commenthistory_id) +) ENGINE=InnoDB COMMENT='Historical host and service comments'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_comments +-- + +CREATE TABLE IF NOT EXISTS icinga_comments ( + comment_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + entry_time timestamp NULL, + entry_time_usec int default 0, + comment_type smallint default 0, + entry_type smallint default 0, + object_id bigint unsigned default 0, + comment_time timestamp NULL, + internal_comment_id bigint unsigned default 0, + author_name varchar(64) character set latin1 default '', + comment_data TEXT character set latin1, + is_persistent smallint default 0, + comment_source smallint default 0, + expires smallint default 0, + expiration_time timestamp NULL, + name TEXT character set latin1 default NULL, + session_token int default NULL, + PRIMARY KEY (comment_id) +) ENGINE=InnoDB COMMENT='Usercomments on Icinga objects'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_configfiles +-- + +CREATE TABLE IF NOT EXISTS icinga_configfiles ( + configfile_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + configfile_type smallint default 0, + configfile_path varchar(255) character set latin1 default '', + PRIMARY KEY (configfile_id), + UNIQUE KEY instance_id (instance_id,configfile_type,configfile_path) +) ENGINE=InnoDB COMMENT='Configuration files'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_configfilevariables +-- + +CREATE TABLE IF NOT EXISTS icinga_configfilevariables ( + configfilevariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + configfile_id bigint unsigned default 0, + varname varchar(64) character set latin1 default '', + varvalue TEXT character set latin1, + PRIMARY KEY (configfilevariable_id) +) ENGINE=InnoDB COMMENT='Configuration file variables'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_conninfo +-- + +CREATE TABLE IF NOT EXISTS icinga_conninfo ( + conninfo_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + agent_name varchar(32) character set latin1 default '', + agent_version varchar(32) character set latin1 default '', + disposition varchar(32) character set latin1 default '', + connect_source varchar(32) character set latin1 default '', + connect_type varchar(32) character set latin1 default '', + connect_time timestamp NULL, + disconnect_time timestamp NULL, + last_checkin_time timestamp NULL, + data_start_time timestamp NULL, + data_end_time timestamp NULL, + bytes_processed bigint unsigned default '0', + lines_processed bigint unsigned default '0', + entries_processed bigint unsigned default '0', + PRIMARY KEY (conninfo_id) +) ENGINE=InnoDB COMMENT='IDO2DB daemon connection information'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contactgroups +-- + +CREATE TABLE IF NOT EXISTS icinga_contactgroups ( + contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + contactgroup_object_id bigint unsigned default 0, + alias varchar(255) character set latin1 default '', + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (contactgroup_id), + UNIQUE KEY instance_id (instance_id,config_type,contactgroup_object_id) +) ENGINE=InnoDB COMMENT='Contactgroup definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contactgroup_members +-- + +CREATE TABLE IF NOT EXISTS icinga_contactgroup_members ( + contactgroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + contactgroup_id bigint unsigned default 0, + contact_object_id bigint unsigned default 0, + PRIMARY KEY (contactgroup_member_id) +) ENGINE=InnoDB COMMENT='Contactgroup members'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contactnotificationmethods +-- + +CREATE TABLE IF NOT EXISTS icinga_contactnotificationmethods ( + contactnotificationmethod_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + contactnotification_id bigint unsigned default 0, + start_time timestamp NULL, + start_time_usec int default 0, + end_time timestamp NULL, + end_time_usec int default 0, + command_object_id bigint unsigned default 0, + command_args TEXT character set latin1, + PRIMARY KEY (contactnotificationmethod_id), + UNIQUE KEY instance_id (instance_id,contactnotification_id,start_time,start_time_usec) +) ENGINE=InnoDB COMMENT='Historical record of contact notification methods'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contactnotifications +-- + +CREATE TABLE IF NOT EXISTS icinga_contactnotifications ( + contactnotification_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + notification_id bigint unsigned default 0, + contact_object_id bigint unsigned default 0, + start_time timestamp NULL, + start_time_usec int default 0, + end_time timestamp NULL, + end_time_usec int default 0, + PRIMARY KEY (contactnotification_id), + UNIQUE KEY instance_id (instance_id,contact_object_id,start_time,start_time_usec) +) ENGINE=InnoDB COMMENT='Historical record of contact notifications'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contacts +-- + +CREATE TABLE IF NOT EXISTS icinga_contacts ( + contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + contact_object_id bigint unsigned default 0, + alias varchar(255) character set latin1 default '', + email_address varchar(255) character set latin1 default '', + pager_address varchar(64) character set latin1 default '', + host_timeperiod_object_id bigint unsigned default 0, + service_timeperiod_object_id bigint unsigned default 0, + host_notifications_enabled smallint default 0, + service_notifications_enabled smallint default 0, + can_submit_commands smallint default 0, + notify_service_recovery smallint default 0, + notify_service_warning smallint default 0, + notify_service_unknown smallint default 0, + notify_service_critical smallint default 0, + notify_service_flapping smallint default 0, + notify_service_downtime smallint default 0, + notify_host_recovery smallint default 0, + notify_host_down smallint default 0, + notify_host_unreachable smallint default 0, + notify_host_flapping smallint default 0, + notify_host_downtime smallint default 0, + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (contact_id), + UNIQUE KEY instance_id (instance_id,config_type,contact_object_id) +) ENGINE=InnoDB COMMENT='Contact definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contactstatus +-- + +CREATE TABLE IF NOT EXISTS icinga_contactstatus ( + contactstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + contact_object_id bigint unsigned default 0, + status_update_time timestamp NULL, + host_notifications_enabled smallint default 0, + service_notifications_enabled smallint default 0, + last_host_notification timestamp NULL, + last_service_notification timestamp NULL, + modified_attributes int default 0, + modified_host_attributes int default 0, + modified_service_attributes int default 0, + PRIMARY KEY (contactstatus_id), + UNIQUE KEY contact_object_id (contact_object_id) +) ENGINE=InnoDB COMMENT='Contact status'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contact_addresses +-- + +CREATE TABLE IF NOT EXISTS icinga_contact_addresses ( + contact_address_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + contact_id bigint unsigned default 0, + address_number smallint default 0, + address varchar(255) character set latin1 default '', + PRIMARY KEY (contact_address_id), + UNIQUE KEY contact_id (contact_id,address_number) +) ENGINE=InnoDB COMMENT='Contact addresses'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_contact_notificationcommands +-- + +CREATE TABLE IF NOT EXISTS icinga_contact_notificationcommands ( + contact_notificationcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + contact_id bigint unsigned default 0, + notification_type smallint default 0, + command_object_id bigint unsigned default 0, + command_args varchar(255) character set latin1 default '', + PRIMARY KEY (contact_notificationcommand_id), + UNIQUE KEY contact_id (contact_id,notification_type,command_object_id,command_args) +) ENGINE=InnoDB COMMENT='Contact host and service notification commands'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_customvariables +-- + +CREATE TABLE IF NOT EXISTS icinga_customvariables ( + customvariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + object_id bigint unsigned default 0, + config_type smallint default 0, + has_been_modified smallint default 0, + varname varchar(255) character set latin1 collate latin1_general_cs default NULL, + varvalue TEXT character set latin1, + is_json smallint default 0, + PRIMARY KEY (customvariable_id), + UNIQUE KEY object_id_2 (object_id,config_type,varname), + KEY varname (varname) +) ENGINE=InnoDB COMMENT='Custom variables'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_customvariablestatus +-- + +CREATE TABLE IF NOT EXISTS icinga_customvariablestatus ( + customvariablestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + object_id bigint unsigned default 0, + status_update_time timestamp NULL, + has_been_modified smallint default 0, + varname varchar(255) character set latin1 collate latin1_general_cs default NULL, + varvalue TEXT character set latin1, + is_json smallint default 0, + PRIMARY KEY (customvariablestatus_id), + UNIQUE KEY object_id_2 (object_id,varname), + KEY varname (varname) +) ENGINE=InnoDB COMMENT='Custom variable status information'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_dbversion +-- + +CREATE TABLE IF NOT EXISTS icinga_dbversion ( + dbversion_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + name varchar(10) character set latin1 default '', + version varchar(10) character set latin1 default '', + create_time timestamp NULL, + modify_time timestamp NULL, + PRIMARY KEY (dbversion_id), + UNIQUE KEY dbversion (name) +) ENGINE=InnoDB; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_downtimehistory +-- + +CREATE TABLE IF NOT EXISTS icinga_downtimehistory ( + downtimehistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + downtime_type smallint default 0, + object_id bigint unsigned default 0, + entry_time timestamp NULL, + author_name varchar(64) character set latin1 default '', + comment_data TEXT character set latin1, + internal_downtime_id bigint unsigned default 0, + triggered_by_id bigint unsigned default 0, + is_fixed smallint default 0, + duration bigint(20) default 0, + scheduled_start_time timestamp NULL, + scheduled_end_time timestamp NULL, + was_started smallint default 0, + actual_start_time timestamp NULL, + actual_start_time_usec int default 0, + actual_end_time timestamp NULL, + actual_end_time_usec int default 0, + was_cancelled smallint default 0, + is_in_effect smallint default 0, + trigger_time timestamp NULL, + name TEXT character set latin1 default NULL, + PRIMARY KEY (downtimehistory_id) +) ENGINE=InnoDB COMMENT='Historical scheduled host and service downtime'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_eventhandlers +-- + +CREATE TABLE IF NOT EXISTS icinga_eventhandlers ( + eventhandler_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + eventhandler_type smallint default 0, + object_id bigint unsigned default 0, + state smallint default 0, + state_type smallint default 0, + start_time timestamp NULL, + start_time_usec int default 0, + end_time timestamp NULL, + end_time_usec int default 0, + command_object_id bigint unsigned default 0, + command_args TEXT character set latin1, + command_line TEXT character set latin1, + timeout smallint default 0, + early_timeout smallint default 0, + execution_time double default '0', + return_code smallint default 0, + output TEXT character set latin1, + long_output TEXT, + PRIMARY KEY (eventhandler_id), + UNIQUE KEY instance_id (instance_id,object_id,start_time,start_time_usec) +) ENGINE=InnoDB COMMENT='Historical host and service event handlers'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_externalcommands +-- + +CREATE TABLE IF NOT EXISTS icinga_externalcommands ( + externalcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + entry_time timestamp NULL, + command_type smallint default 0, + command_name varchar(128) character set latin1 default '', + command_args TEXT character set latin1, + PRIMARY KEY (externalcommand_id) +) ENGINE=InnoDB COMMENT='Historical record of processed external commands'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_flappinghistory +-- + +CREATE TABLE IF NOT EXISTS icinga_flappinghistory ( + flappinghistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + event_time timestamp NULL, + event_time_usec int default 0, + event_type smallint default 0, + reason_type smallint default 0, + flapping_type smallint default 0, + object_id bigint unsigned default 0, + percent_state_change double default '0', + low_threshold double default '0', + high_threshold double default '0', + comment_time timestamp NULL, + internal_comment_id bigint unsigned default 0, + PRIMARY KEY (flappinghistory_id) +) ENGINE=InnoDB COMMENT='Current and historical record of host and service flapping'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hostchecks +-- + +CREATE TABLE IF NOT EXISTS icinga_hostchecks ( + hostcheck_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + host_object_id bigint unsigned default 0, + check_type smallint default 0, + is_raw_check smallint default 0, + current_check_attempt smallint default 0, + max_check_attempts smallint default 0, + state smallint default 0, + state_type smallint default 0, + start_time timestamp NULL, + start_time_usec int default 0, + end_time timestamp NULL, + end_time_usec int default 0, + command_object_id bigint unsigned default 0, + command_args TEXT character set latin1, + command_line TEXT character set latin1, + timeout smallint default 0, + early_timeout smallint default 0, + execution_time double default '0', + latency double default '0', + return_code smallint default 0, + output TEXT character set latin1, + long_output TEXT, + perfdata TEXT character set latin1, + PRIMARY KEY (hostcheck_id) +) ENGINE=InnoDB COMMENT='Historical host checks'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hostdependencies +-- + +CREATE TABLE IF NOT EXISTS icinga_hostdependencies ( + hostdependency_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + host_object_id bigint unsigned default 0, + dependent_host_object_id bigint unsigned default 0, + dependency_type smallint default 0, + inherits_parent smallint default 0, + timeperiod_object_id bigint unsigned default 0, + fail_on_up smallint default 0, + fail_on_down smallint default 0, + fail_on_unreachable smallint default 0, + PRIMARY KEY (hostdependency_id), + KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable) +) ENGINE=InnoDB COMMENT='Host dependency definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hostescalations +-- + +CREATE TABLE IF NOT EXISTS icinga_hostescalations ( + hostescalation_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + host_object_id bigint unsigned default 0, + timeperiod_object_id bigint unsigned default 0, + first_notification smallint default 0, + last_notification smallint default 0, + notification_interval double default '0', + escalate_on_recovery smallint default 0, + escalate_on_down smallint default 0, + escalate_on_unreachable smallint default 0, + PRIMARY KEY (hostescalation_id), + UNIQUE KEY instance_id (instance_id,config_type,host_object_id,timeperiod_object_id,first_notification,last_notification) +) ENGINE=InnoDB COMMENT='Host escalation definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hostescalation_contactgroups +-- + +CREATE TABLE IF NOT EXISTS icinga_hostescalation_contactgroups ( + hostescalation_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + hostescalation_id bigint unsigned default 0, + contactgroup_object_id bigint unsigned default 0, + PRIMARY KEY (hostescalation_contactgroup_id), + UNIQUE KEY instance_id (hostescalation_id,contactgroup_object_id) +) ENGINE=InnoDB COMMENT='Host escalation contact groups'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hostescalation_contacts +-- + +CREATE TABLE IF NOT EXISTS icinga_hostescalation_contacts ( + hostescalation_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + hostescalation_id bigint unsigned default 0, + contact_object_id bigint unsigned default 0, + PRIMARY KEY (hostescalation_contact_id), + UNIQUE KEY instance_id (instance_id,hostescalation_id,contact_object_id) +) ENGINE=InnoDB COMMENT='Host escalation contacts'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hostgroups +-- + +CREATE TABLE IF NOT EXISTS icinga_hostgroups ( + hostgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + hostgroup_object_id bigint unsigned default 0, + alias varchar(255) character set latin1 default '', + notes TEXT character set latin1 default NULL, + notes_url TEXT character set latin1 default NULL, + action_url TEXT character set latin1 default NULL, + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (hostgroup_id), + UNIQUE KEY instance_id (instance_id,hostgroup_object_id) +) ENGINE=InnoDB COMMENT='Hostgroup definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hostgroup_members +-- + +CREATE TABLE IF NOT EXISTS icinga_hostgroup_members ( + hostgroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + hostgroup_id bigint unsigned default 0, + host_object_id bigint unsigned default 0, + PRIMARY KEY (hostgroup_member_id) +) ENGINE=InnoDB COMMENT='Hostgroup members'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hosts +-- + +CREATE TABLE IF NOT EXISTS icinga_hosts ( + host_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + host_object_id bigint unsigned default 0, + alias varchar(255) character set latin1 default '', + display_name varchar(255) character set latin1 collate latin1_general_cs default '', + address varchar(128) character set latin1 default '', + address6 varchar(128) character set latin1 default '', + check_command_object_id bigint unsigned default 0, + check_command_args TEXT character set latin1, + eventhandler_command_object_id bigint unsigned default 0, + eventhandler_command_args TEXT character set latin1, + notification_timeperiod_object_id bigint unsigned default 0, + check_timeperiod_object_id bigint unsigned default 0, + failure_prediction_options varchar(128) character set latin1 default '', + check_interval double default '0', + retry_interval double default '0', + max_check_attempts smallint default 0, + first_notification_delay double default '0', + notification_interval double default '0', + notify_on_down smallint default 0, + notify_on_unreachable smallint default 0, + notify_on_recovery smallint default 0, + notify_on_flapping smallint default 0, + notify_on_downtime smallint default 0, + stalk_on_up smallint default 0, + stalk_on_down smallint default 0, + stalk_on_unreachable smallint default 0, + flap_detection_enabled smallint default 0, + flap_detection_on_up smallint default 0, + flap_detection_on_down smallint default 0, + flap_detection_on_unreachable smallint default 0, + low_flap_threshold double default '0', + high_flap_threshold double default '0', + process_performance_data smallint default 0, + freshness_checks_enabled smallint default 0, + freshness_threshold int default 0, + passive_checks_enabled smallint default 0, + event_handler_enabled smallint default 0, + active_checks_enabled smallint default 0, + retain_status_information smallint default 0, + retain_nonstatus_information smallint default 0, + notifications_enabled smallint default 0, + obsess_over_host smallint default 0, + failure_prediction_enabled smallint default 0, + notes TEXT character set latin1, + notes_url TEXT character set latin1, + action_url TEXT character set latin1, + icon_image TEXT character set latin1, + icon_image_alt TEXT character set latin1, + vrml_image TEXT character set latin1, + statusmap_image TEXT character set latin1, + have_2d_coords smallint default 0, + x_2d smallint default 0, + y_2d smallint default 0, + have_3d_coords smallint default 0, + x_3d double default '0', + y_3d double default '0', + z_3d double default '0', + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (host_id), + UNIQUE KEY instance_id (instance_id,config_type,host_object_id) +) ENGINE=InnoDB COMMENT='Host definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_hoststatus +-- + +CREATE TABLE IF NOT EXISTS icinga_hoststatus ( + hoststatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + host_object_id bigint unsigned default 0, + status_update_time timestamp NULL, + output TEXT character set latin1, + long_output TEXT, + perfdata TEXT character set latin1, + check_source varchar(255) character set latin1 default '', + current_state smallint default 0, + has_been_checked smallint default 0, + should_be_scheduled smallint default 0, + current_check_attempt smallint default 0, + max_check_attempts smallint default 0, + last_check timestamp NULL, + next_check timestamp NULL, + check_type smallint default 0, + last_state_change timestamp NULL, + last_hard_state_change timestamp NULL, + last_hard_state smallint default 0, + last_time_up timestamp NULL, + last_time_down timestamp NULL, + last_time_unreachable timestamp NULL, + state_type smallint default 0, + last_notification timestamp NULL, + next_notification timestamp NULL, + no_more_notifications smallint default 0, + notifications_enabled smallint default 0, + problem_has_been_acknowledged smallint default 0, + acknowledgement_type smallint default 0, + current_notification_number int unsigned default 0, + passive_checks_enabled smallint default 0, + active_checks_enabled smallint default 0, + event_handler_enabled smallint default 0, + flap_detection_enabled smallint default 0, + is_flapping smallint default 0, + percent_state_change double default '0', + latency double default '0', + execution_time double default '0', + scheduled_downtime_depth smallint default 0, + failure_prediction_enabled smallint default 0, + process_performance_data smallint default 0, + obsess_over_host smallint default 0, + modified_host_attributes int default 0, + original_attributes TEXT character set latin1 default NULL, + event_handler TEXT character set latin1, + check_command TEXT character set latin1, + normal_check_interval double default '0', + retry_check_interval double default '0', + check_timeperiod_object_id bigint unsigned default 0, + is_reachable smallint default 0, + PRIMARY KEY (hoststatus_id), + UNIQUE KEY object_id (host_object_id) +) ENGINE=InnoDB COMMENT='Current host status information'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_host_contactgroups +-- + +CREATE TABLE IF NOT EXISTS icinga_host_contactgroups ( + host_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + host_id bigint unsigned default 0, + contactgroup_object_id bigint unsigned default 0, + PRIMARY KEY (host_contactgroup_id) +) ENGINE=InnoDB COMMENT='Host contact groups'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_host_contacts +-- + +CREATE TABLE IF NOT EXISTS icinga_host_contacts ( + host_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + host_id bigint unsigned default 0, + contact_object_id bigint unsigned default 0, + PRIMARY KEY (host_contact_id) +) ENGINE=InnoDB COMMENT='Host contacts'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_host_parenthosts +-- + +CREATE TABLE IF NOT EXISTS icinga_host_parenthosts ( + host_parenthost_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + host_id bigint unsigned default 0, + parent_host_object_id bigint unsigned default 0, + PRIMARY KEY (host_parenthost_id) +) ENGINE=InnoDB COMMENT='Parent hosts'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_instances +-- + +CREATE TABLE IF NOT EXISTS icinga_instances ( + instance_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_name varchar(64) character set latin1 default '', + instance_description varchar(128) character set latin1 default '', + PRIMARY KEY (instance_id) +) ENGINE=InnoDB COMMENT='Location names of various Icinga installations'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_logentries +-- + +CREATE TABLE IF NOT EXISTS icinga_logentries ( + logentry_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + logentry_time timestamp NULL, + entry_time timestamp NULL, + entry_time_usec int default 0, + logentry_type int default 0, + logentry_data TEXT character set latin1, + realtime_data smallint default 0, + inferred_data_extracted smallint default 0, + object_id bigint unsigned default NULL, + PRIMARY KEY (logentry_id) +) ENGINE=InnoDB COMMENT='Historical record of log entries'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_notifications +-- + +CREATE TABLE IF NOT EXISTS icinga_notifications ( + notification_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + notification_type smallint default 0, + notification_reason smallint default 0, + object_id bigint unsigned default 0, + start_time timestamp NULL, + start_time_usec int default 0, + end_time timestamp NULL, + end_time_usec int default 0, + state smallint default 0, + output TEXT character set latin1, + long_output TEXT, + escalated smallint default 0, + contacts_notified smallint default 0, + PRIMARY KEY (notification_id), + UNIQUE KEY instance_id (instance_id,object_id,start_time,start_time_usec) +) ENGINE=InnoDB COMMENT='Historical record of host and service notifications'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_objects +-- + +CREATE TABLE IF NOT EXISTS icinga_objects ( + object_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + objecttype_id bigint unsigned default 0, + name1 varchar(255) character set latin1 collate latin1_general_cs default '', + name2 varchar(255) character set latin1 collate latin1_general_cs default NULL, + is_active smallint default 0, + PRIMARY KEY (object_id), + KEY objecttype_id (objecttype_id,name1,name2) +) ENGINE=InnoDB COMMENT='Current and historical objects of all kinds'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_processevents +-- + +CREATE TABLE IF NOT EXISTS icinga_processevents ( + processevent_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + event_type smallint default 0, + event_time timestamp NULL, + event_time_usec int default 0, + process_id bigint unsigned default 0, + program_name varchar(16) character set latin1 default '', + program_version varchar(20) character set latin1 default '', + program_date varchar(10) character set latin1 default '', + PRIMARY KEY (processevent_id) +) ENGINE=InnoDB COMMENT='Historical Icinga process events'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_programstatus +-- + +CREATE TABLE IF NOT EXISTS icinga_programstatus ( + programstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + program_version varchar(64) character set latin1 collate latin1_general_cs default NULL, + status_update_time timestamp NULL, + program_start_time timestamp NULL, + program_end_time timestamp NULL, + endpoint_name varchar(255) character set latin1 collate latin1_general_cs default NULL, + is_currently_running smallint default 0, + process_id bigint unsigned default 0, + daemon_mode smallint default 0, + last_command_check timestamp NULL, + last_log_rotation timestamp NULL, + notifications_enabled smallint default 0, + disable_notif_expire_time timestamp NULL, + active_service_checks_enabled smallint default 0, + passive_service_checks_enabled smallint default 0, + active_host_checks_enabled smallint default 0, + passive_host_checks_enabled smallint default 0, + event_handlers_enabled smallint default 0, + flap_detection_enabled smallint default 0, + failure_prediction_enabled smallint default 0, + process_performance_data smallint default 0, + obsess_over_hosts smallint default 0, + obsess_over_services smallint default 0, + modified_host_attributes int default 0, + modified_service_attributes int default 0, + global_host_event_handler TEXT character set latin1, + global_service_event_handler TEXT character set latin1, + config_dump_in_progress smallint default 0, + PRIMARY KEY (programstatus_id), + UNIQUE KEY instance_id (instance_id) +) ENGINE=InnoDB COMMENT='Current program status information'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_runtimevariables +-- + +CREATE TABLE IF NOT EXISTS icinga_runtimevariables ( + runtimevariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + varname varchar(64) character set latin1 default '', + varvalue TEXT character set latin1, + PRIMARY KEY (runtimevariable_id) +) ENGINE=InnoDB COMMENT='Runtime variables from the Icinga daemon'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_scheduleddowntime +-- + +CREATE TABLE IF NOT EXISTS icinga_scheduleddowntime ( + scheduleddowntime_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + downtime_type smallint default 0, + object_id bigint unsigned default 0, + entry_time timestamp NULL, + author_name varchar(64) character set latin1 default '', + comment_data TEXT character set latin1, + internal_downtime_id bigint unsigned default 0, + triggered_by_id bigint unsigned default 0, + is_fixed smallint default 0, + duration bigint(20) default 0, + scheduled_start_time timestamp NULL, + scheduled_end_time timestamp NULL, + was_started smallint default 0, + actual_start_time timestamp NULL, + actual_start_time_usec int default 0, + is_in_effect smallint default 0, + trigger_time timestamp NULL, + name TEXT character set latin1 default NULL, + session_token int default NULL, + PRIMARY KEY (scheduleddowntime_id) +) ENGINE=InnoDB COMMENT='Current scheduled host and service downtime'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_servicechecks +-- + +CREATE TABLE IF NOT EXISTS icinga_servicechecks ( + servicecheck_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + service_object_id bigint unsigned default 0, + check_type smallint default 0, + current_check_attempt smallint default 0, + max_check_attempts smallint default 0, + state smallint default 0, + state_type smallint default 0, + start_time timestamp NULL, + start_time_usec int default 0, + end_time timestamp NULL, + end_time_usec int default 0, + command_object_id bigint unsigned default 0, + command_args TEXT character set latin1, + command_line TEXT character set latin1, + timeout smallint default 0, + early_timeout smallint default 0, + execution_time double default '0', + latency double default '0', + return_code smallint default 0, + output TEXT character set latin1, + long_output TEXT, + perfdata TEXT character set latin1, + PRIMARY KEY (servicecheck_id) +) ENGINE=InnoDB COMMENT='Historical service checks'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_servicedependencies +-- + +CREATE TABLE IF NOT EXISTS icinga_servicedependencies ( + servicedependency_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + service_object_id bigint unsigned default 0, + dependent_service_object_id bigint unsigned default 0, + dependency_type smallint default 0, + inherits_parent smallint default 0, + timeperiod_object_id bigint unsigned default 0, + fail_on_ok smallint default 0, + fail_on_warning smallint default 0, + fail_on_unknown smallint default 0, + fail_on_critical smallint default 0, + PRIMARY KEY (servicedependency_id), + KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical) +) ENGINE=InnoDB COMMENT='Service dependency definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_serviceescalations +-- + +CREATE TABLE IF NOT EXISTS icinga_serviceescalations ( + serviceescalation_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + service_object_id bigint unsigned default 0, + timeperiod_object_id bigint unsigned default 0, + first_notification smallint default 0, + last_notification smallint default 0, + notification_interval double default '0', + escalate_on_recovery smallint default 0, + escalate_on_warning smallint default 0, + escalate_on_unknown smallint default 0, + escalate_on_critical smallint default 0, + PRIMARY KEY (serviceescalation_id), + UNIQUE KEY instance_id (instance_id,config_type,service_object_id,timeperiod_object_id,first_notification,last_notification) +) ENGINE=InnoDB COMMENT='Service escalation definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_serviceescalation_contactgroups +-- + +CREATE TABLE IF NOT EXISTS icinga_serviceescalation_contactgroups ( + serviceescalation_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + serviceescalation_id bigint unsigned default 0, + contactgroup_object_id bigint unsigned default 0, + PRIMARY KEY (serviceescalation_contactgroup_id), + UNIQUE KEY instance_id (serviceescalation_id,contactgroup_object_id) +) ENGINE=InnoDB COMMENT='Service escalation contact groups'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_serviceescalation_contacts +-- + +CREATE TABLE IF NOT EXISTS icinga_serviceescalation_contacts ( + serviceescalation_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + serviceescalation_id bigint unsigned default 0, + contact_object_id bigint unsigned default 0, + PRIMARY KEY (serviceescalation_contact_id), + UNIQUE KEY instance_id (instance_id,serviceescalation_id,contact_object_id) +) ENGINE=InnoDB COMMENT='Service escalation contacts'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_servicegroups +-- + +CREATE TABLE IF NOT EXISTS icinga_servicegroups ( + servicegroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + servicegroup_object_id bigint unsigned default 0, + alias varchar(255) character set latin1 default '', + notes TEXT character set latin1 default NULL, + notes_url TEXT character set latin1 default NULL, + action_url TEXT character set latin1 default NULL, + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (servicegroup_id), + UNIQUE KEY instance_id (instance_id,config_type,servicegroup_object_id) +) ENGINE=InnoDB COMMENT='Servicegroup definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_servicegroup_members +-- + +CREATE TABLE IF NOT EXISTS icinga_servicegroup_members ( + servicegroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + servicegroup_id bigint unsigned default 0, + service_object_id bigint unsigned default 0, + PRIMARY KEY (servicegroup_member_id) +) ENGINE=InnoDB COMMENT='Servicegroup members'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_services +-- + +CREATE TABLE IF NOT EXISTS icinga_services ( + service_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + host_object_id bigint unsigned default 0, + service_object_id bigint unsigned default 0, + display_name varchar(255) character set latin1 collate latin1_general_cs default '', + check_command_object_id bigint unsigned default 0, + check_command_args TEXT character set latin1, + eventhandler_command_object_id bigint unsigned default 0, + eventhandler_command_args TEXT character set latin1, + notification_timeperiod_object_id bigint unsigned default 0, + check_timeperiod_object_id bigint unsigned default 0, + failure_prediction_options varchar(64) character set latin1 default '', + check_interval double default '0', + retry_interval double default '0', + max_check_attempts smallint default 0, + first_notification_delay double default '0', + notification_interval double default '0', + notify_on_warning smallint default 0, + notify_on_unknown smallint default 0, + notify_on_critical smallint default 0, + notify_on_recovery smallint default 0, + notify_on_flapping smallint default 0, + notify_on_downtime smallint default 0, + stalk_on_ok smallint default 0, + stalk_on_warning smallint default 0, + stalk_on_unknown smallint default 0, + stalk_on_critical smallint default 0, + is_volatile smallint default 0, + flap_detection_enabled smallint default 0, + flap_detection_on_ok smallint default 0, + flap_detection_on_warning smallint default 0, + flap_detection_on_unknown smallint default 0, + flap_detection_on_critical smallint default 0, + low_flap_threshold double default '0', + high_flap_threshold double default '0', + process_performance_data smallint default 0, + freshness_checks_enabled smallint default 0, + freshness_threshold int default 0, + passive_checks_enabled smallint default 0, + event_handler_enabled smallint default 0, + active_checks_enabled smallint default 0, + retain_status_information smallint default 0, + retain_nonstatus_information smallint default 0, + notifications_enabled smallint default 0, + obsess_over_service smallint default 0, + failure_prediction_enabled smallint default 0, + notes TEXT character set latin1, + notes_url TEXT character set latin1, + action_url TEXT character set latin1, + icon_image TEXT character set latin1, + icon_image_alt TEXT character set latin1, + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (service_id), + UNIQUE KEY instance_id (instance_id,config_type,service_object_id) +) ENGINE=InnoDB COMMENT='Service definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_servicestatus +-- + +CREATE TABLE IF NOT EXISTS icinga_servicestatus ( + servicestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + service_object_id bigint unsigned default 0, + status_update_time timestamp NULL, + output TEXT character set latin1, + long_output TEXT, + perfdata TEXT character set latin1, + check_source varchar(255) character set latin1 default '', + current_state smallint default 0, + has_been_checked smallint default 0, + should_be_scheduled smallint default 0, + current_check_attempt smallint default 0, + max_check_attempts smallint default 0, + last_check timestamp NULL, + next_check timestamp NULL, + check_type smallint default 0, + last_state_change timestamp NULL, + last_hard_state_change timestamp NULL, + last_hard_state smallint default 0, + last_time_ok timestamp NULL, + last_time_warning timestamp NULL, + last_time_unknown timestamp NULL, + last_time_critical timestamp NULL, + state_type smallint default 0, + last_notification timestamp NULL, + next_notification timestamp NULL, + no_more_notifications smallint default 0, + notifications_enabled smallint default 0, + problem_has_been_acknowledged smallint default 0, + acknowledgement_type smallint default 0, + current_notification_number int unsigned default 0, + passive_checks_enabled smallint default 0, + active_checks_enabled smallint default 0, + event_handler_enabled smallint default 0, + flap_detection_enabled smallint default 0, + is_flapping smallint default 0, + percent_state_change double default '0', + latency double default '0', + execution_time double default '0', + scheduled_downtime_depth smallint default 0, + failure_prediction_enabled smallint default 0, + process_performance_data smallint default 0, + obsess_over_service smallint default 0, + modified_service_attributes int default 0, + original_attributes TEXT character set latin1 default NULL, + event_handler TEXT character set latin1, + check_command TEXT character set latin1, + normal_check_interval double default '0', + retry_check_interval double default '0', + check_timeperiod_object_id bigint unsigned default 0, + is_reachable smallint default 0, + PRIMARY KEY (servicestatus_id), + UNIQUE KEY object_id (service_object_id) +) ENGINE=InnoDB COMMENT='Current service status information'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_service_contactgroups +-- + +CREATE TABLE IF NOT EXISTS icinga_service_contactgroups ( + service_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + service_id bigint unsigned default 0, + contactgroup_object_id bigint unsigned default 0, + PRIMARY KEY (service_contactgroup_id) +) ENGINE=InnoDB COMMENT='Service contact groups'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_service_contacts +-- + +CREATE TABLE IF NOT EXISTS icinga_service_contacts ( + service_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + service_id bigint unsigned default 0, + contact_object_id bigint unsigned default 0, + PRIMARY KEY (service_contact_id) +) ENGINE=InnoDB COMMENT='Service contacts'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_statehistory +-- + +CREATE TABLE IF NOT EXISTS icinga_statehistory ( + statehistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + state_time timestamp NULL, + state_time_usec int default 0, + object_id bigint unsigned default 0, + state_change smallint default 0, + state smallint default 0, + state_type smallint default 0, + current_check_attempt smallint default 0, + max_check_attempts smallint default 0, + last_state smallint default 0, + last_hard_state smallint default 0, + output TEXT character set latin1, + long_output TEXT, + check_source varchar(255) character set latin1 default NULL, + PRIMARY KEY (statehistory_id) +) ENGINE=InnoDB COMMENT='Historical host and service state changes'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_systemcommands +-- + +CREATE TABLE IF NOT EXISTS icinga_systemcommands ( + systemcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + start_time timestamp NULL, + start_time_usec int default 0, + end_time timestamp NULL, + end_time_usec int default 0, + command_line TEXT character set latin1, + timeout smallint default 0, + early_timeout smallint default 0, + execution_time double default '0', + return_code smallint default 0, + output TEXT character set latin1, + long_output TEXT, + PRIMARY KEY (systemcommand_id), + UNIQUE KEY instance_id (instance_id,start_time,start_time_usec) +) ENGINE=InnoDB COMMENT='Historical system commands that are executed'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_timeperiods +-- + +CREATE TABLE IF NOT EXISTS icinga_timeperiods ( + timeperiod_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + config_type smallint default 0, + timeperiod_object_id bigint unsigned default 0, + alias varchar(255) character set latin1 default '', + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (timeperiod_id), + UNIQUE KEY instance_id (instance_id,config_type,timeperiod_object_id) +) ENGINE=InnoDB COMMENT='Timeperiod definitions'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_timeperiod_timeranges +-- + +CREATE TABLE IF NOT EXISTS icinga_timeperiod_timeranges ( + timeperiod_timerange_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + timeperiod_id bigint unsigned default 0, + day smallint default 0, + start_sec int default 0, + end_sec int default 0, + PRIMARY KEY (timeperiod_timerange_id) +) ENGINE=InnoDB COMMENT='Timeperiod definitions'; + + +-- -------------------------------------------------------- +-- Icinga 2 specific schema extensions +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_endpoints +-- + +CREATE TABLE IF NOT EXISTS icinga_endpoints ( + endpoint_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + endpoint_object_id bigint(20) unsigned DEFAULT '0', + zone_object_id bigint(20) unsigned DEFAULT '0', + config_type smallint(6) DEFAULT '0', + identity varchar(255) DEFAULT NULL, + node varchar(255) DEFAULT NULL, + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (endpoint_id) +) ENGINE=InnoDB COMMENT='Endpoint configuration'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_endpointstatus +-- + +CREATE TABLE IF NOT EXISTS icinga_endpointstatus ( + endpointstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + endpoint_object_id bigint(20) unsigned DEFAULT '0', + zone_object_id bigint(20) unsigned DEFAULT '0', + status_update_time timestamp NULL, + identity varchar(255) DEFAULT NULL, + node varchar(255) DEFAULT NULL, + is_connected smallint(6), + PRIMARY KEY (endpointstatus_id) +) ENGINE=InnoDB COMMENT='Endpoint status'; + +-- +-- Table structure for table icinga_zones +-- + +CREATE TABLE IF NOT EXISTS icinga_zones ( + zone_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + zone_object_id bigint(20) unsigned DEFAULT '0', + config_type smallint(6) DEFAULT '0', + parent_zone_object_id bigint(20) unsigned DEFAULT '0', + is_global smallint(6), + config_hash varchar(64) DEFAULT NULL, + PRIMARY KEY (zone_id) +) ENGINE=InnoDB COMMENT='Zone configuration'; + +-- -------------------------------------------------------- + +-- +-- Table structure for table icinga_zonestatus +-- + +CREATE TABLE IF NOT EXISTS icinga_zonestatus ( + zonestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + zone_object_id bigint(20) unsigned DEFAULT '0', + status_update_time timestamp NULL, + parent_zone_object_id bigint(20) unsigned DEFAULT '0', + PRIMARY KEY (zonestatus_id) +) ENGINE=InnoDB COMMENT='Zone status'; + + + + +ALTER TABLE icinga_servicestatus ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_hoststatus ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_contactstatus ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_programstatus ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_comments ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_scheduleddowntime ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_runtimevariables ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_customvariablestatus ADD COLUMN endpoint_object_id bigint default NULL; + +ALTER TABLE icinga_acknowledgements ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_commenthistory ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_contactnotifications ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_downtimehistory ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_eventhandlers ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_externalcommands ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_flappinghistory ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_hostchecks ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_logentries ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_notifications ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_processevents ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_servicechecks ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_statehistory ADD COLUMN endpoint_object_id bigint default NULL; +ALTER TABLE icinga_systemcommands ADD COLUMN endpoint_object_id bigint default NULL; + +-- ----------------------------------------- +-- add index (delete) +-- ----------------------------------------- + +-- for periodic delete +-- instance_id and +-- SYSTEMCOMMANDS, SERVICECHECKS, HOSTCHECKS, EVENTHANDLERS => start_time +-- EXTERNALCOMMANDS => entry_time + +-- instance_id +CREATE INDEX servicechecks_i_id_idx on icinga_servicechecks(instance_id); +CREATE INDEX hostchecks_i_id_idx on icinga_hostchecks(instance_id); +CREATE INDEX externalcommands_i_id_idx on icinga_externalcommands(instance_id); + +-- time +CREATE INDEX systemcommands_time_id_idx on icinga_systemcommands(start_time); +CREATE INDEX servicechecks_time_id_idx on icinga_servicechecks(start_time); +CREATE INDEX hostchecks_time_id_idx on icinga_hostchecks(start_time); +CREATE INDEX eventhandlers_time_id_idx on icinga_eventhandlers(start_time); +CREATE INDEX externalcommands_time_id_idx on icinga_externalcommands(entry_time); + + +-- for starting cleanup - referenced in dbhandler.c:882 +-- instance_id only + +-- realtime data +CREATE INDEX hoststatus_i_id_idx on icinga_hoststatus(instance_id); +CREATE INDEX servicestatus_i_id_idx on icinga_servicestatus(instance_id); +CREATE INDEX contactstatus_i_id_idx on icinga_contactstatus(instance_id); +CREATE INDEX customvariablestatus_i_id_idx on icinga_customvariablestatus(instance_id); + +-- config data +CREATE INDEX configfilevariables_i_id_idx on icinga_configfilevariables(instance_id); +CREATE INDEX customvariables_i_id_idx on icinga_customvariables(instance_id); +CREATE INDEX timeperiod_timeranges_i_id_idx on icinga_timeperiod_timeranges(instance_id); +CREATE INDEX contactgroup_members_i_id_idx on icinga_contactgroup_members(instance_id); +CREATE INDEX hostgroup_members_i_id_idx on icinga_hostgroup_members(instance_id); +CREATE INDEX servicegroup_members_i_id_idx on icinga_servicegroup_members(instance_id); +CREATE INDEX contact_addresses_i_id_idx on icinga_contact_addresses(instance_id); +CREATE INDEX contact_notifcommands_i_id_idx on icinga_contact_notificationcommands(instance_id); +CREATE INDEX host_parenthosts_i_id_idx on icinga_host_parenthosts(instance_id); +CREATE INDEX host_contacts_i_id_idx on icinga_host_contacts(instance_id); +CREATE INDEX service_contacts_i_id_idx on icinga_service_contacts(instance_id); +CREATE INDEX service_contactgroups_i_id_idx on icinga_service_contactgroups(instance_id); +CREATE INDEX host_contactgroups_i_id_idx on icinga_host_contactgroups(instance_id); +CREATE INDEX hostesc_cgroups_i_id_idx on icinga_hostescalation_contactgroups(instance_id); +CREATE INDEX serviceesc_cgroups_i_id_idx on icinga_serviceescalation_contactgroups(instance_id); + +-- ----------------------------------------- +-- more index stuff (WHERE clauses) +-- ----------------------------------------- + +-- hosts +CREATE INDEX hosts_host_object_id_idx on icinga_hosts(host_object_id); + +-- hoststatus +CREATE INDEX hoststatus_stat_upd_time_idx on icinga_hoststatus(status_update_time); +CREATE INDEX hoststatus_current_state_idx on icinga_hoststatus(current_state); +CREATE INDEX hoststatus_check_type_idx on icinga_hoststatus(check_type); +CREATE INDEX hoststatus_state_type_idx on icinga_hoststatus(state_type); +CREATE INDEX hoststatus_last_state_chg_idx on icinga_hoststatus(last_state_change); +CREATE INDEX hoststatus_notif_enabled_idx on icinga_hoststatus(notifications_enabled); +CREATE INDEX hoststatus_problem_ack_idx on icinga_hoststatus(problem_has_been_acknowledged); +CREATE INDEX hoststatus_act_chks_en_idx on icinga_hoststatus(active_checks_enabled); +CREATE INDEX hoststatus_pas_chks_en_idx on icinga_hoststatus(passive_checks_enabled); +CREATE INDEX hoststatus_event_hdl_en_idx on icinga_hoststatus(event_handler_enabled); +CREATE INDEX hoststatus_flap_det_en_idx on icinga_hoststatus(flap_detection_enabled); +CREATE INDEX hoststatus_is_flapping_idx on icinga_hoststatus(is_flapping); +CREATE INDEX hoststatus_p_state_chg_idx on icinga_hoststatus(percent_state_change); +CREATE INDEX hoststatus_latency_idx on icinga_hoststatus(latency); +CREATE INDEX hoststatus_ex_time_idx on icinga_hoststatus(execution_time); +CREATE INDEX hoststatus_sch_downt_d_idx on icinga_hoststatus(scheduled_downtime_depth); + +-- services +CREATE INDEX services_host_object_id_idx on icinga_services(host_object_id); + +-- servicestatus +CREATE INDEX srvcstatus_stat_upd_time_idx on icinga_servicestatus(status_update_time); +CREATE INDEX srvcstatus_current_state_idx on icinga_servicestatus(current_state); +CREATE INDEX srvcstatus_check_type_idx on icinga_servicestatus(check_type); +CREATE INDEX srvcstatus_state_type_idx on icinga_servicestatus(state_type); +CREATE INDEX srvcstatus_last_state_chg_idx on icinga_servicestatus(last_state_change); +CREATE INDEX srvcstatus_notif_enabled_idx on icinga_servicestatus(notifications_enabled); +CREATE INDEX srvcstatus_problem_ack_idx on icinga_servicestatus(problem_has_been_acknowledged); +CREATE INDEX srvcstatus_act_chks_en_idx on icinga_servicestatus(active_checks_enabled); +CREATE INDEX srvcstatus_pas_chks_en_idx on icinga_servicestatus(passive_checks_enabled); +CREATE INDEX srvcstatus_event_hdl_en_idx on icinga_servicestatus(event_handler_enabled); +CREATE INDEX srvcstatus_flap_det_en_idx on icinga_servicestatus(flap_detection_enabled); +CREATE INDEX srvcstatus_is_flapping_idx on icinga_servicestatus(is_flapping); +CREATE INDEX srvcstatus_p_state_chg_idx on icinga_servicestatus(percent_state_change); +CREATE INDEX srvcstatus_latency_idx on icinga_servicestatus(latency); +CREATE INDEX srvcstatus_ex_time_idx on icinga_servicestatus(execution_time); +CREATE INDEX srvcstatus_sch_downt_d_idx on icinga_servicestatus(scheduled_downtime_depth); + +-- hostchecks +CREATE INDEX hostchks_h_obj_id_idx on icinga_hostchecks(host_object_id); + +-- servicechecks +CREATE INDEX servicechks_s_obj_id_idx on icinga_servicechecks(service_object_id); + +-- objects +CREATE INDEX objects_name1_idx ON icinga_objects(name1); +CREATE INDEX objects_name2_idx ON icinga_objects(name2); +CREATE INDEX objects_inst_id_idx ON icinga_objects(instance_id); + +-- instances +-- CREATE INDEX instances_name_idx on icinga_instances(instance_name); + +-- logentries +-- CREATE INDEX loge_instance_id_idx on icinga_logentries(instance_id); +-- #236 +CREATE INDEX loge_time_idx on icinga_logentries(logentry_time); +-- CREATE INDEX loge_data_idx on icinga_logentries(logentry_data); +CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logentry_time DESC); + +-- commenthistory +-- CREATE INDEX c_hist_instance_id_idx on icinga_logentries(instance_id); +-- CREATE INDEX c_hist_c_time_idx on icinga_logentries(comment_time); +-- CREATE INDEX c_hist_i_c_id_idx on icinga_logentries(internal_comment_id); + +-- downtimehistory +-- CREATE INDEX d_t_hist_nstance_id_idx on icinga_downtimehistory(instance_id); +-- CREATE INDEX d_t_hist_type_idx on icinga_downtimehistory(downtime_type); +-- CREATE INDEX d_t_hist_object_id_idx on icinga_downtimehistory(object_id); +-- CREATE INDEX d_t_hist_entry_time_idx on icinga_downtimehistory(entry_time); +-- CREATE INDEX d_t_hist_sched_start_idx on icinga_downtimehistory(scheduled_start_time); +-- CREATE INDEX d_t_hist_sched_end_idx on icinga_downtimehistory(scheduled_end_time); + +-- scheduleddowntime +-- CREATE INDEX sched_d_t_downtime_type_idx on icinga_scheduleddowntime(downtime_type); +-- CREATE INDEX sched_d_t_object_id_idx on icinga_scheduleddowntime(object_id); +-- CREATE INDEX sched_d_t_entry_time_idx on icinga_scheduleddowntime(entry_time); +-- CREATE INDEX sched_d_t_start_time_idx on icinga_scheduleddowntime(scheduled_start_time); +-- CREATE INDEX sched_d_t_end_time_idx on icinga_scheduleddowntime(scheduled_end_time); + +-- statehistory +CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time); +-- #2274 +create index statehist_state_idx on icinga_statehistory(object_id,state); + + +-- Icinga Web Notifications +CREATE INDEX notification_idx ON icinga_notifications(notification_type, object_id, start_time); +CREATE INDEX notification_object_id_idx ON icinga_notifications(object_id); +CREATE INDEX contact_notification_idx ON icinga_contactnotifications(notification_id, contact_object_id); +CREATE INDEX contacts_object_id_idx ON icinga_contacts(contact_object_id); +CREATE INDEX contact_notif_meth_notif_idx ON icinga_contactnotificationmethods(contactnotification_id, command_object_id); +CREATE INDEX command_object_idx ON icinga_commands(object_id); +CREATE INDEX services_combined_object_idx ON icinga_services(service_object_id, host_object_id); + + +-- #2618 +CREATE INDEX cntgrpmbrs_cgid_coid ON icinga_contactgroup_members (contactgroup_id,contact_object_id); +CREATE INDEX hstgrpmbrs_hgid_hoid ON icinga_hostgroup_members (hostgroup_id,host_object_id); +CREATE INDEX hstcntgrps_hid_cgoid ON icinga_host_contactgroups (host_id,contactgroup_object_id); +CREATE INDEX hstprnthsts_hid_phoid ON icinga_host_parenthosts (host_id,parent_host_object_id); +CREATE INDEX runtimevars_iid_varn ON icinga_runtimevariables (instance_id,varname); +CREATE INDEX sgmbrs_sgid_soid ON icinga_servicegroup_members (servicegroup_id,service_object_id); +CREATE INDEX scgrps_sid_cgoid ON icinga_service_contactgroups (service_id,contactgroup_object_id); +CREATE INDEX tperiod_tid_d_ss_es ON icinga_timeperiod_timeranges (timeperiod_id,day,start_sec,end_sec); + +-- #3649 +CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC); +CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time); +CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1); + +-- #4985 +CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, comment_time, internal_comment_id); + +-- #10066 +CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id); +CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id); + +CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id); +CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id); + +CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id); +CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id); + +CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id); +CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id); + +-- #12210 +CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token); +CREATE INDEX idx_downtimes_session_del ON icinga_scheduleddowntime (instance_id, session_token); + +-- #12107 +CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); + +-- #12435 +CREATE INDEX idx_contactgroup_members_object_id on icinga_contactgroup_members(contact_object_id); +CREATE INDEX idx_hostgroup_members_object_id on icinga_hostgroup_members(host_object_id); +CREATE INDEX idx_servicegroup_members_object_id on icinga_servicegroup_members(service_object_id); +CREATE INDEX idx_servicedependencies_dependent_service_object_id on icinga_servicedependencies(dependent_service_object_id); +CREATE INDEX idx_hostdependencies_dependent_host_object_id on icinga_hostdependencies(dependent_host_object_id); +CREATE INDEX idx_service_contacts_service_id on icinga_service_contacts(service_id); +CREATE INDEX idx_host_contacts_host_id on icinga_host_contacts(host_id); + +-- #5458 +create index idx_downtimehistory_remove on icinga_downtimehistory (object_id, entry_time, scheduled_start_time, scheduled_end_time); +create index idx_scheduleddowntime_remove on icinga_scheduleddowntime (object_id, entry_time, scheduled_start_time, scheduled_end_time); + +-- #5492 +CREATE INDEX idx_commenthistory_remove ON icinga_commenthistory (object_id, entry_time); +CREATE INDEX idx_comments_remove ON icinga_comments (object_id, entry_time); + +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW()) +ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW(); + + diff --git a/lib/db_ido_mysql/schema/upgrade/2.0.2.sql b/lib/db_ido_mysql/schema/upgrade/2.0.2.sql new file mode 100644 index 0000000..c622ae9 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.0.2.sql @@ -0,0 +1,20 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.0.2 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +UPDATE icinga_objects SET name2 = NULL WHERE name2 = ''; + +ALTER TABLE `icinga_customvariables` MODIFY COLUMN `varname` varchar(255) character set latin1 collate latin1_general_cs default NULL; +ALTER TABLE `icinga_customvariablestatus` MODIFY COLUMN `varname` varchar(255) character set latin1 collate latin1_general_cs default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.11.6', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.11.6', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.1.0.sql b/lib/db_ido_mysql/schema/upgrade/2.1.0.sql new file mode 100644 index 0000000..7bbed72 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.1.0.sql @@ -0,0 +1,17 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.1.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +ALTER TABLE `icinga_programstatus` ADD COLUMN `endpoint_name` varchar(255) character set latin1 collate latin1_general_cs default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.11.7', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.11.7', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.11.0.sql b/lib/db_ido_mysql/schema/upgrade/2.11.0.sql new file mode 100644 index 0000000..bafa93f --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.11.0.sql @@ -0,0 +1,89 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.11.0 +-- +-- ----------------------------------------- +-- Copyright (c) 2019 Icinga Development Team (https://icinga.com/) +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- -------------------------------------------------------- +-- Helper functions and procedures for DROP INDEX IF EXISTS +-- -------------------------------------------------------- + +DELIMITER // +DROP FUNCTION IF EXISTS ido_index_exists // +CREATE FUNCTION ido_index_exists( + f_table_name varchar(64), + f_index_name varchar(64) +) + RETURNS BOOL + DETERMINISTIC + READS SQL DATA + BEGIN + DECLARE index_exists BOOL DEFAULT FALSE; + SELECT EXISTS ( + SELECT 1 + FROM information_schema.statistics + WHERE table_schema = SCHEMA() + AND table_name = f_table_name + AND index_name = f_index_name + ) INTO index_exists; + RETURN index_exists; + END // + +DROP PROCEDURE IF EXISTS ido_drop_index_if_exists // +CREATE PROCEDURE ido_drop_index_if_exists ( + IN p_table_name varchar(64), + IN p_index_name varchar(64) +) + DETERMINISTIC + MODIFIES SQL DATA + BEGIN + IF ido_index_exists(p_table_name, p_index_name) + THEN + SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`'); + PREPARE stmt FROM @ido_drop_index_sql; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET @ido_drop_index_sql = NULL; + END IF; + END // +DELIMITER ; + +CALL ido_drop_index_if_exists('icinga_commands', 'commands_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_comments', 'idx_comments_object_id'); +CALL ido_drop_index_if_exists('icinga_comments', 'comments_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_configfiles', 'configfiles_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_contactgroups', 'contactgroups_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_contacts', 'contacts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_customvariables', 'idx_customvariables_object_id'); +CALL ido_drop_index_if_exists('icinga_eventhandlers', 'eventhandlers_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostdependencies', 'hostdependencies_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostescalations', 'hostesc_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostescalation_contacts', 'hostesc_contacts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hostgroups', 'hostgroups_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_hosts', 'host_object_id'); +CALL ido_drop_index_if_exists('icinga_hosts', 'hosts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_objects', 'objects_objtype_id_idx'); +CALL ido_drop_index_if_exists('icinga_programstatus', 'programstatus_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_runtimevariables', 'runtimevariables_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'scheduleddowntime_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'idx_scheduleddowntime_object_id'); +CALL ido_drop_index_if_exists('icinga_serviceescalations', 'serviceesc_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_serviceescalation_contacts', 'serviceesc_contacts_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_servicegroups', 'servicegroups_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_services', 'services_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_services', 'service_object_id'); +CALL ido_drop_index_if_exists('icinga_systemcommands', 'systemcommands_i_id_idx'); +CALL ido_drop_index_if_exists('icinga_timeperiods', 'timeperiods_i_id_idx'); + +DROP FUNCTION ido_index_exists; +DROP PROCEDURE ido_drop_index_if_exists; + +-- ----------------------------------------- +-- set dbversion (same as 2.11.0) +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.0', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.12.7.sql b/lib/db_ido_mysql/schema/upgrade/2.12.7.sql new file mode 100644 index 0000000..6319b37 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.12.7.sql @@ -0,0 +1,15 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.12.7 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ------------- +-- set dbversion +-- ------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.0', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.13.0.sql b/lib/db_ido_mysql/schema/upgrade/2.13.0.sql new file mode 100644 index 0000000..462be6f --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.13.0.sql @@ -0,0 +1,23 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.13.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ---------------------------------------- +-- #7472 Support hosts with >128 characters +-- ---------------------------------------- + +ALTER TABLE icinga_objects + MODIFY COLUMN name1 varchar(255) character set latin1 collate latin1_general_cs default '', + MODIFY COLUMN name2 varchar(255) character set latin1 collate latin1_general_cs default NULL; + +-- ------------- +-- set dbversion +-- ------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.13.3.sql b/lib/db_ido_mysql/schema/upgrade/2.13.3.sql new file mode 100644 index 0000000..577eb0a --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.13.3.sql @@ -0,0 +1,15 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.13.3 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2021 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ------------- +-- set dbversion +-- ------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.2.0.sql b/lib/db_ido_mysql/schema/upgrade/2.2.0.sql new file mode 100644 index 0000000..22a6115 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.2.0.sql @@ -0,0 +1,23 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.2.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +ALTER TABLE `icinga_programstatus` ADD COLUMN `program_version` varchar(64) character set latin1 collate latin1_general_cs default NULL; + +ALTER TABLE icinga_contacts MODIFY alias TEXT character set latin1; +ALTER TABLE icinga_hosts MODIFY alias TEXT character set latin1; + +ALTER TABLE icinga_customvariables ADD COLUMN is_json smallint default 0; +ALTER TABLE icinga_customvariablestatus ADD COLUMN is_json smallint default 0; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.12.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.12.0', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.3.0.sql b/lib/db_ido_mysql/schema/upgrade/2.3.0.sql new file mode 100644 index 0000000..f2fe463 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.3.0.sql @@ -0,0 +1,26 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.3.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #7765 drop unique constraint +-- ----------------------------------------- + +ALTER TABLE icinga_servicedependencies DROP KEY instance_id; +ALTER TABLE icinga_hostdependencies DROP KEY instance_id; + +ALTER TABLE icinga_servicedependencies ADD KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical); +ALTER TABLE icinga_hostdependencies ADD KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable); + + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.13.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.13.0', modify_time=NOW(); + diff --git a/lib/db_ido_mysql/schema/upgrade/2.4.0.sql b/lib/db_ido_mysql/schema/upgrade/2.4.0.sql new file mode 100644 index 0000000..f6803f7 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.4.0.sql @@ -0,0 +1,75 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.4.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #9286 - zone tables +-- ----------------------------------------- + +ALTER TABLE icinga_endpoints ADD COLUMN zone_object_id bigint(20) unsigned DEFAULT '0'; +ALTER TABLE icinga_endpointstatus ADD COLUMN zone_object_id bigint(20) unsigned DEFAULT '0'; + +CREATE TABLE IF NOT EXISTS icinga_zones ( + zone_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + zone_object_id bigint(20) unsigned DEFAULT '0', + config_type smallint(6) DEFAULT '0', + parent_zone_object_id bigint(20) unsigned DEFAULT '0', + is_global smallint(6), + PRIMARY KEY (zone_id) +) ENGINE=InnoDB COMMENT='Zone configuration'; + +CREATE TABLE IF NOT EXISTS icinga_zonestatus ( + zonestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, + instance_id bigint unsigned default 0, + zone_object_id bigint(20) unsigned DEFAULT '0', + status_update_time timestamp NOT NULL, + parent_zone_object_id bigint(20) unsigned DEFAULT '0', + PRIMARY KEY (zonestatus_id) +) ENGINE=InnoDB COMMENT='Zone status'; + + +-- ----------------------------------------- +-- #9576 - freshness_threshold +-- ----------------------------------------- + +ALTER TABLE icinga_services MODIFY freshness_threshold int; +ALTER TABLE icinga_hosts MODIFY freshness_threshold int; + +-- ----------------------------------------- +-- #10392 - original attributes +-- ----------------------------------------- + +ALTER TABLE icinga_servicestatus ADD COLUMN original_attributes TEXT character set latin1 default NULL; +ALTER TABLE icinga_hoststatus ADD COLUMN original_attributes TEXT character set latin1 default NULL; + +-- ----------------------------------------- +-- #10436 deleted custom vars +-- ----------------------------------------- + +ALTER TABLE icinga_customvariables ADD COLUMN session_token int default NULL; +ALTER TABLE icinga_customvariablestatus ADD COLUMN session_token int default NULL; + +CREATE INDEX cv_session_del_idx ON icinga_customvariables (session_token); +CREATE INDEX cvs_session_del_idx ON icinga_customvariablestatus (session_token); + +-- ----------------------------------------- +-- #10431 comment/downtime name +-- ----------------------------------------- + +ALTER TABLE icinga_comments ADD COLUMN name TEXT character set latin1 default NULL; +ALTER TABLE icinga_commenthistory ADD COLUMN name TEXT character set latin1 default NULL; + +ALTER TABLE icinga_scheduleddowntime ADD COLUMN name TEXT character set latin1 default NULL; +ALTER TABLE icinga_downtimehistory ADD COLUMN name TEXT character set latin1 default NULL; + +-- ----------------------------------------- +-- update dbversion +-- ----------------------------------------- + +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.0', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.5.0.sql b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql new file mode 100644 index 0000000..d5714a0 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.5.0.sql @@ -0,0 +1,103 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.5.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- ----------------------------------------- +-- #10069 IDO: check_source should not be a TEXT field +-- ----------------------------------------- + +ALTER TABLE icinga_hoststatus MODIFY COLUMN check_source varchar(255) character set latin1 default ''; +ALTER TABLE icinga_servicestatus MODIFY COLUMN check_source varchar(255) character set latin1 default ''; + +-- ----------------------------------------- +-- #10070 +-- ----------------------------------------- + +CREATE INDEX idx_comments_object_id on icinga_comments(object_id); +CREATE INDEX idx_scheduleddowntime_object_id on icinga_scheduleddowntime(object_id); + +-- ----------------------------------------- +-- #11962 +-- ----------------------------------------- + +ALTER TABLE icinga_hoststatus MODIFY COLUMN current_notification_number int unsigned default 0; +ALTER TABLE icinga_servicestatus MODIFY COLUMN current_notification_number int unsigned default 0; + +-- ----------------------------------------- +-- #10061 +-- ----------------------------------------- + +ALTER TABLE icinga_contactgroups MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_contacts MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_hostgroups MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_hosts MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_servicegroups MODIFY COLUMN alias varchar(255) character set latin1 default ''; +ALTER TABLE icinga_timeperiods MODIFY COLUMN alias varchar(255) character set latin1 default ''; + +-- ----------------------------------------- +-- #10066 +-- ----------------------------------------- + +CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id); +CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id); + +CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id); +CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id); + +CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id); +CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id); + +CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id); +CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id); + +-- ----------------------------------------- +-- #12107 +-- ----------------------------------------- +CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); + +-- ----------------------------------------- +-- #12258 +-- ----------------------------------------- +ALTER TABLE icinga_comments ADD COLUMN session_token INTEGER default NULL; +ALTER TABLE icinga_scheduleddowntime ADD COLUMN session_token INTEGER default NULL; + +CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token); +CREATE INDEX idx_downtimes_session_del ON icinga_scheduleddowntime (instance_id, session_token); + +-- ----------------------------------------- +-- #12435 +-- ----------------------------------------- +ALTER TABLE icinga_commands ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_contactgroups ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_contacts ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_hostgroups ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_hosts ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_servicegroups ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_services ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_timeperiods ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_endpoints ADD config_hash VARCHAR(64) DEFAULT NULL; +ALTER TABLE icinga_zones ADD config_hash VARCHAR(64) DEFAULT NULL; + +ALTER TABLE icinga_customvariables DROP session_token; +ALTER TABLE icinga_customvariablestatus DROP session_token; + +CREATE INDEX idx_customvariables_object_id on icinga_customvariables(object_id); +CREATE INDEX idx_contactgroup_members_object_id on icinga_contactgroup_members(contact_object_id); +CREATE INDEX idx_hostgroup_members_object_id on icinga_hostgroup_members(host_object_id); +CREATE INDEX idx_servicegroup_members_object_id on icinga_servicegroup_members(service_object_id); +CREATE INDEX idx_servicedependencies_dependent_service_object_id on icinga_servicedependencies(dependent_service_object_id); +CREATE INDEX idx_hostdependencies_dependent_host_object_id on icinga_hostdependencies(dependent_host_object_id); +CREATE INDEX idx_service_contacts_service_id on icinga_service_contacts(service_id); +CREATE INDEX idx_host_contacts_host_id on icinga_host_contacts(host_id); + +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.1', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.1', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.6.0.sql b/lib/db_ido_mysql/schema/upgrade/2.6.0.sql new file mode 100644 index 0000000..33dd780 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.6.0.sql @@ -0,0 +1,151 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.6.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +-- ----------------------------------------- +-- #10502 IDO: Support NO_ZERO_DATE and NO_ZERO_IN_DATE SQL modes +-- ----------------------------------------- + +ALTER TABLE icinga_acknowledgements + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_commenthistory + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN comment_time timestamp NULL, + MODIFY COLUMN expiration_time timestamp NULL, + MODIFY COLUMN deletion_time timestamp NULL; + +ALTER TABLE icinga_comments + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN comment_time timestamp NULL, + MODIFY COLUMN expiration_time timestamp NULL; + +ALTER TABLE icinga_conninfo + MODIFY COLUMN connect_time timestamp NULL, + MODIFY COLUMN disconnect_time timestamp NULL, + MODIFY COLUMN last_checkin_time timestamp NULL, + MODIFY COLUMN data_start_time timestamp NULL, + MODIFY COLUMN data_end_time timestamp NULL; + +ALTER TABLE icinga_contactnotificationmethods + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_contactnotifications + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_contactstatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN last_host_notification timestamp NULL, + MODIFY COLUMN last_service_notification timestamp NULL; + +ALTER TABLE icinga_customvariablestatus + MODIFY COLUMN status_update_time timestamp NULL; + +ALTER TABLE icinga_dbversion + MODIFY COLUMN create_time timestamp NULL, + MODIFY COLUMN modify_time timestamp NULL; + +ALTER TABLE icinga_downtimehistory + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN scheduled_start_time timestamp NULL, + MODIFY COLUMN scheduled_end_time timestamp NULL, + MODIFY COLUMN actual_start_time timestamp NULL, + MODIFY COLUMN actual_end_time timestamp NULL, + MODIFY COLUMN trigger_time timestamp NULL; + +ALTER TABLE icinga_eventhandlers + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_externalcommands + MODIFY COLUMN entry_time timestamp NULL; + +ALTER TABLE icinga_flappinghistory + MODIFY COLUMN event_time timestamp NULL, + MODIFY COLUMN comment_time timestamp NULL; + +ALTER TABLE icinga_hostchecks + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_hoststatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN last_check timestamp NULL, + MODIFY COLUMN next_check timestamp NULL, + MODIFY COLUMN last_state_change timestamp NULL, + MODIFY COLUMN last_hard_state_change timestamp NULL, + MODIFY COLUMN last_time_up timestamp NULL, + MODIFY COLUMN last_time_down timestamp NULL, + MODIFY COLUMN last_time_unreachable timestamp NULL, + MODIFY COLUMN last_notification timestamp NULL, + MODIFY COLUMN next_notification timestamp NULL; + +ALTER TABLE icinga_logentries + MODIFY COLUMN logentry_time timestamp NULL, + MODIFY COLUMN entry_time timestamp NULL; + +ALTER TABLE icinga_notifications + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_processevents + MODIFY COLUMN event_time timestamp NULL; + +ALTER TABLE icinga_programstatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN program_start_time timestamp NULL, + MODIFY COLUMN program_end_time timestamp NULL, + MODIFY COLUMN last_command_check timestamp NULL, + MODIFY COLUMN last_log_rotation timestamp NULL, + MODIFY COLUMN disable_notif_expire_time timestamp NULL; + +ALTER TABLE icinga_scheduleddowntime + MODIFY COLUMN entry_time timestamp NULL, + MODIFY COLUMN scheduled_start_time timestamp NULL, + MODIFY COLUMN scheduled_end_time timestamp NULL, + MODIFY COLUMN actual_start_time timestamp NULL, + MODIFY COLUMN trigger_time timestamp NULL; + +ALTER TABLE icinga_servicechecks + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_servicestatus + MODIFY COLUMN status_update_time timestamp NULL, + MODIFY COLUMN last_check timestamp NULL, + MODIFY COLUMN next_check timestamp NULL, + MODIFY COLUMN last_state_change timestamp NULL, + MODIFY COLUMN last_hard_state_change timestamp NULL, + MODIFY COLUMN last_time_ok timestamp NULL, + MODIFY COLUMN last_time_warning timestamp NULL, + MODIFY COLUMN last_time_unknown timestamp NULL, + MODIFY COLUMN last_time_critical timestamp NULL, + MODIFY COLUMN last_notification timestamp NULL, + MODIFY COLUMN next_notification timestamp NULL; + +ALTER TABLE icinga_statehistory + MODIFY COLUMN state_time timestamp NULL; + +ALTER TABLE icinga_systemcommands + MODIFY COLUMN start_time timestamp NULL, + MODIFY COLUMN end_time timestamp NULL; + +ALTER TABLE icinga_endpointstatus + MODIFY COLUMN status_update_time timestamp NULL; + +ALTER TABLE icinga_zonestatus + MODIFY COLUMN status_update_time timestamp NULL; + +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.2', NOW(), NOW()) +ON DUPLICATE KEY UPDATE version='1.14.2', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.8.0.sql b/lib/db_ido_mysql/schema/upgrade/2.8.0.sql new file mode 100644 index 0000000..8d511a7 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.8.0.sql @@ -0,0 +1,81 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.8.0 +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- -------------------------------------------------------- +-- Helper functions and procedures for DROP INDEX IF EXISTS +-- -------------------------------------------------------- + +DELIMITER // +DROP FUNCTION IF EXISTS ido_index_exists // +CREATE FUNCTION ido_index_exists( + f_table_name varchar(64), + f_index_name varchar(64) +) + RETURNS BOOL + DETERMINISTIC + READS SQL DATA + BEGIN + DECLARE index_exists BOOL DEFAULT FALSE; + SELECT EXISTS ( + SELECT 1 + FROM information_schema.statistics + WHERE table_schema = SCHEMA() + AND table_name = f_table_name + AND index_name = f_index_name + ) INTO index_exists; + RETURN index_exists; + END // + +DROP PROCEDURE IF EXISTS ido_drop_index_if_exists // +CREATE PROCEDURE ido_drop_index_if_exists ( + IN p_table_name varchar(64), + IN p_index_name varchar(64) +) + DETERMINISTIC + MODIFIES SQL DATA + BEGIN + IF ido_index_exists(p_table_name, p_index_name) + THEN + SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`'); + PREPARE stmt FROM @ido_drop_index_sql; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET @ido_drop_index_sql = NULL; + END IF; + END // +DELIMITER ; + +CALL ido_drop_index_if_exists('icinga_downtimehistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_commenthistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_comments', 'instance_id'); + +DROP FUNCTION ido_index_exists; +DROP PROCEDURE ido_drop_index_if_exists; + +-- ----------------------------------------- +-- #5458 IDO: Improve downtime removal/cancel +-- ----------------------------------------- + +CREATE INDEX idx_downtimehistory_remove ON icinga_downtimehistory (object_id, entry_time, scheduled_start_time, scheduled_end_time); +CREATE INDEX idx_scheduleddowntime_remove ON icinga_scheduleddowntime (object_id, entry_time, scheduled_start_time, scheduled_end_time); + +-- ----------------------------------------- +-- #5492 IDO: Improve comment removal +-- ----------------------------------------- + +CREATE INDEX idx_commenthistory_remove ON icinga_commenthistory (object_id, entry_time); +CREATE INDEX idx_comments_remove ON icinga_comments (object_id, entry_time); + +-- ----------------------------------------- +-- set dbversion +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.3', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.3', modify_time=NOW(); diff --git a/lib/db_ido_mysql/schema/upgrade/2.8.1.sql b/lib/db_ido_mysql/schema/upgrade/2.8.1.sql new file mode 100644 index 0000000..98f8511 --- /dev/null +++ b/lib/db_ido_mysql/schema/upgrade/2.8.1.sql @@ -0,0 +1,67 @@ +-- ----------------------------------------- +-- upgrade path for Icinga 2.8.1 (fix for fresh 2.8.0 installation only) +-- +-- ----------------------------------------- +-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ +-- +-- Please check https://docs.icinga.com for upgrading information! +-- ----------------------------------------- + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- -------------------------------------------------------- +-- Helper functions and procedures for DROP INDEX IF EXISTS +-- -------------------------------------------------------- + +DELIMITER // +DROP FUNCTION IF EXISTS ido_index_exists // +CREATE FUNCTION ido_index_exists( + f_table_name varchar(64), + f_index_name varchar(64) +) + RETURNS BOOL + DETERMINISTIC + READS SQL DATA + BEGIN + DECLARE index_exists BOOL DEFAULT FALSE; + SELECT EXISTS ( + SELECT 1 + FROM information_schema.statistics + WHERE table_schema = SCHEMA() + AND table_name = f_table_name + AND index_name = f_index_name + ) INTO index_exists; + RETURN index_exists; + END // + +DROP PROCEDURE IF EXISTS ido_drop_index_if_exists // +CREATE PROCEDURE ido_drop_index_if_exists ( + IN p_table_name varchar(64), + IN p_index_name varchar(64) +) + DETERMINISTIC + MODIFIES SQL DATA + BEGIN + IF ido_index_exists(p_table_name, p_index_name) + THEN + SET @ido_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`'); + PREPARE stmt FROM @ido_drop_index_sql; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET @ido_drop_index_sql = NULL; + END IF; + END // +DELIMITER ; + +CALL ido_drop_index_if_exists('icinga_downtimehistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_scheduleddowntime', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_commenthistory', 'instance_id'); +CALL ido_drop_index_if_exists('icinga_comments', 'instance_id'); + +DROP FUNCTION ido_index_exists; +DROP PROCEDURE ido_drop_index_if_exists; + +-- ----------------------------------------- +-- set dbversion (same as 2.8.0) +-- ----------------------------------------- +INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.14.3', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.14.3', modify_time=NOW(); -- cgit v1.2.3