Skip to Content
0

Strange behavior after dropping database on ASE 15.7 SP132

Jan 03, 2017 at 06:33 PM

192

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Bret Halford
Jan 03, 2017 at 08:45 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Avinash Kothare Jan 04, 2017 at 05:06 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Jan 07, 2017 at 01:13 AM
0

Neal,

That's a lot of info that should've been provided with the original post ...

1 - When unmount database failed due to the db descriptor issue, did anyone actually try bumping up the number of open databases config?

2 - Who's idea was it to yank the devices out from under a database? [That's not a suggested/supported/smart operation.]

3 - Soooo, the client doesn't like the idea of bouncing the dataserver, but they're ok with effectively corrupting the dataserver by yanking away underlying db devices in a non-standard/non-approved manner?

4 - It's not apparent, from the code snippets provided, that you ran the same exact test in the AIX and Solaris(?) dataservers:

4a - the AIX example shows use Vpine, drop database Vpine, and the Vpine database still exists; setting aside for the moment the Msg 823, this is the behavior I'd expect since you can't run drop database from within said database (ie, you'll be told to use master before issuing drop database) [I'm guessing the Msg 823 is masking the normal error/message you'd get for trying to dump database while not in the master database with the result being that the drop database fails - not because of the Msg 823 but because you're not in the master database - and the Vpine db remains intact.]

4b - for the AIX example, what happens if you run drop database Vpine from within the master database? [If the Vpine database *is* dropped then this would tend to indicate the drop database command *is* working - or at least removing the record from sysdatabases - and that pesky Msg 823 isn't actually aborting the drop database command.]

4c - the Solaris (?) example doesn't show us what database you were in when you ran drop database; if you were in the master database then you got the expected behavior, ie, the database was dropped; if you were not in the master database (eg, you were in the Vdb2 database) then I'd be worried about behavior that differs from the AIX example

5 - for the successful drop database commands, have you verified that all master system tables (eg, sysdatabases, sysusages, sysattributes) have had references to said database removed? [I'd be worried about the Msg 823 generation indicating that part of the dump database command's under-the-cover operations are not being completed.]

-------------

If you find that drop database (when run from the master database) *is* working properly in all environments, then I'm guessing the Msg 823 - while indicative of a major issue - isn't hampering the drop database command.

-------------

I'd also recommend someone go back to the drawing board re: how to handle a failed unmount database ... yanking devices out from under the dataserver, while not an approved method, is just plain not wise; besides the Msg 823 there could be other issues lurking under the covers that could affect the stability and behavior of the dataserver (eg, your original issue with use <db> not complaining but leaving you in the master database).

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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.html)?

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!

0

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.

0