Duplicate rows in sysusers??

If you’re like me, you’ve run into lots of different issues with the Sybase ASE DBMS over the years. Today, Drew Montgomery looked at what happens when the 12.5.x -> 12.5.4 upgrade goes bad:

If you receive the 8419 error on sysusers after applying ASE 12.5.4 (or any other upgrade for that matter) such as the following:

01:00000:00472:2007/12/10 14:46:03.86 server Error: 8419, Severity: 20, State: 3
01:00000:00472:2007/12/10 14:46:03.86 server Could not find index descriptor for objid 10, indid 3 in dbid 13.
01:00000:00472:2007/12/10 14:46:03.88 kernel ************************************
01:00000:00472:2007/12/10 14:46:03.88 kernel SQL causing error : select * from sysusers
01:00000:00472:2007/12/10 14:46:03.88 kernel ************************************
01:00000:00472:2007/12/10 14:46:03.88 server SQL Text: select * from sysusers
01:00000:00472:2007/12/10 14:46:03.89 kernel curdb = 13 tempdb = 9 pstat = 0x10000
01:00000:00472:2007/12/10 14:46:03.89 kernel lasterror = 8419 preverror = 0 transtate = 1

Fear not because Drew has you covered with a simple script that will fix the problem. Note that it involves a dump and load of the affected database but you could just restart the ASE server if you don’t mind the down time:

sp_configure "allow updates", 1
go
use db_with_8419
go

select * into tempdb..temp_sysusers from sysusers
go

select uid, cnt= count(*) 
  into tempdb..temp_uid 
  from tempdb..temp_sysusers 
 group by uid 
 having count(*) > 1
go

select s.uid, s.suid
  into tempdb..temp_fixuid 
  from tempdb..temp_sysusers s, tempdb..temp_uid t
 where s.uid = t.uid
go

select uid, min(suid) suid
  into tempdb..temp_rmuid
  from tempdb..temp_fixuid
 group by uid
go

delete tempdb..temp_fixuid
  from tempdb..temp_fixuid f, tempdb..temp_rmuid r
 where f.uid = r.uid
   and f.suid = r.suid
go

alter table tempdb..temp_fixuid add cntr int identity
go

declare @max_uid int
select @max_uid = max(uid) 
  from tempdb..temp_sysusers
 where suid > 16300

update sysusers
   set uid = @max_uid + cntr
  from sysusers s, tempdb..temp_fixuid f
 where s.uid = f.uid
   and s.suid = f.suid
go

use master
go

dump database db_with_8419 to "compress::N::/DumpLocation/dumpfilename.dmp"
go
load database db_with_8419 from "compress::/DumpLocation/dumpfilename.dmp"
go
online database db_with_8419
go

sp_configure "allow updates", 0
go

Bau-jen Liu over at Sybase came up with the following method but it does require rebooting ASE twice:

sp_configure "allow updates", 1 
go 
update ..sysobjects 
set sysstat = 115, type = "U" 
where name = "sysusers" 
go 

Shutdown ASE, reboot, and log back in as a user with sa_role.

use dbname
go 

create unique index ncsysusers2 on sysusers(uid) 
go 

The above CREATE INDEX command may fail due to the presence of duplicate key rows in sysusers. If this occurs, recreate the index as non-unique first:

create index ncsysusers2 on sysusers(uid) 
go 

Now identify any duplicates that may exist in sysusers:

select uid, name, count(*) 
from sysusers 
group by uid 
having count(*) > 1 
go 

Remove any duplicates found using the DELETE command and try the CREATE INDEX again:

create unique index ncsysusers2 on sysusers(uid) 
go 

Now, clean up after yourself:

update ..sysobjects 
set sysstat = 113, type = "S" 
where name = "sysusers" 
go 

sp_configure "allow updates", 0 
go

Sybase has created bug # 298620 for this issue.

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>