Alerts cannot be displayed

[Former Member]

Hi all,

we've just made a fresh new Hana instance on CAL, Update:68 (Apr 3, 2015, 1:40:26 PM), and when I open the Studio, it says

SQL error while updating the administration overview

Alerts cannot be displayed

and the error log has several entries

SAP DBTech JDBC: [2048]: column store error: search table error:  [29] attribute not defined for physical index;unknown sort attribute _SYS_STATISTICS:STATISTICS_ALERTSen/ALERT_TIMESTAMP

com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [2048]: column store error: search table error:  [29] attribute not defined for physical index;unknown sort attribute _SYS_STATISTICS:STATISTICS_ALERTSen/ALERT_TIMESTAMP

    at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)

    at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)

    at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:102)

    at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1033)

    at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:823)

    at com.sap.db.jdbc.StatementSapDB.sendCommand(StatementSapDB.java:896)

    at com.sap.db.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:945)

    at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:256)

    at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:228)

    at com.sap.db.jdbc.trace.Statement.execute(Statement.java:132)

    at com.sap.ndb.studio.jdbc.util.SQLExecutor$1.run(SQLExecutor.java:118)

Is this a problem that can be fixed or is there some inherent problem in this instance?

Cheers,

-- Micha

[ventsislav_ivanov2]

Hi Micha,

You probably used older version of HANA Developer edition.

Update 68 is based on SP 9 revision 91.

Which version of the Studio are you using?

I guess you'll need to update it to the most recent one.

Best Regards,

Ventsislav

[Former Member]

Hi Ventislav,

the Hana AWS instance is the newest one available, so it cannot be an older one. As to studio, I don't see how this should matter, the error are also written in Hana trace files and my studio works with older Hana instances. My studio is 2.0.12.

Cheers,

-- Micha

[craigcmehil]

You have the same error if you view the trace files from the Web Based Development Workbench? Have you suspended and then restarted the instance?

It's an odd error and I've not been able to reproduce it which makes me think that perhaps something went wrong when you created the instance and you may need to terminate it and launch a new one.

[Former Member]

Well, as a matter of fact, this is exactly what I have done - I've made an AWS Hana instance and it had this error. I've terminated it, created a new one and exactly the same error is being shown. So I think this is an error in the AWS instance image. You mean you have tried to make a new AWS development Hana instance SPS9 and you haven't had this error?

Cheers,

-- Micha

[craigcmehil]

Constantly, as the one who creates the Dev Edition I'm constantly starting instances and I've not seen the error. If you were only seeing the error through the Studio I would think it might be your firewall blocking one of the several ports we use for the instance.

Since you see it via the web based tools as well I'm not sure. I just started an instance without problem a few minutes ago.

Let me see if I can get some others to look at this thread, maybe someone else will see something I might be missing.

[craigcmehil]

In CAL when you were creating the instance which size of instance did you take? Did you change any of the ports?

[Former Member]

I've taken the r3.2xlarge, no ports changed, manual starting and stopping.

Why are you mentioning web based tools? I mean when I log in to the machine using ssh and look at the trace files, I see the errors which I've described in my first post. This has no relation to firewalls (I don't have any) or anything concerning my access to the machine, the trace files are written by Hana itself before I had my first access to it. I've tried to reboot and stop/start the instance and to restart Hana, nothing has changed,

Cheers,

-- Micha

[craigcmehil]

Most people use the web based tools or Studio with the HANA Dev Edition. Habit on my side to ask. (e.g. /sap/hana/ide/trace/ )

I've asked some of development team to look at this thread.

[Former Member]

When I look for instance at the indexserver_alert_vhcalhdbdb.trc file, I see some errors from January and February and then starting with the day I've created the instance there is for example:

[24841]{-1}[-1/-1] 2015-04-17 10:29:36.539937 e tz

DateParser.cpp(04115) : Time zone validity checker could not find database tables with timezone data. Abort

ing validity check. Please make sure that you have up-to-date timezone data tables. (see SAP Note 1932132)

[24837]{300589}[14/-1] 2015-04-17 11:34:59.836133 e TrexNet

EndPoint.cpp(00260) : ERROR: failed to open channel 127.0.0.1:30005! reason: (connection refused)

[24933]{300589}[14/-1] 2015-04-17 11:34:59.836509 e cs_statistics

CS_Statistics.cpp(01237) : getColumnsStats: collect statistics failed for at least one partition of tab

le _SYS_STATISTICS:STATISTICS_ALERTS (133)

[24933]{300589}[14/-1] 2015-04-17 11:34:59.836966 e cs_statistics

CS_Statistics.cpp(01378) : sendRemoteRequest failed: Exception in communication code. Action: Creating

request, remote server: vhcalhdbdb:30005

[24933]{300589}[14/-1] 2015-04-17 11:34:59.845746 e SessionRemoteReq RemoteRequest.cc(00063) : failed to create a request: dest=vhcalhdbdb:30005: OpenChannelException at ne

twork layer:

  message: an error occured while opening the channel

  info:    connection refused

  param:   127.0.0.1:30005

[24841]{300589}[14/-1] 2015-04-17 11:34:59.846309 e RuntimeData

RuntimeData.cpp(00881) : Exception in communication code. Action: Creating request, remote server: vhca

lhdbdb:30005

[24933]{300589}[14/-1] 2015-04-17 11:34:59.848625 e Row_Engine

msglog.cc(00082) : query: failed to get volume id for _SYS_STATISTICS.STATISTICS_ALERT_LAST_CHECK_INFOR

MATION, part 0, therefore current plan is forced to be global (at ptime/query/query.cc:4127 )

[24933]{300589}[14/-1] 2015-04-17 11:34:59.848687 e Row_Engine

msglog.cc(00082) : query: failed to get volume id for _SYS_STATISTICS.STATISTICS_ALERT_INFORMATION, par

t 0, therefore current plan is forced to be global (at ptime/query/query.cc:4127 )

[24935]{300593}[26/-1] 2015-04-17 11:39:12.285577 e join_eval

JERequestHandler.cpp(01925) : do_getAttributes failed for logical index part _SYS_STATISTICS:_SYS_SPLIT

_STATISTICS_ALERTS~1en rc=1

[24935]{300593}[26/-1] 2015-04-17 11:39:12.285607 e join_eval

IndexInfo.cpp(00153) : getAttributes failed for _SYS_STATISTICS:STATISTICS_ALERTSen/ALERT_TIMESTAMP rc=

1

[24935]{300593}[26/-1] 2015-04-17 11:39:12.285616 e join_eval	CheckAttributes.cpp(00406) : unknown sort attribute _SYS_STATISTICS:STATISTICS_ALERTSen/ALERT_TIMESTAMP
[24935]{300593}[26/-1] 2015-04-17 11:39:12.285631 e SERVER_TRACE	TRexApiSearch.cpp(10146) : IndexID: SYSTEM:0x00007f063ffe9700:3:0:#600000000000113 (432345564227567892)

: unknown sort attribute _SYS_STATISTICS:STATISTICS_ALERTSen/ALERT_TIMESTAMP

IMHO this does not look like a healthy instance...?

Cheers,

-- Micha

[craigcmehil]

With the stand alone statistic server being turned off and the XS engine in embed mode there should be no problems (Jan Feb errors are from when we first set things up)

Thanks for the logs, we'll look into it!

[Former Member]

Thanks a lot. If you or your colleagues need to access the machine directly, just let me know.

-- Micha

[Former Member]

Hi Craig,

any news about this problem?

-- Micha

[craigcmehil]

SAPPHIRE got in the way but we are redoing a server now to see if we can reproduce.

[Former Member]

Hi all SAP colleagues,

this is a monthly reminder that SAP HANA, developer edition 1.0 SPS 09,

Update:

69 (May 15, 2015, 8:20:55 AM)

does not work properly with the r3.2xlarge AWS instance, it still has no alerts, problem description see above.

Cheers,

-- Micha

[former_member220221]

Hello,

Could you fix the Statisticserver issue ?

I have the same error. Today I updated the full Amazon HANA DB instance to newest one. (97), but the embedded statisticserver not start. 

[Former Member]

I have the same error as well , when will this be fixed

[craigcmehil]

This was completely fixed in the new SPS10 edition now available.

[former_member220221]

Great! But we deleted the full Amazon account and installed an on-premise HANA DB system. 

[craigcmehil]

That's great too 🙂

[Former Member]

Hi Craig, I've got this too.

Just upgraded to SPS11 and I still don't get any current alerts and messages.

I found this thread whilst trying to move to embedded stats server. I've not been able to successfully perform the migration, and the error I see (as the OP listed) is an error when communicating to Stats server on port 30005. Additionally, the same communication error is encountered whenever I try to query any tables in schema _SYS_STATISTICS, which is presumably why I cannot perform the migration.

My stats server is constantly in standby...

A bit more detail, for clarity. When migrating from old to new statservers, I see this in the nameserver trace...

[24071]{-1}[-1/-1] 2016-01-12 02:01:36.608133 i STATS_CTRL       NameServerControllerThread.cpp(00495) : installing...

[24071]{-1}[-1/-1] 2016-01-12 02:01:36.608150 i STATS_CTRL       NameServerControllerThread.cpp(00496) : delay time: 900

[24071]{-1}[-1/-1] 2016-01-12 02:01:36.608220 i STATS_CTRL       NameServerControllerThread.cpp(00644) : old StatisticsServer: vhcalhdbdb:30005, volume: -

[24071]{-1}[-1/-1] 2016-01-12 02:01:36.608226 i STATS_CTRL       NameServerControllerThread.cpp(00713) : waiting for start of old StatisticsServer vhcalhdbdb:30005, volume: -...

[24071]{-1}[-1/-1] 2016-01-12 02:01:36.628430 i STATS_CTRL       NameServerControllerThread.cpp(00737) : waiting for old StatisticsServer vhcalhdbdb:30005, volume: - to stop all operations...

[24071]{-1}[-1/-1] 2016-01-12 02:01:56.628555 i STATS_CTRL       NameServerControllerThread.cpp(00739) : old StatisticsServer vhcalhdbdb:30005, volume: - has stopped all operations

[24071]{-1}[-1/-1] 2016-01-12 02:01:56.628569 i STATS_CTRL       NameServerControllerThread.cpp(00742) : old StatisticsServer is ready. starting...

[24071]{-1}[-1/-1] 2016-01-12 02:01:56.628692 i STATS_CTRL       CallInterfaceProxy.cpp(00044) : sending install request

[24071]{-1}[-1/-1] 2016-01-12 02:01:59.388874 i STATS_CTRL       CallInterfaceProxy.cpp(00048) : response to install request: ERR:

[24071]{-1}[-1/-1] 2016-01-12 02:01:59.388958 e STATS_CTRL       CallInterfaceProxy.cpp(00052) : error during install: ERR:

[24071]{-1}[-1/-1] 2016-01-12 02:01:59.389592 e STATS_CTRL       NameServerControllerThread.cpp(00531) : error installing: exception  1: no.9002004  (StatisticsServerAsSQLScript/global/CallInterfaceProxy.cpp:53)

    Error during installation: ERR: .

exception throw location:

Noting the last few lines. Similarly, over in the statisticsserver trace, I see this for the same time period...

[24325]{-1}[-1/-1] 2016-01-12 02:00:49.150328 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:00:49.151057 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:00:49.151903 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:00:49.153028 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:00:50.083121 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:00:50.084038 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:00:50.089115 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:01:56.637321 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

[24325]{-1}[-1/-1] 2016-01-12 02:01:56.637436 e TrexNet          Request.cpp(01506) : deserialization of InheritableLocalStorage from active channel (since 252mues) 63 from 127.0.0.1:52172 to 30005 with method DistTableStatistics failed with an exception: exception  1: no.70000025  (TRexUtils/TransToken.cpp:1970)

    TransToken is invalid

exception throw location:

1: 0x00007f401d4b09df in TrexNet::NetException::NetException(TrexNet::NetException::_ExceptionType, char const*, unsigned int, char const*, char const*)+0x3b at Exception.cpp:81 (libhdbbasement.so)

2: 0x00007f401d64409b in Newdb::TransTokenThreadContainer::deserialize(TRexUtils::Deserializer&)+0x347 at TransToken.cpp:1970 (libhdbbasement.so)

3: 0x00007f401d6401ff in Newdb::TransTokenThreadContainer::deserializeFromClient(TRexUtils::Deserializer&, Execution::Context&)+0x2b at TransToken.cpp:2428 (libhdbbasement.so)

4: 0x00007f401d4b6a71 in TrexNet::handleInheritableLocalStorage(TRexUtils::BasicByteBuffer<unsigned char>*, TrexTrace::PerformanceTraceId*, TrexNet::RequestOrigin*)+0x520 at Request.cpp:1293 (libhdbbasement.so)

5: 0x00007f401d4b7e99 in TrexNet::Request::readHeader(TrexNet::Channel*, bool)+0x9a5 at Request.cpp:1502 (libhdbbasement.so)

6: 0x00007f401d4f5b0c in TrexNet::Request::readHeaderComplete()+0x18 at Request.h:450 (libhdbbasement.so)

7: 0x00007f401d4f1492 in TrexService::WorkerThread::run(void*)+0x330 at TrexServiceThreads.cpp:440 (libhdbbasement.so)

8: 0x00007f401d50e776 in TrexThreads::PoolThread::run()+0x842 at PoolThread.cpp:392 (libhdbbasement.so)

9: 0x00007f401d5100a0 in TrexThreads::PoolThread::run(void*&)+0x10 at PoolThread.cpp:165 (libhdbbasement.so)

10: 0x00007f40050e3271 in Execution::Thread::staticMainImp(void**)+0x6f0 at Thread.cpp:461 (libhdbbasis.so)

11: 0x00007f40050e4848 in Execution::Thread::staticMain(void*)+0x34 at ThreadMain.cpp:26 (libhdbbasis.so)

[24325]{-1}[-1/-1] 2016-01-12 02:01:59.379467 e trans_token      TransToken.cpp(01966) : Watermark mismatch: system_wm:-1, token_wm:1882093, one of nodes may have restarted

Lots of trans token stuff. Beats me. And having followed about 10 different notes (from here: SAP HANA Statistics Service Troubleshooting - Technology Troubleshooting Guide - SCN Wiki) to get this working, when I try to query the _SYS_STATISTICS schema, like so...

call CHECK_TABLE_CONSISTENCY ('CHECK','_SYS_STATISTICS',null);

I get this:

Could not execute 'call CHECK_TABLE_CONSISTENCY ('CHECK','_SYS_STATISTICS',null)' in 248 ms 272 µs .

[129]: transaction rolled back by an internal error: exception 71002567: the application threw an exception

I actually can't query any of the tables/views in this schema. From the index server alert trace:

[24271]{300322}[146/-1] 2016-01-12 02:25:51.756688 e CLIENT_TRACE     TRexApiSearch.cpp(11980) : TrexNet Exception caught

[24503]{300322}[145/-1] 2016-01-12 02:25:51.757042 e CLIENT_TRACE     TRexApiSearch.cpp(11980) : TrexNet Exception caught

[25364]{300322}[144/-1] 2016-01-12 02:25:51.757146 e CLIENT_TRACE     TRexApiSearch.cpp(11980) : TrexNet Exception caught

[24341]{300322}[143/-1] 2016-01-12 02:25:51.757525 e Row_Engine       msglog.cc(00082) : the application threw an exception (at ptime/session/eapi/jdbc/Statement.cc:2033 )

[24341]{300322}[143/-1] 2016-01-12 02:25:51.757543 e EmbeddedAPI      Statement.cc(04397) : unhandled ltt exception was caught: conn_id=300322, stmt_id=1289876605148186, stmt_hash=3e4f6a677addf22e861be4ae10b157b3, error=the application threw an exception, sql=call CHECK_TABLE_CONSISTENCY ('CHECK','_SYS_STATISTICS',null)

[24341]{300322}[-1/-1] 2016-01-12 02:25:51.757576 e EmbeddedAPI      Connection.cc(02012) : internal error: message=the application threw an exception

[24341]{300322}[-1/-1] 2016-01-12 02:25:51.757596 e EmbeddedAPI      Connection.cc(00336) : exception  1: no.71002567  (TableConsistencyCheck/Request.cc:727)

    the application threw an exception

NO exception throw location recorded. Stack generation suppressed.

From the index server trace:

[25364]{300322}[144/-1] 2016-01-12 02:25:51.757146 e CLIENT_TRACE     TRexApiSearch.cpp(11980) : TrexNet Exception caught

[24341]{300322}[143/-1] 2016-01-12 02:25:51.757531 i TraceContext     TraceContext.cpp(00855) : UserName=SYSTEM, ApplicationUserName=hagen, ApplicationName=HDBStudio, ApplicationSource=csns.sql.editor.SQLExecuteFormEditor$2$1.run(SQLExecuteFormEditor.java:856);, Client=001, StatementHash=3e4f6a677addf22e861be4ae10b157b3

[24341]{300322}[143/-1] 2016-01-12 02:25:51.757525 e Row_Engine       msglog.cc(00082) : the application threw an exception (at ptime/session/eapi/jdbc/Statement.cc:2033 )

[24341]{300322}[143/-1] 2016-01-12 02:25:51.757543 e EmbeddedAPI      Statement.cc(04397) : unhandled ltt exception was caught: conn_id=300322, stmt_id=1289876605148186, stmt_hash=3e4f6a677addf22e861be4ae10b157b3, error=the application threw an exception, sql=call CHECK_TABLE_CONSISTENCY ('CHECK','_SYS_STATISTICS',null)

[24341]{300322}[-1/-1] 2016-01-12 02:25:51.757576 e EmbeddedAPI      Connection.cc(02012) : internal error: message=the application threw an exception

[24341]{300322}[-1/-1] 2016-01-12 02:25:51.757596 e EmbeddedAPI      Connection.cc(00336) : exception  1: no.71002567  (TableConsistencyCheck/Request.cc:727)

    the application threw an exception

NO exception throw location recorded. Stack generation suppressed.

So it seems to me, the root lies in communicating to the old stats server. It's just not accepting requests. The whole reason I've headed down this path is to resolve an rapidly growing SYS.M_DEV_ROLLBACK_TRANSACTION_TABLE () - each time we stop/start HANA this table grows and eventually cripples the system until the thread worker heap is increased. This can only be done for so long however. The associated notes do not resolve this problem.

Lastly, not attempting to hijack the thread, but I have a tonne of information about this, which may be of use.

Cheers,

Hagen

[craigcmehil]

You upgraded the trial instance? These are not designed for upgrades.

Also the above issue above we have isolated and taken care of as of the SPS10 release of the Developer Edition.

[Former Member]

Thanks for the comment - I've done away with my old instance and spun up a new SPS11 dev edition. In fact the SPS11 dev edition comes bundled with heaps of goodies. I'm really enjoying playing around with it.

[craigcmehil]

I've not released the SPS11 Dev Edition yet? Are you sure you are not on the SPS10 one?

[Former Member]

SPS10. My mistake.