on 10-28-2014 1:55 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
Please refer to Note 2087751
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
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!
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
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
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
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
Hello p517710 sap basis I'm Facing exactly the same error.
Would you please tell us how did you solve it?
Kind regards, Diego
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;
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!
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
User | Count |
---|---|
95 | |
13 | |
13 | |
11 | |
10 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.