2072211 – Upgrade of the Embedded Statistics Service (ESS) from Revision 74.02 to Revision 81 or 82 fails with "unknown catalog object"

Symptom

The upgrade of the Embedded Statistics Service (ESS) from Revision 74.02 to Revision 81 or 82 fails. Historical data is not collected and alerting no longer takes place.

In the table _SYS_STATISTICS.STATISTICS_PROPERTIES, the key “internal.installation.state” has the value “Done (Error) since <timestamp>”.

In nameserver.ini, section “statisticsserver”, the key “active” has the value “false”.

The following entry appears in the indexserver trace file:

[32222]{-1}[-1/-1] 2014-09-25 16:41:54.872030 i STATS_WORKER ConfigurableInstaller.cpp(00168) : creating procedure for 39: CREATE PROCEDURE _SYS_STATISTICS.Alert_Long_Running_Statements (IN snapshot_id timestamp, IN thresholds _SYS_STATISTICS.tt_statistics_alert_thresholds, IN return_resultset integer, IN caller nvarchar(128), OUT was_cancelled integer, OUT used_values _SYS_STATISTICS.tt_statistics_used_values) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

— snapshot_id [IN]: snapshot id

— thresholds [IN]: current thresholds

— return_resultset [IN]: indicator whether a result set should be returned

— caller [IN]: caller of alert

— was_cancelled [OUT]: indicator whether the alert has been cancelled

— used_values [OUT]: values used in alert

factor int := 1000;

statistics_my_config_id int := 39;

threshold_info int;

threshold_low int;

threshold_medium int;

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION begin if ::SQL_ERROR_CODE=139 then was_cancelled:=1; else CALL _SYS_STATISTICS.shared_write_internal_alert(snapshot_id, ‘[‘ || ’39’ || ‘] ‘ || ::SQL_ERROR_CODE || ‘ ‘ || ::SQL_ERROR_MESSAGE); end if;end;was_cancelled:=0;select threshold into threshold_info from :thresholds where severity = 1;select threshold into threshold_low from :thresholds where severity = 2;select threshold into threshold_medium from :thresholds where severity = 3;tables = select T1.HOST||’.’||T1.PORT||’.’||T1.THREAD_ID ID, T1.DURATION DURATION, map(T2.TRANSACTION_ID, null, -1, T2.TRANSACTION_ID) TRANSACTION_ID, map(T2.CLIENT_PID, null, -1, T2.CLIENT_PID) CLIENT_PID from _SYS_STATISTICS.source_alert_39_service_threads T1 left outer join _SYS_STATISTICS.source_alert_39_connections T2 on T1.CONNECTION_ID = T2.CONNECTION_ID where T1.THREAD_TYPE = ‘SqlExecutor’ and (T1.THREAD_METHOD = ‘ExecuteStatement’ or T1.THREAD_METHOD = ‘ExecutePrepared’) and upper(substring(t1.thread_detail, 1, 6)) != ‘BACKUP’ and T1.DURATION >= :threshold_info * :factor;insert into _SYS_STATISTICS.statistics_alerts_base(snapshot_id, alert_id, alert_timestamp, index, alert_rating, alert_details) select :snapshot_id, 39, current_timestamp, id, map(sign(floor(duration / map(:threshold_info , 0, :factor, (:threshold_info * :factor)) ) ) + sign(floor(duration / map(:threshold_low , 0, :factor, (:threshold_low * :factor)) ) ) + sign(floor(duration / map(:threshold_medium, 0, :factor, (:threshold_medium * :factor)) ) ) , 1, 1, 2, 2, 3, 3, 0), ‘The following statement has been running for ‘ || round(duration / :factor, 2) || ‘ seconds, Transaction ID: ‘ || transaction_id || ‘, client PID: ‘ || client_pid || ‘.’ from :tables where map(sign(floor(duration / map(:threshold_info , 0, :factor, (:threshold_info * :factor)) ) ) + sign(floor(duration / map(:threshold_low , 0, :factor, (:threshold_low * :factor)) ) ) + sign(floor(duration / map(:threshold_medium, 0, :factor, (:threshold_medium * :factor)) ) ) , 1, 1, 2, 2, 3, 3, 0) in (1, 2, 3);if :caller = ‘Timer’ then insert into _SYS_STATISTICS.statistics_email_processing (snapshot_id, alert_id, severity) select :snapshot_id, 39, ifnull(max(map(map(sign(floor(duration / map(:threshold_info , 0, :factor, (:threshold_info * :factor)) ) ) + sign(floor(duration / map(:threshold_low , 0, :factor, (:threshold_low * :factor)) ) ) + sign(floor(duration / map(:threshold_medium, 0, :factor, (:threshold_medium * :factor)) ) ) , 1, 1, 2, 2, 3, 3, 0), 0, 0, 5)), 0) from :tables;end if;if :return_resultset = 1 then used_values = select snapshot_id snapshot_id, ‘THRESHOLD’ type, to_varchar(id) index, ‘THRESHOLD_LONG_RUNNING_STATEMENTS’ name, (select threshold from :thresholds where severity = (map(sign(floor(duration / map(:threshold_info , 0, :factor, (:threshold_info * :factor)) ) ) + sign(floor(duration / map(:threshold_low , 0, :factor, (:threshold_low * :factor)) ) ) + sign(floor(duration / map(:threshold_medium, 0, :factor, (:threshold_medium * :factor)) ) ) , 1, 1, 2, 2, 3, 3, 0))) value, null value_nclob from :tables union all select snapshot_id snapshot_id, ‘VALUE’ type, to_varchar(id) index, ‘HOST_LONG_RUNNING_STATEMENTS_TRANSACTION_ID’ name, to_varchar(transaction_id) value, null value_nclob from :tables union all select snapshot_id snapshot_id, ‘VALUE’ type, to_varchar(id) index, ‘HOST_LONG_RUNNING_STATEMENTS_CLIENT_PID’ name, to_varchar(client_pid) value, null value_nclob from :tables union all select snapshot_id snapshot_id, ‘VALUE’ type, to_varchar(id) index, ‘HOST_LONG_RUNNING_STATEMENTS_DURATION’ name, to_varchar(duration) value, null value_nclob from :tables union all select snapshot_id snapshot_id, ‘SEVERITY’ type, to_varchar(id) index, null name, to_varchar(map(sign(floor(duration / map(:threshold_info , 0, :factor, (:threshold_info * :factor)) ) ) + sign(floor(duration / map(:threshold_low , 0, :factor, (:threshold_low * :factor)) ) ) + sign(floor(duration / map(:threshold_medium, 0, :factor, (:threshold_medium * :factor)) ) ) , 1, 1, 2, 2, 3, 3, 0)) value, null value_nclob from :tables union all select snapshot_id snapshot_id, ‘LABEL’ type, to_varchar(id) index, null name, to_varchar(‘The following statement has been running for ‘ || round(duration / :factor, 2) || ‘ seconds, Transaction ID: ‘ || transaction_id || ‘, client PID: ‘ || client_pid || ‘.’) value, null value_nclob from :tables order by index, name asc; else used_values = select current_utctimestamp snapshot_id, null type, null index, null name, null value, null value_nclob from sys.dummy where 1=0;end if;

END;

[32222]{-1}[-1/-1] 9/25/2014 4:41:54 PM.882179 i PersistenceManag VirtualFileStatsProxy.cpp(00718) : UserName=

[32222]{-1}[-1/-1] 2014-09-25 16:41:54.882111 e STATS_WORKER TrexNetCommandHandler.cpp(00112) : error handling request Install: exception 1: no.9002004 (StatisticsServerAsSQLScript/global/CallInterfaceDirect.cpp:213)

Error during installation: exception 9002003: Unknown catalog object.

.

exception throw location:

1: 0x00007f519e54b0bb in StatisticsService::CallInterfaceDirect::install()+0x267 at CallInterfaceDirect.cpp:213 (libstatisticsserver2.so)

2: 0x00007f519e564825 in StatisticsService::TrexNetCommandHandler::handle(TrexNet::Request&)+0x6a1 at TrexNetCommandHandler.cpp:69 (libstatisticsserver2.so)

3: 0x00000000004654e2 in TRexAPI::TREXIndexServer::handle(TrexNet::Request&, TrexService::HandlerContext&)+0x2130 at TREXIndexServer.cpp:3154 (hdbindexserver)

4: 0x000000000049e7a3 in WorkerThread::run(void*)+0x980 at TrexService.cpp:1449 (hdbindexserver)

5: 0x00007f5195c451f2 in TrexThreads::PoolThread::run()+0x850 at PoolThread.cpp:265 (libhdbbasement.so)

6: 0x00007f5195c46d58 in TrexThreads::PoolThread::run(void*&)+0x14 at PoolThread.cpp:124 (libhdbbasement.so)

7: 0x00007f518b16fccf in Execution::Thread::staticMainImp(void**)+0x98b at Thread.cpp:475 (libhdbbasis.so)

8: 0x00007f518b17021d in Execution::Thread::staticMain(void*)+0x39 at Thread.cpp:545 (libhdbbasis.so)

 

Other Terms

Statistics Server, upgrade

 

Reason and Prerequisites

Synonyms required by ESS to create SQLScript procedures are missing. The ESS must have been activated successfully before the upgrade.

 

Solution

The following two SQL statements must be executed as SYSTEM user:

create synonym _sys_statistics.source_alert_39_service_threads for sys.m_service_threads;
create synonym _sys_statistics.source_alert_39_connections for sys.m_connections;

Then, in nameserver.ini, section “statisticsserver”, the key “active” muss be set to “true” again.

Then the ESS continues the upgrade.

 

Leave a Reply