Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

serious issue with sybase bug? please advise URGENT

Posted on 2004-10-26
12
Medium Priority
?
300 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
Comment
Question by:gagaliya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12421158
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
ID: 12422504
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
ID: 12435659
What Sybase product, version, and platform are you using?
0
Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

 
LVL 6

Expert Comment

by:ChrisKing
ID: 12439211
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
ID: 12440905
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
ID: 12440909
sorry bret, i mean ase 12.5 on linux/unix (not sure exactly which)
0
 
LVL 6

Expert Comment

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

Expert Comment

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

Author Comment

by:gagaliya
ID: 12449511
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:
bret earned 750 total points
ID: 12449772
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
ChrisKing earned 750 total points
ID: 12459265
when you post the DDL for "Tableone", please inclide the DDL for "tableoneLog" too
0
 
LVL 1

Author Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This week I attended a Startup Week Chattanooga talk on Gender Diversity in Technology. Check out what I learned.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

636 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