Launching soon: The world's first vendor agnostic bug scrubLearn more & join waitlist
OPERATIONAL DEFECT DATABASE
...
...
User connection information from Firepower User Agent stops being processed by FMC following a catastrophic crash of the FMC due to a power outage. Also found tables (dns_queries_by_record_type_month_1590642000_0 and dns_queries_by_record_type_month_1590642000_0) were corrupted in DBCheck.log. See the below logs ----------------------------------------------------------------------------------------------------------------------------------------------------- [Tue Jun 30 14:52:56 2020][FATAL] [missing table] dns_queries_by_record_type_month_1590642000_0 [Tue Jun 30 14:52:56 2020][FATAL] [character set mismatch] database [mysql], table [dns_queries_by_record_type_month], actual [], expected [utf8] [Tue Jun 30 14:52:56 2020][FATAL] [missing column] database [mysql], table [dns_queries_by_record_type_month], column [bytes_out], def [bytes_out BIGINT(20) UNSIGNED NOT NULL DEFAULT 0] [Tue Jun 30 14:52:56 2020][FATAL] [missing column] database [mysql], table [dns_queries_by_record_type_month], column [bytes_in], def [bytes_in BIGINT(20) UNSIGNED NOT NULL DEFAULT 0] [Tue Jun 30 14:52:56 2020][FATAL] [missing column] database [mysql], table [dns_queries_by_record_type_month], column [deny], def [deny BIGINT(20) UNSIGNED NOT NULL DEFAULT 0] ............................ ............................ [Tue Jun 30 14:53:53 2020][FATAL] [missing column] database [mysql], table [dns_queries_by_record_type_day], column [netmap_num], def [netmap_num SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0] [Tue Jun 30 14:53:53 2020][FATAL] [mysql option missing] table [dns_queries_by_record_type_day], option [ENGINE], value [] [Tue Jun 30 14:53:53 2020][FATAL] [missing index] database [mysql], table [dns_queries_by_record_type_day], index [primary_key], unique [yes], columns [netmap_num,tv_sec,sensor_id,id] -----------------------------------------------------------------------------------------------------------------------------------------------------
ui_archiver fails to start, because Sourcefire database table (such as agent_messages) became corrupt as a result of the outage
To repair the corrupted tables dns_queries_by_record_type_month_1590642000_0 and dns_queries_by_record_type_month_1590642000_0 using below steps. 1. Login to the FMC and become a root user. 2. Stop the SFDataCorrelator process using pmtool. 3. renamed/moved corrupted files in dir "/var/lib/mysql/sfsnort" a. mv dns_queries_by_record_type_day_1590642000_0.frm dns_queries_by_record_type_day_1590642000_0.frm.bad b. mv dns_queries_by_record_type_month_1590642000_0.frm dns_queries_by_record_type_month_1590642000_0.frm.bad 4. Create the corrupted tables using below commands. a. OminiQuery.pl -db mdb -e "CREATE TABLE `dns_queries_by_record_type_month_1590642000_0` ( `netmap_num` smallint(5) unsigned NOT NULL DEFAULT '0', `tv_sec` int(10) unsigned NOT NULL DEFAULT '0', `id` int(10) unsigned NOT NULL DEFAULT '0', `sensor_id` int(10) unsigned NOT NULL DEFAULT '0', `bytes_in` bigint(20) unsigned NOT NULL DEFAULT '0', `bytes_out` bigint(20) unsigned NOT NULL DEFAULT '0', `allow` bigint(20) unsigned NOT NULL DEFAULT '0', `deny` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`netmap_num`,`tv_sec`,`sensor_id`,`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;" b. OminiQuery.pl -db mdb -e "CREATE TABLE `dns_queries_by_record_type_month_1590642000_0` ( `netmap_num` smallint(5) unsigned NOT NULL DEFAULT '0', `tv_sec` int(10) unsigned NOT NULL DEFAULT '0', `id` int(10) unsigned NOT NULL DEFAULT '0', `sensor_id` int(10) unsigned NOT NULL DEFAULT '0', `bytes_in` bigint(20) unsigned NOT NULL DEFAULT '0', `bytes_out` bigint(20) unsigned NOT NULL DEFAULT '0', `allow` bigint(20) unsigned NOT NULL DEFAULT '0', `deny` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`netmap_num`,`tv_sec`,`sensor_id`,`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;" 5. Run DBCheck.pl and make sure result is clear.
Same issue is been observed in 6.4.0.8 version during upgrade to 6.5 with below logs. ====================================================================================== Jun 26 19:18:02 NSB-FIREPOWER SF-IMS[17360]: [17360] (none):MySQLDatastore [WARN] MySQLDatastore.c:526:Connect(): Trying to connect to database server after error 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) Jun 26 19:18:23 NSB-FIREPOWER SF-IMS[1193]: [1193] Event Streamer:GetTableValues_Offset [WARN] Trying to reconnect to database server after error 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) Jun 26 19:18:47 NSB-FIREPOWER SF-IMS[1334]: [2981] SFDataCorrelator:MySQLDatastore [WARN] Trying to connect to database server after error 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) Jun 26 19:18:50 NSB-FIREPOWER SF-IMS[341]: [351] CloudAgent:url_license [INFO] Peer with active URLFiltering: 34e27f50-6f4d-11e7-aa9b-dc5ff269e22e Jun 26 19:18:50 NSB-FIREPOWER SF-IMS[341]: [351] CloudAgent:url_license [INFO] Peer with active URLFiltering: 594d3750-7753-11e7-8b84-a282055b7219 Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1194]: [1194] fpcollect:GetTableValues_Offset [CRITICAL] Database server error has persisted for 60 seconds. Killing process. Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[4705]: [4705] pm:log [INFO] Process 'fpcollect' closed output. Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[4705]: [4705] pm:process [INFO] Process fpcollect (1194) exited cleanly Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1336]: [1336] ui_archiver:GetTableValues_Raw [CRITICAL] Util.c:61:MySQLServerReconnected(): Database server error has persisted for 60 seconds. Killing process. Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[4705]: [4705] pm:process [INFO] Process ui_archiver (1336) exited cleanly Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [4793] SFDataCorrelator:WriteEventsToDatabase [CRITICAL] Database server error has persisted for 60 seconds. Killing process. Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator:UpdateUserIpMap [CRITICAL] Database server error has persisted for 60 seconds. Killing process. Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] --------------STACK STRACE------------------- Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/bin/SFDataCorrelator() [0x4883f8] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/bin/SFDataCorrelator() [0x488575] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /lib64/libpthread.so.0(+0x11a90) [0x7fed2e953a90] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /lib64/libc.so.6(+0x35bc5) [0x7fed27c7abc5] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /lib64/libc.so.6(+0x35cca) [0x7fed27c7acca] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/lib64/datastore/libSFDatastore_MySQL.so(+0x1a2d1) [0x7fed25b972d1] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/lib64/datastore/libSFDatastore_MySQL.so(+0x1a6d4) [0x7fed25b976d4] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/lib64/datastore/libSFDatastore_MySQL.so(+0x1203e) [0x7fed25b8f03e] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/lib64/datastore/libSFDatastore_MySQL.so(+0x12384) [0x7fed25b8f384] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/bin/SFDataCorrelator(DatastoreClient_LoginUserIdentity+0xa3) [0x5caac3] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/bin/SFDataCorrelator(_Z22HandleUserLoginInfoMsgP10_DCE_EVENTP14_UserLoginInfohP7sf_listjP19_DatastoreClientSett+0xb9f) [0x481caf] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/bin/SFDataCorrelator(_Z25UserIdentityQualificationP10_DCE_EVENTP19_DatastoreClientSet+0x1f5) [0x483515] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/bin/SFDataCorrelator(_Z13RNACorrelatorP10_DCE_EVENTP19_DatastoreClientSet+0x28c) [0x4b9efc] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /usr/local/sf/bin/SFDataCorrelator(_Z12EventHandlerP11_ThreadNodePv+0x231) [0x4f7ab1] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /lib64/libpthread.so.0(+0x7484) [0x7fed2e949484] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] /lib64/libc.so.6(clone+0x3f) [0x7fed27d2d7ef] Jun 26 19:18:56 NSB-FIREPOWER SF-IMS[1334]: [1556] SFDataCorrelator: [ERROR] --------------------------------------------- Jun 26 19:18:57 NSB-FIREPOWER SF-IMS[17360]: [17360] (none):MySQLDatastore [ERROR] MySQLDatastore.c:532:Connect(): Unable to connect to database after 60 seconds: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) Jun 26 19:18:57 NSB-FIREPOWER SF-IMS[17360]: [17360] (none):DatastoreClient [ERROR] DatastoreClient.c:122:DatastoreClient_Create(): Unable to connect to datastore: Unhandled database error