SPECTRUM Report Manager (SRM) is not collecting data because its start up error due to column Key_read_request out of range value exception.

Document ID : KB000051646
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The cause of the problem is due to out of range value of 'Key_read_requests' performance counter in performance table. Because of this SRM is not collecting data and SPECTRUM Status screen under Admin Tools tab shows SpectroSERVER and Archive Manager statuses are green but no monitoring check box is selected. If you try to check the boxes you receive a message that the updates were successful, but after the update none of the boxes are checked. CA is aware of this issue and problem ticket swbug022603 has been raised to address it.

Solution:

To flush the accumulating performance counters

  1. Stop OneClick server:
    On Windows you can stop SpectrumTomcat service from Windows Services panel.
    On Solaris/Linux you can run $SPECROOT/tomcat/bin/stopTomcat.sh

  2. On a bash shell go to $SPECROOT/mysql/bin and log in to MySQL reporting database on OneClick + SRM server
    cd $SPECROOT/mysql/bin
    mysql -uroot -proot reporting

  3. Run the following command at the mysql prompt:
    flush status;

  4. Restart OneClick server.
    On Windows you can start SpectrumTomcat service from Windows Services panel.
    On Solaris/Linux you can run $SPECROOT/tomcat/bin/startTomcat.sh

    EXCEPTION ERROR: <Date and time> - SRM unable to start up cleanly
    org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT into performance (runcode, runtime, memused, memfree, Aborted_clients, Aborted_connects, Binlog_cache_disk_use, Binlog_cache_use, Bytes_received, Bytes_sent, Com_admin_commands, Com_alter_db, Com_alter_table, Com_analyze, Com_backup_table, Com_begin, Com_call_procedure, Com_change_db, Com_change_master, Com_check, Com_checksum, Com_commit, Com_create_db, Com_create_function, Com_create_index, Com_create_table, Com_create_user, Com_dealloc_sql, Com_delete, Com_delete_multi, Com_do, Com_drop_db, Com_drop_function, Com_drop_index, Com_drop_table, Com_drop_user, Com_execute_sql, Com_flush, Com_grant, Com_ha_close, Com_ha_open, Com_ha_read, Com_help, Com_insert, Com_insert_select, Com_kill, Com_load, Com_load_master_data, Com_load_master_table, Com_lock_tables, Com_optimize, Com_preload_keys, Com_prepare_sql, Com_purge, Com_purge_before_date, Com_rename_table, Com_repair, Com_replace, Com_replace_select, Com_reset, Com_restore_table, Com_revoke, Com_revoke_all, Com_rollback, Com_savepoint, Com_select, Com_set_option, Com_show_binlog_events, Com_show_binlogs, Com_show_charsets, Com_show_collations, Com_show_column_types, Com_show_create_db, Com_show_create_table, Com_show_databases, Com_show_errors, Com_show_fields, Com_show_grants, Com_show_innodb_status, Com_show_keys, Com_show_logs, Com_show_master_status, Com_show_ndb_status, Com_show_new_master, Com_show_open_tables, Com_show_privileges, Com_show_processlist, Com_show_slave_hosts, Com_show_slave_status, Com_show_status, Com_show_storage_engines, Com_show_tables, Com_show_triggers, Com_show_variables, Com_show_warnings, Com_slave_start, Com_slave_stop, Com_stmt_close, Com_stmt_execute, Com_stmt_fetch, Com_stmt_prepare, Com_stmt_reset, Com_stmt_send_long_data, Com_truncate, Com_unlock_tables, Com_update, Com_update_multi, Com_xa_commit, Com_xa_end, Com_xa_prepare, Com_xa_recover, Com_xa_rollback, Com_xa_start, Connections, Created_tmp_disk_tables, Created_tmp_files, Created_tmp_tables, Delayed_errors, Delayed_insert_threads, Delayed_writes, Flush_commands, Handler_commit, Handler_delete, Handler_discover, Handler_prepare, Handler_read_first, Handler_read_key, Handler_read_next, Handler_read_prev, Handler_read_rnd, Handler_read_rnd_next, Handler_rollback, Handler_savepoint, Handler_savepoint_rollback, Handler_update, Handler_write, Innodb_buffer_pool_pages_data, Innodb_buffer_pool_pages_dirty, Innodb_buffer_pool_pages_flushed, Innodb_buffer_pool_pages_free, Innodb_buffer_pool_pages_misc, Innodb_buffer_pool_pages_total, Innodb_buffer_pool_read_ahead_rnd, Innodb_buffer_pool_read_ahead_seq, Innodb_buffer_pool_read_requests, Innodb_buffer_pool_reads, Innodb_buffer_pool_wait_free, Innodb_buffer_pool_write_requests, Innodb_data_fsyncs, Innodb_data_pending_fsyncs, Innodb_data_pending_reads, Innodb_data_pending_writes, Innodb_data_read, Innodb_data_reads, Innodb_data_writes, Innodb_data_written, Innodb_dblwr_pages_written, Innodb_dblwr_writes, Innodb_log_waits, Innodb_log_write_requests, Innodb_log_writes, Innodb_os_log_fsyncs, Innodb_os_log_pending_fsyncs, Innodb_os_log_pending_writes, Innodb_os_log_written, Innodb_page_size, Innodb_pages_created, Innodb_pages_read, Innodb_pages_written, Innodb_row_lock_current_waits, Innodb_row_lock_time, Innodb_row_lock_time_avg, Innodb_row_lock_time_max, Innodb_row_lock_waits, Innodb_rows_deleted, Innodb_rows_inserted, Innodb_rows_read, Innodb_rows_updated, Key_blocks_not_flushed, Key_blocks_unused, Key_blocks_used, Key_read_requests, Key_reads, Key_write_requests, Key_writes, Last_query_cost, Max_used_connections, Not_flushed_delayed_rows, Open_files, Open_streams, Open_tables, Opened_tables, Prepared_stmt_count, Qcache_free_blocks, Qcache_free_memory, Qcache_hits, Qcache_inserts, Qcache_lowmem_prunes, Qcache_not_cached, Qcache_queries_in_cache, Qcache_total_blocks, Questions, Select_full_join, Select_full_range_join, Select_range, Select_range_check, Select_scan, Slave_open_temp_tables, Slave_retried_transactions, Slow_launch_threads, Slow_queries, Sort_merge_passes, Sort_range, Sort_rows, Sort_scan, Ssl_accept_renegotiates, Ssl_accepts, Ssl_callback_cache_hits, Ssl_client_connects, Ssl_connect_renegotiates, Ssl_ctx_verify_depth, Ssl_ctx_verify_mode, Ssl_default_timeout, Ssl_finished_accepts, Ssl_finished_connects, Ssl_session_cache_hits, Ssl_session_cache_misses, Ssl_session_cache_overflows, Ssl_session_cache_size, Ssl_session_cache_timeouts, Ssl_sessions_reused, Ssl_used_session_cache_entries, Ssl_verify_depth, Ssl_verify_mode, Table_locks_immediate, Table_locks_waited, Tc_log_max_pages_used, Tc_log_page_size, Tc_log_page_waits, Threads_cached, Threads_connected, Threads_created, Threads_running, Uptime) VALUES
    ("ReportManagerServlet run()", "2009-10-22 08:10:28", 82313216, 37477840, 169, 9, 0, 0, 8194, 54593, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 94, 121, 0, 0, 0, 1, 0, 0, 0, 0, 0, 22, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 56, 0, 0, 6, 6, 0, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 411, 22, 40, 27, 0, 0, 0, 1, 0, 0, 0, 0, 24, 6, 683, 0, 0, 842, 0, 0, 0, 0, 739, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2087, 224129, 4758314921, 1089606779, 722548151, 686912930, 0.000000, 14, 0, 247, 0, 128, 235, 37, 0, 0, 0, 0, 0, 0, 0, 0, 296, 0, 0, 0, 0, 30, 0, 0, 0, 0, 0, 0, 93, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 156736698, 9431199, 0, 0, 0, 0, 10, 410, 6, 3687841)]; Data truncation: Out of range value adjusted for column 'Key_read_requests' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'Key_read_requests' at row 1
    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'Key_read_requests' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2868)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)
    at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316)
    at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
    at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:403)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:282)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:413)
    at com.aprisma.spectrum.app.repmgr.util.PerformanceMonitor.grabMySQLStatus(PerformanceMonitor.java:211)
    at com.aprisma.spectrum.app.repmgr.web.servlet.ReportManagerServlet.doStartup(ReportManagerServlet.java:330)
    at com.aprisma.spectrum.app.repmgr.web.servlet.ReportManagerServlet.run(ReportManagerServlet.java:230)
    at com.aprisma.spectrum.app.repmgr.web.servlet.ReportManagerServlet.performTask(ReportManagerServlet.java:493)
    at com.aprisma.util.thread.CachedThread.run(CachedThread.java:128)