1<?php 2/* 3 +-------------------------------------------------------------------------+ 4 | Copyright (C) 2004-2021 The Cacti Group | 5 | | 6 | This program is free software; you can redistribute it and/or | 7 | modify it under the terms of the GNU General Public License | 8 | as published by the Free Software Foundation; either version 2 | 9 | of the License, or (at your option) any later version. | 10 | | 11 | This program is distributed in the hope that it will be useful, | 12 | but WITHOUT ANY WARRANTY; without even the implied warranty of | 13 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the | 14 | GNU General Public License for more details. | 15 +-------------------------------------------------------------------------+ 16 | Cacti: The Complete RRDtool-based Graphing Solution | 17 +-------------------------------------------------------------------------+ 18 | This code is designed, written, and maintained by the Cacti Group. See | 19 | about.php and/or the AUTHORS file for specific developer information. | 20 +-------------------------------------------------------------------------+ 21 | http://www.cacti.net/ | 22 +-------------------------------------------------------------------------+ 23*/ 24 25/* update_replication_crc - update hash stored in settings table to inform 26 remote pollers to replicate tables 27 @arg $poller_id - the id of the poller impacted by hash update 28 @arg $variable - the variable name to store in the settings table */ 29function update_replication_crc($poller_id, $variable) { 30 $hash = hash('ripemd160', date('Y-m-d H:i:s') . rand() . $poller_id); 31 32 db_execute_prepared("REPLACE INTO settings 33 SET value = ?, name='$variable" . ($poller_id > 0 ? "_" . "$poller_id'":"'"), 34 array($hash)); 35} 36 37function repopulate_poller_cache() { 38 global $config; 39 40 include_once($config['library_path'] . '/api_data_source.php'); 41 42 $poller_data = db_fetch_assoc('SELECT ' . SQL_NO_CACHE . ' dl.*, h.poller_id 43 FROM data_local AS dl 44 INNER JOIN host AS h 45 ON dl.host_id=h.id 46 WHERE dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != "") 47 ORDER BY h.poller_id ASC'); 48 49 $poller_items = array(); 50 $local_data_ids = array(); 51 $poller_prev = 1; 52 $i = 0; 53 $j = 0; 54 55 if (cacti_sizeof($poller_data)) { 56 foreach ($poller_data as $data) { 57 if ($j == 0) { 58 $poller_prev = $data['poller_id']; 59 } 60 61 $poller_id = $data['poller_id']; 62 63 if ($i > 500 || $poller_prev != $poller_id) { 64 poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_prev); 65 $i = 0; 66 $local_data_ids = array(); 67 $poller_items = array(); 68 } 69 70 $poller_prev = $poller_id; 71 $poller_items = array_merge($poller_items, update_poller_cache($data)); 72 $local_data_ids[] = $data['id']; 73 $i++; 74 $j++; 75 } 76 77 if ($i > 0) { 78 poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id); 79 } 80 } 81 82 $poller_ids = array_rekey( 83 db_fetch_assoc('SELECT DISTINCT poller_id 84 FROM poller_item'), 85 'poller_id', 'poller_id' 86 ); 87 88 if (cacti_sizeof($poller_ids)) { 89 foreach ($poller_ids as $poller_id) { 90 api_data_source_cache_crc_update($poller_id); 91 } 92 } 93 94 /* update the field mappings for the poller */ 95 db_execute('TRUNCATE TABLE poller_data_template_field_mappings'); 96 db_execute('INSERT IGNORE INTO poller_data_template_field_mappings 97 SELECT dtr.data_template_id, dif.data_name, 98 GROUP_CONCAT(dtr.data_source_name ORDER BY dtr.data_source_name) AS data_source_names, NOW() 99 FROM data_template_rrd AS dtr 100 INNER JOIN data_input_fields AS dif 101 ON dtr.data_input_field_id = dif.id 102 WHERE dtr.local_data_id = 0 103 GROUP BY dtr.data_template_id, dif.data_name'); 104} 105 106function update_poller_cache_from_query($host_id, $data_query_id, $local_data_ids) { 107 global $config; 108 109 include_once($config['library_path'] . '/api_data_source.php'); 110 111 $poller_data = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' * 112 FROM data_local 113 WHERE host_id = ? 114 AND snmp_query_id = ? 115 AND id IN(' . implode(', ', $local_data_ids) . ') 116 AND snmp_index != ""', 117 array($host_id, $data_query_id)); 118 119 $poller_id = db_fetch_cell_prepared('SELECT poller_id 120 FROM host 121 WHERE id = ?', 122 array($host_id)); 123 124 $i = 0; 125 $poller_items = $local_data_ids = array(); 126 127 if (cacti_sizeof($poller_data)) { 128 foreach ($poller_data as $data) { 129 $poller_items = array_merge($poller_items, update_poller_cache($data)); 130 $local_data_ids[] = $data['id']; 131 $i++; 132 133 if ($i > 500) { 134 $i = 0; 135 poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id); 136 $local_data_ids = array(); 137 $poller_items = array(); 138 } 139 } 140 141 if ($i > 0) { 142 poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id); 143 } 144 } 145 146 if ($poller_id > 1) { 147 api_data_source_cache_crc_update($poller_id); 148 } 149} 150 151function update_poller_cache($data_source, $commit = false) { 152 global $config; 153 154 include_once($config['library_path'] . '/data_query.php'); 155 include_once($config['library_path'] . '/api_poller.php'); 156 157 if (!is_array($data_source)) { 158 $data_source = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' * 159 FROM data_local AS dl 160 WHERE id = ? 161 AND (dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != ""))', 162 array($data_source)); 163 } 164 165 $poller_items = array(); 166 167 if (!cacti_sizeof($data_source)) { 168 return $poller_items; 169 } 170 171 $poller_id = db_fetch_cell_prepared('SELECT poller_id 172 FROM host AS h 173 INNER JOIN data_local AS dl 174 ON h.id=dl.host_id 175 WHERE dl.id = ?', 176 array($data_source['id'])); 177 178 $data_input = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' 179 di.id, di.type_id, dtd.id AS data_template_data_id, 180 dtd.data_template_id, dtd.active, dtd.rrd_step 181 FROM data_template_data AS dtd 182 INNER JOIN data_input AS di 183 ON dtd.data_input_id=di.id 184 WHERE dtd.local_data_id = ?', 185 array($data_source['id'])); 186 187 if (cacti_sizeof($data_input)) { 188 // Check whitelist for input validation 189 if (!data_input_whitelist_check($data_input['id'])) { 190 return $poller_items; 191 } 192 193 /* we have to perform some additional sql queries if this is a 'query' */ 194 if (($data_input['type_id'] == DATA_INPUT_TYPE_SNMP_QUERY) || 195 ($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT_QUERY) || 196 ($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER)){ 197 $field = data_query_field_list($data_input['data_template_data_id']); 198 199 $params = array(); 200 if ($field['output_type'] != '') { 201 $output_type_sql = ' AND sqgr.snmp_query_graph_id = ' . $field['output_type']; 202 } else { 203 $output_type_sql = ''; 204 } 205 $params[] = $data_input['data_template_id']; 206 $params[] = $data_source['id']; 207 208 $outputs = db_fetch_assoc_prepared('SELECT DISTINCT ' . SQL_NO_CACHE . " 209 sqgr.snmp_field_name, dtr.id as data_template_rrd_id 210 FROM snmp_query_graph_rrd AS sqgr 211 INNER JOIN data_template_rrd AS dtr FORCE INDEX (local_data_id) 212 ON sqgr.data_template_rrd_id = dtr.local_data_template_rrd_id 213 WHERE sqgr.data_template_id = ? 214 AND dtr.local_data_id = ? 215 $output_type_sql 216 ORDER BY dtr.id", $params); 217 } 218 219 if ($data_input['active'] == 'on') { 220 if (($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT) || 221 ($data_input['type_id'] == DATA_INPUT_TYPE_PHP_SCRIPT_SERVER)) { 222 if ($data_input['type_id'] == DATA_INPUT_TYPE_PHP_SCRIPT_SERVER) { 223 $action = POLLER_ACTION_SCRIPT_PHP; 224 $script_path = get_full_script_path($data_source['id']); 225 } else { 226 $action = POLLER_ACTION_SCRIPT; 227 $script_path = get_full_script_path($data_source['id']); 228 } 229 230 $num_output_fields = cacti_sizeof(db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' id 231 FROM data_input_fields 232 WHERE data_input_id = ? 233 AND input_output = "out" 234 AND update_rra="on"', 235 array($data_input['id']))); 236 237 if ($num_output_fields == 1) { 238 $data_template_rrd_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id 239 FROM data_template_rrd 240 WHERE local_data_id = ?', 241 array($data_source['id'])); 242 243 $data_source_item_name = get_data_source_item_name($data_template_rrd_id); 244 } else { 245 $data_source_item_name = ''; 246 } 247 248 $poller_items[] = api_poller_cache_item_add($data_source['host_id'], array(), $data_source['id'], $data_input['rrd_step'], $action, $data_source_item_name, 1, $script_path); 249 } elseif ($data_input['type_id'] == DATA_INPUT_TYPE_SNMP) { 250 /* get the host override fields */ 251 $data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id 252 FROM data_template_data 253 WHERE local_data_id = ?', 254 array($data_source['id'])); 255 256 /* get host fields first */ 257 $host_fields = array_rekey( 258 db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value 259 FROM data_input_fields AS dif 260 LEFT JOIN data_input_data AS did 261 ON dif.id=did.data_input_field_id 262 WHERE (type_code LIKE "snmp_%" OR type_code IN("hostname","host_id")) 263 AND did.data_template_data_id = ? 264 AND did.value != ""', array($data_input['data_template_data_id'])), 265 'type_code', 'value' 266 ); 267 268 $data_template_fields = array_rekey( 269 db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value 270 FROM data_input_fields AS dif 271 LEFT JOIN data_input_data AS did 272 ON dif.id=did.data_input_field_id 273 WHERE (type_code LIKE "snmp_%" OR type_code="hostname") 274 AND did.data_template_data_id = ? 275 AND data_template_data_id = ? 276 AND did.value != ""', array($data_template_id, $data_template_id)), 277 'type_code', 'value' 278 ); 279 280 if (cacti_sizeof($host_fields)) { 281 if (cacti_sizeof($data_template_fields)) { 282 foreach($data_template_fields as $key => $value) { 283 if (!isset($host_fields[$key])) { 284 $host_fields[$key] = $value; 285 } 286 } 287 } 288 } elseif (cacti_sizeof($data_template_fields)) { 289 $host_fields = $data_template_fields; 290 } 291 292 $data_template_rrd_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id 293 FROM data_template_rrd 294 WHERE local_data_id = ?', 295 array($data_source['id'])); 296 297 $poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], 0, get_data_source_item_name($data_template_rrd_id), 1, (isset($host_fields['snmp_oid']) ? $host_fields['snmp_oid'] : '')); 298 } elseif ($data_input['type_id'] == DATA_INPUT_TYPE_SNMP_QUERY) { 299 $snmp_queries = get_data_query_array($data_source['snmp_query_id']); 300 301 /* get the host override fields */ 302 $data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id 303 FROM data_template_data 304 WHERE local_data_id = ?', 305 array($data_source['id'])); 306 307 /* get host fields first */ 308 $host_fields = array_rekey( 309 db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value 310 FROM data_input_fields AS dif 311 LEFT JOIN data_input_data AS did 312 ON dif.id=did.data_input_field_id 313 WHERE (type_code LIKE "snmp_%" OR type_code="hostname") 314 AND did.data_template_data_id = ? 315 AND did.value != ""', array($data_input['data_template_data_id'])), 316 'type_code', 'value' 317 ); 318 319 $data_template_fields = array_rekey( 320 db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value 321 FROM data_input_fields AS dif 322 LEFT JOIN data_input_data AS did 323 ON dif.id=did.data_input_field_id 324 WHERE (type_code LIKE "snmp_%" OR type_code="hostname") 325 AND did.data_template_data_id = ? 326 AND data_template_data_id = ? 327 AND did.value != ""', array($data_template_id, $data_template_id)), 328 'type_code', 'value' 329 ); 330 331 if (cacti_sizeof($host_fields)) { 332 if (cacti_sizeof($data_template_fields)) { 333 foreach ($data_template_fields as $key => $value) { 334 if (!isset($host_fields[$key])) { 335 $host_fields[$key] = $value; 336 } 337 } 338 } 339 } elseif (cacti_sizeof($data_template_fields)) { 340 $host_fields = $data_template_fields; 341 } 342 343 if (cacti_sizeof($outputs) && cacti_sizeof($snmp_queries)) { 344 foreach ($outputs as $output) { 345 if (isset($snmp_queries['fields'][$output['snmp_field_name']]['oid'])) { 346 $oid = $snmp_queries['fields'][$output['snmp_field_name']]['oid'] . '.' . $data_source['snmp_index']; 347 348 if (isset($snmp_queries['fields'][$output['snmp_field_name']]['oid_suffix'])) { 349 $oid .= '.' . $snmp_queries['fields'][$output['snmp_field_name']]['oid_suffix']; 350 } 351 } 352 353 if (!empty($oid)) { 354 $poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], 0, get_data_source_item_name($output['data_template_rrd_id']), cacti_sizeof($outputs), $oid); 355 } 356 } 357 } 358 } elseif (($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT_QUERY) || 359 ($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER)) { 360 $script_queries = get_data_query_array($data_source['snmp_query_id']); 361 362 /* get the host override fields */ 363 $data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id 364 FROM data_template_data 365 WHERE local_data_id = ?', 366 array($data_source['id'])); 367 368 /* get host fields first */ 369 $host_fields = array_rekey( 370 db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value 371 FROM data_input_fields AS dif 372 LEFT JOIN data_input_data AS did 373 ON dif.id=did.data_input_field_id 374 WHERE (type_code LIKE "snmp_%" OR type_code="hostname") 375 AND did.data_template_data_id = ? 376 AND did.value != ""', array($data_input['data_template_data_id'])), 377 'type_code', 'value' 378 ); 379 380 $data_template_fields = array_rekey( 381 db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value 382 FROM data_input_fields AS dif 383 LEFT JOIN data_input_data AS did 384 ON dif.id=did.data_input_field_id 385 WHERE (type_code LIKE "snmp_%" OR type_code="hostname") 386 AND data_template_data_id = ? 387 AND did.data_template_data_id = ? 388 AND did.value != ""', array($data_template_id, $data_template_id)), 389 'type_code', 'value' 390 ); 391 392 if (cacti_sizeof($host_fields)) { 393 if (cacti_sizeof($data_template_fields)) { 394 foreach ($data_template_fields as $key => $value) { 395 if (!isset($host_fields[$key])) { 396 $host_fields[$key] = $value; 397 } 398 } 399 } 400 } elseif (cacti_sizeof($data_template_fields)) { 401 $host_fields = $data_template_fields; 402 } 403 404 if (cacti_sizeof($outputs) && cacti_sizeof($script_queries)) { 405 foreach ($outputs as $output) { 406 if (isset($script_queries['fields'][$output['snmp_field_name']]['query_name'])) { 407 $identifier = $script_queries['fields'][$output['snmp_field_name']]['query_name']; 408 409 if ($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER) { 410 $action = POLLER_ACTION_SCRIPT_PHP; 411 412 $prepend = ''; 413 if (isset($script_queries['arg_prepend']) && $script_queries['arg_prepend'] != '') { 414 $prepend = $script_queries['arg_prepend']; 415 } 416 417 $script_path = get_script_query_path(trim($prepend . ' ' . $script_queries['arg_get'] . ' ' . $identifier . ' ' . $data_source['snmp_index']), $script_queries['script_path'] . ' ' . $script_queries['script_function'], $data_source['host_id']); 418 } else { 419 $action = POLLER_ACTION_SCRIPT; 420 $script_path = get_script_query_path(trim((isset($script_queries['arg_prepend']) ? $script_queries['arg_prepend'] : '') . ' ' . $script_queries['arg_get'] . ' ' . $identifier . ' ' . $data_source['snmp_index']), $script_queries['script_path'], $data_source['host_id']); 421 } 422 } 423 424 if (isset($script_path)) { 425 $poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], $action, get_data_source_item_name($output['data_template_rrd_id']), cacti_sizeof($outputs), $script_path); 426 } 427 } 428 } 429 } else { 430 $arguments = array( 431 'poller_items' => $poller_items, 432 'data_source' => $data_source, 433 'data_input' => $data_input 434 ); 435 436 $arguments = api_plugin_hook_function('data_source_to_poller_items', $arguments); 437 438 // Process the returned poller items 439 if ((isset($arguments['poller_items'])) && 440 (is_array($arguments['poller_items'])) && 441 (cacti_sizeof($poller_items) < cacti_sizeof($arguments['poller_items']))) { 442 $poller_items = $arguments['poller_items']; 443 } 444 } 445 } 446 } else { 447 $data_template_data = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' * 448 FROM data_template_data 449 WHERE local_data_id = ?', 450 array($data_source['id'])); 451 452 if (cacti_sizeof($data_template_data) && $data_template_data['data_input_id'] > 0) { 453 cacti_log('WARNING: Repopulate Poller Cache found Data Input Missing for Data Source ' . $data_source['id'] . '. Database may be corrupted'); 454 } 455 } 456 457 if ($commit && cacti_sizeof($poller_items)) { 458 poller_update_poller_cache_from_buffer((array)$data_source['id'], $poller_items, $poller_id); 459 } elseif (!$commit) { 460 return $poller_items; 461 } 462} 463 464function push_out_data_input_method($data_input_id) { 465 $data_sources = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dl.*, h.poller_id 466 FROM data_local AS dl 467 INNER JOIN ( 468 SELECT DISTINCT local_data_id 469 FROM data_template_data 470 WHERE data_input_id = ? 471 AND local_data_id > 0 472 ) AS dtd 473 ON dtd.local_data_id = dl.id 474 INNER JOIN host AS h 475 ON h.id = dl.host_id 476 WHERE dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != "") 477 ORDER BY h.poller_id ASC', 478 array($data_input_id)); 479 480 $poller_items = array(); 481 $_my_local_data_ids = array(); 482 483 if (cacti_sizeof($data_sources)) { 484 $prev_poller = -1; 485 foreach ($data_sources as $data_source) { 486 if ($prev_poller > 0 && $data_source['poller_id'] != $prev_poller) { 487 poller_update_poller_cache_from_buffer($_my_local_data_ids, $poller_items, $prev_poller); 488 $_my_local_data_ids = array(); 489 $poller_items = array(); 490 } else { 491 $_my_local_data_ids[] = $data_source['id']; 492 $poller_items = array_merge($poller_items, update_poller_cache($data_source)); 493 } 494 495 $prev_poller = $data_source['poller_id']; 496 } 497 498 if (cacti_sizeof($_my_local_data_ids)) { 499 poller_update_poller_cache_from_buffer($_my_local_data_ids, $poller_items, $prev_poller); 500 } 501 } 502} 503 504/** mass update of poller cache - can run in parallel to poller 505 * @param array/int $local_data_ids - either a scalar (all ids) or an array of data source to act on 506 * @param array $poller_items - the new items for poller cache 507 * @param int $poller_id - the poller_id of the buffer 508 */ 509function poller_update_poller_cache_from_buffer($local_data_ids, &$poller_items, $poller_id = 1) { 510 global $config; 511 512 /* set all fields present value to 0, to mark the outliers when we are all done */ 513 $ids = ''; 514 if (cacti_sizeof($local_data_ids)) { 515 $ids = implode(', ', $local_data_ids); 516 517 if ($ids != '') { 518 db_execute_prepared("UPDATE poller_item 519 SET present=0 520 WHERE poller_id = ? 521 AND local_data_id IN ($ids)", 522 array($poller_id)); 523 524 if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) { 525 db_execute_prepared("UPDATE poller_item 526 SET present=0 527 WHERE poller_id = ? 528 AND local_data_id IN ($ids)", 529 array($poller_id), true, $rcnn_id); 530 } 531 } 532 } else { 533 /* don't mark anything in case we have no $local_data_ids => 534 *this would flush the whole table at bottom of this function */ 535 } 536 537 /* setup the database call */ 538 $sql_prefix = 'INSERT INTO poller_item (local_data_id, poller_id, host_id, action, hostname, ' . 539 'snmp_community, snmp_version, snmp_timeout, snmp_username, snmp_password, ' . 540 'snmp_auth_protocol, snmp_priv_passphrase, snmp_priv_protocol, snmp_context, snmp_engine_id, ' . 541 'snmp_port, rrd_name, rrd_path, rrd_num, rrd_step, rrd_next_step, arg1, arg2, arg3, present) ' . 542 'VALUES'; 543 544 $sql_suffix = ' ON DUPLICATE KEY UPDATE poller_id=VALUES(poller_id), host_id=VALUES(host_id), action=VALUES(action), hostname=VALUES(hostname), ' . 545 'snmp_community=VALUES(snmp_community), snmp_version=VALUES(snmp_version), snmp_timeout=VALUES(snmp_timeout), ' . 546 'snmp_username=VALUES(snmp_username), snmp_password=VALUES(snmp_password), snmp_auth_protocol=VALUES(snmp_auth_protocol), ' . 547 'snmp_priv_passphrase=VALUES(snmp_priv_passphrase), snmp_priv_protocol=VALUES(snmp_priv_protocol), ' . 548 'snmp_context=VALUES(snmp_context), snmp_engine_id=VALUES(snmp_engine_id), snmp_port=VALUES(snmp_port), ' . 549 'rrd_path=VALUES(rrd_path), rrd_num=VALUES(rrd_num), ' . 550 'rrd_step=VALUES(rrd_step), rrd_next_step=VALUES(rrd_next_step), arg1=VALUES(arg1), arg2=VALUES(arg2), ' . 551 'arg3=VALUES(arg3), present=VALUES(present)'; 552 553 /* use a reasonable insert buffer, the default is 1MByte */ 554 $max_packet = 256000; 555 556 /* setup somme defaults */ 557 $overhead = strlen($sql_prefix) + strlen($sql_suffix); 558 $buf_len = 0; 559 $buf_count = 0; 560 $buffer = ''; 561 562 if (cacti_sizeof($poller_items)) { 563 foreach ($poller_items as $record) { 564 /* take care of invalid entries */ 565 if ($record == '') { 566 continue; 567 } 568 569 if ($buf_count == 0) { 570 $delim = ' '; 571 } else { 572 $delim = ', '; 573 } 574 575 $buffer .= $delim . $record; 576 577 $buf_len += strlen($record); 578 579 if ($overhead + $buf_len > $max_packet - 1024) { 580 db_execute($sql_prefix . $buffer . $sql_suffix); 581 582 if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) { 583 db_execute($sql_prefix . $buffer . $sql_suffix, true, $rcnn_id); 584 } 585 586 $buffer = ''; 587 $buf_len = 0; 588 $buf_count = 0; 589 } else { 590 $buf_count++; 591 } 592 } 593 } 594 595 if ($buf_count > 0) { 596 db_execute($sql_prefix . $buffer . $sql_suffix); 597 598 if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) { 599 db_execute($sql_prefix . $buffer . $sql_suffix, true, $rcnn_id); 600 } 601 } 602 603 /* remove stale records FROM the poller cache */ 604 if ($ids != '') { 605 db_execute_prepared("DELETE FROM poller_item 606 WHERE present = 0 607 AND poller_id = ? 608 AND local_data_id IN ($ids)", 609 array($poller_id)); 610 611 if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) { 612 db_execute_prepared("DELETE FROM poller_item 613 WHERE present = 0 614 AND poller_id = ? 615 AND local_data_id IN ($ids)", 616 array($poller_id), true, $rcnn_id); 617 } 618 } else { 619 /* only handle explicitely given local_data_ids */ 620 } 621} 622 623/** for a given data template, update all input data and the poller cache 624 * @param int $host_id - id of host, if any 625 * @param int $local_data_id - id of a single data source, if any 626 * @param int $data_template_id - id of data template 627 * works on table data_input_data and poller cache 628 */ 629function push_out_host($host_id, $local_data_id = 0, $data_template_id = 0) { 630 global $config; 631 632 include_once($config['library_path'] . '/api_data_source.php'); 633 634 /* ok here's the deal: first we need to find every data source that uses this host. 635 then we go through each of those data sources, finding each one using a data input method 636 with "special fields". if we find one, fill it will the data here FROM this host */ 637 /* setup the poller items array */ 638 $poller_items = array(); 639 $local_data_ids = array(); 640 $hosts = array(); 641 $template_fields = array(); 642 $sql_where = ''; 643 644 /* setup the sql where, and if using a host, get it's host information */ 645 if ($host_id != 0) { 646 /* get all information about this host so we can write it to the data source */ 647 $hosts[$host_id] = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' id AS host_id, host.* 648 FROM host WHERE id = ?', 649 array($host_id)); 650 651 $sql_where .= ' AND dl.host_id=' . $host_id; 652 } 653 654 /* sql WHERE for local_data_id */ 655 if ($local_data_id != 0) { 656 $sql_where .= ' AND dl.id=' . $local_data_id; 657 } 658 659 /* sql WHERE for data_template_id */ 660 if ($data_template_id != 0) { 661 $sql_where .= ' AND dtd.data_template_id=' . $data_template_id; 662 } 663 664 $data_sources = db_fetch_assoc('SELECT ' . SQL_NO_CACHE . " dtd.id, 665 dtd.data_input_id, dtd.local_data_id, 666 dtd.local_data_template_data_id, dl.host_id, 667 dl.snmp_query_id, dl.snmp_index 668 FROM data_local AS dl 669 INNER JOIN data_template_data AS dtd 670 ON dl.id=dtd.local_data_id 671 WHERE dtd.data_input_id>0 672 AND (dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != '')) 673 $sql_where"); 674 675 /* loop through each matching data source */ 676 if (cacti_sizeof($data_sources)) { 677 foreach ($data_sources as $data_source) { 678 /* set the host information */ 679 if (!isset($hosts[$data_source['host_id']])) { 680 $hosts[$data_source['host_id']] = db_fetch_row_prepared('SELECT * 681 FROM host 682 WHERE id = ?', 683 array($data_source['host_id'])); 684 } 685 $host = $hosts[$data_source['host_id']]; 686 687 /* get field information FROM the data template */ 688 if (!isset($template_fields[$data_source['local_data_template_data_id']])) { 689 $template_fields[$data_source['local_data_template_data_id']] = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' 690 did.value, did.t_value, dif.id, dif.type_code 691 FROM data_input_fields AS dif 692 LEFT JOIN data_input_data AS did 693 ON dif.id=did.data_input_field_id 694 WHERE dif.data_input_id = ? 695 AND did.data_template_data_id = ? 696 AND (did.t_value="" OR did.t_value is null) 697 AND dif.input_output = "in"', 698 array($data_source['data_input_id'], $data_source['local_data_template_data_id'])); 699 } 700 701 /* loop through each field contained in the data template and push out a host value if: 702 - the field is a valid "host field" 703 - the value of the field is empty 704 - the field is set to 'templated' */ 705 if (cacti_sizeof($template_fields[$data_source['local_data_template_data_id']])) { 706 foreach ($template_fields[$data_source['local_data_template_data_id']] as $template_field) { 707 if (preg_match('/^' . VALID_HOST_FIELDS . '$/i', $template_field['type_code']) && $template_field['value'] == '' && $template_field['t_value'] == '') { 708 // handle special case type_code 709 if ($template_field['type_code'] == 'host_id') { 710 $template_field['type_code'] = 'id'; 711 } 712 713 db_execute_prepared('REPLACE INTO data_input_data 714 (data_input_field_id, data_template_data_id, value) 715 VALUES (?, ?, ?)', 716 array($template_field['id'], $data_source['id'], $host[$template_field['type_code']])); 717 } 718 } 719 } 720 721 /* flag an update to the poller cache as well */ 722 $local_data_ids[] = $data_source['local_data_id']; 723 724 /* create a new compatible structure */ 725 $data = $data_source; 726 $data['id'] = $data['local_data_id']; 727 728 $poller_items = array_merge($poller_items, update_poller_cache($data)); 729 } 730 } 731 732 if (cacti_sizeof($hosts)) { 733 foreach($hosts as $host) { 734 if (isset($host['poller_id'])) { 735 $poller_ids[$host['poller_id']] = $host['poller_id']; 736 } 737 } 738 739 if (cacti_sizeof($poller_ids) > 1) { 740 cacti_log('WARNING: function push_out_host() discovered more than a single host', false, 'POLLER'); 741 } 742 } 743 744 $poller_id = db_fetch_cell_prepared('SELECT poller_id 745 FROM host 746 WHERE id = ?', 747 array($host_id)); 748 749 if (cacti_sizeof($local_data_ids)) { 750 poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id); 751 } 752 753 api_data_source_cache_crc_update($poller_id); 754} 755 756function data_input_whitelist_check($data_input_id) { 757 global $config; 758 759 static $data_input_whitelist = null; 760 static $validated_input_ids = null; 761 static $notified = array(); 762 763 // no whitelist file defined, everything whitelisted 764 if (!isset($config['input_whitelist'])) { 765 return true; 766 } 767 768 // whitelist is configured but does not exist, means nothing whitelisted 769 if (!file_exists($config['input_whitelist'])) { 770 return false; 771 } 772 773 // load whitelist, but only once within process execution 774 if ($data_input_whitelist == null) { 775 $data_input_ids = array_rekey( 776 db_fetch_assoc('SELECT * FROM data_input'), 777 'hash', array('id', 'name', 'input_string') 778 ); 779 780 $data_input_whitelist = json_decode(file_get_contents($config['input_whitelist']), true); 781 if ($data_input_whitelist === null) { 782 cacti_log('ERROR: Failed to parse input whitelist file: ' . $config['input_whitelist']); 783 return true; 784 } 785 786 if (cacti_sizeof($data_input_ids)) { 787 foreach ($data_input_ids as $hash => $id) { 788 if ($id['input_string'] != '') { 789 if (isset($data_input_whitelist[$hash])) { 790 if ($data_input_whitelist[$hash] == $id['input_string']) { 791 $validated_input_ids[$id['id']] = true; 792 } else { 793 cacti_log('ERROR: Whitelist entry failed validation for Data Input: ' . $id['name'] . '[ ' . $id['id'] . ' ]. Data Collection will not run. Run CLI command input_whitelist.php --audit and --update to remediate.'); 794 $validated_input_ids[$id['id']] = false; 795 } 796 } else { 797 cacti_log('WARNING: Whitelist entry missing for Data Input: ' . $id['name'] . '[ ' . $id['id'] . ' ]. Run CLI command input_whitelist.php --update to remediate.'); 798 $validated_input_ids[$id['id']] = true; 799 } 800 } else { 801 $validated_input_ids[$id['id']] = true; 802 } 803 } 804 } 805 } 806 807 if (isset($validated_input_ids[$data_input_id])) { 808 if ($validated_input_ids[$data_input_id] == true) { 809 return true; 810 } else { 811 cacti_log('WARNING: Data Input ' . $data_input_id . ' failing validation check.'); 812 $notified[$data_input_id] = true; 813 return false; 814 } 815 } else { 816 return true; 817 } 818} 819 820function utilities_get_mysql_recommendations() { 821 global $config, $local_db_cnn_id; 822 823 // MySQL/MariaDB Important Variables 824 // Assume we are successfully, until we aren't! 825 $result = DB_STATUS_SUCCESS; 826 827 if ($config['poller_id'] == 1) { 828 $variables = array_rekey(db_fetch_assoc('SHOW GLOBAL VARIABLES'), 'Variable_name', 'Value'); 829 } else { 830 $variables = array_rekey(db_fetch_assoc('SHOW GLOBAL VARIABLES', false, $local_db_cnn_id), 'Variable_name', 'Value'); 831 } 832 833 $memInfo = utilities_get_system_memory(); 834 835 if (strpos($variables['version'], 'MariaDB') !== false) { 836 $database = 'MariaDB'; 837 $version = str_replace('-MariaDB', '', $variables['version']); 838 839 if (isset($variables['innodb_version'])) { 840 $link_ver = substr($variables['innodb_version'], 0, 3); 841 } else { 842 $link_ver = '5.5'; 843 } 844 } else { 845 $database = 'MySQL'; 846 $version = $variables['version']; 847 $link_ver = substr($variables['version'], 0, 3); 848 } 849 850 $recommendations = array( 851 'version' => array( 852 'value' => '5.6', 853 'class' => 'warning', 854 'measure' => 'ge', 855 'comment' => __('MySQL 5.6+ and MariaDB 10.0+ are great releases, and are very good versions to choose. Make sure you run the very latest release though which fixes a long standing low level networking issue that was causing spine many issues with reliability.') 856 ) 857 ); 858 859 if (isset($variables['innodb_version']) && version_compare($variables['innodb_version'], '5.6', '<')) { 860 if (version_compare($link_ver, '5.5', '>=')) { 861 if (!isset($variables['innodb_version'])) { 862 $recommendations += array( 863 'innodb' => array( 864 'value' => 'ON', 865 'class' => 'warning', 866 'measure' => 'equalint', 867 'comment' => __('It is STRONGLY recommended that you enable InnoDB in any %s version greater than 5.5.3.', $database) 868 ) 869 ); 870 871 $variables['innodb'] = 'UNSET'; 872 } 873 } 874 875 $recommendations += array( 876 'collation_server' => array( 877 'value' => 'utf8mb4_unicode_ci', 878 'class' => 'warning', 879 'measure' => 'equal', 880 'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4_unicode_ci collation type as some characters take more than a single byte.') 881 ), 882 'character_set_server' => array( 883 'value' => 'utf8mb4', 884 'class' => 'warning', 885 'measure' => 'equal', 886 'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.') 887 ), 888 'character_set_client' => array( 889 'value' => 'utf8mb4', 890 'class' => 'warning', 891 'measure' => 'equal', 892 'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.') 893 ) 894 ); 895 } else { 896 if (version_compare($link_ver, '5.2', '>=')) { 897 if (!isset($variables['innodb_version'])) { 898 $recommendations += array( 899 'innodb' => array( 900 'value' => 'ON', 901 'class' => 'warning', 902 'measure' => 'equalint', 903 'comment' => __('It is recommended that you enable InnoDB in any %s version greater than 5.1.', $database) 904 ) 905 ); 906 907 $variables['innodb'] = 'UNSET'; 908 } 909 } 910 911 $recommendations += array( 912 'collation_server' => array( 913 'value' => 'utf8mb4_unicode_ci', 914 'measure' => 'equal', 915 'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4_unicode_ci collation type as some characters take more than a single byte.') 916 ), 917 'character_set_server' => array( 918 'value' => 'utf8mb4', 919 'class' => 'warning', 920 'measure' => 'equal', 921 'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.') 922 ), 923 'character_set_client' => array( 924 'value' => 'utf8mb4', 925 'class' => 'warning', 926 'measure' => 'equal', 927 'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.') 928 ) 929 ); 930 } 931 932 $recommendations += array( 933 'max_connections' => array( 934 'value' => '100', 935 'measure' => 'ge', 936 'comment' => __('Depending on the number of logins and use of spine data collector, %s will need many connections. The calculation for spine is: total_connections = total_processes * (total_threads + script_servers + 1), then you must leave headroom for user connections, which will change depending on the number of concurrent login accounts.', $database) 937 ), 938 'table_cache' => array( 939 'value' => '200', 940 'measure' => 'ge', 941 'comment' => __('Keeping the table cache larger means less file open/close operations when using innodb_file_per_table.') 942 ), 943 'max_allowed_packet' => array( 944 'value' => 16777216, 945 'measure' => 'ge', 946 'comment' => __('With Remote polling capabilities, large amounts of data will be synced from the main server to the remote pollers. Therefore, keep this value at or above 16M.') 947 ), 948 'max_heap_table_size' => array( 949 'value' => '1.6', 950 'measure' => 'pmem', 951 'class' => 'warning', 952 'comment' => __('If using the Cacti Performance Booster and choosing a memory storage engine, you have to be careful to flush your Performance Booster buffer before the system runs out of memory table space. This is done two ways, first reducing the size of your output column to just the right size. This column is in the tables poller_output, and poller_output_boost. The second thing you can do is allocate more memory to memory tables. We have arbitrarily chosen a recommended value of 10%% of system memory, but if you are using SSD disk drives, or have a smaller system, you may ignore this recommendation or choose a different storage engine. You may see the expected consumption of the Performance Booster tables under Console -> System Utilities -> View Boost Status.') 953 ), 954 'tmp_table_size' => array( 955 'value' => '1.6', 956 'measure' => 'pmem', 957 'class' => 'warning', 958 'comment' => __('When executing subqueries, having a larger temporary table size, keep those temporary tables in memory.') 959 ), 960 'join_buffer_size' => array( 961 'value' => '3.2', 962 'measure' => 'pmem', 963 'class' => 'warning', 964 'comment' => __('When performing joins, if they are below this size, they will be kept in memory and never written to a temporary file.') 965 ), 966 'innodb_file_per_table' => array( 967 'value' => 'ON', 968 'measure' => 'equalint', 969 'class' => 'error', 970 'comment' => __('When using InnoDB storage it is important to keep your table spaces separate. This makes managing the tables simpler for long time users of %s. If you are running with this currently off, you can migrate to the per file storage by enabling the feature, and then running an alter statement on all InnoDB tables.', $database) 971 ), 972 'innodb_file_format' => array( 973 'value' => 'Barracuda', 974 'measure' => 'equal', 975 'class' => 'error', 976 'comment' => __('When using innodb_file_per_table, it is important to set the innodb_file_format to Barracuda. This setting will allow longer indexes important for certain Cacti tables.') 977 ), 978 'innodb_large_prefix' => array( 979 'value' => '1', 980 'measure' => 'equalint', 981 'class' => 'error', 982 'comment' => __('If your tables have very large indexes, you must operate with the Barracuda innodb_file_format and the innodb_large_prefix equal to 1. Failure to do this may result in plugins that can not properly create tables.') 983 ), 984 'innodb_buffer_pool_size' => array( 985 'value' => '25', 986 'measure' => 'pmem', 987 'class' => 'warning', 988 'comment' => __('InnoDB will hold as much tables and indexes in system memory as is possible. Therefore, you should make the innodb_buffer_pool large enough to hold as much of the tables and index in memory. Checking the size of the /var/lib/mysql/cacti directory will help in determining this value. We are recommending 25%% of your systems total memory, but your requirements will vary depending on your systems size.') 989 ), 990 'innodb_doublewrite' => array( 991 'value' => 'ON', 992 'measure' => 'equalint', 993 'class' => 'error', 994 'comment' => __('This settings should remain ON unless your Cacti instances is running on either ZFS or FusionI/O which both have internal journaling to accomodate abrupt system crashes. However, if you have very good power, and your systems rarely go down and you have backups, turning this setting to OFF can net you almost a 50% increase in database performance.') 995 ), 996 'innodb_additional_mem_pool_size' => array( 997 'value' => '80M', 998 'measure' => 'gem', 999 'comment' => __('This is where metadata is stored. If you had a lot of tables, it would be useful to increase this.') 1000 ), 1001 'innodb_lock_wait_timeout' => array( 1002 'value' => '50', 1003 'measure' => 'ge', 1004 'comment' => __('Rogue queries should not for the database to go offline to others. Kill these queries before they kill your system.') 1005 ), 1006 'innodb_flush_method' => array( 1007 'value' => 'O_DIRECT', 1008 'measure' => 'eq', 1009 'comment' => __('Maximum I/O performance happens when you use the O_DIRECT method to flush pages.') 1010 ) 1011 ); 1012 1013 if (isset($variables['innodb_version'])) { 1014 if (version_compare($variables['innodb_version'], '5.6', '<')) { 1015 $recommendations += array( 1016 'innodb_flush_log_at_trx_commit' => array( 1017 'value' => '2', 1018 'measure' => 'equal', 1019 'comment' => __('Setting this value to 2 means that you will flush all transactions every second rather than at commit. This allows %s to perform writing less often.', $database) 1020 ), 1021 'innodb_file_io_threads' => array( 1022 'value' => '16', 1023 'measure' => 'ge', 1024 'comment' => __('With modern SSD type storage, having multiple io threads is advantageous for applications with high io characteristics.') 1025 ) 1026 ); 1027 } elseif (version_compare($variables['innodb_version'], '10.5', '<')) { 1028 $recommendations += array( 1029 'innodb_flush_log_at_timeout' => array( 1030 'value' => '3', 1031 'measure' => 'ge', 1032 'comment' => __('As of %s %s, the you can control how often %s flushes transactions to disk. The default is 1 second, but in high I/O systems setting to a value greater than 1 can allow disk I/O to be more sequential', $database, $version, $database), 1033 ), 1034 'innodb_read_io_threads' => array( 1035 'value' => '32', 1036 'measure' => 'ge', 1037 'comment' => __('With modern SSD type storage, having multiple read io threads is advantageous for applications with high io characteristics. Depending on your MariaDB/MySQL versions, this value can go as high as 64. But try to keep the number less than your total SMT threads on the database server.') 1038 ), 1039 'innodb_write_io_threads' => array( 1040 'value' => '16', 1041 'measure' => 'ge', 1042 'comment' => __('With modern SSD type storage, having multiple write io threads is advantageous for applications with high io characteristics. Depending on your MariaDB/MySQL versions, this value can go as high as 64. But try to keep the number less than your total SMT threads on the database server.') 1043 ), 1044 'innodb_buffer_pool_instances' => array( 1045 'value' => '16', 1046 'measure' => 'pinst', 1047 'class' => 'warning', 1048 'comment' => __('%s will divide the innodb_buffer_pool into memory regions to improve performance for versions of MariaDB less than 10.5. The max value is 64. When your innodb_buffer_pool is less than 1GB, you should use the pool size divided by 128MB. Continue to use this equation upto the max of 64.', $database) 1049 ), 1050 'innodb_io_capacity' => array( 1051 'value' => '5000', 1052 'measure' => 'ge', 1053 'class' => 'warning', 1054 'comment' => __('If you have SSD disks, use this suggestion. If you have physical hard drives, use 200 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 100000 can be used.') 1055 ), 1056 'innodb_io_capacity_max' => array( 1057 'value' => '10000', 1058 'measure' => 'ge', 1059 'class' => 'warning', 1060 'comment' => __('If you have SSD disks, use this suggestion. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used.') 1061 ), 1062 'innodb_flush_neighbor_pages' => array( 1063 'value' => 'none', 1064 'measure' => 'eq', 1065 'class' => 'warning', 1066 'comment' => __('If you have SSD disks, use this suggestion. Otherwise, do not set this setting.') 1067 ) 1068 ); 1069 } else { 1070 $recommendations += array( 1071 'innodb_flush_log_at_timeout' => array( 1072 'value' => '3', 1073 'measure' => 'ge', 1074 'comment' => __('As of %s %s, the you can control how often %s flushes transactions to disk. The default is 1 second, but in high I/O systems setting to a value greater than 1 can allow disk I/O to be more sequential', $database, $version, $database), 1075 ), 1076 'innodb_read_io_threads' => array( 1077 'value' => '32', 1078 'measure' => 'ge', 1079 'comment' => __('With modern SSD type storage, having multiple read io threads is advantageous for applications with high io characteristics.') 1080 ), 1081 'innodb_write_io_threads' => array( 1082 'value' => '16', 1083 'measure' => 'ge', 1084 'comment' => __('With modern SSD type storage, having multiple write io threads is advantageous for applications with high io characteristics.') 1085 ), 1086 'innodb_io_capacity' => array( 1087 'value' => '5000', 1088 'measure' => 'ge', 1089 'class' => 'warning', 1090 'comment' => __('If you have SSD disks, use this suggestion. If you have physical hard drives, use 200 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 100000 can be used.') 1091 ), 1092 'innodb_io_capacity_max' => array( 1093 'value' => '10000', 1094 'measure' => 'ge', 1095 'class' => 'warning', 1096 'comment' => __('If you have SSD disks, use this suggestion. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used.') 1097 ), 1098 'innodb_flush_neighbor_pages' => array( 1099 'value' => 'none', 1100 'measure' => 'eq', 1101 'class' => 'warning', 1102 'comment' => __('If you have SSD disks, use this suggestion. Otherwise, do not set this setting.') 1103 ) 1104 ); 1105 1106 unset($recommendations['innodb_additional_mem_pool_size']); 1107 } 1108 } 1109 1110 if ($database == 'MariaDB' && version_compare($version, '10.2.4', '>')) { 1111 $recommendations['innodb_doublewrite'] = array( 1112 'value' => 'OFF', 1113 'measure' => 'equalint', 1114 'class' => 'error', 1115 'comment' => __('When using MariaDB 10.2.4 and above, this setting should be off if atomic writes are enabled. Therefore, please enable atomic writes instead of the double write buffer as it will increase performance.') 1116 ); 1117 1118 $recommendations['innodb_use_atomic_writes'] = array( 1119 'value' => 'ON', 1120 'measure' => 'equalint', 1121 'class' => 'error', 1122 'comment' => __('When using MariaDB 10.2.4 and above, you can use atomic writes over the doublewrite buffer to increase performance.') 1123 ); 1124 } 1125 1126 if (file_exists('/etc/my.cnf.d/server.cnf')) { 1127 $location = '/etc/my.cnf.d/server.cnf'; 1128 } else { 1129 $location = '/etc/my.cnf'; 1130 } 1131 1132 if ($database == 'MariaDB') { 1133 $variables_url = 'https://mariadb.com/kb/en/server-system-variables/'; 1134 } else { 1135 $variables_url = html_escape('https://dev.mysql.com/doc/refman/' . $link_ver . '/en/server-system-variables.html'); 1136 } 1137 1138 print '<tr class="tableHeader tableFixed">'; 1139 print '<th colspan="2">' . __('%s Tuning', $database) . ' (' . $location . ') - [ <a class="linkOverDark" target="_blank" href="' . $variables_url . '">' . __('Documentation') . '</a> ] ' . __('Note: Many changes below require a database restart') . '</th>'; 1140 print '</tr>'; 1141 1142 form_alternate_row(); 1143 print "<td colspan='2' style='text-align:left;padding:0px'>"; 1144 print "<table id='mysql' class='cactiTable' style='width:100%'>"; 1145 print "<thead>"; 1146 print "<tr class='tableHeader'>"; 1147 print " <th class='tableSubHeaderColumn'>" . __('Variable') . "</th>"; 1148 print " <th class='tableSubHeaderColumn right'>" . __('Current Value'). "</th>"; 1149 print " <th class='tableSubHeaderColumn center'> </th>"; 1150 print " <th class='tableSubHeaderColumn'>" . __('Recommended Value') . "</th>"; 1151 print " <th class='tableSubHeaderColumn'>" . __('Comments') . "</th>"; 1152 print "</tr>"; 1153 print "</thead>"; 1154 1155 $innodb_pool_size = 0; 1156 foreach ($recommendations as $name => $r) { 1157 if (isset($variables[$name])) { 1158 $class = ''; 1159 1160 // Unset $passed so that we only display fields that we checked 1161 unset($passed); 1162 1163 $compare = ''; 1164 $value_recommend = isset($r['value']) ? $r['value'] : '<unset>'; 1165 $value_current = isset($variables[$name]) ? $variables[$name] : '<unset>'; 1166 $value_display = $value_current; 1167 1168 switch($r['measure']) { 1169 case 'gem': 1170 $compare = '>='; 1171 $value_display = ($variables[$name]/1024/1024) . ' M'; 1172 $value = trim($r['value'], 'M') * 1024 * 1024; 1173 if ($variables[$name] < $value) { 1174 $passed = false; 1175 } 1176 break; 1177 case 'ge': 1178 $compare = '>='; 1179 $passed = (version_compare($value_current, $value_recommend, '>=')); 1180 break; 1181 case 'equalint': 1182 case 'equal': 1183 $compare = '='; 1184 if (!isset($variables[$name])) { 1185 $passed = false; 1186 } else { 1187 $e_var = $variables[$name]; 1188 $e_rec = $value_recommend; 1189 if ($r['measure'] == 'equalint') { 1190 $e_var = (!strcasecmp('on', $e_var) ? 1 : (!strcasecmp('off', $e_var) ? 0 : $e_var)); 1191 $e_rec = (!strcasecmp('on', $e_rec) ? 1 : (!strcasecmp('off', $e_rec) ? 0 : $e_rec)); 1192 } 1193 $passed = $e_var == $e_rec; 1194 } 1195 break; 1196 case 'pmem': 1197 if (isset($memInfo['MemTotal'])) { 1198 $totalMem = $memInfo['MemTotal']; 1199 } elseif (isset($memInfo['TotalVisibleMemorySize'])) { 1200 $totalMem = $memInfo['TotalVisibleMemorySize']; 1201 } else { 1202 break; 1203 } 1204 1205 if ($name == 'innodb_buffer_pool_size') { 1206 $innodb_pool_size = $variables[$name]; 1207 } 1208 1209 $compare = '>='; 1210 $passed = ($variables[$name] >= ($r['value']*$totalMem/100)); 1211 $value_display = round($variables[$name]/1024/1024, 2) . ' M'; 1212 $value_recommend = round($r['value']*$totalMem/100/1024/1024, 2) . ' M'; 1213 break; 1214 case 'pinst': 1215 $compare = '>='; 1216 1217 // Divide the buffer pool size by 128MB, and ensure 1 or more 1218 $pool_instances = round(($innodb_pool_size / 1024 / 1024 / 128) + 0.5); 1219 1220 if ($pool_instances < 1) { 1221 $pool_instances = 1; 1222 } elseif ($pool_instances > 64) { 1223 $pool_instances = 64; 1224 } 1225 1226 $passed = ($variables[$name] >= $pool_instances); 1227 $value_recommend = $pool_instances; 1228 break; 1229 default: 1230 $compare = $r['measure']; 1231 $passed = true; 1232 } 1233 1234 if (isset($passed)) { 1235 if (!$passed) { 1236 if (isset($r['class']) && $r['class'] == 'warning') { 1237 $class = 'textWarning'; 1238 if ($result == DB_STATUS_SUCCESS) { 1239 $result = DB_STATUS_WARNING; 1240 } 1241 } else { 1242 $class = 'textError'; 1243 if ($result != DB_STATUS_ERROR) { 1244 $result = DB_STATUS_ERROR; 1245 } 1246 } 1247 } 1248 1249 form_alternate_row(); 1250 1251 print "<td>" . $name . "</td>"; 1252 print "<td class='right $class'>$value_display</td>"; 1253 print "<td class='center'>$compare</td>"; 1254 print "<td>$value_recommend</td>"; 1255 print "<td class='$class'>" . $r['comment'] . "</td>"; 1256 1257 form_end_row(); 1258 } 1259 1260 } 1261 } 1262 print "</table>"; 1263 print "</td>"; 1264 form_end_row(); 1265 return $result; 1266} 1267 1268function utilities_php_modules() { 1269 /* 1270 Gather phpinfo into a string variable - This has to be done before 1271 any headers are sent to the browser, as we are going to do some 1272 output buffering fun 1273 */ 1274 1275 ob_start(); 1276 phpinfo(INFO_MODULES); 1277 $php_info = ob_get_contents(); 1278 ob_end_clean(); 1279 1280 /* Remove nasty style sheets, links and other junk */ 1281 $php_info = str_replace("\n", '', $php_info); 1282 $php_info = preg_replace('/^.*\<body\>/', '', $php_info); 1283 $php_info = preg_replace('/\<\/body\>.*$/', '', $php_info); 1284 $php_info = preg_replace('/(\<a name.*\>)([^<>]*)(\<\/a\>)/U', '$2', $php_info); 1285 $php_info = preg_replace('/\<img.*\>/U', '', $php_info); 1286 $php_info = preg_replace('/\<div[^<>]*\>\<\/div\>/U', '', $php_info); 1287 $php_info = preg_replace('/\<\/?address\>/', '', $php_info); 1288 1289 return $php_info; 1290} 1291 1292function memory_bytes($val) { 1293 $val = trim($val); 1294 $last = strtolower($val[strlen($val)-1]); 1295 $val = trim($val, 'GMKgmk'); 1296 switch($last) { 1297 case 'g': 1298 $val *= 1024; 1299 case 'm': 1300 $val *= 1024; 1301 case 'k': 1302 $val *= 1024; 1303 } 1304 1305 return $val; 1306} 1307 1308function memory_readable($val) { 1309 if ($val < 1024) { 1310 $val_label = 'bytes'; 1311 } elseif ($val < 1048576) { 1312 $val_label = 'K'; 1313 $val /= 1024; 1314 } elseif ($val < 1073741824) { 1315 $val_label = 'M'; 1316 $val /= 1048576; 1317 } else { 1318 $val_label = 'G'; 1319 $val /= 1073741824; 1320 } 1321 1322 return $val . $val_label; 1323} 1324 1325function utilities_get_system_memory() { 1326 global $config; 1327 1328 $memInfo = array(); 1329 1330 if ($config['cacti_server_os'] == 'win32') { 1331 exec('wmic os get FreePhysicalMemory', $memInfo['FreePhysicalMemory']); 1332 exec('wmic os get FreeSpaceInPagingFiles', $memInfo['FreeSpaceInPagingFiles']); 1333 exec('wmic os get FreeVirtualMemory', $memInfo['FreeVirtualMemory']); 1334 exec('wmic os get SizeStoredInPagingFiles', $memInfo['SizeStoredInPagingFiles']); 1335 exec('wmic os get TotalVirtualMemorySize', $memInfo['TotalVirtualMemorySize']); 1336 exec('wmic os get TotalVisibleMemorySize', $memInfo['TotalVisibleMemorySize']); 1337 if (cacti_sizeof($memInfo)) { 1338 foreach ($memInfo as $key => $values) { 1339 $memInfo[$key] = $values[1] * 1000; 1340 } 1341 } 1342 } else { 1343 $file = ''; 1344 if (file_exists('/proc/meminfo')) { 1345 $file = '/proc/meminfo'; 1346 } elseif(file_exists('/linux/proc/meminfo')) { 1347 $file = '/linux/proc/meminfo'; 1348 } elseif(file_exists('/compat/linux/proc/meminfo')) { 1349 $file = '/compat/linux/proc/meminfo'; 1350 } elseif(file_exists('/usr/compat/linux/proc/meminfo')) { 1351 $file = '/usr/compat/linux/proc/meminfo'; 1352 } 1353 1354 if ($file != '') { 1355 $data = explode("\n", file_get_contents($file)); 1356 foreach ($data as $l) { 1357 if (trim($l) != '') { 1358 list($key, $val) = explode(':', $l); 1359 $val = trim($val, " kBb\r\n"); 1360 $memInfo[$key] = round($val * 1000,0); 1361 } 1362 } 1363 } elseif (file_exists('/usr/bin/free')) { 1364 $menInfo = array(); 1365 $output = array(); 1366 $exit_code = 0; 1367 1368 exec('/usr/bin/free', $output, $exit_code); 1369 if ($exit_code == 0) { 1370 foreach ($output as $line) { 1371 $parts = preg_split('/\s+/', $line); 1372 switch ($parts[0]) { 1373 case 'Mem:': 1374 $memInfo['MemTotal'] = (isset($parts[1]) ? $parts[1]*1000:0); 1375 $memInfo['MemUsed'] = (isset($parts[2]) ? $parts[2]*1000:0); 1376 $memInfo['MemFree'] = (isset($parts[3]) ? $parts[3]*1000:0); 1377 $memInfo['MemShared'] = (isset($parts[4]) ? $parts[4]*1000:0); 1378 $memInfo['Buffers'] = (isset($parts[5]) ? $parts[5]*1000:0); 1379 $memInfo['Cached'] = (isset($parts[6]) ? $parts[6]*1000:0); 1380 break; 1381 case '-/+': 1382 $memInfo['Active'] = (isset($parts[2]) ? $parts[3]*1000:0); 1383 $memInfo['Inactive'] = (isset($parts[3]) ? $parts[3]*1000:0); 1384 break; 1385 case 'Swap:': 1386 $memInfo['SwapTotal'] = (isset($parts[1]) ? $parts[1]*1000:0); 1387 $memInfo['SwapUsed'] = (isset($parts[2]) ? $parts[2]*1000:0); 1388 break; 1389 } 1390 } 1391 } 1392 } 1393 } 1394 1395 return $memInfo; 1396} 1397 1398function utility_php_sort_extensions($a, $b) { 1399 $name_a = isset($a['name']) ? $a['name'] : ''; 1400 $name_b = isset($b['name']) ? $b['name'] : ''; 1401 return strcasecmp($name_a, $name_b); 1402} 1403 1404 1405function utility_php_extensions() { 1406 global $config; 1407 1408 $php = cacti_escapeshellcmd(read_config_option('path_php_binary', true)); 1409 $php_file = cacti_escapeshellarg($config['base_path'] . '/install/cli_check.php') . ' extensions'; 1410 $json = shell_exec($php . ' -q ' . $php_file); 1411 $ext = @json_decode($json, true); 1412 1413 utility_php_verify_extensions($ext, 'web'); 1414 utility_php_set_installed($ext); 1415 1416 return $ext; 1417} 1418 1419function utility_php_verify_extensions(&$extensions, $source) { 1420 global $config; 1421 1422 if (empty($extensions)) { 1423 $extensions = array( 1424 'ctype' => array('cli' => false, 'web' => false), 1425 'date' => array('cli' => false, 'web' => false), 1426 'filter' => array('cli' => false, 'web' => false), 1427 'gd' => array('cli' => false, 'web' => false), 1428 'gmp' => array('cli' => false, 'web' => false), 1429 'hash' => array('cli' => false, 'web' => false), 1430 'json' => array('cli' => false, 'web' => false), 1431 'ldap' => array('cli' => false, 'web' => false), 1432 'mbstring' => array('cli' => false, 'web' => false), 1433 'openssl' => array('cli' => false, 'web' => false), 1434 'pcre' => array('cli' => false, 'web' => false), 1435 'PDO' => array('cli' => false, 'web' => false), 1436 'pdo_mysql' => array('cli' => false, 'web' => false), 1437 'session' => array('cli' => false, 'web' => false), 1438 'simplexml' => array('cli' => false, 'web' => false), 1439 'sockets' => array('cli' => false, 'web' => false), 1440 'spl' => array('cli' => false, 'web' => false), 1441 'standard' => array('cli' => false, 'web' => false), 1442 'xml' => array('cli' => false, 'web' => false), 1443 'zlib' => array('cli' => false, 'web' => false) 1444 ); 1445 1446 if ($config['cacti_server_os'] == 'unix') { 1447 $extensions['posix'] = array('cli' => false, 'web' => false); 1448 } else { 1449 $extensions['com_dotnet'] = array('cli' => false, 'web' => false); 1450 } 1451 } 1452 1453 uksort($extensions, "utility_php_sort_extensions"); 1454 1455 foreach ($extensions as $name=>$extension) { 1456 if (extension_loaded($name)){ 1457 $extensions[$name][$source] = true; 1458 } 1459 } 1460} 1461 1462function utility_php_recommends() { 1463 global $config; 1464 1465 $php = cacti_escapeshellcmd(read_config_option('path_php_binary', true)); 1466 $php_file = cacti_escapeshellarg($config['base_path'] . '/install/cli_check.php') . ' recommends'; 1467 $json = shell_exec($php . ' -q ' . $php_file); 1468 $ext = array('web' => '', 'cli' => ''); 1469 $ext['cli'] = @json_decode($json, true); 1470 1471 utility_php_verify_recommends($ext['web'], 'web'); 1472 utility_php_set_recommends_text($ext); 1473 1474 return $ext; 1475} 1476 1477function utility_get_formatted_bytes($input_value, $wanted_type, &$output_value, $default_type = 'B') { 1478 1479 $default_type = strtoupper($default_type); 1480 $multiplier = array( 1481 'B' => 1, 1482 'K' => 1024, 1483 'M' => 1024*1024, 1484 'G' => 1024*1024*1024, 1485 ); 1486 1487 if ($input_value > 0 && preg_match('/([0-9.]+)([BKMG]){0,1}/i',$input_value,$matches)) { 1488 $input_value = $matches[1]; 1489 if (isset($matches[2])) { 1490 $default_type = $matches[2]; 1491 } 1492 1493 if (isset($multiplier[$default_type])) { 1494 $input_value = $input_value * $multiplier[$default_type]; 1495 } 1496 } 1497 1498 if (intval($input_value) < 0) { 1499 $output_value = $input_value . $wanted_type; 1500 } elseif (isset($multiplier[$wanted_type])) { 1501 $output_value = ($input_value / $multiplier[$wanted_type]) . $wanted_type; 1502 } elseif (isset($multiplier[$default_type])) { 1503 $output_value = ($input_value * $multiplier[$default_type]) . $default_type; 1504 } else { 1505 $output_value = $input_value . 'B'; 1506 } 1507 return $input_value; 1508} 1509 1510function utility_php_verify_recommends(&$recommends, $source) { 1511 global $original_memory_limit; 1512 1513 $rec_version = '5.4.0'; 1514 $rec_memory_mb = (version_compare(PHP_VERSION, '7.0.0', '<') ? 800 : 400); 1515 $rec_execute_m = 1; 1516 $memory_ini = (isset($original_memory_limit) ? $original_memory_limit : ini_get('memory_limit')); 1517 1518 // adjust above appropriately (used in configs) 1519 $rec_execute = $rec_execute_m * 60; 1520 $rec_memory = utility_get_formatted_bytes($rec_memory_mb, 'M', $rec_memory_mb, 'M'); 1521 $memory_limit = utility_get_formatted_bytes($memory_ini, 'M', $memory_ini, 'B'); 1522 1523 $execute_time = ini_get('max_execution_time'); 1524 1525 $timezone = ini_get('date.timezone'); 1526 1527 $recommends = array( 1528 array( 1529 'name' => 'version', 1530 'value' => $rec_version, 1531 'current' => PHP_VERSION, 1532 'status' => version_compare(PHP_VERSION, $rec_version, '>=') ? DB_STATUS_SUCCESS : DB_STATUS_ERROR, 1533 ), 1534 array( 1535 'name' => 'memory_limit', 1536 'value' => $rec_memory_mb, 1537 'current' => $memory_ini, 1538 'status' => ($memory_limit <= 0 || $memory_limit >= $rec_memory) ? DB_STATUS_SUCCESS : DB_STATUS_WARNING, 1539 ), 1540 array( 1541 'name' => 'max_execution_time', 1542 'value' => $rec_execute, 1543 'current' => $execute_time, 1544 'status' => ($execute_time <= 0 || $execute_time >= $rec_execute) ? DB_STATUS_SUCCESS : DB_STATUS_WARNING, 1545 ), 1546 array( 1547 'name' => 'date.timezone', 1548 'value' => '<timezone>', 1549 'current' => $timezone, 1550 'status' => ($timezone ? DB_STATUS_SUCCESS : DB_STATUS_ERROR), 1551 ), 1552 ); 1553} 1554 1555function utility_php_set_recommends_text(&$recs) { 1556 if (is_array($recs) && cacti_sizeof($recs)) { 1557 foreach ($recs as $name => $recommends) { 1558 if (cacti_sizeof($recommends)) { 1559 foreach ($recommends as $index => $recommend) { 1560 if ($recommend['name'] == 'version') { 1561 $recs[$name][$index]['description'] = __('PHP %s is the mimimum version', $recommend['value']); 1562 } elseif ($recommend['name'] == 'memory_limit') { 1563 $recs[$name][$index]['description'] = __('A minimum of %s memory limit', $recommend['value']); 1564 } elseif ($recommend['name'] == 'max_execution_time') { 1565 $recs[$name][$index]['description'] = __('A minimum of %s m execution time', $recommend['value']); 1566 } elseif ($recommend['name'] == 'date.timezone') { 1567 $recs[$name][$index]['description'] = __('A valid timezone that matches MySQL and the system'); 1568 } 1569 } 1570 } 1571 } 1572 } 1573} 1574 1575function utility_php_optionals() { 1576 global $config; 1577 1578 $php = cacti_escapeshellcmd(read_config_option('path_php_binary', true)); 1579 $php_file = cacti_escapeshellarg($config['base_path'] . '/install/cli_check.php') . ' optionals'; 1580 $json = shell_exec($php . ' -q ' . $php_file); 1581 $opt = @json_decode($json, true); 1582 1583 utility_php_verify_optionals($opt, 'web'); 1584 utility_php_set_installed($opt); 1585 1586 return $opt; 1587} 1588 1589function utility_php_verify_optionals(&$optionals, $source) { 1590 if (empty($optionals)) { 1591 $optionals = array( 1592 'snmp' => array('web' => false, 'cli' => false), 1593 'gettext' => array('web' => false, 'cli' => false), 1594 'TrueType Box' => array('web' => false, 'cli' => false), 1595 'TrueType Text' => array('web' => false, 'cli' => false), 1596 ); 1597 } 1598 1599 foreach ($optionals as $name => $optional) { 1600 if (extension_loaded($name)){ 1601 $optionals[$name][$source] = true; 1602 } 1603 } 1604 1605 $optionals['TrueType Box'][$source] = function_exists('imagettfbbox'); 1606 $optionals['TrueType Text'][$source] = function_exists('imagettftext'); 1607} 1608 1609function utility_php_set_installed(&$extensions) { 1610 foreach ($extensions as $name=>$extension) { 1611 $extensions[$name]['installed'] = $extension['web'] && $extension['cli']; 1612 } 1613} 1614