Strange behavior after dropping database on ASE 15.7 SP132

[neal_stack2]

Hello,

I might be losing my mind here. We appeared to successfully drop a database but it still showed up in sp_helpdb and in sysdatabases:

1> select dbid, name from sysdatabases
2> go | egrep "master|xxx_trading"
      1 master
     15 xxx_trading

When I attempted to use the database I thought was dropped, I was actually put in master:

1> use xxx_trading
2> go
1> select db_name()
2> go
 ------------------------------
 master

(1 row affected)

What really concerned me here was that I was able to "use xxx_trading" and issue a create table command without error. The table showed up in master. I expected an error like 823 when I created the table.

We then shutdown ASE and restarted it, the database was marked appropriately offline (because we had removed its underlying devices during the original drop database process). We were then able to drop the database and its devices without error.

Has anyone seen where you "use database_xxx" that you know should at least be offline and get put into master?

[neal_stack2]

Hi Mark,

Thanks for your response. Answers to your questions...

Sorry, my cut and paste was not clear but I always drop the database from the master database. ASE doesn't allow me to execute "DROP DATABASE xxx" if I am currently in "xxx".

1. We did bump up the "number of open databases" after we noticed the error but this was after the other problems that alerted me to go back through the customer's log files.

2. Our software takes snapshots of the file system at certain points in time. By removing the NFS mount before issuing the "DROP DATABASE" command, we prevent ASE from zero'ing the pages. All references to the database are properly removed from the system tables on all other platforms other than AIX. Then we can restore the file system (to whatever snapshot is selected) and issue the "MOUNT DATABASE" command and put the database back into a useable state. When you say that this is not a "suggested/supported/smart operation", are there known bugs surrounding this? Have you seen documentation suggesting this (I couldn't find anything here http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1600/doc/html/san1393050983039...?

What if you had a disk failure and you needed to drop/recreate the database on a different file system? Are you saying you should not execute "DROP DATABASE" if the disks failed and were removed?

This morning I am having trouble reproducing the behavior on Solaris so I will have to backtrack my steps. I love Mondays!

[Mark_A_Parsons]

The comment about "not a suggested/supported/smart operation" is in reference to yanking devices out from under a dataserver, eg, said operation appears to have left your AIX dataserver in an unstable state.

[former_member89972]

What ASE version do you run ?

Check the various status bits in master..sysdatabases for the database "xxx_trading".

What does full output of sp_helpdb show ?

What does select db_name() show after you execute "use xxx_trading" and it succeeds/fails ?

As Bret says, use <dbname> does not fail silently ? So can you check the error status after that command ?

You may be able to off line the database using new dbcc dbreboot command. (Use at your discretion)

More details on the command at link below

https://wiki.scn.sap.com/wiki/display/SYBASE/DBCC+dbreboot

Avinash

[former_member188958]

Hi Neal,

When you issued the "use xxx_trading" command, what database were you in?
I suspect you were already in master and the USE command failed silently and simply left you in master.

What were the exact sequence of commands you used to drop the database and devices?

I don't recall seeing "use database" fail silently before.

-bret

[neal_stack2]

Hi Bret,

Happy New Year!

I got their ASE error log and have a little more background and I think your explanation makes sense. The specific version string:

Adaptive Server Enterprise/15.7/EBF 23820 SMP SP132 /P/RS6000/AIX 6.1/ase157sp131x/3919/64-bit/FBO/Tue Jan 20 04:15:36 2015

We attempted to do an "UNMOUNT DATABASE xxx_trading to '/some/manifestfile' WITH OVERRIDE". This failed with:

ASE message 905 : Unable to allocate a DBTABLE descriptor to open database 'ams_trading'. Close or drop another database before opening this one, or ask your System Administrator to raise the configuration parameter 'number of open databases'.

When the UNMOUNT fails, we fallback to trying a "DROP DATABASE". Before unmounting the database, we unmount the NFS share where the database devices live so that the "DROP DATABASE" does not write to the devices:

SELECT d.name AS device_name, SUM(u.size) AS device_size, d.phyname as device_path FROM master..sysusages u, master..sysdevices d WHERE d.vdevno=u.vdevno AND dbid=db_id('xxx_trading') GROUP BY d.name

ASE message 823 : I/O error detected during read for BUF=0x0000000168169520, MASS=0x0000000168169520, Buf#=0, page=0x000000016800a800, dbid=15, Mass vdevno=92, vpage=27, Buf lpage=25, Mass stat=0x4000100, Buf stat=0x1, size=2048, cid=0 ('default data cache'), Pinned xdes=0x0000000000000000, spid=0. 

DROP DATABASE xxx_trading

ASE message 3758 : Cannot drop this database because ASE cannot determine its status. Please use DBCC DBREPAIR(xxx_trading, DROPDB) to drop this database.

And then I experienced the weirdness with "use xxx_trading".

I tried reproducing with a slightly older version (Adaptive Server Enterprise/15.7.0/EBF 19803 SMP ESD#01 /P/RS6000/AIX 6.1/aseasap/2918/64-bit/FBO/Wed Feb 8 06:09:12 2012) but I'm having trouble getting error 905 even after setting the number of open databases down to 6.

I tried something slightly different. I unmounted the NFS directory containing my devices and tried the UNMOUNT command:

1> UNMOUNT DATABASE Vpine to "/tmp/Vpine.manifest" with OVERRIDE
2> go
Msg 823, Level 24, State 1:
Server 'AIX10114_S1', Line 1:
I/O error detected during read for BUF=0x000000018544df70, MASS=0x000000018544df70, Buf#=0, page=0x0000000182fc3000, dbid=0, Mass vdevno=98, vpage=2, Buf lpage=0, Mass stat=0x110, Buf stat=0x0, size=4096, cid=0 ('default
data cache'), Pinned xdes=0x0000000000000000, spid=0.
ASE is terminating this process.
isql -Usa -SAIX10114_S1 -w220 -Psybase 
1> use Vpine
2> go
1> select db_name()
2> go
                               
 ------------------------------ 
 Vpine                          
(1 row affected)

1> drop database Vpine
2> go
Msg 823, Level 24, State 1:
Server 'AIX10114_S1', Line 1:
I/O error detected during read for BUF=0x000000018544d3a8, MASS=0x000000018544d3a8, Buf#=0, page=0x0000000182fb6000, dbid=9, Mass vdevno=98, vpage=690, Buf lpage=344, Mass stat=0x4000100, Buf stat=0x1, size=4096, cid=0
('default data cache'), Pinned xdes=0x0000000000000000, spid=0.
1> sp_helpdb
2> go
 name                         db_size       owner dbid  created      durability  lobcomplvl inrowlen status                                                                                    
 ---------------------------- ------------- ----- ----- ------------ ----------- ---------- -------- ----------------------------------------------------------------------------------------- 
 Vpine                             250.0 MB sa        9 Dec 29, 2016 full                 0        0 select into/bulkcopy/pllsort, trunc log on chkpt  

So my questions:

1. Should the database be marked "offline" for this type of error?
2. On AIX the "drop database" command fails when error 823 is raised and the database is left behind. I have to restart ASE in order to drop the database which the customer finds undesirable. On other platforms (I tested Solaris and believe Linux behaves the same way), when I issue "DROP DATABASE" and get error 823, the database is dropped despite the error. Why does AIX behave differently here?

Adaptive Server Enterprise/15.7/EBF 22235 SMP SP121 /P/Solaris AMD64/OS 5.10/ase157sp12x/3660/64-bit/FBO/Thu Mar 20 06:07:20 2014

1> drop database Vdb2
2> go
Msg 823, Level 24, State 1:
Server 'nstacksolasestg', Line 1:
I/O error detected during read for BUF=0xfffffd7ffda7e718, MASS=0xfffffd7ffda7e718, Buf#=0, page=0xfffffd7ffd552000, dbid=11684, Mass vdevno=43, vpage=1520, Buf lpage=760, Mass stat=0x4000100, Buf stat=0x1, size=4096,
cid=0 ('default data cache'), Pinned xdes=0x0000000000000000, spid=0.
1> sp_helpdb
2> go
 name           db_size       owner dbid  created      durability  lobcomplvl inrowlen status                                                                                    
 -------------- ------------- ----- ----- ------------ ----------- ---------- -------- ----------------------------------------------------------------------------------------- 
 master               26.0 MB sa        1 Aug 29, 2016 full                 0     NULL mixed log and data                                                                        
 model                 6.0 MB sa        3 Aug 29, 2016 full                 0     NULL mixed log and data                                                                        
 pubs2                 7.0 MB sa        4 Aug 29, 2016 full                 0     NULL trunc log on chkpt, mixed log and data                                                    
 sybsystemdb           6.0 MB sa    31513 Aug 29, 2016 full                 0     NULL mixed log and data                                                                        
 sybsystemprocs      172.0 MB sa    31514 Aug 29, 2016 full                 0     NULL trunc log on chkpt, mixed log and data                                                    
 tempdb                6.0 MB sa        2 Jan 03, 2017 no_recovery          0     NULL select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data, allow wide dol rows 

Thanks,

Neal