• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1308
  • Last Modified:

Sybase merge_join

At one point a stored procedure was working correctly and then we decided to insert rows into a table to do further testing. Not sure what got messed up after the insertion but after that sybase started giving an error message-

Fatal Error 644 occurred at Apr 25 2011  2:05PM.  Please note the error and time, and contact a user with System Administrator (SA) authorization.

00:00000:00074:2011/04/26 14:05:39.42 server  Index row entry for data row id (23693, 6) is missing from index page 41953 of index id 2 of table 'employee_table' in database 'sybasedb_user'. Xactid is (51051,11). Drop and re-create the index.

We tried dropping the table and recreating all indexes, but the problem remained.

After doing quite some analysis, we found that the query plan being used for one of the sql in the stored procedure was using a merge_join. There is supposedly a bug in sybase ver15.0 that causes the above mentioned error. We had to include a statement "set merge_join off" in the stored procedure to get rid of this error and the stored procedure started working fine again.

My question is, why did the sp start to fail only after the inserts were done to the table and not before - when it was working just fine - and when we did not even need the statement "set merge_join off" . What during the simple insert statement would have caused Sybase to give the fatal errror.

  • 2
  • 2
1 Solution
Acoording to Sybase manuals:

"Error 644


Message text

Index row entry for data row id (%ld, %d) is missing from index page %ld of index id %d of table '%S_OBJID' in database '%S_DBID'. Xactid  is (% ld,%d). Drop and re-create the index.

NoteThis error may be caused by a hardware problem.

This error occurs when the nonclustered index indicated by “index id” is corrupt. This corruption is detected when a process tries to delete a nonexistent row."

I don't think that the actual error has anything to do with merge_join. It might be just a coincidence.

What is exact version of 15.0? Maybe it is a bug in an earlier version and you need to apply a recent maintenance release.

Or it might be a defect on disk. Have you checked operating system logs for hardware problems?
4LearningAuthor Commented:
Regarding nonclustered index, we recreated the table and indexes, but despite that still get the error

Again, just by adding the merge_join statement, the sp works, but without, it keeps giving the fatal error about the same index - even though the table and the index is brand new.

The version is 15.0.3. We asked the DBAs to do a dbcc check on the table, but it seems that if the problem is fixed in whatever way, then further analysis just does not take place :-(

Also this only happened on the table after we did the insert. The exact same table on another server with the same sp  is giving absolutely no problems. Did the insert by any chance mess any indexes up?

And the OS logs are not accessible as only the dbas have access to it.

Any other thoughts/ideas/suggestions/clues?
If there is a defect on your disk (it may be hardware defect as well), you might be hitting that page again when you recreate the index.

By adding the merge_join statement, you avoid using that index. But it doesn't mean that it is not corrupt any more. You can check it by scanning that table through that index (you can force query optimizer to do that).

I think you should convince your DBA to run dbcc, running dbcc for one index doesn't take long.

If you insert more data on the other server, most probably you will not experience any problem. It might be a good way to test whether it is due to a defect on that particular server.
4LearningAuthor Commented:
I tried using another column in the where clause, so that the index would be bypassed and guess what, the query even without the merge_join now works. And no more fatal error!

And I think you are right about the statement you made...
"If there is a defect on your disk (it may be hardware defect as well), you might be hitting that page again when you recreate the index......"

I will investigate it further .

thankyou alpmoon for suggesting an option that even though I did not try it exactly, but something else close to it, it works.

And yes, I will certainly now follow up with the dba who are very certain that there is no hardware problem ;-)


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now