statistics server migration to embedded

[Former Member]

Hi team,

We have a problem where we get strange alerts related to statistics server migration to embedded.

301 unique constraint violated: TrexUpdate failed on table
'_SYS_STATISTICS:HOST_SERVICE_THREAD_SAMPLES_BASE' with error: unique
constraint violation in self check for table _SYS_STATISTICS:HOST_SERVICE_THREAD_SAMPLES_BASEen

I have checked the video as well for a failed migration there is no option but to restore. But here we have a problem we cannot restore since we would be losing lots of data , just for statistic server sake we cannot restore the entire database.

What are our options ?

[statisticsserver]

active = true

this is the value from nameserver.ini.

Now the problem is that if we set it to false it wont work according to the video.

http://www.saphana.com/docs/DOC-4403

What can we do overcome this problem ?

Message was edited by: Tom Flanagan

[Former Member · Accepted solution]

Yes, that's what I mean. You can't do much damage by trying, so just go ahead. If everything works fine, I would make sure that this information is also updated in the SAP Note.

[nicholas_chang · Accepted solution]

The problem is this error only happens in my Prod. I can't take this risk on production server.
Why don't SAP tested it out and update it? no clue why they informed that fixes only release in SP10.

[Former Member · Accepted solution]

You can't harm the system if you drop the primary keys. And if no history data collection is done for these tables, you also can't have a negative impact on the history data collection.

I now had some discussions and I tested the deletion of the constraints and the subsequent statistics server activity with success.

Please see SAP Note 2143679 and check if it works for you. You may only drop the three critical primary keys for the moment, just to keep the amount of changes at a minimum level. If there is any issue, please let me know.

[nicholas_chang · Accepted solution]

Hi Martin,

Thanks again for your help. I've went thru note 2143679 and executed "HANA_StatisticsServer_PrimaryKeysOnHistoryTables" and encounter error below:

Could not execute 'C.TABLE_NAME, C.CONSTRAINT_NAME PK_CONSTRAINT, 'ALTER TABLE "_SYS_STATISTICS"."' || C.TABLE_NAME || ...'

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "C": line 1 col 1 (at pos 1)

And I believe SAP overlook the "Select" for the SQL statement below:

*/

Select <------ missing out in the SQL statement in note 1969700 - SQL statement collection for SAP HANA

  C.TABLE_NAME,

  C.CONSTRAINT_NAME PK_CONSTRAINT,

  'ALTER TABLE "_SYS_STATISTICS"."' || C.TABLE_NAME || '" DROP CONSTRAINT "' || C.CONSTRAINT_NAME || '";' DROP_COMMAND

FROM

  TABLES T,

Thanks,

Nicholas Chang

[Former Member · Accepted solution]

Sorry for the problems. Executing this SQL statement outside of SAP HANA Studio (e.g. in DBACOCKPIT) works fine, but SAP HANA Studio has a problem: It interprets the ";" characters in the "EXAMPLE OUTPUT" in the comment as statement separators and so it forgets about the "SELECT" at the very beginning of the command. I have now uploaded a new version with a "corrected" version. You can also just remove the comments after the initial SELECT in order to come around the issue.

[Former Member · Accepted solution]

Thanks a ton martin for all the help. One small question can you please check my other thread regarding rollback, could you please help me with that.

[Former Member · Accepted solution]

HI Martin,

I've the similiar Issue, I use HANA DB rev 85.3 for SAP Business One

installing SAP B1 9.1 PL 8

how to solve this issue ??

I

[Former Member · Accepted solution]

I've restarted XSengine, but I think still error

[Former Member · Accepted solution]

As a workaround you can drop the primary index on HOST_SERVICE_THREAD_SAMPLES_BASE as described in SAP Note 2143679. With current Revisions hopefully no duplicates will be inserted, so in the future it should be possible to recreate the primary key again.

[former_member183326]

Please refer to Note 2087751

[nicholas_chang]

note 2087751 doesnt solve the problem!

[former_member183326]

Hello, please refer to the following notes:

2127297

2137142

2127852

[Former Member]

Hi Nicholas,

Does this not work?

Nothing else was really mentioned in the update from the developer. There is a workaround which is to disable the collector if you cannot upgrade to SP9 at the time. Please keep in mind that when you do upgrade to SP9, you must re-enable the collector.

Disable:
update _sys_statistics.statistics_schedule set status = 'Inactive' where id = 5034;

Re-Enable:
update _sys_statistics.statistics_schedule set status = 'Idle' where id = 5034;

[nicholas_chang]

Nope, there's no resolution yet despite disable the collector ID 5015, 5016 and 5017. SAP replied that this only will solved in SP10, which is kinda ridiculous.

2127852 - HANA Error: 301 unique constraint violated: TrexUpdate failed on table

By stopping the particular collect, alert "301 Unique constraint violated" stop apperating, however, we are losing these thread where you can't view the history resources usage!

[Former Member]

Why don't you just disable the primary constraints for the tables related to collector IDs 5015, 5016 and 5017?

_SYS_STATISTICS.HOST_VOLUME_IO_DETAILED_STATISTICS_BASE

_SYS_STATISTICS.HOST_VOLUME_IO_RETRY_STATISTICS_BASE

_SYS_STATISTICS.HOST_VOLUME_IO_TOTAL_STATISTICS_BASE

I see no urgent need to have a primary key on these columns so dropping it should be a valid workaround.

[nicholas_chang]

Hi Martin,

Do you mean by

ALTER TABLE "__SYS_STATISTICS.HOST_VOLUME_IO_DETAILED_STATISTICS_BASE" DROP CONSTRAINT '$trexexternalkey$'

ALTER TABLE "_SYS_STATISTICS.HOST_VOLUME_IO_RETRY_STATISTICS_BASE" DROP CONSTRAINT '$trexexternalkey$'

ALTER TABLE "__SYS_STATISTICS.HOST_VOLUME_IO_RETRY_STATISTICS_BASE" DROP CONSTRAINT '$trexexternalkey$'

like those mention in note 2127297 - HANA error 301 unique constraint violation in self check for table _SYS_STATISTICS:HOST_SE...

From above note, it mention only dropping constraint key for HOST_SERVICE_THREAD_SAMPLES_BASE AND DISABLE collector for ID 5015, 5016 and 5017 which are the 3 threads above.

Earlier, i've created a ticket to SAP and asked can we manually delete the contrainst key for above 3 thread, but they said no and wait for the fixes in SP10 which is quite unacceptable for me.

Hope to hear from you.

Thanks,

Nicholas Chang

[HayBouten]

Hi,

SAP HANA can do a restore and roll forward.

For a successful recovery you need the backup that you made before you started the statistics server migration and all the backed up log segments in the logbackup location and you can do a full restore.

- Hay

[Former Member]

Hay,

if you did get my question, i cannot go back to the point before the statistics server migration.

Since there have lots of data changes.

Suppose i have done the statistics server migration on 20th october , i have the backup from 19th and logs till the migration has started.

Now if i go back then i would be losing data from 20th to 28th of october right since it would contain the statistics server migrated tables right.

[Former Member]

Also there is no monitoring mechanism for the migration of the stats server into the index server.

Below is the note which we have followed, it doesnt talk about to as to view the progress of the migration whether its successfull or not.

1917938 - Migration of the statistics server for Revision 74 or
higher

[Former Member]

Its been resolved, so closing the thread.

[Former Member]

Hello,

Just wondering how you solved this issue? I'm getting the same error.

Thanks!

[JimmyYang]

Hi Patrick,

You must first validate that the migration to the embedded statistics server was fine.

Basically you look into the nameserver trace files for "STATS_CTRL" and see if there is an entry like:

STATS_CTRL       NameServerControllerThread.cpp(00251) : error installing:

The complete resolution is documented in this note:

2006652 - SAP HANA Statistics Server - Switch to Embedded Statistics
Service fails

Best Regards,
Jimmy

[Former Member]

Jimmy have gone through the note.

select TOP 1000 * FROM "_SYS_STATISTICS"."STATISTICS_OBJECTS"

This table is not empty.

I did check for the STATS_CTRL in the trace directory

# ResultSet.row[]  = [u'''indexserver''', u'''stats_ctrl'''] this is what i see its not the same as in the note.

searched for the keyword as mentioned in the note :

didnt find anything , STATS_WORKER in the index server trace which corresponds to migration to the embedded statistics server.

Result of the query

SELECT * FROM _SYS_STATISTICS.STATISTICS_PROPERTIES where key =

'internal.installation.state'

key :      internal.installation.state

value : Done (okay) since

[JimmyYang]

Hi,

There is a known issue regarding this issue which is resolved in SP9. The problem is in the monitoring view M_SERVICE_THREAD_SAMPLES used by Collector_Host_Service_Thread_Samples(5034) collector.

Occasionally during thread context initialization the thread ID is assigned as -1 which is not guaranteed to be unique when there are multiple threads in this state and therefore causing unique constraint violation error when copying data back to HOST_SERVICE_THREAD_SAMPLES_BASE table.

We have fixed this issue internally by changing the behavior of M_SERVICE_THREAD_SAMPLES to filter out all thread with thread_id=-1.

The patch is scheduled in first version of SP9.

Best Regards,
Jimmy

[Former Member]

Hi Jimmy,

Thank you for your information! Just wondering how you "fixed this issue internally by changing the behavior of M_SERVICE_THREAD_SAMPLES to filter out all thread with thread_id=-1"? Do you have any example?

At this time we cannot upgrade to SPS09.

Regards,

Patrick

[JimmyYang]

Hi Patrick,

Nothing else was really mentioned in the update from the developer. There is a workaround which is to disable the collector if you cannot upgrade to SP9 at the time. Please keep in mind that when you do upgrade to SP9, you must re-enable the collector.

Disable:
update _sys_statistics.statistics_schedule set status = 'Inactive' where id = 5034;

Re-Enable:
update _sys_statistics.statistics_schedule set status = 'Idle' where id = 5034;

Best Regards,
Jimmy

[former_member208312]

Hello p517710 sap basis     I'm Facing exactly the same error.

Would you please tell us how did you solve it?

Kind regards, Diego

[Former Member]

Hi Diego,

Have you checked the previous updates.

Nothing else was really mentioned in the update from the developer. There is a workaround which is to disable the collector if you cannot upgrade to SP9 at the time. Please keep in mind that when you do upgrade to SP9, you must re-enable the collector.

Disable:
update _sys_statistics.statistics_schedule set status = 'Inactive' where id = 5034;

Re-Enable:
update _sys_statistics.statistics_schedule set status = 'Idle' where id = 5034;

[former_member208312]

Thanks a lot p517710 sap basis, in my case I could finnaly solver the problem by implementing the note 2087751 which says this:

"To solve this issue go to the Hana Studio.

Select Administration >> Landscape >> right click Xsengine.
From here stop the Xsengine. Normally the Xsengine will restart automatically.
The Xsengine restart should solve the error.
"

After a few hours the error was gone!

[Former Member]

To be honest we restarted the whole system. For me when i try to open the note it says document not released.

[former_member208312]

Yes, happened the same to me "the document is not released" But I asked it to the SAP support team to give the note to me.

This is what the note says "

Select Administration >> Landscape >> right click Xsengine.

From here stop the Xsengine. Normally the Xsengine will restart automatically.

The Xsengine restart should solve the error."

Kind regards

Diego