If you don't care about eventlog (you shouldn't, it's so full of shit), just drop the database and restart from scratch.
You might want to take a device list beforehand and write a little script to import them.
RRDs are all named based on data from the devices, they don't rely on database state for naming.
adam.
On 2013-05-19 23:30, Jason Lixfeld wrote:
So that didn't work either on account of location_country not being in the devices table either. Looking through the sql-schema-errors.log, it seems as though I have lots of update errors. Most of which are code 28 which seems to indicate a lack of disk space. I can't deny that at some point (many points, actually) my disk was full and went unnoticed, so it's possible that the errors were missed on account of my terrible sysadmin skills..
So is it possible to re-import all the schema files from $observium/sql-schema without causing too many issues?
Like:
cd sql-schema && for i in * ; do mysql < $i observium ; done
[root@monitor01 observium]# cat sql-schema-errors.log | egrep "^==|Error: " ====== Schema update 047 -> 048 ============== Error: The table '#sql-584_37a73' is full ====== Schema update 048 -> 049 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 049 -> 050 ============== Error: Error writing file './observium/devices_perftimes.frm' (Errcode: 28) ====== Schema update 050 -> 051 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 051 -> 052 ============== Error: Error writing file './observium/ip_mac.frm' (Errcode: 28) ====== Schema update 052 -> 053 ============== Error: Can't change size of file (Errcode: 28) ====== Schema update 053 -> 054 ============== Error: Can't change size of file (Errcode: 28) ====== Schema update 054 -> 055 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 055 -> 056 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 056 -> 057 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 057 -> 058 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 058 -> 059 ============== Error: Key column 'vlan_id' doesn't exist in table ====== Schema update 059 -> 060 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 060 -> 061 ============== Error: Unknown column 'vmwVmUUID' in 'vminfo' ====== Schema update 061 -> 062 ============== Error: Can't change size of file (Errcode: 28) ====== Schema update 062 -> 063 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Unknown column 'bgpPeerRemoteAddr' in 'bgpPeers_cbgp' Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 063 -> 064 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) ====== Schema update 064 -> 065 ============== Error: Error writing file './observium/#sql-584_37a73.frm' (Errcode: 28) Error: Unknown column 'location_city' in 'devices' ====== Schema update 065 -> 066 ============== Error: Unknown column 'location_lat' in 'field list' ====== Schema update 066 -> 067 ============== Error: Unknown column 'location_country' in 'devices' [root@monitor01 observium]#
On 2013-05-19, at 1:05 PM, Adam Armstrong adama@memetic.org wrote:
This field was added quite recently in 067.sql on april 23rd.
Try running :
ALTER TABLE `devices` ADD `location_geoapi` VARCHAR(16) NOT NULL AFTER `location_country`;
Does anyone else have this issue?
adam.
On 2013-05-19 16:51, Jason Lixfeld wrote: Indeed. ERROR 1054 (42S22): Unknown column 'location_geoapi' in 'field list' Sooo... was this me thinking that I actually did run './discovery.php -h new' after one of my updates when in fact I was dreaming it?? I was damn sure that I ran that, and made sure I ran it after the upgrade to 4027. Would that not have cleared it if I had forgotten to run it at some point prior? Or do I have to svn back to 3992, run discovery -h new, then upgrade to 4027 again and run discovery -h new again? On 2013-05-19, at 10:50 AM, Adam Armstrong adama@memetic.org wrote: Do you get an error if you try to run the query by hand? The UPDATE query between SQL[]. adam. On 2013-05-19 15:19, Jason Lixfeld wrote: I'm having a similar issue, but this particular device is brand new and had nothing to do with maps. Device information only shows "Cisco IOS" where other devices of the same hardware type running the same OS all show their Device Information just fine. I've tried to delete the device and let it get re-polled, with no difference. I should be clear - all other Observium behaviour on the device is fine, just that Device Information stuff seems to be stuck... Here's the same outpot from ./poller -h ... -d SQL[UPDATE `devices` set `uptime` ='154877',`sysObjectID` ='.1.3.6.1.4.1.9.1.1250',`sysName` ='pe01.2bloorstw01.yyz',`sysDescr` ='Cisco IOS Software, ME360x Software (ME360x-UNIVERSAL-M), Version 15.3(2)S, RELEASE SOFTWARE (fc1)\r\nTechnical Support: http://www.cisco.com/techsupport%5Cr%5CnCopyright (c) 1986-2013 by Cisco Systems, Inc.\r\nCompiled Mon 25-Mar-13 12:58 by prod_rel_team',`location_geoapi` ='mapquest',`location_lat` ='37.7463058',`location_lon` ='-25.6668573',`location_city` ='Unknown',`location_county` ='Unknown',`location_state` ='Unknown',`location_country` ='Unknown',`version` ='15.3(2)S',`features` ='UNIVERSAL',`hardware` ='ciscoMe3600x24fsM',`last_polled` =NOW(),`last_polled_timetaken` ='2.832',`device_state` ='N;' WHERE `device_id` = '182'] SQL[INSERT INTO `perf_times` (`type`,`doing`,`start`,`duration`,`devices`) VALUES ('poll','pe01.2bloorstw01.yyz','1368972218.5967','2.912','1')] ./poller.php pe01.2bloorstw01.yyz May 19, 2013, 10:03 - 1 devices polled in 2.912 secs The database, however, is missing much of this.. mysql> select * from devices where sysname = 'pe01.2bloorstw01.yyz'; +-----------+----------------------+----------------------+-----------+-----------+----------+----------+----------+------------+------------+---------+------+-----------+---------+---------+----------+------------+-------------+----------+------------+---------+----------+----------+----------+------+--------+--------+----------+--------+--------------+-------------+-----------------------+---------------------------+---------------------+---------+---------+--------+------+ | device_id | hostname | sysName | community | authlevel | authname | authpass | authalgo | cryptopass | cryptoalgo | snmpver | port | transport | timeout | retries | ssh_port | bgpLocalAs | sysObjectID | sysDescr | sysContact | version | hardware | features | location | os | status | ignore | disabled | uptime | agent_uptime | last_polled | last_polled_timetaken | last_discovered_timetaken | last_discovered | purpose | type | serial | icon | +-----------+----------------------+----------------------+-----------+-----------+----------+----------+----------+------------+------------+---------+------+-----------+---------+---------+----------+------------+-------------+----------+------------+---------+----------+----------+----------+------+--------+--------+----------+--------+--------------+-------------+-----------------------+---------------------------+---------------------+---------+---------+--------+------+ | 182 | pe01.2bloorstw01.yyz | pe01.2BloorStW01.YYZ | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 22 | 21949 | NULL | NULL | | NULL | NULL | NULL | | ios | 1 | 0 | 0 | NULL | 0 | NULL | NULL | 1.91 | 2013-05-19 09:08:43 | NULL | network | NULL | NULL | +-----------+----------------------+----------------------+-----------+-----------+----------+----------+----------+------------+------------+---------+------+-----------+---------+---------+----------+------------+-------------+----------+------------+---------+----------+----------+----------+------+--------+--------+----------+--------+--------------+-------------+-----------------------+---------------------------+---------------------+---------+---------+--------+------+ 1 row in set (0.00 sec) mysql> This device was first added while I was running v0.13.5.3992. I upgraded to 4027, deleted and rediscovered and the above output is from the 4027 -d output. Anything else I can try to help? PS. I just checked back and I have another device that is having the same issue. It's not an ME3600, it's a C4500 and among many others running the same OS, same HW configuration, etc. On 2013-05-18, at 4:46 PM, observium@velder.li wrote: Tested it. All new added devices on my observium aren't polled. Test Install, it works. Strange. btw, where is the map? regards Am 17.05.2013 19:38, schrieb observium@velder.li: Tried multiple devices. Still exists. Will try a fresh installation of observium tonight. Am 17.05.2013 15:13, schrieb Adam Armstrong: There are no issues with that SQL query, so I'm not sure where the problem could be. adam. On 2013-05-16 21:10, observium@velder.li wrote: Hi Adam Thx for your answer. Output: SQL[UPDATE `devices` set `uptime` ='7915',`sysObjectID` ='.1.3.6.1.4.1.8072.3.2.10',`sysDescr` ='Linux ns3 3.2.0-4-amd64 #1 SMP Debian 3.2.41-2+deb7u2 x86_64',`location_geoapi` ='mapquest',`location_lat` ='33.7490987',`location_lon` ='-84.3901849',`location_city` ='Atlanta',`location_county` ='Fulton County',`location_state` ='Georgia',`location_country` ='us',`version` ='3.2.0-4-amd64',`features` ='Debian 7.0',`hardware` ='Generic x86 64-bit',`last_polled` =NOW(),`last_polled_timetaken` ='85.83',`device_state` ='a:2:{s:7:"ucd_mem";a:7:{s:10:"swap_total";s:6:"522236";s:10:"swap_avail";s:6:"521932";s:9:"mem_total";s:6:"250904";s:9:"mem_avail";s:5:"17104";s:10:"mem_shared";s:1:"0";s:10:"mem_buffer";s:5:"18036";s:10:"mem_cached";s:6:"160488";}s:8:"ucd_load";s:1:"1";}' WHERE `device_id` = '55'] SQL[INSERT INTO `perf_times` (`type`,`doing`,`start`,`duration`,`devices`) VALUES ('poll','ns3.mydomain.li','1368734745.1935','85.83','1')] ./poller.php ns3.mydomain.li May 16, 2013, 22:07 - 1 devices polled in 85.83 secs But it still shows not polled yet. Any Idea? Regards Patrick Am 16.05.2013 21:38, schrieb Adam Armstrong: This is definitely a bug. It's failing to update the device record for some reason. Run : ./poller.php -h <host> -d there will be a SQL query towards the end which is trying to update the location and set the last_polled time. This might help explain why it's failing. adam. On 2013-05-16 20:25, observium@velder.li wrote: Hello there i changed a complete device (hostname is the same, but different location). Location was not updated. Hmmm, ok, i deleted the device, rm -r'ed rrd dir and re-added the device. Now it says Device not yet polled That's not correct. Device is polled. And the LocationBar does not update the locations. There's only UNKNOWN (1). Bug or my problem? Regards, patrick _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium