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

serious issue with sybase bug? please advise URGENT

trigger

create trigger tableoneTrgUpd
on Tableone
for update
as
insert into tableoneLog
select inserted.*, "UPDATE" from inserted
-----------------------------------------------------
sql

update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus'
--------------------------------------------------------------
I ran this sql a couple times, the trigger puts COMPLETELY junk into my log table:
for example:
486555904      -21877      llenged      1852073330      [NULL]      &à@[•Œ1N´$ins&à€~¬´klK­tK­w•Œ1O ÿ>§.TB÷Ü¡$ùLÉF„Ï”é+!quickstats                    &à •Œ1N´&àP\t      T            9/13/0000 4:12:05.333 PM       @      12/16/0000 7:03:07.626 PM      UPDATE
-----------------------------------------------------------------
when i do sql

update tableone set columnname = 'somevalue' where Id in (5432) and Status = 'somestatus'

this works perfectly. Note those two sql does the exact same thing. Why? please help
-----------------------------------------------------------------

to summarize:

update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus'

and

update tableone set columnname = 'somevalue' where Id in (5432) and Status = 'somestatus'

is exactly the same, as in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') returns 5432,

yet if i just use 5432 it works, but if i use an inner select it completely messes up my log table and possibly cause a db crash.

this is very urgent and we are all out of ideas, any expert please help!
0
gagaliya
Asked:
gagaliya
  • 5
  • 4
  • 3
2 Solutions
 
ChrisKingCommented:
OK, not sure how literally to take your example, but ...

select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N'
can only return the id 1234, so I would run the following

select * from tableone where id = 1234
I would be sure that the curruption is in that data and is not being caused by your trigger
0
 
gagaliyaAuthor Commented:
chris can you explain more, i dont understand.

update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus'

this causes the trigger on tableon to corrupt the data in its log table.

update tableone set columnname = 'somevalue' where Id in (5432) and Status = 'somestatus'

this works fine.

5432 is just some id i made up, in reality we have to use update tableone set columnname = 'somevalue' where Id in (select distinct Id from sometable e where e.Id = 1234 and e.Flag = 'N') and Status = 'somestatus' because the id is dynamic and not static 5432
0
 
bretCommented:
What Sybase product, version, and platform are you using?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
ChrisKingCommented:
what I am saying is: if you select distinct id's and your where clause states id = 1234, then the select distinct can only return a value of 1234.

there for the corruption would appear to be in the data row with the id of 1234, and is not being caused by the trigger.

so, run the following:
select * from tableone where id = 1234
and look for the corruption

select * from tableone where id = 5432
will not be corrupted

I know the table names and id's are not real, just substitute those for the real values and have a look
0
 
gagaliyaAuthor Commented:
bret i am using sybase 12.5

chrisking, we did further analysis and determined the issue must be from the trigger inserted temp table.  Our tables are not corrupted, the database crashes only when trigger try to put the value into the logtable from inserted.  Basically if you do printout of select * from inserted within the trigger you will see corrupted data. Then when the trigger attempts to insert those corrupted data into our log table, it crashes the db.
0
 
gagaliyaAuthor Commented:
sorry bret, i mean ase 12.5 on linux/unix (not sure exactly which)
0
 
ChrisKingCommented:
to get the exact version.       SELECT @@version
and post the output
0
 
bretCommented:
The database crashes?  Does anything appear in the errorlog when that happens?
0
 
gagaliyaAuthor Commented:
chris, we tried on a bunch versions. 12.5.0.3, 12.5.2, etc   12.5.2 doesnt crash and just puts the corrupted data into the table. 12.5.03 crashes.

bret, yes the ENTIRE ase server crashed and it was production. Not just a single database. Stacktrace dba provided proves to be no help at all. We pinpointed the problem to be what i stated above. trigger insert into log table, fired from an update containing an inner select from a view(maybe a table too, not sure).

create trigger tableoneTrgUpd
on Tableone
for update
as
insert into tableoneLog
select inserted.*, "UPDATE" from inserted --this produces corrupted data

0
 
bretCommented:
The stacktrace might mean something to me, please feel free to post it.

Can you provide the DDL for Tableone, a row of data, and an update statement that (with the trigger in place) will cause the problem?
0
 
ChrisKingCommented:
when you post the DDL for "Tableone", please inclide the DDL for "tableoneLog" too
0
 
gagaliyaAuthor Commented:
thanks guys sorry i cannot provide the stacktrace, we are going to try resolve this through offical support for now
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now