Solved

serious issue with sybase bug? please advise URGENT

Posted on 2004-10-26
283 Views
Last Modified: 2008-03-04
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
Question by:gagaliya
    12 Comments
     
    LVL 6

    Expert Comment

    by:ChrisKing
    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
     
    LVL 1

    Author Comment

    by:gagaliya
    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
     
    LVL 10

    Expert Comment

    by:bret
    What Sybase product, version, and platform are you using?
    0
     
    LVL 6

    Expert Comment

    by:ChrisKing
    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
     
    LVL 1

    Author Comment

    by:gagaliya
    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
     
    LVL 1

    Author Comment

    by:gagaliya
    sorry bret, i mean ase 12.5 on linux/unix (not sure exactly which)
    0
     
    LVL 6

    Expert Comment

    by:ChrisKing
    to get the exact version.       SELECT @@version
    and post the output
    0
     
    LVL 10

    Expert Comment

    by:bret
    The database crashes?  Does anything appear in the errorlog when that happens?
    0
     
    LVL 1

    Author Comment

    by:gagaliya
    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
     
    LVL 10

    Accepted Solution

    by:
    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
     
    LVL 6

    Assisted Solution

    by:ChrisKing
    when you post the DDL for "Tableone", please inclide the DDL for "tableoneLog" too
    0
     
    LVL 1

    Author Comment

    by:gagaliya
    thanks guys sorry i cannot provide the stacktrace, we are going to try resolve this through offical support for now
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Lean Six Sigma Project Manager Certification

    There are many schools of thought around successful project management, but few as highly regarded as the Six Sigma and Lean methods. With 37 hours of learning, this training will explain concrete processes for increasing efficiency and limiting wasted time and effort.

    Suggested Solutions

    Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
    With the shift in today’s hiring climate (http://blog.experts-exchange.com/ee-blog/5-tips-on-succeeding-in-the-new-gig-economy/?cid=Blog_031816), many companies are choosing to hire freelancers to get projects completed efficiently and inexpensively…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now