[Observium] Database schema out of whack

12 Mar 2016


      I’m not sure how this happened. I ran into some seemingly unrelated issues and one of the many issues I’ve been troubleshooting since is the apparent destruction of my database schema. Here’s the output of `discovery.php -u`:
000 -> 001 ... (db) done.
001 -> 002 ... (db) done (5 errors).
002 -> 003 ... (db) done (2 errors).
003 -> 004 ... (db) done (3 errors).
004 -> 005 ... (db) done (4 errors).
005 -> 006 ... (db) done.
006 -> 007 ... (db) done (1 errors).
007 -> 008 ... (db) done (1 errors).
008 -> 009 ... (db) done.
009 -> 010 ... (db) done (1 errors).
010 -> 011 ... (db) done.
011 -> 012 ... (db) done.
012 -> 013 ... (db) done (1 errors).
013 -> 014 ... (db) done.
014 -> 015 ... (db) done (1 errors).
015 -> 016 ... (db) done (4 errors).
016 -> 017 ... (db) done.
017 -> 018 ... (db) done (2 errors).
018 -> 019 ... (db) done.
019 -> 020 ... (db) done (6 errors).
020 -> 021 ... (db) done (1 errors).
021 -> 022 ... (db) done (1 errors).
022 -> 023 ... (db) done (5 errors).
023 -> 024 ... (db) done (2 errors).
024 -> 025 ... (db) done (10 errors).
025 -> 026 ... (db) done.
026 -> 027 ... (db) done.
027 -> 028 ... (db) done (11 errors).
028 -> 029 ... (db) done.
029 -> 030 ... (db) done (2 errors).
030 -> 031 ... (db) done (1 errors).
031 -> 032 ... (db) done (2 errors).
032 -> 033 ... (db) done.
033 -> 034 ... (db) done.
034 -> 035 ... (db) done (1 errors).
035 -> 036 ... (db) done (1 errors).
036 -> 037 ... (db) done.
037 -> 038 ... (db) done (2 errors).
038 -> 039 ... (db) done.
039 -> 040 ... (db) done.
040 -> 041 ... (db) done (2 errors).
041 -> 042 ... (db) done.
042 -> 043 ... (db) done (4 errors).
043 -> 044 ... (db) done (1 errors).
044 -> 045 ... (db) done (4 errors).
045 -> 046 ... (db) done.
046 -> 047 ... (db) done.
047 -> 048 ... (db) done (1 errors).
048 -> 049 ... (db) done (1 errors).
049 -> 050 ... (db) done.
050 -> 051 ... (db) done.
051 -> 052 ... (db) done.
052 -> 053 ... (db) done.
053 -> 054 ... (db) done (1 errors).
054 -> 055 ... (db) done (4 errors).
055 -> 056 ... (db) done (1 errors).
056 -> 057 ... (db) done (1 errors).
057 -> 058 ... (db) done (1 errors).
058 -> 059 ... (db) done (1 errors).
059 -> 060 ... (db) done (1 errors).
060 -> 061 ... (db) done (1 errors).
061 -> 062 ... (db) done (1 errors).
062 -> 063 ... (db) done (4 errors).
063 -> 064 ... (db) done.
064 -> 065 ... (db) done.
065 -> 066 ... (db) done.
066 -> 067 ... (db) done.
067 -> 068 ... (db) done (1 errors).
068 -> 069 ... (db) done.
069 -> 070 ... (db) done (1 errors).
070 -> 071 ... (db) done.
071 -> 072 ... (db) done (1 errors).
072 -> 073 ... (db) done.
073 -> 074 ... (php) Updating netscaler service RRDs
074 -> 075 ... (db) done (1 errors).
075 -> 076 ... (db) done (1 errors).
076 -> 077 ... (db) done.
077 -> 078 ... (db) done.
078 -> 079 ... (db) done.
079 -> 080 ... (db) done.
080 -> 081 ... (db) done.
081 -> 082 ... (db) done (1 errors).
082 -> 083 ... (db) done.
083 -> 084 ... (db) done.
084 -> 085 ... (db) done (1 errors).
085 -> 086 ... (db) done.
086 -> 087 ... (db) done (1 errors).
087 -> 088 ... (db) done.
088 -> 089 ... (db) done (5 errors).
089 -> 090 ... (db) done (3 errors).
090 -> 091 ... (db) done (1 errors).
091 -> 092 ... (db) done (2 errors).
092 -> 093 ... (db) done (2 errors).
093 -> 094 ... (db) done.
094 -> 095 ... (php) Converting alert serialize() arrays to JSON: ........................................
095 -> 096 ... (db) done (1 errors).
096 -> 097 ... (db) done.
097 -> 098 ... (db) done.
098 -> 099 ... (db) done (1 errors).
099 -> 100 ... (db) done (1 errors).
100 -> 101 ... (db) done (1 errors).
101 -> 102 ... (db) done.
102 -> 103 ... (db) done (1 errors).
103 -> 104 ... (db) done (1 errors).
104 -> 105 ... (db) done.
105 -> 106 ... (db) done (2 errors).
106 -> 107 ... (db) done.
107 -> 108 ... (db) done (1 errors).
108 -> 109 ... (db) done.
109 -> 110 ... (db) done (1 errors).
110 -> 111 ... (db) done (2 errors).
111 -> 112 ... (db) done (1 errors).
112 -> 113 ... (db) done.
113 -> 114 ... (db) done (1 errors).
114 -> 115 ... (db) done (1 errors).
115 -> 116 ... (db) done.
116 -> 117 ... (db) done.
117 -> 118 ... (db) done.
118 -> 119 ... (db) done.
119 -> 120 ... (db) done.
120 -> 121 ... (db) done.
121 -> 122 ... (db) done (3 errors).
122 -> 123 ... (db) done.
123 -> 124 ... (db) done (1 errors).
124 -> 125 ... (db) done (1 errors).
125 -> 126 ... (db) done (2 errors).
126 -> 127 ... (php) Converting FDB count RRD ds fdb->value: ......................................................................................................................................................................................................................................................
127 -> 128 ... (php) Rename old storage RRDs and DB: 
128 -> 129 ... (php) Rename old mempools RRDs and DB: 
129 -> 130 ... (db) done.
130 -> 131 ... (db) done.
131 -> 132 ... (db) done.
132 -> 133 ... (db) done (1 errors).
133 -> 134 ... (db) done (1 errors).
134 -> 135 ... (db) done (1 errors).
135 -> 136 ... (db) done (1 errors).
136 -> 137 ... (db) done.
137 -> 138 ... (db) done.
138 -> 139 ... (db) done (4 errors).
139 -> 140 ... (db) done.
140 -> 141 ... (db) done (3 errors).
141 -> 142 ... (db) done (1 errors).
142 -> 143 ... (db) done.
143 -> 144 ... (db) done.
144 -> 145 ... (db) done (1 errors).
145 -> 146 ... (db) done.
146 -> 147 ... (db) done.
147 -> 148 ... (db) done (3 errors).
148 -> 149 ... (db) done.
149 -> 150 ... (db) done (6 errors).
150 -> 151 ... (db) done.
151 -> 152 ... (db) done.
152 -> 153 ... (php) Converting RRD ds type for tcpCurrEstab COUNTER->GAUGE: .......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
153 -> 154 ... (db) done (1 errors).
154 -> 155 ... (db) done (1 errors).
155 -> 156 ... (db) done (1 errors).
156 -> 157 ... (db) done (1 errors).
157 -> 158 ... (db) done (1 errors).
158 -> 159 ... (db) done.
159 -> 160 ... (db) done.
160 -> 161 ... (db) done (1 errors).
161 -> 162 ... (db) done.
162 -> 163 ... (db) done.
163 -> 164 ... (php) Superseded by update 165
164 -> 165 ... (php) Adding MIN RRA to very old RRD files:
  * hr_processes: .....
  * hr_users: .....
  * ucd_contexts: ...
  * ucd_load: ...
  * ucd_interrupts: ...
165 -> 166 ... (db) done (1 errors).
166 -> 167 ... (db) done.
167 -> 168 ... (db) done.
168 -> 169 ... (db) done.
169 -> 170 ... (php) Move old GEO data if exist: ----------------
170 -> 171 ... (db) done.
171 -> 172 ... (db) done.
172 -> 173 ... (db) done.
173 -> 174 ... (db) done (3 errors).
174 -> 175 ... (db) done (2 errors).
175 -> 176 ... (php) Clean incorrect syslog entries: 
176 -> 177 ... (php) Clean incorrect manual geolocation: 
177 -> 178 ... (db) done (5 errors).
178 -> 179 ... (db) done (2 errors).
179 -> 180 ... (db) done.
180 -> 181 ... (db) done.
181 -> 182 ... (db) done.
182 -> 183 ... (db) done.
183 -> 184 ... (db) done (3 errors).
184 -> 185 ... (db) done.
185 -> 186 ... (db) done (9 errors).
186 -> 187 ... (php)
187 -> 188 ... (db) done.
188 -> 189 ... (db) done (2 errors).
189 -> 190 ... (db) done (2 errors).
190 -> 191 ... (php) Migrating state sensors to status entries: 
191 -> 192 ... (db) done (2 errors).
192 -> 193 ... (db) done (1 errors).
193 -> 194 ... (db) done (1 errors).
194 -> 195 ... (db) done (3 errors).
195 -> 196 ... (db) done (1 errors).
196 -> 197 ... (db) done.
197 -> 198 ... (db) done.
198 -> 199 ... (db) done.
199 -> 200 ... (db) done (2 errors).
200 -> 201 ... (db) done.
201 -> 202 ... (db) done.
202 -> 203 ... (db) done (1 errors).
203 -> 204 ... (db) done.
204 -> 205 ... (db) done.
205 -> 206 ... (db) done (4 errors).
206 -> 207 ... (db) done (1 errors).
207 -> 208 ... (db) done (1 errors).
208 -> 209 ... (db) done (5 errors).
209 -> 210 ... (php)
210 -> 211 ... (db) done (3 errors).
211 -> 212 ... (php)
212 -> 213 ... (db) done (11 errors).
213 -> 214 ... (db) done.
214 -> 215 ... (db) done (1 errors).
215 -> 216 ... (db) done (2 errors).
216 -> 217 ... (db) done.
217 -> 218 ... (db) done.
218 -> 219 ... (db) done.
219 -> 220 ... (db) done (4 errors).
220 -> 221 ... (db) done (1 errors).
221 -> 222 ... (db) done.
222 -> 223 ... (db) done (1 errors).
223 -> 224 ... (db) done.
224 -> 225 ... (db) done.
225 -> 226 ... (db) done.
226 -> 227 ... (db) done (2 errors).
227 -> 228 ... (db) done (10 errors).
228 -> 229 ... (db) done (1 errors).
229 -> 230 ... (db) done (1 errors).
230 -> 231 ... (db) done.
231 -> 232 ... (db) done.
232 -> 233 ... (db) done (2 errors).
233 -> 234 ... (db) done (1 errors).
234 -> 235 ... (db) done.
235 -> 236 ... (db) done (1 errors).
236 -> 237 ... (db) done.
237 -> 238 ... (db) done.
238 -> 239 ... (db) done (1 errors).
239 -> 240 ... (db) done (3 errors).
240 -> 241 ... (db) done.
241 -> 242 ... (db) done.
242 -> 243 ... (db) done.
243 -> 244 ... (db) done (3 errors).
244 -> 245 ... (db) done.
245 -> 246 ... (db) done (1 errors).
246 -> 247 ... (db) done (1 errors).
247 -> 248 ... (db) done.
248 -> 249 ... (db) done (1 errors).
249 -> 250 ... (db) done.
250 -> 251 ... (db) done (1 errors).
251 -> 252 ... (db) done (1 errors).
252 -> 253 ... (db) done.
253 -> 254 ... (db) done.
254 -> 255 ... (db) done (1 errors).
255 -> 256 ... (php)
256 -> 257 ... (php)
257 -> 258 ... (php)
258 -> 259 ... (db) done.
259 -> 260 ... (php)
260 -> 261 ... (db) done (5 errors).
261 -> 262 ... (php)
262 -> 263 ... (db) done (1 errors).
263 -> 264 ... (db) done (8 errors).
264 -> 265 ... (db) done (1 errors).
265 -> 266 ... (db) done.
266 -> 267 ... (db) done.
267 -> 268 ... (php)
268 -> 269 ... (php)
269 -> 270 ... (db) done.
270 -> 271 ... (db) done (11 errors).
-- Done.
So…there are a couple of errors. I grabbed a few samples of db.log and found several examples where columns are missing. Pretty much 99% of the log is failed queries to nonexistent database fields, columns, etc. Here’s two:
[2016/03/11 21:39:25 -0700] poller.php(22166): Failed dbQuery (#1054 - Unknown column 'attrib_value' in 'field list'), Query: SELECT `attrib_value` FROM `observium_attribs` WHERE `attrib_type` = 'dbSchema';
mysql> describe observium_attribs;
    +---------+---------+------+-----+---------+-------+
    | Field   | Type    | Null | Key | Default | Extra |
    +---------+---------+------+-----+---------+-------+
    | version | int(11) | NO   | PRI | NULL    |       |
    +---------+---------+------+-----+---------+-------+
    1 row in set (0.00 sec)
[2016/03/11 21:43:34 -0700] poller.php(18006): Failed dbQuery (#1054 - Unknown column 'device_id' in 'where clause'), Query: SELECT * FROM `devices_attribs` WHERE `device_id` = '136'
mysql> describe devices_attribs;
    +--------------+--------------+------+-----+-------------------+-----------------------------+
    | Field        | Type         | Null | Key | Default           | Extra                       |
    +--------------+--------------+------+-----+-------------------+-----------------------------+
    | attrib_id    | int(11)      | NO   | PRI | NULL              | auto_increment              |
    | entity_type  | varchar(32)  | NO   |     | NULL              |                             |
    | entity_id    | int(11)      | NO   | MUL | NULL              |                             |
    | attrib_type  | varchar(128) | NO   |     | NULL              |                             |
    | attrib_value | text         | NO   |     | NULL              |                             |
    | updated      | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +--------------+--------------+------+-----+-------------------+-----------------------------+
    6 rows in set (0.00 sec)
Outside of `discovery.php -u` that is failing, is it possible to recover the schema? I would imagine doing it manually is the only way. Is it worth it?

[Observium] Database schema out of whack

Sean Pedersen