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?
I dumped my current schema. I wonder if someone could send me the correct schema so I could do a compare and fix the mismatched entities?
Seems like this might be the best option.
On Mar 11, 2016, at 9:51 PM, Sean Pedersen spedersen.lists@gmail.com wrote:
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?
I'll give you a little more time to work out how you could, perhaps, create an empty database with the correct current schema yourself...
Adam.
Sent from BlueMail
On Mar 12, 2016, 04:54, at 04:54, Sean Pedersen spedersen.lists@gmail.com wrote:
I dumped my current schema. I wonder if someone could send me the correct schema so I could do a compare and fix the mismatched entities?
Seems like this might be the best option.
On Mar 11, 2016, at 9:51 PM, Sean Pedersen
spedersen.lists@gmail.com wrote:
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 mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
I installed Observium on a VM and pulled the schema from that. I found a number of things that had somehow gotten out of sync - columns existed where they shouldn’t, or didn’t where they should, etc. etc. I fixed them, re-ran `./discovery.php -u` which still produced a number of errors, but the “update your database schema” warning disappeared so I’m going to call it good for now.
In the interim we somehow lost all of our alert checker criteria, cue ~2,500 e-mails. I had to rebuild those manually.
Everything appears to be back to normal, or at least normal enough until we either completely rebuild or stop using Observium in the next six months or so.
I’m still seeing a number of errors in db.log even after the updates, but the tables or columns being called (particularly by poller.php) didn’t exist in the correct / current schema, either.
On Mar 12, 2016, at 1:24 AM, Adam Armstrong adama@memetic.org wrote:
I'll give you a little more time to work out how you could, perhaps, create an empty database with the correct current schema yourself...
Adam.
Sent from BlueMail http://www.bluemail.me/r
On Mar 12, 2016, at 04:54, Sean Pedersen <spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote: I dumped my current schema. I wonder if someone could send me the correct schema so I could do a compare and fix the mismatched entities?
Seems like this might be the best option.
On Mar 11, 2016, at 9:51 PM, Sean Pedersen < spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote:
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 mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
Yup, that was the way to get a clean schema!
What are the db.log errors? There are a couple of obscure ones which persist, but given your db managed to self destruct once, it might be useful to make sure they arent serious.
adam
Sent from Mailbird [http://www.getmailbird.com/?utm_source=Mailbird&utm_medium=email&utm...] On 13/03/2016 00:25:16, Sean Pedersen spedersen.lists@gmail.com wrote: I installed Observium on a VM and pulled the schema from that. I found a number of things that had somehow gotten out of sync - columns existed where they shouldn’t, or didn’t where they should, etc. etc. I fixed them, re-ran `./discovery.php -u` which still produced a number of errors, but the “update your database schema” warning disappeared so I’m going to call it good for now.
In the interim we somehow lost all of our alert checker criteria, cue ~2,500 e-mails. I had to rebuild those manually.
Everything appears to be back to normal, or at least normal enough until we either completely rebuild or stop using Observium in the next six months or so.
I’m still seeing a number of errors in db.log even after the updates, but the tables or columns being called (particularly by poller.php) didn’t exist in the correct / current schema, either.
On Mar 12, 2016, at 1:24 AM, Adam Armstrong <adama@memetic.org [mailto:adama@memetic.org]> wrote:
I'll give you a little more time to work out how you could, perhaps, create an empty database with the correct current schema yourself... Adam. Sent from BlueMail [http://www.bluemail.me/r]
On Mar 12, 2016, at 04:54, Sean Pedersen <spedersen.lists@gmail.com [mailto:spedersen.lists@gmail.com]> wrote: I dumped my current schema. I wonder if someone could send me the correct schema so I could do a compare and fix the mismatched entities?
Seems like this might be the best option.
On Mar 11, 2016, at 9:51 PM, Sean Pedersen < spedersen.lists@gmail.com [mailto:spedersen.lists@gmail.com]> wrote:
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 mailing list observium@observium.org [mailto:observium@observium.org] http://postman.memetic.org/cgi-bin/mailman/listinfo/observium [http://postman.memetic.org/cgi-bin/mailman/listinfo/observium]
_______________________________________________ observium mailing list observium@observium.org [mailto:observium@observium.org] http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
Here's a tail:
[2016/03/13 16:09:19 -0600] poller.php(4787): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '278' [2016/03/13 16:09:19 -0600] poller.php(4588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'port' AND `entity_id` IN ('12804','12805','12806','12807','12808','12809','12810','12811','12812','12813','12814','12815','12816','12817','12818','12819','12820','12821','12822','12823','12824','12825','12826','12827','12828','12829','12830','12831','12832','12833','223805','223811','223817','227731') [2016/03/13 16:09:19 -0600] poller.php(6256): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '43' [2016/03/13 16:09:19 -0600] poller.php(4855): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '73' [2016/03/13 16:09:19 -0600] poller.php(4588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '76' [2016/03/13 16:09:19 -0600] poller.php(6256): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT `attrib_value` FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '43' AND `attrib_type` = 'sysORID' [2016/03/13 16:09:20 -0600] poller.php(5937): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'port' AND `entity_id` IN ('136207','136208','136209','136210','136211','136212','136213','136214','136215','136216','136217','136218','136219','136220','136221','136222','136223','136224','136225','136226','136227','136228','136229','136230','136231','136232','136233','136234','136235','136236','136237','136238','136239','136240','136241','136242','136243','136244','136245','136246','136247','136248','136249','136250','136251','136252','136253','136254','136255','136256','136257','136258','136259','136260','136261','136262','136263','136264','136265','136266','136267','136268','136269','136270','136271','136272','136273','136274','136275','136276','136277') [2016/03/13 16:09:20 -0600] poller.php(6588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '152' [2016/03/13 16:09:20 -0600] poller.php(6614): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '201' [2016/03/13 16:09:20 -0600] poller.php(6588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT `attrib_value` FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '152' AND `attrib_type` = 'sysORID'
Seems to be these two that show up the most. The others that I mentioned not existing appear to have been cleaned up during my manual fixes. Either that, or the above have drowned them out.
Looking at this fresh, I went through the schema again and noticed that this was a table I had either missed or meant to look at again later. Table `entity_attribs` has a stale column called `device_id` that is not present in the new schema, and is missing two columns present in the new schema: `entity_type` and `entity_id` - I went ahead and manually added those columns. I have not removed `device_id` yet.
Unfortunately without the entity_type or _id being set, the data is probably going to be wrong - but the error messages have stopped at least. Here is what the table looks like now:
mysql> select * from `entity_attribs`; +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ | attrib_id | entity_type | entity_id | device_id | attrib_type | attrib_value | updated | +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ | 1 | | 0 | 35 | poll_unix-agent | 1 | 2013-02-21 10:55:08 | | 2 | | 0 | 243 | poll_unix-agent | 1 | 2014-10-30 16:25:14 | | 3 | | 0 | 243 | poll_wifi | 0 | 2014-10-30 16:25:54 | | 4 | | 0 | 243 | poll_ospf | 0 | 2014-10-30 16:26:04 | | 5 | | 0 | 243 | poll_toner | 0 | 2014-10-30 16:26:09 | | 6 | | 0 | 243 | poll_bgp-peers | 0 | 2014-10-30 16:26:15 | | 7 | | 0 | 243 | enable_ports_adsl | 0 | 2014-10-30 16:26:22 | | 8 | | 0 | 243 | discover_junose-atm-vp | 0 | 2014-10-30 16:26:28 | | 9 | | 0 | 243 | discover_ospf-autodiscovery | 0 | 2014-10-30 16:26:32 | | 10 | | 0 | 243 | discover_cisco-pw | 0 | 2014-10-30 16:26:37 | | 11 | | 0 | 243 | discover_cisco-vrf | 0 | 2014-10-30 16:26:40 | | 12 | | 0 | 243 | discover_cisco-sla | 0 | 2014-10-30 16:26:44 | | 13 | | 0 | 243 | discover_cisco-cbqos | 0 | 2014-10-30 16:26:47 | | 14 | | 0 | 243 | discover_libvirt-vminfo | 0 | 2014-10-30 16:26:49 | | 15 | | 0 | 243 | discover_toner | 0 | 2014-10-30 16:26:52 | | 16 | | 0 | 243 | discover_bgp-peers | 0 | 2014-10-30 16:26:58 | | 17 | | 0 | 244 | poll_unix-agent | 1 | 2014-10-31 11:10:27 | | 18 | | 0 | 244 | poll_bgp-peers | 0 | 2014-10-31 11:10:33 | | 19 | | 0 | 244 | poll_toner | 0 | 2014-10-31 11:10:37 | | 20 | | 0 | 244 | poll_wifi | 0 | 2014-10-31 11:10:40 | | 21 | | 0 | 244 | poll_ospf | 0 | 2014-10-31 11:10:42 | | 22 | | 0 | 244 | discover_wifi | 0 | 2014-10-31 11:10:50 | | 23 | | 0 | 244 | discover_toner | 0 | 2014-10-31 11:10:53 | | 24 | | 0 | 244 | discover_libvirt-vminfo | 0 | 2014-10-31 11:10:56 | | 25 | | 0 | 244 | discover_bgp-peers | 0 | 2014-10-31 11:10:59 | | 26 | | 0 | 244 | discover_cisco-pw | 0 | 2014-10-31 11:11:02 | | 27 | | 0 | 244 | discover_cisco-vrf | 0 | 2014-10-31 11:11:05 | | 28 | | 0 | 244 | discover_cisco-sla | 0 | 2014-10-31 11:11:08 | | 29 | | 0 | 244 | discover_cisco-cbqos | 0 | 2014-10-31 11:11:13 | | 30 | | 0 | 244 | enable_ports_adsl | 0 | 2014-10-31 11:11:18 | +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ 30 rows in set (0.00 sec)
| entity_attribs | CREATE TABLE `entity_attribs` ( `attrib_id` int(11) NOT NULL AUTO_INCREMENT, `entity_type` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `entity_id` int(11) NOT NULL, `device_id` int(11) NOT NULL, `attrib_type` varchar(128) CHARACTER SET utf8 NOT NULL, `attrib_value` text CHARACTER SET utf8 NOT NULL, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`attrib_id`), KEY `device_id` (`device_id`), KEY `device_type` (`device_id`,`attrib_type`(50)) ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
I have plans to go through and remove AUTO_INCREMENT where it is still present in a number of tables, like the one above. I just haven’t gotten around to it.
On Mar 13, 2016, at 5:10 AM, Adam Armstrong adama@memetic.org wrote:
Yup, that was the way to get a clean schema!
What are the db.log errors? There are a couple of obscure ones which persist, but given your db managed to self destruct once, it might be useful to make sure they arent serious.
adam
Sent from Mailbird http://www.getmailbird.com/?utm_source=Mailbird&utm_medium=email&utm_campaign=sent-from-mailbirdOn 13/03/2016 00:25:16, Sean Pedersen spedersen.lists@gmail.com wrote:
I installed Observium on a VM and pulled the schema from that. I found a number of things that had somehow gotten out of sync - columns existed where they shouldn’t, or didn’t where they should, etc. etc. I fixed them, re-ran `./discovery.php -u` which still produced a number of errors, but the “update your database schema” warning disappeared so I’m going to call it good for now.
In the interim we somehow lost all of our alert checker criteria, cue ~2,500 e-mails. I had to rebuild those manually.
Everything appears to be back to normal, or at least normal enough until we either completely rebuild or stop using Observium in the next six months or so.
I’m still seeing a number of errors in db.log even after the updates, but the tables or columns being called (particularly by poller.php) didn’t exist in the correct / current schema, either.
On Mar 12, 2016, at 1:24 AM, Adam Armstrong <adama@memetic.org mailto:adama@memetic.org> wrote:
I'll give you a little more time to work out how you could, perhaps, create an empty database with the correct current schema yourself...
Adam.
Sent from BlueMail http://www.bluemail.me/r
On Mar 12, 2016, at 04:54, Sean Pedersen <spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote: I dumped my current schema. I wonder if someone could send me the correct schema so I could do a compare and fix the mismatched entities?
Seems like this might be the best option.
On Mar 11, 2016, at 9:51 PM, Sean Pedersen < spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote:
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 mailing list observium@observium.org mailto:observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org mailto: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
Existing entries in that table should be given entity_type device, and the device_id column renamed to entity_id.
You should have checked the update/ directory for the file which made these changes, it'd have been more obvious.
Adam.
Sent from BlueMail
On Mar 13, 2016, 22:26, at 22:26, Sean Pedersen spedersen.lists@gmail.com wrote:
Here's a tail:
[2016/03/13 16:09:19 -0600] poller.php(4787): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '278' [2016/03/13 16:09:19 -0600] poller.php(4588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'port' AND `entity_id` IN ('12804','12805','12806','12807','12808','12809','12810','12811','12812','12813','12814','12815','12816','12817','12818','12819','12820','12821','12822','12823','12824','12825','12826','12827','12828','12829','12830','12831','12832','12833','223805','223811','223817','227731') [2016/03/13 16:09:19 -0600] poller.php(6256): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '43' [2016/03/13 16:09:19 -0600] poller.php(4855): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '73' [2016/03/13 16:09:19 -0600] poller.php(4588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '76' [2016/03/13 16:09:19 -0600] poller.php(6256): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT `attrib_value` FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '43' AND `attrib_type` = 'sysORID' [2016/03/13 16:09:20 -0600] poller.php(5937): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'port' AND `entity_id` IN ('136207','136208','136209','136210','136211','136212','136213','136214','136215','136216','136217','136218','136219','136220','136221','136222','136223','136224','136225','136226','136227','136228','136229','136230','136231','136232','136233','136234','136235','136236','136237','136238','136239','136240','136241','136242','136243','136244','136245','136246','136247','136248','136249','136250','136251','136252','136253','136254','136255','136256','136257','136258','136259','136260','136261','136262','136263','136264','136265','136266','136267','136268','136269','136270','136271','136272','136273','136274','136275','136276','136277') [2016/03/13 16:09:20 -0600] poller.php(6588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '152' [2016/03/13 16:09:20 -0600] poller.php(6614): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '201' [2016/03/13 16:09:20 -0600] poller.php(6588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT `attrib_value` FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '152' AND `attrib_type` = 'sysORID'
Seems to be these two that show up the most. The others that I mentioned not existing appear to have been cleaned up during my manual fixes. Either that, or the above have drowned them out.
Looking at this fresh, I went through the schema again and noticed that this was a table I had either missed or meant to look at again later. Table `entity_attribs` has a stale column called `device_id` that is not present in the new schema, and is missing two columns present in the new schema: `entity_type` and `entity_id` - I went ahead and manually added those columns. I have not removed `device_id` yet.
Unfortunately without the entity_type or _id being set, the data is probably going to be wrong - but the error messages have stopped at least. Here is what the table looks like now:
mysql> select * from `entity_attribs`; +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ | attrib_id | entity_type | entity_id | device_id | attrib_type | attrib_value | updated | +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ | 1 | | 0 | 35 | poll_unix-agent | 1 | 2013-02-21 10:55:08 | | 2 | | 0 | 243 | poll_unix-agent | 1 | 2014-10-30 16:25:14 | | 3 | | 0 | 243 | poll_wifi | 0 | 2014-10-30 16:25:54 | | 4 | | 0 | 243 | poll_ospf | 0 | 2014-10-30 16:26:04 | | 5 | | 0 | 243 | poll_toner | 0 | 2014-10-30 16:26:09 | | 6 | | 0 | 243 | poll_bgp-peers | 0 | 2014-10-30 16:26:15 | | 7 | | 0 | 243 | enable_ports_adsl | 0 | 2014-10-30 16:26:22 | | 8 | | 0 | 243 | discover_junose-atm-vp | 0 | 2014-10-30 16:26:28 | | 9 | | 0 | 243 | discover_ospf-autodiscovery | 0 | 2014-10-30 16:26:32 | | 10 | | 0 | 243 | discover_cisco-pw | 0 | 2014-10-30 16:26:37 | | 11 | | 0 | 243 | discover_cisco-vrf | 0 | 2014-10-30 16:26:40 | | 12 | | 0 | 243 | discover_cisco-sla | 0 | 2014-10-30 16:26:44 | | 13 | | 0 | 243 | discover_cisco-cbqos | 0 | 2014-10-30 16:26:47 | | 14 | | 0 | 243 | discover_libvirt-vminfo | 0 | 2014-10-30 16:26:49 | | 15 | | 0 | 243 | discover_toner | 0 | 2014-10-30 16:26:52 | | 16 | | 0 | 243 | discover_bgp-peers | 0 | 2014-10-30 16:26:58 | | 17 | | 0 | 244 | poll_unix-agent | 1 | 2014-10-31 11:10:27 | | 18 | | 0 | 244 | poll_bgp-peers | 0 | 2014-10-31 11:10:33 | | 19 | | 0 | 244 | poll_toner | 0 | 2014-10-31 11:10:37 | | 20 | | 0 | 244 | poll_wifi | 0 | 2014-10-31 11:10:40 | | 21 | | 0 | 244 | poll_ospf | 0 | 2014-10-31 11:10:42 | | 22 | | 0 | 244 | discover_wifi | 0 | 2014-10-31 11:10:50 | | 23 | | 0 | 244 | discover_toner | 0 | 2014-10-31 11:10:53 | | 24 | | 0 | 244 | discover_libvirt-vminfo | 0 | 2014-10-31 11:10:56 | | 25 | | 0 | 244 | discover_bgp-peers | 0 | 2014-10-31 11:10:59 | | 26 | | 0 | 244 | discover_cisco-pw | 0 | 2014-10-31 11:11:02 | | 27 | | 0 | 244 | discover_cisco-vrf | 0 | 2014-10-31 11:11:05 | | 28 | | 0 | 244 | discover_cisco-sla | 0 | 2014-10-31 11:11:08 | | 29 | | 0 | 244 | discover_cisco-cbqos | 0 | 2014-10-31 11:11:13 | | 30 | | 0 | 244 | enable_ports_adsl | 0 | 2014-10-31 11:11:18 | +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ 30 rows in set (0.00 sec)
| entity_attribs | CREATE TABLE `entity_attribs` ( `attrib_id` int(11) NOT NULL AUTO_INCREMENT, `entity_type` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `entity_id` int(11) NOT NULL, `device_id` int(11) NOT NULL, `attrib_type` varchar(128) CHARACTER SET utf8 NOT NULL, `attrib_value` text CHARACTER SET utf8 NOT NULL, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`attrib_id`), KEY `device_id` (`device_id`), KEY `device_type` (`device_id`,`attrib_type`(50)) ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
I have plans to go through and remove AUTO_INCREMENT where it is still present in a number of tables, like the one above. I just haven’t gotten around to it.
On Mar 13, 2016, at 5:10 AM, Adam Armstrong adama@memetic.org
wrote:
Yup, that was the way to get a clean schema!
What are the db.log errors? There are a couple of obscure ones which
persist, but given your db managed to self destruct once, it might be useful to make sure they arent serious.
adam
Sent from Mailbird
http://www.getmailbird.com/?utm_source=Mailbird&utm_medium=email&utm_campaign=sent-from-mailbirdOn 13/03/2016 00:25:16, Sean Pedersen spedersen.lists@gmail.com wrote:
I installed Observium on a VM and pulled the schema from that. I
found a number of things that had somehow gotten out of sync - columns existed where they shouldn’t, or didn’t where they should, etc. etc. I fixed them, re-ran `./discovery.php -u` which still produced a number of errors, but the “update your database schema” warning disappeared so I’m going to call it good for now.
In the interim we somehow lost all of our alert checker criteria,
cue ~2,500 e-mails. I had to rebuild those manually.
Everything appears to be back to normal, or at least normal enough
until we either completely rebuild or stop using Observium in the next six months or so.
I’m still seeing a number of errors in db.log even after the
updates, but the tables or columns being called (particularly by poller.php) didn’t exist in the correct / current schema, either.
On Mar 12, 2016, at 1:24 AM, Adam Armstrong <adama@memetic.org
mailto:adama@memetic.org> wrote:
I'll give you a little more time to work out how you could,
perhaps, create an empty database with the correct current schema yourself...
Adam.
Sent from BlueMail http://www.bluemail.me/r
On Mar 12, 2016, at 04:54, Sean Pedersen <spedersen.lists@gmail.com
mailto:spedersen.lists@gmail.com> wrote:
I dumped my current schema. I wonder if someone could send me the
correct schema so I could do a compare and fix the mismatched entities?
Seems like this might be the best option.
On Mar 11, 2016, at 9:51 PM, Sean Pedersen <
spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote:
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 mailing list observium@observium.org mailto:observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
observium mailing list observium@observium.org mailto: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
Adjusted, thanks for the info!
On Mar 13, 2016, at 4:10 PM, Adam Armstrong adama@memetic.org wrote:
Existing entries in that table should be given entity_type device, and the device_id column renamed to entity_id.
You should have checked the update/ directory for the file which made these changes, it'd have been more obvious.
Adam.
Sent from BlueMail http://www.bluemail.me/r
On Mar 13, 2016, at 22:26, Sean Pedersen <spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote: Here's a tail:
[2016/03/13 16:09:19 -0600] poller.php(4787): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '278' [2016/03/13 16:09:19 -0600] poller.php(4588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'port' AND `entity_id` IN ('12804','12805','12806','12807','12808','12809','12810','12811','12812','12813','12814','12815','12816','12817','12818','12819','12820','12821','12822','12823','12824','12825','12826','12827','12828','12829','12830','12831','12832','12833','223805','223811','223817','227731') [2016/03/13 16:09:19 -0600] poller.php(6256): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '43' [2016/03/13 16:09:19 -0600] poller.php(4855): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '73' [2016/03/13 16:09:19 -0600] poller.php(4588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '76' [2016/03/13 16:09:19 -0600] poller.php(6256): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT `attrib_value` FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '43' AND `attrib_type` = 'sysORID' [2016/03/13 16:09:20 -0600] poller.php(5937): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'port' AND `entity_id` IN ('136207','136208','136209','136210','136211','136212','136213','136214','136215','136216','136217','136218','136219','136220','136221','136222','136223','136224','136225','136226','136227','136228','136229','136230','136231','136232','136233','136234','136235','136236','136237','136238','136239','136240','136241','136242','136243','136244','136245','136246','136247','136248','136249','136250','136251','136252','136253','136254','136255','136256','136257','136258','136259','136260','136261','136262','136263','136264','136265','136266','136267','136268','136269','136270','136271','136272','136273','136274','136275','136276','136277') [2016/03/13 16:09:20 -0600] poller.php(6588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '152' [2016/03/13 16:09:20 -0600] poller.php(6614): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT * FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '201' [2016/03/13 16:09:20 -0600] poller.php(6588): Failed dbQuery (#1054 - Unknown column 'entity_type' in 'where clause'), Query: SELECT `attrib_value` FROM `entity_attribs` WHERE `entity_type` = 'device' AND `entity_id` = '152' AND `attrib_type` = 'sysORID'
Seems to be these two that show up the most. The others that I mentioned not existing appear to have been cleaned up during my manual fixes. Either that, or the above have drowned them out.
Looking at this fresh, I went through the schema again and noticed that this was a table I had either missed or meant to look at again later. Table `entity_attribs` has a stale column called `device_id` that is not present in the new schema, and is missing two columns present in the new schema: `entity_type` and `entity_id` - I went ahead and manually added those columns. I have not removed `device_id` yet.
Unfortunately without the entity_type or _id being set, the data is probably going to be wrong - but the error messages have stopped at least. Here is what the table looks like now:
mysql> select * from `entity_attribs`; +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ | attrib_id | entity_type | entity_id | device_id | attrib_type | attrib_value | updated | +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ | 1 | | 0 | 35 | poll_unix-agent | 1 | 2013-02-21 10:55:08 | | 2 | | 0 | 243 | poll_unix-agent | 1 | 2014-10-30 16:25:14 | | 3 | | 0 | 243 | poll_wifi | 0 | 2014-10-30 16:25:54 | | 4 | | 0 | 243 | poll_ospf | 0 | 2014-10-30 16:26:04 | | 5 | | 0 | 243 | poll_toner | 0 | 2014-10-30 16:26:09 | | 6 | | 0 | 243 | poll_bgp-peers | 0 | 2014-10-30 16:26:15 | | 7 | | 0 | 243 | enable_ports_adsl | 0 | 2014-10-30 16:26:22 | | 8 | | 0 | 243 | discover_junose-atm-vp | 0 | 2014-10-30 16:26:28 | | 9 | | 0 | 243 | discover_ospf-autodiscovery | 0 | 2014-10-30 16:26:32 | | 10 | | 0 | 243 | discover_cisco-pw | 0 | 2014-10-30 16:26:37 | | 11 | | 0 | 243 | discover_cisco-vrf | 0 | 2014-10-30 16:26:40 | | 12 | | 0 | 243 | discover_cisco-sla | 0 | 2014-10-30 16:26:44 | | 13 | | 0 | 243 | discover_cisco-cbqos | 0 | 2014-10-30 16:26:47 | | 14 | | 0 | 243 | discover_libvirt-vminfo | 0 | 2014-10-30 16:26:49 | | 15 | | 0 | 243 | discover_toner | 0 | 2014-10-30 16:26:52 | | 16 | | 0 | 243 | discover_bgp-peers | 0 | 2014-10-30 16:26:58 | | 17 | | 0 | 244 | poll_unix-agent | 1 | 2014-10-31 11:10:27 | | 18 | | 0 | 244 | poll_bgp-peers | 0 | 2014-10-31 11:10:33 | | 19 | | 0 | 244 | poll_toner | 0 | 2014-10-31 11:10:37 | | 20 | | 0 | 244 | poll_wifi | 0 | 2014-10-31 11:10:40 | | 21 | | 0 | 244 | poll_ospf | 0 | 2014-10-31 11:10:42 | | 22 | | 0 | 244 | discover_wifi | 0 | 2014-10-31 11:10:50 | | 23 | | 0 | 244 | discover_toner | 0 | 2014-10-31 11:10:53 | | 24 | | 0 | 244 | discover_libvirt-vminfo | 0 | 2014-10-31 11:10:56 | | 25 | | 0 | 244 | discover_bgp-peers | 0 | 2014-10-31 11:10:59 | | 26 | | 0 | 244 | discover_cisco-pw | 0 | 2014-10-31 11:11:02 | | 27 | | 0 | 244 | discover_cisco-vrf | 0 | 2014-10-31 11:11:05 | | 28 | | 0 | 244 | discover_cisco-sla | 0 | 2014-10-31 11:11:08 | | 29 | | 0 | 244 | discover_cisco-cbqos | 0 | 2014-10-31 11:11:13 | | 30 | | 0 | 244 | enable_ports_adsl | 0 | 2014-10-31 11:11:18 | +-----------+-------------+-----------+-----------+-----------------------------+--------------+---------------------+ 30 rows in set (0.00 sec)
| entity_attribs | CREATE TABLE `entity_attribs` ( `attrib_id` int(11) NOT NULL AUTO_INCREMENT, `entity_type` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `entity_id` int(11) NOT NULL, `device_id` int(11) NOT NULL, `attrib_type` varchar(128) CHARACTER SET utf8 NOT NULL, `attrib_value` text CHARACTER SET utf8 NOT NULL, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`attrib_id`), KEY `device_id` (`device_id`), KEY `device_type` (`device_id`,`attrib_type`(50)) ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
I have plans to go through and remove AUTO_INCREMENT where it is still present in a number of tables, like the one above. I just haven’t gotten around to it.
On Mar 13, 2016, at 5:10 AM, Adam Armstrong < adama@memetic.org mailto:adama@memetic.org> wrote:
Yup, that was the way to get a clean schema!
What are the db.log errors? There are a couple of obscure ones which persist, but given your db managed to self destruct once, it might be useful to make sure they arent serious.
adam
Sent from Mailbird http://www.getmailbird.com/?utm_source=Mailbird&utm_medium=email&utm_campaign=sent-from-mailbirdOn 13/03/2016 00:25:16, Sean Pedersen <spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote:
I installed Observium on a VM and pulled the schema from that. I found a number of things that had somehow gotten out of sync - columns existed where they shouldn’t, or didn’t where they should, etc. etc. I fixed them, re-ran `./discovery.php -u` which still produced a number of errors, but the “update your database schema” warning disappeared so I’m going to call it good for now.
In the interim we somehow lost all of our alert checker criteria, cue ~2,500 e-mails. I had to rebuild those manually.
Everything appears to be back to normal, or at least normal enough until we either completely rebuild or stop using Observium in the next six months or so.
I’m still seeing a number of errors in db.log even after the updates, but the tables or columns being called (particularly by poller.php) didn’t exist in the correct / current schema, either.
On Mar 12, 2016, at 1:24 AM, Adam Armstrong < adama@memetic.org mailto:adama@memetic.org> wrote:
I'll give you a little more time to work out how you could, perhaps, create an empty database with the correct current schema yourself...
Adam.
Sent from BlueMail http://www.bluemail.me/r
On Mar 12, 2016, at 04:54, Sean Pedersen < spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote: I dumped my current schema. I wonder if someone could send me the correct schema so I could do a compare and fix the mismatched entities?
Seems like this might be the best option.
On Mar 11, 2016, at 9:51 PM, Sean Pedersen < spedersen.lists@gmail.com mailto:spedersen.lists@gmail.com> wrote:
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 mailing list observium@observium.org mailto:observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org mailto:observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
observium mailing list observium@observium.org mailto: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 http://postman.memetic.org/cgi-bin/mailman/listinfo/observium _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
participants (2)
-
Adam Armstrong -
Sean Pedersen