Reproducible: dbsrv17 -k

...stay tuned for more details.


The previous title was "Why are most DB_PROPERTY values returned as zero?" but the question is more fundamental: What could possibly make 17.0.10.5963 return '0' from DB_PROPERTY ( 'ConnCount' ) without crashing the server or causing other symptoms?


Previous post...

Why are most DB_PROPERTY values returned as zero?

I have never seen anything like this: The databases in one instance of SQL Anywhere 17.0.10.5963 are returning zero for many DB_PROPERTY values.

For example, DB_PROPERTY ( 'ConnCount' ) cannot be zero, yet for this server the return value is '0'.

Here are some excerpts from SELECT CURRENT TIMESTAMP, @@VERSION, * FROM sa_db_properties() from the suspect server, and from a test server on a different computer:

Suspect server...

2020-02-12 08:53:22.426,'17.0.10.5963',0,392,'ApproximateCPUTime','Approximate CPU time used','1724030.1124608'
...
2020-02-12 08:53:22.426,'17.0.10.5963',0,2,'BytesReceived','Bytes received by server','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,3,'BytesReceivedUncomp','Bytes received after decompression','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,4,'BytesSent','Bytes sent to client','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,5,'BytesSentUncomp','Bytes sent before compression','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,10,'CacheHits','Cache Hits','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,16,'CacheRead','Cache reads','0'
...
2020-02-12 08:53:22.426,'17.0.10.5963',0,43,'ConnCount','Number of active connections','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,393,'ConnectedTime','Total time connections have been connected','14877281.5125692'
...
2020-02-12 08:53:22.426,'17.0.10.5963',0,54,'DiskRead','Disk reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,55,'DiskReadHint','Disk read hints','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,56,'DiskReadHintPages','Disk read hint pages','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,52,'DiskReadIndInt','Disk index interior reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,53,'DiskReadIndLeaf','Disk index leaf reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,63,'DiskReadTable','Disk table reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,66,'DiskReadWorkTable','Disk work table reads','0'

Working server...

2020-02-12 08:30:39.808,'17.0.10.5963',0,392,'ApproximateCPUTime','Approximate CPU time used','.2028544'
...
2020-02-12 08:30:39.808,'17.0.10.5963',0,2,'BytesReceived','Bytes received by server','24501'
2020-02-12 08:30:39.808,'17.0.10.5963',0,3,'BytesReceivedUncomp','Bytes received after decompression','24501'
2020-02-12 08:30:39.808,'17.0.10.5963',0,4,'BytesSent','Bytes sent to client','11262'
2020-02-12 08:30:39.808,'17.0.10.5963',0,5,'BytesSentUncomp','Bytes sent before compression','11262'
2020-02-12 08:30:39.808,'17.0.10.5963',0,10,'CacheHits','Cache Hits','8914'
2020-02-12 08:30:39.808,'17.0.10.5963',0,16,'CacheRead','Cache reads','8914'
...
2020-02-12 08:30:39.808,'17.0.10.5963',0,43,'ConnCount','Number of active connections','3'
2020-02-12 08:30:39.808,'17.0.10.5963',0,393,'ConnectedTime','Total time connections have been connected','89.2171611900035'
...
2020-02-12 08:30:39.808,'17.0.10.5963',0,54,'DiskRead','Disk reads','191'
2020-02-12 08:30:39.808,'17.0.10.5963',0,55,'DiskReadHint','Disk read hints','146'
2020-02-12 08:30:39.808,'17.0.10.5963',0,56,'DiskReadHintPages','Disk read hint pages','222'
2020-02-12 08:30:39.808,'17.0.10.5963',0,52,'DiskReadIndInt','Disk index interior reads','10'
2020-02-12 08:30:39.808,'17.0.10.5963',0,53,'DiskReadIndLeaf','Disk index leaf reads','59'
2020-02-12 08:30:39.808,'17.0.10.5963',0,63,'DiskReadTable','Disk table reads','123'
2020-02-12 08:30:39.808,'17.0.10.5963',0,66,'DiskReadWorkTable','Disk work table reads','0'

asked 12 Feb, 10:36

Breck%20Carter's gravatar image

Breck Carter
29.9k490664970
accept rate: 20%

edited 13 Feb, 10:51

What is in 'rroad_database_properties()'? Is it really just a select DB_PROPERTY( 'ConnCount' )? or is is calling one of the sa_.*_property() functions? If it is the latter, might it be returning properties for multiple databases (eg, including the utility_db)? Or maybe you are connected to the utility_db?

(12 Feb, 10:42) John Smirnios
Replies hidden

Sorry, wrong copy-and-paste, fixed it :)

(12 Feb, 10:45) Breck Carter

Okay... sa_db_properties returns properties of all databases by default. The first column is the database number. Does the suspect server list properties for any other databases? Equivalently, how many rows does sa_db_list() return (since sa_db_properties uses sa_db_list() to get all db IDs)?

(12 Feb, 11:17) John Smirnios

I don't have immediate access to the suspect server.

The suspect server has only one database.

Note that DB_PROPERTY ( 'ConnCount' ) returns '0'.

SQL Central also displays zero in the Server - Database tab - # Conn column.

I don't know how SQL Anywhere can return so many invalid property values without otherwise crashing.

This behavior started sometime between September 2018 (17.0.9.4803) and February 2020 (17.0.10.5963).

My feeling is the software should be reinstalled from scratch.

(12 Feb, 13:13) Breck Carter

This not a bug, it is a feature!

The server -k option was introduced in SQL Anywhere 10, and one of the side effects is to turn off DB_PROPERTY ( 'ConnCount' ) and other stuff (although I haven't exhaustively tested it on V10 through 16, just 17.

Anyway, the client's gonna remove dbsrv17 -k

...and I'm gonna add code to Foxhound to send an Alert if the target database is running with -k.

permanent link

answered 13 Feb, 13:41

Breck%20Carter's gravatar image

Breck Carter
29.9k490664970
accept rate: 20%

and one of the side effects is to turn off DB_PROPERTY ( 'ConnCount' )

Seems to be an undocumented side effect, right?

(And another "Stealth mode" option:))

(14 Feb, 03:19) Volker Barth
Replies hidden

Here's where it gets really embarrassing: This behavior is documented in the Foxhound Help; go to this topic and click on "(What is INT: StmtPerfMngrConn?)" to see this...

You can eliminate the INT: StmtPerfMngrConn connection by using the dbsrv17 -k option, but that's a bad idea if you use Foxhound because it disables many important performance properties
(14 Feb, 08:27) Breck Carter

Hm, I would not think of a mere "ConnCount" as an important performance property...

(14 Feb, 09:05) Volker Barth

ConnCount isn't the only one disabled.

(14 Feb, 10:56) Breck Carter

Yes, I'm aware, I was just trying to understand the docs and still are thinking that I would not consider "ConnCount" a performance property at all...:) But apparently, it is.

(14 Feb, 11:13) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×4

question asked: 12 Feb, 10:36

question was seen: 139 times

last updated: 14 Feb, 11:13